老二牛車第七章理論課子程序和程序包

Oracle數(shù)據(jù)庫(kù)應(yīng)用 理論課 子程序和程序包 本章 技能 目標(biāo) 創(chuàng)建和使用子程序 創(chuàng)建和使用程序包 1. 子程序 子程序是 命名的 PL/SQL 塊，編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中。 可以為它們指定參數(shù)，可以從任何 數(shù)據(jù)庫(kù)客戶端和應(yīng)用程序中調(diào)用它們。命名的 PL/SQL 程序包包括存儲(chǔ)過程和函數(shù)。程序包是存儲(chǔ)過程和函數(shù)的集合。 與未命名或匿名 PL/SQL 塊一樣，子程序也有聲明部分，執(zhí)行部分和一個(gè)可選的異常處理部分。聲明部分包含類型、游標(biāo)、常量、變量、異常和嵌套子程序的聲明。這些內(nèi)容都是本 地的，在程序退出時(shí)會(huì)自動(dòng)銷毀。執(zhí)行部分包含賦值語(yǔ)句、流程控制語(yǔ)句和 Oracle 的數(shù)據(jù)操作語(yǔ)句。異常處理部分包含異常處理 處理。 子程序的各個(gè)部分： 聲明部分 可執(zhí)行部分 異常處理部分 (可選 ) 子程序的分類： 過程 執(zhí)行某些操作 函數(shù) 執(zhí)行操作并返回值 子程序的優(yōu)點(diǎn) 如下 ： 模塊化 ： 通過子程序，可以將程序分解為可管理的，明確的 邏輯模塊 可重用性 ：子程序在創(chuàng)建并執(zhí)行后，就可以在 任意數(shù)目的程序 中 調(diào)用 可維護(hù)性 ：子程序可以 簡(jiǎn)化維護(hù)操作 ，因?yàn)槿绻粋€(gè)子程序受到影響，則只需要修改該子程序的定義。 安全性 ： 通過設(shè)置權(quán) 限，使數(shù)據(jù) 訪問的唯一方式就是通過用戶提供的過程和函數(shù)，這不僅可以讓數(shù)據(jù)更加安全，而且還可以保證它的正確性。 1.1 過程 第 7章 子程序和程序包 - 2 - 2 過程是 執(zhí)行某些操作的子程序， 它是執(zhí)行 特定任務(wù)的 模塊。 從根本上講，過程就是命名的PL/SQL 塊，它可以被賦予參數(shù)，存儲(chǔ)在數(shù)據(jù)庫(kù)中，然后由一個(gè)應(yīng)用程序或其它 PL/SQL 程序調(diào)用。 創(chuàng)建過程： 用于 創(chuàng)建過程的語(yǔ)法 如下 ： CREATE OR REPLACE PROCEDURE () IS|AS BEGIN EXCEPTION END ； 其中： procedure_ name 是過程的名稱， parameter_ list 是參數(shù)列表， local_variable_declaration 是局部聲明。 executable_ statements 是可執(zhí)行語(yǔ)句， exception_ handlers 是異常處理程 序。 創(chuàng)建過程的語(yǔ)法的大部分都與 PL/SQL 塊相似。聲明部分置于關(guān)鍵字 IS 與 BEGIN 之間。 存儲(chǔ)過程中的聲明不使用 DECLARE關(guān)鍵字 .過程最后的 END關(guān)鍵字后可以使用可選的 .其它部分與匿名 Pl/SQL 塊完全相同。 例 1 演示如何創(chuàng)建過程。 例 1： CREATE OR REPLACE PROCEDURE find_emp(emp_no NUMBER) AS empname VARCHAR2(20)； BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no； DBMS_OUTPUT.PUT_LINE(雇員姓名是 | empname)； EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (雇員編號(hào)未找到 )； END find_emp； / 該例創(chuàng)建了 find_emp 過程。該過程接受一個(gè)雇員編號(hào)參數(shù) emp_no,然后 SELECT 語(yǔ)句從 EMP表中選擇 empno 為 emp_no 的雇員記錄，并顯示雇員。如果在表中未找到此雇 員編號(hào)。 該過程顯示 NO_DATA_FOUND 異常，并顯示消息“ 雇員編號(hào)未找到 ”。 例 1-1： CREATE OR REPLACE PROCEDURE getDeptCount AS deptCount int； BEGIN SELECT COUNT(*) INTO deptCount FROM dept； DBMS_OUTPUT.PUT_LINE(dept 表共有 |deptCount|行記錄 )； END getDeptCount； 第 7章 子程序和程序包 - 3 - 3 創(chuàng)建不帶參數(shù)的存儲(chǔ)過程，該過程返回 dept 表行數(shù) 。 當(dāng)我們創(chuàng)建的存儲(chǔ)過程沒有參數(shù)時(shí)，在存儲(chǔ)過程名字后面不能有括號(hào) 。 執(zhí)行過程 在 SQL 提示符下，使用 EXECUTE 語(yǔ)句來執(zhí)行過程。 執(zhí)行過程的語(yǔ)法 如下 ： EXECUTE procedure_name(parameters_list)； 其中： procedure_ name 是過程的名稱， parameter_ list 是參數(shù)列表。 要執(zhí)行 find_emp 過程，請(qǐng)輸入以下命令。 SETSERVEROUTPUT ON EXECUTE find_emp (7900)； 還 使用以下代碼可以執(zhí)行存儲(chǔ)過程： BEGIN getDeptCount； END； 還可以通過以下代碼來簡(jiǎn)化調(diào)用： EXEC getDeptCount； CALL getDeptCount()； 注意： 并不是所有的存儲(chǔ)過程都可以用這種方式來調(diào)用 定義無(wú)參存儲(chǔ)過程時(shí)，存儲(chǔ)過程名后不能加 () 在塊中或是通過 EXEC 調(diào)用存儲(chǔ)過程時(shí)可以省略 () 通過 CALL 調(diào)用無(wú)參存儲(chǔ)過程必須加上 () 過程參數(shù)模式： 過程參數(shù)的三種模式： IN ,OUT,IN OUT 。 定義過程參數(shù)的語(yǔ)法如下： parameter_name IN | OUT | IN OUT datatype := | DEFAULT expression IN 用于接受調(diào)用程序的值 默認(rèn)的參數(shù)模式 OUT 用于向調(diào)用程序返回值 IN OUT 用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值 在返回到調(diào)用環(huán)境之前，必須先給 OUT 或 IN OUT 參數(shù)賦值。 可以在參數(shù)列表中為 IN 參數(shù)賦予一個(gè)默認(rèn)值，不能為 OUT， IN OUT 參數(shù)賦予默認(rèn)值。 創(chuàng)建帶有 IN 參數(shù)的存儲(chǔ)過程 。 例 2： CREATE OR REPLACE PROCEDURE itemdesc(item_code IN VARCHAR2) IS v_itemdesc VARCHAR2(5)； 第 7章 子程序和程序包 - 4 - 4 BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode = item_code； DBMS_OUTPUT.PUT_LINE(item_code|項(xiàng)目的說明為 |v_itemdesc)； END； / 要執(zhí)行 itemdesc 過程，請(qǐng)輸入以下查詢。 EXECUTE itemdesc (i201)； / 例 2-1： CREATE OR REPLACE PROCEDURE getSalaryByEmpNo0(eNo NUMBER) -參數(shù)的數(shù)據(jù)類型不能指定長(zhǎng)度 AS salary emp.sal%TYPE； BEGIN SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo； DBMS_OUTPUT.PUT_LINE(eNo|號(hào)員工的工資為 |salary)； EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(沒有找到該編號(hào)的員工 )； END； 當(dāng)定義的存儲(chǔ)過程含有參數(shù)時(shí)，參數(shù)的數(shù)據(jù)類型不能指定長(zhǎng)度。參數(shù)還有輸入和輸出之分，本例中沒有指定，默認(rèn)情況為輸入?yún)?shù)，也可顯示的指定某個(gè)參數(shù)是輸入?yún)?shù)，如 (eNo IN NUMBER)。 BEGIN getSalaryByEmpNo0(7788)； END； / EXEC getSalaryByEmpNo0(7788)； / CALL getSalaryByEmpNo0(7788)； 但是如果傳給一個(gè)存儲(chǔ)過程的參數(shù)是變量時(shí)，必須 使用 BEGIN END 塊，如下： DECLARE no emp.empNo%TYPE； BEGIN no:=7788； getSalaryByEmpNo(no)； END； 如果某個(gè)包中含有常量，也可以通過如下的方式調(diào)用： EXEC getSalaryByEmpNo(ConstantPackage.no)； 但這種方式不能再使用 CALL 調(diào)用。 創(chuàng)建帶有 OUT 參數(shù)的存儲(chǔ)過程 。 例 3： CREATE OR REPLACE PROCEDURE test(value1 IN VARCHAR2,value2 OUT NUMBER) IS identity NUMBER； 第 7章 子程序和程序包 - 5 - 5 BEGIN SELECT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1； IF identity 7788)； -使用默認(rèn)值 EXEC addEmp(mgr=7788,empNo=7779,eName=sunliu)； -更改參數(shù)順序 將過程的執(zhí)行權(quán)限授予其他用戶： 過程創(chuàng)建之后，只有創(chuàng)建該過程的用戶才有權(quán)調(diào)用它。其它用戶如果要調(diào)用該過程，需要得到過程的 EXECUTE 權(quán)限。 例 6 演示如 何將執(zhí)行過程的權(quán)限授予其它用戶。 例 6： SQL GRANT EXECUTE ON find_emp TO MARTIN； SQL GRANT EXECUTE ON swap TO PUBLIC； 維護(hù)過程 1、刪除存儲(chǔ)過程 DROP PROCEDURE Proc_Name； 2、查看過程狀態(tài) SELECT object_name,status FROM USER_OBJECTS WHERE object_type=PROCEDURE； 3、重新編譯過程 ALTER PROCEDURE Proc_Name COMPILE； 4、查看過程代碼 SELECT * FROM USER_SOURCE WHERE TYPE=PROCEDURE； 1.2 函數(shù) 函數(shù)與過程相似，也是數(shù)據(jù)庫(kù)中已命名 PL/SQL 程序塊。函數(shù)的主要特性是它必須返回一個(gè)值。創(chuàng)建函數(shù)時(shí)通過 RETURN 子句指定函數(shù)返回值的數(shù)據(jù)類型。 在函數(shù)體的任何地方用戶都可以通過 RETURN expression 語(yǔ)句從函數(shù)返回，這里的表達(dá)式的數(shù)據(jù)類型要與 RETURN 子句指定的類型相同。 與過程類似，函數(shù) 將代碼分割成模塊，有助于組織代碼。 定義函數(shù) 用于定義函數(shù)的語(yǔ)法如下： CREATE OR REPLACE FUNCTION (param1,param2) RETURN IS|AS local declarations BEGIN 第 7章 子程序和程序包 - 8 - 8 Executable Statements； RETURN result； EXCEPTION Exception handlers； END； 創(chuàng)建函數(shù) 例 7 演示如何創(chuàng)建 函數(shù) 。 例 7： CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN 朋友，您好！今天是 | TO_CHAR(SYSDATE, DAY)； END； / 上例創(chuàng)建了一個(gè)簡(jiǎn)單函數(shù) fun_hello。 與過程不同，函數(shù)不能單獨(dú)執(zhí)行，只能通過 SQL 語(yǔ)句或 PL/SQL 程序塊來調(diào)用。 訪問函數(shù)的兩種方式： 使用 PL/SQL 塊 使用 SQL 語(yǔ)句 要調(diào)用此函數(shù)，請(qǐng)輸入以下語(yǔ)句。 SELECT fun_hello FROM DUAL； 上述語(yǔ)句將返回消息“朋友，您好” 。 例 8 演示一個(gè)有業(yè)務(wù)功能的函數(shù) 。 例 8： CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER； max_price NUMBER； BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile； IF price = min_price AND price job)； DBMS_OUTPUT.PUT_LINE(job)； END； 創(chuàng)建帶有輸入輸出參數(shù)的函數(shù) 例 13： CREATE OR REPLACE FUNCTION getHiredateAndJobByEmpName(nb IN OUT VARCHAR2) RETURN DATE AS hireDate DATE； BEGIN SELECT hiredate,job INTO hireDate,nb FROM EMP WHERE eName=nb； RETURN hireDate； END； 當(dāng)函數(shù)中有輸出參數(shù)時(shí)，不能直接通過 SELECT 語(yǔ)句查看，必須在 BEGIN END 塊中調(diào)用，如下： DECLARE n emp.eName%TYPE:=SCOTT； BEGIN DBMS_OUTPUT.PUT_LINE(getHiredateAndJobByEmpName(n)； DBMS_OUTPUT.PUT_LINE(n)； END； 第 7章 子程序和程序包 - 11 - 11 將 函數(shù) 的執(zhí)行權(quán)限授予其他用戶： 與過程相同，用戶要調(diào)用其它用戶的函數(shù)也需要得到授權(quán)，權(quán)限的名稱也是 EXECUTE。 SQL GRANT EXECUTE ON fun_hello TO MARTIN； 維護(hù)函數(shù) 1、刪除函數(shù) DROP FUNCTION Fun_Name； 2、查看函數(shù)狀態(tài) SELECT object_name,status FROM USER_OBJECTS WHERE object_type=FUNCTION； 3、重新編譯函數(shù) ALTER FUNCTION Fun_Name COMPILE； 4、查看函數(shù)代碼 SELECT * FROM USER_SOURCE WHERE TYPE=FUNCTION； 使用函數(shù)的限制： 從 SQL 表達(dá)式調(diào)用函數(shù)有以下限制： 從 SELECT 語(yǔ)句調(diào)用的任何函數(shù)均不能修改數(shù)據(jù)庫(kù)表。 當(dāng)遠(yuǎn)程執(zhí)行或并行執(zhí)行時(shí)，函數(shù)不得讀取或?qū)懭氤绦虬械淖兞俊?從 SELECT， VALUES 或 SET 子句調(diào)用的函數(shù)可以寫入程序包變量，所有其他子句中的函數(shù)均不能寫入程序包變量。 如果函數(shù)調(diào)用執(zhí)行 UPDATE 的存儲(chǔ)過程，則該函數(shù)不能在 SQL 語(yǔ)句中使用。 1.3 過程和函數(shù)的比較 過程和函數(shù)的比較 過 程 函 數(shù) 作為 PL/SQL 語(yǔ)句執(zhí)行 作為表達(dá)式的一部分調(diào)用 在規(guī)格說明中不包含 RETURN 子句 必須在規(guī)格說明中包含 RETURN 子句 不返回任何值 必須返回單個(gè)值 可以包含 RETURN 語(yǔ)句，但是與函數(shù)不同，它不能用于返回值 必須包含至少一條 RETURN 語(yǔ)句 1.4 自治 事務(wù)處理 自治事務(wù)允許你離開調(diào)用的事務(wù)上下文，執(zhí)行一個(gè)獨(dú)立的事務(wù)，然后返回調(diào)用的事務(wù)而不 會(huì)影響到調(diào)用事務(wù)的狀態(tài)。自治事務(wù)和調(diào)用事務(wù)不同，只有提交的事務(wù)才會(huì)在事務(wù)見共享。 以下的 PL/SQL 代碼塊可以定義為自治事務(wù)。 第 7章 子程序和程序包 - 12 - 12 存儲(chǔ)過程和函數(shù) 定義在聲明塊里的本地存儲(chǔ)過程和函數(shù) 打包的存儲(chǔ)過程和函數(shù) 類型方法 頂層的匿名塊 最簡(jiǎn)單的理解自治事務(wù)的方法是查看他們的行為。我們創(chuàng)建一個(gè)測(cè)試表格，然后放入 2 行數(shù)據(jù)，注意數(shù)據(jù)沒有提交。 CREATE TABLE at_test ( id NUMBER NOT NULL, description VARCHAR2(50) NOT NULL )； INSERT INTO at_test (id, description) VALUES (1, Description for 1)； INSERT INTO at_test (id, description) VALUES (2, Description for 2)； SELECT * FROM at_test； ID DESCRIPTION - - 1 Description for 1 2 Description for 2 2 rows selected. 下一步，我們使用匿名的自治事務(wù)塊插入另外 8 行數(shù)據(jù)，同時(shí)提交。 DECLARE PRAGMA AUTONOMOUS_TRANSACTION； BEGIN FOR i IN 3 . 10 LOOP INSERT INTO at_test (id, description) VALUES (i, Description for | i)； END LOOP； COMMIT； END； / PL/SQL procedure successfully completed. SELECT * FROM at_test； ID DESCRIPTION - - 1 Description for 1 2 Description for 2 3 Description for 3 4 Description for 4 5 Description for 5 6 Description for 6 7 Description for 7 8 Description for 8 第 7章 子程序和程序包 - 13 - 13 9 Description for 9 10 Description for 10 10 rows selected. 和預(yù)想的一樣，我們得到了 10 行數(shù)據(jù)。如果我們執(zhí)行一個(gè)回滾 (rollback)語(yǔ)句，我們得到了如下的結(jié)果 ROLLBACK； SELECT * FROM at_test； ID DESCRIPTION - - 3 Description for 3 4 Description for 4 5 Description for 5 6 Description for 6 7 Description for 7 8 Description for 8 9 Description for 9 10 Description for 10 8 rows selected. 被我們當(dāng)前事務(wù)插入的 2 行數(shù)據(jù)被回滾了，而被自治事務(wù)插入的數(shù)據(jù)繼續(xù)存在。編譯描述符 PRAGMA AUTONOMOUS_TRANSACTION 使得自治塊在自己的事務(wù)里運(yùn)行，所以內(nèi)部的提交語(yǔ)句不會(huì)影響調(diào)用方的事務(wù) 自治事務(wù)一般用于日常的錯(cuò)誤日志，錯(cuò)誤信息必須保留，無(wú)論事務(wù)是提交還是回滾。例如下面的表保存了基本的錯(cuò)誤信息。 CREATE TABLE error_logs ( id NUMBER(10) NOT NULL, log_timestamp TIMESTAMP NOT NULL, error_message VARCHAR2(4000), CONSTRAINT error_logs_pk PRIMARY KEY (id) )； CREATE SEQUENCE error_logs_seq； 我們定義了一個(gè)自治事務(wù)的存儲(chǔ)過程來記錄錯(cuò)誤信息 CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION； BEGIN INSERT INTO error_logs (id, log_timestamp, error_message) VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message)； COMMIT； END； / 下面的代碼強(qiáng)制產(chǎn)生一個(gè)錯(cuò)誤 ,被捕獲且記錄到日志 。 BEGIN 第 7章 子程序和程序包 - 14 - 14 INSERT INTO at_test (id, description) VALUES (998, Description for 998)； - Force invalid insert. INSERT INTO at_test (id, description) VALUES (999, NULL)； EXCEPTION WHEN OTHERS THEN log_errors (p_error_message = SQLERRM)； ROLLBACK； END； / PL/SQL procedure successfully completed. SELECT * FROM at_test WHERE id = 998； no rows selected SELECT * FROM error_logs； ID LOG_TIMESTAMP - - ERROR_MESSAGE - 1 28-FEB-2006 11:10:10.107625 ORA-01400: cannot insert NULL into (TIM_HALL.AT_TEST.DESCRIPTION) 1 row selected. 由此可知， LOG_ERRORS 事務(wù)被分割為自治塊。如果不是，我們可以期待在自治塊插入的第一個(gè)數(shù)據(jù)被 LOG_ERRORS 存儲(chǔ)過程的提交語(yǔ)句后保留。 小心你使用自治存儲(chǔ)過程的方式。如果你胡亂使用，可能會(huì)引起死鎖，同時(shí)在分析跟蹤事務(wù)時(shí)引起沖突 。 自治 事務(wù)處理的特征： 與主事務(wù)處理的狀態(tài)無(wú)關(guān) 提交或回滾操作不影響主事務(wù)處理 自治事務(wù)處 理的結(jié)果對(duì)其他事務(wù)是可見的 能夠啟動(dòng)其他自主事務(wù)處理 2. 程序包 程序包是 一種數(shù)據(jù)庫(kù)對(duì)象，它是 對(duì)相關(guān)過程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝 。 程序包由 程序包 規(guī)范和 程序包 主體兩部分組成 。在程序包規(guī)范中，可以聲明類型，變量，常量，異常，游標(biāo)和子程序。 程序包主體用于實(shí)現(xiàn)在程序包規(guī)范中定義的游標(biāo)，子程序。 第 7章 子程序和程序包 - 15 - 15 程序包的組成部分如下圖所示： 2.1 程序包規(guī)范 程序包規(guī)范包含一些應(yīng)用程序可見的公共對(duì)象和類型，是與應(yīng)用程序的接口。規(guī)范包含應(yīng)用程序所需的程序包資源。如果程序包規(guī)范只聲明類型，常量，變量和異常，則不需要有程序包 主體，因?yàn)槭褂妙愋?，常量，變量和異常所需的所有信息均已在?guī)范中指定。只有子程序和游標(biāo)才具有底層實(shí)現(xiàn)或定義，因此他們必須有程序包主體。 創(chuàng)建包規(guī)范的語(yǔ)法 如下 ： CREATE OR REPLACE PACKAGE package_name IS|AS Public type and item declarations Subprogram specifications END package_name； 其中： package_name 是包的名稱。 Public type and item declarations 是聲明類型、常量、變量、異常和游標(biāo)等。 Subprogram specifications 聲明 PL/SQL 子程序。 在程序包規(guī)范中聲明的項(xiàng)也可以在程序包之外使用， 這樣的項(xiàng)稱為“公用對(duì)象” 例 14 演示如何創(chuàng)建程序包規(guī)范 例 14： CREATE OR REPLACE PACKAGE pack_me IS PROCEDURE order_proc (orno VARCHAR2)； FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2； END pack_me； / 該例中創(chuàng)建了 pack_me 包，在此程序包中，聲明了子程序 order_proc 和 order_fun，這些子程序的實(shí)現(xiàn)定義在程序包主體中給出。 2.2 程序包主體 第 7章 子程序和程序包 - 16 - 16 程序包主體包含在程序包規(guī)范中聲明的每個(gè)游標(biāo)和子程序的具體實(shí)現(xiàn)。私有聲明也可以包含在程序包主體中。 程序包主體的初始化部分是可選的，它可以用于初始化程序包中的變量。程序包的初始化部分既不能調(diào)用程序包，也不能將參數(shù)傳遞給程序包。 而且，程序包的初始化部分僅運(yùn)行一次。 以下是創(chuàng)建程序包主體的語(yǔ)法： CREATE OR REPLACE PACKAGE BODY package_name IS|AS Public type and item declarations Subprogram bodies BEGIN Initialization_statements END package_name； 其中： package_name 是包的名稱。 Public type and item declarations 聲明變量，常量，游標(biāo)，異常或類型 。 Subprogram bodies 定義公共和私有 PL/SQL 子程序。 例 15 演示如何創(chuàng)建 pack_me 包的主體。 例 15： CREATE OR REPLACE PACKAGE BODY pack_me AS PROCEDURE order_proc (orno VARCHAR2) IS stat CHAR(1)； BEGIN SELECT ostatus INTO stat FROM order_master WHERE orderno = orno； IF stat = p THEN DBMS_OUTPUT.PUT_LINE(暫掛的訂單 )； ELSE DBMS_OUTPUT.PUT_LINE(已完成的訂單 )； END IF； END order_proc； FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2 IS icode VARCHAR2(5)； ocode VARCHAR2(5)； qtyord NUMBER； qtydeld NUMBER； BEGIN SELECT qty_ord, qty_deld, itemcode, orderno INTO qtyord, qtydeld, icode, ocode FROM order_detail WHERE orderno = ornos； IF qtyord qtydeld THEN RETURN ocode； ELSE RETURN icode； END IF； END order_fun； END pack_me； 第 7章 子程序和程序包 - 17 - 17 / 提示 : 如果創(chuàng)建包規(guī)范或主體時(shí)出現(xiàn)編譯錯(cuò)誤，請(qǐng)輸入 SHOW ERRORS 命令查看詳細(xì)的錯(cuò)誤報(bào)告。 要引用在程序包規(guī)范中聲明的類型，對(duì)象和子程序，請(qǐng)使用以下語(yǔ)法： package_name.type_name； package_name.object_name； package_name.subprogram_name； 也可以從數(shù)據(jù)庫(kù)觸發(fā)器，匿名的 PL/SQL 塊，存儲(chǔ)子程序和 Oracle 工具中（ SQL*Plus）引用程序報(bào)內(nèi)容。存儲(chǔ)子程序可以調(diào)用程序包的子程序。 要執(zhí)行 packe_me 包中的 order_proc 過程，請(qǐng)輸入以下命令。 EXECUTE pack_me.order_proc(o002)； 上述語(yǔ)句的輸出結(jié)果如下所示： 為了執(zhí)行在程序包中定義的函數(shù)，可以使用如下代碼快： DECLARE msg VARCHAR2(10)； BEGIN msg := pack_me.order_fun(o002)； DBMS_OUTPUT.PUT_LINE(值是 | msg)； END； / 2.3 程序包的優(yōu)點(diǎn)： 程序包將相關(guān)的功能在邏輯上組織在一起，包 比 單獨(dú) 的 過程具有更大的優(yōu) 勢(shì)。包 的 優(yōu)點(diǎn)包括： 模塊化 使用程序包，可以封裝相關(guān)的類型，對(duì)象和子程序，每個(gè)程序包均將幫助我們以更好 的方式理解應(yīng)用程序中涉及的概念。 更輕松的應(yīng)用程序設(shè)計(jì) 要設(shè)計(jì)應(yīng)用程序，必須首先在程序包規(guī)范中指定對(duì)象，類型或子程序。編譯完規(guī)格說明之后，可以編譯引用程序包的存儲(chǔ) 子程序。因此，在準(zhǔn)備 好應(yīng)用程序的規(guī)格說明之前，不需要完整定義程序包主體。也就是說，可以在沒有主體的情況下編寫程序包規(guī)范的代碼并進(jìn)行編譯。 信息隱藏 前面我們介紹了私有對(duì)象和公有對(duì)象。這些對(duì)象還可以用于保護(hù)程序包的完整性。假如一個(gè)程序包包含 4 個(gè)子程序 （ 3 個(gè)公有子程序和 1 個(gè)私有子程序），程序 包 隱藏私有 子 程序的定義，所以在其定義改變時(shí)，只有該程序包（而不是應(yīng)用程序）受到影響。這樣，實(shí)現(xiàn)的細(xì)節(jié)對(duì)其它用戶不可見，因此保護(hù)了程序包的完整性。 新增功能 允許用戶在同一個(gè)包中創(chuàng)建多個(gè)同名的過程，過程參數(shù)的數(shù)量或數(shù)據(jù)類型可以不同。允許創(chuàng)建可在包 的 所有過程和函數(shù)中使用的全局變量和游標(biāo)。 第 7章 子程序和程序包 - 18 - 18 性能更佳 首次調(diào)用打包 的 子程序時(shí)，整個(gè)程序包均加載到內(nèi)存中，因此，后續(xù)調(diào)用不需要磁盤 I/O。此外，如果更改已打包函數(shù)的定義，則 Oracle 不需要重新編譯調(diào)用子程序 ，因?yàn)樗鼈儾灰蕾囉谧映?序的主體。 2.4 私有項(xiàng)與公有項(xiàng) 可以從其它程序和 PL/SQL 塊引用包 的 公有元素。這些公有元素在規(guī)范中定義。私有元素是在程序包主體中定義的，并不出現(xiàn)在規(guī)范中。私有元素不能在程序包之外引用。 程序包中的任何其它元素均可以使用私有元素。 如果必須使私有元素變?yōu)楣玫模瑒t可以將該項(xiàng)添加到程序包規(guī)范中，然后重新編譯該規(guī)范，操作完成后，該項(xiàng)在程序包之外將是可見的。 對(duì)于程序包元素的引用，請(qǐng)使用點(diǎn)分表示法。先后順序?yàn)椋撼绦虬Q，點(diǎn)，元素名稱。同一程序包不需要使用點(diǎn)分表示法。 在上面有關(guān)程序包規(guī)范和程序包主體的創(chuàng)建的例子 中。在規(guī)范的例子中，沒有聲明變量和游標(biāo)，因此沒有可以由其他程序包引用的公用變量或游標(biāo)。但是，在程序包主體的例子中，過程order_proc 中定義了一個(gè)變量 stat,這個(gè)變量被認(rèn)為是私有變量，此變量只能在過程內(nèi)訪問。 2.5 程序包中的游標(biāo) 在程序包中可以定義和使用游標(biāo)， 游標(biāo)的定義分為游標(biāo)規(guī)范和游標(biāo)主體兩部分 。在更改游標(biāo)主體時(shí)，無(wú)需改變游標(biāo)規(guī)范。此外， 在包規(guī)范中聲明游標(biāo)規(guī)范時(shí)必須使用 RETURN 子句指定游標(biāo)的返回類型 。 RETURN 子句指示從游標(biāo)獲取并返回的數(shù)據(jù)元素。實(shí)際上，這些數(shù)據(jù)元素由該游標(biāo)的 SELECT語(yǔ)句確定。但是， SELECT 語(yǔ)句僅出現(xiàn)在主體中，而不出現(xiàn)在規(guī)范中。游標(biāo)規(guī)范必須包含程序使用游標(biāo)所需的所有信息。因此需要返回?cái)?shù)據(jù)類型。 RETURN 子句指定的數(shù)據(jù)類型可以是： 用 %ROWTYPE 屬性引用表定義的記錄類型 程序員定義的記錄類型 例 16 演示如何在程序包中聲明游標(biāo)和使用游標(biāo)。 例 16： CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE； PROCEDURE ord_pro(vcode VARCHAR2)； END cur_pack； / 例 16 在 cur_pack 包中為 order_master 表創(chuàng)建和定義 ord_cur 游標(biāo)，還創(chuàng)建了 ord_pro 過程。 請(qǐng)輸入以下代碼創(chuàng)建 cur_pack 包的主體 CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS 第 7章 子程序和程序包 - 19 - 19 SELECT * FROM order_master WHERE VENCODE=vcode； PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE； BEGIN OPEN ord_cur(vcode)； LOOP FETCH ord_cur INTO or_rec； EXIT WHEN ord_cur%NOTFOUND； DBMS_OUTPUT.PUT_LIne(返回的值為 | or_rec.orderno)； END LOOP； END ord_pro； END cur_pack； / 要執(zhí)行 ord_pro 過程，請(qǐng)輸入以下命令 。 EXEC cur_pack.ord_pro(V001)； 以上代碼用供應(yīng)商編號(hào) v001 打開 cur_pack 包中的 ord_pro 過程。 輸出結(jié)果如下： 3. 有關(guān)子程序和程序包的信息 子程序和程序包是數(shù)據(jù)庫(kù)中存儲(chǔ)的對(duì)象， Oracle 會(huì)在數(shù)據(jù)字典中存儲(chǔ)所有對(duì)象的信息。通過查詢 USER_OBJECTS 數(shù)據(jù)字典視圖，可以獲取有關(guān)在會(huì)話中創(chuàng)建的子程序和程序包的信息。 例 17 的代碼 可以查詢有關(guān)過程，函數(shù)和程序包的信息。 例 17： COLUMN OBJECT_NAME FORMAT A18 SELECT object_name, object_type FROM USER_OBJECTS WHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY)； 輸出結(jié)果如下： OBJECT_NAME OBJECT_TYPE - - FIND_EMP PROCEDURE GETDEPTCOUNT PROCEDURE ITEMDESC PROCEDURE TEST PROCEDURE GETSALARYBYEMPNO PROCEDURE 第 7章 子程序和程序包 - 20 - 20 SWAP PROCEDURE GETSALARYBYEMPNO1 PROCEDURE ADDEMP PROCEDURE FUN_HELLO FUNCTION ITEM_PRICE_RANGE FUNCTION CURRENTTIME FUNCTION GETEMPNAMEBYEMPNO FUNCTION GETEMPNAMEANDJOBBY FUNCTION EMPNO GETSALARYBYEMPNO0 PROCEDURE GETHIREDATEANDJOBB FUNCTION YEMPNAME LOG_ERRORS PROCEDURE PACK_ME PACKAGE PACK_ME PACKAGE BODY CUR_PACK PACKAGE CUR_PACK PACKAGE BODY 通過查詢 USER_SOURCE 數(shù)據(jù)字典視圖，可以獲取存儲(chǔ)子過程的文本。該視圖的結(jié)構(gòu)如下所示： 例 18： DESC USER_SOURCE 輸出結(jié)果如下： Name Type Nullable Default Comments - - - - - NAME VARCHAR2(30) Y Name of the object TYPE VARCHAR2(12) Y Type of the object: CTION, PACKAGE, PACKAGE BODY or JAVA SOURCE LINE NUMBER Y Line number of this line of source TEXT VARCHAR