ORACLE存儲過程詳解,教程

1、第 8 章存儲過程8.18.1 存儲過程和函數(shù)8.1 存儲過程和函數(shù)8.1.1 認(rèn)識存儲過程和函數(shù)存儲過程和函數(shù)也是一種 PL/SQL 塊，是存入數(shù)據(jù)庫的 PL/SQL 塊。但存儲過程和函數(shù)不同于已經(jīng)介紹過的 PL/SQL 程序，我們通常把 PL/SQL 程序稱為無名塊，而存儲過程和函數(shù)是以命名的方式存儲于數(shù)據(jù)庫中的。和 PL/SQL 程序相比，存儲過程有很多優(yōu)點(diǎn)，具體歸納如下：存儲過程和函數(shù)以命名的數(shù)據(jù)庫對象形式存儲于數(shù)據(jù)庫當(dāng)中。存儲在數(shù)據(jù)庫中的優(yōu)點(diǎn)是很明顯的，因?yàn)榇a不保存在本地，用戶可以在任何客戶機(jī)上登錄到數(shù)據(jù)庫，并調(diào)用或修改代碼。存儲過程和函數(shù)可由數(shù)據(jù)庫提供安全保證，要想使用存儲過程和

2、函數(shù)，需要有存儲過程和函數(shù)的所有者的授權(quán)，只有被授權(quán)的用戶或創(chuàng)建者本身才能執(zhí)行存儲過程或調(diào)用函數(shù)。存儲過程和函數(shù)的信息是寫入數(shù)據(jù)字典的，所以存儲過程可以看作是一個公用模塊，用戶編寫的 PL/SQL 程序或其他存儲過程都可以調(diào)用它（但存儲過程和函數(shù)不能調(diào)用 PL/SQL 程序）。一個重復(fù)使用的功能，可以設(shè)計成為存儲過程，比如：顯示一張工資統(tǒng)計表，可以設(shè)計成為存儲過程；一個經(jīng)常調(diào)用的計算，可以設(shè)計成為存儲函數(shù)；根據(jù)雇員編號返回雇員的姓名，可以設(shè)計成存儲函數(shù)。像其他高級語言的過程和函數(shù)一樣，可以傳遞參數(shù)給存儲過程或函數(shù)，參數(shù)的傳遞也有多種方式。存儲過程可以有返回值，也可以沒有返回值，存儲過程的返回值

3、必須通過參數(shù)帶回；函數(shù)有一定的數(shù)據(jù)類型，像其他的標(biāo)準(zhǔn)函數(shù)一樣，我們可以通過對函數(shù)名的調(diào)用返回函數(shù)值。存儲過程和函數(shù)需要進(jìn)行編譯，以排除語法錯誤，只有編譯通過才能調(diào)用。8.1.2 創(chuàng)建和刪除存儲過程創(chuàng)建存儲過程，需要有 CREATEPROCEDURE 或 CREATEANYPROCEDURE 的系統(tǒng)權(quán)限。 該權(quán)限可由系統(tǒng)管理員授予。 倉 U 建一個存儲過程的基本語句如下：CREATE ORREPLACE PROCEDURE 存儲過程名 （參數(shù) IN|OUT|INOUT數(shù)據(jù)類型.）AS|IS說明部分BEGIN可執(zhí)行部分EXCEPTION錯誤處理部分END過程名;其中：可選關(guān)鍵字ORREPLACE表

4、示如果存儲過程已經(jīng)存在， 則用新的存儲過程覆蓋，通常用于存儲過程的重建。參數(shù)部分用于定義多個參數(shù)（如果沒有參數(shù)，就可以省略）。參數(shù)有三種形式：IN、OUT 和 INOUTo 如果沒有指明參數(shù)的形式，則默認(rèn)為 IN 關(guān)鍵字 AS 也可以寫成 IS,后跟過程的說明部分，可以在此定義過程的局部變量。編寫存儲過程可以使用任何文本編輯器或直接在 SQL*Plus 環(huán)境下進(jìn)行，編寫好的存儲過程必須要在 SQL*Plus 環(huán)境下進(jìn)行編譯，生成編譯代碼，原代碼和編譯代碼在編譯過程中都會被存入數(shù)據(jù)庫。編譯成功的存儲過程就可以在 Oracle 環(huán)境下進(jìn)行調(diào)用了。一個存儲過程在不需要時可以刪除。刪除存儲過程的人是過

5、程的創(chuàng)建者或者擁有 DROPANYPROCEDURE 系統(tǒng)權(quán)限的人。 刪除存儲過程的語法如下：DROPPROCEDURE 存儲過程名；如果要重新編譯一個存儲過程，則只能是過程的創(chuàng)建者或者擁有 ALTERANYPROCEDURE 系統(tǒng)權(quán)限的人。語法如下：ALTERPROCEDURE 存儲過程名 COMPILE;執(zhí)行（或調(diào)用）存儲過程的人是過程的創(chuàng)建者或是擁有EXECUTEANYPROCEDURE 系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE 權(quán)限的人。執(zhí)行的方法如下：方法 1：EXECUTE 模式名.存儲過程名（參數(shù).）;方法 2:BEGIN模式名.存儲過程名（參數(shù).）;END;傳遞的參數(shù)必須與定

6、義的參數(shù)類型、個數(shù)和順序一致（如果參數(shù)定義了默認(rèn)值，則調(diào)用時可以省略參數(shù)）。參數(shù)可以是變量、常量或表達(dá)式，用法參見下一節(jié)。如果是調(diào)用本賬戶下的存儲過程，則模式名可以省略。要調(diào)用其他賬戶編寫的存儲過程，則模式名必須要添加。以下是一個生成和調(diào)用簡單存儲過程的訓(xùn)練。注意要事先授予創(chuàng)建存儲過程的權(quán)限?！居?xùn)練 1】創(chuàng)建一個顯示雇員總?cè)藬?shù)的存儲過程。步驟 1:登錄 SCOTT 賬戶（或?qū)W生個人賬戶）。步驟 2:在 SQL*Plus 輸入?yún)^(qū)中，輸入以下存儲過程：CREATEORREPLACEPROCEDUREEMP_COUNTASV_TOTALNUMBER（10）;BEGINSELECTCOUNT(*)IN

7、TOV_TOTALFROMEMP;DBMS_OUTPUT.PUT_LINE(雇員總?cè)藬?shù)為：|V_TOTAL);END;步驟 3:按執(zhí)行”按鈕進(jìn)行編譯。如果存在錯誤，就會顯示：警告：創(chuàng)建的過程帶有編譯錯誤。如果存在錯誤，對腳本進(jìn)行修改，直到?jīng)]有錯誤產(chǎn)生。如果編譯結(jié)果正確，將顯示：過程已創(chuàng)建。步驟 4:調(diào)用存儲過程，在輸入?yún)^(qū)中輸入以下語句并執(zhí)行：EXECUTEEMP_COUNT;顯示結(jié)果為：雇員總?cè)藬?shù)為：14PL/SQL 過程已成功完成。說明：在該訓(xùn)練中，V_TOTAL 變量是存儲過程定義的局部變量，用于接收查詢到的雇員總?cè)藬?shù)。注意：在 SQL*Plus 中輸入存儲過程，按執(zhí)行”按鈕是進(jìn)行編譯，不

8、是執(zhí)行存儲過程。如果在存儲過程中引用了其他用戶的對象，比如表，則必須有其他用戶授予的對象訪問權(quán)限。一個存儲過程一旦編譯成功，就可以由其他用戶或程序來引用。但存儲過程或函數(shù)的所有者必須授予其他用戶執(zhí)行該過程的權(quán)限。存儲過程沒有參數(shù)，在調(diào)用時，直接寫過程名即可表 8-1 參數(shù)的類型參數(shù)類型說明IN 定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程OUT 定義一個輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)INOUT 定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能參數(shù)的定義形式和作用如下：參數(shù)名 ININ 數(shù)據(jù)類型 DEFAULTDEFAULT 值；定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程。在調(diào)用存儲過程時

9、，主程序的實(shí)際參數(shù)可以是常量、有值變量或表達(dá)式等。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。如果在調(diào)用存儲過程時不指明參數(shù)，則參數(shù)變量取默認(rèn)值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進(jìn)行賦值。參數(shù)名 OUTOUT 數(shù)據(jù)類型；定義一個輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)，即變量從存儲過程中返回值給主程序。在調(diào)用存儲過程時，主程序的實(shí)際參數(shù)只能是一個變量，而不能是常量或表達(dá)式。在存儲過程中，參數(shù)變量只能被賦值而不能將其用于賦值，在存儲過程中必須給輸出變量至少賦值一次。參數(shù)名 INOUT 數(shù)據(jù)類型 DEFAULT 值；定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能。在調(diào)用存

10、儲過程時，主程序的實(shí)際參數(shù)只能是一個變量，而不能是常量或表達(dá)式。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。在存儲過程中，變量接收主程序傳遞的值，同時可以參加賦值運(yùn)算，也可以對其進(jìn)行賦值。在存儲過程中必須給變量至少賦值一次。如果省略 IN、OUT 或 INOUT,則默認(rèn)模式是 IN?！居?xùn)練 1】編寫給雇員增加工資的存儲過程 CHANGE_SALARY,通過 IN 類型的參數(shù)傳遞要增加工資的雇員編號和增加的工資額。步驟 1:登錄 SCOTT 賬戶。步驟 2:在 SQL*Plus 輸入?yún)^(qū)中輸入以下存儲過程并執(zhí)行：CREATEORREPLACEPROCEDURECHANGE_SALARY(

11、P_EMPNOINNUMBERDEFAULT7788,P_RAISENUMBERDEFAULT10)ASV_ENAMEVARCHAR2(10);V_SALNUMBER(5);BEGINSELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=P_EMPNO;UPDATEEMPSETSAL=SAL+P_RAISEWHEREEMPNO=P_EMPNO;DBMS_OUTPUT.PUT_LINE(雇員|V_ENAME|的工資被改為|TO_CHAR(V_SAL+P_RAISE);COMMIT;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.

12、PUT_LINE(發(fā)生錯誤,修改失?。?;ROLLBACK;END;執(zhí)行結(jié)果為：過程已創(chuàng)建。步驟 3:調(diào)用存儲過程，在輸入?yún)^(qū)中輸入以下語句并執(zhí)行：EXECUTECHANGE_SALARY(7788,80)顯示結(jié)果為：雇員 SCOTT 的工資被改為 3080說明：從執(zhí)行結(jié)果可以看到，雇員 SCOTT 的工資已由原來的3000 改為 3080。參數(shù)的值由調(diào)用者傳遞，傳遞的參數(shù)的個數(shù)、類型和順序應(yīng)該和定義的一致。如果順序不一致，可以采用以下調(diào)用方法。如上例，執(zhí)行語句可以改為：EXECUTECHANGE_SALARY(P_RAISE=80,P_EMPNO=7788);可以看出傳遞參數(shù)的順序發(fā)生了變化，

13、并且明確指出了參數(shù)名和要傳遞的值，=運(yùn)算符左側(cè)是參數(shù)名，右側(cè)是參數(shù)表達(dá)式，這種賦值方法的意義較清楚。BEGIN可執(zhí)行部分RETURN(表達(dá)式)EXCEPTION錯誤處理部分END函數(shù)名;其中，參數(shù)是可選的，但只能是 IN 類型(IN 關(guān)鍵字可以省略)。在定義部分的 RETURN 數(shù)據(jù)類型，用來表示函數(shù)的數(shù)據(jù)類型，也就是返回值的類型，此部分不可省略。在可執(zhí)行部分的 RETURN(表達(dá)式)，用來生成函數(shù)的返回值，其表達(dá)式的類型應(yīng)該和定義部分說明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個 RETURN 語句，但只有一個 RETURN 語句會被執(zhí)行，一旦執(zhí)行了 RETURN 語句，則函數(shù)結(jié)

14、束并返回調(diào)用環(huán)境。一個存儲函數(shù)在不需要時可以刪除，但刪除的人應(yīng)是函數(shù)的創(chuàng)建者或者是擁有 DROPANYPROCEDURE 系統(tǒng)權(quán)限的人。其語法如下：DROPFUNCTION 函數(shù)名重新編譯一個存儲函數(shù)時，編譯的人應(yīng)是函數(shù)的創(chuàng)建者或者擁有 ALTERANYPROCEDURE 系統(tǒng)權(quán)限的人。重新編譯一個存儲函數(shù)的語法如下：ALTERPROCEDURE 函數(shù)名 COMPILE;函數(shù)的調(diào)用者應(yīng)是函數(shù)的創(chuàng)建者或擁有 EXECUTEANYPROCEDURE 系統(tǒng)權(quán)限的人，或是被函數(shù)的擁有者授予了函數(shù)執(zhí)行權(quán)限的賬戶。函數(shù)的引用和存儲過程不同，函數(shù)要出現(xiàn)在程序體中，可以參加表達(dá)式的運(yùn)算或單獨(dú)出現(xiàn)在表達(dá)式中，其

15、形式如下：變量名尸函數(shù)名(.)【訓(xùn)練 1】創(chuàng)建一個通過雇員編號返回雇員名稱的函數(shù)GET_EMP_NAME。步驟 1:登汆 SCOTT 賬戶。步驟 2:在 SQL*Plus 輸入?yún)^(qū)中輸入以下存儲函數(shù)并編譯：CREATEORREPLACEFUNCTIONGET_EMP_NAME(P_EMPNONUMBERDEFAULT7788)RETURNVARCHAR2ASV_ENAMEVARCHAR2(10);BEGINSELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=P_EMPNO;RETURN(V_ENAME);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(沒有該編號雇員！);RETURN(NULL);WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(有重復(fù)雇員編號！);RETURN(NULL);WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(發(fā)生其他錯誤！);RETURN(NULL);END;步驟 3:調(diào)用該存儲函數(shù)，輸入并執(zhí)行以下程序：BEGINDBMS_OUTPUT.PUT_LINE(雇員 7