15年以上前の PL/SQL

やたらと長いですが、重要な部分は二つです。

1) SAVEPOINTROLLBACK の関係
2) UPDATE で 対象データが無い場合に IF SQL%NOTFOUND THEN で、INSERT を実行

/*
/*=================================
	受注集計処理
=================================*/
CREATE or REPLACE PROCEDURE JHUPD
	(
	 ERR		IN OUT	NUMBER
	,P区分		IN	VARCHAR2	-- 1:作成,2:変更,3:集計,4:SZ03,SZ04集計
	,P組織		IN	VARCHAR2	-- 入力組織
	,P受注番号	IN	NUMBER
	,P行		IN	NUMBER		-- 登録行
	,P数量1		IN	NUMBER
	,P数量2		IN	NUMBER
	)
	AS

	EXIT_FLG	NUMBER;
	ERR_FLG		NUMBER;
	V数量		NUMBER;
	V数量2		NUMBER;
	V入出荷数量2	NUMBER;
	V完了		VARCHAR2(1);
	V在庫組織	VARCHAR2(4);

	V大分類		VARCHAR2(2);
	V商品コード	VARCHAR2(20);
	V場所区分	VARCHAR2(1);
	V場所コード	VARCHAR2(8);
	V単品コード	VARCHAR2(15);
	V認識		VARCHAR2(1);

CURSOR cur_TR01 is
		select 識別A   from TR01
		 where "識別A" = '1' AND "識別C" = ' ' AND "入力組織" = P組織  AND  "受発注番号" = P受注番号;
BEGIN
/*=================================
   初期処理
=================================*/
DBMS_OUTPUT.PUT_LINE('JHUPD START');
	SAVEPOINT UPD_START;
	ERR_FLG := ERR;
	ERR_FLG := 0;
	EXIT_FLG := 0;

	BEGIN
		select 在庫組織 into V在庫組織 from NA10 where 組織 = P組織;
	EXCEPTION
		WHEN OTHERS THEN
			V在庫組織 := P組織;
	END;
	BEGIN
		select
			大分類,
			商品コード,
			場所区分,
			場所コード,
			単品コード,
			入力数量2,
			入出荷数量2
		into
			V大分類,
			V商品コード,
			V場所区分,
			V場所コード,
			V単品コード,
			V数量2,
			V入出荷数量2
		from TR07
			WHERE
				"識別A" = '7' AND
				"識別C" = ' ' AND
				"入力組織" = P組織 AND
				"伝票番号" = P受注番号 AND
				"登録行" = P行;
	EXCEPTION
		WHEN OTHERS THEN
			EXIT_FLG := 1;
			ERR := 9;
	END;

	IF P区分 = '5' THEN
		V数量2 := V数量2 * -1;
	END IF;



 
/*=================================
   主処理
=================================*/
/*   作成  */
	ERR_FLG := 1;
	IF P区分 = '1'  and EXIT_FLG = 0 THEN
		UPDATE TR07 SET
			 "入出荷数量1"		= 0
			,"入出荷数量2"		= 0
			,"残高数量1"		= P数量1
			,"残高数量2"		= P数量2
		WHERE	"識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
		IF SQL%NOTFOUND THEN
			ERR := ERR_FLG;
		ELSE
			LOOP
				UPDATE SZ03 SET
					"現在現物数量2"		= "現在現物数量2"	- P数量2,
					"更新日"		= sysdate,
					"在庫更新日"		= sysdate
				WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
				IF SQL%NOTFOUND THEN
					INSERT INTO SZ03 VALUES
					(V在庫組織,V大分類,V商品コード
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0
					,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0);	/* 142 */
				ELSE
					EXIT;
				END IF;
			END LOOP;
			LOOP
				UPDATE SZ04 SET
					"現在現物数量2"		= "現在現物数量2"	- P数量2,
					"更新日"		= sysdate,
					"在庫更新日"		= sysdate
				WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード AND "場所区分" = NVL(V場所区分,' ') AND "場所コード" = NVL(V場所コード,' ') AND "単品コード" = NVL(V単品コード,' ');
				IF SQL%NOTFOUND THEN
					INSERT INTO SZ04 VALUES
					(V在庫組織
					,NVL(V場所区分,' ')
					,NVL(V場所コード,' ')
					,V大分類
					,V商品コード
					,NVL(V単品コード,' ')
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0
					,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
					,sysdate,NULL,sysdate,0);	/* 83 */
				ELSE
					EXIT;
				END IF;
			END LOOP;
		END IF;

	END IF;

/*   変更  */
	ERR_FLG := 2;
	IF P区分 = '2'  and EXIT_FLG = 0 THEN
		UPDATE TR07 SET
			 "残高数量1"		= 入力数量1 - "入出荷数量1"
			,"残高数量2"		= 入力数量2 - "入出荷数量2"
		WHERE	"識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
		IF SQL%NOTFOUND THEN
			ERR := ERR_FLG;
		ELSE
			LOOP
				UPDATE SZ03 SET
					"現在現物数量2"		= "現在現物数量2"	- P数量2,
					"更新日"		= sysdate,
					"在庫更新日"		= sysdate
				WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
				IF SQL%NOTFOUND THEN
					INSERT INTO SZ03 VALUES
					(V在庫組織,V大分類,V商品コード,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL
					,NULL,NULL,sysdate,NULL,sysdate,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0);	/* 142 */
				ELSE
					EXIT;
				END IF;
			END LOOP;
			LOOP
				UPDATE SZ04 SET
					"現在現物数量2"		= "現在現物数量2"	- P数量2,
					"更新日"		= sysdate,
					"在庫更新日"		= sysdate
				WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード AND "場所区分" = NVL(V場所区分,' ') AND "場所コード" = NVL(V場所コード,' ') AND "単品コード" = NVL(V単品コード,' ');
				IF SQL%NOTFOUND THEN
					INSERT INTO SZ04 VALUES
					(V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' '),V大分類,V商品コード
					,NVL(V単品コード,' ')
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
					,0,0,0,0,0,0,0,0
					,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
					,sysdate,NULL,sysdate,0);	/* 83 */
				ELSE
					EXIT;
				END IF;
			END LOOP;
		END IF;
	END IF;

/*   集計  */
	ERR_FLG := 3;
	IF P区分 = '3'  and EXIT_FLG = 0 THEN
		UPDATE TR07 SET
			 "入出荷数量1"		= "入出荷数量1" + P数量1
			,"入出荷数量2"		= "入出荷数量2" + P数量2
			,"残高数量1"		= "残高数量1" - P数量1
			,"残高数量2"		= "残高数量2" - P数量2
		WHERE	"識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
		IF SQL%NOTFOUND THEN
			ERR := ERR_FLG;
		ELSE
			SELECT NVL("残高数量2",0),完了 INTO V数量,V完了  FROM TR07
				WHERE
				"識別A" = '7' 
				AND "識別C" = ' ' 
				AND "入力組織" = P組織 
				AND "伝票番号" = P受注番号 
				AND "登録行" = P行;
			IF V完了 is NULL THEN
				IF V数量 <= 0 THEN
					UPDATE TR07 SET "完了" = '1'
					WHERE
					"識別A" = '7' 
					AND "識別C" = ' ' 
					AND "入力組織" = P組織 
					AND "伝票番号" = P受注番号 
					AND "登録行" = P行;
				END IF;
			ELSE
				IF V数量 > 0 THEN
					UPDATE TR07 SET "完了" = NULL
					WHERE	
					"識別A" = '7' 
					AND "識別C" = ' ' 
					AND "入力組織" = P組織 
					AND "伝票番号" = P受注番号 
					AND "登録行" = P行;
				END IF;
			END IF;
		END IF;
	END IF;

	ERR_FLG := 4;
	IF ( P区分 = '4' or P区分 = '5' ) and EXIT_FLG = 0 THEN
		LOOP
			UPDATE SZ03 SET
				"現在現物数量2"		= "現在現物数量2"	+ V数量2,
				"更新日"		= sysdate,
				"在庫更新日"		= sysdate
			WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
			IF SQL%NOTFOUND THEN
				INSERT INTO SZ03 VALUES
				(V在庫組織,V大分類,V商品コード
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0
				,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0);	/* 142 */
			ELSE
				EXIT;
			END IF;
		END LOOP;
		LOOP
			UPDATE SZ04 SET
				"現在現物数量2"		= "現在現物数量2"	+ V数量2,
				"更新日"		= sysdate,
				"在庫更新日"		= sysdate
			WHERE	
				"組織" = V在庫組織 
				AND "大分類" = V大分類 
				AND "商品コード" = V商品コード 
				AND "場所区分" = NVL(V場所区分,' ') 
				AND "場所コード" = NVL(V場所コード,' ') 
				AND "単品コード" = NVL(V単品コード,' ');
			IF SQL%NOTFOUND THEN
				INSERT INTO SZ04 VALUES
				(V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' '),V大分類,V商品コード
				,NVL(V単品コード,' ')
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0
				,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
				,sysdate,NULL,sysdate,0);	/* 83 */
			ELSE
				EXIT;
			END IF;
		END LOOP;
	END IF;

	ERR_FLG := 6;
	IF ( P区分 = '6' ) and EXIT_FLG = 0  THEN 
	OPEN cur_TR01;

		FETCH	cur_TR01 INTO
			V認識;
		if cur_TR01%FOUND then
			V認識 := 2;
		else
			V認識 := 1;
		end if;	
	CLOSE cur_TR01;
	IF  V認識 = '1' THEN 

		LOOP

		
			UPDATE SZ03 SET
				"現在現物数量2"		= "現在現物数量2"	+ V数量2,
				"更新日"		= sysdate,
				"在庫更新日"		= sysdate
			WHERE	"組織" = V在庫組織 AND "大分類" = V大分類 AND "商品コード" = V商品コード;
			IF SQL%NOTFOUND THEN
				INSERT INTO SZ03 VALUES
				(V在庫組織,V大分類,V商品コード
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0
				,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0);	/* 142 */
			ELSE
				EXIT;
			END IF;
		END LOOP;
		LOOP
			UPDATE SZ04 SET
				"現在現物数量2"		= "現在現物数量2"	+ V数量2,
				"更新日"		= sysdate,
				"在庫更新日"		= sysdate
			WHERE	
				"組織" = V在庫組織 
				AND "大分類" = V大分類 
				AND "商品コード" = V商品コード 
				AND "場所区分" = NVL(V場所区分,' ') 
				AND "場所コード" = NVL(V場所コード,' ') 
				AND "単品コード" = NVL(V単品コード,' ');
			IF SQL%NOTFOUND THEN
				INSERT INTO SZ04 VALUES
				(V在庫組織,NVL(V場所区分,' '),NVL(V場所コード,' ')
				,V大分類,V商品コード,NVL(V単品コード,' ')
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
				,0,0,0,0,0,0,0,0
				,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
				,sysdate,NULL,sysdate,0);	/* 83 */
			ELSE
				EXIT;
			END IF;
		
		END LOOP;
	
	END IF;
	END IF;
/*=================================
   終了処理
=================================*/
DBMS_OUTPUT.PUT_LINE('JHUPD NORMAL END');

EXCEPTION
	WHEN OTHERS THEN
--		ERR_FLG := 1;
		ERR := ERR_FLG;
		DBMS_OUTPUT.PUT_LINE('ERROR AT JHUPD');
		ROLLBACK TO UPD_START;
END JHUPD;
/