1 第9章存儲過程和事務 9 1存儲過程概述9 2創建管理簡單存儲過程9 3創建管理帶參的存儲過程9 4事務 2 9 1存儲過程概述 1 存儲過程概念存儲過程是為了實現某個特定任務 由一組預先編譯好的SQL語句組成 將其放在服務器上 由用戶通過指定存儲過程的名字來執行的一種數據庫對象 2 存儲過程類型系統存儲過程 以SP 為前綴 是由SQLServer2005自己創建 管理和使用的一種特殊的存儲過程 不能對其進行修改或刪除 如sp helpdb sp renamedb等擴展存儲過程 以XP 為前綴 SQLSERVER的實例可以動態加載和運行的DLL 直接在實例地址空間中運行 可以使用SQLSERVER擴展存儲過程API完成編程 如xp servicecontrol 停止或啟動某個服務 用戶自定義存儲過程 由用戶自行創建的存儲過程 可以輸入參數 向客戶端返回表格或結果 消息等 也可以返回輸出函數 3 9 2創建管理簡單的存儲過程 9 2 1無參存儲過程的創建9 2 2無參存儲過程的執行9 2 3查看存儲過程9 2 4修改存儲過程9 2 5編譯存儲過程9 2 6刪除存儲過程 4 9 2 1無參存儲過程的創建 使用SQL語句創建存儲過程1 語法格式如下 CREATEPROC EDURE procedure nameASsql statement n 2 語法注釋 procedure name 新建存儲過程的名稱 其名稱必須符合標識符命名規則 且對于數據庫及其所有者必須唯一 sql statement 指存儲過程中的任意數目和類型的Transact SQL語句 5 9 2 1無參存儲過程的創建 例1 在PUBS數據庫中 創建一個名稱為pr searchorddate的存儲過程 該存儲過程將查詢出sales表中訂購日期ord date在1994年以后的記錄信息 代碼如下 CREATEPROCpr searchorddateASSELECT FROMsalesWHEREord date 1994 1 1 GO 6 創建存儲過程的注意事項 只能在當前數據庫中創建存儲過程 數據庫的所有者可以創建存儲過程 也可以授權其他用戶創建存儲過程 存儲過程是數據庫對象 其名稱必須遵守標識符命名規則 不能將CREATEPROCEDURE語句與其它SQL語句組合到單個批處理中 7 9 2創建管理簡單的存儲過程 9 2 1無參存儲過程的創建9 2 2無參存儲過程的執行9 2 3查看存儲過程9 2 4修改存儲過程9 2 5編譯存儲過程9 2 6刪除存儲過程 8 9 2 2無參存儲過程的執行 對存儲在服務器上的存儲過程 可以使用EXECUTE命令或其名稱執行它 其語法格式如下 EXEC UTE procedure name對上例的存儲過程pr searchorddate的執行語句如下 EXECUTEpr searchorddate 9 9 2創建管理簡單的存儲過程 9 2 1無參存儲過程的創建9 2 2無參存儲過程的執行9 2 3查看存儲過程9 2 4修改存儲過程9 2 5編譯存儲過程9 2 6刪除存儲過程 10 9 2 3查看存儲過程 1 使用對象資源管理器查看存儲過程2 使用系統存儲過程查看存儲過程信息在SQLServer中 可以使用sp helptext sp depends sp help等系統存儲過程來查看存儲過程的不同信息 1 使用sp helptext查看存儲過程的文本信息 其語法格式為 sp helptext存儲過程名2 使用sp depends查看存儲過程的相關性 其語法格式為 sp depends存儲過程名3 使用sp help查看存儲過程的一般信息 其語法格式為 sp help存儲過程名 11 9 2 4修改存儲過程 修改存儲過程語法格式為 ALTERPROC DURE procedure name parameterdata type default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION ASSql statement 12 9 2 4修改存儲過程 例14 修改存儲過程pr searchorddate 查詢出sales表中訂購日期在93年以后的訂單 其程序清單如下 AlterPROCpr searchorddateASSELECT FROMsalesWHEREord date 1993 1 1 GO 13 9 2 5編譯存儲過程 在我們使用了一次存儲過程后 可能會因為某些原因 必須向表中新增加數據列或者為表新添加索引 從而改變了數據庫的邏輯結構 這時 需要對存儲過程進行重新編譯 SQLServer提供三種重新編譯存儲過程的方法 1 在建立存儲過程時設定重新編譯語法格式 CREATEPROCEDUREprocedure nameWITHRECOMPILEASsql statement2 在執行存儲過程時設定重編譯語法格式 EXECUTEprocedure nameWITHRECOMPILE3 通過使用系統存儲過程設定重編譯語法格式為 EXECsp recompileOBJECT 14 第9章存儲過程 9 1存儲過程概述9 2創建管理簡單存儲過程9 3創建管理帶參的存儲過程9 4其他存儲過程 15 9 3創建管理帶參的存儲過程 9 3 1創建帶參存儲過程的語法9 3 2創建 執行帶輸入參數的存儲過程9 3 3創建 執行帶輸出參數的存儲過程9 3 4存儲過程的返回值 16 9 3 1創建帶參存儲過程的語法 1 語法格式如下 CREATEPROC DURE procedure name parameterdata type default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION ASSql statement 17 9 3 1創建帶參存儲過程的語法 2 語法注釋 parameter 存儲過程中的輸入和輸出參數 data type 參數的數據類型 OUTPUT 表明參數是返回參數 該選項的值可以返回給EXEC UTE 18 9 3創建管理帶參的存儲過程 9 3 1創建帶參存儲過程的語法9 3 2創建 執行帶輸入參數的存儲過程9 3 3創建 執行帶輸出參數的存儲過程9 3 4存儲過程的返回值 19 9 3 2創建 執行帶輸入參數的存儲過程 例2 在PUBS數據庫中創建一個存儲過程pr searchempl 查詢出authors表中state字段為某個州且姓中包含某字符串的所有的員工信息 CREATEPROCpr searchempl statechar 2 strvarchar 40 AsSelect FromauthorsWherestate stateandau lnamelike str 20 9 3 2創建 執行帶輸入參數的存儲過程 語法格式如下 EXEC UTE return status procedure name parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 對上例的存儲過程pr searchempl的執行語句如下 EXECUTEpr searchempl CA hi 21 9 3 2創建 執行帶輸入參數的存儲過程 例3 查詢出northwind數據庫中城市值為某某值并且雇傭時間在某某日期之后的所有員工的基本信息 實現的T SQL語句 USEnorthwindGOCREATEPROCp getEmployee citynvarchar 15 hiredatedatetimeASSELECT FROMemployeesWHEREcity cityANDhiredate hiredateGO 22 9 3 2創建 執行帶輸入參數的存儲過程 執行帶輸入參數的存儲過程按位置傳遞參數值在執行存儲過程的語句中 直接給出參數的值 當有多個參數時 給出的參數的順序與創建執行存儲過程的語句中的參數的順序一致 即參數傳遞的順序就是參數定義的順序 通過參數名傳遞參數值在執行存儲過程的語句中 使用 參數名 參數值 的形式給出參數值 其優點是參數可以以任意順序給出 23 9 3 2創建 執行帶輸入參數的存儲過程 在輸入參數中使用默認值在執行存儲過程p getEmployee時 如果沒有指定參數 則系統運行就會出錯 此時如果希望在執行時不給出參數也能正確運行 則在創建存儲過程時給輸入參數指定默認值 24 9 3 2創建 執行帶輸入參數的存儲過程 按位置傳遞參數值EXECp getEmployee london 1994 1 1 通過參數名傳遞參數值EXECp getEmployee city london hiredate 1994 1 1 或EXECp getEmployee hiredate 1994 1 1 city london 25 9 3 2創建 執行帶輸入參數的存儲過程 例4 對存儲過程p getEmployee進行修改 指定城市默認值為 london 指定雇傭日期為1990年1月1日 USEnorthwindGOALTERPROCp getEmployee citynvarchar 15 london hiredatedatetime 1990 1 1 ASSELECT FROMemployeesWHEREcity cityANDhiredate hiredateGOEXECp getEmployee 26 9 3創建管理帶參的存儲過程 9 3 1創建帶參存儲過程的語法9 3 2創建 執行帶輸入參數的存儲過程9 3 3創建 執行帶輸出參數的存儲過程9 3 4存儲過程的返回值 27 9 3 3創建 執行帶輸出參數的存儲過程 例5 在PUBS數據庫中創建一個存儲過程pr titleprice 統計出titles表中pub id字段為某編號的書籍總價格 CREATEPROCpr titleprice pub idchar 4 spricemoneyoutputAsSelect sprice sum price FromtitlesWherepub id pub id存儲過程的執行 declare ssmoneyexecpr titleprice 0877 ssoutputselect ssas總價格 28 9 3 3創建 執行帶輸出參數的存儲過程 例6 創建一個存儲過程p getCountEmployees 用于統計nothwind數據庫員工表中雇傭日期在某某時間之后的員工的個數 實現的T SQL語句 USEnorthwindGOCREATEPROCp getCountEmployees hiredatedatetime 1990 1 1 countintOUTPUTASSELECT count count FROMemployeesWHEREhiredate hiredateGO 29 9 3 3創建 執行帶輸出參數的存儲過程 調用存儲過程的T SQL語句 DECLARE ecountintEXECp getCountEmployees 1993 1 1 ecountOUTPUTSELECT 員工個數為 str ecount GO 30 9 3創建管理帶參的存儲過程 9 3 1創建帶參存儲過程的語法9 3 2創建 執行帶輸入參數的存儲過程9 3 3創建 執行帶輸出參數的存儲過程9 3 4存儲過程的返回值 31 9 3 4存儲過程的返回值 存儲過程在執行后都會返回一個整型值 如果執行成功 則返回0 否則返回 1到 99之間的隨機數 也可以使用RETURN語句來指定一個存儲過程的返回值 32 9 3 4存儲過程的返回值 例7 在northwind數據庫創建一個存儲過程 返回產品表中的所有產品的庫存量 createprocpr lierasbegindeclare fanhuizhiintselect fanhuizhi sum unitsinstock fromproductsreturn fanhuizhiend 33 9 3 4存儲過程的返回值 接受這個返回值必須要用變量來接收declare jieshouzhiintexec jieshouzhi pr lierprint jieshouzhi 34 9 3 4存儲過程的返回值 上例中返回的值也可以用output參數來返回 可以將上例的存儲過程改寫為 createprocpr lier 2 fanhuizhiintoutputasbeginselect fanhuizhi sum unitsinstock fromproductsend 35 9 3 4存儲過程的返回值 接收output的返回值也必須要用變量 如 declare jieshouzhiintexecpr lier 2 jieshouzhioutputprint jieshouzhi注意 1 return返回的是整數 output可以返回任何數據 2 如果讓return返回非整數值 在創建存儲過程時不會出錯 但是運行存儲過程時將會出錯 36 存儲過程案例 例9 在PUBS數據庫中創建一個帶參數的存儲過程SelectUser 查詢出用戶表UserMember中是否存在某用戶 如果不存在 則返回值為1 否則則查詢該用戶的密碼是否正確 如不正確 則返回值為2 否則返回值為0 37 存儲過程案例 createprocselectuser usernamevarchar 20 passvarchar 20 asif usernamenotin select用戶名fromusermember return 1 elseifexists select fromusermemberwhere用戶名 usernameand密碼 pass return 0 elsereturn 2 38 存儲過程案例 執行存儲過程語句如下 Declare fanintExec fan selectuser aaa aaa If fan 0Print 用戶名和密碼都正確 If fan 1Print 用戶名不存在 If fan 2Print 用戶名存在 密碼不正確 39 9 4事務的概述 1 事務的概念事務是最小的工作單元 這個工作單元要么成功完成所有操作 要么就是失敗 并將所做的一切復原 2 事務特性原子性 Atomic 整個數據庫事務是不可分割的工作單位一致性 ConDemoltent 指數據庫事務不能破壞關系數據的完整性以及業務邏輯上的一致性獨立性 Isolated 在并發環境中 當不同的事務同時操縱相同的數據時 每個事務都有各自的完整數據空間 持久性 Durable 只要事務成功結束 它對數據庫所做的更新就必須永久保存下來 40 9 4事務的概述 3 事務的運行模式顯式事務 顯示事務是手工配置的事務 用保留字標識顯式事務的開始和結束 開始顯式事務 使用BEGINTRAN 結束顯示事務 使用COMMITTRAN 取消事務 使用ROLLBACKTRAN命令 隱式事務 在前一個事務完成時新事務隱式啟動 但每個事務仍以COMMIT或ROLLBACK語句顯示完成 41 9 4事務的概述 自動提交事務每條單獨的SQL語句都是一個事務 這是SQL默認的事務管理模式 每個T SQL語句完成時 都被 成功 提交或 失敗 回滾 42 9 4事務的管理與應用 1 BEGINTRAN語句使用BEGINTRAN語句主要是顯示地命令SQLSERVER開始一個新事務 如果遇上錯誤 在BEGINTRAN之后的所有數據改動都能進行回滾 以將數據返回到已知的一致狀態 該語句主要用于顯示事務中 其語法結構如下 BEGIN TRAN TRANSACTION transaction name tran name variable WITHMARK description 43 9 4事務的管理與應用 1 BEGINTRAN語句參數說明 TRANSACTION可簡寫為TRANtransaction name為事務的名稱 其命名必須符合標識符規則 也可以省略不寫 tran name variable指用戶定義的含有有效事務名稱的變量名稱 WITHMARK description 用于指定在日志中標記事務 44 9 4事務的管理與應用 2 COMMITTRAN語句COMMITTRAN語句用于提交事務的操作結果 如果執行事務直到它無誤地完成 則可以使用該語句對數據庫做永久的改動 其語句格式為 COMMIT TRAN TRANSACTION transaction name tran name variable 45 9 4事務的管理與應用 3 ROLLBACKTRAN語句ROLLBACKTRAN語句用于當事務中的T SQL語句發生錯誤時進行回滾操作 從而恢復數據庫至事務開始之前的狀態 其語法格式為 ROLLBACK TRAN TRANSACTION transaction name tran name variable savepoint name savepoint variable 46 9 4事務的管理與應用 4 SAVETRAN語句SAVETRAN語句允許部分地提交一個事務 同時仍能回滾這個事務的其余部分 其語法格式為 SAVE TRAN TRANSACTION savepoint name savepoint variable 47 9 4事務的管理與應用 例1 請用事務在PUBS數據庫中創建一個存儲過程pr auth user 當向authors表中插入一個作者信息 同時將該作者的姓名插入到用戶表中的username列 pwd列的初始值為用戶名 實現的T SQL語句 CREATETABLEusremember usernamevarchar 80 Pwdvarchar 50 Emailvarchar 50 Phonechar 12 GO 48 9 4事務的管理與應用 CREATEPROCpr auth user au idvarchar 11 au lnamevarchar 40 au fnamevarchar 40 phonechar 12 contractbit 一般將具有默認值的參數放在后邊定義 便于調用時省略 addressvarchar 50 null cityvarchar 20 null statechar 2 null zipchar 5 nullAS 49 9 4事務的管理與應用 BEGINTRA