【PL/SQL】プロシージャ・ファンクション完全ガイド｜10軸比較・戻り値5種・DETERMINISTIC／RESULT_CACHE・AUTHID・実務10パターン

-- プロシージャ（値を返さない、OUTパラメータで複数値可）
CREATE OR REPLACE PROCEDURE <名前>(
  p_引数 IN|OUT|IN OUT 型,
  ...
) IS
  -- ローカル変数
BEGIN
  -- 処理
EXCEPTION
  -- 例外処理
END <名前>;
/

-- ファンクション（必ず戻り値、SELECT内でも使用可）
CREATE OR REPLACE FUNCTION <名前>(
  p_引数 IN 型,
  ...
) RETURN <戻り型> IS
  v_result <戻り型>;
BEGIN
  -- 処理
  RETURN v_result;       -- 必須
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END <名前>;
/

CREATE OR REPLACE PROCEDURE update_user_status(
  p_user_id IN NUMBER,
  p_status  IN VARCHAR2 DEFAULT 'active'   -- デフォルト値指定可
) IS
  v_count NUMBER;
BEGIN
  UPDATE users
     SET status     = p_status,
         updated_at = SYSTIMESTAMP
   WHERE id = p_user_id;

  v_count := SQL%ROWCOUNT;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'ユーザーが見つかりません: ' || p_user_id);
  END IF;

  -- 監査ログも残す
  INSERT INTO user_status_log(user_id, new_status, changed_at)
  VALUES (p_user_id, p_status, SYSTIMESTAMP);
EXCEPTION
  WHEN OTHERS THEN
    pkg_error.log(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END update_user_status;
/

-- パターン①：PL/SQLブロック内（位置引数）
BEGIN
  update_user_status(1, 'inactive');
END;
/

-- パターン②：名前付き引数（推奨：可読性UP）
BEGIN
  update_user_status(
    p_user_id => 1,
    p_status  => 'inactive'
  );
END;
/

-- パターン③：デフォルト値の活用
BEGIN
  update_user_status(p_user_id => 1);   -- p_status は 'active' になる
END;

-- パターン④：SQL*Plus / SQLcl の CALL文
CALL update_user_status(1, 'inactive');

-- パターン⑤：Javaからの呼び出し（JDBC CallableStatement）
CallableStatement cs = conn.prepareCall("{ call update_user_status(?, ?) }");

CREATE OR REPLACE FUNCTION get_user_full_name(
  p_user_id IN NUMBER
) RETURN VARCHAR2
DETERMINISTIC            -- 同入力→同出力保証（SQL内で最適化される）
RESULT_CACHE             -- 結果をSGAキャッシュ（12c+）
IS
  v_first  users.first_name%TYPE;
  v_last   users.last_name%TYPE;
BEGIN
  SELECT first_name, last_name
    INTO v_first, v_last
    FROM users
   WHERE id = p_user_id;

  RETURN v_last || ' ' || v_first;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END get_user_full_name;
/

-- パターン①：変数に代入
DECLARE
  v_name VARCHAR2(200);
BEGIN
  v_name := get_user_full_name(1);
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

-- パターン②：SELECT内で利用（ここがファンクションの真価）
SELECT id, get_user_full_name(id) AS full_name
  FROM users
 WHERE status = 'active';

-- パターン③：WHERE条件にも
SELECT *
  FROM orders
 WHERE get_order_total(id) > 10000;

-- パターン④：名前付き引数
DECLARE
  v_name VARCHAR2(200);
BEGIN
  v_name := get_user_full_name(p_user_id => 1);
END;

CREATE OR REPLACE PROCEDURE calculate_order(
  p_order_id IN  NUMBER,                     -- 入力
  p_total    OUT NUMBER,                     -- 合計金額を返す
  p_tax      OUT NUMBER,                     -- 税額を返す
  p_discount IN OUT NUMBER                   -- 割引率を受け取り、最終割引額を返す
) IS
  v_subtotal NUMBER;
BEGIN
  SELECT SUM(unit_price * quantity) INTO v_subtotal
    FROM order_items
   WHERE order_id = p_order_id;

  p_discount := v_subtotal * NVL(p_discount, 0) / 100;   -- 入力率→出力金額に加工
  v_subtotal := v_subtotal - p_discount;
  p_tax   := ROUND(v_subtotal * 0.10, 0);
  p_total := v_subtotal + p_tax;
END;
/

-- 呼び出し
DECLARE
  v_total    NUMBER;
  v_tax      NUMBER;
  v_discount NUMBER := 10;   -- 10%割引
BEGIN
  calculate_order(
    p_order_id => 100,
    p_total    => v_total,
    p_tax      => v_tax,
    p_discount => v_discount
  );
  DBMS_OUTPUT.PUT_LINE('total=' || v_total || ', tax=' || v_tax || ', disc=' || v_discount);
END;

CREATE OR REPLACE FUNCTION calc_age(p_birthday DATE)
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_birthday) / 12);
END;

-- 複数の値を構造体で返す（呼び出し元はPL/SQLのみ）
CREATE OR REPLACE PACKAGE pkg_user IS
  TYPE t_user_summary IS RECORD (
    id          NUMBER,
    name        VARCHAR2(100),
    order_count NUMBER
  );

  FUNCTION get_user_summary(p_id IN NUMBER) RETURN t_user_summary;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_user IS
  FUNCTION get_user_summary(p_id IN NUMBER) RETURN t_user_summary IS
    v_rec t_user_summary;
  BEGIN
    SELECT u.id, u.name, COUNT(o.id)
      INTO v_rec
      FROM users u
      LEFT JOIN orders o ON o.user_id = u.id
     WHERE u.id = p_id
     GROUP BY u.id, u.name;
    RETURN v_rec;
  END;
END;

CREATE OR REPLACE TYPE t_numbers AS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION get_order_ids(p_user_id IN NUMBER)
RETURN t_numbers
PIPELINED            -- ストリーム返却（省メモリ）
IS
BEGIN
  FOR rec IN (SELECT id FROM orders WHERE user_id = p_user_id) LOOP
    PIPE ROW(rec.id);    -- 1行ずつ返す
  END LOOP;
  RETURN;
END;
/

-- SQL内で TABLE() として展開
SELECT * FROM TABLE(get_order_ids(1));

CREATE OR REPLACE FUNCTION search_users(
  p_status IN VARCHAR2
) RETURN SYS_REFCURSOR IS
  v_rc SYS_REFCURSOR;
BEGIN
  OPEN v_rc FOR
    SELECT id, email FROM users
     WHERE status = p_status
     ORDER BY created_at DESC;
  RETURN v_rc;
END;
/

-- 呼び出し（Javaや.NETも受け取れる）
DECLARE
  v_rc SYS_REFCURSOR;
  v_id NUMBER;
  v_email VARCHAR2(200);
BEGIN
  v_rc := search_users('active');
  LOOP
    FETCH v_rc INTO v_id, v_email;
    EXIT WHEN v_rc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_email);
  END LOOP;
  CLOSE v_rc;
END;

CREATE OR REPLACE FUNCTION is_admin(p_user_id IN NUMBER) RETURN BOOLEAN IS
  v_role VARCHAR2(20);
BEGIN
  SELECT role INTO v_role FROM users WHERE id = p_user_id;
  RETURN v_role = 'admin';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END;
/

BEGIN
  IF is_admin(p_user_id => 1) THEN
    DBMS_OUTPUT.PUT_LINE('管理者です');
  END IF;
END;

CREATE OR REPLACE PACKAGE pkg_formatter IS
  -- NUMBER版
  FUNCTION fmt(p_val IN NUMBER) RETURN VARCHAR2;

  -- DATE版
  FUNCTION fmt(p_val IN DATE) RETURN VARCHAR2;

  -- DATE + フォーマット指定版
  FUNCTION fmt(p_val IN DATE, p_format IN VARCHAR2) RETURN VARCHAR2;

  -- VARCHAR2版
  FUNCTION fmt(p_val IN VARCHAR2) RETURN VARCHAR2;
END pkg_formatter;
/

CREATE OR REPLACE PACKAGE BODY pkg_formatter IS
  FUNCTION fmt(p_val IN NUMBER) RETURN VARCHAR2 IS
  BEGIN RETURN TO_CHAR(p_val, 'FM9,999,999,999'); END;

  FUNCTION fmt(p_val IN DATE) RETURN VARCHAR2 IS
  BEGIN RETURN TO_CHAR(p_val, 'YYYY-MM-DD'); END;

  FUNCTION fmt(p_val IN DATE, p_format IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN RETURN TO_CHAR(p_val, p_format); END;

  FUNCTION fmt(p_val IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN RETURN NVL(p_val, '(null)'); END;
END;
/

-- 呼び出し側は型で自動選択
SELECT pkg_formatter.fmt(12345)       AS num_fmt,
       pkg_formatter.fmt(SYSDATE)     AS date_fmt,
       pkg_formatter.fmt(SYSDATE, 'MM/DD') AS date_fmt2
FROM dual;

-- 同じ入力に対して同じ出力を保証
CREATE OR REPLACE FUNCTION square(p_x IN NUMBER) RETURN NUMBER
DETERMINISTIC IS
BEGIN
  RETURN p_x * p_x;
END;

-- SQL内で同じ引数で呼ばれたら再計算されずキャッシュされる
SELECT square(x) FROM t;   -- 内部的に同じxに対しては1回だけ計算

-- SGAの結果キャッシュ領域を使う
CREATE OR REPLACE FUNCTION get_tax_rate(p_country IN VARCHAR2)
RETURN NUMBER
RESULT_CACHE
RELIES_ON(tax_rates)   -- このテーブルが変わるとキャッシュ自動無効化
IS
  v_rate NUMBER;
BEGIN
  SELECT rate INTO v_rate FROM tax_rates WHERE country = p_country;
  RETURN v_rate;
END;

-- パラレルクエリ内で使用可能にする
CREATE OR REPLACE FUNCTION process_row(p_val IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE IS
BEGIN
  RETURN p_val * 2;
END;

-- 大量データの加工で並列度を上げられる
SELECT /*+ PARALLEL(8) */ process_row(val) FROM big_table;

-- メモリに全件溜めず1行ずつ返す（超大量データ向き）
CREATE OR REPLACE FUNCTION generate_dates(
  p_start IN DATE,
  p_end   IN DATE
) RETURN t_dates PIPELINED IS
  v_cur DATE := p_start;
BEGIN
  WHILE v_cur <= p_end LOOP
    PIPE ROW(v_cur);
    v_cur := v_cur + 1;
  END LOOP;
  RETURN;
END;

SELECT * FROM TABLE(generate_dates(DATE '2026-04-01', DATE '2026-04-30'));

-- Definer Rights（デフォルト、省略時）
-- → プロシージャ所有者の権限で実行
CREATE OR REPLACE PROCEDURE get_secret
AUTHID DEFINER    -- 明示してもOK、省略時のデフォルト
IS
  v_secret VARCHAR2(100);
BEGIN
  SELECT value INTO v_secret FROM admin.secrets WHERE id = 1;
  -- 呼び出し側が admin.secrets への権限を持たなくても実行できる
END;

-- Invoker Rights（呼び出し側の権限）
-- → 呼び出したユーザーの権限で実行
CREATE OR REPLACE PROCEDURE show_my_data
AUTHID CURRENT_USER   -- 呼び出したユーザー自身の権限
IS
BEGIN
  -- 呼び出したユーザーが持つテーブルやデータだけアクセス
  FOR rec IN (SELECT * FROM user_tables) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.table_name);
  END LOOP;
END;

-- エラーログのようにメイン処理がROLLBACKしても残したいデータを書く
CREATE OR REPLACE PROCEDURE log_error(
  p_code IN NUMBER,
  p_msg  IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log(error_time, error_code, error_msg)
  VALUES (SYSTIMESTAMP, p_code, p_msg);
  COMMIT;     -- 独立トランザクションなので呼び出し元に影響なし
END;
/

-- 呼び出し側
BEGIN
  INSERT INTO orders VALUES (...);   -- メイン処理
  log_error(0, 'checkpoint');       -- ログ保存（独立トランザクション）
  RAISE_APPLICATION_ERROR(-20001, 'テスト');   -- 例外発生
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM);     -- ログは残る
    ROLLBACK;                         -- メイン処理はROLLBACK（ログはそのまま）
END;

DECLARE
  -- ブロック内だけで使うヘルパー関数
  FUNCTION local_format(p_n IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    RETURN TO_CHAR(p_n, 'FM9,999,999,999');
  END local_format;

  -- ローカルプロシージャ
  PROCEDURE log_info(p_msg IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('[INFO] ' || p_msg);
  END log_info;
BEGIN
  log_info('total: ' || local_format(1234567));
END;
/

WITH FUNCTION fmt(p_n NUMBER) RETURN VARCHAR2 IS
BEGIN
  RETURN TO_CHAR(p_n, 'FM9,999,999,999');
END;
SELECT id, fmt(amount) AS formatted FROM orders
/

-- SQL文内だけで使える使い捨て関数
-- ストアドファンクションより呼び出しコストが低い
-- 詳細は【PL/SQL】WITH FUNCTION句ガイドを参照

CREATE OR REPLACE FUNCTION factorial(p_n IN NUMBER) RETURN NUMBER IS
BEGIN
  IF p_n <= 1 THEN
    RETURN 1;
  END IF;
  RETURN p_n * factorial(p_n - 1);   -- 自分自身を呼ぶ
END;
/

SELECT factorial(10) FROM dual;  -- 3628800

-- 階層データの探索
CREATE OR REPLACE PROCEDURE print_tree(
  p_node_id IN NUMBER,
  p_depth   IN NUMBER DEFAULT 0
) IS
BEGIN
  FOR rec IN (SELECT id, name FROM nodes WHERE parent_id = p_node_id) LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(' ', p_depth * 2) || rec.name);
    print_tree(rec.id, p_depth + 1);   -- 再帰
  END LOOP;
END;

CREATE OR REPLACE PROCEDURE register_user(
  p_email    IN VARCHAR2,
  p_password IN VARCHAR2,
  p_user_id  OUT NUMBER
) IS
BEGIN
  IF p_email IS NULL OR NOT REGEXP_LIKE(p_email, '^[^@]+@[^@]+\.[^@]+$') THEN
    RAISE_APPLICATION_ERROR(-20001, '無効なメールアドレス');
  END IF;
  IF LENGTH(p_password) < 8 THEN
    RAISE_APPLICATION_ERROR(-20002, 'パスワードは8文字以上');
  END IF;

  INSERT INTO users(email, password_hash)
  VALUES (p_email, hash_password(p_password))
  RETURNING id INTO p_user_id;
END;

CREATE OR REPLACE FUNCTION calc_tax(p_amount IN NUMBER) RETURN NUMBER
DETERMINISTIC IS
BEGIN
  RETURN ROUND(p_amount * 0.10);
END;

-- 派生列として使用
SELECT id, amount, calc_tax(amount) AS tax FROM orders;

CREATE OR REPLACE PROCEDURE get_order_stats(
  p_user_id  IN  NUMBER,
  p_count    OUT NUMBER,
  p_total    OUT NUMBER,
  p_avg      OUT NUMBER
) IS
BEGIN
  SELECT COUNT(*), SUM(amount), AVG(amount)
    INTO p_count, p_total, p_avg
    FROM orders
   WHERE user_id = p_user_id;
END;

CREATE OR REPLACE FUNCTION search_products(
  p_keyword IN VARCHAR2
) RETURN SYS_REFCURSOR IS
  v_rc SYS_REFCURSOR;
BEGIN
  OPEN v_rc FOR
    SELECT id, name, price FROM products
    WHERE LOWER(name) LIKE '%' || LOWER(p_keyword) || '%';
  RETURN v_rc;
END;

CREATE OR REPLACE PROCEDURE close_expired_orders IS
  v_count NUMBER;
BEGIN
  UPDATE orders SET status = 'closed'
   WHERE status = 'pending'
     AND created_at < SYSDATE - 30;
  v_count := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE(v_count || '件をcloseしました');
  COMMIT;
END;

CREATE OR REPLACE FUNCTION total_subordinates(p_mgr_id IN NUMBER) RETURN NUMBER IS
  v_total NUMBER := 0;
BEGIN
  FOR rec IN (SELECT id FROM employees WHERE manager_id = p_mgr_id) LOOP
    v_total := v_total + 1 + total_subordinates(rec.id);
  END LOOP;
  RETURN v_total;
END;

CREATE OR REPLACE PROCEDURE log_to_audit(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(ts, msg) VALUES (SYSTIMESTAMP, p_msg);
  COMMIT;
END;

CREATE OR REPLACE FUNCTION split_csv(p_str IN VARCHAR2)
RETURN t_strings PIPELINED IS
  v_part VARCHAR2(4000);
  v_pos  PLS_INTEGER := 1;
  v_next PLS_INTEGER;
BEGIN
  LOOP
    v_next := INSTR(p_str, ',', v_pos);
    IF v_next = 0 THEN
      PIPE ROW(SUBSTR(p_str, v_pos));
      EXIT;
    END IF;
    PIPE ROW(SUBSTR(p_str, v_pos, v_next - v_pos));
    v_pos := v_next + 1;
  END LOOP;
  RETURN;
END;

SELECT * FROM TABLE(split_csv('apple,banana,cherry'));

CREATE OR REPLACE FUNCTION get_country_name(p_code IN VARCHAR2)
RETURN VARCHAR2
RESULT_CACHE RELIES_ON(countries) IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM countries WHERE code = p_code;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END;

CREATE OR REPLACE PACKAGE pkg_notify IS
  PROCEDURE send(p_user_id IN NUMBER, p_msg IN VARCHAR2);
  PROCEDURE send(p_emails IN VARCHAR2_ARRAY, p_msg IN VARCHAR2);
  PROCEDURE send(p_user IN users%ROWTYPE, p_msg IN VARCHAR2);
END;