累積テーブルへのバックアップ方法について

質問

OracleでのデータバックアップをSQLにて作成しようとしています。

【処理内容】
売上テーブル(URITBL)から売上累積テーブル(URIRUITBL)へ
　※売上年月がシステム日付より12カ月以上前（2010年8月以前） AND 領収済みのものを
　　売上テーブルから売上累積テーブルへ追加し、
　　売上テーブルにあったデータは削除する

売上明細テーブル(URIMTBL)と売上明細累積テーブル(URIRUIMTBL)へ
　※売上年月システム日付より12カ月以上前（2010年8月以前） AND 領収済みのものを
　　売上明細テーブルから売上明細累積テーブルへ追加し、
　　売上明細テーブルにあったデータは削除する

【テーブル内容】
売上テーブル(URITBL)更新前
　支店CD、顧客NO、売上年月、氏名、TEL、領収済フラグ（※領収済…'1'、領収未…SPACEです）
　STNCOD、KKKNO、URIYM、NAME、TEL、RYOFLG
　001　　　 000012　　201006　　Ａ田太郎　　123-1234　　　1
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　　　　
　001　　　 000012　　201008　　Ａ田太郎　　123-1234　　　1
　001　　　 000012　　201009　　Ａ田太郎　　123-1234　　　1
　001　　　 000012　　201010　　Ａ田太郎　　123-1234　　　1
　002　　　 000101　　201006　　Ｂ田二郎　　345-5678　　　
　002　　　 000101　　201007　　Ｂ田二郎　　345-5678　　　1
　002　　　 000101　　201008　　Ｂ田二郎　　345-5678　　　1
　002　　　 000101　　201009　　Ｂ田二郎　　345-5678　　
　002　　　 000101　　201010　　Ｂ田二郎　　345-5678　　　1

売上明細テーブル(URIMTBL)更新前
　支店CD、顧客NO、売上年月、売上金額
　STNCOD、KKKNO、URIYM、URIKIN
　001　　　 000012　　201006　　　 1,000
　001　　　 000012　　201007　　　 2,000
　001　　　 000012　　201008　　　 3,000
　001　　　 000012　　201009　　　 4,000
　001　　　 000012　　201010　　　 5,000
　002　　　 000101　　201006　　　 500
　002　　　 000101　　201007　　　 1,500
　002　　　 000101　　201008　　　 2,500
　002　　　 000101　　201009　　　 3,500
　002　　　 000101　　201010　　　 4,500

売上テーブル(URITBL)更新後
　支店CD、顧客NO、売上年月、氏名、TEL、領収済フラグ
　STNCOD、KKKNO、URIYM、NAME、TEL、RYOFLG
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　　　
　001　　　 000012　　201009　　Ａ田太郎　　123-1234　　　1
　001　　　 000012　　201010　　Ａ田太郎　　123-1234　　　1
　002　　　 000101　　201006　　Ｂ田二郎　　345-5678　　　
　002　　　 000101　　201009　　Ｂ田二郎　　345-5678　　　
　002　　　 000101　　201010　　Ｂ田二郎　　345-5678　　　1

売上明細テーブル(URIMTBL)更新後
　支店CD、顧客NO、売上年月、売上金額
　STNCOD、KKKNO、URIYM、URIKIN
　001　　　 000012　　201007　　　 2,000
　001　　　 000012　　201009　　　 4,000
　001　　　 000012　　201010　　　 5,000
　002　　　 000101　　201006　　　 500
　002　　　 000101　　201009　　　 3,500
　002　　　 000101　　201010　　　 4,500

売上累積テーブル(URIMTBL)更新後
　支店CD、顧客NO、売上年月、氏名、TEL、領収済フラグ
　STNCOD、KKKNO、URIYM、NAME、TEL、RYOFLG
　001　　　 000012　　201006　　Ａ田太郎　　123-1234　　　1
　001　　　 000012　　201008　　Ａ田太郎　　123-1234　　　1
　002　　　 000101　　201007　　Ｂ田二郎　　345-5678　　　1
　002　　　 000101　　201008　　Ｂ田二郎　　345-5678　　　1

売上明細累積テーブル(URIMRUITBL)更新後
　支店CD、顧客NO、売上年月、売上金額
　STNCOD、KKKNO、URIYM、URIKIN
　001　　　 000012　　201006　　　 1,000
　001　　　 000012　　201008　　　 3,000
　002　　　 000101　　201007　　　 1,500
　002　　　 000101　　201008　　　 2,500

売上テーブル(URITBL)から売上累積テーブル(URIRUITBL)へ追加のSQL、売上テーブル(URITBL)の削除のSQLは以下の2つではないかと思います。

INSERT INTO URIRUITBL SELECT * FROM URITBL WHERE RYOFLG='1' AND ( URIYM < ADD_MONTHS (SYSDATE, -12) ) ;
DELETE FROM URITBL WHERE RYOFLG=1 AND ( URIYM < ADD_MONTHS (SYSDATE, -12) ) ;

ただ、売上明細テーブル(URIMTBL)と売上明細累積テーブル(URIRUIMTBL)へ追加のSQL、売上累積テーブル(URIMTBL)の削除のSQLはどうやってコーディングしたら良いのか悩んでいます。上記と同じように作っていくので大丈夫なのか、それとも別関数を使って作っていくのか、SQLの本などを半日ほど費やして調べてるのですが、いまだ至っておりません。最悪な時は別プログラムでとも考えているのですが、納期間近でできればSQLで解決したいと思いながら苦戦しています。

以前もこちらで教えて頂いてるのでたびたびの質問で恐縮ですが、分かる方がいれば手助けして頂きたいと思い、今回投稿しますのでご教授賜りたく思います。よろしくお願いします。

回答No.2

Siegrune

・この処理を行うのは毎月第1営業日となるため、月末日で月またがりになることはありません。
ならば、条件２は気にする必要ないですね。

・売上明細テーブルと売上明細累積テーブルのプライマリーキーは、支店CD・顧客NO・売上年月と
　先ほどの質問には書かれていない売上区分の4つとなっております。

ということでしたら、
売上区分が異なるだけで、支店CD・顧客NO・売上年月が同一のレコードが複数あった場合の処理は
どうしますか？
例）
　　STNCOD、KKKNO、URIYM、URIKIN、URIKBN
　　001　　　 000012　　201007　　　 2,000　　　1
　　001　　　 000012　　201007　　　 100　　 　3

STNCOD、KKKNO、URIYM、NAME、TEL、URIKBN,RYOFLG
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　1　　
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　3　1
となっていた場合、
(1)それぞれ１行目は累積へ移動して消したいが、
２行目は消したくないとするのが普通、ですかね。
(2)それとも、領収済フラグはどれか１レコードにしか入らないので、１行目にあっても
２行目にあっても、１，２行目両方累積へ移動して消したいということですか？

また、領収済フラグは、'1','2','3'の３種類とも対象にしたいのでは？

(1)の場合、
INSERT INTO URIMRUITBL SELECT * FROM URIMTBL a
WHERE ( URIYM < ADD_MONTHS (SYSDATE, -12) )
and
(select max(b.RYOFLG) from URITBL b
where a.STNCOD= b.STNCOD
and a.KKKNO = b.KKKNO
and a.URIYM = b.URIYM
and a.URIKBN = b.URIKBN)
in('1','2','3');
または、in('1','2','3')を>' '(スペース)とするなど。
※プライマリキーで一意のレコードが取得できるのでmax()は別に必要ないですが、
ま、習慣的につけていてもいいかも。

(2)の場合は、
INSERT INTO URIMRUITBL SELECT * FROM URIMTBL a
WHERE ( URIYM < ADD_MONTHS (SYSDATE, -12) )
and
(select max(b.RYOFLG) from URITBL b
where a.STNCOD= b.STNCOD
and a.KKKNO = b.KKKNO
and a.URIYM = b.URIYM)
in('1','2','3');
または、in('1','2','3')を>' '(スペース)とするなど。
※max()を使っているので、
スペースと'1'があれば、大きいほうの'1'を取得します。
プライマリキーで取得できていないのでmax()をつけていないと
複数レコードが取得されます。実行時にエラーとなります。

注：スペース、ですよね？　Null(なにも入っていない状態)ではないですよね？
Null なら、>' 'ではなく、is not null とか書きます。

なお、
プライマリキーが2件以上存在する構成にはならないようにしています。
プライマリキーとはレコードを一意（ユニーク）に識別するためのキーであり
重複を許すキーはプライマリキーにならないです。
（プライマリキーという用語の意味です。念のため。）

回答No.1

Siegrune

>売上明細テーブル(URIMTBL)と売上明細累積テーブル(URIRUIMTBL)へ追加のSQL、
>売上累積テーブル(URIMTBL)の削除のSQLはどうやってコーディングしたら良いのか悩んでいます。
>上記と同じように作っていくので大丈夫なのか、

まあ、いくつかの条件に合致しなければ大丈夫です。
条件１：売上明細テーブルの処理を先に実行すること。
売上テーブル処理を先に処理してしまうと、
売上明細テーブル処理のInsert時もDelete時も、売上テーブルの領収済フラグを判定できません。
（売上累積テーブルを参照していれば問題ないですが、売上累積テーブルのほうが削除前の
　売上テーブルより遥かに件数が多いなら、レスポンスが悪くなるかも。）

条件２：月末日の深夜１２時をまたぐタイミングで実行しないこと
片方のテーブルの処理が終わる前に日付が変わると処理する対象の月が変わってしまい、
もう一方のテーブルと整合性がとれなくなります。

あるいは、売上明細テーブル処理時に売上テーブルの領収済フラグ判定方法がわからないということ
でしょうか？

insertもdeleteも
WHERE ( URIYM < ADD_MONTHS (SYSDATE, -12) )
and
(select max(b.RYOFLG) from URITBL b
where a.STNCOD= b.STNCOD
and a.KKKNO = b.KKKNO
and a.URIYM = b.URIYM)
='1';
を使えばいいです。
ただし、STNCOD、KKKNO、URIYMがプライマリキーになっているか、
STNCOD、KKKNO、URIYM、NAME、TEL、RYOFLG
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　　　
　001　　　 000012　　201007　　Ａ田太郎　　123-1234　　　1

STNCOD、KKKNO、URIYM、URIKIN
　001　　　 000012　　201007　　　 2,000
　001　　　 000012　　201007　　　 1,500
というようなデータが存在できないようになっている前提。
（あるとどうしようもないです。どう考えても、どんな方法でも無理。）

>それとも別関数を使って作っていくのか、
別の方法です。

売上テーブルの処理を先にします。
そして、以下の条件で処理をします。
WHERE
not exist (select * from URITBL b
where a.STNCOD= b.STNCOD
and a.KKKNO = b.KKKNO
and a.URIYM = b.URIYM);
これは、売上テーブルに存在しないレコードを対象に処理します。
（条件は上に同じ）

なお、どちらの方法でも、トランザクションを１つにして、commitかエラー時にrollback
しないと不整合がおきますので気をつけてください。
（別プログラムにするとトランザクションを一緒にできません。）
片方の処理が正常終了して、次の処理が異常終了すると即不整合がでます。。。

お礼

御礼が遅くなって失礼しました。
このたびは、ご丁寧に詳細な説明ありがとうございます。
ただいま社内システムの自社開発中で、今回よりSQLを初めて触れるようになったので、
本当に初歩的な構文しか理解できていない旨をご了承ください。

こちらの記載不足で申し訳ありませんが、

　・この処理を行うのは毎月第1営業日となるため、月末日で月またがりになることはありません。

　・売上明細テーブルと売上明細累積テーブルのプライマリーキーは、支店CD・顧客NO・売上年月と
　　先ほどの質問には書かれていない売上区分の4つとなっております。
　　売上区分は、1：通常売上代、2：通常売上消費税、3：リース売上代、4：リース売上消費税で、
　　プライマリキーが2件以上存在する構成にはならないようにしています。
　　（例）
　　STNCOD、KKKNO、URIYM、URIKIN、URIKBN
　　001　　　 000012　　201007　　　 2,000　　　1
　　001　　　 000012　　201007　　　 100　　 　2
　　001　　　 000012　　201007　　　 500　 　 　3
　　001　　　 000012　　201007　　　 25　 　　4

　・売上テーブル・売上累積テーブルの領収済フラグの区分の持ち方は、スペース：領収未、
　　1：現金領収済、2：振込領収済、3：クレジット領収済　となっています。

　・売上年月のデータ型はcharです。

以上のような条件となります。

この場合、max関数を使っても大丈夫なのでしょうか？
また、売上年月のデータ型がcharの場合でも大丈夫でしたでしょうか？

もし大丈夫でしたら、一応コーディングしてみたのですが、
INSERT INTO URIMRUITBL SELECT * FROM URIMTBL a
WHERE ( URIYM < ADD_MONTHS (SYSDATE, -12) )
and
(select max(b.RYOFLG) from URITBL b
where a.STNCOD= b.STNCOD
and a.KKKNO = b.KKKNO
and a.URIYM = b.URIYM)
='1';
こんな感じで良いのかな…と作ってみましたが、正直自信がありません。

大変図々しい質問内容となりましたが、
もしよろしければ自分の教養のためにも添削・アドバイスをお願いできたらと思います。

申し訳ありませんが、よろしくお願いいたします。