1、OracleOracle數(shù)據(jù)庫技術數(shù)據(jù)庫技術q存儲過程存儲過程/ /函數(shù)是能完成一定處理函數(shù)是能完成一定處理/ /計算功能并存儲計算功能并存儲在數(shù)據(jù)字典中的程序，調(diào)用時在數(shù)據(jù)庫服務器上運行在數(shù)據(jù)字典中的程序，調(diào)用時在數(shù)據(jù)庫服務器上運行存儲過程和函數(shù)存儲過程和函數(shù)q 創(chuàng)建存儲過程語法創(chuàng)建存儲過程語法CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE procedure_nameprocedure_name (argument IN | OUT | IN OUT (argument IN | OUT | IN OUT datatypeda

2、tatype, ), ) IS | AS IS | AS 說明部分說明部分; ; BEGINBEGIN 執(zhí)行部分執(zhí)行部分; ;EXCEPTIONEXCEPTION 異常處理部分異常處理部分; ;END END procedure_nameprocedure_name;q 參數(shù)參數(shù)q ININ型參數(shù)：由實際參數(shù)單向傳遞給形式參數(shù)型參數(shù)：由實際參數(shù)單向傳遞給形式參數(shù)q OUTOUT型參數(shù)：用于返回值型參數(shù)：用于返回值q IN OUTIN OUT型參數(shù)：給定實際參數(shù)值并返回一個結果值型參數(shù)：給定實際參數(shù)值并返回一個結果值q 默認為默認為ININq 形式參數(shù)說明時不可以指定數(shù)據(jù)寬度形式參數(shù)說明時不可以

3、指定數(shù)據(jù)寬度q 形式參數(shù)應該以形式參數(shù)應該以“P_”P_”前綴命名前綴命名q注釋：注釋：-、/ /* * * */ /、REMREMqSHOW ERRORSSHOW ERRORS查看創(chuàng)建錯誤查看創(chuàng)建錯誤SHOW ERRORS FUNCTION | PROCEDURE | PACKAGE SHOW ERRORS FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | PACKAGE BODY | TRIGGER | VIEW 1.1.存儲過程存儲過程q 不可以在存儲過程中引用主

4、機不可以在存儲過程中引用主機( (綁定綁定) )變量變量q SQL SQL* *PlusPlus中執(zhí)行存儲過程中執(zhí)行存儲過程qEXECUTE EXECUTE procedure_nameprocedure_nameq 查看存儲過程參數(shù)查看存儲過程參數(shù)qDESCRIBE DESCRIBE procedure_nameprocedure_nameq 刪除存儲過程刪除存儲過程DROP PROCEDURE DROP PROCEDURE procedure_nameprocedure_name; ;1.1.存儲過程存儲過程例例9.21 9.21 查詢指定編號雇員的名字、工資和傭金。查詢指定編號雇員的名字

5、、工資和傭金。 EXA_09_21.SQL q創(chuàng)建函數(shù)語法創(chuàng)建函數(shù)語法q CREATE OR REPLACE FUNCTION function_nameCREATE OR REPLACE FUNCTION function_nameq (argument IN datatype, ) (argument IN datatype, )q RETURN return_type RETURN return_typeq IS | AS IS | AS q 說明部分說明部分; ; q BEGINBEGINq q return return 返回值表達式返回值表達式; ;q q EXCEPTIONEXC

6、EPTIONq 異常處理部分異常處理部分; ;q END function_name;END function_name;2.2.函數(shù)函數(shù)q 在在SQLSQL* *PlusPlus中調(diào)用函數(shù)中調(diào)用函數(shù)qSELECT SELECT function_namefunction_name FROM DUAL; FROM DUAL;q 查看函數(shù)的參數(shù)和返回值類型查看函數(shù)的參數(shù)和返回值類型qDESCRIBE DESCRIBE function_namefunction_nameq 刪除函數(shù)刪除函數(shù)DROP FUNCTION DROP FUNCTION function_namefunction_name

7、; ;2.2.函數(shù)函數(shù)例例9.22 9.22 編寫函數(shù)按編寫函數(shù)按YYYY-MM-DD HH24:MI:SSYYYY-MM-DD HH24:MI:SS格式以字符串格式以字符串形式返回當前系統(tǒng)時間。形式返回當前系統(tǒng)時間。 EXA_09_22.SQL 例例9.23 9.23 十萬級幣值轉換函數(shù)，將輸入的數(shù)值十萬級幣值轉換函數(shù)，將輸入的數(shù)值( (該值不超過該值不超過100100萬萬) ) 按整數(shù)轉換為大寫的金額字符串。按整數(shù)轉換為大寫的金額字符串。 EXA_09_23.SQL q 用戶自定義函數(shù)可以用于以下場合用戶自定義函數(shù)可以用于以下場合q(1) (1) SELECTSELECT語句的語句的Sel

8、ectSelect列表列表q(2) (2) WHEREWHERE和和HAVINGHAVING子句中子句中q(3) (3) CONNECT BY, START WITH, ORDER BY, GROUP BYCONNECT BY, START WITH, ORDER BY, GROUP BYq(4) (4) INSERTINSERT語句的語句的VALUESVALUES子句子句q(5) (5) UPDATEUPDATE語句的語句的SETSET子句子句q 在在SQLSQL語句中使用自定義函數(shù)的限制語句中使用自定義函數(shù)的限制q(1) (1) 在在SQLSQL表達式中調(diào)用的函數(shù)不可以包含表達式中調(diào)用的函

9、數(shù)不可以包含DMLDML語句語句q(2) (2) 在在UPDATE/DELETEUPDATE/DELETE語句中調(diào)用的函數(shù)不能包含有對該語句語句中調(diào)用的函數(shù)不能包含有對該語句正在操作的表執(zhí)行正在操作的表執(zhí)行DMLDML操作的語句操作的語句q(3) (3) 在在UPDATE/DELETEUPDATE/DELETE語句中調(diào)用的函數(shù)不能包含有對該語句語句中調(diào)用的函數(shù)不能包含有對該語句正在操作的表執(zhí)行正在操作的表執(zhí)行SELECTSELECT的語句的語句q(4) (4) 在在SQLSQL表達式中調(diào)用的函數(shù)不可以包含有結束事務的語句表達式中調(diào)用的函數(shù)不可以包含有結束事務的語句q(5) (5) 函數(shù)調(diào)用的子

10、程序也不能破壞上述限制函數(shù)調(diào)用的子程序也不能破壞上述限制2.2.函數(shù)函數(shù)q 三種參數(shù)傳遞形式三種參數(shù)傳遞形式(1) (1) 按位置順序按位置順序(2) (2) 名稱前綴名稱前綴參數(shù)名參數(shù)名=實參值實參值 (3) (3) 位置與名稱的組合位置與名稱的組合所有按位置傳遞的參數(shù)應該出現(xiàn)在以名稱前綴表所有按位置傳遞的參數(shù)應該出現(xiàn)在以名稱前綴表示的參數(shù)之前示的參數(shù)之前3.3.參數(shù)的傳遞形式參數(shù)的傳遞形式例例9.219.21的過程調(diào)用可修改為如下形式的過程調(diào)用可修改為如下形式EXECUTE query_emp(p_no=7369,p_name=:g_name,p_sal=:g_sal,p_comm=:g_

11、comm)EXECUTE query_emp(p_no=7369,p_name=:g_name,p_sal=:g_sal,p_comm=:g_comm)EXECUTE query_emp(7369,p_name=:g_name,p_sal=:g_sal,p_comm=:g_ comm)EXECUTE query_emp(7369,p_name=:g_name,p_sal=:g_sal,p_comm=:g_ comm)q定義在一個子程序中的命名子程序定義在一個子程序中的命名子程序q局部子程序只在定義它的存儲過程或函數(shù)中才有效局部子程序只在定義它的存儲過程或函數(shù)中才有效q局部子程序只能出現(xiàn)在定義它

12、的子程序的說明部分局部子程序只能出現(xiàn)在定義它的子程序的說明部分的最后的最后4.4.局部子程序局部子程序例例9.24 9.24 編寫當刪除雇員時，希望能記載是哪個用戶在什么編寫當刪除雇員時，希望能記載是哪個用戶在什么時候刪除的。這里面涉及兩件事情，一是刪除指定的時候刪除的。這里面涉及兩件事情，一是刪除指定的雇員記錄，二是記載是哪個用戶刪除的。因此可以考雇員記錄，二是記載是哪個用戶刪除的。因此可以考慮將記載誰刪除的雇員記錄這件事情獨立出來，可創(chuàng)慮將記載誰刪除的雇員記錄這件事情獨立出來，可創(chuàng)建一個局部子程序。建一個局部子程序。 EXA_09_24.SQL q對象訪問權限的獲得途徑對象訪問權限的獲得途

13、徑q (1) (1) 直接獲得：通過對象和角色權限的授予獲得直接獲得：通過對象和角色權限的授予獲得q (2) (2) 間接獲得：通過獲得對對象進行處理的子程序的間接獲得：通過獲得對對象進行處理的子程序的執(zhí)行權限來間接獲得執(zhí)行權限來間接獲得q執(zhí)行子程序的用戶對對象訪問權限的身份驗證方法執(zhí)行子程序的用戶對對象訪問權限的身份驗證方法q (1) (1) CURRENT_USERCURRENT_USER：用執(zhí)行該子程序的用戶權限訪問用執(zhí)行該子程序的用戶權限訪問涉及到的數(shù)據(jù)庫對象涉及到的數(shù)據(jù)庫對象 q (2) (2) DEFINERDEFINER：用創(chuàng)建者的權限訪問相關數(shù)據(jù)庫對象用創(chuàng)建者的權限訪問相關數(shù)據(jù)

14、庫對象q在子程序首部用在子程序首部用AUTHIDAUTHID關鍵字設置關鍵字設置5.5.子程序的管理子程序的管理例例9.25 9.25 子程序中對象訪問權限的身份驗證。子程序中對象訪問權限的身份驗證。 EXA_09_25.SQL q與子程序相關的數(shù)據(jù)字典與子程序相關的數(shù)據(jù)字典qUSER_OBJECTSUSER_OBJECTSqUSER_SOURCEUSER_SOURCEqUSER_ERRORSUSER_ERRORS5.5.子程序的管理子程序的管理例例9.26 9.26 查看當前用戶定義的對象。查看當前用戶定義的對象。 EXA_09_26.SQL v 包用于將相關的數(shù)據(jù)類型、數(shù)據(jù)對象、處理過程和

15、函數(shù)等邏輯上組織在一起v 一個完整的包由說明部分、包主體兩部分組成v 無主體包，常用于全局變量和標志變量等的說明v 包不可以被整體調(diào)用或當作參數(shù)，不可以嵌套定義v 一個包內(nèi)成員被訪問時，整個包都被讀入內(nèi)存v 在包的說明部分中聲明的過程和函數(shù)等是公共的(全局的)v 包說明部分體現(xiàn)了外部訪問接口v 包主體中聲明的過程為私有的(局部的)包包v 在開發(fā)包時應遵循的幾條原則 為了通用/共享目的而使用包 先定義包的說明部分，然后定義包主體 包說明部分只應包含全局元素 只在一次會話或事務中使用的元素應放在包主體的聲明部 分進行聲明 修改包的說明部分必須重新編譯包主體 包說明部分中的元素要盡可能少，只含外部訪

16、問接口所需 的元素v 在SQL語句中使用的包函數(shù)應滿足如下限制規(guī)定 函數(shù)中不能有結束事務、回退或ALTER SESSION、ALTER SYSTEM等操作 函數(shù)中不能有DML或修改數(shù)據(jù)庫的操作。 函數(shù)中不能有讀或修改該SQL語句正操作表的操作包概述包概述v 一般按如下步驟建立包 編寫建立包的腳本文件，將文件的擴展名設為SQL 在SQL*Plus中裝入并運行該腳本文件使用SHOW ERRORS 命令查看包的編譯出錯信息v 說明部分用于聲明包中的全局數(shù)據(jù)對象和程序單元包的創(chuàng)建與刪除包的創(chuàng)建與刪除包內(nèi)變量如果沒有賦初值，系統(tǒng)將默認賦值為NULL 例 4：創(chuàng)建薪資管理包說明部分。 EXA_09_27.

17、SQL CREATE OR REPLACE PACKAGE schema.package_name IS|AS 全局類型和變量、游標、異常等項目的聲明; 包中子程序的聲明;END package_name;v 包主體用于定義在說明部分中聲明的函數(shù)和過程的具體實現(xiàn)包的創(chuàng)建與刪除包的創(chuàng)建與刪除刪除包說明部分DROP PACKAGE package_name ;刪除包主體DROP PACKAGE BODY package_name ; 例 5：創(chuàng)建薪資管理包的包主體。EXA_09_28.SQL CREATE OR REPLACE PACKAGE BODYschema.package_name IS|

18、AS 私有類型和變量、游標、異常等項目的聲明 包中子程序的主體定義;END package_name;v 在不同場合下引用包內(nèi)元素有不同的語法形式v 在包內(nèi)引用直接使用元素名稱即可 如：g_comm := p_comm;v 在包定義之外引用包元素，需要添加包名前綴 schema. 包.元素名 如：EXECUTE wage_package.reset_comm(150)v 訪問遠程包需要使用數(shù)據(jù)庫鏈接，參數(shù)放最后包.元素名db_link(參數(shù))包內(nèi)元素的引用包內(nèi)元素的引用v 只有說明部分，沒有主體部分的包v 無體包常用于全局類型、常量、變量、異常、游標等的聲明 例 6：創(chuàng)建一個無體包并引用其中元素。 EXA_09_29.SQL 無體包無體包v 同名的子程序，形式參數(shù)的個數(shù)或類型不同v 如下情形不允許使用重載 兩個子程序僅在形式參數(shù)的數(shù)據(jù)類型或子類型上有差別，而這些貌似不同的類型屬于同一類型族 兩個子程序僅在返回值類型上有差別 獨立的子程序不允許重載v 用戶自定義的同名子程序?qū)⒏采w系統(tǒng)內(nèi)置子程序 此時要使用系統(tǒng)提供的子程序，需要使用系統(tǒng)包名稱前綴，如STANDARD.TO_CHAR()例 7:建立可重載