存儲過程

資料庫程式設計基礎

【辨別符】

       資料庫對象的名稱即辨別符

       辨別符分為标準辨別符和分隔辨別符，分隔辨別符即辨別符中包括一些特殊字元，或者定義的辨別符為系統中的一些保留字

       如果使用分隔辨別符，必須使用括号或者方括号辨別起來

【資料類型】

1、  精确數字

a)         Bigint

b)        Smallint

c)         Int

d)        Tinyint

e)         Money

f)         Smallmoney

g)        Numeric

h)        Bit

i)          decimal

2、  近似數字

a)         Float

b)        Real

3、  日期和時間

a)         Datetime

b)        smalldatetime

4、  字元串

a)         Char

b)        Text

c)         Varchar

5、  Unicode

a)         Nchar

b)        Ntext

c)         nvarchar

6、  二進制字元串

a)         Binary

b)        Image

c)         varbinary

7、  其他資料類型

a)         Cursor，遊标資料

【運算符】

1、  算術運算符

a)         +、-、*、/、%（取模）

2、  邏輯運算符

a)         ALL

b)        AND

c)         ANY

d)        BETWEEN

e)         EXISTS

f)         IN

g)        LIKE

h)        NOT

i)          OR

j)          SOME

3、  指派運算符

4、  字元串聯接運算符

5、  比較運算符

流程控制語句

1、  IF……ELSE條件分支語句

If  布爾表達式

    {SQL語句 |  SQL語句塊}

ELSE 

布爾表達式是傳回TRUE或者FALSE的表達式；如果表達式包含SQL語句，則SQL語句必須用括号括起來

例一：如果合同号為101的合同金額大于10000元，則顯示“該合同金額為10000元”，否則顯示“該合同金額不足10000元”

       If (select charge from contract)>10000

              Print ‘該合同金額為10000元’

       ELSE

              Print ‘該合同金額不足10000元’

2、  用BEGIN……END定義語句塊

BEGIN

{

SQL語句

}

END

可以寫多條SQL語句，在BEGIN……END塊中的語句順序執行

3、  WHILE語句實作循環

While  布爾表達式

{SQL語句 |  SQL語句塊}

{break;}

{continue}

如果使用嵌套的循環，則BREAK會中斷本次循環；跳到外層循環去執行

RETURN可以無條件中止循環

CONTINUE可以使循環重新開始執行

例二：判斷一個數是否為素數

       Declare @i int

       Declare @itest int

       Set @itest=59

       Set @i=2

       While @i<@itest

       Begin

              If @itest %@i=0

              Begin

                     Print ‘該數不是素數’

                     Break

              End

       Set @i=@i+1

       If @itest=@i

       Print ‘該數是素數’

       End

4、  WAITFOR為語句設定執行的延遲

WAITFOR {DELYA ‘TIME’ |  TIME ‘TIME’}

例三：

       {

              Waitfor time ’23:30’

              Exec backup_contract

5、  TRY……CATCH語句

此語句用于對異常的錯誤處理

BEGIN TRY

END TRY

BEGIN CATCH

END CATH

TRY語句塊後必須緊跟CATCH塊；如果TRY語句塊中沒有錯誤，則轉到緊鄰的下一個END CATCH語句塊執行；如果有錯誤，則會轉給下一個CATCH語句中的第一個語句執行

TRY……CATCH語句使用的錯誤函數

1、  ERROR_NUMBER()，傳回錯誤号

2、  ERROR_MESSAGE()，傳回具體錯誤資訊

3、  ERROR_SEVERITY()，傳回錯誤嚴重性

4、  ERROR_STATE（），傳回錯誤狀态号

5、  ERROR-LINE（），傳回導緻錯誤的行号

6、  ERROR_PROCEDURE()，傳回導緻錯誤的存儲過程或者觸發器

【遊标】

       在需要按行處理資料的時候，遊标可以打開一個結果集，并提供按行處理資料的功能。根據不同的遊标，可以對其進行復原或者前進

一、遊标的定義

1、  SQL-92文法

DECLARE NAME [INSENSITIVE] [SCROLL] CURSOR

For select_statement

[for { READ ONLY  |  UPDATE [OF column_name]}]

2、  TRANSACT-SQL擴充文法

DECLARE NAME CURSOR

[ LOCAL | GLOBAL]

[ FORWARD_ONLY | SCROLL]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[ READ ONLY | SCROLL_LOCKS | OPTIMISTIC]

[ TYPE_WARNING]

FOR select_statement

[for UPDATE [OF column_name] ]

1)        NAME：為遊标定義名稱

2)        INSENSITIVE：建立将由該遊标使用的資料的臨時複本。對遊标的所有操作都從臨時資料庫中獲得資料；是以對些遊标進行提取資料時不反映對基表所做的修改；該遊标不可以修改

3)        SCROLL：指定所有的提取操作均可以實作；如果不指定則隻支援NEXT的提取資料操作

4)        select_statement：用于定義SQL語句塊，該語句塊不支援使用COMPUTE,COMPUTE BY,FOR BROWSE,INTO

5)        READ ONLY：禁止對遊标進行修改

6)        UPDATE [OF column_name：用于指定可以更新的資料列

7)        LOCAL：用于指定遊标是局部的；GLOBAL用于指定全局遊标

8)        FORWARD_ONLY：用來指定遊标隻能向前滾，即從第一行讀到最後一行

9)        STATIC：用于定義靜态遊标，允許向任何一個方向移動遊标讀取資料；但如果在使用者檢視資料的同時，資料進行了更新，它不會動态地顯示出資料的變化

10)     DYNAMIC和KEYSET型遊标：可以動态地顯示資料的更新；其中KEYSET型遊标不能看到其他使用者增加的記錄

11)     FAST_FORWARD：用于指定啟用了性能優化的FORWARD_ONLY、READ_ONLY型遊标

12)     SCROLL_LOCKS：鎖會被放置到遊标的結果集中

13)     OPTIMITISC：資料被讀入遊标後，如果對某一行資料進行更新或者删除，操作有可能會失敗

14)     TYPE_WARNING：指明遊标類型被修改成與使用者定義不同時，向用戶端發送一個警告資訊

例四：添加一個隻讀型的遊标

       DECLARE CompanyCursor CURSOR

       For select * from company

       For READ ONLY

二、打開遊标

Open cursor-name

例五：

       Open CompanyCursor

結果會顯示出company表中的所有記錄，當然記錄是存在于遊标中的，使用者可以按照自己的需要按行來處理資料

三、使用遊标讀取資料

1、  如果定義為可滾動【SCROLL型遊标】的 ，則可以在任何時候讀取任意行資料

2、  如果定義為非滾動的遊标，則隻能對目前行的下一行進行讀取操作

3、  遊标的結果集可以放入到局部變量中

FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]  FROM { [GLOBAL] cursor-name | cursor-variable-name}

[INTO @variable-name]

1)        NEXT：表示緊跟目前行傳回結果行，并且以傳回行作為目前行；預設的遊标提取資料選項

2)        PRIOR：表示傳回目前行前一行的資料，并以傳回行作為目前行

3)        FIRST：傳回第一行資料，并作為目前行

4)        LAST：傳回最後一行資料，并作為目前行

5)        ABSOLUTE { n | @nvar }：如果n為正數，傳回從頭開始的第N行記錄；如果為負數，傳回從尾開始的第N行資料；如果為零，不傳回資料

6)        RELATIVE { n | @nvar }：如果N為正數，傳回從目前行開始的第N條記錄；如果為負數，則傳回從目前行前的第N條資料；如果為零，則傳回目前行

7)        GLOBAL：用于指定遊标是全局遊标

8)        INTO：用此語句将遊标的結果集放入一個局部變量中

9)        每一次的FETCH操作都會存儲在系統變量@@fetch-status中。如果執行成功，則變量被設定為0；其值為-1表示FETCH語句失敗或行不存在在結果集中；如果為-2表示要提取的行不存在

四、關閉和釋放遊标

CLOSE cursor-name

DEALLOCATE cursor-name

【存儲過程】

       存儲過程分為系統存儲過程和使用者定義存儲過程；其中系統存儲過程用sp_ 開頭，擴充存儲過程也屬于系統存儲過程，其用xp_開頭

定義存儲過程

       CREATE PROCEDURE [schema-name.] procedure-name [ ; number]

       [ {@parameter [ type_schema_name.] datatype}

       [ VARYING ] [ =default ] [ [ OUT  [ PUT ] ]

       [ WITH <procedure_option> ]

       [ FOR REPLICATION ]

       AS { sql-statement }

       <procedure_option> ::=

       [ ENCRYPTION ]

       [ RECOMPILE ]

       [ EXECUTE_AS_Clause ]

1)        @parameter：是過程中的參數。使用@符号作為第一個字元來指定參數名稱

2)        type_schema_name.] datatype：指定參數的資料類型，除了TABLE類型外的所有資料類型都可以作為存儲過程的參數使用。如果使用CURSOR類型的參數，則必須指定VARYING和OUTPUT參數

3)        VARYING指定作為輸出參數支援的結果集

4)        OUTPUT訓示參數是輸出參數。此選項的值可以傳回給調用EXECUTE的語句

5)        RECOMPILE：訓示資料庫引擎不緩存該過程的計劃，該過程在運作時編譯

6)        ENCRYPTION：訓示對存儲過程文本進行加密。資料庫進行複制時不會對此檔案進行釋出

7)        EXECUTE AS ：指定在其中執行存儲過程的安全上下文

8)        FRO REPLICATION：指定不能在訂閱伺服器上執行為複制建立的存儲過程

例六：建立一個存儲過程，傳回指定公司的所有合同明細情況

       CREATE PROCEDURE DisplayCompanyDetail

       @CompanyName varchar(50)

       AS

              Select * from contract where CompanyName=@CompanyName

       EXEC DisplayCompanyDetail ‘北京電信公司’