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

存儲過程是SQL語句和可選控制流程語句的預編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數據庫內，可由應用程序通過一個調用執行，而且允許用戶聲明變量、有條件執行以及其它強大的編程功能。存儲過程可包含程序流、邏輯以及對數據庫的查詢。它們可以接受參數、輸出參數、返回單個或多個結果集以及返回值。

存儲過程具有以下優點：

1、可以在單個存儲過程中執行一系列SQL語句。

2、可以從自己的存儲過程內引用其它存儲過程，這可以簡化一系列復雜語句。

3、存儲過程在創建時即在服務器上進行編譯，所以執行起來比單個SQL語句快。

14.1存儲過程的分類(1)系統存儲過程系統存儲過程是由系統提供的存儲過程，可以作為命令執行各種操作。系統存儲過程定義在系統數據庫master中，其前綴是sp_。(2)本地存儲過程本地存儲過程是指在用戶數據庫中創建的存儲過程，這種存儲過程完成用戶指定的數據庫操作，其名稱不能以sp_為前綴。

(3)臨時存儲過程臨時存儲過程屬于本地存儲過程。如果本地存儲過程的名稱前面有一個“#”，該存儲過程就稱為局部臨時存儲過程，這種存儲過程只能在一個用戶會話中使用。

(4)遠程存儲過程遠程存儲過程指從遠程服務器上調用的存儲過程。(5)擴展存儲過程在SQLServer環境之外執行的動態鏈接庫稱為擴展存儲過程，其前綴是sp_。使用時需要先加載到SQLServer系統中，并且按照使用存儲過程的方法執行。14.2用戶存儲過程的創建與執行1.通過SQL命令創建和執行存儲過程步驟如下：(1)定義如下存儲過程USEschoolGOCREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoGO(2)調用存儲過程EXECaGO14.2用戶存儲過程的創建與執行1)創建存儲過程語法格式：CREATEPROC[EDURE]procedure_name[;number]/*定義存儲過程名*/[{@parameterdata_type} /*定義參數的類型*/[VARYING][=default][OUTPUT]

] /*定義參數的屬性*/[,...n] [WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] /*定義存儲過程的處理方式*/[FORREPLICATION]ASsql_statements /*執行的操作*/14.2用戶存儲過程的創建與執行2)存儲過程的執行通過EXEC命令可以執行一個已定義的存儲過程。語法格式：[EXEC[UTE]]

[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]一、設計簡單的存儲過程例14.1利用school數據庫中的school表、score表和course表，編寫一無參存儲過程用于查詢所有學生的選修課程成績記錄。CREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoEXECa二、使用帶有參數的存儲過程例14.2編寫一存儲過程，根據school數據庫的student、score、course三個表查詢指定學生的選修課程成績情況。CREATEPROCEDUREb@snochar(5)ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDstudent.sno=@snoEXECb'101'三、帶有通配符參數的存儲過程例14.3利用shool數據庫中student表、score、course表創建一存儲過程c，查詢指定課程名的選修情況。該存儲過程在參數中使用了模糊查詢，如果沒有提供參數，則使用預設的默認值。CREATEPROCEDUREc@cnamevarchar(16)='%計算機%'ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDcnameLIKE@cnameEXECcEXECc'操作%'EXECc'%計%'四、帶有OUTPUT參數的存儲過程例14.4編寫一存儲過程，統計指定課程選修人數，存儲過程中使用了輸入和輸出參數。CREATEPROCEDUREm@cnamechar(16),@numintoutputASSELECT@num=COUNT(sno)FROMscore,courseWHEREscore.cno=course.cnoANDcnameLIKE@cnameDECLARE@numintEXECm'操作系統',@numoutputSELECT@numAS'選修次數'五、帶有OUTPUT游標參數的存儲過程例14.5在school數據庫的student表上聲明并打開一個游標。CREATEPROCEDUREstudent_cursor@student_curCURSORVARYINGOUTPUTASSET@student_cur=CURSORFORWARD_ONLYSTATICFORSELECTsno,sname,ssex,classFROMstudentOPEN@student_curDECLARE@mycursorCURSOREXECstudent_cursor@student_cur=@mycursorOUTPUTFETCHNEXTFROM@mycursorWHILE@@FETCH_STATUS=0FETCHNEXTFROM@mycursorCLOSE@mycursorDEALLOCATE@mycursor六、使用WITHENCRYPTION選項例14.6創建加密過程（使用sp_helptext系統存儲過程獲取關于加密的存儲過程的信息）。CREATEPROCEDUREencryption_thisWITHENCRYPTIONASSELECT*FROMstudentEXECsp_helptext'encryption_this'七、創建用戶定義的系統存儲過程例14.7創建存儲過程sp_showtable,顯示以student開頭的所有表名及其對應的索引名。如果沒有指定參數，該存儲過程將返回以course開頭的所有表名及對應的索引名。CREATEPROCEDUREsp_showtable@tablevarchar(20)='course%'ASSELECTsysobjects.name,sysindexes.nameFROMsysobjects,sysindexesWHEREsysobjects.id=sysindexes.idANDsysobjects.nameLIKE@tableANDindid<>0ANDindid<>255EXECsp_showtableEXECsp_showtable'student%'14.2用戶存儲過程的創建與執行2.利用企業管理器創建用戶存儲過程(1)在SQLServer

企業管理器的目錄樹中，選中school數據庫文件夾下的存儲過程圖標右擊，出現一快捷菜單，選擇菜單項“新建存儲過程”，進入新建存儲過程窗口，如圖所示；(2)在新建存儲過程窗口輸入定義的存儲過程，然后選擇“確定”按鈕。14.3用戶存儲過程的編輯修改使用ALTERPROCEDURE命令可修改已存在的存儲過程。語法格式：ALTERPROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT]][,...n][WITH

{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}][FORREPLICATION]AS

sql_statements14.4用戶存儲過程的刪除

如果確認一個數據庫的某個