Jiang Ying-Fu的部落格

隔了很多年，第二篇開工。

此文章只對初學者有用。

Trigger 觸發程序

Create Trigger [Trigger Name] on [Table Name] For [Action Event]

as

begin

   SQL語法

   ( Inserted , Deleted )

end

以上為簡略格式，若要詳細設定，請至各官方網站參考。

Trigger Name ：自訂名稱

Table Name：目標的Table名稱

ActionEvent：動作，如Insert、Update、Delete

Inserted：執行Insert和Update時產生的暫存變更資料表。

Deleted：執行Update和Delete時產生的暫存變更資料表。

Trigger是用在當資料異動時，接續要處理的動作、程式。

 例如說

當訂單明細的金額變更時，主訂單的總金額也要同步更新。

一般常見的做法是在程式執行兩次SQL語法。

但了解Trigger的人，則會在Client針對明細做異動

其餘的由Trigger幫助運算並變更。

程式範例如下：

Create Trigger Trg_Detail on Detail For Insert 

as 

Begin

    Declare @oid as Char(10)  -- 宣告變數，記錄訂單編號

    Declare @price as Integer --宣告變數，記錄明細金額

    Select @oid = oid , @price = price From Inserted) --將查詢到的訂單編號和金額，指向至變數

    Update Orders Set Price = Price + @price Where Oid = @oid  --依據變更資料表查到的訂單編號，更新主訂單的金額。

End

Trg_Detail 是自訂的Trigger名稱。

On 後面接著的是 Detail ，是指針對 Detail 有動做時。

For 後面接著的是 Insert ，所以是指使用者在執行 Insert 語法時。

Inserted 是資料庫的變更後暫存資料區，是專供Trigger使用的資料表。

Inserted 和 Deleted 這兩個資料表，是一般人最多問號的地方。

很多人會問為什麼沒有 Updated ，這是因為其實 Update 是由兩個動做所組合。

Update 動作，會先將舊資料放至 Deleted(刪除區)，再將新資料放至 Inserted(新增區)

 所以在Trigger中，可以透過Delete(刪除區)，找到更新前，或是刪除前的資料。

而在Inserted(新增區)中，尋找到新增或更新後的記錄。

亦可改成如下：

Create Trigger Trg_Detail on Detail For Insert , Update

as 

Begin

    Declare @oid as Char(10)  -- 宣告變數，記錄訂單編號

    Declare @price as Integer --宣告變數，記錄明細金額

    Select @oid = oid , @price = price From Inserted) --將查詢到的訂單編號和金額，指向至變數

    Update Orders Set Price = Price + @price Where Oid = @oid  --依據變更資料表查到的訂單編號，更新主訂單的金額。

End

當執行 Insert 或 是 Update 時，就觸發這段程式。

用半型逗號「 , 」隔開。

Procedure 預存程序

Create Procedure [  Procedure Name ] 

AS 

Begin

      自訂程式

End

Create Procedure [  Procedure Name ]  ( 自訂外來參數)

AS 

Begin

      自訂程式

End

在SQLServer 的SQLQuery中的執行方法是

 Exec  [  Procedure Name ]  [自訂外來參數]

在一般程式中要呼叫執行的方法

把執行SQL指令函數中的SQL語法，改成 [  Procedure Name ] 就可以了。

例如SqlCommand.CommandText = [  Procedure Name ] ;

預存程序中若要自訂變數使用

語法如下：

Declare @a Integer          -- 宣告變數 @a 為Integer型態

Declare @b VarChar(20)  -- 宣告變數 @b 為VarChar(20)型態

Set @a = 20      -- 設定 @a 為20

Set @b = 'Test'  -- 設定 @b 為 Test 字串

Set @b = (Select Name From Student Where ID = @a ) -- 設定 @b 為 從Student資料表中ID = 20 的學生姓名

若要在SQL語法的Where條件中加入變數

語法如下：

Select * From TableName Where Seqn = @a

預存程序中的查詢條件要使用Like語法的方法

使用變數，並把%放至變數中。

範例：

Declare @tmp Varchar(20)

Set @tmp = ‘王%’

Select * from Students Where Name like @tmp

Declare & Select 多個欄位

在預存程序和觸發程序中，當我們需要一次針對單筆記錄

同時取出多個欄位的值時，可以使用下面的做法。

(1)宣告變數

Declare @id Varchar(20) 

Declare @name Varchar(20)

Declare @sex bit

(2)執行SQL指令，並指定變數和欄位的對應

Select @id = id , @name = name , @sex = sex

From Student

where id = 'X70X550'

此方法只限定查詢結果為一筆的記錄。

索引 Index 

Create Index IndexName on TableName (ColumnName)

Create Index IndexName on TableName (ColumnName1,ColumnName2,......,ColumnNameN)

1.索引是用來加快查詢(Where條件)速度用。

2.索引會佔用大量空間。

3.不是所有欄位都適合當索引。

4.不適當的索引反而會讓查詢變慢。

5.每隔一段時間要重新檢視之前建立的索引是否損壞。

6.建立索引的建議條件
   (1) 固定長度的欄位 Char > VarChar
   (2) 型態：數字 > 英文字  Int > Char
   (3) 存在大量Null值的欄位不建議當索引。
   (4) 資料屬唯一者最佳，重覆性越低越好。
   (5) 資料永遠不會變動的資料欄最佳。
   (6) 程式設計師真正有使用到的欄位。 

Fetch 指令 (SQL Server)

Declare 指標 Cursor For Select語法

Fetch Next From 指標 Into 變數容器1,變數容器2,變數容器3,......

Declare rs Cursor For Select id,name From student --宣告建立rs指標，指向後方的select查詢

Declare @id Char(8) --宣告一個變數id
Declare @name VarChar(12) --宣告一個變數name

Open rs --開啟執行rs 

Fetch Next From rs Into @id , @name --抓取rs指標目前所指向的下一筆記錄，也就是vb中的MoveNext 

While @@Fetch_Status = 0 -- 0是成功，-1是失敗，-2是遺漏

Begin

  Print @id --印出抓取出來的id
  Print @name --印出抓取出來的name

  Fetch Next From rs Into @id , @name --抓取rs指標目前所指向的下一筆記錄，也就是vb中的MoveNext

End

Close rs --關閉rs指標
Deallocate rs --釋放rs指標

以上是一個簡略的範例

Declare，其實就是一個宣告指令
「Declare @id char(8)」，就如同使用Java的宣告一個字串型變數 id，「string id ;」

 所謂有借有還，再借不難。在這裡是「有開有關，再開不難」。
若是程式中，只有open，而沒有close，那再下一次程式啟動時，就會發現到rs無法再使用。
這時需另外執行close指令，否則rs會一直保留著，直到SQL Server釋放為止。

為何會有兩個Fetch Next？
因為一個資料表，可以拆分表頭、表身、表尾三個區段。
其實換個角度看，也就是如下表

一般正常的情況下，剛抓出來的資料，都是停留在BOF
即使抓出來的資料表是空表，一樣有BOF和EOF
所以第一步都需將指標往下移一次
這是一些程式語言的通例。

至於在while廻圈內，為何還要再打一次？
那是因為指標在沒有下指令前，是不會有任何的動作
若是沒有重新抓取下一筆，那指標永遠會停留在最後一次抓取的記錄
造成無窮迴圈的出現。

Begin .... End 其實就是程式中的 { } 
也是如下所示(左邊是TSQL，右邊是XX程式語言)

@@Fetch_Status
是指最後一次指標所發出來的指標狀態。
注意：若一支程式中，有多個指標，這時會容易有人的錯亂感覺產生。
請善用Begin....End，並且把程式流程完善後，即可消除此錯亂感覺。

單引號引發的錯誤

單引號「 ' 」

String sql = " Select name From book Where name='Jason's book' "

在SQL語法執行時，會引發錯誤。

因為在解析時，會拆解成類似以下的五個SQL區塊。

SQL1：「Select name」

SQL2：「From book」

SQL3：「Where」

SQL4：「name='Jason'」

SQL5：「s book'」

會因為SQL4語法破碎，而產生SQL5而引發錯誤。

為了解決這個問題，語法要改成使用兩個單引號

String sql = " Select name From book Where name='Jason''s book' "

這時SQL在解析語法時，會變成

SQL1：「Select name」

SQL2：「From book」

SQL3：「Where」

SQL4：「name='Jason's book'」

使得SQL4區塊完整不破碎，而沒有SQL5的存在。

單引號在SQL有「斷開」的意思存在，所以在使用上要小心。

以上資料，僅供參考。