PL/SQL : リアルサンプル1

  SAMPLE01 



CREATE or REPLACE PROCEDURE SAMPLE01
        (
         PM_BLOCKSHO	IN      NUMBER
        ,PM_DATE	IN      DATE
        )
        AS

/*===========================================================================
   変数
===========================================================================*/
	source_cursor		integer;
	destination_cursor	integer;
	rows_processed		integer;

	WK_DATE			DATE;
	WK_SELECT	VARCHAR2(1024);			-- select 文
	WK_INSERT	VARCHAR2(1024);			-- insert 文

	売上通常		NUMBER(10,0);		-- (E) 1
	売上通常消費税		NUMBER(10,0);		-- (F) 1
	売上中間請求		NUMBER(10,0);		-- (A) 2
	売上中間請求消費税	NUMBER(10,0);		-- (B) 2
	売上最終請求		NUMBER(10,0);		-- (I) 3
	売上最終請求消費税	NUMBER(10,0);		-- (J) 3
	売上値引		NUMBER(10,0);		-- (C) 4
	売上値引消費税		NUMBER(10,0);		-- (D) 4
	売上修正		NUMBER(10,0);		-- (G) 5
	売上修正消費税		NUMBER(10,0);		-- (H) 5

	ﾌﾞﾛｯｸ小ｺｰﾄﾞ		NUMBER(4,0);
	取引先ｺｰﾄﾞ		NUMBER(5,0);
	取引先支店ｺｰﾄﾞ		NUMBER(2,0);
	ｿｰﾄ区分			NUMBER(1,0);
	金額			NUMBER(10,0);
	消費税			NUMBER(10,0);

	SVﾌﾞﾛｯｸ小ｺｰﾄﾞ		NUMBER(4,0);
	SV取引先ｺｰﾄﾞ		NUMBER(5,0);
	SV取引先支店ｺｰﾄﾞ	NUMBER(2,0);

	事業部			NUMBER(2,0);
	部			NUMBER(1,0);
	課			NUMBER(1,0);
	前月請求額		NUMBER(10,0);
	入金金額		NUMBER(10,0);

	最新請求年月日		DATE;

/*===========================================================================
   カーソル
===========================================================================*/

/*===========================================================================
   ローカル関数
===========================================================================*/
/*=======================================================
   最新請求締年月日取得
   IN           PM_DATE         : 締年月日
   RETURN       WK_DATE         : 最新請求締年月日
=====================================================*/
FUNCTION GET_DATE_SIME (PM_DATE IN DATE) RETURN DATE
        AS
        WK_DATE         DATE;
BEGIN
        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        WK_DATE         := WK_DATE + 1;
        RETURN          WK_DATE;
END GET_DATE_SIME;

/*=======================================================
   請求明細テーブル名取得
   IN		PM_DATE         : 締年月日
   RETURN	WK_TBLNAME      : 目的テーブル名
=====================================================*/
FUNCTION GET_SEIDT_NAME (PM_DATE IN DATE) RETURN VARCHAR2
	AS
	WK_DATE         DATE;
        WK_DAY          VARCHAR2(30);
        WK_TBLNAME	VARCHAR2(30);
BEGIN
        WK_DATE := LAST_DAY(PM_DATE);
        if WK_DATE = PM_DATE then
                WK_TBLNAME := 'KF_SEIDT99';
        else
                WK_DAY := TO_CHAR( PM_DATE, 'DD' );
                WK_TBLNAME := 'KF_SEIDT' || WK_DAY;
        end if;
	RETURN	WK_TBLNAME;
dbms_output.put_line( WK_TBLNAME );
END GET_SEIDT_NAME;


/*=======================================================
   長い文字列出力
=====================================================*/
PROCEDURE PUT_LSTR (PM_STR IN VARCHAR2)
        AS
BEGIN
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,1,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,81,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,161,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,241,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,321,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,401,80));
	DBMS_OUTPUT.PUT_LINE(substr(PM_STR,481,80));
END PUT_LSTR;

/*=======================================================
   請求ファイルの前レコード当月請求額を取得
=====================================================*/
FUNCTION GET_BACK_SEIKYU
	(
	PM_BLOCKSHO IN NUMBER
	,PM_TORI IN NUMBER
	,PM_TORI_ST IN NUMBER
	)
	RETURN NUMBER
        AS
	WK_KINGAKU	NUMBER(10,0);
        CURSOR KF_SEI_cur is 
                select
                        KIN_TOSEI
                from
                        KF_SEI
		where
			CD_BLOCKSHO = PM_BLOCKSHO and
			CD_TORI = PM_TORI and
			CD_TORI_ST = PM_TORI_ST
		order by 
			YMD_SIME_SEI DESC;

BEGIN
	BEGIN
	        OPEN    KF_SEI_cur;
        	FETCH	KF_SEI_cur	INTO	WK_KINGAKU;
		if KF_SEI_cur%NOTFOUND then
			WK_KINGAKU := 0;
		end if;

	EXCEPTION
	        WHEN OTHERS THEN
			WK_KINGAKU := 0;

	END;	

	RETURN WK_KINGAKU;
END GET_BACK_SEIKYU;

/*=======================================================
   回収Ｆより入金額を取得
=====================================================*/
FUNCTION GET_KAISYU
	(
	PM_BLOCKSHO IN NUMBER
	,PM_TORI IN NUMBER
	,PM_TORI_ST IN NUMBER
	)
	RETURN NUMBER
        AS
	WK_KINGAKU1	NUMBER(10,0);
	WK_KINGAKU2	NUMBER(10,0);
	WK_NYUKIN	NUMBER(10,0);
BEGIN

	WK_KINGAKU1 := 0;
	WK_KINGAKU2 := 0;

	select
		nvl(sum(KIN_KAI),0)
	into
		WK_KINGAKU1
	from
		KF_TOKU_KAI
	where
		CD_BLOCKSHO = PM_BLOCKSHO and
		CD_TORI	= PM_TORI and
		CD_TORI_ST = PM_TORI_ST and
		NO_KIN = 1 and
		NO_SYU not in (23,26) and
		KIN_KAI is not NULL;

	select
		nvl(sum(KIN_KAI),0)
	into
		WK_KINGAKU2
	from
		KF_TOKU_KAI
	where
		CD_BLOCKSHO = PM_BLOCKSHO and
		CD_TORI	= PM_TORI and
		CD_TORI_ST = PM_TORI_ST and
		NO_KIN = 2 and
		NO_SYU not in (23,26) and
		YMD_KAI between WK_DATE and PM_DATE and
		KIN_KAI is not NULL;

	WK_NYUKIN := WK_KINGAKU1 - WK_KINGAKU2;

	RETURN WK_NYUKIN;

END GET_KAISYU;

/*=====================================================
   取引サブＭより部門コードを取得して
   最新請求締年月日を更新し、
   請求締日変更ファイルより該当レコードを削除する
=====================================================*/
PROCEDURE GET_TORISUB
        (
         PM_BLOCKSHO	IN      NUMBER
        ,PM_TORI        IN      NUMBER
        ,PM_TORI_ST	IN	NUMBER
        ,PM_DATE	IN	DATE
        ,PM_JIGYO	IN OUT  NUMBER
        ,PM_BUMON	IN OUT  NUMBER
        ,PM_KA		IN OUT  NUMBER
        )
        AS

	WK_KBN_JITA	NUMBER(1,0);
	WK_SIME		DATE;
BEGIN
	BEGIN
		select					-- 取引サブＭより部門コードを取得
			CD_JIGYO,
			CD_BUMON,
			CD_KA,
			YMD_SEI_NEW
		into
			PM_JIGYO,
			PM_BUMON,
			PM_KA,
			最新請求年月日
		from
			MM_TORISUB
		where
			CD_TORI = PM_TORI and
			CD_TORI_ST = PM_TORI_ST;

		if	最新請求年月日 is NULL then
		        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        		WK_DATE         := WK_DATE + 1;
		else
        		WK_DATE         := 最新請求年月日 + 1;
		end if;

--		update
--			MM_TORISUB
--		set
--			YMD_SEI_NEW = PM_DATE
--		where
--			CD_TORI = PM_TORI and
--			CD_TORI_ST = PM_TORI_ST;

		select
			KBN_JITA
		into	
			WK_KBN_JITA
		from
			MM_TORI
		where
			CD_TORI	= PM_TORI and
			CD_TORI_ST = PM_TORI_ST;

		if	WK_KBN_JITA = 0 then
                        BEGIN
				select
					YMD_HSIME_SEI
				into
					WK_SIME
				from
					KF_HSIME_SEI
				where
					CD_BLOCKSHO	= PM_BLOCKSHO and
					CD_TORI		= PM_TORI and
					CD_TORI_ST	= PM_TORI_ST;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN NULL;
			END;
			if SQL%FOUND then
				if WK_SIME = PM_DATE then
					delete
					from
						KF_HSIME_SEI
					where
						CD_BLOCKSHO	= PM_BLOCKSHO and
						CD_TORI		= PM_TORI and
						CD_TORI_ST	= PM_TORI_ST;
				end if;
			end if;
		end if;

        EXCEPTION
                WHEN NO_DATA_FOUND THEN
                        PM_JIGYO := NULL;             
                        PM_BUMON := NULL;             
                        PM_KA := NULL;             
		        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        		WK_DATE         := WK_DATE + 1;
                WHEN OTHERS THEN
                        PM_JIGYO := NULL;             
                        PM_BUMON := NULL;             
                        PM_KA := NULL;             
		        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        		WK_DATE         := WK_DATE + 1;
        END;

END GET_TORISUB;

/*===========================================================================
   単純サブルーチン
===========================================================================*/
/*=====================================================
   ワーク変数の初期化
=====================================================*/
PROCEDURE ZERO_CLEAR
	AS
BEGIN

	売上通常		:= 0;
	売上通常消費税		:= 0;
	売上中間請求		:= 0;
	売上中間請求消費税	:= 0;	
	売上最終請求		:= 0;
	売上最終請求消費税	:= 0;
	売上値引		:= 0;
	売上値引消費税		:= 0;
	売上修正		:= 0;
	売上修正消費税		:= 0;

END ZERO_CLEAR;

/*=====================================================
   データの登録
=====================================================*/
PROCEDURE INSERT_DATA
	AS
BEGIN

	dbms_sql.bind_variable(destination_cursor, 'CD_BLOCKSHO', PM_BLOCKSHO);
	GET_TORISUB(PM_BLOCKSHO,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ,PM_DATE,事業部,部,課);
	dbms_sql.bind_variable(destination_cursor, 'CD_JIGYO', 事業部);
	dbms_sql.bind_variable(destination_cursor, 'CD_BUMON', 部);
	dbms_sql.bind_variable(destination_cursor, 'CD_KA', 課);
	dbms_sql.bind_variable(destination_cursor, 'CD_TORI', SV取引先ｺｰﾄﾞ);
	dbms_sql.bind_variable(destination_cursor, 'CD_TORI_ST', SV取引先支店ｺｰﾄﾞ);
	dbms_sql.bind_variable(destination_cursor, 'YMD_FROM_SEI', WK_DATE);
	dbms_sql.bind_variable(destination_cursor, 'YMD_SIME_SEI', PM_DATE);
	前月請求額 := GET_BACK_SEIKYU(SVﾌﾞﾛｯｸ小ｺｰﾄﾞ,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ);
	dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSEI', 前月請求額);
	入金金額 := GET_KAISYU(SVﾌﾞﾛｯｸ小ｺｰﾄﾞ,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ);
	dbms_sql.bind_variable(destination_cursor, 'KIN_NYUKIN', 入金金額);
	dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSYU', 売上修正+売上修正消費税);
	dbms_sql.bind_variable(destination_cursor, 'KIN_URIAGE', 売上通常+売上最終請求+売上値引);
	dbms_sql.bind_variable(destination_cursor, 'KIN_TYUKAN', 売上中間請求);
	dbms_sql.bind_variable(destination_cursor, 'ZEI_URIAGE', 売上中間請求消費税
		+売上値引消費税
		+売上通常消費税
		+売上最終請求消費税);
	dbms_sql.bind_variable(destination_cursor, 'KIN_TOSEI', 前月請求額
		-入金金額
		+売上通常+売上最終請求+売上値引
		+売上中間請求
		+売上中間請求消費税+売上値引消費税+売上通常消費税+売上最終請求消費税);

--	dbms_sql.bind_variable(destination_cursor, 'YMD_UPD', sysdate);

	rows_processed := dbms_sql.execute(destination_cursor);

END INSERT_DATA;

/*===========================================================================
   主処理
===========================================================================*/
BEGIN

/*=====================================================
   初期処理
=====================================================*/
--	WK_DATE	:= GET_DATE_SIME(PM_DATE);			-- 今回の締年月日よりfrom年月日を取得


/*=====================================================
   SELECT文作成(請求明細**読込用)
   金額と数量をサマリ
=====================================================*/


	WK_SELECT	:=
	'select 
		CD_BLOCKSHO,
		CD_TORI,
		CD_TORI_ST,
		sum(KIN_URIAGE),
		sum(ZEI_URIAGE),
		KBN_SORT
	from ' || GET_SEIDT_NAME(PM_DATE) || 
	' group by
		CD_BLOCKSHO,
		CD_TORI,
		CD_TORI_ST,
		KBN_SORT';
/*
	'select 
		CD_BLOCKSHO,
		CD_TORI,
		CD_TORI_ST,
		sum(KIN_URIAGE),
		sum(ZEI_URIAGE),
		KBN_SORT
	from ' || GET_SEIDT_NAME(PM_DATE) || 
	' where
		CD_BLOCKSHO = :PM_BLOCKSHO and
		YMD_URI_KJ between :WK_DATE and :PM_DATE
	group by
		CD_BLOCKSHO,
		CD_TORI,
		CD_TORI_ST,
		KBN_SORT';

*/

/*=====================================================
   入力定義
=====================================================*/
	source_cursor := dbms_sql.open_cursor;
	dbms_sql.parse(source_cursor, WK_SELECT, 2 );
	dbms_sql.define_column(source_cursor, 1, ﾌﾞﾛｯｸ小ｺｰﾄﾞ);
	dbms_sql.define_column(source_cursor, 2, 取引先ｺｰﾄﾞ);
	dbms_sql.define_column(source_cursor, 3, 取引先支店ｺｰﾄﾞ);
	dbms_sql.define_column(source_cursor, 4, 金額);
	dbms_sql.define_column(source_cursor, 5, 消費税);
	dbms_sql.define_column(source_cursor, 6, ｿｰﾄ区分);

--	dbms_sql.bind_variable(source_cursor, 'PM_BLOCKSHO', PM_BLOCKSHO);
--	dbms_sql.bind_variable( source_cursor, 'WK_DATE', WK_DATE );
--	dbms_sql.bind_variable( source_cursor, 'PM_DATE', PM_DATE );

	rows_processed := dbms_sql.execute(source_cursor);


/*=====================================================
   出力定義(請求Ｆ)
=====================================================*/
	destination_cursor := dbms_sql.open_cursor;
	WK_INSERT	:=
	'insert into KF_SEI (
		CD_BLOCKSHO,
		CD_JIGYO,
		CD_BUMON,
		CD_KA,
		CD_TORI,
		CD_TORI_ST,
		YMD_FROM_SEI,
		YMD_SIME_SEI,
		KIN_ZENSEI,
		KIN_NYUKIN,
		KIN_ZENSYU,
		KIN_URIAGE,
		KIN_TYUKAN,
		ZEI_URIAGE,
		KIN_TOSEI
--		YMD_UPD
		)
	 values (
		:CD_BLOCKSHO,
		:CD_JIGYO,
		:CD_BUMON,
		:CD_KA,
		:CD_TORI,
		:CD_TORI_ST,
		:YMD_FROM_SEI,
		:YMD_SIME_SEI,
		:KIN_ZENSEI,
		:KIN_NYUKIN,
		:KIN_ZENSYU,
		:KIN_URIAGE,
		:KIN_TYUKAN,
		:ZEI_URIAGE,
		:KIN_TOSEI
--		:YMD_UPD
		)';

	dbms_sql.parse( destination_cursor, WK_INSERT, 2 );

	SVﾌﾞﾛｯｸ小ｺｰﾄﾞ		:= -1;
	ZERO_CLEAR;


/*=====================================================
   入出力
=====================================================*/
	loop

		if dbms_sql.fetch_rows(source_cursor) > 0 then
			dbms_sql.column_value(source_cursor, 1, ﾌﾞﾛｯｸ小ｺｰﾄﾞ);
			dbms_sql.column_value(source_cursor, 2, 取引先ｺｰﾄﾞ);
			dbms_sql.column_value(source_cursor, 3, 取引先支店ｺｰﾄﾞ);
			dbms_sql.column_value(source_cursor, 4, 金額);
			dbms_sql.column_value(source_cursor, 5, 消費税);
			dbms_sql.column_value(source_cursor, 6, ｿｰﾄ区分);
			DBMS_OUTPUT.PUT_LINE(
				'取引先ｺｰﾄﾞ:' || TO_CHAR(取引先ｺｰﾄﾞ) ||
				'  取引先支店ｺｰﾄﾞ:' || TO_CHAR(取引先支店ｺｰﾄﾞ) ||
				'  ｿｰﾄ区分:' || TO_CHAR(ｿｰﾄ区分) ||
				'  金額:' || TO_CHAR(金額) ||
				'  消費税:' || TO_CHAR(消費税)
			);


			if SVﾌﾞﾛｯｸ小ｺｰﾄﾞ = -1 then
				NULL;
			else
				if	SVﾌﾞﾛｯｸ小ｺｰﾄﾞ != ﾌﾞﾛｯｸ小ｺｰﾄﾞ or
					SV取引先ｺｰﾄﾞ != 取引先ｺｰﾄﾞ or
					SV取引先支店ｺｰﾄﾞ != 取引先支店ｺｰﾄﾞ then

					BEGIN

						INSERT_DATA;

					EXCEPTION
						WHEN DUP_VAL_ON_INDEX THEN
							delete from KF_SEI where
								CD_BLOCKSHO = PM_BLOCKSHO and
								CD_JIGYO = 事業部 and
								CD_BUMON = 部 and
								CD_KA = 課 and
								CD_TORI = SV取引先ｺｰﾄﾞ and
								CD_TORI_ST = SV取引先支店ｺｰﾄﾞ and
								YMD_SIME_SEI = PM_DATE;

								INSERT_DATA;
						WHEN OTHERS THEN

							NULL;

					END;

					ZERO_CLEAR;

				end if;

			end if;

			if	ｿｰﾄ区分 = 1 then
				売上通常		:= 金額;
				売上通常消費税		:= 消費税;
			elsif	ｿｰﾄ区分 = 2 then
				売上中間請求		:= 金額;
				売上中間請求消費税	:= 消費税;	
			elsif	ｿｰﾄ区分 = 3 then
				売上最終請求		:= 金額;
				売上最終請求消費税	:= 消費税;
			elsif	ｿｰﾄ区分 = 4 then
				売上値引		:= 金額;
				売上値引消費税		:= 消費税;
			elsif	ｿｰﾄ区分 = 5 then
				売上修正		:= 金額;
				売上修正消費税		:= 消費税;
			end if;

			SVﾌﾞﾛｯｸ小ｺｰﾄﾞ		:= ﾌﾞﾛｯｸ小ｺｰﾄﾞ;
			SV取引先ｺｰﾄﾞ		:= 取引先ｺｰﾄﾞ;
			SV取引先支店ｺｰﾄﾞ	:= 取引先支店ｺｰﾄﾞ;

		else

			if SVﾌﾞﾛｯｸ小ｺｰﾄﾞ = -1 then
				NULL;
			else
				BEGIN
					INSERT_DATA;

				EXCEPTION
					WHEN DUP_VAL_ON_INDEX THEN
						delete from KF_SEI where
							CD_BLOCKSHO = PM_BLOCKSHO and
							CD_JIGYO = 事業部 and
							CD_BUMON = 部 and
							CD_KA = 課 and
							CD_TORI = SV取引先ｺｰﾄﾞ and
							CD_TORI_ST = SV取引先支店ｺｰﾄﾞ and
							YMD_SIME_SEI = PM_DATE;

							INSERT_DATA;

					WHEN OTHERS THEN
						NULL;
				END;
			end if;

			exit;

		end if;

	end loop;


/*=====================================================
   終了処理
=====================================================*/
	commit;
	dbms_sql.close_cursor(source_cursor);
	dbms_sql.close_cursor(destination_cursor);
dbms_output.put_line( 'NEC7' );

/*=====================================================
   例外処理
=====================================================*/

EXCEPTION
	WHEN OTHERS THEN
		if dbms_sql.is_open(source_cursor) then
			dbms_sql.close_cursor(source_cursor);
		end if;
		if dbms_sql.is_open(destination_cursor) then
			dbms_sql.close_cursor(destination_cursor);
		end if;


END SAMPLE01;
/










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




フリーフォントWEBサービス
フリーフォントで簡単ロゴ作成
アイコン・はんこ画像作成
フリーフォントで横書きロゴ作成
フリーフォントで縦書きロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し素材作成
フリーフォントで PDF 作成
デュアルフォント
書体見本
フリーフォント配布サイト
キーボード画像作成
フリーフォント ギャラリー
イラストACよりフリーダウンロード
UTF-8文字Tool
SQLの窓WEBサービス
リアルタイムHTML
テキスト変換
ストリートビューとMAPのコード
SyntaxHighlighter
Shadowbox 埋め込みコード作成

SQLの窓フリーソフト
SQLの窓 Build C++
販売管理B
「送る」に入れる簡易ダンプ
WMI Query
コンピュータアイコンメニュー
ドメイン内検索

素材
足成
Image * After
ぱくたそ
イラストAC
イラスト素材サイト

一般WEBツールリンク
WEB アーカイブ
疑わしいファイルや URL を分析する
HTML整形ツール
JSONLint
ttf to woff converter
Run Code
Twitter Card validator
Google URL Shortener
C#<->VB.NET

SQLの窓
logical　error
GINPRO
WAHA land
職人的フリーソフトの使い方
WEB 職人
ソース掲示板

フリーソフト
7-Zip
FileZilla
PrimoPDF(英語版)
GifCam
GIMP Portable
Classic Shell

JSライブラリ
jQuery
Lightbox
Shadowbox.js
The Google Hosted Libraries
cdnjs
Three.js