マンガでよめる痔のこと・薬のこと

データベースはOracle10gです。

あるテーブルAにレコードが登録されたタイミングで、
別のテーブルBにレコードを登録するトリガを作成しようとしています。

トリガ内に全ての処理を記述するとかなりの行数になりそうなので、
登録のプロシージャを作成し、トリガからコールしようと考えています。

Aに登録されたレコードのカラムの一部を、Bに登録するレコードでも使用したいのですが、
プロシージャに引数として渡す際、
使用したいカラムを一つずつ指定しなければならないのでしょうか?
(使用したいカラムは10ほどあります。)
トリガに
「REFERENCING NEW ROW R1」と記述して、
このR1を渡すことはできないのでしょうか?

A 回答 (2件)

>REFERENCING NEW ROW R1


という記述はできないと思います。

登録用プロシージャの引数はレコード型にしておいて、
トリガーでselect into でレコード型変数に値を格納して渡すようにしてはどうでしょうか。
トリガーではカラムを一つずつ列挙しないといけませんが、
プロシージャの引数はすっきり書けるようになります。

create or replace procedure test_proc(
a_rec table_a%rowtype --不要な項目が多ければ別途レコード型を作成しても
) is
begin
insert into table_b
values (a_rec.c1,a_rec.c2,…);
end;
/

create or replace trigger test_trig
after insert on table_a
for each row
declare
w_rec table_a%rowtype;
begin
select :new.c1,:new.c2,…
into w_rec
from dual;
test_proc(w_rec);
end;
/

あんまり変わらないですかね・・・
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

とりあえず行数は気にせず、
プロシージャを呼び出さずにトリガ内だけで処理を行ってみたところ、

CREATE TRIGGER TRG_TEST
AFTER INSERT ON TBL_A
REFERENCING NEW ROW R1
FOR EACH NOW
BEGIN
INSERT INTO TBL_B (USER_ID, USER_NM, SYOZOKU_NM) VALUES (
R1.user_id,
R1.user_nm,
(SELECT syozoku_nm FROM TBL_SYOZOKU WHERE syozoku_cd = R1.syozoku_cd)
);
END

のような形で実行できました。(説明用に一部だけ抜粋しています)
省略した項目の中に、コードから名称を取得するような項目が他にも多数あったり、
コードの値によって分岐するような処理(IF文使用)があり、
処理が長くなってしまいそうなのでプロシージャを作ってコールしようとしています。

"プロシージャに引き渡す場合に「REFERENCING~」はダメ"という意味だったのでしょうか?

NO.1の方にご回答頂きましたが
引数10個ならそのまま記述することもアリなようですので
引数はそのまま記述してしまおうかと考えています。
レコード型を利用するなら不要な項目が多いので
オリジナルのレコード型を作成する方式になりそうです。

お礼日時:2010/12/25 09:11

項目10個くらいなら書けばいいでしょう。

desc テーブルAとかやってコピペで何とかなるでしょう。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

一般的なプロシージャのコールの場合、引数に10個記述しても良いのかな?
ということで質問しました。
実際記述してみて動作させましたが問題なさそうですね。

お礼日時:2010/12/25 09:14

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

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

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

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

Q特定のカラムが更新されたときのみ、そのレコードを別テーブルへ格納するト

特定のカラムが更新されたときのみ、そのレコードを別テーブルへ格納するトリガの作成方法を教えてください・

トリガ名:TEST_UPDATE
テーブル名:MV_ORACLE_MV_TEST
カラム名:ORAPRIME
ORACHANGE
ORANAME
CHANGENAME

対象カラム:CHANGENAME
格納先テーブル:TRITEST_ORACLE_MV_TEST

作成トリガ:
CREATE OR REPLACE TRIGGER TEST_UPDATE
after update of CHANGENAME on MV_ORACLE_MV_TEST
FOR EACH ROW
begin
INSERT INTO TRITEST_ORACLE_MV_TEST
SELECT MV_ORACLE_MV_TEST.ORAPRIME ,
MV_ORACLE_MV_TEST.ORACHANGe ,
MV_ORACLE_MV_TEST.ORANAME ,
MV_ORACLE_MV_TEST.CHANGENAME
FROM
MV_ORACLE_MV_TEST
where
MV_ORACLE_MV_TEST.CHANGENAME = :new.CHANGENAME
-- commit;

commit;
end
/

テーブルMV_ORACLE_MV_TESTのうち、CHANGENAMEカラムが変更(更新)されたレコードのみを、
TRITEST_ORACLE_MV_TESTテーブルに格納したいと思っております。
この時、他のカラムも一緒に格納することが必要でして、
(もしカラムに変更があった場合、そのカラムも変更する。変更されていない場合は元の値を格納する)
そのトリガを上記のように書いたのですが、
コンパイルエラーが発生しました。
どの部分が誤っているのか、ご教授願えませんでしょうか

特定のカラムが更新されたときのみ、そのレコードを別テーブルへ格納するトリガの作成方法を教えてください・

トリガ名:TEST_UPDATE
テーブル名:MV_ORACLE_MV_TEST
カラム名:ORAPRIME
ORACHANGE
ORANAME
CHANGENAME

対象カラム:CHANGENAME
格納先テーブル:TRITEST_ORACLE_MV_TEST

作成トリガ:
CREATE OR REPLACE TRIGGER TEST_UPDATE
after update of CHANGENAME on MV_ORACLE_MV_TEST
FOR EACH ROW
begin
INSERT INTO TRITEST_ORACLE_MV_TEST
SELECT MV_ORACLE_MV_TEST.ORAPRIME ,
...続きを読む

Aベストアンサー

・・・ご自身で少しは考えていらっしゃいますか?
やりたいことはそういうことなのだろうと思っていましたが敢えて書きませんでした。

CHANGENAMEが変更されたら、というIF文を書けばいいだけなのではないですか?
if :old.CHANGENAME != :new.CHANGENAME THEN
--insert
end if;

CHANGENAMEにnullが入る場合は上記の条件では足りないと思いますが、
そこはご自身で書いてください。

QPL/SQL PLS-00103エラーについて

PL/SQL PLS-00103エラーについて

夜分遅くに申し訳ございません。
PL/SQLのコーディングを実施し、コンパイル中にPLS-00103エラーが発生してしまったのですが、
対処方法がわからず困ってしまいました。

・メッセージ内容:”PLS-00103: 記号"="が見つかりました。”


上記メッセージで指定されていたソース内容(イメージ)↓
--------------------------------------------------------------


BEGIN
LV_STEP := '**登録処理開始'  -- 左記の:=の=部分がエラーメッセージで指定されていました。(LV_STEPはログ出力用の変数)

CURSOR AAAA IS
SELECT GG.EPLY_NO GG_EPLY_NO
,GG.R_CD GG_R_CD
,GG.S_CARD GG_S_CARD

    ・
   ・
FROM GRA_GRA_TR GG



------------------------------------------------------------------

といった感じなのですが、
このPLS-00103エラーの対処方法をどなたかご教示いただけませんでしょうか。
よろしくお願いいたします。

PL/SQL PLS-00103エラーについて

夜分遅くに申し訳ございません。
PL/SQLのコーディングを実施し、コンパイル中にPLS-00103エラーが発生してしまったのですが、
対処方法がわからず困ってしまいました。

・メッセージ内容:”PLS-00103: 記号"="が見つかりました。”


上記メッセージで指定されていたソース内容(イメージ)↓
--------------------------------------------------------------


BEGIN
LV_STEP := '**登録処理開始'  -- 左記の:=の=部分がエラーメッセージで指定されていま...続きを読む

Aベストアンサー

PLS-00103は構文解析エラーです。
指定されている行にはセミコロンがありませんが、
これが原因だとすると、
エラーメッセージは後ろに続く文で出るはずです。
エラーメッセージで示された箇所より前の部分(行)を調べてください。

# ソースの内容をイメージで示されても困ります。
# 回答者に超能力者は(おそらく)いないと思いますので
# 勘で答えるよりほか仕方がありません。

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

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

で確認ができる。

QViewにインデックスは張れますか?

件名の通りなのですが、作成したViewが遅くて困っています。
改善方法としてはViewを作成しないで従来のSQLにインデックスを張って取得する方法にしようかなと考えています。
なにかいい方法はありますか?

Aベストアンサー

Viewの元テーブルに適切なIndexを貼る、ではいけないのですか?

QPL/SQLをWindowsのBATファイルで実行するには

いつも参考にさせていただいております。
表題のとおり、WindowsServer2003上でBATファイルを起動したいのですが、うまくいきません。

BATファイルの中身
sqlplus USER_A/USER_A @DB_A @SQLPAT
sqlplus USER_B/USER_B @DB_B @SQLPAT

SQLPATファイルは、大体以下の感じで、2つのスキーマーで同じPL/SQLを実行します。

Declare

カーソルループ
更新


END;

タスクにBATファイルを登録し実行すると
最初のPL/SQLは実行されますが、次のUSER_Bが実行されません。

どなたか詳しい方ご教授をお願いできませんでしょうか?
オラクルのVerは9iです。よろしくお願い致します。

Aベストアンサー

pl_sqlは予めDBに登録しておく。
CREATE OR REPLACE PROCEDURE pl_hoge()
IS
・・・
BEGIN
・・・
END;
/

■hoge.bat
sqlplus user/pass@dbname @hoge.sql

■hoge.sql
SET serveroutput ON;
BEGIN
pl_hoge();
END;
/
exit;

1さんはhoge.sql最後のexit;を指摘してます。
私もそれと疑いますが。

Qプロシージャ 引数 指定

プロシージャ定義・宣言時、引数を使わないので、引数なしで宣言したいのですが、できるのでしょうか?
どう定義・宣言すればいいのでしょうか?
よろしくお願いいたします。
PROCEDURE A(); やPROCEDURE A;でも駄目なので、
PROCEDURE A() IS~やPROCEDURE A IS~も駄目でした。

Aベストアンサー

SQL> CREATE OR REPLACE PROCEDURE NOP
2 IS BEGIN
3 NULL;
4 END;
5 /

プロシージャが作成されました。

SQL>

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) ||'い'

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

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.年月 = 任意の値

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

QSIDとSERVICE_NAMEの違いとは?

tnsnames.oraの中の(CONNECT_DATA=に SIDだったりSERVICE_NAMEだったりしますが、どのような違いがあるのでしょうか?
どちらでもつながるのでいいかとは思いますが、何かメリット・デメリットでもあるのでしょうか?
それとも過去の遺物が未だ共存しているだけでしょうか?
SIDはインスタンスにつけられた名前だと知っていますが、SERVICE_NAMEってなんですか?

Aベストアンサー

SIDはインスタンスの識別子、service_nameはサービス名。
インスタンスとサービスの違いはRAC を勉強されるとよくわかると思います。

サービス名は初期化パラメータのservice_namesで設定します。
設定されていない場合は先述のとおりdb_unuque_name.db_domainになります。
(これが同義と誤解されやすい原因となっているわけですが)

ところで、service_namesパラメータですが、複数形になっていることからもわかるように、複数のサービス名をコンマ区切りで指定できるようになっています。

これをこのように使います。

2ノードRAC環境で、インスタンス1(SID=hoge1)、インスタンス2(SID=hoge2)があったとします。
ノードを意識することなく接続できるようにするためにクライアント側のtnsnames.oraにはこんな設定を追加します。

HOGE.EXAMPLE.COM
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(CONNECT_DATA = (SERVER = DECIDATED)(SERVICE_NAME = hoge.example.com))
)

インスタンス1とインスタンス2のservice_namesパラメータにhoge.example.comを追加します。すると、インスタンス1とインスタンス2にラウンドロビンで接続できるようになります。

ここで、もしtnsnames.oraのservice_name=...がSID=hoge1だったらどうなるでしょう?
インスタンス2のSIDはhoge2ですから一致せず、インスタンス2には接続できず、いつもインスタンス1に繋がってしまいます。

次に、一時的にメンテナンスのためにインスタンス2にアクセスしてほしくないケースを考えます。service_nameであれば、インスタンス2のservice_namesパラメータからhoge.example.comを削除することで(動的に変更できます)直ちにインスタンス1にのみ接続されるようにできます。

ここで、もしORACLE_SIDだったら・・・変更のためにインスタンスの再起動が必要になってしまいますね?

最後にservice_namesは複数登録できます。3ノードRAC環境において、オンライン処理(service_name=ONLINE)、バッチ処理(service_name=BATCH)があったとします。
バッチ処理は1つのノード(インスタンス1)でのみ処理したいとします。オンライン処理は3つのノードでしたいとします。

そんなとき、service_nameであれば、

<<サーバ側の初期化パラメータ>>
1:service_names = online, batch
2:service_names = online
3.service_names = online

<<クライアント側のtnsnames.ora>>

オンライン処理
ONLINE.EXAMPLE.COM
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(CONNECT_DATA = (SERVER = DECIDATED)(SERVICE_NAME = online.example.com))
)

BATCH.EXAMPLE.COM
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(CONNECT_DATA = (SERVER = DECIDATED)(SERVICE_NAME = batch.example.com))
)

と、クライアント側では接続先サーバを気にすることなく構成できますね?
BATCHのノードを1から2にしたい場合の変更はサーバ側のみでできますよね?
serivce_namesの"batch"設定をノード2に移すだけです。

SIDはインスタンスの識別子、service_nameはサービス名。
インスタンスとサービスの違いはRAC を勉強されるとよくわかると思います。

サービス名は初期化パラメータのservice_namesで設定します。
設定されていない場合は先述のとおりdb_unuque_name.db_domainになります。
(これが同義と誤解されやすい原因となっているわけですが)

ところで、service_namesパラメータですが、複数形になっていることからもわかるように、複数のサービス名をコンマ区切りで指定できるようになっています。

これをこのように...続きを読む


人気Q&Aランキング

おすすめ情報