|
認(rèn)識(shí)存儲(chǔ)過(guò)程和函數(shù)
存儲(chǔ)過(guò)程和函數(shù)也是一種PL/SQL塊,，是存入數(shù)據(jù)庫(kù)的PL/SQL塊。但存儲(chǔ)過(guò)程和函數(shù)不同于已經(jīng)介紹過(guò)的PL/SQL程序,，我們通常把PL/SQL程序稱為無(wú)名塊,，而存儲(chǔ)過(guò)程和函數(shù)是以命名的方式存儲(chǔ)于數(shù)據(jù)庫(kù)中的,。和PL/SQL程序相比，存儲(chǔ)過(guò)程有很多優(yōu)點(diǎn),，具體歸納如下： * 存儲(chǔ)過(guò)程和函數(shù)以命名的數(shù)據(jù)庫(kù)對(duì)象形式存儲(chǔ)于數(shù)據(jù)庫(kù)當(dāng)中,。存儲(chǔ)在數(shù)據(jù)庫(kù)中的優(yōu)點(diǎn)是很明顯的，因?yàn)榇a不保存在本地,，用戶可以在任何客戶機(jī)上登錄到數(shù)據(jù)庫(kù),，并調(diào)用或修改代碼。 * 存儲(chǔ)過(guò)程和函數(shù)可由數(shù)據(jù)庫(kù)提供安全保證,，要想使用存儲(chǔ)過(guò)程和函數(shù),，需要有存儲(chǔ)過(guò)程和函數(shù)的所有者的授權(quán)，只有被授權(quán)的用戶或創(chuàng)建者本身才能執(zhí)行存儲(chǔ)過(guò)程或調(diào)用函數(shù),。 * 存儲(chǔ)過(guò)程和函數(shù)的信息是寫入數(shù)據(jù)字典的,，所以存儲(chǔ)過(guò)程可以看作是一個(gè)公用模塊，用戶編寫的PL/SQL程序或其他存儲(chǔ)過(guò)程都可以調(diào)用它(但存儲(chǔ)過(guò)程和函數(shù)不能調(diào)用PL/SQL程序),。一個(gè)重復(fù)使用的功能,，可以設(shè)計(jì)成為存儲(chǔ)過(guò)程，比如：顯示一張工資統(tǒng)計(jì)表,，可以設(shè)計(jì)成為存儲(chǔ)過(guò)程,；一個(gè)經(jīng)常調(diào)用的計(jì)算，可以設(shè)計(jì)成為存儲(chǔ)函數(shù),；根據(jù)雇員編號(hào)返回雇員的姓名,，可以設(shè)計(jì)成存儲(chǔ)函數(shù)。 * 像其他高級(jí)語(yǔ)言的過(guò)程和函數(shù)一樣,，可以傳遞參數(shù)給存儲(chǔ)過(guò)程或函數(shù),，參數(shù)的傳遞也有多種方式。存儲(chǔ)過(guò)程可以有返回值,，也可以沒(méi)有返回值,，存儲(chǔ)過(guò)程的返回值必須通過(guò)參數(shù)帶回；函數(shù)有一定的數(shù)據(jù)類型,，像其他的標(biāo)準(zhǔn)函數(shù)一樣,，我們可以通過(guò)對(duì)函數(shù)名的調(diào)用返回函數(shù)值。 存儲(chǔ)過(guò)程和函數(shù)需要進(jìn)行編譯,，以排除語(yǔ)法錯(cuò)誤,，只有編譯通過(guò)才能調(diào)用。 創(chuàng)建和刪除存儲(chǔ)過(guò)程 創(chuàng)建存儲(chǔ)過(guò)程,，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限,。該權(quán)限可由系統(tǒng)管理員授予。創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的基本語(yǔ)句如下： CREATE [OR REPLACE] PROCEDURE 存儲(chǔ)過(guò)程名[(參數(shù)[IN|OUT|IN OUT] 數(shù)據(jù)類型...)] {AS|IS} [說(shuō)明部分] BEGIN 可執(zhí)行部分 [EXCEPTION 錯(cuò)誤處理部分] END [過(guò)程名]; 其中： 可選關(guān)鍵字OR REPLACE 表示如果存儲(chǔ)過(guò)程已經(jīng)存在，則用新的存儲(chǔ)過(guò)程覆蓋,，通常用于存儲(chǔ)過(guò)程的重建,。 參數(shù)部分用于定義多個(gè)參數(shù)(如果沒(méi)有參數(shù)，就可以省略),。參數(shù)有三種形式：IN,、OUT和IN OUT。如果沒(méi)有指明參數(shù)的形式,，則默認(rèn)為IN,。 關(guān)鍵字AS也可以寫成IS，后跟過(guò)程的說(shuō)明部分,，可以在此定義過(guò)程的局部變量,。 編寫存儲(chǔ)過(guò)程可以使用任何文本編輯器或直接在SQL*Plus環(huán)境下進(jìn)行，編寫好的存儲(chǔ)過(guò)程必須要在SQL*Plus環(huán)境下進(jìn)行編譯,，生成編譯代碼,，原代碼和編譯代碼在編譯過(guò)程中都會(huì)被存入數(shù)據(jù)庫(kù)。編譯成功的存儲(chǔ)過(guò)程就可以在Oracle環(huán)境下進(jìn)行調(diào)用了,。 一個(gè)存儲(chǔ)過(guò)程在不需要時(shí)可以刪除,。刪除存儲(chǔ)過(guò)程的人是過(guò)程的創(chuàng)建者或者擁有DROP ANY PROCEDURE系統(tǒng)權(quán)限的人。刪除存儲(chǔ)過(guò)程的語(yǔ)法如下： DROP PROCEDURE 存儲(chǔ)過(guò)程名,； 如果要重新編譯一個(gè)存儲(chǔ)過(guò)程,，則只能是過(guò)程的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人。語(yǔ)法如下： ALTER PROCEDURE 存儲(chǔ)過(guò)程名 COMPILE,； 執(zhí)行(或調(diào)用)存儲(chǔ)過(guò)程的人是過(guò)程的創(chuàng)建者或是擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE權(quán)限的人,。執(zhí)行的方法如下： 方法1： EXECUTE 模式名.存儲(chǔ)過(guò)程名[(參數(shù)...)]; 方法2： BEGIN 模式名.存儲(chǔ)過(guò)程名[(參數(shù)...)]; END; 傳遞的參數(shù)必須與定義的參數(shù)類型、個(gè)數(shù)和順序一致(如果參數(shù)定義了默認(rèn)值,，則調(diào)用時(shí)可以省略參數(shù)),。參數(shù)可以是變量、常量或表達(dá)式,，用法參見(jiàn)下一節(jié),。 如果是調(diào)用本賬戶下的存儲(chǔ)過(guò)程，則模式名可以省略,。要調(diào)用其他賬戶編寫的存儲(chǔ)過(guò)程,，則模式名必須要添加,。 以下是一個(gè)生成和調(diào)用簡(jiǎn)單存儲(chǔ)過(guò)程的訓(xùn)練,。注意要事先授予創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限。 【訓(xùn)練1】 創(chuàng)建一個(gè)顯示雇員總?cè)藬?shù)的存儲(chǔ)過(guò)程,。 步驟1：登錄SCOTT賬戶(或?qū)W生個(gè)人賬戶),。 步驟2：在SQL*Plus輸入?yún)^(qū)中，輸入以下存儲(chǔ)過(guò)程： Sql代碼
CREATE OR REPLACE PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM EMP;
DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)為：'||V_TOTAL);
END;
步驟3：按“執(zhí)行”按鈕進(jìn)行編譯。 如果存在錯(cuò)誤,，就會(huì)顯示: 警告: 創(chuàng)建的過(guò)程帶有編譯錯(cuò)誤,。 如果存在錯(cuò)誤，對(duì)腳本進(jìn)行修改,，直到?jīng)]有錯(cuò)誤產(chǎn)生,。 如果編譯結(jié)果正確，將顯示： Sql代碼
過(guò)程已創(chuàng)建,。 步驟4：調(diào)用存儲(chǔ)過(guò)程，在輸入?yún)^(qū)中輸入以下語(yǔ)句并執(zhí)行： Sql代碼
EXECUTE EMP_COUNT; 顯示結(jié)果為： Sql代碼
雇員總?cè)藬?shù)為：14 PL/SQL 過(guò)程已成功完成,。 說(shuō)明：在該訓(xùn)練中，V_TOTAL變量是存儲(chǔ)過(guò)程定義的局部變量,，用于接收查詢到的雇員總?cè)藬?shù),。 注意：在SQL*Plus中輸入存儲(chǔ)過(guò)程，按“執(zhí)行”按鈕是進(jìn)行編譯,，不是執(zhí)行存儲(chǔ)過(guò)程,。 如果在存儲(chǔ)過(guò)程中引用了其他用戶的對(duì)象，比如表,，則必須有其他用戶授予的對(duì)象訪問(wèn)權(quán)限,。一個(gè)存儲(chǔ)過(guò)程一旦編譯成功，就可以由其他用戶或程序來(lái)引用,。但存儲(chǔ)過(guò)程或函數(shù)的所有者必須授予其他用戶執(zhí)行該過(guò)程的權(quán)限,。 存儲(chǔ)過(guò)程沒(méi)有參數(shù)，在調(diào)用時(shí),，直接寫過(guò)程名即可,。 【訓(xùn)練2】 在PL/SQL程序中調(diào)用存儲(chǔ)過(guò)程。 步驟1：登錄SCOTT賬戶,。 步驟2：授權(quán)STUDENT賬戶使用該存儲(chǔ)過(guò)程,，即在SQL*Plus輸入?yún)^(qū)中，輸入以下的命令： Sql代碼
GRANT EXECUTE ON EMP_COUNT TO STUDENT Sql代碼
授權(quán)成功,。 步驟3：登錄STUDENT賬戶，在SQL*Plus輸入?yún)^(qū)中輸入以下程序： Sql代碼
SET SERVEROUTPUT ON BEGIN SCOTT.EMP_COUNT; END; 步驟4：執(zhí)行以上程序,，結(jié)果為： Sql代碼
雇員總?cè)藬?shù)為：14 PL/SQL 過(guò)程已成功完成。 說(shuō)明：在本例中,，存儲(chǔ)過(guò)程是由SCOTT賬戶創(chuàng)建的,，STUDEN賬戶獲得SCOTT賬戶的授權(quán)后,，才能調(diào)用該存儲(chǔ)過(guò)程。 注意：在程序中調(diào)用存儲(chǔ)過(guò)程，使用了第二種語(yǔ)法。 【訓(xùn)練3】 編寫顯示雇員信息的存儲(chǔ)過(guò)程EMP_LIST,，并引用EMP_COUNT存儲(chǔ)過(guò)程。 步驟1：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲(chǔ)過(guò)程： Sql代碼
CREATE OR REPLACE PROCEDURE EMP_LIST AS CURSOR emp_cursor IS SELECT empno,ename FROM emp; BEGIN FOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename); END LOOP; EMP_COUNT; END; 執(zhí)行結(jié)果： Sql代碼
過(guò)程已創(chuàng)建。 步驟2：調(diào)用存儲(chǔ)過(guò)程,，在輸入?yún)^(qū)中輸入以下語(yǔ)句并執(zhí)行： Sql代碼
EXECUTE EMP_LIST 顯示結(jié)果為： Sql代碼
7369SMITH 7499ALLEN 7521WARD 7566JONES 執(zhí)行結(jié)果： 雇員總?cè)藬?shù)為：14 PL/SQL 過(guò)程已成功完成。 說(shuō)明：以上的EMP_LIST存儲(chǔ)過(guò)程中定義并使用了游標(biāo),，用來(lái)循環(huán)顯示所有雇員的信息,。然后調(diào)用已經(jīng)成功編譯的存儲(chǔ)過(guò)程EMP_COUNT，用來(lái)附加顯示雇員總?cè)藬?shù),。通過(guò)EXECUTE命令來(lái)執(zhí)行EMP_LIST存儲(chǔ)過(guò)程,。 【練習(xí)1】編寫顯示部門信息的存儲(chǔ)過(guò)程DEPT_LIST，要求統(tǒng)計(jì)出部門個(gè)數(shù),。 參數(shù)傳遞 參數(shù)的作用是向存儲(chǔ)過(guò)程傳遞數(shù)據(jù),，或從存儲(chǔ)過(guò)程獲得返回結(jié)果。正確的使用參數(shù)可以大大增加存儲(chǔ)過(guò)程的靈活性和通用性,。 參數(shù)的類型有三種,，如下所示。 Sql代碼
IN 定義一個(gè)輸入?yún)?shù)變量,，用于傳遞參數(shù)給存儲(chǔ)過(guò)程 OUT 定義一個(gè)輸出參數(shù)變量，用于從存儲(chǔ)過(guò)程獲取數(shù)據(jù) IN OUT 定義一個(gè)輸入,、輸出參數(shù)變量,，兼有以上兩者的功能 參數(shù)的定義形式和作用如下： 參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值； 定義一個(gè)輸入?yún)?shù)變量,，用于傳遞參數(shù)給存儲(chǔ)過(guò)程,。在調(diào)用存儲(chǔ)過(guò)程時(shí)，主程序的實(shí)際參數(shù)可以是常量,、有值變量或表達(dá)式等,。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來(lái)設(shè)定參數(shù)的默認(rèn)值,。如果在調(diào)用存儲(chǔ)過(guò)程時(shí)不指明參數(shù),，則參數(shù)變量取默認(rèn)值,。在存儲(chǔ)過(guò)程中,，輸入變量接收主程序傳遞的值,，但不能對(duì)其進(jìn)行賦值。 參數(shù)名 OUT 數(shù)據(jù)類型,； 定義一個(gè)輸出參數(shù)變量,，用于從存儲(chǔ)過(guò)程獲取數(shù)據(jù)，即變量從存儲(chǔ)過(guò)程中返回值給主程序,。 在調(diào)用存儲(chǔ)過(guò)程時(shí),，主程序的實(shí)際參數(shù)只能是一個(gè)變量，而不能是常量或表達(dá)式,。在存儲(chǔ)過(guò)程中,，參數(shù)變量只能被賦值而不能將其用于賦值，在存儲(chǔ)過(guò)程中必須給輸出變量至少賦值一次,。 參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值,； 定義一個(gè)輸入、輸出參數(shù)變量,，兼有以上兩者的功能,。在調(diào)用存儲(chǔ)過(guò)程時(shí)，主程序的實(shí)際參數(shù)只能是一個(gè)變量,，而不能是常量或表達(dá)式,。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來(lái)設(shè)定參數(shù)的默認(rèn)值,。在存儲(chǔ)過(guò)程中,，變量接收主程序傳遞的值，同時(shí)可以參加賦值運(yùn)算,，也可以對(duì)其進(jìn)行賦值,。在存儲(chǔ)過(guò)程中必須給變量至少賦值一次。 如果省略IN,、OUT或IN OUT,，則默認(rèn)模式是IN。 【訓(xùn)練1】 編寫給雇員增加工資的存儲(chǔ)過(guò)程CHANGE_SALARY,，通過(guò)IN類型的參數(shù)傳遞要增加工資的雇員編號(hào)和增加的工資額,。 步驟1：登錄SCOTT賬戶。 步驟2：在SQL*Plus輸入?yún)^(qū)中輸入以下存儲(chǔ)過(guò)程并執(zhí)行： Sql代碼
CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
AS
V_ENAME VARCHAR2(10);
V_SAL NUMBER(5);
BEGIN
SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('雇員'||V_ENAME||'的工資被改為'||TO_CHAR(V_SAL+P_RAISE));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('發(fā)生錯(cuò)誤,，修改失??！');
ROLLBACK;
END;
執(zhí)行結(jié)果為： Sql代碼
過(guò)程已創(chuàng)建,。 步驟3：調(diào)用存儲(chǔ)過(guò)程,，在輸入?yún)^(qū)中輸入以下語(yǔ)句并執(zhí)行： Sql代碼
EXECUTE CHANGE_SALARY(7788,80) 顯示結(jié)果為： Sql代碼
雇員SCOTT的工資被改為3080 說(shuō)明：從執(zhí)行結(jié)果可以看到，雇員SCOTT的工資已由原來(lái)的3000改為3080,。 參數(shù)的值由調(diào)用者傳遞,，傳遞的參數(shù)的個(gè)數(shù)、類型和順序應(yīng)該和定義的一致,。如果順序不一致,，可以采用以下調(diào)用方法,。如上例，執(zhí)行語(yǔ)句可以改為： EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788); 可以看出傳遞參數(shù)的順序發(fā)生了變化,，并且明確指出了參數(shù)名和要傳遞的值,，=>運(yùn)算符左側(cè)是參數(shù)名，右側(cè)是參數(shù)表達(dá)式,，這種賦值方法的意義較清楚,。 【練習(xí)1】創(chuàng)建插入雇員的存儲(chǔ)過(guò)程INSERT_EMP，并將雇員編號(hào)等作為參數(shù),。 在設(shè)計(jì)存儲(chǔ)過(guò)程的時(shí)候,，也可以為參數(shù)設(shè)定默認(rèn)值，這樣調(diào)用者就可以不傳遞或少傳遞參數(shù)了,。 【訓(xùn)練2】 調(diào)用存儲(chǔ)過(guò)程CHANGE_SALARY,，不傳遞參數(shù)，使用默認(rèn)參數(shù)值,。 在SQL*Plus輸入?yún)^(qū)中輸入以下命令并執(zhí)行： Sql代碼
EXECUTE CHANGE_SALARY 顯示結(jié)果為： Sql代碼
雇員SCOTT的工資被改為3090 說(shuō)明：在存儲(chǔ)過(guò)程的調(diào)用中沒(méi)有傳遞參數(shù)，而是采用了默認(rèn)值7788和10,，即默認(rèn)雇員號(hào)為7788，增加的工資為10,。 【訓(xùn)練3】 使用OUT類型的參數(shù)返回存儲(chǔ)過(guò)程的結(jié)果,。 步驟1：登錄SCOTT賬戶。 步驟2：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲(chǔ)過(guò)程： Sql代碼
CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO P_TOTAL FROM EMP; END; 執(zhí)行結(jié)果為： Sql代碼
過(guò)程已創(chuàng)建,。 步驟3：輸入以下程序并執(zhí)行： Sql代碼
DECLARE
V_EMPCOUNT NUMBER;
BEGIN
EMP_COUNT(V_EMPCOUNT);
DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)為：'||V_EMPCOUNT);
END;
顯示結(jié)果為： Sql代碼
雇員總?cè)藬?shù)為：14 PL/SQL 過(guò)程已成功完成。 說(shuō)明：在存儲(chǔ)過(guò)程中定義了OUT類型的參數(shù)P_TOTAL,，在主程序調(diào)用該存儲(chǔ)過(guò)程時(shí)，傳遞了參數(shù)V_EMPCOUNT,。在存儲(chǔ)過(guò)程中的SELECT...INTO...語(yǔ)句中對(duì)P_TOTAL進(jìn)行賦值,，賦值結(jié)果由V_EMPCOUNT變量帶回給主程序并顯示。 以上程序要覆蓋同名的EMP_COUNT存儲(chǔ)過(guò)程,，如果不使用OR REPLACE選項(xiàng)，就會(huì)出現(xiàn)以下錯(cuò)誤： Sql代碼
ERROR 位于第 1 行: ORA-00955: 名稱已由現(xiàn)有對(duì)象使用,。 【練習(xí)2】創(chuàng)建存儲(chǔ)過(guò)程,，使用OUT類型參數(shù)獲得雇員經(jīng)理名,。 【訓(xùn)練4】 使用IN OUT類型的參數(shù)，給電話號(hào)碼增加區(qū)碼,。 步驟1：登錄SCOTT賬戶,。 步驟2：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲(chǔ)過(guò)程： Sql代碼
CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2) AS BEGIN P_HPONE_NUM:='0755-'||P_HPONE_NUM; END; 執(zhí)行結(jié)果為： Sql代碼
過(guò)程已創(chuàng)建,。 步驟3：輸入以下程序并執(zhí)行： Sql代碼
SET SERVEROUTPUT ON
DECLARE
V_PHONE_NUM VARCHAR2(15);
BEGIN
V_PHONE_NUM:='26731092';
ADD_REGION(V_PHONE_NUM);
DBMS_OUTPUT.PUT_LINE('新的電話號(hào)碼：'||V_PHONE_NUM);
END;
顯示結(jié)果為： Sql代碼
新的電話號(hào)碼：0755-26731092 PL/SQL 過(guò)程已成功完成。 說(shuō)明：變量V_HPONE_NUM既用來(lái)向存儲(chǔ)過(guò)程傳遞舊電話號(hào)碼,，也用來(lái)向主程序返回新號(hào)碼,。新的號(hào)碼在原來(lái)基礎(chǔ)上增加了區(qū)號(hào)0755和-,。 創(chuàng)建和刪除存儲(chǔ)函數(shù) 創(chuàng)建函數(shù),，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限。該權(quán)限可由系統(tǒng)管理員授予,。創(chuàng)建存儲(chǔ)函數(shù)的語(yǔ)法和創(chuàng)建存儲(chǔ)過(guò)程的類似,，即 CREATE [OR REPLACE] FUNCTION 函數(shù)名[(參數(shù)[IN] 數(shù)據(jù)類型...)] RETURN 數(shù)據(jù)類型 {AS|IS} [說(shuō)明部分] BEGIN 可執(zhí)行部分 RETURN (表達(dá)式) [EXCEPTION 錯(cuò)誤處理部分] END [函數(shù)名]; 其中，參數(shù)是可選的,，但只能是IN類型(IN關(guān)鍵字可以省略),。 在定義部分的RETURN 數(shù)據(jù)類型，用來(lái)表示函數(shù)的數(shù)據(jù)類型,，也就是返回值的類型,，此部分不可省略。 在可執(zhí)行部分的RETURN(表達(dá)式),，用來(lái)生成函數(shù)的返回值,，其表達(dá)式的類型應(yīng)該和定義部分說(shuō)明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個(gè)RETURN語(yǔ)句，但只有一個(gè)RETURN語(yǔ)句會(huì)被執(zhí)行,，一旦執(zhí)行了RETURN語(yǔ)句,，則函數(shù)結(jié)束并返回調(diào)用環(huán)境。 一個(gè)存儲(chǔ)函數(shù)在不需要時(shí)可以刪除,，但刪除的人應(yīng)是函數(shù)的創(chuàng)建者或者是擁有DROP ANY PROCEDURE系統(tǒng)權(quán)限的人,。其語(yǔ)法如下： DROP FUNCTION 函數(shù)名； 重新編譯一個(gè)存儲(chǔ)函數(shù)時(shí),，編譯的人應(yīng)是函數(shù)的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人,。重新編譯一個(gè)存儲(chǔ)函數(shù)的語(yǔ)法如下： ALTER PROCEDURE 函數(shù)名 COMPILE； 函數(shù)的調(diào)用者應(yīng)是函數(shù)的創(chuàng)建者或擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人,，或是被函數(shù)的擁有者授予了函數(shù)執(zhí)行權(quán)限的賬戶,。函數(shù)的引用和存儲(chǔ)過(guò)程不同，函數(shù)要出現(xiàn)在程序體中,，可以參加表達(dá)式的運(yùn)算或單獨(dú)出現(xiàn)在表達(dá)式中,，其形式如下： 變量名:=函數(shù)名(...) 【訓(xùn)練1】 創(chuàng)建一個(gè)通過(guò)雇員編號(hào)返回雇員名稱的函數(shù)GET_EMP_NAME,。 步驟1：登錄SCOTT賬戶,。 步驟2：在SQL*Plus輸入?yún)^(qū)中輸入以下存儲(chǔ)函數(shù)并編譯： Sql代碼
CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)
RETURN VARCHAR2
AS
V_ENAME VARCHAR2(10);
BEGIN
ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;
RETURN(V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('沒(méi)有該編號(hào)雇員！');
RETURN (NULL);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('有重復(fù)雇員編號(hào),！');
RETURN (NULL);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('發(fā)生其他錯(cuò)誤,！');
RETURN (NULL);
END;
步驟3：調(diào)用該存儲(chǔ)函數(shù)，輸入并執(zhí)行以下程序： Sql代碼
BEGIN
DBMS_OUTPUT.PUT_LINE('雇員7369的名稱是：'|| GET_EMP_NAME(7369));
DBMS_OUTPUT.PUT_LINE('雇員7839的名稱是：'|| GET_EMP_NAME(7839));
END;
顯示結(jié)果為： Sql代碼
雇員7369的名稱是：SMITH 雇員7839的名稱是：KING PL/SQL 過(guò)程已成功完成,。 說(shuō)明：函數(shù)的調(diào)用直接出現(xiàn)在程序的DBMS_OUTPUT.PUT_LINE語(yǔ)句中，作為字符串表達(dá)式的一部分,。如果輸入了錯(cuò)誤的雇員編號(hào),，就會(huì)在函數(shù)的錯(cuò)誤處理部分輸出錯(cuò)誤信息。試修改雇員編號(hào),，重新運(yùn)行調(diào)用部分,。 【練習(xí)1】創(chuàng)建一個(gè)通過(guò)部門編號(hào)返回部門名稱的存儲(chǔ)函數(shù)GET_DEPT_NAME。 【練習(xí)2】將函數(shù)的執(zhí)行權(quán)限授予STUDENT賬戶,，然后登錄STUDENT賬戶調(diào)用,。 存儲(chǔ)過(guò)程和函數(shù)的查看 可以通過(guò)對(duì)數(shù)據(jù)字典的訪問(wèn)來(lái)查詢存儲(chǔ)過(guò)程或函數(shù)的有關(guān)信息，如果要查詢當(dāng)前用戶的存儲(chǔ)過(guò)程或函數(shù)的源代碼,，可以通過(guò)對(duì)USER_SOURCE數(shù)據(jù)字典視圖的查詢得到,。USER_SOURCE的結(jié)構(gòu)如下： Sql代碼
DESCRIBE USER_SOURCE 結(jié)果為： Sql代碼
名稱 是否為空? 類型 ------------------------------------------------------------- ------------- ----------------------- NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) 說(shuō)明：里面按行存放著過(guò)程或函數(shù)的腳本，NAME是過(guò)程或函數(shù)名，TYPE 代表類型(PROCEDURE或FUNCTION),，LINE是行號(hào),，TEXT 為腳本。 【訓(xùn)練1】 查詢過(guò)程EMP_COUNT的腳本,。 在SQL*Plus中輸入并執(zhí)行如下查詢： Sql代碼
select TEXT from user_source WHERE NAME='EMP_COUNT'; 結(jié)果為： Sql代碼
TEXT -------------------------------------------------------------------------------- PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO P_TOTAL FROM EMP; END; 【訓(xùn)練2】 查詢過(guò)程GET_EMP_NAME的參數(shù),。 在SQL*Plus中輸入并執(zhí)行如下查詢： Sql代碼
DESCRIBE GET_EMP_NAME 結(jié)果為： Sql代碼
FUNCTION GET_EMP_NAME RETURNS VARCHAR2 參數(shù)名稱 類型 輸入/輸出默認(rèn)值,？ ----------------------------------------- ----------------------------------- ----------------- ------------- P_EMPNO NUMBER(4) IN DEFAULT 【訓(xùn)練3】 在發(fā)生編譯錯(cuò)誤時(shí),，顯示錯(cuò)誤。 Sql代碼
SHOW ERRORS 以下是一段編譯錯(cuò)誤顯示： Sql代碼
LINE/COL ERROR ------------- ----------------------------------------------------------------- 4/2 PL/SQL: SQL Statement ignored 4/36 PLS-00201: 必須說(shuō)明標(biāo)識(shí)符 'EMPP' 說(shuō)明：查詢一個(gè)存儲(chǔ)過(guò)程或函數(shù)是否是有效狀態(tài)(即編譯成功),，可以使用數(shù)據(jù)字典USER_OBJECTS的STATUS列,。 【訓(xùn)練4】 查詢EMP_LIST存儲(chǔ)過(guò)程是否可用： Sql代碼
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST'; 結(jié)果為： Sql代碼
STATUS ------------ VALID 說(shuō)明：VALID表示該存儲(chǔ)過(guò)程有效(即通過(guò)編譯)，INVALID表示存儲(chǔ)過(guò)程無(wú)效或需要重新編譯,。當(dāng)Oracle調(diào)用一個(gè)無(wú)效的存儲(chǔ)過(guò)程或函數(shù)時(shí),，首先試圖對(duì)其進(jìn)行編譯，如果編譯成功則將狀態(tài)置成VALID并執(zhí)行,，否則給出錯(cuò)誤信息,。 當(dāng)一個(gè)存儲(chǔ)過(guò)程編譯成功，狀態(tài)變?yōu)閂ALID,，會(huì)不會(huì)在某些情況下變成INVALID,。結(jié)論是完全可能的。比如一個(gè)存儲(chǔ)過(guò)程中包含對(duì)表的查詢,，如果表被修改或刪除,，存儲(chǔ)過(guò)程就會(huì)變成無(wú)效INVALID。所以要注意存儲(chǔ)過(guò)程和函數(shù)對(duì)其他對(duì)象的依賴關(guān)系,。 如果要檢查存儲(chǔ)過(guò)程或函數(shù)的依賴性,，可以通過(guò)查詢數(shù)據(jù)字典USER_DENPENDENCIES來(lái)確定，該表結(jié)構(gòu)如下： Sql代碼
DESCRIBE USER_DEPENDENCIES; 結(jié)果： Sql代碼
名稱 是否為空? 類型 -------------------------------------------------------------- ------------- ---------------------------- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12) REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4) 說(shuō)明：NAME為實(shí)體名,，TYPE為實(shí)體類型,，REFERENCED_OWNER為涉及到的實(shí)體擁有者賬戶，REFERENCED_NAME為涉及到的實(shí)體名,，REFERENCED_TYPE 為涉及到的實(shí)體類型,。 【訓(xùn)練5】 查詢EMP_LIST存儲(chǔ)過(guò)程的依賴性。 Sql代碼
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST'; 執(zhí)行結(jié)果： Sql代碼
REFERENCED_NAME REFERENCED_TYPE ------------------------------------------------------------------------------------------ ---------------------------- STANDARD PACKAGE SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE DBMS_OUTPUT PACKAGE DBMS_OUTPUT SYNONYM DBMS_OUTPUT NON-EXISTENT EMP TABLE EMP_COUNT PROCEDURE 說(shuō)明：可以看出存儲(chǔ)過(guò)程EMP_LIST依賴一些系統(tǒng)包,、EMP表和EMP_COUNT存儲(chǔ)過(guò)程,。如果刪除了EMP表或EMP_COUNT存儲(chǔ)過(guò)程，EMP_LIST將變成無(wú)效,。 還有一種情況需要我們注意：如果一個(gè)用戶A被授予執(zhí)行屬于用戶B的一個(gè)存儲(chǔ)過(guò)程的權(quán)限,，在用戶B的存儲(chǔ)過(guò)程中，訪問(wèn)到用戶C的表，用戶B被授予訪問(wèn)用戶C的表的權(quán)限,，但用戶A沒(méi)有被授予訪問(wèn)用戶C表的權(quán)限,，那么用戶A調(diào)用用戶B的存儲(chǔ)過(guò)程是失敗的還是成功的呢？答案是成功的,。如果讀者有興趣,，不妨進(jìn)行一下實(shí)際測(cè)試。 包 包的概念和組成 包是用來(lái)存儲(chǔ)相關(guān)程序結(jié)構(gòu)的對(duì)象,，它存儲(chǔ)于數(shù)據(jù)字典中,。包由兩個(gè)分離的部分組成：包頭(PACKAGE)和包體(PACKAGE BODY)。包頭是包的說(shuō)明部分,，是對(duì)外的操作接口,，對(duì)應(yīng)用是可見(jiàn)的；包體是包的代碼和實(shí)現(xiàn)部分,，對(duì)應(yīng)用來(lái)說(shuō)是不可見(jiàn)的黑盒,。 包中可以包含的程序結(jié)構(gòu)如下所示。 Sql代碼
過(guò)程(PROCUDURE) 帶參數(shù)的命名的程序模塊 函數(shù)(FUNCTION) 帶參數(shù),、具有返回值的命名的程序模塊 變量(VARIABLE) 存儲(chǔ)變化的量的存儲(chǔ)單元 常量(CONSTANT) 存儲(chǔ)不變的量的存儲(chǔ)單元 游標(biāo)(CURSOR) 用戶定義的數(shù)據(jù)操作緩存區(qū),，在可執(zhí)行部分使用 類型(TYPE) 用戶定義的新的結(jié)構(gòu)類型 異常(EXCEPTION) 在標(biāo)準(zhǔn)包中定義或由用戶自定義，用于處理程序錯(cuò)誤 說(shuō)明部分可以出現(xiàn)在包的三個(gè)不同的部分：出現(xiàn)在包頭中的稱為公有元素,，出現(xiàn)在包體中的稱為私有元素，出現(xiàn)在包體的過(guò)程(或函數(shù))中的稱為局部變量,。它們的性質(zhì)有所不同,，如下所示。 Sql代碼
公有元素(PUBLIC) 在包頭中說(shuō)明，在包體中具體定義 在包外可見(jiàn)并可以訪問(wèn),，對(duì)整個(gè)應(yīng)用的全過(guò)程有效 私有元素(PRIVATE) 在包體的說(shuō)明部分說(shuō)明 只能被包內(nèi)部的其他部分訪問(wèn) 局部變量(LOCAL) 在過(guò)程或函數(shù)的說(shuō)明部分說(shuō)明 只能在定義變量的過(guò)程或函數(shù)中使用 在包體中出現(xiàn)的過(guò)程或函數(shù),，如果需要對(duì)外公用，就必須在包頭中說(shuō)明,，包頭中的說(shuō)明應(yīng)該和包體中的說(shuō)明一致,。 包有以下優(yōu)點(diǎn)： * 包可以方便地將存儲(chǔ)過(guò)程和函數(shù)組織到一起，每個(gè)包又是相互獨(dú)立的,。在不同的包中,，過(guò)程、函數(shù)都可以重名，這解決了在同一個(gè)用戶環(huán)境中命名的沖突問(wèn)題,。 * 包增強(qiáng)了對(duì)存儲(chǔ)過(guò)程和函數(shù)的安全管理,，對(duì)整個(gè)包的訪問(wèn)權(quán)只需一次授予。 * 在同一個(gè)會(huì)話中,，公用變量的值將被保留,，直到會(huì)話結(jié)束。 * 區(qū)分了公有過(guò)程和私有過(guò)程,，包體的私有過(guò)程增加了過(guò)程和函數(shù)的保密性,。 * 包在被首次調(diào)用時(shí)，就作為一個(gè)整體被全部調(diào)入內(nèi)存,，減少了多次訪問(wèn)過(guò)程或函數(shù)的I/O次數(shù),。 創(chuàng)建包和包體 包由包頭和包體兩部分組成，包的創(chuàng)建應(yīng)該先創(chuàng)建包頭部分,，然后創(chuàng)建包體部分,。創(chuàng)建、刪除和編譯包的權(quán)限同創(chuàng)建,、刪除和編譯存儲(chǔ)過(guò)程的權(quán)限相同,。 創(chuàng)建包頭的簡(jiǎn)要語(yǔ)句如下： CREATE [OR REPLACE] PACKAGE 包名 {IS|AS} 公有變量定義 公有類型定義 公有游標(biāo)定義 公有異常定義 函數(shù)說(shuō)明 過(guò)程說(shuō)明 END; 創(chuàng)建包體的簡(jiǎn)要語(yǔ)法如下： CREATE [OR REPLACE] PACKAGE BODY 包名 {IS|AS} 私有變量定義 私有類型定義 私有游標(biāo)定義 私有異常定義 函數(shù)定義 過(guò)程定義 END; 包的其他操作命令包括： 刪除包頭： DROP PACKAGE 包頭名 刪除包體： DROP PACKAGE BODY 包體名 重新編譯包頭： ALTER PACKAGE 包名 COMPILE PACKAGE 重新編譯包體： ALTER PACKAGE 包名 COMPILE PACKAGE BODY 在包頭中說(shuō)明的對(duì)象可以在包外調(diào)用，調(diào)用的方法和調(diào)用單獨(dú)的過(guò)程或函數(shù)的方法基本相同,，惟一的區(qū)別就是要在調(diào)用的過(guò)程或函數(shù)名前加上包的名字(中間用“.”分隔),。但要注意，不同的會(huì)話將單獨(dú)對(duì)包的公用變量進(jìn)行初始化,，所以不同的會(huì)話對(duì)包的調(diào)用屬于不同的應(yīng)用,。 系統(tǒng)包 Oracle預(yù)定義了很多標(biāo)準(zhǔn)的系統(tǒng)包，這些包可以在應(yīng)用中直接使用,，比如在訓(xùn)練中我們使用的DBMS_OUTPUT包,，就是系統(tǒng)包。PUT_LINE是該包的一個(gè)函數(shù),。常用系統(tǒng)包下所示,。 Sql代碼
DBMS_OUTPUT 在SQL*Plus環(huán)境下輸出信息 DBMS_DDL 編譯過(guò)程函數(shù)和包 DBMS_SESSION 改變用戶的會(huì)話,，初始化包等 DBMS_TRANSACTION 控制數(shù)據(jù)庫(kù)事務(wù) DBMS_MAIL 連接Oracle*Mail DBMS_LOCK 進(jìn)行復(fù)雜的鎖機(jī)制管理 DBMS_ALERT 識(shí)別數(shù)據(jù)庫(kù)事件告警 DBMS_PIPE 通過(guò)管道在會(huì)話間傳遞信息 DBMS_JOB 管理Oracle的作業(yè) DBMS_LOB 操縱大對(duì)象 DBMS_SQL 執(zhí)行動(dòng)態(tài)SQL語(yǔ)句 包的應(yīng)用 在SQL*Plus環(huán)境下,，包和包體可以分別編譯，也可以一起編譯,。如果分別編譯,，則要先編譯包頭，后編譯包體,。如果在一起編譯,，則包頭寫在前,，包體在后，中間用“/”分隔,。 可以將已經(jīng)存在的存儲(chǔ)過(guò)程或函數(shù)添加到包中,，方法是去掉過(guò)程或函數(shù)創(chuàng)建語(yǔ)句的CREATE OR REPLACE部分，將存儲(chǔ)過(guò)程或函數(shù)復(fù)制到包體中 ,，然后重新編譯即可,。 如果需要將私有過(guò)程或函數(shù)變成共有過(guò)程或函數(shù)的話，將過(guò)程或函數(shù)說(shuō)明部分復(fù)制到包頭說(shuō)明部分,，然后重新編譯就可以了,。 【訓(xùn)練1】 創(chuàng)建管理雇員信息的包EMPLOYE，它具有從EMP表獲得雇員信息,，修改雇員名稱,，修改雇員工資和寫回EMP表的功能。 步驟1：登錄SCOTT賬戶,，輸入以下代碼并編譯： Sql代碼
CREATE OR REPLACE PACKAGE EMPLOYE --包頭部分
IS
PROCEDURE SHOW_DETAIL;
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);
PROCEDURE SAVE_EMPLOYE;
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);
END EMPLOYE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYE --包體部分
IS
EMPLOYE EMP%ROWTYPE;
-------------- 顯示雇員信息 ---------------
PROCEDURE SHOW_DETAIL
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘----- 雇員信息 -----’);
DBMS_OUTPUT.PUT_LINE('雇員編號(hào)：'||EMPLOYE.EMPNO);
DBMS_OUTPUT.PUT_LINE('雇員名稱：'||EMPLOYE.ENAME);
DBMS_OUTPUT.PUT_LINE('雇員職務(wù)：'||EMPLOYE.JOB);
DBMS_OUTPUT.PUT_LINE('雇員工資：'||EMPLOYE.SAL);
DBMS_OUTPUT.PUT_LINE('部門編號(hào)：'||EMPLOYE.DEPTNO);
END SHOW_DETAIL;
----------------- 從EMP表取得一個(gè)雇員 --------------------
PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)
AS
BEGIN
SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('獲取雇員'||EMPLOYE.ENAME||'信息成功');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('獲取雇員信息發(fā)生錯(cuò)誤！');
END GET_EMPLOYE;
---------------------- 保存雇員到EMP表 --------------------------
PROCEDURE SAVE_EMPLOYE
AS
BEGIN
UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=
EMPLOYE.EMPNO;
DBMS_OUTPUT.PUT_LINE('雇員信息保存完成,！');
END SAVE_EMPLOYE;
---------------------------- 修改雇員名稱 ------------------------------
PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)
AS
BEGIN
EMPLOYE.ENAME:=P_NEWNAME;
DBMS_OUTPUT.PUT_LINE('修改名稱完成,！');
END CHANGE_NAME;
---------------------------- 修改雇員工資 --------------------------
PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)
AS
BEGIN
EMPLOYE.SAL:=P_NEWSAL;
DBMS_OUTPUT.PUT_LINE('修改工資完成！');
END CHANGE_SAL;
END EMPLOYE;
步驟2：獲取雇員7788的信息: Sql代碼
SET SERVEROUTPUT ON EXECUTE EMPLOYE.GET_EMPLOYE(7788); 結(jié)果為： Sql代碼
獲取雇員SCOTT信息成功 PL/SQL 過(guò)程已成功完成,。 步驟3：顯示雇員信息： Sql代碼
EXECUTE EMPLOYE.SHOW_DETAIL; 結(jié)果為： Sql代碼
------------------ 雇員信息 ------------------ 雇員編號(hào)：7788 雇員名稱：SCOTT 雇員職務(wù)：ANALYST 雇員工資：3000 部門編號(hào)：20 PL/SQL 過(guò)程已成功完成,。 步驟4：修改雇員工資： Sql代碼
EXECUTE EMPLOYE.CHANGE_SAL(3800); 結(jié)果為： Sql代碼
修改工資完成,！ PL/SQL 過(guò)程已成功完成。 步驟5：將修改的雇員信息存入EMP表 Sql代碼
EXECUTE EMPLOYE.SAVE_EMPLOYE; 結(jié)果為： Sql代碼
雇員信息保存完成！ PL/SQL 過(guò)程已成功完成,。 說(shuō)明：該包完成將EMP表中的某個(gè)雇員的信息取入內(nèi)存記錄變量,，在記錄變量中進(jìn)行修改編輯，在確認(rèn)顯示信息正確后寫回EMP表的功能,。記錄變量EMPLOYE用來(lái)存儲(chǔ)取得的雇員信息,，定義為私有變量,，只能被包的內(nèi)部模塊訪問(wèn)。 【練習(xí)1】為包增加修改雇員職務(wù)和部門編號(hào)的功能,。 階段訓(xùn)練 下面的訓(xùn)練通過(guò)定義和創(chuàng)建完整的包EMP_PK并綜合運(yùn)用本章的知識(shí),，完成對(duì)雇員表的插入、刪除等功能,，包中的主要元素解釋如下所示,。 Sql代碼
程序結(jié)構(gòu) 類 型 說(shuō) 明 V_EMP_COUNT 公有變量 跟蹤雇員的總?cè)藬?shù)變化，插入,、刪除雇員的同時(shí)修改該變量的值 INIT 公有過(guò)程 對(duì)包進(jìn)行初始化,，初始化雇員人數(shù)和工資修改的上、下限 LIST_EMP 公有過(guò)程 顯示雇員列表 INSERT_EMP 公有過(guò)程 通過(guò)編號(hào)插入新雇員 DELETE_EMP 公有過(guò)程 通過(guò)編號(hào)刪除雇員 CHANGE_EMP_SAL 公有過(guò)程 通過(guò)編號(hào)修改雇員工資 V_MESSAGE 私有變量 存放準(zhǔn)備輸出的信息 C_MAX_SAL 私有變量 對(duì)工資修改的上限 C_MIN_SAL 私有變量 對(duì)工資修改的下限 SHOW_MESSAGE 私有過(guò)程 顯示私有變量V_MESSAGE中的信息 EXIST_EMP 私有函數(shù) 判斷某個(gè)編號(hào)的雇員是否存在,，該函數(shù)被INSERT_EMP,、DELETE_EMP和CHANGE_EMP_SAL等過(guò)程調(diào)用 【訓(xùn)練1】 完整的雇員包EMP_PK的創(chuàng)建和應(yīng)用。 步驟1：在SQL*Plus中登錄SCOTT賬戶,，輸入以下包頭和包體部分,，按“執(zhí)行”按鈕編譯： Sql代碼
CREATE OR REPLACE PACKAGE EMP_PK
--包頭部分
IS
V_EMP_COUNT NUMBER(5);
--雇員人數(shù)
PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER); --初始化
PROCEDURE LIST_EMP;
--顯示雇員列表
PROCEDURE INSERT_EMP(P_EMPNO NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,
P_SAL NUMBER);
--插入雇員
PROCEDURE DELETE_EMP(P_EMPNO NUMBER); --刪除雇員
PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);
--修改雇員工資
END EMP_PK;
/CREATE OR REPLACE PACKAGE BODY EMP_PK
--包體部分
IS
V_MESSAGE VARCHAR2(50); --顯示信息
V_MAX_SAL NUMBER(7); --工資上限
V_MIN_SAL NUMBER(7); --工資下限
FUNCTION EXIST_EMP(P_EMPNO NUMBER) RETURN BOOLEAN; --判斷雇員是否存在函數(shù)
PROCEDURE SHOW_MESSAGE; --顯示信息過(guò)程
------------------------------- 初始化過(guò)程 ----------------------------
PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;
V_MAX_SAL:=P_MAX;
V_MIN_SAL:=P_MIN;
V_MESSAGE:='初始化過(guò)程已經(jīng)完成！';
SHOW_MESSAGE;
END INIT;
---------------------------- 顯示雇員列表過(guò)程 ---------------------
PROCEDURE LIST_EMP
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('姓名 職務(wù) 工資');
FOR emp_rec IN (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));
END LOOP;
DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)'||V_EMP_COUNT);
END LIST_EMP;
----------------------------- 插入雇員過(guò)程 -----------------------------
PROCEDUREINSERT_EMP(P_EMPNO NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER)
IS
BEGIN
IF NOT EXIST_EMP(P_EMPNO) THEN
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL) VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);
COMMIT;
V_EMP_COUNT:=V_EMP_COUNT+1;
V_MESSAGE:='雇員'||P_EMPNO||'已插入!';
ELSE
V_MESSAGE:='雇員'||P_EMPNO||'已存在,不能插入!';
END IF;
SHOW_MESSAGE;
EXCEPTION
WHEN OTHERS THEN
V_MESSAGE:='雇員'||P_EMPNO||'插入失敗!';
SHOW_MESSAGE;
END INSERT_EMP;
--------------------------- 刪除雇員過(guò)程 --------------------
PROCEDURE DELETE_EMP(P_EMPNO NUMBER)
IS
BEGIN
IF EXIST_EMP(P_EMPNO) THEN
DELETE FROM EMP WHERE EMPNO=P_EMPNO;
COMMIT;
V_EMP_COUNT:=V_EMP_COUNT-1;
V_MESSAGE:='雇員'||P_EMPNO||'已刪除!';
ELSE
V_MESSAGE:='雇員'||P_EMPNO||'不存在,，不能刪除!';
END IF;
SHOW_MESSAGE;
EXCEPTION
WHEN OTHERS THEN
V_MESSAGE:='雇員'||P_EMPNO||'刪除失敗!';
SHOW_MESSAGE;
END DELETE_EMP;
--------------------------------------- 修改雇員工資過(guò)程 ------------------------------------
PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)
IS
BEGIN
IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN
V_MESSAGE:='工資超出修改范圍!';
ELSIF NOT EXIST_EMP(P_EMPNO) THEN
V_MESSAGE:='雇員'||P_EMPNO||'不存在,，不能修改工資!';
ELSE
UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;
COMMIT;
V_MESSAGE:='雇員'||P_EMPNO||'工資已經(jīng)修改!';
END IF;
SHOW_MESSAGE;
EXCEPTION
WHEN OTHERS THEN
V_MESSAGE:='雇員'||P_EMPNO||'工資修改失敗!';
SHOW_MESSAGE;
END CHANGE_EMP_SAL;
---------------------------- 顯示信息過(guò)程 ----------------------------
PROCEDURE SHOW_MESSAGE
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('提示信息：'||V_MESSAGE);
END SHOW_MESSAGE;
------------------------ 判斷雇員是否存在函數(shù) -------------------
FUNCTION EXIST_EMP(P_EMPNO NUMBER)
RETURN BOOLEAN
IS
V_NUM NUMBER; --局部變量
BEGIN
SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;
IF V_NUM=1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END EXIST_EMP;
-----------------------------
END EMP_PK;
結(jié)果為： Sql代碼
程序包已創(chuàng)建,。 程序包主體已創(chuàng)建。 步驟2：初始化包： Sql代碼
SET SERVEROUTPUT ON EXECUTE EMP_PK.INIT(6000,600); 顯示為： Sql代碼
提示信息：初始化過(guò)程已經(jīng)完成,！ 步驟3：顯示雇員列表： Sql代碼
EXECUTE EMP_PK.LIST_EMP; 顯示為： Sql代碼
姓名 職務(wù) 工資 SMITH CLERK 1560 ALLEN SALESMAN 1936 WARD SALESMAN 1830 JONES MANAGER 2975 ... 雇員總?cè)藬?shù)：14 PL/SQL 過(guò)程已成功完成,。 步驟4：插入一個(gè)新記錄： Sql代碼
EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000); 顯示結(jié)果為： Sql代碼
提示信息：雇員8001已插入! PL/SQL 過(guò)程已成功完成。 步驟5：通過(guò)全局變量V_EMP_COUNT查看雇員人數(shù)： Sql代碼
BEGIN DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT); END; 顯示結(jié)果為： Sql代碼
15 PL/SQL 過(guò)程已成功完成,。 步驟6：刪除新插入記錄： Sql代碼
EXECUTE EMP_PK.DELETE_EMP(8001); 顯示結(jié)果為： Sql代碼
提示信息：雇員8001已刪除! PL/SQL 過(guò)程已成功完成。 再次刪除該雇員： Sql代碼
EXECUTE EMP_PK.DELETE_EMP(8001); 結(jié)果為： Sql代碼
提示信息：雇員8001不存在,，不能刪除! 步驟7：修改雇員工資： Sql代碼
EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000); 顯示結(jié)果為： Sql代碼
提示信息：工資超出修改范圍! PL/SQL 過(guò)程已成功完成,。 步驟8：授權(quán)其他用戶調(diào)用包： 如果是另外一個(gè)用戶要使用該包,，必須由包的所有者授權(quán)，下面授予STUDEN賬戶對(duì)該包的使用權(quán)： Sql代碼
GRANT EXECUTE ON EMP_PK TO STUDENT; 每一個(gè)新的會(huì)話要為包中的公用變量開辟新的存儲(chǔ)空間,，所以需要重新執(zhí)行初始化過(guò)程,。兩個(gè)會(huì)話的進(jìn)程互不影響。 步驟9：其他用戶調(diào)用包,。 啟動(dòng)另外一個(gè)SQL*Plus,，登錄STUDENT賬戶，執(zhí)行以下過(guò)程： Sql代碼
SET SERVEROUTPUT ON EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700); 結(jié)果為： Sql代碼
提示信息：初始化過(guò)程已經(jīng)完成！ PL/SQL 過(guò)程已成功完成,。 說(shuō)明：在初始化中設(shè)置雇員的總?cè)藬?shù)和修改工資的上,、下限，初始化后V_EMP_COUNT為14人,，插入雇員后V_EMP_COUNT為15人,。V_EMP_COUNT為公有變量，所以可以在外部程序中使用DBMS_OUTPUT.PUT_LINE輸出,，引用時(shí)用EMP_PK.V_EMP_COUNT的形式,，說(shuō)明所屬的包。而私有變量V_MAX_SAL和V_MIN_SAL不能被外部訪問(wèn),，只能通過(guò)內(nèi)部過(guò)程來(lái)修改,。同樣，EXIST_EMP和SHOW_MESSAGE也是私有過(guò)程,，也只能在過(guò)程體內(nèi)被其他模塊引用,。 注意：在最后一個(gè)步驟中，因?yàn)镾TUDENT模式調(diào)用了SCOTT模式的包,，所以包名前要增加模式名SCOTT,。不同的會(huì)話對(duì)包的調(diào)用屬于不同的應(yīng)用，所以需要重新進(jìn)行初始化,。 練習(xí) 1．如果存儲(chǔ)過(guò)程的參數(shù)類型為OUT，那么調(diào)用時(shí)傳遞的參數(shù)應(yīng)該為： A．常量 B．表達(dá)式 C．變量 D．都可以 2．下列有關(guān)存儲(chǔ)過(guò)程的特點(diǎn)說(shuō)法錯(cuò)誤的是： A．存儲(chǔ)過(guò)程不能將值傳回調(diào)用的主程序 B．存儲(chǔ)過(guò)程是一個(gè)命名的模塊 C．編譯的存儲(chǔ)過(guò)程存放在數(shù)據(jù)庫(kù)中 D．一個(gè)存儲(chǔ)過(guò)程可以調(diào)用另一個(gè)存儲(chǔ)過(guò)程 3．下列有關(guān)函數(shù)的特點(diǎn)說(shuō)法錯(cuò)誤的是： A．函數(shù)必須定義返回類型 B．函數(shù)參數(shù)的類型只能是IN C．在函數(shù)體內(nèi)可以多次使用RETURN語(yǔ)句 D．函數(shù)的調(diào)用應(yīng)使用EXECUTE命令 4．包中不能包含的元素為: A．存儲(chǔ)過(guò)程 B．存儲(chǔ)函數(shù) C．游標(biāo) D．表 5．下列有關(guān)包的使用說(shuō)法錯(cuò)誤的是： A．在不同的包內(nèi)模塊可以重名 B．包的私有過(guò)程不能被外部程序調(diào)用 C．包體中的過(guò)程和函數(shù)必須在包頭部分說(shuō)明 D．必須先創(chuàng)建包頭,，然后創(chuàng)建包體 黑色頭發(fā):http://heisetoufa./ |
|
|
