1、SQL Server 2005數據庫管理系統(tǒng)第十六講 存儲過程16.1 概述16.2 創(chuàng)建存儲過程16.3 查看存儲過程 16.4 修改存儲過程16.5 執(zhí)行存儲過程16.6 刪除存儲過程16.7 存儲過程及其參數15. 1 概述 存儲過程的概念 SQL Server的存儲過程類似于編程語言中的過程。在使用Transact-SQL語言編程過程中，可將某些需要多次調用的實現某個特定任務的代碼段編寫成一個過程，將其保存在數據庫中，并由SQL Server服務器通過過程名來調用它們，這些過程就叫做存儲過程。 存儲過程在創(chuàng)建時就被編譯和優(yōu)化，調用一次以后，相關信息就保存在內存中，下次調用時可以直接執(zhí)行

2、。 存儲過程的優(yōu)點 1、實現了模塊化編程 存儲過程經過了一次創(chuàng)建以后，可以被調用無數次。用戶可以獨立于應用程序而對存儲過程進行修改?？砂椽毺氐墓δ苣Ｊ皆O計不同的存儲過程以供使用。存儲過程的優(yōu)點 2、存儲過程具有對數據庫立即訪問的功能 存儲過程作為一個獨立的數據庫對象，可以作為一個單元被用戶的應用程序調用。由于存儲過程是已經編譯好的代碼，所以執(zhí)行時不必再次進行編譯，從而提高了程序的運行效率。 存儲過程的優(yōu)點 3、使用存儲過程可以加快程序的運行速度 存儲過程在創(chuàng)建時就經過了語法檢查和性能優(yōu)化，因此在執(zhí)行時不必再重復這些步驟。存儲過程在經過第一次調用之后，就駐留在內存中，不必再經過編譯和優(yōu)化，所以執(zhí)

3、行速度快。在有大量批處理的Transact-SQL語句要重復執(zhí)行的時候，使用存儲過程可以極大提高運行效率。存儲過程的優(yōu)點 4、使用存儲過程可以減少網絡流量 存儲過程中可以包含大量的Transact-SQL語句，但存儲過程作為一個獨立的單元來使用。在進行調用時，只需要使用一個語句就可以實現，所以大大減少了網絡上數據的傳輸。存儲過程的優(yōu)點 5、使用存儲過程可以提高數據庫的安全性 可以設置用戶通過存儲過程對某些關鍵數據進行訪問，但不允許用戶直接使用T-SQL或企業(yè)管理器對數據進行訪問。存儲過程的分類系統(tǒng)存儲過程 用戶自定義存儲過程 15. 2 創(chuàng)建存儲過程 1、使用企業(yè)管理器創(chuàng)建存儲過程 在SQL

4、Server企業(yè)管理器中，選擇指定的服務器和數據庫，右擊要創(chuàng)建存儲過程的數據庫，在彈出的快捷菜單中依次選擇“新建|存儲過程”選項。2、使用Transact-SQL語句創(chuàng)建存儲過程 命令格式:create proc edure procedure_name ;number parameter data_type varying =dafault output with encryption as sql_statement n procedure_name: 新存儲過程的名稱. number: 可選的整數,用來對同名的過程分組,以便用一條drop procedure語句即可將同組的過程一起除去.

5、 parameter: 過程中的參數.存儲過程最多可以有2100個參數。 data_type: 參數的數據類型. varying: 指定作為輸出參數支持的結果集(由存儲過程動態(tài)構造,內容可以變化).default: 參數的默認值. output: 表明參數是返回參數. n: 表示最多可以指定2100個參數的占位符. as: 指定過程要執(zhí)行的操作. sql_statement: 過程中要包含的任意數目和類型的Transact-SQl 語句,但有一些限制. as sql_statement: n : 其中的n是表示此過程可以包含多條Transact-SQL語句的占位符.例：use 學生管理數據庫G

6、oif exists (select name from sysobjectswhere name = my_proc1 and type = p)drop procedure my_proc1 Gocreate proc my_proc1 as select 學號,姓名,性別,年齡,所在院系 from 學生表 where 所在院系=計算機and 性別=女15. 2 創(chuàng)建存儲過程3、 使用向導創(chuàng)建存儲過程 在企業(yè)管理器中，選中某個SQL Server服務器，選擇要創(chuàng)建存儲過程的數據庫，選擇“工具”菜單中的“向導”菜單項，系統(tǒng)會彈出“選擇向導”對話框，單擊向導中“數據庫”選項左邊的加號，選中“創(chuàng)

7、建存儲過程向導”選項。 15. 3 查看存儲過程 1、使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程（1）從樹型結構上選中存儲過程所在的數據庫節(jié)點。（2）選中數據庫節(jié)點下的存儲過程節(jié)點（3）右擊存儲過程，選擇快捷菜單上的“屬性”命令。15. 3 查看存儲過程2、使用T-SQL語句查看存儲過程（1）sp_helptext：用于顯示存儲過程的源代碼。 sp_helptext objname= name 參數name為要查看的存儲過程的名稱。 例如： Use 學生課程數據庫 go sp_helptext my_proc go15. 3 查看存儲過程（2）sp_help：用于顯示存儲過程的一般信息（參數及其數據

8、類型） sp_help objname= name 參數name為要查看的存儲過程的名稱。 例如： Use 學生課程數據庫 go sp_help my_proc go15. 3 查看存儲過程（3）如果要知道某個表被存儲過程引用的情況，則可以使用sp_depends語句查看。 例如： sp_depends lwqk15. 3 查看存儲過程（4）sp_depends：用于顯示和存儲過程相關的數據庫對象 sp_depends objname=object 參數object為要查看依賴關系的存儲過程的名稱。 例如： sp_depends my_proc15. 3 查看存儲過程（5） 返回當前數據庫中的

9、存儲過程列表 Execs sp_stored_procedures 命令格式: alter procedure procedure_name ; number parameter data_type varying =dafault output with encryption as sql_statement n 15.4 修改存儲過程例： use 學生課程數據庫 go alter proc my_proc as select 學號,姓名,性別,年齡,所在院系 from 學生表 where 所在院系=計算機15.4 修改存儲過程 存儲過程創(chuàng)建成功后，保存在數據庫中。在SQL Server中可

10、以使用EXECUTE命令來直接執(zhí)行存儲過程。 execute return_status = procedure_name ; number | procedure_name_var parameter = value | variable output | default , n 15.5 執(zhí)行存儲過程 return_status: 一個可選的整形變量,保存存儲過程的返回狀態(tài)。 procedure_name: 擬調用的存儲過程完全合法或者不完全合法的名稱。 number: 可選的整數,用于將相同的名稱的過程進行組合,使得它們可以用一句drop procedure語句除去。 procedure

11、_name_var: 局部定義變量名,代表存儲過程的名稱。parameter: 過程的參數,在create procedure 語句中定義. value: 過程中參數的值. variable: 用來保存參數或者返回參數的變量. output: 指定存儲過程必須返回一個參數. default: 根據過程的定義提供參數的默認值. n: 占位符,表示在它前面的項目可以多次重復執(zhí)行. 如果對存儲過程的調用是批處理的第一條語句，則可以直接使用存儲過程的名字調用該存儲過程。例如： use lwzz go my_proc1 go 如果批處理中對存儲過程的調用不是第一個語句，應該使用EXECUTE或EXEC關

12、鍵字： use lwzz exec my_proc1 go15. 6 重命名存儲過程 1 、 重命名存儲過程 1）使用企業(yè)管理器修改存儲過程名稱 2）使用系統(tǒng)存儲過程修改存儲過程名稱 語法形式： sp_rename 原存儲過程名稱，新存儲過程名稱15. 7 刪除存儲過程1、使用企業(yè)管理器刪除存儲過程（ 1）從樹型結構上選中存儲過程所在的數據庫節(jié)點。（2）選中數據庫節(jié)點下的存儲過程節(jié)點。（3）選中要刪除的存儲過程，右擊鼠標，在彈出的快捷菜單中選擇“刪除”命令。（4）選擇“全部除去”按鈕即完成操作。15. 7 刪除存儲過程2、使用Transact-SQL語句刪除存儲過程 drop procedur

13、e 例： use 學生課程數據庫 go drop procedure my_proc輸入參數: 輸入參數允許調用程序向存儲過程傳送數據值。輸出參數: 輸出參數允許存儲過程將數據值或指針變量傳回調用程序。15. 8 存儲過程及其參數1、輸入參數： use 學生課程數據庫 go create procedure my_procss ( sex char(2) ) as select * from 學生表 where 性別=sex return go execute my_procss 女 go execute my_procss男 2、輸出參數： use 學生課程數據庫 go create pro

14、cedure my_procname ( name varchar(8) =null, sex char (2) output, age tinyint output ) as if name=null begin print 查找哪位同學的年齡? return end select age=年齡, sex =性別 from 學生表 where 姓名=name return use 學生課程數據庫 go declare ages tinyint,sexs char(2) execute my_procname 沈香娜, sexs output ,ages output select ages

15、as 年齡,sexs as性別 在執(zhí)行存儲過程時,參數既可以由位置標識,也可以由名字標識。 create procedure max_int (a int,b int,c int, maxabc int output ) as if (a=b) and (a=c) select maxabc=a else if (b=a) and (b=c) select maxabc=b else select maxabc=c return15. 8 關于參數順序的問題declare maxs intexecute max_int 2,9,7, maxs output select maxs或者:declare maxs intexecute max_int a= 2, c= 7, b= 9, maxs outputselect maxs15. 8 關于參數順序的問題15. 9存儲過程的返回值Create proc Test_ret ( intput _int int =0 )If intput _int 0 return 1If intput _int 0 return -1If intput _int =0 return 0Go GoDeclare Ret_int intExec Ret_int= Test_ret-50Select返回值=Ret