SELECT INTO によるデータの取得

  対象テーブル



列名型名最大桁精度スケールNULL主キー型説明
1得意先コードVARCHAR24N1文字列値
2得意先名NVARCHAR2100バイナリ値
3得意先区分VARCHAR21文字列値
4担当者VARCHAR24文字列値
5郵便番号VARCHAR27文字列値
6住所1NVARCHAR2200バイナリ値
7住所2NVARCHAR2200バイナリ値
8作成日DATE日付/時刻スタンプ
9更新日DATE日付/時刻スタンプ
10締日NUMBER20固定精度およびスケールの正確な数値
11締日区分NUMBER10固定精度およびスケールの正確な数値
12支払日NUMBER20固定精度およびスケールの正確な数値


10) 締日 NUMBER( 2, 0 )
11) 締日区分 NUMBER( 1, 0 )
12) 支払日 NUMBER( 2, 0 )

10) は、1〜30
11) は、0〜
12) は、1〜30

20、1、10 ならば、20日締めの翌月10日払い
2008/01/05 -> 2008/02/10
2008/01/21 -> 2008/03/10

※ 締日・支払日は5の倍数という前提( 但し 30 は、月末で、30日締め・30日支払いは存在しない



  任意の日付と、得意先コードより、得意先側の支払日を取得する



  
FUNCTION GET_PAY
(
	PM_TCODE IN VARCHAR2
	,PM_UDATE IN DATE
)
RETURN DATE

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_締日 得意先マスタ.締日%TYPE;
	WK_締日区分 得意先マスタ.締日区分%TYPE;
	WK_支払日 得意先マスタ.支払日%TYPE;
	WK_DATE DATE;

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('引数:'||PM_TCODE);
	DBMS_OUTPUT.PUT_LINE('引数:'||PM_UDATE);

	BEGIN
		SELECT
			締日
			,締日区分
			,支払日
		INTO
			WK_締日
			,WK_締日区分
			,WK_支払日
		FROM 得意先マスタ
		WHERE
			得意先コード = PM_TCODE;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			NULL;
		WHEN TOO_MANY_ROWS THEN
			NULL;
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
	END;

	DBMS_OUTPUT.PUT_LINE('締日:'||WK_締日);
	DBMS_OUTPUT.PUT_LINE('締日区分:'||WK_締日区分);
	DBMS_OUTPUT.PUT_LINE('支払日:'||WK_支払日);

	IF WK_締日 = 30 THEN
		WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分 );
	ELSE
		IF EXTRACT(DAY from PM_UDATE) <= WK_締日 then
			WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分 );
		ELSE
			WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分+1 );
		END IF;
	END IF;

	DBMS_OUTPUT.PUT_LINE('加工前支払日:'||WK_DATE);

	IF WK_支払日 = 30 then
		WK_DATE := LAST_DAY( WK_DATE );
	ELSE
		WK_DATE := WK_DATE - EXTRACT(day from WK_DATE)
			+ WK_支払日;
	END IF;

	RETURN WK_DATE;

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





Oracle の日付関数のサンプル



  引数を追加して、伝票データより伝票単位の支払日を求めてリストを出力する

↓実行SQL
  
select
	GET_PAY2(
		max(取引先コード),
		max(取引日付),
		sum(金額),
		伝票番号
	) as 支払日
from 取引データ
group by 伝票番号
  

  
FUNCTION GET_PAY2
(
	PM_TCODE IN VARCHAR2
	,PM_UDATE IN DATE
	,PM_KIN IN NUMBER
	,PM_DEN IN NUMBER
)
RETURN DATE

/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_締日 得意先マスタ.締日%TYPE;
	WK_締日区分 得意先マスタ.締日区分%TYPE;
	WK_支払日 得意先マスタ.支払日%TYPE;
	WK_DATE DATE;
	FILEHANDLE	UTL_FILE.FILE_TYPE;

/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	DBMS_OUTPUT.PUT_LINE('引数:'||PM_TCODE);
	DBMS_OUTPUT.PUT_LINE('引数:'||PM_UDATE);

	BEGIN
		SELECT
			締日
			,締日区分
			,支払日
		INTO
			WK_締日
			,WK_締日区分
			,WK_支払日
		FROM 得意先マスタ
		WHERE
			得意先コード = PM_TCODE;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			NULL;
		WHEN TOO_MANY_ROWS THEN
			NULL;
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
	END;

	DBMS_OUTPUT.PUT_LINE('締日:'||WK_締日);
	DBMS_OUTPUT.PUT_LINE('締日区分:'||WK_締日区分);
	DBMS_OUTPUT.PUT_LINE('支払日:'||WK_支払日);

	IF WK_締日 = 30 THEN
		WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分 );
	ELSE
		IF EXTRACT(DAY from PM_UDATE) <= WK_締日 then
			WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分 );
		ELSE
			WK_DATE := ADD_MONTHS( PM_UDATE, WK_締日区分+1 );
		END IF;
	END IF;

	DBMS_OUTPUT.PUT_LINE('加工前支払日:'||WK_DATE);

	IF WK_支払日 = 30 then
		WK_DATE := LAST_DAY( WK_DATE );
	ELSE
		WK_DATE := WK_DATE - EXTRACT(day from WK_DATE)
			+ WK_支払日;
	END IF;

	-- 書き込み
	FILEHANDLE := UTL_FILE.FOPEN( 'LOGDIR', 'GET_PAY', 'a' );
	UTL_FILE.PUT_LINE( FILEHANDLE, 
		PM_TCODE || ',' ||
		TO_CHAR(WK_DATE) || ',' ||
		TO_CHAR(PM_KIN)  || ',' ||
		TO_CHAR(PM_DEN) 
	);
	UTL_FILE.FCLOSE( FILEHANDLE );

	RETURN WK_DATE;

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



  入金予定テーブルにデータを登録する

列名型名最大桁精度スケール
1得意先コードVARCHAR24
2支払日DATE
3伝票合計金額NUMBER100
4伝票番号NUMBER100

  
PROCEDURE 入金予定データ作成
/**********************************************************/
/* 変数の定義 */
/**********************************************************/
AS
	WK_CODE VARCHAR2(4);
	WK_DATE DATE;
	WK_KIN NUMBER(10,0);
	WK_DEN NUMBER(10,0);

	CURSOR cur_main IS
	select
		max(取引先コード) as 取引先コード
		,max(取引日付) as 取引日付
		,sum(金額) as 金額
		,伝票番号
	from 取引データ
	group by 伝票番号;

BEGIN

delete from 入金予定;

OPEN cur_main;

LOOP
	FETCH cur_main INTO
		WK_CODE,
		WK_DATE,
		WK_KIN,
		WK_DEN;
	if cur_main%NOTFOUND then
		EXIT;
	end if;

	WK_DATE := GET_PAY(WK_CODE,WK_DATE);

	insert into 入金予定 values(
		WK_CODE,
		WK_DATE,
		WK_KIN,
		WK_DEN
	);

END LOOP;

CLOSE cur_main;

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










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





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

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ