ORACLE PL/SQL 編程之六：
把過程與函數(shù)說透 ( 窮追猛打，把根兒都拔起 !)
?
繼上篇： ORACLE PL/SQL 編程之八：把觸發(fā)器說透 得到了大家的強(qiáng)力支持，感謝。接下來再下猛藥，介紹下一篇，大家一定要支持與推薦呀 ~ ！我也才有動(dòng)力寫后面的。
?
本篇主要內(nèi)容如下：
6.1? 引言
6.2? 創(chuàng)建函數(shù)
6.3? 存儲(chǔ)過程
6.3.1 ? 創(chuàng)建過程
6.3.2 ? 調(diào)用存儲(chǔ)過程
6.3.3?A UTHID
6.3.4?P RAGMA AUTONOMOUS_TRANSACTION
6.3.5 ? 開發(fā)存儲(chǔ)過程步驟
6.3.6 ? 刪除過程和函數(shù)
6.3.7 ? 過程與函數(shù)的比較
?
6.1? 引言
過程與函數(shù)（另外還有包與觸發(fā)器）是命名的 PL/SQL 塊（也是用戶的方案對象），被編譯后存儲(chǔ)在數(shù)據(jù)庫中，以備執(zhí)行。因此，其它 PL/SQL 塊可以按名稱來使用他們。所以，可以將商業(yè)邏輯、企業(yè)規(guī)則寫成函數(shù)或過程保存到數(shù)據(jù)庫中，以便共享。
過程和函數(shù)統(tǒng)稱為 PL/SQL 子程序，他們是被命名的 PL/SQL 塊，均存儲(chǔ)在數(shù)據(jù)庫中，并通過輸入、輸出參數(shù)或輸入 / 輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過程則不返回?cái)?shù)據(jù)。在本節(jié)中，主要介紹：
1． ?? 創(chuàng)建存儲(chǔ)過程和函數(shù)。
2． ?? 正確使用系統(tǒng)級(jí)的異常處理和用戶定義的異常處理。
3． ?? 建立和管理存儲(chǔ)過程和函數(shù)。
6.2? 創(chuàng)建函數(shù)
1. 創(chuàng)建函數(shù)
?
語法如下：
?
?(arg1? [ ?{?IN?|?OUT?|?IN?OUT?} ] ?type1? [ DEFAULT?value1 ] ,
? [ arg2?[?{?IN?|?OUT?|?IN?OUT?} ] ?type2? [ DEFAULT?value1 ] ],
?......
? [ argn?[?{?IN?|?OUT?|?IN?OUT?} ] ?typen? [ DEFAULT?valuen ] ])
? [ ?AUTHID?DEFINER?|?CURRENT_USER? ]
RETURN ?return_type?
? IS ? | ? AS
???? < 類型.變量的聲明部分 > ?
BEGIN
????執(zhí)行部分
???? RETURN ?expression
EXCEPTION
????異常處理部分
END ?function_name;
?
l ???????? IN,OUT,IN OUT 是形參的模式。若省略，則為 IN 模式。 IN 模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部，但只能讀不能寫，函數(shù)返回時(shí)實(shí)參的值不變。 OUT 模式的形參會(huì)忽略調(diào)用時(shí)的實(shí)參值（或說該形參的初始值總是 NULL ），但在函數(shù)內(nèi)部可以被讀或?qū)懀瘮?shù)返回時(shí)形參的值會(huì)賦予給實(shí)參。 IN OUT 具有前兩種模式的特性，即調(diào)用時(shí)，實(shí)參的值總是傳遞給形參，結(jié)束時(shí)，形參的值傳遞給實(shí)參。調(diào)用時(shí)，對于 IN 模式的實(shí)參可以是常量或變量，但對于 OUT 和 IN OUT 模式的實(shí)參必須是變量。
?
l ???????? 一般，只有在確認(rèn) function_name 函數(shù)是新函數(shù)或是要更新的函數(shù)時(shí)，才使用 OR REPALCE 關(guān)鍵字，否則容易刪除有用的函數(shù)。
?
例1. ?????????? 獲取某部門的工資總和：
?
CREATE ? OR ? REPLACE
FUNCTION ?get_salary(
??Dept_no? NUMBER ,
??Emp_count?OUT? NUMBER )
?? RETURN ? NUMBER ?
IS
??V_sum? NUMBER ;
BEGIN
?? SELECT ? SUM (SALARY),? count ( * )? INTO ?V_sum,?emp_count
???? FROM ?EMPLOYEES? WHERE ?DEPARTMENT_ID = dept_no;
?? RETURN ?v_sum;
EXCEPTION
??? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 你需要的數(shù)據(jù)不存在! ' );
??? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?get_salary;
?
2. 函數(shù)的調(diào)用
函數(shù)聲明時(shí)所定義的參數(shù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱為實(shí)際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時(shí)，可以使用以下三種方法向函數(shù)傳遞參數(shù)：
?
第一種參數(shù)傳遞格式：位置表示法。
即在調(diào)用時(shí)按形參的排列順序，依次寫出實(shí)參的名稱，而將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞。用這種方法進(jìn)行調(diào)用，形參與實(shí)參的名稱是相互獨(dú)立，沒有關(guān)系，強(qiáng)調(diào)次序才是重要的。
格式為：
?????? argument_value1[,argument_value2 …]
?
例 2 ： 計(jì)算某部門的工資總和：
?
??V_num? NUMBER ;
??V_sum? NUMBER ;
BEGIN
??V_sum?: = get_salary( 10 ,?v_num);
??DBMS_OUTPUT.PUT_LINE( ' 部門號(hào)為:10的工資總和： ' || v_sum || ' ，人數(shù)為： ' || v_num);
END ;
第二種參數(shù)傳遞格式：名稱表示法。
即在調(diào)用時(shí)按形參的名稱與實(shí)參的名稱，寫出實(shí)參對應(yīng)的形參，而將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞。這種方法，形參與實(shí)參的名稱是相互獨(dú)立的，沒有關(guān)系，名稱的對應(yīng)關(guān)系才是最重要的，次序并不重要。
格式為：
?????? argument => parameter [,…]
其中： argument 為形式參數(shù)，它必須與函數(shù)定義時(shí)所聲明的形式參數(shù)名稱相同 parameter 為實(shí)際參數(shù)。
在這種格式中，形勢參數(shù)與實(shí)際參數(shù)成對出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。
例 3 ： 計(jì)算某部門的工資總和：
?
??V_num? NUMBER ;
????V_sum? NUMBER ;
BEGIN
????V_sum?: = get_salary(emp_count? => ?v_num,?dept_no? => ? 10 );
????DBMS_OUTPUT.PUT_LINE( ' 部門號(hào)為:10的工資總和： ' || v_sum || ' ，人數(shù)為： ' || v_num);
END ;
第三種參數(shù)傳遞格式：組合傳遞。
即在調(diào)用一個(gè)函數(shù)時(shí)，同時(shí)使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時(shí)，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個(gè)參數(shù)，只要其中有一個(gè)參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。
?
例 4 ：
??Name? VARCHAR2 , -- 注意VARCHAR2不能給精度，如：VARCHAR2(10)，其它類似
??Age? INTEGER ,
??Sex? VARCHAR2 )
?? RETURN ? VARCHAR2 ?
AS
??V_var? VARCHAR2 ( 32 );
BEGIN
??V_var?: = ?name || ' ： ' || TO_CHAR(age) || ' 歲. ' || sex;
?? RETURN ?v_var;
END ;
DECLARE ?
?? Var ? VARCHAR ( 32 );
BEGIN
?? Var ?: = ?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 ;
?
無論采用哪一種參數(shù)傳遞方法，實(shí)際參數(shù)和形式參數(shù)之間的數(shù)據(jù)傳遞只有兩種方法： 傳址法和傳值法 。所謂傳址法是指在調(diào)用函數(shù)時(shí)，將實(shí)際參數(shù)的地址指針傳遞給形式參數(shù)，使形式參數(shù)和實(shí)際參數(shù)指向內(nèi)存中的同一區(qū)域，從而實(shí)現(xiàn)參數(shù)數(shù)據(jù)的傳遞。這種方法又稱作參照法，即形式參數(shù)參照實(shí)際參數(shù)數(shù)據(jù)。輸入?yún)?shù)均采用傳址法傳遞數(shù)據(jù)。
?????? 傳值法是指將實(shí)際參數(shù)的數(shù)據(jù)拷貝到形式參數(shù)，而不是傳遞實(shí)際參數(shù)的地址。默認(rèn)時(shí)，輸出參數(shù)和輸入 / 輸出參數(shù)均采用傳值法。在函數(shù)調(diào)用時(shí)， ORACLE 將實(shí)際參數(shù)數(shù)據(jù)拷貝到輸入 / 輸出參數(shù)，而當(dāng)函數(shù)正常運(yùn)行退出時(shí)，又將輸出形式參數(shù)和輸入 / 輸出形式參數(shù)數(shù)據(jù)拷貝到實(shí)際參數(shù)變量中。
?
3. 參數(shù)默認(rèn)值
在 CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時(shí)可以使用 DEFAULT 關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。
?
例 5 ：
??Name? VARCHAR2 ,
??Age? INTEGER ,
??Sex? VARCHAR2 ? DEFAULT ? ' 男 ' )
?? RETURN ? VARCHAR2 ?
AS
??V_var? VARCHAR2 ( 32 );
BEGIN
??V_var?: = ?name || ' ： ' || TO_CHAR(age) || ' 歲. ' || sex;
?? RETURN ?v_var;
END ;
?
具有默認(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 );
BEGIN
? Var := demo_fun( 'user1' , 30 );
?DBMS_OUTPUT.PUT_LINE( var );
? Var := demo_fun( 'user2' , age => 40 );
?DBMS_OUTPUT.PUT_LINE( var );
? Var := demo_fun( 'user3' , sex => ' 女 ' , age => 20 );
?DBMS_OUTPUT.PUT_LINE( var);
END ;
6.3? 存儲(chǔ)過程
6.3.1 ? 創(chuàng)建過程
?
建立存儲(chǔ)過程
在 ORACLE SERVER 上建立存儲(chǔ)過程 , 可以被多個(gè)應(yīng)用程序調(diào)用 , 可以向存儲(chǔ)過程傳遞參數(shù) , 也可以向存儲(chǔ)過程傳回參數(shù) .
?
創(chuàng)建過程語法 :
?
( [ arg1?[?IN?|?OUT?|?IN?OUT? ] ]?type1? [ DEFAULT?value1 ] ,
? [ arg2?[?IN?|?OUT?|?IN?OUT? ] ]?type2? [ DEFAULT?value1 ] ],
?......
? [ argn?[?IN?|?OUT?|?IN?OUT? ] ]?typen? [ DEFAULT?valuen ] )
???? [ ?AUTHID?DEFINER?|?CURRENT_USER? ]
{? IS ? | ? AS ?}
?? < 聲明部分 > ?
BEGIN
?? < 執(zhí)行部分 >
EXCEPTION
?? < 可選的異常錯(cuò)誤處理程序 >
END ?procedure_name;
?
說明： 相關(guān)參數(shù)說明參見函數(shù)的語法說明。
?
例 6 ． 用戶連接登記記錄；
?
CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
IS
BEGIN
INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;
?
例 7 ． 刪除指定員工記錄；
?
PROCEDURE ?DelEmp
(v_empno? IN ?employees.employee_id % TYPE)?
AS
No_result?EXCEPTION;
BEGIN
??? DELETE ? FROM ?employees? WHERE ?employee_id? = ?v_empno;
??? IF ?SQL % NOTFOUND? THEN
??????RAISE?no_result;
??? END ? IF ;
???DBMS_OUTPUT.PUT_LINE( ' 編碼為 ' || v_empno || ' 的員工已被刪除! ' );
EXCEPTION
??? WHEN ?no_result? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數(shù)據(jù)不存在! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?DelEmp;
?
例 8 ． 插入員工記錄 :
?
PROCEDURE ?InsertEmp(
???v_empno????? in ?employees.employee_id % TYPE,
???v_firstname? in ?employees.first_name % TYPE,
???v_lastname?? in ?employees.last_name % TYPE,
???v_deptno???? in ?employees.department_id % TYPE
???)?
AS
???empno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(empno_remaining,? - 1 );
??? /* ?-1?是違反唯一約束條件的錯(cuò)誤代碼? */
BEGIN
??? INSERT ? INTO ?EMPLOYEES(EMPLOYEE_ID,?FIRST_NAME,?LAST_NAME,?HIRE_DATE,DEPARTMENT_ID)
??? VALUES (v_empno,?v_firstname,v_lastname,?sysdate,?v_deptno);
???DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:插入數(shù)據(jù)記錄成功! ' );
EXCEPTION
??? WHEN ?empno_remaining? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:違反數(shù)據(jù)完整性約束! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?InsertEmp;
例 9 ． 使用存儲(chǔ)過程向 departments 表中插入數(shù)據(jù)。
?
PROCEDURE ?insert_dept
??(v_dept_id? IN ?departments.department_id % TYPE,
???v_dept_name? IN ?departments.department_name % TYPE,
???v_mgr_id? IN ?departments.manager_id % TYPE,
???v_loc_id? IN ?departments.location_id % TYPE)
IS
???ept_null_error?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_null_error,? - 1400 );
???ept_no_loc_id?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_no_loc_id,? - 2291 );
BEGIN
??? INSERT ? INTO ?departments
???(department_id,?department_name,?manager_id,?location_id)
??? VALUES
???(v_dept_id,?v_dept_name,?v_mgr_id,?v_loc_id);
???DBMS_OUTPUT.PUT_LINE( ' 插入部門 ' || v_dept_id || ' 成功 ' );
EXCEPTION
??? WHEN ?DUP_VAL_ON_INDEX? THEN
??????RAISE_APPLICATION_ERROR( - 20000 ,? ' 部門編碼不能重復(fù) ' );
??? WHEN ?ept_null_error? THEN
??????RAISE_APPLICATION_ERROR( - 20001 ,? ' 部門編碼、部門名稱不能為空 ' );
??? WHEN ?ept_no_loc_id? THEN
??????RAISE_APPLICATION_ERROR( - 20002 ,? ' 沒有該地點(diǎn) ' );
END ?insert_dept;
/* 調(diào)用實(shí)例一:
DECLARE
???ept_20000?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20000,?-20000);
???ept_20001?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20001,?-20001);
???ept_20002?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20002,?-20002);
BEGIN
???insert_dept(300,?'部門300',?100,?2400);
???insert_dept(310,?NULL,?100,?2400);
???insert_dept(310,?'部門310',?100,?900);
EXCEPTION
???WHEN?ept_20000?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復(fù)');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點(diǎn)');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;
調(diào)用實(shí)例二：
DECLARE
???ept_20000?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20000,?-20000);
???ept_20001?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20001,?-20001);
???ept_20002?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20002,?-20002);
BEGIN
???insert_dept(v_dept_name?=>?'部門310',?v_dept_id?=>?310,?
???????????????v_mgr_id?=>?100,?v_loc_id?=>?2400);
???insert_dept(320,?'部門320',?v_mgr_id?=>?100,?v_loc_id?=>?900);
EXCEPTION
???WHEN?ept_20000?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復(fù)');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點(diǎn)');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;
*/
?
6.3.2 ? 調(diào)用存儲(chǔ)過程
?
?? ? 存儲(chǔ)過程建立完成后，只要通過授權(quán)，用戶就可以在 SQLPLUS 、 ORACLE 開發(fā)工具或第三方開發(fā)工具中來調(diào)用運(yùn)行。對于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。 ORACLE 使用 EXECUTE 語句來實(shí)現(xiàn)對存儲(chǔ)過程的調(diào)用：
?
?
例 10 ：
?
?
例 11 ： 查詢指定員工記錄；
?
PROCEDURE ?QueryEmp
(v_empno? IN ??employees.employee_id % TYPE,
?v_ename?OUT?employees.first_name % TYPE,
?v_sal???OUT?employees.salary % TYPE)?
AS
BEGIN
??????? SELECT ?last_name? || ?last_name,?salary? INTO ?v_ename,?v_sal?
???? FROM ?employees?
???? WHERE ?employee_id? = ?v_empno;?
???????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:編碼為 ' || v_empno || ' 的員工已經(jīng)查到! ' );
EXCEPTION
??????? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數(shù)據(jù)不存在! ' );
?????? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?QueryEmp;
-- 調(diào)用
? DECLARE
????v1?employees.first_name % TYPE;
????v2?employees.salary % TYPE;
? BEGIN
???QueryEmp( 100 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
???QueryEmp( 103 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
???QueryEmp( 104 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
END ;
?
例 12 ． 計(jì)算指定部門的工資總和，并統(tǒng)計(jì)其中的職工數(shù)量。
?
PROCEDURE ?proc_demo
(
??dept_no? NUMBER ? DEFAULT ? 10 ,
????sal_sum?OUT? NUMBER ,
????emp_count?OUT? NUMBER
??)
IS
BEGIN
???? SELECT ? SUM (salary),? COUNT ( * )? INTO ?sal_sum,?emp_count
?? FROM ?employees? WHERE ?department_id? = ?dept_no;
EXCEPTION
??? WHEN ?NO_DATA_FOUND? THEN
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數(shù)據(jù)不存在! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?proc_demo;
DECLARE
V_num? NUMBER ;
V_sum? NUMBER ( 8 ,? 2 );
BEGIN
??Proc_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_num);
DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:10號(hào)部門工資總和： ' || v_sum || ' ,人數(shù)： ' || v_num);
END ;
?????? 在 PL/SQL 程序中還可以在塊內(nèi)建立本地函數(shù)和過程，這些函數(shù)和過程不存儲(chǔ)在數(shù)據(jù)庫中，但可以在創(chuàng)建它們的 PL/SQL 程序中被重復(fù)調(diào)用。本地函數(shù)和過程在 PL/SQL 塊的聲明部分定義，它們的語法格式與存儲(chǔ)函數(shù)和過程相同，但不能使用 CREATE OR REPLACE 關(guān)鍵字。
?
例 13 ： 建立本地過程，用于計(jì)算指定部門的工資總和，并統(tǒng)計(jì)其中的職工數(shù)量；
?
V_num? NUMBER ;
V_sum? NUMBER ( 8 ,? 2 );
PROCEDURE ?proc_demo
??(
????Dept_no? NUMBER ? DEFAULT ? 10 ,
????Sal_sum?OUT? NUMBER ,
????Emp_count?OUT? NUMBER
??)
IS
BEGIN
???? SELECT ? SUM (salary),? COUNT ( * )? INTO ?sal_sum,?emp_count?
???? FROM ?employees? WHERE ?department_id = dept_no;
EXCEPTION
??? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 你需要的數(shù)據(jù)不存在! ' );
??? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?proc_demo;
-- 調(diào)用方法：
BEGIN
????Proc_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_num);
DBMS_OUTPUT.PUT_LINE( ' 10號(hào)部門工資總和： ' || v_sum || ' ，人數(shù)： ' || v_num);
END ;
6.3.3?A UTHID
過程中的 AUTHID 指令可以告訴 ORACLE ，這個(gè)過程使用誰的權(quán)限運(yùn)行．默任情況下，存儲(chǔ)過程會(huì)作為調(diào)用者的過程運(yùn)行，但是具有設(shè)計(jì)者的特權(quán)．這稱為設(shè)計(jì)者權(quán)利運(yùn)行．
?
例 14 ： 建立過程，使用 AUTOID DEFINER ；
?
DROP ? TABLE ?logtable;
CREATE ? table ?logtable?(userid? VARCHAR2 ( 10 ),?logdate?date);
CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
????AUTHID?DEFINER
IS
BEGIN
??? INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;
GRANT ? EXECUTE ? ON ?logexecution? TO ? PUBLIC ;
CONNECT? / ? AS ?SYSDBA
GRANT ?CONNECT? TO ?testuser1?IDENTIFIED? BY ?userpwd1;
CONNECT?testuser1 / userpwd1
INSERT ? INTO ?HR.LOGTABLE? VALUES ?( USER ,?SYSDATE);
EXECUTE ?HR.logexecution
CONNECT?HR / qaz
SELECT ? * ? FROM ?HR.logtable;
?
例 15 ： 建立過程，使用 AUTOID CURRENT_USER ；
?
CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
??AUTHID? CURRENT_USER
IS
BEGIN
??? INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;
GRANT ? EXECUTE ? ON ?logexecution? TO ? PUBLIC ;
CONNECT?testuser1 / userpwd1
INSERT ? INTO ?HR.LOGTABLE? VALUES ?( USER ,?SYSDATE);
EXECUTE ?HR.logexecution
?
6.3.4?P RAGMA AUTONOMOUS_TRANSACTION
?
ORACLE8i 可以支持事務(wù)處理中的事務(wù)處理的概念．這種子事務(wù)處理可以完成它自己的工作，獨(dú)立于父事務(wù)處理進(jìn)行提交或者回滾．通過使用這種方法，開發(fā)者就能夠這樣的過程，無論父事務(wù)處理是提交還是回滾，它都可以成功執(zhí)行．
?
例 16 ： 建立過程，使用自動(dòng)事務(wù)處理進(jìn)行日志記錄；
?
CREATE ? TABLE ?logtable(
??Username? varchar2 ( 20 ),
??Dassate_time?date,
??Mege? varchar2 ( 60 )
);
CREATE ? TABLE ?temp_table(?N? number ?);
CREATE ? OR ? REPLACE ? PROCEDURE ?log_message(p_message? varchar2 )
?? AS
??PRAGMA?AUTONOMOUS_TRANSACTION;
BEGIN
?? INSERT ? INTO ?logtable? VALUES ?(? user ,?sysdate,?p_message?);
?? COMMIT ;
END ?log_message;
BEGIN
??Log_message?(‘About? to ? insert ? into ?temp_table‘);
?? INSERT ? INTO ?temp_table? VALUES ?( 1 );
??Log_message?(‘ Rollback ? to ? insert ? into ?temp_table‘);
?? ROLLBACK ;
END ;
SELECT ? * ? FROM ?logtable;
SELECT ? * ? FROM ?temp_table;
?
例 17 ： 建立過程，沒有使用自動(dòng)事務(wù)處理進(jìn)行日志記錄；
?
?? AS
BEGIN
?? INSERT ? INTO ?logtable? VALUES ?(? user ,?sysdate,?p_message?);
?? COMMIT ;
END ?log_message;
BEGIN
??Log_message?( ' About?to?insert?into?temp_table ' );
?? INSERT ? INTO ?temp_table? VALUES ?( 1 );
??Log_message?( ' Rollback?to?insert?into?temp_table ' );
?? ROLLBACK ;
END ;
SELECT ? * ? FROM ?logtable;
SELECT ? * ? FROM ?temp_table;
?
6.3.5 ? 開發(fā)存儲(chǔ)過程步驟
??? 開發(fā)存儲(chǔ)過程、函數(shù)、包及觸發(fā)器的步驟如下：
?
6.3.5 .1? 使用文字編輯處理軟件編輯存儲(chǔ)過程源碼
??? 使用文字編輯處理軟件編輯存儲(chǔ)過程源碼，要用類似 WORD 文字處理軟件進(jìn)行編輯時(shí)，要將源碼存為文本格式。
?
6.3.5 .2? 在 SQLPLUS 或用調(diào)試工具將存儲(chǔ)過程程序進(jìn)行解釋
??? 在 SQLPLUS 或用調(diào)試工具將存儲(chǔ)過程程序進(jìn)行解釋；
??? 在 SQL> 下調(diào)試，可用 START 或 GET 等 ORACLE 命令來啟動(dòng)解釋。如：
SQL>START c:\stat1.sql
??? 如果使用調(diào)式工具，可直接編輯和點(diǎn)擊相應(yīng)的按鈕即可生成存儲(chǔ)過程。
?
6.3.5 .3? 調(diào)試源碼直到正確
??? 我們不能保證所寫的存儲(chǔ)過程達(dá)到一次就正確。所以這里的調(diào)式是每個(gè)程序員必須進(jìn)行的工作之一。在 SQLPLUS 下來調(diào)式主要用的方法是：
l ???????? 使用 SHOW ERROR 命令來提示源碼的錯(cuò)誤位置；
l ???????? 使用 user_errors 數(shù)據(jù)字典來查看各存儲(chǔ)過程的錯(cuò)誤位置。
?
6.3.5 .4? 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色
如果調(diào)式正確的存儲(chǔ)過程沒有進(jìn)行授權(quán)，那就只有建立者本人才可以運(yùn)行。所以作為應(yīng)用系統(tǒng)的一部分的存儲(chǔ)過程也必須進(jìn)行授權(quán)才能達(dá)到要求。在 SQL*PLUS 下可以用 GRANT 命令來進(jìn)行存儲(chǔ)過程的運(yùn)行授權(quán)。
?
GRANT 語法：
?
TO ? user ? | ?role? | ? PUBLIC ? [ WITH?ADMIN?OPTION ]
GRANT ?object_privilege? | ? ALL ? ON ? schema .object?
TO ? user ? | ?role? | ? PUBLIC ? [ WITH?GRANT?OPTION ]
-- 例子：
CREATE ? OR ? REPLACE ? PUBLIC ?SYNONYM?dbms_job? FOR ?dbms_job
GRANT ? EXECUTE ? ON ?dbms_job? TO ? PUBLIC ? WITH ? GRANT ? OPTION
?
6.3.5 .5? 與過程相關(guān)數(shù)據(jù)字典
?
USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,
ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS
?
相關(guān)的權(quán)限 :
CREATE ANY PROCEDURE
DROP ANY PROCEDURE
?
在 SQL*PLUS 中，可以用 DESCRIBE 命令查看過程的名字及其參數(shù)表。
?
DESC[RIBE] Procedure_name;
?
6.3.6 ? 刪除過程和函數(shù)
?
1 ．刪除過程
可以使用 DROP PROCEDURE 命令對不需要的過程進(jìn)行刪除，語法如下：
DROP PROCEDURE [user.]Procudure_name;
?
2 ．刪除函數(shù)
可以使用 DROP FUNCTION 命令對不需要的函數(shù)進(jìn)行刪除，語法如下：
?
-- 刪除上面實(shí)例創(chuàng)建的存儲(chǔ)過程與函數(shù)
DROP ? PROCEDURE ?logexecution;
DROP ? PROCEDURE ?delemp;
DROP ? PROCEDURE ?insertemp;
DROP ? PROCEDURE ?fireemp;
DROP ? PROCEDURE ?queryemp;
DROP ? PROCEDURE ?proc_demo;
DROP ? PROCEDURE ?log_message;
DROP ? FUNCTION ?demo_fun;
DROP ? FUNCTION ?get_salary;
?
6.3.7 ??????? 過程與函數(shù)的比較
?
使用過程與函數(shù)具有如下優(yōu)點(diǎn)：
?
1 、共同使用的代碼可以只需要被編寫和測試一次，而被需要該代碼的任何應(yīng)用程序（如： .NET 、 C++ 、 JAVA 、 VB 程序，也可以是 DLL 庫）調(diào)用。
2 、這種集中編寫、集中維護(hù)更新、大家共享（或重用）的方法，簡化了應(yīng)用程序的開發(fā)和維護(hù)，提高了效率與性能。
3 、這種模塊化的方法，使得可以將一個(gè)復(fù)雜的問題、大的程序逐步簡化成幾個(gè)簡單的、小的程序部分，進(jìn)行分別編寫、調(diào)試。因此使程序的結(jié)構(gòu)清晰、簡單，也容易實(shí)現(xiàn)。
4 、可以在各個(gè)開發(fā)者之間提供處理數(shù)據(jù)、控制流程、提示信息等方面的一致性。
5 、節(jié)省內(nèi)存空間。它們以一種壓縮的形式被存儲(chǔ)在外存中，當(dāng)被調(diào)用時(shí)才被放入內(nèi)存進(jìn)行處理。并且，如果多個(gè)用戶要執(zhí)行相同的過程或函數(shù)時(shí)，就只需要在內(nèi)存中加載一個(gè)該過程或函數(shù)。
6 、提高數(shù)據(jù)的安全性與完整性。通過把一些對數(shù)據(jù)的操作放到過程或函數(shù)中，就可以通過是否授予用戶有執(zhí)行該過程或的權(quán)限，來限制某些用戶對數(shù)據(jù)進(jìn)行這些操作。
?
過程與函數(shù)的相同功能有：
1、? 都使用 IN 模式的參數(shù)傳入數(shù)據(jù)、 OUT 模式的參數(shù)返回?cái)?shù)據(jù)。
2、? 輸入?yún)?shù)都可以接受默認(rèn)值，都可以傳值或傳引導(dǎo)。
3、? 調(diào)用時(shí)的實(shí)際參數(shù)都可以使用位置表示法、名稱表示法或組合方法。
4、? 都有聲明部分、執(zhí)行部分和異常處理部分。
5、? 其管理過程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。
?
使用過程與函數(shù)的原則：
1 、如果需要返回多個(gè)值和不返回值，就使用過程；如果只需要返回一個(gè)值，就使用函數(shù)。
2 、過程一般用于執(zhí)行一個(gè)指定的動(dòng)作，函數(shù)一般用于計(jì)算和返回一個(gè)值。
3 、可以 SQL 語句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來完成復(fù)雜的計(jì)算問題，但不能調(diào)用過程。所以這是函數(shù)的特色。
? 2011? EricHu
原創(chuàng)作品，轉(zhuǎn)貼請注明作者和出處，留此信息。
?
------------------------------------------------
cnBlobs:
http://www.cnblogs.com/huyong/
CSDN
：
http://blog.csdn.net/chinahuyong
?
?
作者：
EricHu
（
DB
、
C\S
、
B\S
、
WebService
、
WCF
、
PM
等）
出處：
http://www.cnblogs.com/huyong/
Q Q
：
80368704?? E-Mail: 80368704@qq.com
本博文歡迎大家瀏覽和轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，在『參考』的文章中，我會(huì)表明參考的文章來源，尊重他人版權(quán)。若您發(fā)現(xiàn)我侵犯了您的版權(quán)，請及時(shí)與我聯(lián)系。
更多文章請看
?
[
置頂
]
索引貼
——
（不斷更新中）
?
About
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
