數據庫原理與應用(14)第14章 存儲過程

1、第第14章存儲過程章存儲過程14.1 概概 述述存儲過程可不僅可以提高應用程序的處理能力，降低編寫存儲過程可不僅可以提高應用程序的處理能力，降低編寫數據庫應用程序的難度，同時還可以提高應用程序的效率。歸數據庫應用程序的難度，同時還可以提高應用程序的效率。歸納起來存儲過程具有如下優(yōu)點：納起來存儲過程具有如下優(yōu)點：l 執(zhí)行速度快。執(zhí)行速度快。l 采用模塊化程序設計。采用模塊化程序設計。l 減少網絡通信量。減少網絡通信量。l 保證系統(tǒng)的安全性。保證系統(tǒng)的安全性。SQL Server 2005提供了提供了3種存儲過程，即用戶存儲過程、種存儲過程，即用戶存儲過程、系統(tǒng)存儲過程和擴展存儲過程。系統(tǒng)存儲過程

2、和擴展存儲過程。14.2 創(chuàng)建存儲過程創(chuàng)建存儲過程14.2.1. 使用使用SQL Server管理控制器創(chuàng)建存儲過程管理控制器創(chuàng)建存儲過程通過一個簡單的示例說明使用通過一個簡單的示例說明使用SQL Server管理控制器創(chuàng)管理控制器創(chuàng)建存儲過程的操作步驟。建存儲過程的操作步驟。【例【例14.1】 使用使用SQL Server管理控制器創(chuàng)建存儲過程管理控制器創(chuàng)建存儲過程maxdegree，用于輸出所有學生的最高分。，用于輸出所有學生的最高分。解：解：其操作步驟如下：其操作步驟如下：（1）啟動）啟動SQL Server管理控制器。在管理控制器。在“對象資源管理器對象資源管理器”中展開中展開“LCB

3、-PC”服務器節(jié)點。服務器節(jié)點。（2）展開）展開“數據庫數據庫”|“school”|“存儲過程存儲過程”節(jié)點，單擊節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中選擇鼠標右鍵，在出現的快捷菜單中選擇“新建存儲過程新建存儲過程”命令。命令。（3）出現存儲過程編輯窗口，其中含有一個存儲過程模）出現存儲過程編輯窗口，其中含有一個存儲過程模板，用戶可以參照模板在其中輸入存儲過程的板，用戶可以參照模板在其中輸入存儲過程的T-SQL語句，這語句，這里輸入的語句如下（其中紅字部分為主要輸入的里輸入的語句如下（其中紅字部分為主要輸入的T-SQL語句）：語句）： set ANSI_NULLS ONset QUOTED_I

4、DENTIFIER ONGOCREATE PROCEDURE maxdegreeASBEGINSET NOCOUNT ONSELECT MAX(分數分數) AS 最高分最高分 FROM score /*從從score表中查詢表中查詢最高分最高分*/ENDGO（4）單擊工具欄中的）單擊工具欄中的“執(zhí)行執(zhí)行”按鈕，將其保存在數據庫中。按鈕，將其保存在數據庫中。此時選中此時選中“存儲過程存儲過程”節(jié)點，單擊鼠標右鍵，在出現的快捷菜單節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中選擇中選擇“刷新刷新”命令，會看到命令，會看到“存儲過程存儲過程”的下方出現了的下方出現了maxdegree存儲過程，如圖存儲過程，

5、如圖14.1所示。所示。這樣就完成了這樣就完成了maxdegree存儲過程的創(chuàng)建過程。存儲過程的創(chuàng)建過程。14.2.2 使用使用CREATE PROCEDURE語句創(chuàng)建存儲過程語句創(chuàng)建存儲過程使用使用CREATE PROCEDURE語句的基本語法格式如下：語句的基本語法格式如下：CREATE PROCEDURE 存儲過程名存儲過程名 ; number parameter 數據類型數據類型 = 默認值默認值 OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION FOR REPLICATION AS SQL語句語句 n 【例【例1

6、4.2】 編寫一個程序，創(chuàng)建一個簡單的存儲過程編寫一個程序，創(chuàng)建一個簡單的存儲過程stud_degree，用于檢索所有學生的成績記錄。，用于檢索所有學生的成績記錄。解：解：對應的程序如下：對應的程序如下：USE schoolGO-若存在存儲過程若存在存儲過程stud_degree，則刪除之，則刪除之IF EXISTS(SELECT * FROM sysobjects WHERE name=stud_degree AND type=P) DROP PROCEDURE stud_degreeGO-注意，注意，CREATE PROCEDURE必須是一個批處理必須是一個批處理的第一個語句，故此的第一個

7、語句，故此GO不能缺不能缺-創(chuàng)建存儲過程創(chuàng)建存儲過程stud_degreeCREATE PROCEDURE stud_degree AS SELECT student.學號學號,student.姓名姓名,course.課程名課程名,score.分數分數 FROM student,course,score WHERE student.學號學號=score.學號學號AND course.課程號課程號=score.課程號課程號 ORDER BY student.學號學號GO14.3 執(zhí)行存儲過程執(zhí)行存儲過程可以使用可以使用EXECUTE或或EXEC語句來執(zhí)行存儲在服務器上語句來執(zhí)行存儲在服務器上的存

8、儲過程，其完整語法格式如下：的存儲過程，其完整語法格式如下： EXECUTE return_status = 存儲過程名存儲過程名 ;number | procedure_name_var parameter = 值值 | variable OUTPUT | DEFAULT ,n WITH RECOMPILE 【例【例14.3】 執(zhí)行例執(zhí)行例14.1中創(chuàng)建的存儲過程中創(chuàng)建的存儲過程maxdegree并查并查看輸出的結果。看輸出的結果。解：執(zhí)行解：執(zhí)行maxdegree存儲過程的程序如下：存儲過程的程序如下：USE schoolGOEXEC maxdegreeGO其執(zhí)行結果如圖其執(zhí)行結果如圖14

9、.2所示。從結果看到，查詢的最高分為所示。從結果看到，查詢的最高分為92。 14.4 存儲過程的參數存儲過程的參數14.4.1 在存儲過程中使用參數在存儲過程中使用參數在設計存儲過程時可以帶有參數，這樣增加存儲過程的靈在設計存儲過程時可以帶有參數，這樣增加存儲過程的靈活性。帶參數的存儲過程的一般格式如下：活性。帶參數的存儲過程的一般格式如下：CREATE PROCEDURE 存儲過程名存儲過程名( 參數列表參數列表 )AS SQL語句語句在調用存儲過程時，有兩種傳遞參數的方式。在調用存儲過程時，有兩種傳遞參數的方式。第第1種方式是在傳遞參數時，使傳遞的參數和定義時的參種方式是在傳遞參數時，使傳

10、遞的參數和定義時的參數順序一致。其一般格式如下：數順序一致。其一般格式如下：EXEC 存儲過程名存儲過程名 實參列表實參列表第第2種方式是采用種方式是采用“參數參數=值值”的形式，此時，各個參數的的形式，此時，各個參數的順序可以任意排列。其一般格式如下：順序可以任意排列。其一般格式如下：EXEC 存儲過程名存儲過程名 參數參數1=值值1,參數參數2=值值2,【例【例14.5】 設計一個存儲過程設計一個存儲過程maxno，以學號為參數，輸，以學號為參數，輸出指定學號學生的所有課程中最高分和對應的課程名。出指定學號學生的所有課程中最高分和對應的課程名。解：解：采用采用CREATE PROCEDUR

11、E語句設計該存儲過程如語句設計該存儲過程如下：下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=maxno AND type=P) DROP PROCEDURE maxnoGOCREATE PROCEDURE maxno(no char(10) AS /*聲明聲明no為參數為參數*/ SELECT s.學號學號,s.姓名姓名,c.課程名課程名,sc.分數分數 FROM student s,course c,score sc WHERE s.學號學號=no AND s.學號學號=sc.學號學號AND c.課程號課程號=sc.課

12、程號課程號AND sc.分數分數= (SELECT MAX(分數分數) FROM score WHERE 學號學號=no)GO采用第采用第1種方式執(zhí)行存儲過程種方式執(zhí)行存儲過程maxno的程序如下：的程序如下：USE schoolGOEXEC maxno 103GO采用第采用第2種方式執(zhí)行存儲過程種方式執(zhí)行存儲過程maxno的程序如下：的程序如下：USE schoolGOEXEC maxno no=103GO14.4.2 在存儲過程中使用默認參數在存儲過程中使用默認參數在設計存儲過程時，可以為參數提供一個默認值，默認值必在設計存儲過程時，可以為參數提供一個默認值，默認值必須為常量或者須為常量或

13、者NULL。其一般格式如下：。其一般格式如下：CREATE PROCEDURE 存儲過程名存儲過程名( 參數參數1=默認值默認值1, 參數參數2=默認值默認值2, )AS SQL語句語句在調用存儲過程時，如果不指定對應的實參值，則自動用對在調用存儲過程時，如果不指定對應的實參值，則自動用對應的默認值代替。應的默認值代替?！纠纠?4.6】 設計類似例設計類似例14.5功能的存儲過程功能的存儲過程maxno1，指定，指定其默認學號為其默認學號為101。解：解：設計一個新的存儲過程設計一個新的存儲過程maxno1，對應的程序如下：，對應的程序如下：USE schoolGOIF EXISTS(SEL

14、ECT * FROM sysobjects WHERE name=maxno1 AND type=P) DROP PROCEDURE maxno1GOCREATE PROCEDURE maxno1(no int=101) AS /*聲明聲明no為參數為參數*/ SELECT s.學號學號,s.姓名姓名,c.課程名課程名,sc.分數分數 FROM student s,course c,score sc WHERE s.學號學號=no AND s.學號學號=sc.學號學號AND c.課程號課程號=sc.課程號課程號AND sc.分數分數= (SELECT MAX(分數分數) FROM score

15、WHERE 學號學號=no)GO當不指定實參調用當不指定實參調用maxno1存儲過程時，其結果如圖存儲過程時，其結果如圖14.5所示。所示。 當指定實參為當指定實參為105調用調用maxno1存儲過程時，其結果如圖存儲過程時，其結果如圖14.6所示。所示。結論：從執(zhí)行結果可以看到，結論：從執(zhí)行結果可以看到，當調用存儲過程時，沒有指當調用存儲過程時，沒有指定參數值時就自動使用相應定參數值時就自動使用相應的默認值。的默認值。14.4.3 在存儲過程中使用返回參數在存儲過程中使用返回參數在創(chuàng)建存儲過程時，可以定義返回參數。在執(zhí)行存儲過在創(chuàng)建存儲過程時，可以定義返回參數。在執(zhí)行存儲過程時，可以將結果返

16、回給返回參數。返回參數應用程時，可以將結果返回給返回參數。返回參數應用OUTPUT進行說明。進行說明?！纠纠?4.7】 創(chuàng)建一個存儲過程創(chuàng)建一個存儲過程average，它返回兩個參數，它返回兩個參數st_name和和st_avg，分別代表了姓名和平均分。并編寫，分別代表了姓名和平均分。并編寫T-SQL語句執(zhí)行該存儲過程和查看輸出的結果。語句執(zhí)行該存儲過程和查看輸出的結果。解：解：建立存儲過程建立存儲過程average的程序如下：的程序如下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=average AND type=P)

17、 DROP PROCEDURE averageGOCREATE PROCEDURE average(st_no int,st_name char(8) OUTPUT,/*返回參數返回參數*/st_avg float OUTPUT/*返回參數返回參數*/) AS SELECT st_name=student.姓名姓名,st_avg=AVG(score.分數分數) FROM student,score WHERE student.學號學號=score.學號學號 GROUP BY student.學號學號,student.姓名姓名 HAVING student.學號學號=st_noGO執(zhí)行該存儲過程

18、，來查詢學號為執(zhí)行該存儲過程，來查詢學號為“105”的學生姓名和平均分：的學生姓名和平均分：DECLARE st_name char(10)DECLARE st_avg floatEXEC average 105,st_name OUTPUT,st_avg OUTPUTSELECT 姓名姓名=st_name,平均分平均分=st_avgGO4. 存儲過程的返回值存儲過程的返回值存儲過程在執(zhí)行后都會返回一個整型值（稱為存儲過程在執(zhí)行后都會返回一個整型值（稱為“返回代返回代碼碼”），指示存儲過程的執(zhí)行狀態(tài)。），指示存儲過程的執(zhí)行狀態(tài)。如果執(zhí)行成功，返回如果執(zhí)行成功，返回0；否則返回；否則返回-1-9

19、9之間的數值之間的數值（例如（例如-1表示找不到對象，表示找不到對象，-2表示數據類型錯誤，表示數據類型錯誤，-5表示語表示語法錯誤等）。也可以使用法錯誤等）。也可以使用RETURN語句來指定一個返回值。語句來指定一個返回值?！纠纠?4.9】 編寫一個程序，創(chuàng)建存儲過程編寫一個程序，創(chuàng)建存儲過程test_ret，根據，根據輸入的參數來判斷返回值。并執(zhí)行該存儲過程和查看輸出的輸入的參數來判斷返回值。并執(zhí)行該存儲過程和查看輸出的結果。結果。解：解：建立存儲過程建立存儲過程test_ret如下：如下：USE testGOIF EXISTS(SELECT * FROM sysobjects WHER

20、E name=test_ret AND type=P) DROP PROCEDURE test_retGOCREATE PROC test_ret(input_int int = 0) AS/*指定默認參數值指定默認參數值*/ IF input_int=0 RETURN 0 -如果輸入的參數等于如果輸入的參數等于0，則返回，則返回0 IF input_int0 RETURN 1000 -如果輸入的參數大于如果輸入的參數大于0，則返回，則返回1000 IF input_int0 RETURN -1000 -如果輸入的參數小于如果輸入的參數小于0，則返回，則返回-1000GO執(zhí)行該存儲過程：執(zhí)行該

21、存儲過程：USE TestDECLARE ret_int intEXEC ret_int=test_ret 1PRINT 返回值返回值PRINT -PRINT ret_intEXEC ret_int=test_ret 0PRINT ret_intEXEC ret_int=test_ret -1PRINT ret_int14.5 存儲過程的管理存儲過程的管理14.5.1 查看存儲過程查看存儲過程在創(chuàng)建存儲過程后，它的名稱就存儲在系統(tǒng)表在創(chuàng)建存儲過程后，它的名稱就存儲在系統(tǒng)表sysobjects中，中，它的源代碼存放在系統(tǒng)表它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂弥小？梢允褂肧QL

22、 Server管理控制器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。管理控制器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。1. 使用使用SQL Server管理控制器查看存儲過程管理控制器查看存儲過程通過一個例子說明使用通過一個例子說明使用SQL Server管理控制器查看存儲過管理控制器查看存儲過程的操作步驟。程的操作步驟?！纠纠?4.10】 使用使用SQL Server管理控制器查看例管理控制器查看例14.8所創(chuàng)建所創(chuàng)建的存儲過程的存儲過程stud1_degree。解：解：其操作步驟如下：其操作步驟如下：（1）啟動）啟動SQL Server管理控制器。在管理控制器。在“對象資源管理器對象資源管理

23、器”中展開中展開“LCB-PC”服務器節(jié)點。服務器節(jié)點。（2）展開）展開“數據庫數據庫”|“school”|“可編程性可編程性”|“存儲過存儲過程程”|“dbo.stud1_degree”節(jié)點，單擊鼠標右鍵，在出現的快捷節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中選擇菜單中選擇“編寫存儲過程腳本為編寫存儲過程腳本為(S)|CREATE到到(C)|新查詢編新查詢編輯器窗口輯器窗口”命令。命令。（3）在右邊的編輯器窗口中出現存儲過程）在右邊的編輯器窗口中出現存儲過程stud_degree源源代碼，如圖代碼，如圖14.9所示。此時用戶只能查看其代碼。所示。此時用戶只能查看其代碼。2. 使用系統(tǒng)存儲過程來查

24、看存儲過程使用系統(tǒng)存儲過程來查看存儲過程SQL Server 2005提供了如下系統(tǒng)存儲過程用于查看用戶提供了如下系統(tǒng)存儲過程用于查看用戶創(chuàng)建的存儲過程。創(chuàng)建的存儲過程。（1）sp_help用于顯示存儲過程的參數及其數據類型，其語法如下：用于顯示存儲過程的參數及其數據類型，其語法如下：sp_help objname= name其中，參數其中，參數name為要查看的存儲過程的名稱。為要查看的存儲過程的名稱。（2）sp_helptext用于顯示存儲過程的源代碼，其語法如下：用于顯示存儲過程的源代碼，其語法如下：sp_helptext objname= name其中，參數其中，參數name為要查看的

25、存儲過程的名稱。為要查看的存儲過程的名稱。（3）sp_depends用于顯示和存儲過程相關的數據庫對象，其語法如下：用于顯示和存儲過程相關的數據庫對象，其語法如下：sp_depends objname=object其中，參數其中，參數object為要查看依賴關系的存儲過程的名稱。為要查看依賴關系的存儲過程的名稱。（4）sp_stored_procedures用于返回當前數據庫中的存儲過程列表，其語法如下：用于返回當前數據庫中的存儲過程列表，其語法如下：sp_stored_procedure sp_name= name ,sp_owner=owner ,sp_qualifier= qualifi

26、er【例【例14.11】 使用相關系統(tǒng)存儲過程查看例使用相關系統(tǒng)存儲過程查看例14.2所創(chuàng)建的存所創(chuàng)建的存儲過程儲過程stud_degree的相關內容。的相關內容。解：解：對應的程序如下：對應的程序如下：USE schoolGOEXEC sp_help stud_degreeEXEC sp_helptext stud_degreeEXEC sp_depends stud_degree14.5.2 修改存儲過程修改存儲過程在創(chuàng)建存儲過程之后，用戶可以對其進行修改?？梢允褂迷趧?chuàng)建存儲過程之后，用戶可以對其進行修改?？梢允褂肧QL Server管理控制器或使用管理控制器或使用ALTER PROCED

27、URE語句修改用戶創(chuàng)建語句修改用戶創(chuàng)建的存儲過程。的存儲過程。1. 使用使用SQL Server管理控制器修改存儲過程管理控制器修改存儲過程通過一個例子說明使用通過一個例子說明使用SQL Server管理控制器修改存儲過程的管理控制器修改存儲過程的操作步驟。操作步驟?！纠纠?4.12】 使用使用SQL Server管理控制器修改例管理控制器修改例14.2所創(chuàng)建的存所創(chuàng)建的存儲過程儲過程stud_degree。解：解：其操作步驟如下：其操作步驟如下：（1）啟動）啟動SQL Server管理控制器。在管理控制器。在“對象資源管理器對象資源管理器”中中展開展開“LCB-PC”服務器節(jié)點。服務器節(jié)點

28、。（2）展開）展開“數據庫數據庫”|“school”|“可編程性可編程性”|“存儲過存儲過程程”|“dbo.stud_degree”節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中選擇選擇“修改修改”命令。命令。（3）此時右邊的編輯器窗口出現）此時右邊的編輯器窗口出現stud_degree存儲過程的存儲過程的源代碼（將源代碼（將“CREATE PROCEDURE”改為改為“ALTER PROCEDURE”），如圖），如圖14.11所示，用戶可以直接進行修改。所示，用戶可以直接進行修改。修改完畢，單擊工具欄中的修改完畢，單擊工具欄中的“執(zhí)行執(zhí)行”按鈕執(zhí)行該存儲過程，按

29、鈕執(zhí)行該存儲過程，從而達到修改的目的。從而達到修改的目的。2. 使用使用ALTER PROCEDURE語句修改存儲過程語句修改存儲過程使用使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行語句可以更改先前通過執(zhí)行CREATE PROCEDURE語句創(chuàng)建的過程，但不會更改權限，語句創(chuàng)建的過程，但不會更改權限，也不影響相關的存儲過程或觸發(fā)器，其語法形式如下：也不影響相關的存儲過程或觸發(fā)器，其語法形式如下：ALTER PROCEDURE 存儲過程名存儲過程名參數列表參數列表 AS SQL語句語句當使用當使用ALTER PROCEDURE語句時，如果在語句時，如果在CREATE PROCEDU

30、RE語句中使用過參數，那么在語句中使用過參數，那么在ALTER PROCEDURE語句中也應該使用這些參數。每次只能修改一個語句中也應該使用這些參數。每次只能修改一個存儲過程。存儲過程。【例例14.13】 編寫一個程序，先創(chuàng)建一個存儲過程編寫一個程序，先創(chuàng)建一個存儲過程studproc，輸出，輸出“1031”班的所有學生，利用班的所有學生，利用sysobjects和和syscomments兩個系統(tǒng)表輸出該存儲過程的兩個系統(tǒng)表輸出該存儲過程的id和和text列。然后利列。然后利用用ALTER PROCEDURE語句修改該存儲過程，將其改為加密語句修改該存儲過程，將其改為加密方式，最后再輸出該存儲

31、過程的方式，最后再輸出該存儲過程的id和和text列。列。解：解：創(chuàng)建存儲過程創(chuàng)建存儲過程studproc的語句如下：的語句如下：USE schoolGOIF EXISTS(SELECT * FROM sysobjects WHERE name=studproc AND type=P) DROP PROCEDURE studprocGOCREATE PROCEDURE studproc AS SELECT * FROM student WHERE 班號班號=1031GO通過以下語句輸出通過以下語句輸出studproc存儲過程的存儲過程的id和和text列：列：SELECT sysobjects

32、.id,syscomments.textFROM sysobjects,syscommentsWHERE =studproc AND sysobjects.type=P AND sysobjects.id=syscomments.id其執(zhí)行結果如圖其執(zhí)行結果如圖14.12所示。所示。 修改該存儲過程的語句如下：修改該存儲過程的語句如下：USE schoolGOALTER PROCEDURE studproc WITH ENCRYPTION AS SELECT * FROM student WHERE 班號班號=1031GO再次執(zhí)行前面的輸出再次執(zhí)行前面的輸出stu

33、dproc存儲過程的存儲過程的id和和text列的語句，列的語句，其執(zhí)行結果如圖其執(zhí)行結果如圖14.13所示。從中看到，加密過的存儲過程查詢所示。從中看到，加密過的存儲過程查詢出的源代碼是空值，從而起到保護源程序的作用。出的源代碼是空值，從而起到保護源程序的作用。14.5.3 重命名存儲過程重命名存儲過程重命名存儲過程也有兩種方法：使用重命名存儲過程也有兩種方法：使用SQL Server管理控制管理控制器或使用系統(tǒng)存儲過程。器或使用系統(tǒng)存儲過程。1. 使用使用SQL Server管理控制器重命名存儲過程管理控制器重命名存儲過程通過一個例子說明使用通過一個例子說明使用SQL Server管理控制

34、器重命名存儲管理控制器重命名存儲過程的操作步驟。過程的操作步驟。【例【例14.14】 使用使用SQL Server管理控制器將存儲過程管理控制器將存儲過程studproc重命令為重命令為studproc1。解：解：其操作步驟如下：其操作步驟如下：（1）啟動）啟動SQL Server管理控制器。在管理控制器。在“對象資源管理器對象資源管理器”中展開中展開“LCB-PC”服務器節(jié)點。服務器節(jié)點。（2）展開）展開“數據庫數據庫”|“school”|“可編程性可編程性”|“存儲過存儲過程程”|“dbo.studproc”節(jié)點，單擊鼠標右鍵，在出現的快捷菜單節(jié)點，單擊鼠標右鍵，在出現的快捷菜單中選擇中選擇“重命名重命名”命令。命令。（3）此時存儲過程名稱）此時存儲過程名稱“studproc”變成可編輯的，可以變成可編輯的，可以直接修改該存儲過程的名稱