国产亚洲av片在线观看16女人,亚洲av无码天堂一区二区三区,亚洲AV永久无码精品一福利

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ù)的比較

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)用時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。調(diào)用時，對于IN模式的實參可以是常量或變量，但對于OUT和IN OUT模式的實參必須是變量。

l 一般，只有在確認function_name函數(shù)是新函數(shù)或是要更新的函數(shù)時，才使用OR REPALCE關鍵字，否則容易刪除有用的函數(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ù)，應用程序調(diào)用時為函數(shù)傳遞的參數(shù)稱為實際參數(shù)。應用程序在調(diào)用函數(shù)時，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

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

即在調(diào)用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯(lián)起來進行傳遞。用這種方法進行調(diào)用，形參與實參的名稱是相互獨立，沒有關系，強調(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)用時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯(lián)起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關系，名稱的對應關系才是最重要的，次序并不重要。

格式為：

argument => parameter [,…]

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

在這種格式中，形勢參數(shù)與實際參數(shù)成對出現(xià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ù)的地址。默認時，輸出參數(shù)和輸入/輸出參數(shù)均采用傳值法。在函數(shù)調(diào)用時，ORACLE將實際參數(shù)數(shù)據(jù)拷貝到輸入/輸出參數(shù)，而當函數(shù)正常運行退出時，又將輸出形式參數(shù)和輸入/輸出形式參數(shù)數(shù)據(jù)拷貝到實際參數(shù)變量中。

3. 參數(shù)默認值

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

例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;

具有默認值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時，如果沒有為具有默認值的參數(shù)提供實際參數(shù)值，函數(shù)將使用該參數(shù)的默認值。但當調(diào)用者為默認參數(shù)提供實際參數(shù)時，函數(shù)將使用實際參數(shù)值。在創(chuàng)建函數(shù)時，只能為輸入?yún)?shù)設置默認值，而不能為輸入/輸出參數(shù)設置默認值。

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

6.3.1 創(chuàng)建過程

建立存儲過程

在 ORACLE SERVER上建立存儲過程,可以被多個應用程序調(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;

說明：相關參數(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, '部門編碼不能重復');
   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部門編碼不能重復');
   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部門編碼不能重復');
   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)用存儲過程

存儲過程建立完成后，只要通過授權，用戶就可以在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 程序中被重復調(diào)用。本地函數(shù)和過程在PL/SQL 塊的聲明部分定義，它們的語法格式與存儲函數(shù)和過程相同，但不能使用CREATE OR REPLACE 關鍵字。

例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 ，這個過程使用誰的權限運行．默任情況下，存儲過程會作為調(diào)用者的過程運行，但是具有設計者的特權．這稱為設計者權利運行．

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

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

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

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)式工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。

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

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

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

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

6.3.5.4 授權執(zhí)行權給相關的用戶或角色

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

GRANT語法：

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

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相關的權限:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

posted on 2012-09-12 17:33 kxbin 閱讀(283) 評論(0) 編輯收藏所屬分類: ORACLE 、轉(zhuǎn)發(fā)

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 授權執(zhí)行權給相關的用戶或角色

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

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

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

常用鏈接

留言簿(5)

隨筆檔案

文章分類

文章檔案

相冊

收藏夾

J2EE

java技術網(wǎng)站

Linux

平時常去的網(wǎng)站

數(shù)據(jù)庫

電影網(wǎng)站

網(wǎng)站設計

搜索

最新評論

閱讀排行榜

評論排行榜


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: win7 64位系統(tǒng) pl sql 無法解析指定的連接標識符解決辦法 oracle SQL語句執(zhí)行步驟 linux oracle10g安裝 ORACLE鎖機制 Oracle 數(shù)據(jù)庫實例啟動關閉過程 oracle for update和for update nowait的區(qū)別（二） oracle for update和for update nowait的區(qū)別（一） Oracle10g安裝了11g的ODAC后，PL/SQL連接提示TNS：無法解析指定的連接標識符 ORACLE常用命令 Oracle同義詞創(chuàng)建及其作用