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