SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

SQLでユーザ関数を活用2 - 複雑なSQLをデチューンしてみよう

 元のストアドプロシージャをデチューンしていきます。 

 前回作成したスカラー値関数(fn誤差配賦消費税額)をループして処理する形に修正します。長くなっていますが、この方が読みやすいかも知れません。

 ※ もちろん、実際には、明細単位、納品書単位、請求書単位にプロシージャを分割すべきです。

 スカラー値関数のみを直していますので、メインのプログラムに対する影響はゼロです。

 次回は、メインのプログラムを丸ごとループで処理した場合について書いてみます。

CREATE FUNCTION [dbo].[fn誤差配賦消費税額]
(@請求書NO      int
	, @納品書NO int
	, @行NO     int
	, @丸め単位 int
	, @丸め方法 int
	, @単価     int
	, @数量     int
	, @消費税率 money
	)
RETURNS int 
AS
BEGIN

	DECLARE @retValue int;
	DECLARE @明細単位消費税額 int;
	DECLARE @伝票消費税額 int;
	DECLARE @差額 int;
	DECLARE @配賦額 int;
	DECLARE @Cursor納品書NO int;
	DECLARE @Cursor行NO int;
	DECLARE @カウンタ int;
	
	-- @丸め単位 = 2,3以外は明細単位と見なす。
	SET @retValue = dbo.fn丸め(@単価 * @数量 * @消費税率, @丸め方法);

	IF  @丸め単位 = 2
	  BEGIN
		-- 納品書単位の消費税を計算する。
		SELECT 
			@明細単位消費税額 = SUM(dbo.fn丸め(um.単価 * um.数量 * @消費税率, @丸め方法))
			, @伝票消費税額 = dbo.fn丸め(SUM(um.単価 * um.数量) * @消費税率, @丸め方法)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.納品書NO = @納品書NO;
		
		SET @差額 = @伝票消費税額 - @明細単位消費税額;
		
		-- 差額がないときは、明細単位の消費税額を返す。
		IF @差額 = 0
		  BEGIN 
			RETURN @retValue;
		END; 

		-- 差額があるときは、明細の小計から、対象の明細より補正対象か確認する。
		DECLARE 納品書cursor CURSOR FOR
			SELECT 
				um.行NO
			FROM 
				売上 uh
				INNER JOIN 売上明細 um
					ON uh.売上ID = um.売上ID
			WHERE 
				uh.納品書NO = @納品書NO
				AND um.単価 * um.数量 >= @単価 * @数量
			ORDER BY 
				um.単価 * um.数量 DESC, um.行NO;
	
		OPEN 納品書cursor;

		SET @カウンタ = 0;
		SET @配賦額 = 0;
		
		FETCH NEXT FROM 納品書cursor
		INTO @Cursor行NO;

		WHILE @@FETCH_STATUS = 0
		  BEGIN
			SET @カウンタ = @カウンタ + 1;
			IF @カウンタ <= ABS(@差額)
			  BEGIN
				IF @Cursor行NO = @行NO
				  BEGIN
					SET @配賦額 = SIGN(@差額);
					BREAK;
				END;
			END;
			
			FETCH NEXT FROM 納品書cursor
			INTO @Cursor行NO;
			
		END

		CLOSE 納品書cursor;
		DEALLOCATE 納品書cursor;
		
		SET @retValue = @retValue + @配賦額;
		
	END;
	ELSE IF  @丸め単位 = 3
	  BEGIN
		-- 請求書単位の消費税を計算する。
		SELECT 
			@明細単位消費税額 = SUM(dbo.fn丸め(um.単価 * um.数量 * @消費税率, @丸め方法))
			, @伝票消費税額 = dbo.fn丸め(SUM(um.単価 * um.数量) * @消費税率, @丸め方法)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.請求書NO = @請求書NO;
		
		SET @差額 = @伝票消費税額 - @明細単位消費税額;
		
		IF @差額 = 0
		  BEGIN 
			RETURN @retValue;
		END; 
		
		-- 差額があるときは、明細の小計から、対象の明細より補正対象か確認する。
		DECLARE 請求書cursor CURSOR FOR
			SELECT 
				uh.納品書NO
				, um.行NO
			FROM 
				売上 uh
				INNER JOIN 売上明細 um
					ON uh.売上ID = um.売上ID
			WHERE 
				uh.請求書NO = @請求書NO
				AND um.単価 * um.数量 >= @単価 * @数量
			ORDER BY 
				um.単価 * um.数量 DESC, uh.納品書NO, um.行NO;
	
		OPEN 請求書cursor;

		SET @カウンタ = 0;
		SET @配賦額 = 0;
		
		FETCH NEXT FROM 請求書cursor
		INTO @Cursor納品書NO, @Cursor行NO;

		WHILE @@FETCH_STATUS = 0
		  BEGIN
			SET @カウンタ = @カウンタ + 1;
			IF @カウンタ <= ABS(@差額)
			  BEGIN
				IF @Cursor納品書NO = @納品書NO AND @Cursor行NO = @行NO
				  BEGIN
					SET @配賦額 = SIGN(@差額);
					BREAK;
				END;
			END;
			
			FETCH NEXT FROM 請求書cursor
			INTO @Cursor納品書NO, @Cursor行NO;
			
		END

		CLOSE 請求書cursor;
		DEALLOCATE 請求書cursor;
		
		SET @retValue = @retValue + @配賦額;
		
	END;
	
	RETURN @retValue;

END
GO