SQL Server 預存程序、觸發器、遊標

標籤：

預存程序

    1、預存程序是事先編好的、儲存在資料庫中的程式，這些程式用來完成對資料庫的指定操作。

    2、系統預存程序: SQL Server本身提供了一些預存程序，用於管理有關資料庫和使用者的資訊。

       使用者預存程序: 使用者也可以編寫自己的預存程序，並把它存放在資料庫中，供用戶端調用。

    3、這樣安排的主要目的就是要充分發揮資料庫伺服器的功能，盡量減少網路上的堵塞。

    4、系統預存程序

    概念：

    它的目的在於能夠方便地從系統資料表中查詢資訊，或者完成與更新資料庫表相關的管理工作或其它的系統管理任務。

    系統預存程序可以在任意一個資料庫中執行。建立並存放於系統資料庫master中，並且名稱以sp_或者xp_開頭。

    部分系統預存程序：

       sp_addtype：用於定義一個使用者定義資料類型。

       sp_configure：用於管理伺服器配置選項設定。

       xp_sendmail：用於寄送電子郵件或呼叫資訊。

       sp_stored_procedures：用於返回當前資料庫中的預存程序的清單。

       sp_help：用於顯示參數清單和其資料類型。

       sp_helptext：用於顯示預存程序的定義文本。

       sp_rename：用於修改當前資料庫中使用者物件的名稱。

       Sp_who:用於顯示使用資料庫的目前使用者

       sp_help：用於顯示參數清單和其資料類型。

       sp_depends：用於顯示預存程序依據的對象或者依據預存程序的對象

       sp_helptext：用於顯示預存程序的定義文本。

    5、使用者自訂的預存程序

    定義格式：

       CREATE PROC[edure] procedure_name [ ; number ]

       [ @parameter data_type [ = default ][output], … uc1]

       AS sql_statement

 

       procedure_name：給出預存程序名；

       number：對同名的預存程序指定一個序號(允許同名)；

       @parameter：給出參數名；

       data_type：指出參數的資料類型；

        Output:傳回值參數

       default：給出參數的預設值；

       sql_statement：預存程序所要執行的SQL語句，它可以是一組SQL語句，可以包含流程式控制制語句等。

    注意事項:

        預存程序一般用來完成資料查詢和資料處理操作，所以在預存程序中不可以使用建立資料庫物件的語句，

        即在預存程序中一般不能含有以下語句：

       CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT ；

        CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE

    預存程序的傳回值和狀態資訊:

        無論什麼時候執行預存程序，總要返回一個結果碼，用以指示預存程序的執行狀態。

        如果預存程序執行成功，返回的結果碼是0；如果預存程序執行失敗，返回的結果碼一般是一個負數，它和失敗的類型有關。

        我們在建立預存程序時，也可以定義自己的狀態代碼和錯誤資訊。

 

    執行預存程序：

    例：執行帶參數的預存程序,查詢大於歲的學生

       create proc show;3 ( @pno char(6) )

       as

       select * from person where Pno = @pno

       exec show;3 4

    例：CREATE Procedure sp_getstu;1

       AS

       SELECT * FROM 學生

    例：帶參數的預存程序,查詢大於指定年齡的學生

       CREATE proc sp_getstu;2 (@sage int)

       AS

       SELECT * FROM 學生WHERE年齡> @sage

 

    例: 帶輸出參數的預存程序,查詢指定學生的年齡

       CREATE proc sp_getstu;3 ( @name char(10) , @age int output )

       AS

       SELECT @age=年齡 FROM學生WHERE姓名= @name

 

       Declare @sage int

       Exec sp_getstu;3 ‘張三‘,@sage

       Print @sage

 

    例：帶參數和返回狀態值的預存程序。

       CREATE PROCedure sp_getstu;3 (@sage int =NULL )

       AS

       IF @sage IS NULL

           BEGIN

              PRINT ‘必須提供一個數值作參數！‘

              RETURN 13

           END

       IF NOT EXISTS (SELECT * FROM student WHERE sage > @sage)

           BEGIN

              PRINT ‘沒有滿足條件的記錄！‘

              RETURN -103

           END

           SELECT * FROM student WHERE sage > @sage

           RETURN 0

       DECLARE @status int

       EXECUTE @status=sp_getstu;3 22

       print @status

 

 

觸發器

一﹕ 觸發器是一種特殊的預存程序﹐它不能被顯式地調用﹐而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地啟用。所以觸發器可以用來實現對錶實施複雜的完整性約`束。

二﹕ SQL Server為每個觸發器都建立了兩個專用表﹕Inserted表和Deleted表。這兩個表。

一﹕ 觸發器是一種特殊的預存程序﹐它不能被顯式地調用﹐而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地啟用。所以觸發器可以用來實現對錶實施複雜的完整性約`束。
         
二﹕ SQL Server為每個觸發器都建立了兩個專用表﹕Inserted表和Deleted表。這兩個表由系統來維護﹐它們存在於記憶體中而不是在資料庫中。這兩個表的結構總是與被該觸發器作用的表的結構相同。觸發器執行 完成後﹐與該觸發器相關的這兩個表也被刪除。
Deleted表存放由於執行Delete或Update語句而要從表中刪除的所有行。
Inserted表存放由於執行Insert或Update語句而要向表中插入的所有行。
三﹕Instead of 和 After觸發器
SQL Server2000提供了兩種觸發器﹕Instead of 和After 觸發器。這兩種觸發器的差別在於他們被啟用的同﹕
        
Instead of觸發器用於替代引起觸發器執行的T-SQL語句。除表之外﹐Instead of 觸發器也可以用於視圖﹐用來擴充視圖可以支援的更新操作。
        
After觸發器在一個Insert,Update或Deleted語句之後執行﹐進行約束檢查等動作都在After觸發器被啟用之前發生。After觸發器只能用於表。
        
一個表或視圖的每一個修改動作(insert,update和delete)都可以有一個instead of 觸發器﹐一個表的每個修改動作都可以有多個After觸發器。
四﹕觸發器的執行過程
如果一個Insert﹑update或者delete語句違反了約束﹐那么After觸發器不會執行﹐因為對約束的檢查是在After觸發器被激動之前發生的。所以After觸發器不能超越約束。
       
Instead of 觸發器可以取代激發它的操作來執行。它在Inserted表和Deleted表剛剛建立﹐其它任何操作還沒有發生時被執行。因為Instead of 觸發器在約束之前執行﹐所以它可以對約束進行一些預先處理。

1、定義格式

    CREATE TRIGGER trigger_name

    ON table

    FOR { INSERT | UPDATE | DELETE }

    AS

    [IF UPDATE(column) [{AND | OR} UPDATE(column)…]]

    sql_statement

2、插入視圖和刪除視圖

    為觸發器運行而自動派生的兩個視圖：

    inserted——存放剛插入的新記錄

    deleted——存放剛刪除的舊記錄

3、觸發器分類

    插入類觸發器…insert刪除類觸發器…delete更新類觸發器…update

4、觸發時機

    After|For觸發器是在SQL Server伺服器接到執行SQL語句請求之後，

    先建立臨時的Inserted表和Deleted表，然後實際更改資料，最後才啟用觸發器的。

 

    Instead Of觸發器在SQL Server伺服器接到執行SQL語句請求後，先建立臨時的Inserted表和Deleted表，

    然後就觸發了Instead Of觸發器，至於那個SQL語句是插入資料、更新資料還是刪除資料，一概不管，

    把執行權全權交給了Instead Of觸發器，由它去完成之後的操作。

 

例：建立一個簡單的觸發器。

    CREATE TRIGGER test_trigger

    ON student FOR  INSERT

    AS  PRINT ‘插入了一個元組‘

例：定義一個觸發器，使得當刪除學生記錄時，同時將所有該學生選課記錄刪除

    CREATE TRIGGER del_trigger

    ON STUDENT FOR DELETE

    AS

    DELETE FROM SC

    WHERE SNO = (SELECT SNO FROM deleted)

例：對學生表的插入操作定義一個觸發器，使得當插入記錄時，檢查相應的學生年齡是否滿足條件，如果不存在則顯示錯誤資訊。

    create trigger stu_tri

    on student instead of insert

    as

    if ( select age from inserted )>40

       print ‘不能插入大於歲的學生紀錄‘

    else insert into student select * from inserted

 

遊標

1、需要遊標的資料操作

    當select語句的結果中包含多個元組時，使用遊標可以逐個存取這些元組

    活動集：select語句返回的元組的集合

    當前行：活動集中當前處理的那一行。遊標即是指向當前行的指標。

2、遊標分類

    滾動遊標：遊標的位置可以來回移動，可在活動集中取任意元組。

    非滾動遊標：只能在活動集中順序地取下一個元組。

    更新遊標：資料庫對遊標指向的當前行加鎖，當程式讀下一行資料時，本行資料解鎖，下一行資料加鎖。

3、定義與使用遊標的語句

    declare ：

        declare  遊標名[scroll]  cursor  for select語句[for update [of列表名]]

        定義一個遊標，使之對應一個select語句

       for update任選項，表示該遊標可用於對當前行的修改與刪除

    open

       開啟一個遊標，執行遊標對應的查詢，結果集合為該遊標的活動集

       open  遊標名

    fetch

       在活動集中將遊標移到特定的行，並取出該行資料放到相應的變數中

       fetch [next | prior | first | last | current | relative n | absolute m] 遊標名into  [變數表]

    close

       關閉遊標，釋放活動集及其所佔資源。需要再使用該遊標時，執行open語句

       close  遊標名

    deallocate

       刪除遊標，以後不能再對該遊標執行open語句

       deallocate遊標名

    @@FETCH_STATUS

        返回被FETCH語句執行的最後遊標的狀態.

       0 fetch語句成功

        -1 fetch語句失敗

        -2 被提取的行不存在

 

    例:查詢電子商務系學生資訊,性別為女輸出為female,否則輸出為male？

       declare c1 cursor for select sno,sname,ssex from student where sdept=‘ec‘

       declare @sno char(10),@sname char(10),@ssex char(2)

       Open c1

       Fetch c1 into @sno,@sname,@ssex

       While @@fetch_status==0

       Begin

       if @ssex=‘女‘

           begin  set @ssex=‘female‘ end

       else

           begin set @ssex=‘male‘ end

       Select @sno,@sname ,@ssex

           Fetch c1 into @sno,@sname,@ssex

       end

SQL Server 預存程序、觸發器、遊標