Oracle 數(shù)據(jù)庫(kù)管理與應(yīng)用：第8章 存儲(chǔ)過(guò)程與函數(shù)的創(chuàng)建

1、第8章 存儲(chǔ)過(guò)程與函數(shù)的創(chuàng)建本章要點(diǎn)存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行1函數(shù)的創(chuàng)建與執(zhí)行28.1 存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程是一個(gè)命名的程序塊，包括過(guò)程的名稱、過(guò)程使用的參數(shù)、過(guò)程執(zhí)行的操作。 8.1.1 創(chuàng)建與調(diào)用存儲(chǔ)過(guò)程 創(chuàng)建存儲(chǔ)過(guò)程包括存儲(chǔ)過(guò)程頭部的聲明和過(guò)程內(nèi)操作的定義兩部分。 CREATE OR REPLACE PROCEDURE procedure_name (argument1 IN | OUT | IN OUT data_type , argument2 IN | OUT | IN OUT data_type,)IS |AS declaration_section;BEGIN executable_

2、section;EXCEPTION exception_handlers;END procedure_name;注意與匿名塊有三點(diǎn)區(qū)別：1.無(wú)DECLARE關(guān)鍵字2.在END后面可以加過(guò)程名 作為定義結(jié)束的標(biāo)志3.存儲(chǔ)過(guò)程定義完成后需要調(diào)用才能執(zhí)行過(guò)程內(nèi)部的代碼。1. 無(wú)參數(shù)存儲(chǔ)過(guò)程的創(chuàng)建與調(diào)用 例8.1 創(chuàng)建存儲(chǔ)過(guò)程，輸出系統(tǒng)的日期和時(shí)間CREATE OR REPLACE PROCEDURE display_time ISBEGIN dbms_output.put_line(systimestamp);END display_time;在SQL*Plus環(huán)境中調(diào)用存儲(chǔ)過(guò)程有三種方法：使用EX

3、ECUTE(簡(jiǎn)寫EXEC)命令調(diào)用。使用CALL命令調(diào)用。在匿名的程序塊中直接以過(guò)程名調(diào)用。例8.2 使用三種方式調(diào)用上面創(chuàng)建的存儲(chǔ)過(guò)程display_time 。方式一： SET SERVEROUTPUT ON EXECUTE display_time; 方式二： CALL display_time( );方式三： BEGIN display_time; END;注意：用戶調(diào)用存儲(chǔ)過(guò)程時(shí)必須具有EXECUTE執(zhí)行權(quán)限 。例8.3 假設(shè)例8.1中的存儲(chǔ)過(guò)程display_time是由system用戶創(chuàng)建的，那么現(xiàn)在由scott用戶調(diào)用，執(zhí)行過(guò)程如下。CONNECT scott/tiger; -

4、以scott用戶連接數(shù)據(jù)庫(kù)EXEC system.display_time; -調(diào)用存儲(chǔ)過(guò)程，由于缺乏權(quán)限出錯(cuò)CONNECT system/abcdef; -以system用戶連接數(shù)據(jù)庫(kù)GRANT EXECUTE ON display_time TO scott; -為scott用戶授予EXECUTE權(quán)限CONNECT scott/tiger;SET SERVEROUTPUT ON;EXEC system.display_time;2. 帶有IN參數(shù)的存儲(chǔ)過(guò)程的創(chuàng)建定義輸入型參數(shù)時(shí)可以指定IN關(guān)鍵字，也可以省略。例8.4 為scott.emp表創(chuàng)建一個(gè)能完成插入功能的存儲(chǔ)過(guò)程insert_em

5、p。CREATE OR REPLACE PROCEDURE insert_emp (vno IN scott.emp.empno%TYPE, vname IN scott.emp.ename%TYPE DEFAULT NULL, vjob IN scott.emp.job%TYPE DEFAULT SALESMAN, vmgr IN scott.emp.mgr%TYPE DEFAULT 7369, vhired scott.emp.hiredate%TYPE DEFAULT SYSDATE, vsal scott.emp.sal%TYPE DEFAULT 800, vcomm m%TYPE D

6、EFAULT NULL, vdno scott.emp.deptno%TYPE DEFAULT 10)IS e_integrity EXCEPTION; PRAGMA EXCEPTION_INIT (e_integrity,-2291); -違反完整約束條件,未找到父項(xiàng)關(guān)鍵字 BEGIN INSERT INTO scott.emp VALUES(vno,vname,vjob,vmgr,vhired,vsal,vcomm,vdno);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line(該員工已經(jīng)存在！); WHEN e_integr

7、ity THEN dbms_output.put_line(部門編號(hào)填寫錯(cuò)誤！);END;注意：在參數(shù)的定義上，除了向主鍵字段empno插入值的變量no沒有設(shè)置默認(rèn)值外，其他所有的變量都給出了默認(rèn)值，這樣當(dāng)用戶調(diào)用該存儲(chǔ)過(guò)程時(shí)，可以指定1-8個(gè)任意個(gè)數(shù)的實(shí)參。 創(chuàng)建存儲(chǔ)過(guò)程時(shí)有一個(gè)很重要的問(wèn)題值得注意，那就是創(chuàng)建存儲(chǔ)過(guò)程需要的權(quán)限，主要涉及到兩類權(quán)限：創(chuàng)建存儲(chǔ)過(guò)程自身需要的權(quán)限，即CREATE PROCEDURE系統(tǒng)權(quán)限。在存儲(chǔ)過(guò)程內(nèi)部執(zhí)行各種操作時(shí)需要的顯式權(quán)限。隱式權(quán)限在匿名塊中起作用，但在命名塊中不起作用。顯式授權(quán)(直接將對(duì)象授權(quán)給用戶)，隱式授權(quán)(通過(guò)將角色授權(quán)給用戶)。思考：為什么命

8、名塊對(duì)權(quán)限的要求更嚴(yán)格？如果以system用戶創(chuàng)建例8.4中的存儲(chǔ)過(guò)程 應(yīng)首先執(zhí)行以下命令：CONNECT scott/tiger; -以scott用戶連接數(shù)據(jù)庫(kù)GRANT INSERT ON emp TO system; -將emp表的插入數(shù)據(jù)權(quán)限授予給system用戶。CONNECT system/abcdef;3. 有參數(shù)存儲(chǔ)過(guò)程的調(diào)用 形參與實(shí)參的傳遞方式包括三種，分別是： 按名稱傳遞。 EXEC insert_emp(no=1000,name=張三 , salary=1500); 按位置傳遞。 EXEC insert_emp(1001, 李四, CLERK); 混合傳遞。 EXEC

9、insert_emp(1002, 王五, salary=2500, deptno=30);不受參數(shù)位置的限制受參數(shù)位置的限制先位置傳遞后名稱傳遞4. 帶有OUT參數(shù)的存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行 存儲(chǔ)過(guò)程輸出數(shù)據(jù)是利用OUT或IN OUT模式的參數(shù)實(shí)現(xiàn)。當(dāng)定義輸出參數(shù)時(shí)，必須使用OUT關(guān)鍵字標(biāo)識(shí)。 例8.5 從scott.emp表中查詢給定職工編號(hào)的職工姓名和工資，并利用OUT模式的參數(shù)將值傳給調(diào)用者。CREATE OR REPLACE PROCEDURE select_emp(no IN scott.emp.empno%TYPE,name OUT scott.emp.ename%TYPE,salar

10、y OUT scott.emp.sal%TYPE)ISBEGIN SELECT ename,sal into name,salary FROM scott.emp WHERE empno=no;EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(該職工不存在！);END; 存儲(chǔ)過(guò)程的調(diào)用對(duì)具有OUT參數(shù)的存儲(chǔ)過(guò)程調(diào)用要特別注意，給出的實(shí)參一定是事先定義好的變量來(lái)接收OUT參數(shù)輸出的值。例8.5的調(diào)用（使用綁定變量）（注：?jiǎn)尉鋱?zhí)行）VAR emp_name VARCHAR2(10); -定義綁定變量VAR emp_salary NUMBE

11、R; -定義綁定變量為NUMBER類型時(shí)，不能加長(zhǎng)度EXEC select_emp(7369,:emp_name,:emp_salary); -使用綁定變量時(shí)，需要在綁定變量前添加冒號(hào)PRINT emp_name emp_salary; -輸出兩個(gè)綁定變量的值，中間用空格隔開存儲(chǔ)過(guò)程的調(diào)用也可以使用匿名塊調(diào)用，如下例所示。例8.6 使用匿名塊調(diào)用存儲(chǔ)過(guò)程select_emp 。DECLARE emp_name scott.emp.ename%TYPE; emp_salary scott.emp.sal%TYPE;BEGIN select_emp(7369,emp_name,emp_salary

12、); -調(diào)用存儲(chǔ)過(guò)程 IF emp_name IS NOT NULL THEN -如果該職工存在，則輸出 dbms_output.put_line(姓名是：|emp_name| 工資是：|emp_salary); END IF;END;5. 帶有IN OUT參數(shù)的存儲(chǔ)過(guò)程的創(chuàng)建 例8.7 編寫程序，交換兩個(gè)變量的值并輸出。 CREATE OR REPLACE PROCEDURE swap(x IN OUT NUMBER ,y IN OUT NUMBER)IS z NUMBER;BEGINz:=x;x:=y;y:=z;END swap;例8.8 使用匿名塊調(diào)用以上存儲(chǔ)過(guò)程swap。DECLARE

13、 a NUMBER:=10; b NUMBER:=20;BEGIN dbms_output.put_line(交換前a和b是：|a| |b); swap(a,b); dbms_output.put_line(交換后a和b是：|a| |b);END;8.1.2 修改與刪除存儲(chǔ)過(guò)程 修改存儲(chǔ)過(guò)程在創(chuàng)建存儲(chǔ)過(guò)程時(shí)添加OR REPLACE選項(xiàng) 刪除存儲(chǔ)過(guò)程DROP PROCEDURE procedure_name事先應(yīng)具有DROP ANY PROCEDURE系統(tǒng)權(quán)限 8.2 函數(shù)函數(shù)是另外一種命名的程序塊，可以通過(guò)RETURN子句返回函數(shù)的執(zhí)行結(jié)果。如果在應(yīng)用程序中經(jīng)常需要通過(guò)執(zhí)行SQL語(yǔ)句來(lái)返回特定

14、數(shù)據(jù)，那么就可以基于這些操作建立特定的函數(shù)。 8.2.1 創(chuàng)建與調(diào)用函數(shù)創(chuàng)建與調(diào)用函數(shù)需要的權(quán)限和存儲(chǔ)過(guò)程相同，都是CREATE PROCEDURE系統(tǒng)權(quán)限和EXECUTE對(duì)象權(quán)限，只是在語(yǔ)法上稍有不同，具體格式如下： CREATE OR REPLACE FUNCTION function_name (argument1 IN | OUT | IN OUT data_type , argument2 IN | OUT | IN OUT data_type,)RETURN data_typeIS |AS declaration_section;BEGIN executable_section;

15、RETURN expression;EXCEPTION exception_handlers; RETURN expression;END function_name; 例8.10 創(chuàng)建函數(shù)，從scott.emp表中查詢指定職工的工資。CREATE OR REPLACE FUNCTION select_sal(no scott.emp.empno%TYPE)RETURN scott.emp.sal%TYPEIS salary scott.emp.sal%TYPE;BEGIN SELECT sal INTO salary FROM scott.emp WHERE empno=no; RETURN salary;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0;END;注意：因?yàn)楹瘮?shù)具有返回值，所以調(diào)用函數(shù)是作為一個(gè)表達(dá)式的一部分，而不能像調(diào)用過(guò)程那樣作為一個(gè)獨(dú)立的語(yǔ)句使用。 調(diào)用函數(shù)的三種方式調(diào)用函數(shù)的方式1：使用變量接收返回值VAR salary NUMBER;EXEC :sa