ORACLE PL/SQL編程之六：把過程與函數(shù)說透(窮追猛打，把根兒都拔起!)

本篇主要內(nèi)容如下： 6.1 引言 6.2 創(chuàng)建函數(shù) 6.3 存儲過程 6.3.1 創(chuàng)建過程 6.3.2 調(diào)用存儲過程 6.3.3 AUTHID 6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 開發(fā)存儲過程步驟 6.3.6 刪除過程和函數(shù) 6.3.7 過程與函數(shù)的比較

ORACLE PL/SQL編程之六：

把過程與函數(shù)說透(窮追猛打，把根兒都拔起!)

繼上篇：ORACLE PL/SQL編程之八：把觸發(fā)器說透 得到了大家的強力支持，感謝。接下來再下猛藥，介紹下一篇，大家一定要支持與推薦呀~！我也才有動力寫后面的。

本篇主要內(nèi)容如下：

6.1 引言

6.2 創(chuàng)建函數(shù)

6.3 存儲過程

6.3.1 創(chuàng)建過程

6.3.2 調(diào)用存儲過程

6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

6.3.5 開發(fā)存儲過程步驟

6.3.6 刪除過程和函數(shù)

6.3.7 過程與函數(shù)的比較

6.1 引言

過程與函數(shù)（另外還有包與觸發(fā)器）是命名的PL/SQL塊（也是用戶的方案對象），被編譯后存儲在數(shù)據(jù)庫中，以備執(zhí)行。因此，其它PL/SQL塊可以按名稱來使用他們。所以，可以將商業(yè)邏輯、企業(yè)規(guī)則寫成函數(shù)或過程保存到數(shù)據(jù)庫中，以便共享。

過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲在數(shù)據(jù)庫中，并通過輸入、輸出參數(shù)或輸入/輸出參數(shù)與其調(diào)用者交換信息。過程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回數(shù)據(jù)，而過程則不返回數(shù)據(jù)。在本節(jié)中，主要介紹：

1．創(chuàng)建存儲過程和函數(shù)。

2．正確使用系統(tǒng)級的異常處理和用戶定義的異常處理。

3．建立和管理存儲過程和函數(shù)。

6.2 創(chuàng)建函數(shù)

　　1. 創(chuàng)建函數(shù)

　　語法如下：

CREATE [OR REPLACE] FUNCTION function_name
 (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ù)內(nèi)部，但只能讀不能寫，函數(shù)返回時實參的值不變。OUT模式的形參會忽略調(diào)用時的實參值（或說該形參的初始值總是NULL），但在函數(shù)內(nèi)部可以被讀或?qū)懀瘮?shù)返回時形參的值會賦予給實參。IN OUT具有前兩種模式的特性，即調(diào)用時，實參的值總是傳遞給形參，結(jié)束時，形參的值傳遞給實參。調(diào)用時，對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。

　　l 一般，只有在確認(rèn)function_name函數(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ù)，應(yīng)用程序調(diào)用時為函數(shù)傳遞的參數(shù)稱為實際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

　　第一種參數(shù)傳遞格式：位置表示法。

　　即在調(diào)用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關(guān)聯(lián)起來進行傳遞。用這種方法進行調(diào)用，形參與實參的名稱是相互獨立，沒有關(guān)系，強調(diào)次序才是重要的。

　　格式為：

argument_value1[,argument_value2 …]

　　例2：計算某部門的工資總和：

DECLARE
  V_num NUMBER;
  V_sum NUMBER;
BEGIN
  V_sum :=get_salary(10, v_num);
  DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);
END;

　　第二種參數(shù)傳遞格式：名稱表示法。

　　即在調(diào)用時按形參的名稱與實參的名稱，寫出實參對應(yīng)的形參，而將形參與實參關(guān)聯(lián)起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關(guān)系，名稱的對應(yīng)關(guān)系才是最重要的，次序并不重要。

　　格式為：

 argument => parameter [,…]

　　其中：argument 為形式參數(shù)，它必須與函數(shù)定義時所聲明的形式參數(shù)名稱相同parameter 為實際參數(shù)。

　　在這種格式中，形勢參數(shù)與實際參數(shù)成對出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。

　　例3：計算某部門的工資總和：

DECLARE
  V_num NUMBER;
    V_sum NUMBER;
BEGIN
    V_sum :=get_salary(emp_count => v_num, dept_no => 10);
    DBMS_OUTPUT.PUT_LINE('部門號為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);
END;

　　第三種參數(shù)傳遞格式：組合傳遞。

即在調(diào)用一個函數(shù)時，同時使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說，無論函數(shù)具有多少個參數(shù)，只要其中有一個參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。

　　例4：

CREATE OR REPLACE FUNCTION demo_fun(
  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ù)據(jù)傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調(diào)用函數(shù)時，將實際參數(shù)的地址指針傳遞給形式參數(shù)，使形式參數(shù)和實際參數(shù)指向內(nèi)存中的同一區(qū)域，從而實現(xiàn)參數(shù)數(shù)據(jù)的傳遞。這種方法又稱作參照法，即形式參數(shù)參照實際參數(shù)數(shù)據(jù)。輸入?yún)?shù)均采用傳址法傳遞數(shù)據(jù)。

傳值法是指將實際參數(shù)的數(shù)據(jù)拷貝到形式參數(shù)，而不是傳遞實際參數(shù)的地址。默認(rèn)時，輸出參數(shù)和輸入/輸出參數(shù)均采用傳值法。在函數(shù)調(diào)用時，ORACLE將實際參數(shù)數(shù)據(jù)拷貝到輸入/輸出參數(shù)，而當(dāng)函數(shù)正常運行退出時，又將輸出形式參數(shù)和輸入/輸出形式參數(shù)數(shù)據(jù)拷貝到實際參數(shù)變量中。

　　3. 參數(shù)默認(rèn)值

在CREATE OR REPLACE FUNCTION 語句中聲明函數(shù)參數(shù)時可以使用DEFAULT關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。

　　例5：

CREATE OR REPLACE FUNCTION demo_fun(
  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)用時，如果沒有為具有默認(rèn)值的參數(shù)提供實際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實際參數(shù)時，函數(shù)將使用實際參數(shù)值。在創(chuàng)建函數(shù)時，只能為輸入?yún)?shù)設(shè)置默認(rèn)值，而不能為輸入/輸出參數(shù)設(shè)置默認(rèn)值。

DECLARE
 varVARCHAR(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 存儲過程

6.3.1 創(chuàng)建過程

　　建立存儲過程

　　在 ORACLE SERVER上建立存儲過程,可以被多個應(yīng)用程序調(diào)用,可以向存儲過程傳遞參數(shù),也可以向存儲過程傳回參數(shù).

　　創(chuàng)建過程語法:

CREATE [OR REPLACE] PROCEDURE procedure_name
([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
  <可選的異常錯誤處理程序>
END procedure_name;

　　說明：相關(guān)參數(shù)說明參見函數(shù)的語法說明。

　　例6．用戶連接登記記錄；

CREATE TABLE logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution 
IS
BEGIN
INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;

　　例7．刪除指定員工記錄；

CREATE OR REPLACE
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．插入員工記錄:

CREATE OR REPLACE
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 是違反唯一約束條件的錯誤代碼 */
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．使用存儲過程向departments表中插入數(shù)據(jù)。

CREATE OR REPLACE
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, '沒有該地點');
END insert_dept;

/*調(diào)用實例一:
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沒有該地點');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯誤');
END;

調(diào)用實例二：
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沒有該地點');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯誤');
END;
*/

6.3.2 調(diào)用存儲過程

　　存儲過程建立完成后，只要通過授權(quán)，用戶就可以在SQLPLUS 、ORACLE開發(fā)工具或第三方開發(fā)工具中來調(diào)用運行。對于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。ORACLE 使用EXECUTE 語句來實現(xiàn)對存儲過程的調(diào)用：

EXEC[UTE] procedure_name( parameter1, parameter2…);

　　例10：

EXECUTE logexecution;

　　例11：查詢指定員工記錄；

CREATE OR REPLACE
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．計算指定部門的工資總和，并統(tǒng)計其中的職工數(shù)量。

CREATE OR REPLACE
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號部門工資總和：'||v_sum||',人數(shù)：'||v_num);
  Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:10號部門工資總和：'||v_sum||',人數(shù)：'||v_num);
END;

　　在PL/SQL 程序中還可以在塊內(nèi)建立本地函數(shù)和過程，這些函數(shù)和過程不存儲在數(shù)據(jù)庫中，但可以在創(chuàng)建它們的PL/SQL 程序中被重復(fù)調(diào)用。本地函數(shù)和過程在PL/SQL 塊的聲明部分定義，它們的語法格式與存儲函數(shù)和過程相同，但不能使用CREATE OR REPLACE 關(guān)鍵字。

　　例13：建立本地過程，用于計算指定部門的工資總和，并統(tǒng)計其中的職工數(shù)量；

DECLARE
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號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);
    Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||'，人數(shù)：'||v_num);
END;

6.3.3 AUTHID

過程中的AUTHID 指令可以告訴ORACLE ，這個過程使用誰的權(quán)限運行．默任情況下，存儲過程會作為調(diào)用者的過程運行，但是具有設(shè)計者的特權(quán)．這稱為設(shè)計者權(quán)利運行．

　　例14：建立過程，使用AUTOID DEFINER；

Connect HR/qaz
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；

CONNECT HR/qaz

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 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE8i 可以支持事務(wù)處理中的事務(wù)處理的概念．這種子事務(wù)處理可以完成它自己的工作，獨立于父事務(wù)處理進行提交或者回滾．通過使用這種方法，開發(fā)者就能夠這樣的過程，無論父事務(wù)處理是提交還是回滾，它都可以成功執(zhí)行．

　　例16：建立過程，使用自動事務(wù)處理進行日志記錄；

DROP TABLE logtable;

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：建立過程，沒有使用自動事務(wù)處理進行日志記錄；

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)
  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ā)存儲過程步驟

開發(fā)存儲過程、函數(shù)、包及觸發(fā)器的步驟如下：

6.3.5.1 使用文字編輯處理軟件編輯存儲過程源碼

使用文字編輯處理軟件編輯存儲過程源碼，要用類似WORD 文字處理軟件進行編輯時，要將源碼存為文本格式。

6.3.5.2 在SQLPLUS或用調(diào)試工具將存儲過程程序進行解釋

在SQLPLUS或用調(diào)試工具將存儲過程程序進行解釋；

在SQL>下調(diào)試，可用START 或GET 等ORACLE命令來啟動解釋。如：

SQL>START c:\stat1.sql

如果使用調(diào)式工具，可直接編輯和點擊相應(yīng)的按鈕即可生成存儲過程。

6.3.5.3 調(diào)試源碼直到正確

我們不能保證所寫的存儲過程達(dá)到一次就正確。所以這里的調(diào)式是每個程序員必須進行的工作之一。在SQLPLUS下來調(diào)式主要用的方法是：

l 使用 SHOW ERROR命令來提示源碼的錯誤位置；

l 使用 user_errors 數(shù)據(jù)字典來查看各存儲過程的錯誤位置。

6.3.5.4 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色

如果調(diào)式正確的存儲過程沒有進行授權(quán)，那就只有建立者本人才可以運行。所以作為應(yīng)用系統(tǒng)的一部分的存儲過程也必須進行授權(quán)才能達(dá)到要求。在SQL*PLUS下可以用GRANT命令來進行存儲過程的運行授權(quán)。

　　GRANT語法：

GRANT system_privilege | role 
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命令對不需要的過程進行刪除，語法如下：

DROP PROCEDURE [user.]Procudure_name;

　　2．刪除函數(shù)

　　可以使用DROP FUNCTION 命令對不需要的函數(shù)進行刪除，語法如下：

DROP FUNCTION [user.]Function_name;

--刪除上面實例創(chuàng)建的存儲過程與函數(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)點：

1、共同使用的代碼可以只需要被編寫和測試一次，而被需要該代碼的任何應(yīng)用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL庫）調(diào)用。

2、這種集中編寫、集中維護更新、大家共享（或重用）的方法，簡化了應(yīng)用程序的開發(fā)和維護，提高了效率與性能。

3、這種模塊化的方法，使得可以將一個復(fù)雜的問題、大的程序逐步簡化成幾個簡單的、小的程序部分，進行分別編寫、調(diào)試。因此使程序的結(jié)構(gòu)清晰、簡單，也容易實現(xiàn)。

4、可以在各個開發(fā)者之間提供處理數(shù)據(jù)、控制流程、提示信息等方面的一致性。

5、節(jié)省內(nèi)存空間。它們以一種壓縮的形式被存儲在外存中，當(dāng)被調(diào)用時才被放入內(nèi)存進行處理。并且，如果多個用戶要執(zhí)行相同的過程或函數(shù)時，就只需要在內(nèi)存中加載一個該過程或函數(shù)。

6、提高數(shù)據(jù)的安全性與完整性。通過把一些對數(shù)據(jù)的操作放到過程或函數(shù)中，就可以通過是否授予用戶有執(zhí)行該過程或的權(quán)限，來限制某些用戶對數(shù)據(jù)進行這些操作。

過程與函數(shù)的相同功能有：

1、都使用IN模式的參數(shù)傳入數(shù)據(jù)、OUT模式的參數(shù)返回數(shù)據(jù)。

2、輸入?yún)?shù)都可以接受默認(rèn)值，都可以傳值或傳引導(dǎo)。

3、調(diào)用時的實際參數(shù)都可以使用位置表示法、名稱表示法或組合方法。

4、都有聲明部分、執(zhí)行部分和異常處理部分。

5、其管理過程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。

使用過程與函數(shù)的原則：

1、如果需要返回多個值和不返回值，就使用過程；如果只需要返回一個值，就使用函數(shù)。

2、過程一般用于執(zhí)行一個指定的動作，函數(shù)一般用于計算和返回一個值。

3、可以SQL語句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來完成復(fù)雜的計算問題，但不能調(diào)用過程。所以這是函數(shù)的特色。

posted @ 2011-04-28 11:29 .NET快速開發(fā)框架閱讀(47988) 評論(57) 收藏舉報

刷新頁面返回頂部

.NET快速開發(fā)框架

低代碼開發(fā)框架、快速開發(fā)框架、WinForm開發(fā)框架，給用戶和開發(fā)者最佳的.Net框架平臺方案。了解更多：http://www.guosisoft.com/

ORACLE PL/SQL編程之六：把過程與函數(shù)說透(窮追猛打，把根兒都拔起!)

6.1 引言

6.2 創(chuàng)建函數(shù)

6.3 存儲過程

6.3.1 創(chuàng)建過程

6.3.2 調(diào)用存儲過程

6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

6.3.5 開發(fā)存儲過程步驟

6.3.5.1 使用文字編輯處理軟件編輯存儲過程源碼

6.3.5.2 在SQLPLUS或用調(diào)試工具將存儲過程程序進行解釋

6.3.5.3 調(diào)試源碼直到正確

6.3.5.4 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色

6.3.5.5 與過程相關(guān)數(shù)據(jù)字典

6.3.6 刪除過程和函數(shù)

6.3.7 過程與函數(shù)的比較

公告