Oracle DatabaseのSYS_REFCURSORとは？動的カーソルの使い方・OPEN FOR・FETCH・注意点を解説

Oracle

Oracle DatabaseのPL/SQLでは、問い合わせ結果をカーソルとして扱い、呼び出し元へ返したり、実行時に決まるSQLを処理したりできます。その際によく使われるのがSYS_REFCURSORです。

SYS_REFCURSORはOracleがあらかじめ用意している弱い型付けのREF CURSOR型で、固定された1種類のSELECT文だけでなく、異なる問い合わせ結果を参照できます。そのため、ストアドプロシージャから検索結果を返す処理や、条件によってSQLが変化する処理などで利用できます。

ただし、「SYS_REFCURSORを使うこと」と「動的SQLを使うこと」は同じ意味ではありません。固定SQLをSYS_REFCURSORで開くこともできますし、文字列として組み立てた動的SQLをOPEN … FORで開くこともできます。この記事では、この違いを含めて具体的なPL/SQLコードで整理します。

SYS_REFCURSORとは何か

REF CURSORは、SQLの問い合わせ結果を指すカーソル変数です。通常の明示カーソルではSELECT文を宣言時に定義しますが、カーソル変数では実行時に問い合わせへ関連付けられます。

SYS_REFCURSORはOracleによって定義済みのREF CURSOR型です。戻り値となる行型を特定のレコード型へ固定しない弱い型付けのカーソル変数なので、さまざまなSELECT文に利用できます。

例えば、ある処理では社員一覧を取得し、別の処理では部署一覧を取得するといった場合でも、それぞれSYS_REFCURSORを使用できます。検索結果を呼び出し元へ受け渡したいストアドプロシージャでも便利です。

SYS_REFCURSORは動的カーソルとして使用できる

SYS_REFCURSORのカーソル変数は、OPEN FOR文を使って問い合わせへ関連付けます。SQL文があらかじめ決まっている場合は、次のように記述できます。

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT employee_id, last_name
      FROM employees
     WHERE department_id = 50;

  CLOSE l_cursor;
END;
/

この例ではSYS_REFCURSORを使用していますが、SELECT文そのものはソースコード内に固定されているため、SQL文という意味では静的SQLです。

一方、実行時にSQL文字列を決定してOPEN FORへ渡せば、動的SQLの結果をSYS_REFCURSORで扱えます。この方法はOracle PL/SQLのNative Dynamic SQLで利用される方法の一つです。

SYS_REFCURSORで動的SQLを実行する基本例

例えば検索対象となる部署番号を変数として受け取り、動的なSELECT文をカーソルとして開く場合は次のように記述できます。

DECLARE
  l_cursor SYS_REFCURSOR;
  l_sql    VARCHAR2(1000);
  l_emp_id employees.employee_id%TYPE;
  l_name   employees.last_name%TYPE;
  l_dept   NUMBER := 50;
BEGIN
  l_sql := 'SELECT employee_id, last_name FROM employees WHERE department_id = :dept';

  OPEN l_cursor FOR l_sql USING l_dept;

  LOOP
    FETCH l_cursor INTO l_emp_id, l_name;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_emp_id || ' ' || l_name);
  END LOOP;

  CLOSE l_cursor;
END;
/

ポイントは、SQL文字列をOPEN FORに渡し、値についてはUSING句のバインド変数で渡していることです。これによって実行時に決定したSQLをSYS_REFCURSORへ関連付け、その後FETCHで1行ずつ取得できます。

なお、サンプルのemployees表はOracle DatabaseのHRサンプルスキーマを想定しています。実際には使用している表名・列名へ置き換えてください。

OPEN・FETCH・CLOSEの流れを理解する

SYS_REFCURSORをPL/SQL内で処理する場合は、基本的にOPEN → FETCH → CLOSEという流れを理解すると分かりやすくなります。

OPEN FORでカーソル変数を問い合わせへ関連付け、FETCHで結果セットからデータを取得します。FETCHを繰り返す場合は%NOTFOUNDなどを使って取得終了を判定し、処理が終わったらCLOSEでカーソルを閉じます。

例えば2列をSELECTしている場合、FETCH先にも対応する2つの変数が必要です。SELECTリストとFETCH先の個数やデータ型が適切に対応するように設計することが重要です。

ストアドプロシージャからSYS_REFCURSORを返す方法

SYS_REFCURSORの代表的な用途が、ストアドプロシージャのOUTパラメータとして検索結果を呼び出し元へ返す方法です。

例えば部署番号を受け取り、その部署に所属する社員を返すプロシージャは次のように作成できます。

CREATE OR REPLACE PROCEDURE get_employees (
  p_department_id IN NUMBER,
  p_result        OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_result FOR
    SELECT employee_id, last_name
      FROM employees
     WHERE department_id = p_department_id;
END;
/

このケースではSYS_REFCURSORを返していますが、問い合わせ自体は静的です。検索条件の値が変わるだけなら、必ずしもSQL文を文字列として組み立てる必要はありません。

条件によってSELECT文そのものを変更する例

SYS_REFCURSORと動的SQLが特に役立つのは、実行時の条件によってSQL文の構造自体が変わる場合です。

例えば検索画面で、指定された条件に応じてWHERE句を追加するようなケースがあります。

CREATE OR REPLACE PROCEDURE search_employees (
  p_department_id IN NUMBER,
  p_result        OUT SYS_REFCURSOR
)
AS
  l_sql VARCHAR2(4000);
BEGIN
  l_sql := 'SELECT employee_id, last_name FROM employees';

  IF p_department_id IS NOT NULL THEN
    l_sql := l_sql || ' WHERE department_id = :dept';
    OPEN p_result FOR l_sql USING p_department_id;
  ELSE
    OPEN p_result FOR l_sql;
  END IF;
END;
/

この例では部署番号が指定された場合だけWHERE句を追加しています。このように、実行時にSQL文の構造が変化する場面では動的SQLを利用する意味があります。

値を文字列連結せずバインド変数を使う

動的SQLを作成するときに注意したいのが、ユーザー入力などの値をSQL文字列へ直接連結する方法です。

例えば次のように値を直接SQLへ組み込む設計は避けるのが基本です。

l_sql := 'SELECT * FROM employees WHERE last_name = ''' || p_name || '''';

入力内容を直接SQLへ連結すると、SQLインジェクションにつながる可能性があります。また、引用符などを適切に処理する必要も生じ、コードが壊れやすくなります。

値を渡す場合は、可能な限り次のようにプレースホルダーとUSING句を使用します。

l_sql := 'SELECT employee_id, last_name FROM employees WHERE last_name = :name';
OPEN p_result FOR l_sql USING p_name;

動的にしたいのが「値」だけならバインド変数を使い、SQL文字列へ値を直接連結しないと覚えておくと安全です。

テーブル名や列名はバインド変数にできない

バインド変数は検索値などを渡すためのものであり、一般にテーブル名や列名といったSQLの識別子そのものを置き換える用途には使用できません。

例えば「SELECT * FROM :table_name」のようにテーブル名をUSING句で指定することはできません。テーブル名を動的に変更する必要がある場合はSQL文字列の一部として構築する必要があります。

ただし、外部入力をそのままテーブル名やORDER BYの列名として連結するのは危険です。許可されたテーブル名・列名のリストと照合するなど、値のバインドとは別の検証が必要になります。

SYS_REFCURSORと通常のカーソルの違い

通常の明示カーソルとSYS_REFCURSORは、どちらも問い合わせ結果を処理できますが、使い方が異なります。

項目 明示カーソル SYS_REFCURSOR
SELECT文 基本的に宣言時に定義 OPEN FOR時に関連付け
問い合わせの柔軟性 比較的固定的 異なる問い合わせに利用可能
動的SQL 用途が異なる OPEN FORで扱える
結果セットの受け渡し 通常はPL/SQL内部で処理 OUT引数などで渡しやすい
型 SELECT内容が明確 SYS_REFCURSORは弱い型付け

SQLが完全に固定されていてPL/SQL内部だけで処理するのであれば、明示カーソルのほうが意図を読み取りやすいことがあります。一方、検索結果を別のプログラムへ返したい場合や、実行時に異なる問い合わせを開きたい場合にはREF CURSORが適しています。

SYS_REFCURSORと「動的SQL」は区別して考える

混同しやすいポイントですが、SYS_REFCURSOR自体が「SQL文を自動的に動的にする機能」ではありません。SYS_REFCURSORはカーソル変数の型です。

例えばOPEN rc FOR SELECT …のように固定されたSELECTを開けば静的SQLです。一方、OPEN rc FOR sql_string USING …のように実行時に作ったSQL文字列を渡せば動的SQLになります。

つまり「SYS_REFCURSORを使用しているか」と「動的SQLを使用しているか」は別々の観点です。この違いを理解しておくと、Oracleのカーソル関連のコードを読みやすくなります。

EXECUTE IMMEDIATEとの違い

PL/SQLの動的SQLではEXECUTE IMMEDIATEもよく使われます。INSERT、UPDATE、DELETE、DDLなどを動的に実行するときや、SELECT結果が単一行でINTOへ取得できるケースなどで利用できます。

一方、SELECTの複数行の結果をカーソルとして順番にFETCHしたい場合や、結果セットそのものを呼び出し側へ渡したい場合にはOPEN FORとREF CURSORが適しています。

「動的SQLならすべてSYS_REFCURSOR」というわけではなく、処理したいSQLと結果の受け取り方に応じてEXECUTE IMMEDIATEやOPEN FORなどを選択します。

SYS_REFCURSORを使う際の注意点

SYS_REFCURSORは柔軟ですが、必要以上にすべてのSQLを動的にする必要はありません。SQLが固定できるなら静的SQLのほうがコードを理解しやすく、コンパイル時に問題を発見しやすいという利点があります。

また、カーソルをPL/SQL内で開いて処理する場合は、不要になったカーソルを適切に閉じることも重要です。例外が発生する可能性がある処理では、リソース管理まで含めて設計します。

さらに、SYS_REFCURSORは弱い型付けで柔軟な一方、呼び出し側は「どの列がどの順序・型で返ってくるのか」を把握する必要があります。プロシージャをAPIのように利用する場合は、返却する列の仕様を安易に変更しないことも重要です。

まとめ：SYS_REFCURSORで動的な問い合わせ結果を扱える

Oracle DatabaseのPL/SQLでは、SYS_REFCURSORをカーソル変数として使用し、OPEN FORによって問い合わせ結果を扱うことができます。さらにSQL文字列をOPEN FORへ渡すことで、Native Dynamic SQLによる動的なSELECTの結果も扱えます。

ただし、SYS_REFCURSORを使用しただけでSQLが動的になるわけではありません。固定SELECTをOPEN FORで開く使い方と、SQL文字列を実行時に構築して開く使い方を区別することが重要です。

実務では、SQLを固定できる場合はできるだけ静的SQLを利用し、本当にSQL構造を実行時に変更する必要がある場合に動的SQLを選ぶと保守しやすくなります。動的SQLへ値を渡す際は、文字列連結ではなくバインド変数とUSING句を利用することも重要なポイントです。

コメント

タイトルとURLをコピーしました