【獨孤九劍】Oracle 實戰 (六) Procedure、Function、Sequence 進階應用

【獨孤九劍】Oracle 實戰 (六) Procedure、Function、Sequence 進階應用

預儲程序、函數與序列是業務邏輯封裝與主鍵生成的核心工具。本章涵蓋建立、參數模式、異常處理、序列緩存與管理實戰模式。

1. Procedure 預儲程序

建立範本 (含參數模式、異常處理、交易控制)

CREATE OR REPLACE PROCEDURE proc_get_person (
  p_name   IN  VARCHAR2,
  p_msg    OUT VARCHAR2,
  p_cursor OUT SYS_REFCURSOR
) AS
  v_name VARCHAR2(64);
BEGIN
  v_name := p_name;
  OPEN p_cursor FOR
    SELECT * FROM person WHERE name LIKE v_name || '%';
  p_msg := 'SUCCESS';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    p_msg := 'ERROR: ' || SQLERRM;
    ROLLBACK;
END proc_get_person;

• IN：輸入參數 (預設)，僅讀。
• OUT：輸出參數，需在程序內賦值。
• IN OUT：雙向傳遞。
• 參數宣告不指定長度，變數宣告需指定長度。

刪除程序

DROP PROCEDURE proc_get_person;

2. Function 函數

帶 IN 參數

CREATE OR REPLACE FUNCTION fun_get_name (
  p_name   IN VARCHAR2,
  p_gender IN VARCHAR2
) RETURN VARCHAR2 AS
  v_result VARCHAR2(64);
BEGIN
  SELECT name INTO v_result
    FROM person
   WHERE name = p_name AND gender = p_gender AND ROWNUM = 1;
  RETURN v_result;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END;

帶 OUT 參數

CREATE OR REPLACE FUNCTION fun_get_name2 (
  p_name   IN  VARCHAR2,
  p_gender OUT VARCHAR2
) RETURN VARCHAR2 AS
  v_result VARCHAR2(64);
BEGIN
  SELECT name, gender INTO v_result, p_gender
    FROM person
   WHERE name = p_name AND ROWNUM = 1;
  RETURN v_result;
END;

刪除函數

DROP FUNCTION fun_get_name;

3. Sequence 序列完整管理

建立序列

CREATE SEQUENCE seq_person_id
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  NOCYCLE
  CACHE 20;  -- 建議緩存 20~100，效能與間隙平衡

取得值

SELECT seq_person_id.NEXTVAL FROM dual;  -- 下一個值 (首次回傳 START WITH)
SELECT seq_person_id.CURRVAL FROM dual;  -- 當前 Session 最後值 (需先呼叫 NEXTVAL)

SQL 中直接使用

INSERT INTO person (id, name) VALUES (seq_person_id.NEXTVAL, '測試');

修改序列 (除 START WITH 外皆可)

ALTER SEQUENCE seq_person_id MAXVALUE 99999999;

需變更起始值 → DROP SEQUENCE 重建。

刪除序列

DROP SEQUENCE seq_person_id;

實戰最佳實務

• Procedure 回傳結果集請使用 SYS_REFCURSOR (弱型別 REF CURSOR)，彈性最高。
• Function 應盡量無副作用 (不修改表)，便於 SQL 語句中呼叫。
• 高併發主鍵生成：Sequence CACHE 100 + ORDER (RAC 環境) 平衡效能與有序性。
• 異常區塊務必 ROLLBACK 並記錄 SQLERRM/SQLCODE 至日誌表。