こんにちは！ 健史です。
バルクでのコントロールブレイク処理を構造化してみました。
ですが、複雑になりました。
もっと良い方法があるかもしれません。
もっと良い方法をお持ちの方、考え付いた方は、お問い合わせからご連絡いただけば嬉しいです。
個人的には、バルクでのコントロールブレイク処理は、非構造型の方が良いと思います。
プログラム
create or replace PROCEDURE PROC_BULK3 AS
--1.バルク サイズ定義
BULK_SIZE CONSTANT PLS_INTEGER := 2000; -- コンスタントでなくても良い
--2.カーソル定義
CURSOR CUR1 IS
SELECT * FROM 売上履歴
ORDER BY キー１,キー２,連番 ;
--3.バルク テーブル等定義
-- バルク FETCH用テーブル定義
TYPE IN1TBL IS TABLE OF 売上履歴%ROWTYPE INDEX BY BINARY_INTEGER;
IN1REC IN1TBL;
-- バルク INSERT用テーブル定義 WK_KEY1
TYPE OT1TBL IS TABLE OF 売上履歴集計１%ROWTYPE INDEX BY BINARY_INTEGER;
OT1REC OT1TBL;
-- バルク INSERT用テーブル定義 WK_KEY2
TYPE OT2TBL IS TABLE OF 売上履歴集計２%ROWTYPE INDEX BY BINARY_INTEGER;
OT2REC OT2TBL;
-- バルク エラーハンドラ用 ORA-24381: DML配列にエラーがあります。
vBulkErrors PLS_INTEGER := 0;
eBulkProcessNotComplete EXCEPTION;
PRAGMA EXCEPTION_INIT(eBulkProcessNotComplete, -24381);
--4.ワークを定義
入力件数 NUMBER(10);
出力件数1 NUMBER(10);
出力件数2 NUMBER(10);
WK_TIMESTAMP TIMESTAMP;
IXI1 NUMBER(6);
IXO1 NUMBER(6);
IXO2 NUMBER(6);
SV_KEY1 VARCHAR2(100);
SV_KEY2 VARCHAR2(100);
WK_CNT1 NUMBER(10);
WK_CNT2 NUMBER(10);
-- バルク処理でコントロールブレイクを行うための制御スイッチ
FLG_SKIP1 NUMBER(1);
FLG_SKIP2 NUMBER(1);
DUMMY CHAR(1);
--5.ＦＵＮＣＴＩＯＮ定義 必要に応じて定義
--5.INSERT1
FUNCTION FUNC_INSERT1
RETURN VARCHAR2 IS
BEGIN
FORALL i in 1..OT1REC.COUNT SAVE EXCEPTIONS -- ← エラーが発生しても継続
INSERT INTO 売上履歴集計１ VALUES OT1REC(i);
COMMIT;
RETURN(NULL);
-- バルク PROC_BULK_ERROR_HANDLER;
EXCEPTION
WHEN eBulkProcessNotComplete THEN
vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
RETURN(NULL);
END;
--5.INSERT2
FUNCTION FUNC_INSERT2
RETURN VARCHAR2 IS
BEGIN
FORALL i in 1..OT2REC.COUNT SAVE EXCEPTIONS -- ← エラーが発生しても継続
INSERT INTO 売上履歴集計２ VALUES OT2REC(i);
COMMIT;
RETURN(NULL);
-- バルク PROC_BULK_ERROR_HANDLER;
EXCEPTION
WHEN eBulkProcessNotComplete THEN
vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
RETURN(NULL);
END;
--5.OUT-RECORD-SET1
FUNCTION FUNC_OT_SET1
RETURN VARCHAR2 IS
BEGIN
OT1REC(IXO1).件数 := WK_CNT1 ;
出力件数1 := 出力件数1 + 1;
RETURN(NULL);
END;
--5.OUT1-RECORD-SET2
FUNCTION FUNC_OT_SET2
RETURN VARCHAR2 IS
BEGIN
OT2REC(IXO2).件数 := WK_CNT2 ;
出力件数2 := 出力件数2 + 1;
RETURN(NULL);
END;
--6.メイン処理
BEGIN
--6-1.初期処理
OPEN CUR1;
EXECUTE IMMEDIATE 'TRUNCATE TABLE 売上履歴集計１';
EXECUTE IMMEDIATE 'TRUNCATE TABLE 売上履歴集計２';
入力件数 := 0;
出力件数1 := 0;
出力件数2 := 0;
SELECT TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff5') INTO WK_TIMESTAMP FROM DUAL;
DBMS_OUTPUT.PUT_LINE('開始時刻：' || WK_TIMESTAMP);
-- バルク FETCH,INSERT用テーブル初期クリア
IN1REC.DELETE;
OT1REC.DELETE;
OT2REC.DELETE;
-- バルク FETCH
FETCH CUR1 BULK COLLECT INTO IN1REC LIMIT BULK_SIZE;
IXI1 := 1 ;
IXO1 := 0 ;
IXO2 := 0 ;
FLG_SKIP1 := 0;
FLG_SKIP2 := 0;
--6-2.コントロールブレイク
WHILE IN1REC.COUNT != 0 LOOP
--6-2-1.初期処理1
IF FLG_SKIP1 != 2 THEN
SV_KEY1 := IN1REC(IXI1).キー１ ;
WK_CNT1 := 0;
IXO1 := IXO1 + 1 ;
OT1REC(IXO1).キー１ := IN1REC(IXI1).キー１;
OT1REC(IXO1).キー２ := IN1REC(IXI1).キー２;
OT1REC(IXO1).キー３ := IN1REC(IXI1).キー３;
OT1REC(IXO1).連番 := IN1REC(IXI1).連番;
OT1REC(IXO1).数量 := IN1REC(IXI1).数量;
OT1REC(IXO1).単価 := IN1REC(IXI1).単価;
OT1REC(IXO1).団体コード := IN1REC(IXI1).団体コード;
OT1REC(IXO1).件数 := 0;
END IF;
--6-2-1.初期処理2
WHILE IXI1 <= IN1REC.COUNT AND SV_KEY1 = IN1REC(IXI1).キー１ LOOP
IF FLG_SKIP2 != 2 THEN
SV_KEY2 := IN1REC(IXI1).キー２ ;
WK_CNT2 := 0;
IXO2 := IXO2 + 1 ;
OT2REC(IXO2).キー１ := IN1REC(IXI1).キー１;
OT2REC(IXO2).キー２ := IN1REC(IXI1).キー２;
OT2REC(IXO2).キー３ := IN1REC(IXI1).キー３;
OT2REC(IXO2).連番 := IN1REC(IXI1).連番;
OT2REC(IXO2).数量 := IN1REC(IXI1).数量;
OT2REC(IXO2).単価 := IN1REC(IXI1).単価;
OT2REC(IXO2).団体コード := IN1REC(IXI1).団体コード;
OT2REC(IXO2).件数 := 0;
END IF;
--6-2-2.集計処理
WHILE IXI1 <= IN1REC.COUNT AND IN1REC(IXI1).キー１ = SV_KEY1 AND IN1REC(IXI1).キー２ = SV_KEY2 LOOP
入力件数 := 入力件数 + 1;
WK_CNT1 := WK_CNT1 + 1;
WK_CNT2 := WK_CNT2 + 1;
IXI1 := IXI1 + 1 ;
END LOOP;
--6-2-3.後処理2
-- FETCHしたデータが終わったとき
IF IXI1 > IN1REC.COUNT THEN
-- 次のバルクFETCH
IN1REC.DELETE;
FETCH CUR1 BULK COLLECT INTO IN1REC LIMIT BULK_SIZE;
IXI1 := 1 ;
-- FETCHした次のデータが終わり、または、FETCHした次のデータがあり、ブレイクしていたとき
IF IN1REC.COUNT = 0 OR (IN1REC(IXI1).キー１ != SV_KEY1 OR IN1REC(IXI1).キー２ != SV_KEY2) THEN
-- １).集計データをINSERT用テーブルにセット
DUMMY := FUNC_OT_SET2();
-- 2).INSERT
DUMMY := FUNC_INSERT2();
-- 3).INSERT用テーブル初期クリア
OT2REC.DELETE;
IXO2 := 0 ;
FLG_SKIP2 := 1; --FETCHした次のデータは、キーが異なりブレイク処理したので初期処理から
ELSE
FLG_SKIP2 := 2; --FETCHした次のデータは、キーが同じでブレイクしていないので初期処理はスキップ
END IF;
-- FETCHデータがまだある状態でブレイクしたときは、集計データをINSERT用テーブルにセット
ELSE
DUMMY := FUNC_OT_SET2();
FLG_SKIP2 := 9; --FETCHデータがあり、ブレイクしたので初期処理から
END IF;
END LOOP;
--6-2-3.後処理1
-- ＊＊＊最小のブレイク処理でFETCHした次のデータが終わり＊＊＊ここだけ別の処理の変数を使用しているので注意
IF FLG_SKIP2 < 9 THEN
-- FETCHした次のデータがあり、ブレイクしていたとき
IF IN1REC.COUNT = 0 OR SV_KEY1 != IN1REC(IXI1).キー１ THEN
-- １).集計データをINSERT用テーブルにセット
DUMMY := FUNC_OT_SET1();
-- 2).INSERT
DUMMY := FUNC_INSERT1();
-- 3).INSERT用テーブル初期クリア
OT1REC.DELETE;
IXO1 := 0 ;
FLG_SKIP1 := 1; --FETCHした次のデータは、キーが異なりブレイク処理したので初期処理から
ELSE
FLG_SKIP1 := 2; --FETCHした次のデータは、キーが同じでブレイクしていないので初期処理はスキップ
END IF;
ELSE
DUMMY := FUNC_OT_SET1();
FLG_SKIP1 := 9; --FETCHデータがあり、ブレイクしたので初期処理から
END IF;
END LOOP;
--7.終了処理
COMMIT;
DBMS_OUTPUT.PUT_LINE('入力件数 :=' || 入力件数);
DBMS_OUTPUT.PUT_LINE('出力件数１ :=' || 出力件数1);
DBMS_OUTPUT.PUT_LINE('出力件数２ :=' || 出力件数2) ;
DBMS_OUTPUT.PUT_LINE('エラー数:' || vBulkErrors);
SELECT TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff5') INTO WK_TIMESTAMP FROM DUAL;
DBMS_OUTPUT.PUT_LINE('終了時刻：' || WK_TIMESTAMP);
CLOSE CUR1;
END PROC_BULK3;
処理時間の比較
・2,000件 0:09
・ 1件 0:37
開始時刻：25-05-25 08:09:27.761000
入力件数 :=1500000
出力件数１ :=15000
出力件数２ :=150000
エラー数:0
終了時刻：25-05-25 08:09:36.048000
開始時刻：25-05-25 08:12:48.290000
入力件数 :=1500000
出力件数１ :=15000
出力件数２ :=150000
エラー数:0
終了時刻：25-05-25 08:13:25.363000
コメント