一時表で加工データを作成

  仕様



  
CREATE GLOBAL TEMPORARY TABLE "社員加工元データ" 
   (	"社員コード" VARCHAR2(4), 
	"氏名" VARCHAR2(50), 
	"フリガナ" VARCHAR2(50), 
	"所属" VARCHAR2(4), 
	"性別" NUMBER(1,0), 
	"作成日" DATE, 
	"更新日" DATE, 
	"給与" NUMBER(10,2), 
	"手当" NUMBER(8,1), 
	"管理者" VARCHAR2(4), 
	"生年月日" DATE,
	PRIMARY KEY ("社員コード")
   ) ON COMMIT PRESERVE ROWS
  

● データ転送
社員マスタより、総務部(所属コード:1001) を除く社員を、給与+手当の多い順に10名転送する。
但し、10位と同じ金額が11位以以下に存在する場合は、その社員も転送する
※ 給与+手当 を 給与にセットする事

● データ加工
転送された社員で、部下を持つ社員の社員コードの頭1桁を '9' に変更。
転送された社員で、部下を持たない社員の社員コードの頭1桁を '5' に変更。
全ての対象データの更新日を SYSDATE で更新する

● データ転送
社員マスタと同一フォーマットである社員加工データ( 通常テーブル ) に全て転送する
( 社員加工データは、主キーを持た無い加工履歴 )

この処理は、最初のデータ転送のループ処理で、全ての処理を終えて社員加工データを
作成する事ができますが、コードが煩雑になり、メンテナンスが容易ではなくなります。

また、途中の経過を知る為にはデバッグ表示という効率の悪い対応をせまられます。
できるだけ処理の間違いを防ぐ為にも、中間データを作成するのは効果的です。



  PL/SQL コード



この仕様では、データ加工 のキーとして主キーも利用できますが、
このような処理では、必ずしも主キーが存在するとは限りませんので、ROWID を使用しています

  
PROCEDURE 加工処理

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	CURSOR cur_main IS
		SELECT * FROM 社員マスタ
			where 所属 != '1001'
			order by 給与+NVL(手当,0) desc;

	社員_REC cur_main%ROWTYPE;

	CURSOR cur_sub IS
		SELECT
			社員加工元データ.社員コード
			,ROWID
			FROM 社員加工元データ;

	WK_CNT NUMBER := 0; 
	WK_YEN NUMBER := 0; 
	WK_ROWID VARCHAR2(20); 

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('デバッグ:データ転送1開始');

	/**********************************************************/
	/* ● データ転送1 */
	/**********************************************************/
	OPEN cur_main;
	LOOP
		FETCH cur_main INTO 社員_REC;
		EXIT when cur_main%NOTFOUND;

		WK_CNT := WK_CNT + 1;
		if WK_CNT > 10 and 社員_REC.給与 + NVL(社員_REC.手当,0) != WK_YEN then
			EXIT;
		end if;

		insert into 社員加工元データ
		(社員コード,氏名,フリガナ,所属,性別,作成日,更新日,給与,手当,管理者,生年月日)
		values(
			社員_REC.社員コード
			,社員_REC.氏名
			,社員_REC.フリガナ
			,社員_REC.所属
			,社員_REC.性別
			,社員_REC.作成日
			,NULL
			,社員_REC.給与 + NVL(社員_REC.手当,0)
			,社員_REC.手当
			,社員_REC.管理者
			,社員_REC.生年月日
		);

		WK_YEN := 社員_REC.給与 + NVL(社員_REC.手当,0);

	END LOOP;
	CLOSE cur_main;

	DBMS_OUTPUT.PUT_LINE('デバッグ:データ加工開始');

	/**********************************************************/
	/* ● データ加工 */
	/**********************************************************/
	OPEN cur_sub;
	LOOP
		FETCH cur_sub INTO 社員_REC.社員コード,WK_ROWID;
		EXIT when cur_sub%NOTFOUND;

		select count(*) into WK_CNT from 社員マスタ
			where 管理者 = 社員_REC.社員コード;
		if WK_CNT != 0 then
			社員_REC.社員コード :=
				'9' || substr(社員_REC.社員コード,2,3);
		else
			社員_REC.社員コード :=
				'5' || substr(社員_REC.社員コード,2,3);
		end if;

		UPDATE 社員加工元データ
			set 社員コード = 社員_REC.社員コード
			where ROWID = WK_ROWID;

	END LOOP;
	CLOSE cur_sub;

	UPDATE 社員加工元データ
		set 更新日 = sysdate;

	DBMS_OUTPUT.PUT_LINE('デバッグ:データ転送2開始');

	/**********************************************************/
	/* ● データ転送2 */
	/**********************************************************/
	insert into 社員加工データ
		select * from 社員加工元データ;

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加




SQLの窓  天気  IT用語辞典
Yahoo!ニュース  マルチ辞書
PHP マニュアル  Google URL短縮 


フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ