こんにちは！ 健史です。
PLSQLで、バルクでのコントロールブレイク処理を作成しました。
非構造型です。
プログラム
create or replace PROCEDURE PROC_BULK2 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用テーブル定義
TYPE OT1TBL IS TABLE OF 売上履歴集計１%ROWTYPE INDEX BY BINARY_INTEGER;
OT1REC OT1TBL;
-- バルク INSERT用テーブル定義
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;
IXO1 NUMBER(6);
IXO2 NUMBER(6);
SV_KEY1 VARCHAR2(100);
SV_KEY2 VARCHAR2(100);
SV1REC 売上履歴集計１%ROWTYPE;
SV2REC 売上履歴集計２%ROWTYPE;
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;
IXO1 := 0;
OT1REC.DELETE;
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;
IXO2 := 0;
OT2REC.DELETE;
RETURN(NULL);
-- バルク PROC_BULK_ERROR_HANDLER;
EXCEPTION
WHEN eBulkProcessNotComplete THEN
vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
RETURN(NULL);
END;
--5.FUNC_INIT_OT_SET1
FUNCTION FUNC_INIT_OT_SET1(i IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
SV1REC.キー１ := IN1REC(i).キー１;
SV1REC.キー２ := IN1REC(i).キー２;
SV1REC.キー３ := IN1REC(i).キー３;
SV1REC.連番 := IN1REC(i).連番;
SV1REC.数量 := IN1REC(i).数量;
SV1REC.単価 := IN1REC(i).単価;
SV1REC.団体コード := IN1REC(i).団体コード;
SV1REC.件数 := 0;
RETURN(NULL);
END;
--5.FUNC_INIT_OT_SET2
FUNCTION FUNC_INIT_OT_SET2(i IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
SV2REC.キー１ := IN1REC(i).キー１;
SV2REC.キー２ := IN1REC(i).キー２;
SV2REC.キー３ := IN1REC(i).キー３;
SV2REC.連番 := IN1REC(i).連番;
SV2REC.数量 := IN1REC(i).数量;
SV2REC.単価 := IN1REC(i).単価;
SV2REC.団体コード := IN1REC(i).団体コード;
SV2REC.件数 := 0;
RETURN(NULL);
END;
--5.OUT-RECORD-SET1
FUNCTION FUNC_OT_SET1
RETURN VARCHAR2 IS
BEGIN
IXO1 := IXO1 + 1 ;
OT1REC(IXO1) := SV1REC ;
出力件数1 := 出力件数1 + 1;
RETURN(NULL);
END;
--5.OUT1-RECORD-SET2
FUNCTION FUNC_OT_SET2
RETURN VARCHAR2 IS
BEGIN
IXO2 := IXO2 + 1 ;
OT2REC(IXO2) := SV2REC ;
出力件数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用テーブル初期クリア
OT1REC.DELETE;
OT2REC.DELETE;
SV_KEY1 := ' ';
IXO1 := 0;
IXO2 := 0;
LOOP
-- バルク FETCH
IN1REC.DELETE;
FETCH CUR1 BULK COLLECT INTO IN1REC LIMIT BULK_SIZE;
EXIT WHEN IN1REC.COUNT = 0;
--
FOR i in 1 .. IN1REC.COUNT LOOP
IF IN1REC(i).キー１ != SV_KEY1 OR
IN1REC(i).キー２ != SV_KEY2 THEN
IF SV_KEY1 != ' ' THEN
DUMMY := FUNC_OT_SET2();
END IF;
SV_KEY2 := IN1REC(i).キー２ ;
DUMMY := FUNC_INIT_OT_SET2(i);
END IF;
IF (IN1REC(i).キー１ != SV_KEY1) THEN
IF SV_KEY1 != ' ' THEN
DUMMY := FUNC_OT_SET1();
END IF;
SV_KEY1 := IN1REC(i).キー１ ;
DUMMY := FUNC_INIT_OT_SET1(i);
END IF;
-- 集計処理
入力件数 := 入力件数 + 1;
SV1REC.件数 := SV1REC.件数 + 1;
SV2REC.件数 := SV2REC.件数 + 1;
END LOOP;
IF IXO1 > 0 THEN
DUMMY := FUNC_INSERT1();
IXO1 := 0;
END IF;
IF IXO2 > 0 THEN
DUMMY := FUNC_INSERT2();
IXO2 := 0;
END IF;
END LOOP;
IF 入力件数 > 0 THEN
DUMMY := FUNC_OT_SET1();
DUMMY := FUNC_INSERT1();
DUMMY := FUNC_OT_SET2();
DUMMY := FUNC_INSERT2();
END IF;
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_BULK2;
補足
・データ量は「キー１ ＞ キー２」の関係
キー１が大分類、キー２が中分類(小分類)といった関係です。
キー１がブレイクしたら、売上履歴集計１に出力します。
キー１・キー２がブレイクしたら、売上履歴集計２に出力します。
・[FUNCTION]については、本来の使用方法とは異なっている
PL/SQLの関数[FUNCTION]は、値を返すことが前提です。
今回、同じコーディングをしたくないために、複数個所から実行される処理をFUNCTIONで定義し、返すものがないので、DUMMYという項目を定義し、値を返す形にしています。
・終了処理での[IF 入力件数 > 0 THEN]について
IF 入力件数 > 0 THEN
DUMMY := FUNC_OT_SET1();
DUMMY := FUNC_INSERT1();
DUMMY := FUNC_OT_SET2();
DUMMY := FUNC_INSERT2();
END IF;
これがないと、最後のブレイクキーデータが処理されません。
理由は、以下の記事を参照頂ければと思います。
コンロトールブレイク キーブレイクのロジックを徹底解説!VBAで確認
コントロールブレイク・キーブレイク処理をこれから作成する、作成中だけど正確に動かない、作成したけど正確性を確認したいなど思われている方へ、チェックポイントを含め詳細を紐解いて解説します。基本中の基本であるロジックをマスターしちゃいましょう！
jibun-kaizen.com
2021.01.08
処理時間の比較
・2,000件 0:09
・ 1件 0:38
開始時刻：25-05-25 06:14:04.953000
入力件数 :=1500000
出力件数１ :=15000
出力件数２ :=150000
エラー数:0
終了時刻：25-05-25 06:14:15.545000
開始時刻：25-05-25 06:15:03.636000
入力件数 :=1500000
出力件数１ :=15000
出力件数２ :=150000
エラー数:0
終了時刻：25-05-25 06:15:41.429000
コメント