Oracle PL/SQL動的SQLチュートリアル: 即時実行とDBMS_SQL

替え： フィオナ・ブラウン フィオナ・ブラウン
Hours 更新中

動的SQLとは何ですか?

ダイナミック SQL は、実行時にステートメントを生成して実行するためのプログラミング手法です。 これは主に、要件に基づいて実行時に SQL ステートメントが作成および実行される汎用かつ柔軟なプログラムを作成するために使用されます。

動的SQLを記述する方法

PL/SQL には、動的 SQL を作成する XNUMX つの方法があります。

  1. NDS – ネイティブ動的 SQL
  2. DBMS_SQL

NDS (ネイティブ動的 SQL) – 即時実行

ネイティブ動的 SQL は、動的 SQL を記述するより簡単な方法です。実行時に SQL を作成して実行するために、「EXECUTE IMMEDIATE」コマンドを使用します。ただし、この方法を使用するには、実行時に使用されるデータ型と変数の数を事前に知っておく必要があります。また、DBMS_SQL と比較すると、パフォーマンスが向上し、複雑さが軽減されます。

構文 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • 上記の構文は、EXECUTE IMMEDIATE コマンドを示しています。
  • INTO 句はオプションであり、動的 SQL に値をフェッチする選択ステートメントが含まれる場合にのみ使用されます。 変数の型は、select ステートメントの変数の型と一致する必要があります。
  • USING 句はオプションであり、動的 SQL にバインド変数が含まれる場合にのみ使用されます。

例: この例では、NDS ステートメントを使用して、emp_no '1001' の emp テーブルからデータをフェッチします。

NDS - 即時実行

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager
USING 1001;
Dbms_output.put_line('Employee Name:‘||lv_emp_name);
Dbms_output.put_line('Employee Number:‘||ln_emp_no);
Dbms_output.put_line(‘Salary:'||ln_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

出力

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

コードの説明：

  • コード行 2 ～ 6: 変数の宣言。
  • コード行 8: 実行時に SQL をフレーム化します。 SQL の where 条件 ':empno' にバインド変数が含まれています。
  • コード行 9: NDS コマンド「EXECUTE IMMEDIATE」を使用して、フレーム化された SQL テキストを実行します (コード行 8 で実行されます)。
  • 「INTO」句の変数 (lv_emp_name、ln_emp_no、ln_salary、ln_manager) は、SQL クエリからフェッチされた値 (emp_name、emp_no、salary、manager) を保持するために使用されます。
  • 「USING」句は、SQL クエリのバインド変数 (:emp_no) に値を与えます。
  • コード行 10 ～ 13: 取得した値を表示します。

動的SQLのDBMS_SQL

PL/SQL は、動的 SQL を操作できる DBMS_SQL パッケージを提供します。動的 SQL の作成と実行のプロセスには、次のプロセスが含まれます。

  • カーソルを開く: 動的 SQL は、動的 SQL と同じ方法で実行されます。 カーソル。 したがって、SQL ステートメントを実行するには、カーソルを開く必要があります。
  • SQLを解析する: 次のステップは、動的 SQL を解析することです。 このプロセスは構文をチェックするだけで、クエリを実行できる状態に保ちます。
  • バインド変数の値: 次のステップでは、バインド変数がある場合は、その値を割り当てます。
  • 列の定義: 次のステップでは、select ステートメント内の相対位置を使用して列を定義します。
  • 実行: 次のステップは、解析されたクエリを実行することです。
  • 値のフェッチ: 次のステップは、実行された値をフェッチすることです。
  • カーソルを閉じる: 結果がフェッチされたら、カーソルを閉じる必要があります。

例: この例では、DBMS_SQL ステートメントを使用して、emp_no '1001' の emp テーブルからデータをフェッチします。

動的SQLのDBMS_SQL

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50);
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
ln_cursor_id NUMBER;
ln_rows_processed;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager);

ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);

動的SQLのDBMS_SQL

LOOP
IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0
THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); 
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||ln_emp_no); 
Dbms_output.put_line(‘Salary:‘||ln_salary); 
Dbms_output.put_line('Manager ID :‘| ln_manager);
END IF;
END LOOP;

DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);

END:
/

出力

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

コードの説明：

  • コード行 1 ～ 9: 変数宣言。
  • コード行 10: SQL ステートメントのフレームワーク。
  • コード行 11: DBMS_SQL.OPEN_CURSOR を使用してカーソルをオープンします。 開かれているカーソル ID を返します。
  • コード行 12: カーソルがオープンされた後、SQL が解析されます。
  • コード行 13: バインド変数 '1001' は、カーソル ID ':empno' の代わりに割り当てられています。
  • コード行 14 ～ 17: SQL ステートメント内の相対位置に基づいて列名を定義します。 この場合、相対的な位置は (1) emp_name、(2) emp_no、(3) 給与、(4) マネージャーです。 したがって、この位置に基づいてターゲット変数を定義します。
  • コード行 18: DBMS_SQL.EXECUTE を使用してクエリを実行します。 処理されたレコードの数を返します。
  • コード行 19 ～ 33: ループを使用してレコードを取得し、それを表示します。
  • コード行 20: DBMS_SQL.FETCH_ROWS は、処理された行から 0 つのレコードをフェッチします。 すべての行をフェッチするために繰り返し呼び出すことができます。 行をフェッチできない場合は XNUMX を返し、ループを終了します。

製品概要

このセクションでは、動的 SQL と DYNAMIC SQL を実行する方法について説明しました。 また、両方の方法で動的 SQL を実行する際のさまざまな手順も確認しました。 また、同じシナリオが NDS と DBMS_SQL の両方の方法で処理され、実行時に実行される例も見てきました。