oracle創(chuàng)建,刪除存儲(chǔ)過程,參數(shù)傳遞,創(chuàng)建,刪除存儲(chǔ)函數(shù),存儲(chǔ)過程和函數(shù)的查看,包,系統(tǒng)包

1、oracle創(chuàng)建,刪除存儲(chǔ)過程,參數(shù)傳遞,創(chuàng)建,刪除存儲(chǔ)函數(shù),存儲(chǔ)過程和函數(shù)的查看,包,系統(tǒng)包認(rèn)識(shí)存儲(chǔ)過程和函數(shù)存儲(chǔ)過程和函數(shù)也是一種PL/SQL塊，是存入數(shù)據(jù)庫(kù)的PL/SQL塊。但存儲(chǔ)過程和函數(shù)不同于已經(jīng)介紹過的PL/SQL程序，我們通常把PL/SQL程序稱為無(wú)名塊，而存儲(chǔ)過程和函數(shù)是以命名的方式存儲(chǔ)于數(shù)據(jù)庫(kù)中的。和PL/SQL程序相比，存儲(chǔ)過程有很多優(yōu)點(diǎn)，具體歸納如下：* 存儲(chǔ)過程和函數(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ǔ)過程和函數(shù)可由數(shù)據(jù)庫(kù)提供安全保證，要想使用存儲(chǔ)過程

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

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

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

5、儲(chǔ)過程的語(yǔ)法如下：DROPPROCEDURE存儲(chǔ)過程名；如果要重新編譯一個(gè)存儲(chǔ)過程，則只能是過程的創(chuàng)建者或者擁有ALTERANYPROCEDURE系統(tǒng)權(quán)限的人。語(yǔ)法如下：ALTERPROCEDURE存儲(chǔ)過程名COMPILE；執(zhí)行（或調(diào)用）存儲(chǔ)過程的人是過程的創(chuàng)建者或是擁有EXECUTEANYPROCEDURE系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE權(quán)限的人。執(zhí)行的方法如下：方法1：EXECUTE模式名.存儲(chǔ)過程名（參數(shù).）;方法2：BEGIN模式名.存儲(chǔ)過程名（參數(shù).）;END;傳遞的參數(shù)必須與定義的參數(shù)類型、個(gè)數(shù)和順序一致（如果參數(shù)定義了默認(rèn)值，則調(diào)用時(shí)可以省略參數(shù)）。參數(shù)可以是變量、常量

6、或表達(dá)式，用法參見下一節(jié)。如果是調(diào)用本賬戶下的存儲(chǔ)過程，則模式名可以省略。要調(diào)用其他賬戶編寫的存儲(chǔ)過程，則模式名必須要添加。以下是一個(gè)生成和調(diào)用簡(jiǎn)單存儲(chǔ)過程的訓(xùn)練。注意要事先授予創(chuàng)建存儲(chǔ)過程的權(quán)限?！居?xùn)練1】創(chuàng)建一個(gè)顯示雇員總?cè)藬?shù)的存儲(chǔ)過程。步驟1：登錄SCOTT賬戶（或?qū)W生個(gè)人賬戶）。步驟2：在SQL*Plus輸入?yún)^(qū)中，輸入以下存儲(chǔ)過程：Sql代碼CREATEORREPLACEPROCEDUREEMP_COUNTASV_TOTALNUMBER(10);BEGINSELECTCOUNT(*)INTOV_TOTALFROMEMP;DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)為：

7、'|V_TOTAL);END;CREATEORREPLACEPROCEDUREEMP_COUNTASV_TOTALNUMBER(10);BEGINSELECTCOUNT(*)INTOV_TOTALFROMEMP;DBMS_OUTPUT.PUT_LINE('雇員總?cè)藬?shù)為：'|V_TOTAL);END;步驟3：按“執(zhí)行”按鈕進(jìn)行編譯。如果存在錯(cuò)誤，就會(huì)顯示:警告:創(chuàng)建的過程帶有編譯錯(cuò)誤。如果存在錯(cuò)誤，對(duì)腳本進(jìn)行修改，直到?jīng)]有錯(cuò)誤產(chǎn)生。如果編譯結(jié)果正確，將顯示：Sql代碼過程已創(chuàng)建。過程已創(chuàng)建。步驟4：調(diào)用存儲(chǔ)過程，在輸入?yún)^(qū)中輸入以下語(yǔ)

8、句并執(zhí)行：Sql代碼EXECUTEEMP_COUNT;EXECUTEEMP_COUNT;顯示結(jié)果為：Sql代碼雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。說(shuō)明：在該訓(xùn)練中，V_TOTAL變量是存儲(chǔ)過程定義的局部變量，用于接收查詢到的雇員總?cè)藬?shù)。注意：在SQL*Plus中輸入存儲(chǔ)過程，按“執(zhí)行”按鈕是進(jìn)行編譯，不是執(zhí)行存儲(chǔ)過程。如果在存儲(chǔ)過程中引用了其他用戶的對(duì)象，比如表，則必須有其他用戶授予的對(duì)象訪問權(quán)限。一個(gè)存儲(chǔ)過程一旦編譯成功，就可以由其他用戶或程序來(lái)引用。但存儲(chǔ)過程或函數(shù)的所有者必須授予其他用戶執(zhí)行該

9、過程的權(quán)限。存儲(chǔ)過程沒有參數(shù)，在調(diào)用時(shí)，直接寫過程名即可?！居?xùn)練2】在PL/SQL程序中調(diào)用存儲(chǔ)過程。步驟1：登錄SCOTT賬戶。步驟2：授權(quán)STUDENT賬戶使用該存儲(chǔ)過程，即在SQL*Plus輸入?yún)^(qū)中，輸入以下的命令：Sql代碼GRANTEXECUTEONEMP_COUNTTOSTUDENTGRANTEXECUTEONEMP_COUNTTOSTUDENTSql代碼授權(quán)成功。授權(quán)成功。步驟3：登錄STUDENT賬戶，在SQL*Plus輸入?yún)^(qū)中輸入以下程序：Sql代碼SETSERVEROUTPUTONBEGINSCOTT.EMP_COUNT;END;SETSERVEROUTPUTONBEGIN

10、SCOTT.EMP_COUNT;END;步驟4：執(zhí)行以上程序，結(jié)果為：Sql代碼雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。說(shuō)明：在本例中，存儲(chǔ)過程是由SCOTT賬戶創(chuàng)建的，STUDEN賬戶獲得SCOTT賬戶的授權(quán)后，才能調(diào)用該存儲(chǔ)過程。注意：在程序中調(diào)用存儲(chǔ)過程，使用了第二種語(yǔ)法?！居?xùn)練3】編寫顯示雇員信息的存儲(chǔ)過程EMP_LIST，并引用EMP_COUNT存儲(chǔ)過程。步驟1：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲(chǔ)過程：Sql代碼CREATEORREPLACEPROCEDUREEMP_LISTASCURSORemp_cursorISSELE

11、CTempno,enameFROMemp;BEGINFOREmp_recordINemp_cursorLOOPDBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);ENDLOOP;EMP_COUNT;END;CREATEORREPLACEPROCEDUREEMP_LISTASCURSORemp_cursorISSELECTempno,enameFROMemp;BEGINFOREmp_recordINemp_cursorLOOPDBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);

12、ENDLOOP;EMP_COUNT;END;執(zhí)行結(jié)果：Sql代碼過程已創(chuàng)建。過程已創(chuàng)建。步驟2：調(diào)用存儲(chǔ)過程，在輸入?yún)^(qū)中輸入以下語(yǔ)句并執(zhí)行：Sql代碼EXECUTEEMP_LISTEXECUTEEMP_LIST顯示結(jié)果為：Sql代碼7369SMITH7499ALLEN7521WARD7566JONES執(zhí)行結(jié)果：雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。7369SMITH7499ALLEN7521WARD7566JONES執(zhí)行結(jié)果：雇員總?cè)藬?shù)為：14PL/SQL過程已成功完成。說(shuō)明：以上的EMP_LIST存儲(chǔ)過程中定義并使用了游標(biāo)，用來(lái)循環(huán)顯示所有雇員的信息。然后調(diào)用已經(jīng)成功編譯的存儲(chǔ)過程E

13、MP_COUNT，用來(lái)附加顯示雇員總?cè)藬?shù)。通過EXECUTE命令來(lái)執(zhí)行EMP_LIST存儲(chǔ)過程?！揪毩?xí)1】編寫顯示部門信息的存儲(chǔ)過程DEPT_LIST，要求統(tǒng)計(jì)出部門個(gè)數(shù)。參數(shù)傳遞參數(shù)的作用是向存儲(chǔ)過程傳遞數(shù)據(jù)，或從存儲(chǔ)過程獲得返回結(jié)果。正確的使用參數(shù)可以大大增加存儲(chǔ)過程的靈活性和通用性。參數(shù)的類型有三種，如下所示。Sql代碼IN定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲(chǔ)過程OUT定義一個(gè)輸出參數(shù)變量，用于從存儲(chǔ)過程獲取數(shù)據(jù)INOUT定義一個(gè)輸入、輸出參數(shù)變量，兼有以上兩者的功能IN定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲(chǔ)過程OUT定義一個(gè)輸出參數(shù)變量，用于從存儲(chǔ)過程獲取數(shù)據(jù)INOUT定義一個(gè)輸

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

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