【Oracle】ORA-01008の原因と解決方法｜not all variables bound・バインド変数不足の直し方

-- SQL本文に2つのプレースホルダがある
SELECT *
FROM employees
WHERE department_id = :dept_id
  AND status = :status;

-- 実行時には :dept_id と :status の両方に値が必要

-- SQL Developerでは実行時に :dept_id と :status の値を入力する
SELECT *
FROM employees
WHERE department_id = :dept_id
  AND status = :status;

-- SQL*Plus / SQLclでは事前にバインド変数を定義して値を入れる
VARIABLE dept_id NUMBER
VARIABLE status VARCHAR2(20)
EXEC :dept_id := 10
EXEC :status := 'ACTIVE'

SELECT *
FROM employees
WHERE department_id = :dept_id
  AND status = :status;

-- SQLには :dept_id と :status がある
SELECT *
FROM employees
WHERE department_id = :dept_id
  AND status = :status;

-- NG: :status に値を渡していない
-- params = { dept_id: 10 }

-- OK: SQL内の名前と同じ名前で値を渡す
-- params = { dept_id: 10, status: 'ACTIVE' }

-- SQLには位置指定プレースホルダが2つある
SELECT *
FROM employees
WHERE department_id = :1
  AND status = :2;

-- NG: 1つしか渡していない
-- values = [10]

-- OK: :1, :2 の順番で2つ渡す
-- values = [10, 'ACTIVE']

-- 動的SQLが通常のSQL文の場合、出現位置ごとにUSINGへ値を渡す
DECLARE
    v_sql VARCHAR2(200);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :x OR manager_id = :x';

    -- NG: :x が2回出ているのに1つしか渡していない
    -- EXECUTE IMMEDIATE v_sql INTO v_count USING 10;

    -- OK: 出現位置ごとに渡す
    EXECUTE IMMEDIATE v_sql INTO v_count USING 10, 10;
END;
/

-- NG: :status に対応するUSING値がない
DECLARE
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id AND status = :status';
    EXECUTE IMMEDIATE v_sql INTO v_count USING 10;
END;
/

-- OK: SQL内のプレースホルダ数に合わせる
DECLARE
    v_sql VARCHAR2(1000);
    v_count NUMBER;
BEGIN
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id AND status = :status';
    EXECUTE IMMEDIATE v_sql INTO v_count USING 10, 'ACTIVE';
END;
/

-- OK: 入力値はUSING、戻り値はRETURNING INTO
DECLARE
    v_sql VARCHAR2(1000);
    v_employee_id NUMBER;
BEGIN
    v_sql := 'INSERT INTO employees_work (employee_name, status) VALUES (:name, :status) RETURNING employee_id INTO :id';

    EXECUTE IMMEDIATE v_sql
        USING 'Sato', 'ACTIVE'
        RETURNING INTO v_employee_id;
END;
/

DECLARE
    v_sql VARCHAR2(1000);
    v_null VARCHAR2(1);
BEGIN
    v_sql := 'UPDATE employees_work SET memo = :memo WHERE employee_id = :employee_id';

    -- v_null は未初期化なのでNULLとして扱われる
    EXECUTE IMMEDIATE v_sql USING v_null, 100;
END;
/

-- NGの考え方: SQLには条件を足したが、値を足していない
SELECT *
FROM employees
WHERE 1 = 1
  AND department_id = :dept_id
  AND status = :status;

-- params = { dept_id: 10 }  -- :status が不足

-- OK: 条件と値をセットで追加する
-- if status is not null:
--     sql += ' AND status = :status'
--     params['status'] = status

-- NG: 列名をバインド変数として選択しようとしている
SELECT :column_name
FROM employees
WHERE employee_id = :employee_id;

-- OK: 列名はホワイトリストで選び、値だけをバインドする
SELECT employee_name
FROM employees
WHERE employee_id = :employee_id;

-- NG: SQLには2つの値が必要だが、1つしか渡していない
sql = "SELECT * FROM employees WHERE department_id = :dept_id AND status = :status"
params = { "dept_id": 10 }

-- OK: すべてのプレースホルダに値を渡す
params = { "dept_id": 10, "status": "ACTIVE" }

-- JDBCなど位置指定APIの場合は、番号の抜けにも注意
preparedStatement.setInt(1, 10)
preparedStatement.setString(2, "ACTIVE")