新生活を充実させるための「こだわり」を取材!!

PL/SQLのバインド変数について調べていますが理解できず困っています。

[理解内容]
1.利点
 :SQL文の検索にバインド変数を利用すると、同一のSQLとして実行できる
  ⇒ 繰り返し処理(ループ)では有効

2.使用方法
 :静的SQL・動的SQLで使用可能 

 [疑問点]
  静的SQLではバインド変数をどう宣言すればよいですか?
  [SQL*Plus]・[動的SQL]は例文がありました

  ●SQL*Plusでの宣言方法

   (例)
    SQL> set null '<null>'
    SQL> set head off
    SQL> variable var_at_sqlplus number

  ●動的SQLの記述方法

   (例)
    declare
     v_sql varchar2(256);
     v_id varchar2(3);

    begin
     v_sql = 'select id into :v_id from test';
     execute immediate v_sql into v_id;
    end;

  ●静的SQL ・・・【 エラーになる 】

    declare
     variable v_id varchar2(3);

    begin
     select id into :v_id from test'
    end;


どんな事でもかまいませんので回答よろしくお願いします

教えて!goo グレード

A 回答 (2件)

#1 です。



>という事はPL/SQL内の静的SQLではバインド変数は使えないと言うことでしょうか?

ストアドプログラム(パッケージ/プロシージャ/ファンクション)内では動的以外には使えないと思います。
(SQL*PlusからストアドをEXECUTEする時の引数として使用する事は、もちろん可能ですが)

但し、私が例に書いた通り、無名PL/SQLブロック(DECLARE~BEGIN~END)内では静的にも使用可能です。

質問者様の試したサンプルで言えば。

CREATE OR REPLACE PROCEDURE TEST.TEST(v_Id IN VARCHAR2) IS

v_name varchar2(20);

BEGIN
SELECT name
INTO v_name
FROM test
WHERE id = v_Id;

EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
/

これでコンパイルは通りますよね。(すいません、環境が無いので未検証です)

「これじゃ、v_Idはバインド変数にならないんじゃ?」と思うかもしれませんが、これバインド変数なんです。
(うーん、混乱しちゃいますかね?)

SQLトレースを取るとわかるんですが、引数v_Idが '1' だろうが '2' だろうがトレースで解析されるSQLでは

WHERE id = :b1

と言うバインド変数になるんですよ。

>このあたりが理解できていない部分です。

パフォーマンスチューニングで扱われるバインド変数と置換変数(リテラル)の差異を把握する事が、実はこの辺の理屈を理解する上での近道かもしれません。
OTNでパフォーマンス・チューニング・ガイドを落とすか、もしくはパフォチュー系の本を一冊読んでみるとよいかと思います。

もしJavaの経験がおありでしたら、"?"(プレースホルダ)を使ったSQLと、リテラル直指定の同じSQLをそれぞれ実行するJDBCプログラムを作って、SQLトレース(とtkprof)を取って較べてみると見えてくる物があると思います。
私自身、SQLトレースの中身を見るようになって、この辺の事が理解できるようになりましたから。
(SQLトレースに関しては前述のチューニング・ガイド等参照)
    • good
    • 0
この回答へのお礼

ありがとうございました。
今後理解を深めていく手がかりになりました。

お礼日時:2013/01/04 02:38

variable はSQL*Plusのコマンドなのでdeclareの後(無名PL/SQLブロック内)に記述する事はできません。


ブロックの外で記述してください。

他に変数が無いならdeclareも不要です。BEGIN~ENDだけでOKです。

質問者様の例だとシンタックスエラーになりますが、記載ミスと思われるので修正しました。
(testの後のクォートは余分、セミコロンが無い)

variable v_id varchar2(3);

begin
select id into :v_id from test;
end;
/
    • good
    • 0
この回答へのお礼

回答・訂正ありがとうございます。
という事はPL/SQL内の静的SQLではバインド変数は使えないと言うことでしょうか?

以下が今回試してみた全文です。
----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TEST.TEST(v_Id IN VARCHAR2) IS

v_name varchar2(20);

BEGIN
  SELECT name
  INTO v_name
  FROM test
  WHERE id = :v_Id;

EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
----------------------------------------------------------------

この場合以下のエラーになりました。
>PLS-00049:bad bind variable 'V_ID'

このあたりが理解できていない部分です。
バインド変数を使用するにあたり、基本的な事かもしれませんがよろしくお願いします

お礼日時:2013/01/03 01:14

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

関連するカテゴリからQ&Aを探す


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング