Oracle PL/SQL パラメータ付きカーソル

パラメータ付きカーソルについて

PL/SQLのカーソル処理にパラメータを設定して処理したいときの方法を解説します。

「パラメータ付きカーソル」の基本構文

基本構文という程のものでもないですが、パラメータ付きカーソルを定義するためには、仮パラメータを用意する必要があります。

--宣言部の定義
CURSOR <カーソル名> (<仮パラメータ名> <データ型>)
    IS SELECT ~ WHERE ~ <仮パラメータ名>;

--実行部の定義
FOR <ループ索引名> IN <カーソル名> (<実パラメータ名>) LOOP
    <実行したい処理を記述>
END LOOP;

「パラメータ付きカーソル」の実行例

テスト用のテーブルとデータは以下を使用します。

CREATE TABLE animal1
(
    id       VARCHAR2(10)
    ,name    VARCHAR2(20)
    ,address VARCHAR2(20)
);

INSERT ALL
INTO animal1 VALUES('001', 'いるか', '海')
INTO animal1 VALUES('002', 'ぺんぎん', '空')
INTO animal1 VALUES('003', 'うさぎ', '空')
INTO animal1 VALUES('004', 'ふくろう', '空')
SELECT * FROM dual;

実行する「パラメータ付きカーソル」のプログラムは以下を使用します。

DECLARE
    CURSOR animal_cur(v_address VARCHAR2) IS 
        SELECT id, name FROM animal1 WHERE address = v_address;
    v_tmp VARCHAR2(20);
BEGIN
    v_tmp := &a;
    FOR animal_rec IN animal_cur(v_tmp) LOOP
        DBMS_OUTPUT.PUT_LINE( 'id:' || animal_rec.id );
        DBMS_OUTPUT.PUT_LINE( 'name:' || animal_rec.name );
    END LOOP;
END;
/

それでは実際に実行してみましょう！

SQL> DECLARE
  2      CURSOR animal_cur(v_address VARCHAR2) IS
  3          SELECT id, name FROM animal1 WHERE address = v_address;
  4      v_tmp VARCHAR2(20);
  5  BEGIN
  6      v_tmp := &a;
  7      FOR animal_rec IN animal_cur(v_tmp) LOOP
  8          DBMS_OUTPUT.PUT_LINE( 'id:' || animal_rec.id );
  9          DBMS_OUTPUT.PUT_LINE( 'name:' || animal_rec.name );
 10      END LOOP;
 11  END;
 12  /
aに値を入力してください: '空'
旧   6:     v_tmp := &a;
新   6:     v_tmp := '空';
id:002
name:ぺんぎん
id:003
name:うさぎ
id:004
name:ふくろう

PL/SQLプロシージャが正常に完了しました。

SQL*Plusの置換変数「&」を使ってパラメータ付きカーソルがきちんと動作していることが確認できますね＼(^o^)／