バインド変数について

質問

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

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

２．使用方法
　：静的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;

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

回答No.2

utakataXEX · ベストアンサー

#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トレースに関しては前述のチューニング・ガイド等参照）

お礼

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

回答No.1

utakataXEX

variable はSQL*Plusのコマンドなのでdeclareの後（無名PL/SQLブロック内）に記述する事はできません。
ブロックの外で記述してください。

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

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

variable v_id varchar2(3);

begin
select id into :v_id from test;
end;
/

お礼

回答・訂正ありがとうございます。
という事は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'

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