ORACLE PL/SQL程式設計之六：把過程與函式說透(窮追猛打，把根兒都拔起!)

RDIFramework發表於2011-05-04

OracleSQL程式設計函式

ORACLE PL/SQL程式設計之六：

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

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

本篇主要內容如下：

6.1 引言

6.2 建立函式

6.3 儲存過程

6.3.1 建立過程

6.3.2 呼叫儲存過程

6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

6.3.5 開發儲存過程步驟

6.3.6 刪除過程和函式

6.3.7 過程與函式的比較

6.1 引言

過程與函式（另外還有包與觸發器）是命名的PL/SQL塊（也是使用者的方案物件），被編譯後儲存在資料庫中，以備執行。因此，其它PL/SQL塊可以按名稱來使用他們。所以，可以將商業邏輯、企業規則寫成函式或過程儲存到資料庫中，以便共享。

過程和函式統稱為PL/SQL子程式，他們是被命名的PL/SQL塊，均儲存在資料庫中，並通過輸入、輸出引數或輸入/輸出引數與其呼叫者交換資訊。過程和函式的唯一區別是函式總向呼叫者返回資料，而過程則不返回資料。在本節中，主要介紹：

1．建立儲存過程和函式。

2．正確使用系統級的異常處理和使用者定義的異常處理。

3．建立和管理儲存過程和函式。

6.2 建立函式

1. 建立函式

語法如下：

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
    執行部分
    RETURN expression
EXCEPTION
    異常處理部分
END function_name;

l IN,OUT,IN OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實參傳遞給形參，進入函式內部，但只能讀不能寫，函式返回時實參的值不變。OUT模式的形參會忽略呼叫時的實參值（或說該形參的初始值總是NULL），但在函式內部可以被讀或寫，函式返回時形參的值會賦予給實參。IN OUT具有前兩種模式的特性，即呼叫時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。呼叫時，對於IN模式的實參可以是常量或變數，但對於OUT和IN OUT模式的實參必須是變數。

l 一般，只有在確認function_name函式是新函式或是要更新的函式時，才使用OR REPALCE關鍵字，否則容易刪除有用的函式。

例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('你需要的資料不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;

2. 函式的呼叫

函式宣告時所定義的引數稱為形式引數，應用程式呼叫時為函式傳遞的引數稱為實際引數。應用程式在呼叫函式時，可以使用以下三種方法向函式傳遞引數：

第一種引數傳遞格式：位置表示法。

即在呼叫時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯起來進行傳遞。用這種方法進行呼叫，形參與實參的名稱是相互獨立，沒有關係，強調次序才是重要的。

格式為：

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||'，人數為：'||v_num);
END;

第二種引數傳遞格式：名稱表示法。

即在呼叫時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關係，名稱的對應關係才是最重要的，次序並不重要。

格式為：

argument => parameter [,…]

其中：argument 為形式引數，它必須與函式定義時所宣告的形式引數名稱相同parameter 為實際引數。

在這種格式中，形勢引數與實際引數成對出現，相互間關係唯一確定，所以引數的順序可以任意排列。

例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||'，人數為：'||v_num);
END;

第三種引數傳遞格式：組合傳遞。

即在呼叫一個函式時，同時使用位置表示法和名稱表示法為函式傳遞引數。採用這種引數傳遞方法時，使用位置表示法所傳遞的引數必須放在名稱表示法所傳遞的引數前面。也就是說，無論函式具有多少個引數，只要其中有一個引數使用名稱表示法，其後所有的引數都必須使用名稱表示法。

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

無論採用哪一種引數傳遞方法，實際引數和形式引數之間的資料傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在呼叫函式時，將實際引數的地址指標傳遞給形式引數，使形式引數和實際引數指向記憶體中的同一區域，從而實現引數資料的傳遞。這種方法又稱作參照法，即形式引數參照實際引數資料。輸入引數均採用傳址法傳遞資料。

傳值法是指將實際引數的資料拷貝到形式引數，而不是傳遞實際引數的地址。預設時，輸出引數和輸入/輸出引數均採用傳值法。在函式呼叫時，ORACLE將實際引數資料拷貝到輸入/輸出引數，而當函式正常執行退出時，又將輸出形式引數和輸入/輸出形式引數資料拷貝到實際引數變數中。

3. 引數預設值

在CREATE OR REPLACE FUNCTION 語句中宣告函式引數時可以使用DEFAULT關鍵字為輸入引數指定預設值。

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

具有預設值的函式建立後，在函式呼叫時，如果沒有為具有預設值的引數提供實際引數值，函式將使用該引數的預設值。但當呼叫者為預設引數提供實際引數時，函式將使用實際引數值。在建立函式時，只能為輸入引數設定預設值，而不能為輸入/輸出引數設定預設值。

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 建立過程

建立儲存過程

在 ORACLE SERVER上建立儲存過程,可以被多個應用程式呼叫,可以向儲存過程傳遞引數,也可以向儲存過程傳回引數.

建立過程語法:

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
  <執行部分>
EXCEPTION
  <可選的異常錯誤處理程式>
END procedure_name;

說明：相關引數說明參見函式的語法說明。

例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('溫馨提示:你需要的資料不存在!');
   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('溫馨提示:插入資料記錄成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:違反資料完整性約束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END InsertEmp;

例9．使用儲存過程向departments表中插入資料。

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;

/*呼叫例項一:
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出現了其他異常錯誤');
END;

呼叫例項二：
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出現了其他異常錯誤');
END;
*/

6.3.2 呼叫儲存過程

儲存過程建立完成後，只要通過授權，使用者就可以在SQLPLUS 、ORACLE開發工具或第三方開發工具中來呼叫執行。對於引數的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函式的一樣。ORACLE 使用EXECUTE 語句來實現對儲存過程的呼叫：

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||'的員工已經查到!');
EXCEPTION
       WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的資料不存在!');
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END QueryEmp;
--呼叫
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．計算指定部門的工資總和，並統計其中的職工數量。

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('溫馨提示:你需要的資料不存在!');
   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||',人數：'||v_num);
  Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:10號部門工資總和：'||v_sum||',人數：'||v_num);
END;

在PL/SQL 程式中還可以在塊內建立本地函式和過程，這些函式和過程不儲存在資料庫中，但可以在建立它們的PL/SQL 程式中被重複呼叫。本地函式和過程在PL/SQL 塊的宣告部分定義，它們的語法格式與儲存函式和過程相同，但不能使用CREATE OR REPLACE 關鍵字。

例13：建立本地過程，用於計算指定部門的工資總和，並統計其中的職工數量；

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('你需要的資料不存在!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
--呼叫方法：
BEGIN
    Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30號部門工資總和：'||v_sum||'，人數：'||v_num);
    Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||'，人數：'||v_num);
END;

6.3.3 AUTHID

過程中的AUTHID 指令可以告訴ORACLE ，這個過程使用誰的許可權執行．默任情況下，儲存過程會作為呼叫者的過程執行，但是具有設計者的特權．這稱為設計者權利執行．

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

例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 開發儲存過程步驟

開發儲存過程、函式、包及觸發器的步驟如下：

6.3.5.1 使用文字編輯處理軟體編輯儲存過程原始碼

使用文字編輯處理軟體編輯儲存過程原始碼，要用類似WORD 文書處理軟體進行編輯時，要將原始碼存為文字格式。

6.3.5.2 在SQLPLUS或用除錯工具將儲存過程程式進行解釋

在SQLPLUS或用除錯工具將儲存過程程式進行解釋；

在SQL>下除錯，可用START 或GET 等ORACLE命令來啟動解釋。如：

SQL>START c:/stat1.sql

如果使用調式工具，可直接編輯和點選相應的按鈕即可生成儲存過程。

6.3.5.3 除錯原始碼直到正確

我們不能保證所寫的儲存過程達到一次就正確。所以這裡的調式是每個程式設計師必須進行的工作之一。在SQLPLUS下來調式主要用的方法是：

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

l 使用 user_errors 資料字典來檢視各儲存過程的錯誤位置。

6.3.5.4 授權執行權給相關的使用者或角色

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

GRANT語法：

6.3.5.5 與過程相關資料字典

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 命令檢視過程的名字及其參數列。

DESC[RIBE] Procedure_name;

6.3.6 刪除過程和函式

1．刪除過程

可以使用DROP PROCEDURE命令對不需要的過程進行刪除，語法如下：

DROP PROCEDURE [user.]Procudure_name;

2．刪除函式

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

DROP FUNCTION [user.]Function_name;

--刪除上面例項建立的儲存過程與函式
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 過程與函式的比較

使用過程與函式具有如下優點：

1、共同使用的程式碼可以只需要被編寫和測試一次，而被需要該程式碼的任何應用程式（如：.NET、C++、JAVA、VB程式，也可以是DLL庫）呼叫。

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

3、這種模組化的方法，使得可以將一個複雜的問題、大的程式逐步簡化成幾個簡單的、小的程式部分，進行分別編寫、除錯。因此使程式的結構清晰、簡單，也容易實現。

4、可以在各個開發者之間提供處理資料、控制流程、提示資訊等方面的一致性。

5、節省記憶體空間。它們以一種壓縮的形式被儲存在外存中，當被呼叫時才被放入記憶體進行處理。並且，如果多個使用者要執行相同的過程或函式時，就只需要在記憶體中載入一個該過程或函式。

6、提高資料的安全性與完整性。通過把一些對資料的操作放到過程或函式中，就可以通過是否授予使用者有執行該過程或的許可權，來限制某些使用者對資料進行這些操作。

過程與函式的相同功能有：

1、都使用IN模式的引數傳入資料、OUT模式的引數返回資料。

2、輸入引數都可以接受預設值，都可以傳值或傳引導。

3、呼叫時的實際引數都可以使用位置表示法、名稱表示法或組合方法。

4、都有宣告部分、執行部分和異常處理部分。

5、其管理過程都有建立、編譯、授權、刪除、顯示依賴關係等。

使用過程與函式的原則：

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

2、過程一般用於執行一個指定的動作，函式一般用於計算和返回一個值。

3、可以SQL語句內部（如表示式）呼叫函式來完成複雜的計算問題，但不能呼叫過程。所以這是函式的特色。

原創作品，轉貼請註明作者和出處，留此資訊。

------------------------------------------------

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
本博文歡迎大家瀏覽和轉載，但未經作者同意必須保留此段宣告，且在文章頁面明顯位置給出原文連線，在『參考』的文章中，我會表明參考的文章來源，尊重他人版權。若您發現我侵犯了您的版權，請及時與我聯絡。
更多文章請看 [置頂]索引貼——（不斷更新中）

[推薦]ORACLE PL/SQL程式設計之四：把遊標說透(不怕做不到，只怕想不到)
2011-05-04
OracleSQL程式設計
Oracle PL/SQL 之函式
2014-02-08
OracleSQL函式
PL/SQL 中的儲存過程與函式
2017-09-05
SQL儲存過程函式
Oracle Pl/SQL 之儲存過程
2014-02-08
OracleSQL儲存過程
假如我們把函式都改成遞迴...
2023-12-19
函式遞迴
oracle中取得儲存過程、函式等pl sql原始碼的方法
2010-03-15
Oracle儲存過程函式SQL原始碼
PL/SQL在執行過程中，對其依賴的表，函式，過程都加lock和pin嗎？
2011-02-17
SQL函式
把匿名函式賦值給變數，透過變數來呼叫
2018-01-20
函式賦值變數
透過pl/sql計算程式的執行時間
2015-01-17
SQL
Oralce之PL/SQL程式設計(遊標)
2018-11-14
SQL程式設計
ORACLE單行函式與多行函式之六：通用函式示例
2013-11-02
Oracle函式
【SQL/PLUS】Oracle PL/SQL程式設計用set serveroutput on
2012-04-25
SQLOracle程式設計Server
併發程式設計之 Java 三把鎖
2019-02-27
程式設計Java
只把Oracle的儲存過程或者函式全部遷移到其他的資料庫中
2010-05-06
Oracle儲存過程函式資料庫
PL/SQL 函式包
2011-04-12
SQL函式
Oracle PL/SQL 程式設計基礎例項
2017-04-18
OracleSQL程式設計
PL/SQL程式設計(1)
2010-04-18
SQL程式設計
VBA過程―子程式與函式(轉)
2007-08-13
函式
Oracle PL/SQL 優化與調整 – PL/SQL Native Compilation 說明
2012-03-30
OracleSQL優化
透過程式把Domino郵件的MIME資訊輸出
2008-01-30
透過union all把sql查詢的結果拼接起來
2017-10-25
SQL
原創：oracle PL/SQL程式設計基礎上
2020-04-06
OracleSQL程式設計
原創：oracle PL/SQL程式設計基礎下
2020-04-06
OracleSQL程式設計
PL/SQL 06 函式 function
2014-02-12
SQL函式Function
PL/SQL程式設計急速上手
2020-06-09
SQL程式設計
PL/SQL程式設計接觸
2017-08-15
SQL程式設計
pl/sql程式設計基礎
2013-06-02
SQL程式設計
SQL server儲存過程函式
2024-08-09
SQLServer儲存過程函式
把自編儲存過程設定為系統儲存過程
2009-05-19
儲存過程
Oracle PL/SQL 優化與調整 -- Bulk 說明
2012-05-08
OracleSQL優化
把《程式設計珠璣》讀薄
2016-12-17
程式設計
窮追猛打，阿里二面問了我30分鐘從URL輸入到渲染...
2022-03-16
阿里
ORACLE PL/SQL程式設計詳解之一： PL/SQL 程式設計簡介(千里之行，始於足下)
2013-10-05
OracleSQL程式設計
mysql儲存函過程和儲存函式都屬於儲存程式
2013-03-17
MySql儲存函式
PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取(二)
2014-03-30
SQL函式快取
儲存過程與函式
2017-12-21
儲存過程函式
PL/SQL表---table()函式用法
2007-11-07
SQL函式
帝國cms把文章內容都存放到根目錄方法
2024-10-05

ORACLE PL/SQL程式設計之六：把過程與函式說透(窮追猛打，把根兒都拔起!)

6.1 引言

6.2 建立函式

6.3 儲存過程

6.3.1 建立過程

6.3.2 呼叫儲存過程

6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

6.3.5 開發儲存過程步驟

6.3.5.1 使用文字編輯處理軟體編輯儲存過程原始碼

6.3.5.2 在SQLPLUS或用除錯工具將儲存過程程式進行解釋

6.3.5.3 除錯原始碼直到正確

6.3.5.4 授權執行權給相關的使用者或角色

6.3.5.5 與過程相關資料字典

6.3.6 刪除過程和函式

6.3.7 過程與函式的比較

相關文章