SQL第9章存儲過程和事務(wù).ppt

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