人に聞けない痔の悩み、これでスッキリ >>

動的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)

よくわかってないので、宜しくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (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ロジックは、エラー処理を端折っています。(ご注意ください)
    • good
    • 0
この回答へのお礼

丁寧なご回答ありがとうございました。
参考書にはDBMS_SQLパッケージのやり方しか
記載がないので、とても役に立ちそうです。
fetch into~のあとに複数の変数項目を
セットすれば取得できるってことですね。
DBMS_SQLパッケージとどちらを使っていくほうが
いいかは、自分で身につけていきたいと
思います。ありがとうございました。

お礼日時:2005/10/20 09:15

このQ&Aに関連する人気のQ&A

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

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

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QPL/SQL カーソルのFROM句にカーソル

もしご存知の方がいればお教えください。

ORACLE PL/SQLで
CURSOR cur_a AS
SELECT a FROM TBLA
とカーソルを宣言し
そのカーソルを他のカーソルで
使用することは可能でしょうか?
以下のような感じで
CURSOR cur_B AS
SELECT b FROM TBLB,cur_a

ご存知の方が居たら
どのようなSELECT文を
書けばよいかお教えください

よろしくお願いいたします

Aベストアンサー

カーソルにINのパラメータが設定できるので、INで受け取った値をカーソル内のWhere句に指定すれば検索可能です。

昔、こんな感じでやりました。
以下のサンプルはscottユーザで流してみてください。

set serveroutput on size 1000000
DECLARE
CURSOR emp_cur
IS
SELECT
empno,
deptno
FROM emp
;
emp_rec emp_cur%ROWTYPE;

CURSOR dept_cur
(
in_deptno IN NUMBER
)
IS
SELECT
deptno,
dname
FROM dept
WHERE deptno = in_deptno
;
dept_rec dept_cur%ROWTYPE;

BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;

OPEN dept_cur
(
emp_rec.deptno
);

FETCH dept_cur INTO dept_rec;
IF dept_cur%NOTFOUND
THEN
DBMS_OUTPUT.PUT_LINE('dept not found : ' || emp_rec.deptno);
ELSE
DBMS_OUTPUT.PUT_LINE(emp_rec.empno);
DBMS_OUTPUT.PUT_LINE(dept_rec.dname);
END IF;
CLOSE dept_cur;

END LOOP;
CLOSE emp_cur;


EXCEPTION

WHEN OTHERS THEN
DECLARE
ERROR_CODE NUMBER := SQLCODE;
ERROR_MSG VARCHAR2(300) := SQLERRM;
BEGIN
DBMS_OUTPUT.PUT_LINE('*** ORA-ERROR IN THE EXCEPTION ***');
DBMS_OUTPUT.PUT_LINE('ERROR CODE : ' || TO_CHAR(ERROR_CODE));
DBMS_OUTPUT.PUT_LINE('ERROR MSG : ' || ERROR_MSG);
END;

END;
/

カーソルにINのパラメータが設定できるので、INで受け取った値をカーソル内のWhere句に指定すれば検索可能です。

昔、こんな感じでやりました。
以下のサンプルはscottユーザで流してみてください。

set serveroutput on size 1000000
DECLARE
CURSOR emp_cur
IS
SELECT
empno,
deptno
FROM emp
;
emp_rec emp_cur%ROWTYPE;

CURSOR dept_cur
(
in_deptno IN NUMBER
)
IS
SELECT
deptno,
dname
FROM ...続きを読む

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

QSELECT INTOで一度に複数の変数へ代入をするにはどのようにすれがよいでしょうか?

初めてこちらで質問させていただきます。
どうぞよろしくお願いします。
早速ですが、
現在ストアドファンクション(PL/pgSQL)にて以下のような形になっています。
変数d1,d2,d3に値をセットするにあたり
テーブルt1を3回スキャンしています。
これを1回のスキャンでセットするような
方法はないのでしょうか?

DECLARE
d1 integer;
d2 integer;
d3 varchar(10);

begin
select min(c1) into d1 from t1;
select min(c2) into d2 from t1;
select min(c3) into d3 from t1;
各種処理
return v;
end;

気持ち的には以下のようなSQLを書きたいのですが、
syntax error となります。
(NG例)
select min(c1) into d1,min(c2) into d2,min(c3) into d3 from t1;

何か良い案がありましたら教えてください。
「それは出来ません」と言う回答でもありがたいです。
※あきらめがつくので。。

それではよろしくお願いします。

初めてこちらで質問させていただきます。
どうぞよろしくお願いします。
早速ですが、
現在ストアドファンクション(PL/pgSQL)にて以下のような形になっています。
変数d1,d2,d3に値をセットするにあたり
テーブルt1を3回スキャンしています。
これを1回のスキャンでセットするような
方法はないのでしょうか?

DECLARE
d1 integer;
d2 integer;
d3 varchar(10);

begin
select min(c1) into d1 from t1;
select min(c2) into d2 from t1;
select min(c3) into ...続きを読む

Aベストアンサー

select min(c1),min(c2),min(c3) into d1,d2,d3 from t1;
でダメ?

QStatement ignored というエラー

Oracle 9iを使ってsqlファイルを実行させたところ、タイトルにあるように、
PL/SQL: SQL Statement ignored
というエラーメッセージが出力されました。
いろいろ検索してみると、これは、私にシステム権限がないことからおこるとあったのですが、
その設定変更の仕方がいまいちよくわかりませんでした。設定変更のしかたを教えていただけないでしょうか。
また、設定変更したことによって変わるのは私自身のシステム権限だけであって、他にコンピュータ(UNIX)を共有している人たちには影響はありませんよね?
立て続けに質問ばかりすみませんが、よろしくおねがいします。

Aベストアンサー

PL/SQLで権限がないということは、EXECUTE PROCEDUREシステム権限がないということでしょうか・・・。
前後のエラーも記載していただいた方が良いとは思いますが。

システム権限の付与方法は下記で行うことが可能です。
※ここでいう"ユーザ"はOracleユーザのことでスキーマとイコールです。
1.SYSTEMまたはSYS(DBA権限を持ったユーザ)でログイン
2.grant execute procedure to [対象ユーザ];
  または
  grant resource to [対象ユーザ];

以上で一応権限付与はできますが、どのような処理を行いたいかによって、これだけで解決するかどうかわかりません。
また、他の人への影響ですが、ユーザをどのように使用しているか、どのようなポリシーのシステムかによって異なります。(「私自身」というのが、セッションのことかスキーマのことかわかりませんでした。)
同じユーザを他の人と共通で使っていれば、もちろん他の人へ影響が出ますし、一人1ユーザ(スキーマ)で割当てられていれば、影響なく使えます。
「影響が出る」という意味は、セキュリティレベルが権限付与された分落ちるということで、機能的に使用できなくなることはありません。

なお、セッション単位で権限を割当てる方法はないと思っています。
付与した権限をセッション終了次第revokeすれば、元に戻るのではないでしょうか。

PL/SQLで権限がないということは、EXECUTE PROCEDUREシステム権限がないということでしょうか・・・。
前後のエラーも記載していただいた方が良いとは思いますが。

システム権限の付与方法は下記で行うことが可能です。
※ここでいう"ユーザ"はOracleユーザのことでスキーマとイコールです。
1.SYSTEMまたはSYS(DBA権限を持ったユーザ)でログイン
2.grant execute procedure to [対象ユーザ];
  または
  grant resource to [対象ユーザ];

以上で一応権限付与はできますが、どのような処理を...続きを読む

QPL/SQLカーソルの2重FORループができません

こんにちは。
PL/SQLで下記のようなFORループを
2重3重に処理することはできないのでしょうか?

コンパイル時に
PLS-00103: 記号"END"が見つかりました
と、内ループの"END"に対して起こられてしまいます。

お分かりになる方、よろしくお願いします。


<<L_OUT>>
FOR data_a IN csr_a LOOP
 …
<<L_IN>>
FOR data_b IN csr_b LOOP
 END LOOP L_IN;
 …
END LOOP L_OUT;

Aベストアンサー

カーソルのOPEN/CLOSEを明示するなら、問題はないと思います。

しかしながら、csr_a,csr_bを結合した1つのカーソルの
読み出しに変えた方が、より効率的かと思います。

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

Qテーブルの列名をデータとして設定しておき取得する

テーブルが2つあるとします。
テーブルAにはテーブルBの列名がデータとして登録されてます。
テーブルBには値が格納されてます。


テーブルA
Key  field1  field2  field3
------------------------------
1   item2  item3  item5
2   item1  item2  item5
------------------------------


テーブルB
Key  item1  item2  item3  item4  item5
--------------------------------------------
1    101   205    350   420   510
2    111   112    113   114   115
--------------------------------------------


ここでテーブルAとテーブルBへキーを指定して、
テーブルAのfield1~field3に登録されたすべての列名を元にテーブルBから値を取得したいです。

例として
キー1を指定して取得すると
205,350,510 が取れます (item2,item3,item5の列名をテーブルAで指定した為)

キー2を指定して取得すると
111,112,115 が取れます


キーをパラメータとして与え、
できれば2つのテーブルを結合し、1回のSQLで取得する方法はありますでしょうか?
(1回のSQLでなくても簡易であれば良いですが)


よろしくお願い致します。

テーブルが2つあるとします。
テーブルAにはテーブルBの列名がデータとして登録されてます。
テーブルBには値が格納されてます。


テーブルA
Key  field1  field2  field3
------------------------------
1   item2  item3  item5
2   item1  item2  item5
------------------------------


テーブルB
Key  item1  item2  item3  item4  item5
--------------------------------------------
1    101   205    350   420   510
2    111   112   ...続きを読む

Aベストアンサー

え~と・・・時間ができたので(笑)SQL版をば

-- keyの設定(SQL*Plusでの実行を想定)

VARIABLE v_key NUMBER

EXECUTE :v_key := 1

-- なんか…無理矢理だな~、DB設計が悪いんだよな(笑)版

SELECT
MAX(CASE WHEN A.fno = 1 THEN B.fvalue END) AS field1,
MAX(CASE WHEN A.fno = 2 THEN B.fvalue END) AS field2,
MAX(CASE WHEN A.fno = 3 THEN B.fvalue END) AS field3
FROM
(
SELECT 1 AS fno, field1 AS fname FROM テーブルA WHERE key = :v_key
UNION ALL
SELECT 2 AS fno, field2 AS fname FROM テーブルA WHERE key = :v_key
UNION ALL
SELECT 3 AS fno, field3 AS fname FROM テーブルA WHERE key = :v_key
) A,
(
SELECT 'item1' AS fname, item1 AS fvalue FROM テーブルB WHERE key = :v_key
UNION ALL
SELECT 'item2' AS fname, item2 AS fvalue FROM テーブルB WHERE key = :v_key
UNION ALL
SELECT 'item3' AS fname, item3 AS fvalue FROM テーブルB WHERE key = :v_key
UNION ALL
SELECT 'item4' AS fname, item4 AS fvalue FROM テーブルB WHERE key = :v_key
UNION ALL
SELECT 'item5' AS fname, item5 AS fvalue FROM テーブルB WHERE key = :v_key
) B
WHERE A.fname = B.fname
/

「動的SQL」をオススメします。

え~と・・・時間ができたので(笑)SQL版をば

-- keyの設定(SQL*Plusでの実行を想定)

VARIABLE v_key NUMBER

EXECUTE :v_key := 1

-- なんか…無理矢理だな~、DB設計が悪いんだよな(笑)版

SELECT
MAX(CASE WHEN A.fno = 1 THEN B.fvalue END) AS field1,
MAX(CASE WHEN A.fno = 2 THEN B.fvalue END) AS field2,
MAX(CASE WHEN A.fno = 3 THEN B.fvalue END) AS field3
FROM
(
SELECT 1 AS fno, field1 AS fname FROM テーブルA WHERE key = :v_key
UNION ALL
SELECT 2 AS fno, field2 AS fname FROM テ...続きを読む

Qsqlplusで表示が変なので、出力を整形したい。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------

上記のように意味不明な形式で出てきます。

例えばこんな風に

select カラム1,カラム2,カラム3 from hoge;

カラム1 1の値
---------------------------
カラム2 2の値
---------------------------
カラム3 3の値

等のように分かりやすく表示できないでしょうか?

ちなみにOracle9iR2を使用しています。
sqlに関するツールは使用できないルールでして、あくまでsqlplusのコマンド上でみやすくしなければなりません。

分かりづらくですいませんが、皆さま、ご教授お願いします。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
-----------------------...続きを読む

Aベストアンサー

SQLPLUSを起動して、

SQL>set linesize 列数

でどうだ。

SQL>show linesize

で確認ができる。

Qupdate文で改行を入れる

こんにちは。
いつもお世話になっています。

update文を使用して、データに改行を入れたいのですが、どうすればよいでしょうか。

対象列のデータ型はVARCHAR2です。

例えばSQLPLUSから、
SPL>update 「テーブル名」set 「対象列」='あ改行い改行う改行'
を投入し、そのあと
select 「対象列」 from 「テーブル名」
とすると
SQL>あ
SQL>い
SQL>う
と出てくればよいんですが、
SQL>update 「テーブル名」set 「対象列」='あ\nい\nう\n'
としても
SQL>あ\nい\nう\n
と、「\n」が文字列としてでてきてしまいます。

SPL>update 「テーブル名」set 「対象列」='あ
2い
3う'
と投入すると、期待通りになるのですが、
そうではなく一行にまとめたいのですがどうすれば良いでしょうか。

Oracle9iを使用しています。
宜しくお願いいたします。

Aベストアンサー

こんにちわyukio200263さん
以下のSQL文でどうでしょうか?

UPDATE 「テーブル名」
SET 「対象列」= 'あ' || CHR(13) || CHR(10) ||'い'

ちょっと長くなってしまいますが、一行で可能です。

QPL/SQLのコンパイルエラーについて(ignored)

以下はPL/SQLの一部です。

if (vn_CNT > 0 ) then
-- 座席マスタ更新実行
行27update M_SEAT
行28 set M_SEAT.CD_PC = :new.NK_PC ,
行29 M_SEAT.CD_TANTO = :new.CD_TANTO,
行30 where M_SEAT.NO_PORT = :new.NO_PORT ;
end if ;


実行すると以下のエラーがでます。。
行番号 = 27 列番号 = 3 エラー・テキスト = PL/SQL: SQL Statement ignored
行番号 = 30 列番号 = 5 エラー・テキスト = PL/SQL: ORA-01747: user.table.column、table.columnまたは列指定が無効です

ちなみにOEMコンソールで作成しています。
M_SEATというテーブルは存在します。

権限どうのと言われているのはわかるのですが結局なにが原因なのかわかりません。
どなたか教えてください。

以下はPL/SQLの一部です。

if (vn_CNT > 0 ) then
-- 座席マスタ更新実行
行27update M_SEAT
行28 set M_SEAT.CD_PC = :new.NK_PC ,
行29 M_SEAT.CD_TANTO = :new.CD_TANTO,
行30 where M_SEAT.NO_PORT = :new.NO_PORT ;
end if ;


実行すると以下のエラーがでます。。
行番号 = 27 列番号 = 3 エラー・テキスト = PL/SQL: SQL Statement ignored
行番号 = 30 列番号 = 5 エラー・テキスト = PL/SQL: ORA-01747: user.table.column、table.columnまたは列指定が無効です...続きを読む

Aベストアンサー

if (vn_CNT > 0 ) then
-- 座席マスタ更新実行
行27 update M_SEAT
行28 set M_SEAT.CD_PC = :new.NK_PC ,
行29 M_SEAT.CD_TANTO = :new.CD_TANTO
行30 where M_SEAT.NO_PORT = :new.NO_PORT ;
end if ;

これでどうでしょうか?

NK_PCというカラムはありますか?


人気Q&Aランキング