plsql存儲函數(shù)與過程

1、5Copyright Oracle Corporation, 2001. All rights reserved.存儲函數(shù)和過程存儲函數(shù)和過程 1-2Copyright Oracle Corporation, 2001. All rights reserved.存儲函數(shù)和過程存儲函數(shù)和過程ORACLE 提供可以把PL/SQL 程序存儲在數(shù)據(jù)庫中，并可以在任何地方來運行它。這樣就叫存儲過程或函數(shù)。過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲在數(shù)據(jù)庫中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回數(shù)據(jù)，而過程則不返回數(shù)

2、據(jù)。 1-3Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建函數(shù)創(chuàng)建函數(shù)創(chuàng)建函數(shù)的語法如下：CREATE OR REPLACE FUNCTION function_name (argment IN | OUT | IN OUT Type ， argment IN | OUT | IN OUT Type RETURN return_type IS | AS BEGINFUNCTION_bodyEXCEPTION其它語句END; 1-4Copyright Oracle Corporation, 2001. All rights r

3、eserved.創(chuàng)建函數(shù)創(chuàng)建函數(shù)例：獲取某部門員工數(shù)和工資總和CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER, Emp_count OUT NUMBER)RETURN NUMBER ISV_sum NUMBER;BEGINSELECT SUM(sal), count(*) INTO V_sum, emp_countFROM emp WHERE deptno=dept_no;RETURN v_sum;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WH

4、EN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END get_salary; 1-5Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法函數(shù)聲明時所定義的參數(shù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時為函數(shù)傳遞的參數(shù)稱為實際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時，可以使用以下三種方法向函數(shù)傳遞參數(shù)： 第一種參數(shù)傳遞格式稱為位置表示法，格式為： argument_value1,argument_value2 1-6Copyright Oracle Corporation, 2

5、001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法例：計算某部門員工數(shù)和工資總和DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum :=get_salary(30, v_num);DBMS_OUTPUT.PUT_LINE(30號部門工資總和：|v_sum|，人數(shù)：|v_num);END; 1-7Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法第二種參數(shù)傳遞格式稱為名稱表示法，格式為 ：argument = parameter , 其中：argume

6、nt 為形式參數(shù)，它必須與函數(shù)定義時所聲明的形式參數(shù)名稱相同。Parameter 為實際參數(shù)。在這種格式中，形式參數(shù)與實際參數(shù)成對出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。 1-8Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法例：計算某部門的員工數(shù)和工資總和DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum :=get_salary(emp_count = v_num, dept_no = 30);DBMS_OUTPUT.PUT_LINE(30號部門工資總和

7、：|v_sum|，人數(shù)：|v_num);END;1-9Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法第三種參數(shù)傳遞格式稱為混合表示法 ：即在調(diào)用一個函數(shù)時，同時使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個參數(shù)，只要其中有一個參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。1-10Copyright Oracle Corporation, 2001. All rights reserved

8、.調(diào)用函數(shù)方法調(diào)用函數(shù)方法例：DECLARE Var VARCHAR2(32);BEGINVar := demo_fun(user1, 30, sex = 男);DBMS_OUTPUT.PUT_LINE(var);Var := demo_fun(user2, age = 40, sex = 男);DBMS_OUTPUT.PUT_LINE(var);Var := demo_fun(user3, sex = 女, age = 20);DBMS_OUTPUT.PUT_LINE(var);END;1-11Copyright Oracle Corporation, 2001. All rights re

9、served.參數(shù)默認值參數(shù)默認值在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認值。 例：CREATE OR REPLACE FUNCTION demo_fun(Name VARCHAR2,Age INTEGER,Sex VARCHAR2 DEFAULT 男)RETURN VARCHAR2 ISV_var VARCHAR2(32);BEGINV_var := name|：|TO_CHAR(age)|歲，|sex;RETURN v_var;END; 1-12Copyright Oracle Corporation, 20

10、01. All rights reserved.參數(shù)默認值參數(shù)默認值具有默認值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時，如果沒有為具有默認值的參數(shù)提供實際參數(shù)值，函數(shù)將使用該參數(shù)的默認值。但當調(diào)用者為默認參數(shù)提供實際參數(shù)時，函數(shù)將使用實際參數(shù)值。在創(chuàng)建函數(shù)時，只能為輸入?yún)?shù)設(shè)置默認值，而不能為輸入/輸出參數(shù)設(shè)置默認值。 例：DECLARE Var VARCHAR(32);BEGINVar := demo_fun(user1, 30);DBMS_OUTPUT.PUT_LINE(var);Var := demo_fun(user2, age = 40);DBMS_OUTPUT.PUT_LINE(var);Var

11、 := demo_fun(user3, sex = 女, age = 20);DBMS_OUTPUT.PUT_LINE(var);END; 1-13Copyright Oracle Corporation, 2001. All rights reserved.存儲過程存儲過程在 ORACLE SERVER上建立存儲過程,可以被多個應(yīng)用程序調(diào)用,可以向存儲過程傳遞參數(shù),也可以向存儲過程傳回參數(shù) 創(chuàng)建存儲過程語法 ：CREATE OR REPLACE PROCEDURE Procedure_name (argment IN | OUT | IN OUT Type, argment IN | OUT

12、 | IN OUT Type IS | AS BEGINEXCEPTIONEND; 1-14Copyright Oracle Corporation, 2001. All rights reserved.存儲過程存儲過程例 ：刪除指定員工記錄CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE) ASNo_result EXCEPTION;BEGIN DELETE FROM emp WHERE empno=v_empno; IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_O

13、UTPUT.PUT_LINE(編碼為|v_empno|的員工已被除名!); 1-15Copyright Oracle Corporation, 2001. All rights reserved.存儲過程存儲過程例 ：EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END DelEmp; 練習：創(chuàng)建存儲過程InsertEmp插入員工記錄，包括員工號，員工姓名，雇用日期為當天日期，所在部門。（注意主鍵約束

14、）1-16Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用存儲過程調(diào)用存儲過程存儲過程建立完成后，只要通過授權(quán)，用戶就可以在SQLPLUS 、ORACLE開發(fā)工具或第三方開發(fā)工具中來調(diào)用運行。ORACLE 使用EXECUTE 語句來實現(xiàn)對存儲過程的調(diào)用： EXECUTE Procedure_name( parameter1, parameter2)例： EXECUTE DelEmp； 1-17Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用存儲過程調(diào)用存儲過程

15、例：計算指定部門的工資總和，并統(tǒng)計其中的職工數(shù)量。 CREATE OR REPLACE PROCEDURE proc_demo(Dept_no NUMBER DEFAULT 10 , Sal_sum OUT NUMBER,Emp_count OUT NUMBER)ISBEGINSELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WHEN OTHERS T

16、HEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END proc_demo; 1-18Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用存儲過程調(diào)用存儲過程調(diào)用方法: DECLAREV_num NUMBER;V_sum NUMBER(8, 2); BEGINProc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE(30號部門工資總和：|v_sum|，人數(shù)：|v_num);Proc_demo(sal_sum = v_sum, emp_count = v

17、_num);DBMS_OUTPUT.PUT_LINE(10號部門工資總和：|v_sum|，人數(shù)：|v_num); END;1-19Copyright Oracle Corporation, 2001. All rights reserved.刪除過程和函數(shù)刪除過程和函數(shù)可以使用DROP語句刪除過程：DROP PROCEDURE proceduer_name;可以使用DROP語句刪除函數(shù)：DROP FUNCTION function_name;1-20Copyright Oracle Corporation, 2001. All rights reserved.授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色授權(quán)執(zhí)

18、行權(quán)給相關(guān)的用戶或角色 如果調(diào)式正確的存儲過程沒有進行授權(quán)，那就只有建立者本人才可以運行。所以作為應(yīng)用系統(tǒng)的一部分的存儲過程也必須進行授權(quán)才能達到要求。可以用GRANT命令來進行存儲過程的運行授權(quán)GRANT語法：GRANT system_privilege | role TO user | role | PUBLIC WITH ADMIN OPTIONGRANT object_privilege | ALL ON schema.object TO user | role | PUBLIC WITH GRANT OPTION 1-21Copyright Oracle Corporation, 2001. All rights reserved.授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色 例：G