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

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