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

1、第8章 存儲過程與函數(shù)的創(chuàng)建本章要點存儲過程的創(chuàng)建與執(zhí)行1函數(shù)的創(chuàng)建與執(zhí)行28.1 存儲過程 存儲過程是一個命名的程序塊，包括過程的名稱、過程使用的參數(shù)、過程執(zhí)行的操作。 8.1.1 創(chuàng)建與調用存儲過程 創(chuàng)建存儲過程包括存儲過程頭部的聲明和過程內操作的定義兩部分。 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;注意與匿名塊有三點區(qū)別：1.無DECLARE關鍵字2.在END后面可以加過程名 作為定義結束的標志3.存儲過程定義完成后需要調用才能執(zhí)行過程內部的代碼。1. 無參數(shù)存儲過程的創(chuàng)建與調用 例8.1 創(chuàng)建存儲過程，輸出系統(tǒng)的日期和時間CREATE OR REPLACE PROCEDURE display_time ISBEGIN dbms_output.put_line(systimestamp);END display_time;在SQL*Plus環(huán)境中調用存儲過程有三種方法：使用EX

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

4、以scott用戶連接數(shù)據(jù)庫EXEC system.display_time; -調用存儲過程，由于缺乏權限出錯CONNECT system/abcdef; -以system用戶連接數(shù)據(jù)庫GRANT EXECUTE ON display_time TO scott; -為scott用戶授予EXECUTE權限CONNECT scott/tiger;SET SERVEROUTPUT ON;EXEC system.display_time;2. 帶有IN參數(shù)的存儲過程的創(chuàng)建定義輸入型參數(shù)時可以指定IN關鍵字，也可以省略。例8.4 為scott.emp表創(chuàng)建一個能完成插入功能的存儲過程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); -違反完整約束條件,未找到父項關鍵字 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(該員工已經存在！); WHEN e_integr

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

8、名塊對權限的要求更嚴格？如果以system用戶創(chuàng)建例8.4中的存儲過程 應首先執(zhí)行以下命令：CONNECT scott/tiger; -以scott用戶連接數(shù)據(jù)庫GRANT INSERT ON emp TO system; -將emp表的插入數(shù)據(jù)權限授予給system用戶。CONNECT system/abcdef;3. 有參數(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ù)的存儲過程的創(chuàng)建與執(zhí)行 存儲過程輸出數(shù)據(jù)是利用OUT或IN OUT模式的參數(shù)實現(xiàn)。當定義輸出參數(shù)時，必須使用OUT關鍵字標識。 例8.5 從scott.emp表中查詢給定職工編號的職工姓名和工資，并利用OUT模式的參數(shù)將值傳給調用者。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; 存儲過程的調用對具有OUT參數(shù)的存儲過程調用要特別注意，給出的實參一定是事先定義好的變量來接收OUT參數(shù)輸出的值。例8.5的調用（使用綁定變量）（注：單句執(zhí)行）VAR emp_name VARCHAR2(10); -定義綁定變量VAR emp_salary NUMBE

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

12、); -調用存儲過程 IF emp_name IS NOT NULL THEN -如果該職工存在，則輸出 dbms_output.put_line(姓名是：|emp_name| 工資是：|emp_salary); END IF;END;5. 帶有IN OUT參數(shù)的存儲過程的創(chuàng)建 例8.7 編寫程序，交換兩個變量的值并輸出。 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 使用匿名塊調用以上存儲過程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 修改與刪除存儲過程 修改存儲過程在創(chuàng)建存儲過程時添加OR REPLACE選項 刪除存儲過程DROP PROCEDURE procedure_name事先應具有DROP ANY PROCEDURE系統(tǒng)權限 8.2 函數(shù)函數(shù)是另外一種命名的程序塊，可以通過RETURN子句返回函數(shù)的執(zhí)行結果。如果在應用程序中經常需要通過執(zhí)行SQL語句來返回特定

14、數(shù)據(jù)，那么就可以基于這些操作建立特定的函數(shù)。 8.2.1 創(chuàng)建與調用函數(shù)創(chuàng)建與調用函數(shù)需要的權限和存儲過程相同，都是CREATE PROCEDURE系統(tǒng)權限和EXECUTE對象權限，只是在語法上稍有不同，具體格式如下： 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;注意：因為函數(shù)具有返回值，所以調用函數(shù)是作為一個表達式的一部分，而不能像調用過程那樣作為一個獨立的語句使用。 調用函數(shù)的三種方式調用函數(shù)的方式1：使用變量接收返回值VAR salary NUMBER;EXEC :sa