plsql存儲(chǔ)函數(shù)和過程

1、5Copyright Oracle Corporation, 2001. All rights reserved.存儲(chǔ)函數(shù)和過程存儲(chǔ)函數(shù)和過程 1-2Copyright Oracle Corporation, 2001. All rights reserved.存儲(chǔ)函數(shù)和過程存儲(chǔ)函數(shù)和過程ORACLE 提供可以把PL/SQL 程序存儲(chǔ)在數(shù)據(jù)庫(kù)中，并可以在任何地方來運(yùn)行它。這樣就叫存儲(chǔ)過程或函數(shù)。過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲(chǔ)在數(shù)據(jù)庫(kù)中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過程則不返回?cái)?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ù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱為實(shí)際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時(shí)，可以使用以下三種方法向函數(shù)傳遞參數(shù)： 第一種參數(shù)傳遞格式稱為位置表示法，格式為： argument_value1,argument_value2 1-6Copyright Oracle Corporation, 2

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

7、：|v_sum|，人數(shù)：|v_num);END;1-9Copyright Oracle Corporation, 2001. All rights reserved.調(diào)用函數(shù)方法調(diào)用函數(shù)方法第三種參數(shù)傳遞格式稱為混合表示法 ：即在調(diào)用一個(gè)函數(shù)時(shí)，同時(shí)使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時(shí)，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個(gè)參數(shù)，只要其中有一個(gè)參數(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ù)默認(rèn)值參數(shù)默認(rèn)值在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時(shí)可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。 例：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ù)默認(rèn)值參數(shù)默認(rèn)值具有默認(rèn)值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時(shí)，如果沒有為具有默認(rèn)值的參數(shù)提供實(shí)際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實(shí)際參數(shù)時(shí)，函數(shù)將使用實(shí)際參數(shù)值。在創(chuàng)建函數(shù)時(shí)，只能為輸入?yún)?shù)設(shè)置默認(rèn)值，而不能為輸入/輸出參數(shù)設(shè)置默認(rèn)值。 例：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.存儲(chǔ)過程存儲(chǔ)過程在 ORACLE SERVER上建立存儲(chǔ)過程,可以被多個(gè)應(yīng)用程序調(diào)用,可以向存儲(chǔ)過程傳遞參數(shù),也可以向存儲(chǔ)過程傳回參數(shù) 創(chuàng)建存儲(chǔ)過程語法 ：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.存儲(chǔ)過程存儲(chǔ)過程例 ：刪除指定員工記錄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.存儲(chǔ)過程存儲(chǔ)過程例 ：EXCEPTION WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(你需要的數(shù)據(jù)不存在!); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END DelEmp; 練習(xí)：創(chuàng)建存儲(chǔ)過程InsertEmp插入員工記錄，包括員工號(hào)，員工姓名，雇用日期為當(dāng)天日期，所在部門。（注意主鍵約束

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

15、例：計(jì)算指定部門的工資總和，并統(tǒng)計(jì)其中的職工數(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)用存儲(chǔ)過程調(diào)用存儲(chǔ)過程調(diào)用方法: DECLAREV_num NUMBER;V_sum NUMBER(8, 2); BEGINProc_demo(30, v_sum, v_num);DBMS_OUTPUT.PUT_LINE(30號(hào)部門工資總和：|v_sum|，人數(shù)：|v_num);Proc_demo(sal_sum = v_sum, emp_count = v

17、_num);DBMS_OUTPUT.PUT_LINE(10號(hào)部門工資總和：|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)式正確的存儲(chǔ)過程沒有進(jìn)行授權(quán)，那就只有建立者本人才可以運(yùn)行。所以作為應(yīng)用系統(tǒng)的一部分的存儲(chǔ)過程也必須進(jìn)行授權(quán)才能達(dá)到要求?？梢杂肎RANT命令來進(jìn)行存儲(chǔ)過程的運(yùn)行授權(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)的用戶或角色 例：