萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 子程序詳解(一):存儲過程建立、參數模式與調用方式全攻略

基本結構

CREATE [OR REPLACE] PROCEDURE proc_name
  (param1 [IN|OUT|IN OUT] datatype, ...)
IS | AS
  -- 宣告區 (無 DECLARE 關鍵字)
BEGIN
  -- 執行區
EXCEPTION
  -- 異常區
END [proc_name];

三種參數模式

模式說明預設值可讀/可寫
IN唯讀,呼叫端傳入可指定 DEFAULTR / -
OUT唯寫,回傳值給呼叫端不可指定- / W
IN OUT可讀可寫不可指定R / W

建立範例

1. 無參數

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;

2. IN 參數

CREATE OR REPLACE PROCEDURE get_sal_by_eno (p_eno IN NUMBER) AS
  v_sal EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO v_sal FROM EMP WHERE EMPNO = p_eno;
  DBMS_OUTPUT.PUT_LINE('員工 '||p_eno||' 薪資: '||v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('查無此員工');
END;

3. OUT 參數

CREATE OR REPLACE PROCEDURE get_ename_job (
  p_eno IN NUMBER,
  p_ename OUT VARCHAR2,
  p_job   OUT VARCHAR2) AS
BEGIN
  SELECT ENAME, JOB INTO p_ename, p_job FROM EMP WHERE EMPNO = p_eno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_ename := 'NOT FOUND'; p_job := 'N/A';
END;

4. IN OUT 參數

CREATE OR REPLACE PROCEDURE increase_sal (
  p_eno IN NUMBER,
  p_sal IN OUT NUMBER) AS
BEGIN
  SELECT SAL INTO p_sal FROM EMP WHERE EMPNO = p_eno;
  p_sal := p_sal * 1.1;
  UPDATE EMP SET SAL = p_sal WHERE EMPNO = p_eno;
END;

四種調用方式

BEGIN proc_name(args); END;EXEC proc_name(args);CALL proc_name(args);CallableStatement cs = conn.prepareCall("{call proc_name(?,?)}");
方式語法適用場景
匿名區塊測試、腳本
EXECUTESQL*Plus/SQL Developer 互動
CALLSQL 標準、需 OUT 參數時必加 ()
應用程式 (JDBC/ODP.NET)生產環境

常見錯誤與除錯

  • 建立時加 () 於無參過程名後 → PLS-00103。
  • CALL 呼叫無參過程必須加 ()。
  • 編譯錯誤查看:SHOW ERRORS PROCEDURE proc_name 或查 USER_ERRORS。
  • 權限:GRANT EXECUTE ON proc_name TO user;
copyright © 萬盛學電腦網 all rights reserved