EXECUTE IMMEDIATE文を使ってSELECT文を出力する

2024-04-08

PL/SQLブロックからSELECT文を出力する方法

PL/SQLブロックは、Oracleデータベース内で実行されるプログラムです。このブロック内でSELECT文を実行し、その結果を出力することは可能です。

方法

以下の2つの方法があります。

DBMS_OUTPUTパッケージは、PL/SQLブロック内から文字列を出力するための標準的なパッケージです。このパッケージを使う方法は以下の通りです。

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT *
    FROM employees;

  LOOP
    FETCH l_cursor INTO l_emp_id, l_emp_name;
    DBMS_OUTPUT.PUT_LINE(l_emp_id || ', ' || l_emp_name);
  END LOOP;

  CLOSE l_cursor;
END;

DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN('employees.txt', 'w');

  FOR r IN (
    SELECT *
    FROM employees
  ) LOOP
    UTL_FILE.PUT_LINE(l_file, r.emp_id || ', ' || r.emp_name);
  END LOOP;

  UTL_FILE.FCLOSE(l_file);
END;

出力結果

上記の例では、いずれの方法でも、employees表のデータがカンマ区切りで出力されます。

補足

DBMS_OUTPUTパッケージを使う場合は、クライアントツールによって出力結果の表示方法が異なります。
UTL_FILEパッケージを使う場合は、出力ファイルはデータベースサーバー上のファイルシステムに作成されます。

DBMS_OUTPUTパッケージを使う

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT *
    FROM employees;

  LOOP
    FETCH l_cursor INTO l_emp_id, l_emp_name;
    DBMS_OUTPUT.PUT_LINE(l_emp_id || ', ' || l_emp_name);
  END LOOP;

  CLOSE l_cursor;
END;

DECLAREセクションで、l_cursorという名前のカーソル変数を宣言します。
OPEN文で、employees表に対するSELECT文を実行し、その結果をカーソル変数l_cursorに格納します。
LOOP文で、カーソル変数l_cursorからデータを取り出し、1行ずつ出力します。
DBMS_OUTPUT.PUT_LINEプロシージャを使って、データを出力します。
CLOSE文で、カーソル変数l_cursorを閉じます。

UTL_FILEパッケージを使う

DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN('employees.txt', 'w');

  FOR r IN (
    SELECT *
    FROM employees
  ) LOOP
    UTL_FILE.PUT_LINE(l_file, r.emp_id || ', ' || r.emp_name);
  END LOOP;

  UTL_FILE.FCLOSE(l_file);
END;

解説

UTL_FILE.FOPENプロシージャを使って、employees.txtという名前のファイルを開き、ファイルハンドル変数l_fileに格納します。
FORループを使って、SELECT文の結果を1行ずつ処理します。
UTL_FILE.PUT_LINEプロシージャを使って、データを出力ファイルに書き込みます。

実行方法

上記のサンプルコードをOracle SQL Developerなどのツールで実行すると、以下の結果が出力されます。

DBMS_OUTPUTパッケージを使う場合

1, 山田太郎
2, 佐藤花子
3, 田中一郎

UTL_FILEパッケージを使う場合

employees.txt
1, 山田太郎
2, 佐藤花子
3, 田中一郎

PL/SQLブロックからSELECT文を出力する他の方法

EXECUTE IMMEDIATE文は、文字列として渡されたSQL文を実行するものです。この方法を使うと、動的にSELECT文を生成して実行することができます。

DECLARE
  l_sql VARCHAR2(2000);
BEGIN
  l_sql := 'SELECT * FROM employees WHERE department_id = :d_id';

  EXECUTE IMMEDIATE l_sql
    USING IN d_id => 10;
END;

l_sql変数に、SELECT文を代入します。
EXECUTE IMMEDIATE文を使って、l_sql変数に格納されたSQL文を実行します。
USING句を使って、バインド変数d_idに値10を代入します。

DBMS_SQLパッケージは、SQL文を動的に実行するためのパッケージです。このパッケージを使うと、SELECT文だけでなく、INSERT、UPDATE、DELETEなどのSQL文を実行することができます。

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  DBMS_SQL.OPEN_CURSOR(l_cursor, 'SELECT * FROM employees');

  LOOP
    FETCH l_cursor INTO l_emp_id, l_emp_name;
    DBMS_OUTPUT.PUT_LINE(l_emp_id || ', ' || l_emp_name);
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(l_cursor);
END;