PL/SQLの動的SQLで複数の項目を取得する方法
- PL/SQLで複数の項目を取得する方法について教えてください。
- 現在、動的SQLを用いて一つの項目しか取得することができません。他の方法でもよいので、複数の項目を取得する方法を教えてください。
- DBMS_SQLパッケージを利用する方法がおすすめです。具体的には、DBMS_SQL.COLUMN_VALUEを使用して複数の項目を取得することができます。
- ベストアンサー
PL/SQLの動的SQLで複数の項目を取得する方法教えて下さい。
動的SQLで複数の項目を取得する方法で 悩んでいます。 下記の例だと、1項目しか取れません。 何か他のやり方でもいいので、複数の項目を 取得する方法を教えていただきたいです。 ********************************************* declare sql_stmt varchar2(200); wk_grp varchar2(5); wk_name varchar2(30); type cursor_type is ref cursor; cur_name cursor_type; begin wk_grp := '1u'; open cur_name for 'select 名前 from 社員表 where 所属グループ = :v1' using wk_grp; loop fetch cur_name into wk_name; exit when cur_name%notfound; dbms_output.put_line(wk_name); end loop; close cur_name; end; ********************************************* 下記のようなDBMS_SQLパッケージ?? を利用した方法がいいんでしょうかね。。。。 DBMS_SQL.COLUMN_VALUE(SQL,1,WK_NAME) DBMS_SQL.COLUMN_VALUE(SQL,2,WK_NO) よくわかってないので、宜しくお願いします。
- kei161220
- お礼率75% (9/12)
- その他(プログラミング・開発)
- 回答数1
- ありがとう数2
- みんなの回答 (1)
- 専門家の回答
質問者が選んだベストアンサー
複数項目の受け取りは、 declare sql_stmt varchar2(200); wk_name varchar2(30); wk_addr varchar2(50); type cursor_type ~; cur_name cursor_type; begin open cur_name for 'select 名前,住所 from 社員表'; loop fetch cur_name into wk_name,wk_addr; dbms_output.put_line('NAME='||wk_name); dbms_output.put_line('ADRS='||wk_adrs); end loop; close cur_name; end; のように、複数の項目を記述することで、何の問題もありません。 ただし、今回の例のような条件式で、比較条件値を可変にするようなケースでは、 動的SQLを使う意味はありません。静的SQLで実現可能です。 動的SQLを必要とするのは、オラクルの静的オブジェクト(テーブル,ビュー,項目)を 可変要素とする場合と考えてください。 (例えば、自分が所有する全ての表のレコード件数を調べる等) 静的SQLで書く場合の例: declare wk_name varchar2(30); wk_addr varchar2(50); cursor cur_name (wk_grp) is select 名前 from 社員表 where 所属グループ=wk_grp; begin open cur_name('1U'); loop fetch cur_name into wk_name; dbms_output.put_line('NAME='||wk_name); end loop; close cur_name; end; >下記のようなDBMS_SQLパッケージ??を利用した方法が・・ DBMS_SQLパッケージは、動的SQLを投げる場合に、 ・execute imediate ~ ・動的なカーソル で困る場合に使うと良いと思います。 例えば、投げるSQLが動的に決定され、受け取る項目の数やデータ属性が 不明な時に有用です。 動的なカーソルのFETCH文では、受け取る項目の数やデータ属性が、 静的になりますので、あらかじめ決まった要素しか受け取れません。 とても、汎用的な使い方の出来るDBMS_SQLパッケージですが、 取扱が面倒なので、避けれるなら避けた方が”吉”だと思います。 ※投稿中のPL/SQLロジックは、エラー処理を端折っています。(ご注意ください)
関連するQ&A
- 動的なSQLからカーソルを返す。
下記のようなカーソルを返すパッケージがあったとします。 CREATE OR REPLACE PACKAGE TEST AS CURSOR C1 IS SELECT * FROM テーブル; TYPE TC1 IS REF CURSOR RETURN C1%ROWTYPE; PROCEDURE P_TEST(in条件 IN CHAR, CU OUT TC1); END; CREATE OR REPLACE PACKAGE BODY TEST IS PROCEDURE P_TEST(in条件 IN CHAR, CU OUT TC1) AS sqlStr VARCHAR2(1000); cur NUMBER; r NUMBER; BEGIN cur := DBMS_SQL.OPEN_CURSOR; sqlStr := 'OPEN CU FOR SELECT * FROM テーブル WHERE 条件 = ' || in条件; DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(cur, 1, CU); r := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END; SELECTした結果のカーソル(CU)をクライアントで取得したいのですけど 技術的に可能でしょうか? ストアドのコンパイル時に→DBMS_SQL.DEFINE_COLUMN(cur, 1, CU);が エラーとなります。 DBMS_SQL.DEFINE_COLUMNではカーソルタイプは取得できないとわっかたの ですがカーソルを取得する為のDBMS_SQL.DEFINE_COLUMNにかわる DBMS_SQL.?????はないでしょうか? いろいろ調べたのですが手に負えず。 どなたか教えてください。
- ベストアンサー
- その他(データベース)
- VBからストアドの動的SQLを呼んで値をレコードセットで取得する方法
お世話になります。 VBからストアドの動的SQLを呼んで、VB側で値をレコードセットで取得する方法を探しています。 ■ストアド CREATE OR REPLACE PACKAGE SAMPLE as CURSOR c2 IS select A from TBL; TYPE TANTOYOSANDATA IS REF CURSOR RETURN c2%ROWTYPE; PROCEDURE GetNUMBER(C IN NUMBER, CU OUT TANTOYOSANDATA); end; / -- CREATE OR REPLACE PACKAGE BODY SAMPLE IS PROCEDURE GetNUMBER(C IN NUMBER, CU OUT TANTOYOSANDATA) IS CUR INTEGER; STATUS INTEGER; ROW_PROCESSED INTEGER; A NUMBER; BEGIN --カーソルをOPENする CUR :=DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(CUR,'SELECT A FROM TBL ',DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN(CUR,1,A); ROW_PROCESSED :=DBMS_SQL.EXECUTE(CUR); STATUS :=DBMS_SQL.FETCH_ROWS(CUR); WHILE (STATUS <> 0) LOOP DBMS_SQL.COLUMN_VALUE(CUR,1,A); STATUS :=DBMS_SQL.FETCH_ROWS(CUR); END LOOP; END; END SAMPLE; / ■VB側 Set GoORASESSION = CreateObject("OracleInProcServer.XOraSession") Set GoORADATABASE = GoORASESSION.OpenDatabase(dbname, cnuser, ORADB_DEFAULT) Set Recordset = GoORADATABASE.CreatePLSQLDynaset("Begin SAMPLE.GetTANTOINFO(1,:CU)); end;", "CU", ORADYN_DEFAULT) ' If Recordset.EOF Then Do While Not Recordset.EOF Label11.Caption = Recordset(0) Recordset.movenext Loop 復帰値をRecordSetで受けて それをまわしながら1レコードずつ取得する方法を探しています よろしくお願いします。
- 締切済み
- Visual Basic
- PL/SQLでの処理について
キーの異なる2つのトランザクションテーブルA,Bに マスタCの値を引っ張ってきて各A,B埋める処理を、テーブル名を引数にPL/SQLのプロシージャにしたいのですが、良い方法はありますか? 以下のイメージです。 <TABLE A> KEY_A C_CD C_VALUE -----+----+------- 1 3 2 3 3 1 <TABLE B> KEY_B C_CD C_VALUE -----+----+------- 1 3 2 3 3 1 <TABLE C> C_CD C_VALUE ----+-------- 1 A 2 B 3 C SQL>EXECUTE foo('A'); <TABLE A> KEY_A C_CD C_VALUE -----+----+------- 1 3 C 2 3 C 3 1 A SQL>EXECUTE foo('B'); <TABLE B> KEY_B C_CD C_VALUE -----+----+------- 1 3 C 2 3 C 3 1 A <処理イメージ> CREATE OR REPLACE PROCEDURE FOO(TNAME VARCHA2) IS c_cd NUMBER; C1 INTEGER; SQL1 VARCHAR2(2000); I INTEGER; BEGIN SQL1 := 'SELECT c_cd FROM ' || tname; C1 := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(C1, SQL1, DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN(C1, 1, c_cd); LOOP IF DBMS_SQL.FETCH_ROWS(C1) > 0 THEN DBMS_SQL.COLUMN_VALUE(C1, 1, c_cd); UPDATE tname set value = c_cd; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ↑ここの部分をうまくかけないでしょうか? ELSE EXIT; END IF; END LOOP; end /
- ベストアンサー
- その他(データベース)
- 変数が選択リストにありません
変数が選択リストにありません 下記のコードを実行すると「変数が選択リストにありません」というエラーが出てしまいます。 SELECT文とFETCHのINTOの変数の個数が一致していない場合にこのエラーは出るということらしいのですが、問題ないように思うのです。 下記コードで何が問題になっているのか教えてください。 よろしくお願いします。 set serveroutput on; declare -- 各テーブルの全カラムを取得 cursor curcol (p_tblname user_tables.table_name%type) is select COLUMN_NAME,DATA_TYPE from user_tab_columns where table_name = p_tblname ; v_sql varchar2(1000); -- SQL文 v_col varchar2(1000); -- カラム名 v_val varchar2(1000); -- 取得データ v_tblname varchar2(20); -- テーブル名 v_RstExec integer; v_CursorID integer; begin v_col := ''; v_tblname := 'sample'; -- 取得テーブルのカラムを取得 for rowcol in curcol(v_tblname) loop -- 取得カラムをCSV形式に変換 if v_col is null then v_col := rowcol.COLUMN_NAME; else v_col := v_col||'||'',''||'||rowcol.COLUMN_NAME; end if; end loop; dbms_output.put_line(v_col); -- SQL作成 v_sql := 'select '||v_col||' from '||v_tblname; v_CursorID := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_CursorID, v_sql, DBMS_SQL.NATIVE); v_RstExec := DBMS_SQL.EXECUTE(v_CursorID); LOOP IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_val); DBMS_OUTPUT.PUT_LINE(v_val); END LOOP; DBMS_SQL.CLOSE_CURSOR(v_CursorID); end; /
- ベストアンサー
- Oracle
- PL/SQLにてカーソル名を変数に
PL/SQLにて、カーソル名を変数にしたいのですが、どうすればよいのでしょうか? 具体的には以下のとおりです。 DECLARE CURSOR Cur1 IS SELECT K1,K2 FROM EMP; BEGIN FOR Cur_Rec1 IN Cur1 Cur_Rec1.K1 DBMS_OUTPUT.PUT_LINE(Cur_Rec1.K1); ★ END LOOP; END; ★の部分の「Cur_Rec1.K1」を「Cur_Rec1.K2」として、置き換えて使用したいのです。(2回書きたくない) 初心者で、申し訳ないです。どうか、ご教授ください。
- ベストアンサー
- Oracle
- PL/SQLでテーブル名に変数を使いたい
下記のようにV_TABLE_NAMEに不定のテーブル名で使用したい ご存知の方は教えて頂けないでしょうか?V_TABLE_NAMEに取得したテーブルをセットし、データの件数を取得するPL/SQL CURSOR C_CUR IS SELECT COUNT (*) FROM V_TABLE_NAME; BEGIN for ・・・・・ loop ・・・ ・・・ V_TABLE_NAME := GET_TABLE; OPEN C_CUR; FETCH C_CUR into w_count; CLOSE C_CUR; end loop;
- ベストアンサー
- その他(データベース)
- PL/SQLのCREATE文でCHAR型で項目ができない
PL/SQLで以下のようにCREATE文を発行しました。 Bテーブルの項目 項目1,'1'を持つテーブルを作成したいのです。 DECLARE KBN CHAR(1); WK_SQL VARCHAR2(4000); BEGIN KBN := '1'; WK_SQL := 'CREATE TABLE A_TBL AS SELECT B.項目1 , ' || KBN || ' AS A項目 FROM B_TBL B ' ; EXECUTE IMMEDIATE WK_SQL; END; 結果はA_TBLの項目、A項目がどうしてもNUMBER型になるのです。初心者で、質問に不備なところもあるかもしれませんが、どうすればよいか、ご教授ください。
- ベストアンサー
- Oracle
- PL/SQLでCSVを作成又は、読み込みたい
下記のエラーが出て、UTL_FILEパッケージが使用できません。なぜでしょう? 下記SQL文の「※1」箇所で「UTL_FILE」の後 「.」を入力しても、「FILE_TYPE」は選択肢には表示されず、やむを得ず手入力しました。 ~~~~~~~~~~~~~~~~~~~~~~~ 問題のSQL文 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ DECLARE DNM VARCHAR2(200) := 'C:\TMP'; FNM VARCHAR2(200) := 'TEST.CSV'; CURSOR CUR1 IS SELECT COL1, COL2, COL3, COL4, COL5 FROM A; FHANDLE UTL_FILE.FILE_TYPE; ←←←←←←←←←← ※1 REC VARCHAR2(2000); BEGIN FHANDLE := UTL_FILE.FOPEN(DNM,FNM,'W',2002); UTL_FILE.PUTF(FHANDLE,'"%S","%S","%S","%S"\N','タイプ','オブジェクト','作成日','更新日'); BEGIN FOR CUR_REC IN CUR1 LOOP UTL_FILE.PUTF(FHANDLE,'"%S","%S","%S","%S"\N','タイプ','オブジェクト','作成日','更新日'); END LOOP; END; UTL_FILE.FCLOSE(FHANDLE); END; ~~~~~~~~~~~~~~ エラーメッセージ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Error [row:1,col:1] ORA-06510: PL/SQL: ユーザー定義の例外が発生しましたが、処理されませんでした ORA-06512: "SYS.UTL_FILE", 行120 ORA-06512: "SYS.UTL_FILE", 行293 ORA-06512: 行14
- ベストアンサー
- その他(プログラミング・開発)
- カーソルフェッチにて、最終レコードが重複します
いつもお世話になります。 WindowsVista Oracle10gの環境にて、以下のようにPL/SQLを作成し、結果を確認しましたが、最終レコードが重複してしまいます。 SQL文が悪いのかと思い、切り出してSQLPlusで確認しましたが正しく返ってきます。 DECLARE type cur_type is ref cursor; souk_cur cur_type; vSql varchar2(2000); BEGIN vSql := ' select ~ from TableA' open souk_cur for vSql; loop fetch souk_cur into p1; dbms_output.putline(p1) exit when souk_cur%notfound; end loop; close souk_cur; END; / <結果> 1 あああ 2 いいい 3 ううう 3 ううう 試しにorder byで並べ替えると、 <結果> 3 ううう 2 いいい 1 あああ 1 あああ と、やはり最終レコードが重複します。 どなたかご存知の方おられましたらご教授願えませんでしょうか? 宜しくお願い致します。
- ベストアンサー
- Oracle
お礼
丁寧なご回答ありがとうございました。 参考書にはDBMS_SQLパッケージのやり方しか 記載がないので、とても役に立ちそうです。 fetch into~のあとに複数の変数項目を セットすれば取得できるってことですね。 DBMS_SQLパッケージとどちらを使っていくほうが いいかは、自分で身につけていきたいと 思います。ありがとうございました。