您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 進階教學：存儲過程、函數與套件完整指南

編輯：Oracle 資料庫基礎

前言

在 Oracle 資料庫開發中，將商業邏輯封裝於資料庫層是提升效能、安全性與維護性的關鍵手段。本文將系統性介紹存儲過程、函數、套件以及自主事務的核心語法、參數模式、權限管理與最佳實踐，並附上完整可執行的程式碼範例。

一、存儲過程：基礎與進階

1.1 基本語法與建立

存儲過程是一個具名的 PL/SQL 區塊，儲存在資料庫中，可接受參數並執行特定任務。基本結構包含宣告區、執行區與例外處理區。

CREATE OR REPLACE PROCEDURE myproce (p_employee_id IN NUMBER) AS
  v_emp_id     NUMBER(8);
  v_first_name VARCHAR2(20);
  v_last_name  VARCHAR2(20);
BEGIN
  SELECT employee_id, first_name, last_name
    INTO v_emp_id, v_first_name, v_last_name
    FROM employees2
   WHERE employee_id = p_employee_id;

  DBMS_OUTPUT.PUT_LINE('Employee ID = ' || v_emp_id);
  DBMS_OUTPUT.PUT_LINE('First Name  = ' || v_first_name);
  DBMS_OUTPUT.PUT_LINE('Last Name   = ' || v_last_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無資料');
END;
/

1.2 執行存儲過程

SET SERVEROUTPUT ON;
DECLARE
  v_emp_id NUMBER(8) := 2;
BEGIN
  myproce(v_emp_id);
END;
/

1.3 參數傳遞模式

Oracle 提供三種參數模式，正確選擇對資料流向控制至關重要：

IN (預設)：唯讀，呼叫端必須提供值，過程內部不可修改。
OUT：唯寫，呼叫端傳入變數接收回傳值，過程內部必須賦值。
IN OUT：可讀寫，呼叫端傳入初始值，過程可修改並回傳。

範例：OUT 參數查詢員工職務與部門

CREATE OR REPLACE PROCEDURE show_info (
  p_employee_id IN  NUMBER,
  p_job_id      OUT VARCHAR2,
  p_division_id OUT VARCHAR2
) AS
  v_jid VARCHAR2(20);
  v_did VARCHAR2(20);
BEGIN
  SELECT job_id, division_id
    INTO v_jid, v_did
    FROM employees2
   WHERE employee_id = p_employee_id;

  p_job_id      := v_jid;
  p_division_id := v_did;
END;
/

範例：IN OUT 參數交換兩數值

CREATE OR REPLACE PROCEDURE swap (
  p_num1 IN OUT NUMBER,
  p_num2 IN OUT NUMBER
) AS
  v_temp NUMBER;
BEGIN
  v_temp := p_num1;
  p_num1 := p_num2;
  p_num2 := v_temp;
END;
/

1.4 權限授予與移除

-- 授予執行權限
GRANT EXECUTE ON show_info TO user1;
GRANT EXECUTE ON swap TO user1;

-- 移除存儲過程
DROP PROCEDURE show_info;

1.5 錯誤診斷與資料字典查詢

-- 查看編譯錯誤
SHOW ERRORS;

-- 查詢用戶擁有的過程/函數/套件資訊
SELECT object_name, object_type
  FROM user_procedures
 WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

-- 查詢原始碼
SELECT line, text
  FROM user_source
 WHERE name = 'MY_PROCEDURE'
 ORDER BY line;

二、函數：必須回傳值的子程式

函數與過程結構相似，但必須包含 RETURN 子句並回傳單一值，可直接用於 SQL 語句中。

2.1 簡單函數範例

CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS
BEGIN
  RETURN '朋友，您好！今天是 ' || TO_CHAR(SYSDATE, 'DAY');
END;
/

-- 執行方式
SELECT fun_hello FROM dual;

2.2 業務邏輯函數：判斷優秀員工

CREATE OR REPLACE FUNCTION my_function (p_employee_id NUMBER) RETURN VARCHAR2 IS
  v_first_name VARCHAR2(20);
  v_last_name  VARCHAR2(20);
  v_sal        NUMBER(8);
BEGIN
  SELECT first_name, last_name, salary
    INTO v_first_name, v_last_name, v_sal
    FROM employees2
   WHERE employee_id = p_employee_id;

  IF v_sal > 15000 THEN
    RETURN v_first_name || ' ' || v_last_name || ' 優秀員工';
  ELSE
    RETURN v_first_name || ' ' || v_last_name || ' 非優秀員工';
  END IF;
END;
/

2.3 呼叫函數

SET SERVEROUTPUT ON;
DECLARE
  v_info VARCHAR2(50);
BEGIN
  v_info := my_function(2);
  DBMS_OUTPUT.PUT_LINE(v_info);
END;
/

三、自主事務：獨立的交易控制

使用 PRAGMA AUTONOMOUS_TRANSACTION 宣告的程式區塊，會在主事務之外獨立提交或回滾，不影響主事務狀態。常用於日誌記錄、審計追蹤等場景。

CREATE OR REPLACE PROCEDURE my_proc_1 IS
  v_first_name VARCHAR2(20);
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT first_name INTO v_first_name FROM employees2 WHERE employee_id = 2;
  DBMS_OUTPUT.PUT_LINE('my_proc_1 firstname = ' || v_first_name);
  ROLLBACK; -- 僅回滾自主事務
END;
/

CREATE OR REPLACE PROCEDURE my_proc_2 IS
  v_first_name VARCHAR2(20);
BEGIN
  UPDATE employees2 SET first_name = 'scott' WHERE employee_id = 2;
  my_proc_1(); -- 呼叫自主事務過程
  SELECT first_name INTO v_first_name FROM employees2 WHERE employee_id = 2;
  DBMS_OUTPUT.PUT_LINE('my_proc_2 firstname = ' || v_first_name);
END;
/

EXECUTE my_proc_2;

四、套件：模組化封裝的最高形式

套件由規格與主體兩部分組成，將相關過程、函數、變數、游標、型別等封裝在一起，支援資訊隱藏與效能優化（整體載入記憶體）。

4.1 套件規格

CREATE OR REPLACE PACKAGE my_pack IS
  PROCEDURE employee2_proc (p_employee_id NUMBER);
  FUNCTION employee2_function RETURN VARCHAR2;
END my_pack;
/

4.2 套件主體

CREATE OR REPLACE PACKAGE BODY my_pack AS
  PROCEDURE employee2_proc (p_employee_id NUMBER) IS
    v_first_name  VARCHAR2(20);
    v_last_name   VARCHAR2(20);
    v_division_id VARCHAR2(20);
  BEGIN
    SELECT first_name, last_name, division_id
      INTO v_first_name, v_last_name, v_division_id
      FROM employees2
     WHERE employee_id = p_employee_id;

    DBMS_OUTPUT.PUT_LINE('First Name = ' || v_first_name ||
                         ' Last Name = ' || v_last_name ||
                         ' Division ID = ' || v_division_id);
  END employee2_proc;

  FUNCTION employee2_function RETURN VARCHAR2 IS
    v_job_id VARCHAR2(20);
    v_sal    NUMBER(8);
  BEGIN
    SELECT job_id, salary
      INTO v_job_id, v_sal
      FROM employees2
     WHERE employee_id = 2;

    IF v_job_id = 'MGR' THEN
      DBMS_OUTPUT.PUT_LINE('Job = Manager Salary = ' || v_sal);
    END IF;
    RETURN 'Function Executed';
  END employee2_function;
END my_pack;
/

4.3 呼叫套件元件

-- 呼叫過程
SET SERVEROUTPUT ON;
EXECUTE my_pack.employee2_proc(2);

-- 呼叫函數
DECLARE
  v_info VARCHAR2(50);
BEGIN
  v_info := my_pack.employee2_function;
  DBMS_OUTPUT.PUT_LINE('empinfo = ' || v_info);
END;
/

4.4 套件中的游標定義

在規格中宣告游標回傳型別，在主體中定義具體查詢，實現介面與實作分離。

-- 規格
CREATE OR REPLACE PACKAGE my_cur AS
  CURSOR employee_cur RETURN employees2%ROWTYPE;
END my_cur;
/

-- 主體
CREATE OR REPLACE PACKAGE BODY my_cur AS
  CURSOR employee_cur RETURN employees2%ROWTYPE IS
    SELECT * FROM employees2 WHERE salary > 120000;
END my_cur;
/

-- 使用套件游標
DECLARE
  v_rec employees2%ROWTYPE;
BEGIN
  OPEN my_cur.employee_cur;
  LOOP
    FETCH my_cur.employee_cur INTO v_rec;
    EXIT WHEN my_cur.employee_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID=' || v_rec.employee_id ||
                         ' Name=' || v_rec.first_name || ' ' || v_rec.last_name);
  END LOOP;
  CLOSE my_cur.employee_cur;
END;
/