【Oracle】ストアドプロシージャ・ファンクションの作成完全ガイド｜CREATE PROCEDURE・IN/OUT・例外処理・実務パターン

CREATE OR REPLACE PROCEDURE proc_hello
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, Oracle!');
END;
/

CREATE OR REPLACE PROCEDURE proc_update_salary (
    p_employee_id  IN NUMBER,      -- 入力パラメータ（デフォルトは IN）
    p_new_salary   IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001,
            '社員ID ' || p_employee_id || ' が見つかりません');
    END IF;

    COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE proc_get_employee_info (
    p_employee_id  IN  NUMBER,
    p_name         OUT VARCHAR2,    -- 出力パラメータ
    p_salary       OUT NUMBER
)
IS
BEGIN
    SELECT name, salary
    INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_employee_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name   := NULL;
        p_salary := NULL;
END;
/

CREATE OR REPLACE PROCEDURE proc_apply_tax (
    p_amount  IN OUT NUMBER    -- 税抜金額を受け取り、税込金額で上書きして返す
)
IS
    c_tax_rate CONSTANT NUMBER := 0.10;  -- 消費税率 10%
BEGIN
    p_amount := p_amount * (1 + c_tax_rate);
END;
/

CREATE OR REPLACE FUNCTION fn_get_dept_name (
    p_department_id  IN NUMBER
) RETURN VARCHAR2
IS
    v_dept_name VARCHAR2(100);
BEGIN
    SELECT department_name
    INTO v_dept_name
    FROM departments
    WHERE department_id = p_department_id;

    RETURN v_dept_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- SQL文の中でファンクションを呼び出せる
SELECT
    employee_id,
    name,
    salary,
    fn_get_dept_name(department_id) AS dept_name
FROM employees
WHERE department_id = 10;

CREATE OR REPLACE FUNCTION fn_calc_age (
    p_birth_date  IN DATE
) RETURN NUMBER
IS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_birth_date) / 12);
END;
/

-- 使用例
SELECT name, birth_date, fn_calc_age(birth_date) AS age
FROM employees;

CREATE OR REPLACE PROCEDURE proc_transfer_salary (
    p_from_id  IN NUMBER,
    p_to_id    IN NUMBER,
    p_amount   IN NUMBER
)
IS
BEGIN
    -- 送金元から減算
    UPDATE employees SET salary = salary - p_amount
    WHERE employee_id = p_from_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '送金元の社員が見つかりません');
    END IF;

    -- 送金先に加算
    UPDATE employees SET salary = salary + p_amount
    WHERE employee_id = p_to_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, '送金先の社員が見つかりません');
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('エラー: ' || SQLERRM);
        RAISE;  -- エラーを呼び出し元に再送出
END;
/

-- SQL*Plus / SQL Developer から実行
EXECUTE proc_update_salary(1001, 450000);
-- または
CALL proc_update_salary(1001, 450000);

-- PL/SQL ブロック内で実行
BEGIN
    proc_update_salary(1001, 450000);
END;
/

-- OUT パラメータがあるプロシージャの実行
DECLARE
    v_name   VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    proc_get_employee_info(1001, v_name, v_salary);
    DBMS_OUTPUT.PUT_LINE(v_name || ' : ' || v_salary);
END;
/

-- SELECT 文内で実行
SELECT fn_get_dept_name(10) AS dept_name FROM dual;

-- PL/SQL ブロック内で実行
DECLARE
    v_name VARCHAR2(100);
BEGIN
    v_name := fn_get_dept_name(10);
    DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

DROP PROCEDURE proc_hello;
DROP FUNCTION fn_get_dept_name;

-- 最後にコンパイルしたオブジェクトのエラーを表示（SQL*Plus）
SHOW ERRORS;

-- 特定のプロシージャのエラーを表示
SHOW ERRORS PROCEDURE proc_update_salary;

-- SQLでエラー一覧を取得する
SELECT line, position, text
FROM user_errors
WHERE name = 'PROC_UPDATE_SALARY'
  AND type = 'PROCEDURE'
ORDER BY line, position;

SELECT line, text
FROM user_source
WHERE name = 'PROC_UPDATE_SALARY'
  AND type = 'PROCEDURE'
ORDER BY line;

CREATE OR REPLACE PROCEDURE proc_search_employees (
    p_department_id  IN NUMBER   DEFAULT NULL,   -- 省略可能（全部門対象）
    p_min_salary     IN NUMBER   DEFAULT 0       -- 省略可能（最低給与なし）
)
IS
BEGIN
    FOR r IN (
        SELECT employee_id, name, salary, department_id
        FROM employees
        WHERE (p_department_id IS NULL OR department_id = p_department_id)
          AND salary >= p_min_salary
        ORDER BY salary DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(r.name || ' : ' || r.salary);
    END LOOP;
END;
/

-- 呼び出し方（名前付きパラメータ指定が便利）
BEGIN
    proc_search_employees(p_min_salary => 400000);  -- 部門指定なし、最低給与40万以上
END;
/

CREATE OR REPLACE PROCEDURE proc_monthly_aggregate (
    p_target_month  IN VARCHAR2    -- 'YYYY-MM' 形式
)
IS
    v_start_time TIMESTAMP := SYSTIMESTAMP;
    v_count      NUMBER := 0;
BEGIN
    -- 集計処理
    INSERT INTO monthly_summary (yr_month, dept_id, total_sales)
    SELECT p_target_month, department_id, SUM(amount)
    FROM sales
    WHERE TO_CHAR(sale_date, 'YYYY-MM') = p_target_month
    GROUP BY department_id;

    v_count := SQL%ROWCOUNT;
    COMMIT;

    -- ログ記録
    INSERT INTO batch_log (proc_name, target_month, row_count, started_at, finished_at, status)
    VALUES ('proc_monthly_aggregate', p_target_month, v_count, v_start_time, SYSTIMESTAMP, 'SUCCESS');
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        INSERT INTO batch_log (proc_name, target_month, row_count, started_at, finished_at, status, error_msg)
        VALUES ('proc_monthly_aggregate', p_target_month, 0, v_start_time, SYSTIMESTAMP, 'ERROR', SQLERRM);
        COMMIT;
        RAISE;
END;
/