预存程序.ppt

第十二章預存程序與觸發程序(Stored Procedures and Trigger)  Chapter 12 Outline 12-1 建立預存程序 12-2 預存程序的修改 12-3 建立傳遞參數的預存程序 12-4 使用RECOMPILE與ENCRYPTION 的預存程序 12-5 使用預存程序的注意事項 12-6 建立觸發程序 12-7 遞迴式觸發程序 12-8 INSERTED與DELETED表格 12-9 使用DDL觸發程序 12-10 本章總結 12-1 建立預存程序 預存程序是一個利用SQL敘述所撰寫的程式，經過事先的編譯之後，儲存在資料庫中，是屬於伺服器端的程式 。 經常使用的SQL查詢建立成預存程序就可以不用每次都透過網路來傳送、資料庫系統分析SQL語、評估執行計畫之後，才查詢而送回結果集(Result Set) 。 預存程序有兩種基本的型態 第一種是存取資料，例如：SELECT的查詢 第二種型態是不傳回結果集，只在資料庫端做某些動作，例如：DELETE的敘述等 12-1 建立預存程序 建立預存程序的語法結構： CREATE PROC[EDURE] [schema_name.] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default]  [OUTPUT] ] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [FOR REPLICATION] AS sql_statements 12-1 建立預存程序 建立步驟建議如下 : 撰寫SQL敘述 測試所撰寫的SQL敘述 如果所得的結果與您預期的相同，則建立此預存程序 執行這個預存程序 12-1 建立預存程序 例如：想要建立一個統計客戶個數的預存程序 12-1 建立預存程序 執行上面第一段SQL敘述即可建立預存程序 第二段為執行預存程序的方式 12-2 預存程序的修改 修改預存程序的語法與建立時幾乎相同只有將關鍵字CREATE改成ALTER，如下： ALTER PROC[EDURE] [schema_name.] procedure_name [;number] … 12-2 預存程序的修改 12-3 建立傳遞參數的預存程序 撰寫接受參數的預存程序之基本語法結構 : CREATE PROCE[DURE] [schema_name.] procedure_name @parameter_name1 datatype, @paremeter_name2 datatype ,… @paremeter_result datatype OUTPUT AS sql_statements parameter_name1、parameter_name2…等是傳入的參數 OUTPUT是規劃傳回的參數 datatype是宣告參數的資料型態 12-3 建立傳遞參數的預存程序 執行時傳遞的方法可使用參數宣告的順序 : EXEC proc_name [value[,value]…] 使用與參數名稱相對應的方法： EXEC proc_name [@parameter_name2=value2][OUTPUT] [@parameter _name1=value1] … 12-3 建立傳遞參數的預存程序 範例12-3.1 :建立一個「以客戶名稱來查詢其相關資料」的預存程序 create proc dis_name @pname varchar(20) --規劃傳入參數名稱與資料型態 as --以下為預存程序的T-SQL select * from customers where cust_name like %+@cname+%‘ 12-3 建立傳遞參數的預存程序 執行的兩種方法如下： exec cust_name ‘資訊’ exec cust_name @panme=‘資訊’ 12-3 建立傳遞參數的預存程序 範例12-3.2：建立一個可以傳出參數的預存程序 create procedure cust_num @cust_num int, --傳入的參數 @cust_name char(20) OUTPUT --傳出的參數 as select @cust_name=cust_name from custo