Oracle PL/SQL 子程序精要之一：存儲過程完整語法、參數模式、調用方式與維護操作指南

子程序基礎概念

子程序 = 命名 PL/SQL 區塊，可接受參數、被其他程式調用。兩類：過程 (執行動作)、函數 (返回值)。結構包含宣告部、執行部、可選例外部。

存儲過程完整語法

CREATE [OR REPLACE] PROCEDURE procedure_name
  [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ]
  [AUTHID DEFINER | CURRENT_USER]
IS | AS
  -- 宣告區 (變數、游標、型別、嵌套子程序)
BEGIN
  -- 執行區 (SQL、流程控制、呼叫其他子程序)
  [EXCEPTION
    -- 例外處理區]
END [procedure_name];

七大實戰範例涵蓋核心場景

範例 1：無參數過程 (區塊級暫時)

DECLARE
  PROCEDURE get_dept_count AS
    v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_cnt FROM dept;
    DBMS_OUTPUT.PUT_LINE('DEPT 筆數: ' || v_cnt);
  END;
BEGIN
  get_dept_count;
END;

範例 2：無參數永久存儲過程

CREATE OR REPLACE PROCEDURE get_dept_count AS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM dept;
  DBMS_OUTPUT.PUT_LINE('DEPT 筆數: ' || v_cnt);
END;

• 定義時名稱後 不可加 ()。
• 調用：BEGIN get_dept_count; END; 或 EXEC get_dept_count 或 CALL get_dept_count()。

範例 3：IN 參數 (預設模式)

CREATE OR REPLACE PROCEDURE get_salary_by_empno (e_no NUMBER) AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = e_no;
  DBMS_OUTPUT.PUT_LINE('員工 ' || e_no || ' 薪資: ' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無此員工');
END;

• 參數型別 不可指定長度/精度 (如 NUMBER 而非 NUMBER(10))。
• 調用可用位置標記或具名標記：EXEC get_salary_by_empno(7788)。

範例 4：OUT 參數 (返回值)

CREATE OR REPLACE PROCEDURE get_salary_by_empno (
  e_no  IN  NUMBER,
  v_sal OUT NUMBER
) AS
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = e_no;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_sal := NULL;
END;

• 調用 必須在 PL/SQL 區塊中，不可用 EXEC/CALL：

  
  DECLARE
    v_sal NUMBER;
  BEGIN
    get_salary_by_empno(7788, v_sal);
    DBMS_OUTPUT.PUT_LINE('薪資: ' || v_sal);
  END;
  

範例 5：IN OUT 參數 (雙向傳遞)

CREATE OR REPLACE PROCEDURE get_salary_by_empno (v_no_sal IN OUT NUMBER) AS
BEGIN
  SELECT sal INTO v_no_sal FROM emp WHERE empno = v_no_sal;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_no_sal := NULL;
END;

• 調用前變數需預設輸入值，執行後被輸出值覆蓋。

範例 6：預設值參數與具名標記調用

CREATE OR REPLACE PROCEDURE add_emp (
  emp_no    NUMBER,
  e_name    VARCHAR2,
  job       VARCHAR2 DEFAULT 'CLERK',
  mgr       NUMBER,
  hiredate  DATE DEFAULT SYSDATE,
  sal       NUMBER DEFAULT 1000,
  comm      NUMBER DEFAULT 0,
  dept_no   NUMBER DEFAULT 30
) AS
BEGIN
  INSERT INTO emp VALUES (emp_no, e_name, job, mgr, hiredate, sal, comm, dept_no);
END;

• 調用靈活：EXEC add_emp(7776,'zhangsan','CODER',7788,'06-JAN-2000',2000,0,10) (全指定)
• EXEC add_emp(7777,'lisi',mgr=>7788) (具名標記，其餘用預設值)
• EXEC add_emp(mgr=>7788, emp_no=>7779, e_name=>'sunliu') (順序任意)

範例 7：NOCOPY 編譯提示 (大型集合/記錄/物件)

DECLARE
  TYPE dept_list IS TABLE OF VARCHAR2(10);
  v_list dept_list := dept_list('A','B','C','D');
  PROCEDURE my_proc (p_list IN OUT NOCOPY dept_list) AS
  BEGIN
    p_list.EXTEND; p_list(p_list.LAST) := 'E';
  END;
BEGIN
  my_proc(v_list);
  -- v_list 已被修改，無拷貝開銷
END;

• NOCOPY 僅為提示，編譯器可能忽略 (如約束檢查、異常處理需副本時)。
• 正常情況下成功以引用傳遞，大幅降低記憶體與 CPU 開銷。

存儲過程維護操作速查

關鍵調用規則總結表