oracle 存儲(chǔ)過(guò)程

1、 存儲(chǔ)過(guò)程介紹 創(chuàng)建和管理存儲(chǔ)過(guò)程 在存儲(chǔ)過(guò)程中使用參數(shù) 處理錯(cuò)誤信息補(bǔ)充：實(shí)現(xiàn)存儲(chǔ)過(guò)程 定義存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程介紹定義存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程 是存儲(chǔ)在服務(wù)器上的 Transact-SQL 語(yǔ)句的命名集合 是封裝重復(fù)性任務(wù)的方法 支持用戶聲明變量、條件執(zhí)行以及其他強(qiáng)有力的編程特性 SQL Server 中的存儲(chǔ)過(guò)程與其他編程語(yǔ)言中的過(guò)程類中的存儲(chǔ)過(guò)程與其他編程語(yǔ)言中的過(guò)程類似，它可以似，它可以 包含執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其他過(guò)程）的編程語(yǔ)句 接受輸入?yún)?shù) 向調(diào)用過(guò)程或批處理返回狀態(tài)值，以表明成功或失?。ㄒ约笆≡颍?以輸出參數(shù)的形式將多個(gè)值返回至調(diào)用過(guò)程或批處理存儲(chǔ)過(guò)程的

2、優(yōu)點(diǎn)存儲(chǔ)過(guò)程封裝了商務(wù)邏輯，確保一致的數(shù)據(jù)訪問(wèn)和修改存儲(chǔ)過(guò)程封裝了商務(wù)邏輯，確保一致的數(shù)據(jù)訪問(wèn)和修改。若規(guī)則或策略有變化，則只需要修改服務(wù)器上的存儲(chǔ)過(guò)程，所有的客戶端就可以直接使用屏蔽數(shù)據(jù)庫(kù)模式的詳細(xì)資料屏蔽數(shù)據(jù)庫(kù)模式的詳細(xì)資料。用戶不需要訪問(wèn)底層的數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)內(nèi)的對(duì)象提供了安全性機(jī)制提供了安全性機(jī)制。用戶可以被賦予執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，而不必在存儲(chǔ)過(guò)程引用的所有對(duì)象上都有權(quán)限改善性能改善性能。預(yù)編譯的 Transact-SQL 語(yǔ)句，可以根據(jù)條件決定執(zhí)行哪一部分減少網(wǎng)絡(luò)通信量減少網(wǎng)絡(luò)通信量。客戶端用一條語(yǔ)句調(diào)用存儲(chǔ)過(guò)程，就可以完成可能需要大量語(yǔ)句才能完成的任務(wù)，這樣減少了客戶端和服務(wù)器之間的

3、請(qǐng)求/回答包 存儲(chǔ)過(guò)程介紹 創(chuàng)建和管理存儲(chǔ)過(guò)程 在存儲(chǔ)過(guò)程中使用參數(shù) 處理錯(cuò)誤信息實(shí)現(xiàn)存儲(chǔ)過(guò)程創(chuàng)建和管理存儲(chǔ)過(guò)程 創(chuàng)建存儲(chǔ)過(guò)程 執(zhí)行存儲(chǔ)過(guò)程 修改和刪除存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程 創(chuàng)建存儲(chǔ)過(guò)程 只能在當(dāng)前數(shù)據(jù)庫(kù)內(nèi)創(chuàng)建存儲(chǔ)過(guò)程，除了臨時(shí)存儲(chǔ)過(guò)程。臨時(shí)存儲(chǔ)過(guò)程總是創(chuàng)建在 tempdb 數(shù)據(jù)庫(kù)中 存儲(chǔ)過(guò)程可以引用表、視圖、用戶定義函數(shù)、其他存儲(chǔ)過(guò)程以及臨時(shí)表 若存儲(chǔ)過(guò)程創(chuàng)建了局部臨時(shí)表，則當(dāng)存儲(chǔ)過(guò)程執(zhí)行結(jié)束后臨時(shí)表消失USE NorthwindGOCREATE PROC dbo.OverdueOrdersAS SELECT * FROM dbo.Orders WHERE RequiredDate GETD

4、ATE() AND ShippedDate IS NullGO創(chuàng)建存儲(chǔ)過(guò)程（續(xù)） 創(chuàng)建存儲(chǔ)過(guò)程（續(xù)） CREATE PROCEDURE 定義可以包括任何數(shù)目和類型的Transact-SQL語(yǔ)句，但不包括下列對(duì)象創(chuàng)建語(yǔ)句：CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 執(zhí)行 CREATE PROCEDURE 語(yǔ)句的用戶必須是 sysadmin、db_owner 或 db_ddladmin角色的成員，或必須擁有 CREATE PROCEDURE 權(quán)限 依賴于可用內(nèi)存，存儲(chǔ)過(guò)程的最大大小為128 MB

5、存儲(chǔ)過(guò)程可以嵌套32層。當(dāng)前的嵌套層數(shù)存儲(chǔ)在系統(tǒng)函數(shù) nestlevel 中。創(chuàng)建存儲(chǔ)過(guò)程（續(xù)）查看存儲(chǔ)過(guò)程的信息 查看所有類型存儲(chǔ)過(guò)程的額外信息 系統(tǒng)存儲(chǔ)過(guò)程 sp_help、sp_helptext、sp_depends 顯示數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程以及擁有者名字的列表 系統(tǒng)存儲(chǔ)過(guò)程 sp_stored_procedures 得到存儲(chǔ)過(guò)程的信息 查詢系統(tǒng)表 sysobjects、syscomments、sysdepends創(chuàng)建存儲(chǔ)過(guò)程的指導(dǎo)原則 避免出現(xiàn)存儲(chǔ)過(guò)程的擁有者和底層對(duì)象的擁有者不同的情況，建議由dbo用戶擁有數(shù)據(jù)庫(kù)中所有對(duì)象 每個(gè)存儲(chǔ)過(guò)程完成單個(gè)任務(wù) 命名本地存儲(chǔ)過(guò)程的時(shí)候，避免使用“s

6、p_”前綴 盡量少使用臨時(shí)存儲(chǔ)過(guò)程，以避免頻繁連接 tempdb 里的系統(tǒng)表 不要直接從 syscomments 系統(tǒng)表里刪除項(xiàng)執(zhí)行存儲(chǔ)過(guò)程 單獨(dú)執(zhí)行存儲(chǔ)過(guò)程單獨(dú)執(zhí)行存儲(chǔ)過(guò)程不帶參數(shù)的情況：EXECUTE 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名 WITH RECOMPILE 在在 INSERT 語(yǔ)句內(nèi)執(zhí)行存儲(chǔ)過(guò)程語(yǔ)句內(nèi)執(zhí)行存儲(chǔ)過(guò)程語(yǔ)法：INSERT INTO 表名表名 EXECUTE 將本地或遠(yuǎn)程存儲(chǔ)過(guò)程返回的結(jié)果集插入本地表中 在 INSERT 語(yǔ)句內(nèi)執(zhí)行的存儲(chǔ)過(guò)程必須返回關(guān)系結(jié)果集USE NorthwindGOEXEC OverdueOrdersGO 修改和刪除存儲(chǔ)過(guò)程 修改存儲(chǔ)過(guò)程 用 ALTER PRO

7、CEDURE 中的定義取代現(xiàn)有存儲(chǔ)過(guò)程原先的定義，但保留權(quán)限分配USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeIDFROM OrdersWHERE RequiredDate GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO 修改和刪除存儲(chǔ)過(guò)程（續(xù)） 刪除存儲(chǔ)過(guò)程語(yǔ)法：DROP PROCEDURE 存儲(chǔ)過(guò)程名 ,.n 用 DROP PR

8、OCEDURE 語(yǔ)句從當(dāng)前數(shù)據(jù)庫(kù)中移除用戶定義存儲(chǔ)過(guò)程 刪除存儲(chǔ)過(guò)程的注意事項(xiàng) 在刪除存儲(chǔ)過(guò)程之前，執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程 sp_depends 檢查是否有對(duì)象依賴于此存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程介紹 創(chuàng)建和管理存儲(chǔ)過(guò)程 在存儲(chǔ)過(guò)程中使用參數(shù) 處理錯(cuò)誤信息實(shí)現(xiàn)存儲(chǔ)過(guò)程在存儲(chǔ)過(guò)程中使用參數(shù) 使用輸入?yún)?shù) 使用輸入?yún)?shù)執(zhí)行存儲(chǔ)過(guò)程 使用輸出參數(shù)返回值使用輸入?yún)?shù) 輸入?yún)?shù)允許傳遞信息到存儲(chǔ)過(guò)程內(nèi) 在 CREATE PROCEDURE 中指定 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 =默認(rèn)值默認(rèn)值 USE NorthwindGOCREATE PROC dbo.OverdueOrders2Employee_ID int ,O

9、rder_date datetimeASSELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeIDFROM OrdersWHERE EmployeeID = Employee_ID and OrderDate = Order_dateGO 使用輸入?yún)?shù)執(zhí)行存儲(chǔ)過(guò)程 通過(guò)參數(shù)名傳遞值(順序無(wú)所謂) 通過(guò)位置傳遞參數(shù)（順序保持一致）EXEC OverdueOrders2 Employee_ID = 1 , Order_date = 1996-7-17EXEC OverdueOrders2 1 ,

10、1996-7-17EXEC OverdueOrders2 Order_date = 1996-7-17, Employee_ID = 1 使用輸出參數(shù)返回值 輸出參數(shù)：以 OUTPUT 關(guān)鍵字指定的變量CREATE PROC proc1A int , B int , RESULT int OUTPUTASSET RESULT = A * BGO 執(zhí)行有輸出參數(shù)的存儲(chǔ)過(guò)程DECLARE answer intEXEC proc1 4, 7, answer OUTPUTSELECT answer as ANSWER 必須定義一個(gè)變量，必須定義一個(gè)變量，以接受返回值以接受返回值寫上寫上OUTPUT,才

11、才可以接收到返回值可以接收到返回值 存儲(chǔ)過(guò)程介紹 創(chuàng)建和管理存儲(chǔ)過(guò)程 在存儲(chǔ)過(guò)程中使用參數(shù) 處理錯(cuò)誤信息實(shí)現(xiàn)存儲(chǔ)過(guò)程錯(cuò)誤信息處理 為了增強(qiáng)存儲(chǔ)過(guò)程的效率，應(yīng)使用錯(cuò)誤信息向用戶傳達(dá)事務(wù)狀態(tài)（成功或失?。?可以在錯(cuò)誤處理邏輯中檢查下列錯(cuò)誤：返回碼、SQL Server 錯(cuò)誤、用戶定義的錯(cuò)誤信息 RETURN 語(yǔ)句 從查詢或存儲(chǔ)過(guò)程無(wú)條件返回，同時(shí)可以返回一個(gè)整數(shù)狀態(tài)值（返回碼） 返回碼為0表示成功。返回非零表示失敗。用戶定義的返回值總是優(yōu)先于系統(tǒng)的返回值。錯(cuò)誤信息處理（續(xù)） error 全局變量 error 包含了最近執(zhí)行的 Transact-SQL 語(yǔ)句的錯(cuò)誤號(hào)，隨著每一條語(yǔ)句的執(zhí)行而更新 如果

12、語(yǔ)句成功執(zhí)行，返回0演示 錯(cuò)誤信息處理例：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，插入學(xué)生信息。 create create procproc upStudInsert studName varchar(20),birthdate datetime,schooldate datetime as as begin transaction begin transaction insert into tblstudent values(studName,birthdate,schooldate) if errorerror 0 begin rollback tranrollback tran print failfail return end else print successsuccess commit transaction commit transaction GO演示1 錯(cuò)誤信息處理（續(xù)）用下面的語(yǔ)句驗(yàn)證：EXEC upStudInsert Tom, 2007-1-1, 1988-11-1EXEC upStudInsert Tom, 1981-1-1, 19