動的SQL

質問

いつもお世話になってます。
ストアドで、動的SQLを行いたいのですが、イマイチ分かりません。
テーブルT1(文字列)から、項目K1(べた書き)の最大値、最小値を抽出し、変数A,Bに格納したいのですが、どうすればいいのでしょうか？？
なお、テーブルT1がDBに存在することはチェック済みです。

よろしくお願いします。

回答No.4

_tomo_ · ベストアンサー

#2 の修正版です

-- ---------------------------------------------
-- 動的SQLの作成
-- ---------------------------------------------
C_SQL := DBMS_SQL.OPEN_CURSOR;
v_SQL := 'SELECT 項目1,項目2,項目3 FROM ' || i_TABLE_NM;

-- ---------------------------------------------
-- 動的SQLの解析
-- ---------------------------------------------
DBMS_SQL.PARSE(C_SQL ,v_SQL , DBMS_SQL.NATIVE);
FOR n_CNT IN 1..i_SU LOOP
t_MOJI(n_CNT) := '';
DBMS_SQL.DEFINE_COLUMN(C_SQL,n_CNT,t_MOJI(n_CNT),100);
END LOOP;

-- ---------------------------------------------
-- 動的SQLの実行
-- ---------------------------------------------
Y := DBMS_SQL.EXECUTE(C_SQL);

-- ------------------------------------------
-- フェッチ処理
-- ------------------------------------------
IF DBMS_SQL.FETCH_ROWS(C_SQL) = 0 THEN
EXIT;
END IF;

-- ------------------------------------------
-- 値の取得
-- ------------------------------------------
DBMS_SQL.COLUMN_VALUE(C_SQL ,1 , x); --xに項目1の値が入る

DBMS_SQL.COLUMN_VALUE(C_SQL ,1 , y); --yに項目2の値が入る

DBMS_SQL.COLUMN_VALUE(C_SQL ,1 , z); --zに項目3の値が入る

-------------------------------------------------
こんな感じ？

回答No.5

miko_zz

#3
全然意味違いでした。。。

なのでもうひとつの方法として書き方の簡単な方を
アドバイスさせていただきます。
--------------------------------------------------
/* テストパッケージ定義 */
CREATE OR REPLACE PACKAGE
TEST
IS
PROCEDURE TEST2(t1 in varchar );
END;
/

/* テストパッケージ本体 */
CREATE OR REPLACE PACKAGE BODY
TEST
IS
PROCEDURE TEST2(
t1 in varchar
)
IS
N_MAX_VALUE INT; -- 最大値
N_MIN_VALUE INT; -- 最小値
S_SQL varchar(256);
BEGIN

-- 実行するSQL文
S_SQL := 'SELECT MAX(K1), MIN(K1) FROM ' || t1 ;

-- SQL実行と結果取得
EXECUTE IMMEDIATE S_SQL INTO N_MAX_VALUE, N_MIN_VALUE ;

-- とりあえず結果をテストテーブルに格納
INSERT INTO TEST_RESULT(
MAX_VALUE,
MIN_VALUE,
CREATE_DT
)
SELECT N_MAX_VALUE,
N_MIN_VALUE,
SYSDATE
FROM DUAL ;
END TEST2;

END;
/

/* テストプロシージャ実行 */
EXEC TEST.TEST2( 't1' ) ;

/* テスト結果の表示 */
SELECT *
FROM TEST_RESULT ;
--------------------------------------------------

ただSQL解析時のオーバーヘッドが大きいとは聞いてますが、
多用しなければ問題ないと思われます。

回答No.3

miko_zz

始めまして。
miko_zzと申します。

ストアド内で値を取得して使用するのであれば、
下記のテストで確認することは出来ます。
--------------------------------------------------
/* テスト結果テーブル */
CREATE TABLE TEST_RESULT
MAX_VALUE INT,
MIN_VALUE INT,
CREATE_DT DATE ) ;

/* テストパッケージ定義 */
CREATE OR REPLACE PACKAGE
TEST
IS
PROCEDURE TEST;
END;
/

/* テストパッケージ本体 */
CREATE OR REPLACE PACKAGE BODY
TEST
IS
PROCEDURE TEST
IS
N_MAX_VALUE INT; -- 最大値
N_MIN_VALUE INT; -- 最小値
BEGIN
SELECT MAX(K1),MIN(K1)
INTO N_MAX_VALUE,N_MIN_VALUE
FROM T1;

INSER INTO TEST_RESULT(
MAX_VALUE,
MIN_VALUE,
CREATE_DT
)
SELECT N_MAX_VALUE,
N_MIN_VALUE,
SYSDATE
FROM DUAL ;
END TEST;
END;
/

/* テストプロシージャ実行 */
EXEC TEST.TEST ;

/* テスト結果の表示 */
SELECT *
FROM TEST_RESULT ;
--------------------------------------------------

違ってたらごめんなさい。

回答No.2

_tomo_

私は以下の方法で行っています

-- ---------------------------------------------
-- 動的SQLの作成
-- ---------------------------------------------
C_SQL := DBMS_SQL.OPEN_CURSOR;
v_SQL := 'SELECT * FROM ' || i_TABLE_NM;

-- ---------------------------------------------
-- 動的SQLの解析
-- ---------------------------------------------
DBMS_SQL.PARSE(C_SQL ,v_SQL , DBMS_SQL.NATIVE);
FOR n_CNT IN 1..i_SU LOOP
t_MOJI(n_CNT) := '';
DBMS_SQL.DEFINE_COLUMN(C_SQL,n_CNT,t_MOJI(n_CNT),100);
END LOOP;

-- ---------------------------------------------
-- 動的SQLの実行
-- ---------------------------------------------
Y := DBMS_SQL.EXECUTE(C_SQL);

-- ------------------------------------------
-- フェッチ処理
-- ------------------------------------------
　IF DBMS_SQL.FETCH_ROWS(C_SQL) = 0 THEN
EXIT;
END IF;

--------------------------------------------
だいたい、こんな感じです
参考になります？

お礼

なんとなくは分かるんですが、この場合select文で取得したデータはどこに格納されてれるんですか？？

回答No.1

taka451213

こんにちは。

やり方はいろいろ（大きく２通り）ありますが、どれがいいですか？

１．IMMIDEATEを使用
２．DBMS_SQLパッケージを使用

それぞれ特徴（後の処理にも）があります。
(^^ゞ

お礼

さっそくのお答え有難うございます。
ですが・・・それぞれの特徴がわからないのでなんとも・・・
スミマセン！！