ストアドプロシージャでループ処理 - 複雑なSQLをデチューンしてみよう

　前回作成したスカラー値関数（fn誤差配賦消費税額）をループして処理する形にのものを作成しましたが、今回はメインのプロシージャを修正することにします。

　都合、4種類書いてみましたが、全角の利用でイライラしながらも全部で6時間ぐらいでした。やってみたら単純な工数は大差がつかなかったのですけれど、コードが長くなればなるほどドキュメントは必要になりますし、バグの可能性も高くなりますから、実際の工数はもっと大きな差がつくでしょう。

　当たり前の話ですが、いずれの方針を採っても、スタブとユーザインターフェース側との関係は変わりません。しかし、ループのパターンで作っていて、出力項目などが変わったときのインパクトは大きいです。（自分で確認してみてください）

　つまり、出力項目をスタブで完璧に確定しておくことが、全体の工数削減に繋がります。システム開発はユーザインターフェースから作るべきなのです。

　今回書き換える前のストアドプロシージャのスタブは以下の通りです。

	CREATE PROCEDURE dbo.TEST_PROC
	(
		@pFrom datetime	-- 期間開始日
		, @pTo datetime	-- 期間終了日
	)
	AS

		SET NOCOUNT ON;

		-- 本番時は以下のSQLを修正し、このコメントを削除する。
		SELECT * 
		FROM xTEST_PROC_VIEW
		WHERE 
			1 = 1
		;

GO

　本来はセッションIDなどでマルチユーザ対策が必要になるのですが、今回はマルチユーザ対策については省略させていただきます。

　※ もちろん、実際には、明細単位、納品書単位、請求書単位にプロシージャを分割してくださいね。

	CREATE PROCEDURE [dbo].[TEST_PROC]
	(
		@pFrom datetime	-- 期間開始日
		, @pTo datetime	-- 期間終了日
	)
	AS

		SET NOCOUNT ON;
		
		CREATE TABLE #WORK
		(
			請求書NO		int
			, 納品書NO		int
			, 行NO			int
			, 売上日		datetime
			, 得意先ID		int
			, 商品ID		int
			, 商品区分		int
			, 単価			int
			, 数量			int
			, 消費税額		int
		);
		
		/* 明細単位の処理 */
		INSERT INTO #WORK
			(請求書NO, 納品書NO, 行NO, 売上日, 得意先ID
			, 商品ID, 商品区分, 単価, 数量, 消費税額)
		SELECT 
			uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID
			, um.商品ID, p.商品区分, um.単価, um.数量
			, dbo.fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) AS 消費税額 
		FROM
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
			INNER JOIN 得意先 t
				ON uh.得意先ID = t.得意先ID
			INNER JOIN 商品 p
				ON um.商品ID = p.商品ID
		WHERE
			uh.売上日 BETWEEN @pFrom AND @pTo   
			-- 元々、明細単位の丸めの場合はそのまま追加する。
			AND t.丸め方法 = 1;

		--ループ処理で使用する 
		DECLARE @明細単位消費税額	int;
		DECLARE @伝票消費税額 		int;
		DECLARE @差額 			int;
		DECLARE @配賦額 		int;
		DECLARE @カウンタ 		int;
		DECLARE @before請求書NO		int;
		DECLARE @before納品書NO		int;
		
		-- カーソルの戻りを入れる
		DECLARE @請求書NO		int;
		DECLARE @納品書NO		int;
		DECLARE @行NO			int;
		DECLARE @売上日			datetime;
		DECLARE @得意先ID		int;
		DECLARE @商品ID			int;
		DECLARE @商品区分		int;
		DECLARE @単価			int;
		DECLARE @数量			int;
		DECLARE @消費税額		int;

		/* 納品書単位の処理 */
		DECLARE 納品書cursor CURSOR FOR
			SELECT 
				uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID
				, um.商品ID, p.商品区分, um.単価, um.数量
				, dbo.fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) AS 消費税額 
			FROM
				売上 uh
				INNER JOIN 売上明細 um
					ON uh.売上ID = um.売上ID
				INNER JOIN 得意先 t
					ON uh.得意先ID = t.得意先ID
				INNER JOIN 商品 p
					ON um.商品ID = p.商品ID
			WHERE
				uh.売上日 BETWEEN @pFrom AND @pTo   
				AND t.丸め方法 = 2
			ORDER BY 
				uh.納品書NO, um.単価 * um.数量 DESC, um.行NO;

		OPEN 納品書cursor;

		SET @配賦額 = 0;
		SET @差額 = 0;
		SET @before納品書NO = 0;
		
		FETCH NEXT FROM 納品書cursor
		INTO @請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
			, @商品ID, @商品区分, @単価, @数量, @消費税額;

		WHILE @@FETCH_STATUS = 0
		  BEGIN
			IF @before納品書NO != @納品書NO
			  BEGIN
				SET @カウンタ = 0;
				SET @before納品書NO = @納品書NO;
				 
				SELECT 
					@差額 
						= dbo.fn丸め(SUM(um.単価 * um.数量 * p.消費税率), MIN(t.丸め方法))
						- SUM(dbo.fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法))
				FROM 
					売上 uh
					INNER JOIN 売上明細 um
						ON uh.売上ID = um.売上ID
					INNER JOIN 得意先 t
						ON uh.得意先ID = t.得意先ID
					INNER JOIN 商品 p
						ON um.商品ID = p.商品ID
				WHERE 
					uh.納品書NO = @納品書NO;
			END;

			SET @カウンタ = @カウンタ + 1;

			IF @カウンタ <= ABS(@差額)
			  BEGIN
				SET @配賦額 = SIGN(@差額);
			END;
			ELSE
			  BEGIN
				SET @配賦額 = 0;
			END;
			
			INSERT INTO #WORK
				(請求書NO, 納品書NO, 行NO, 売上日, 得意先ID
				, 商品ID, 商品区分, 単価, 数量, 消費税額)
			VALUES
				(@請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
				, @商品ID, @商品区分, @単価, @数量, @消費税額 + @配賦額);
			
			FETCH NEXT FROM 納品書cursor
			INTO @請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
				, @商品ID, @商品区分, @単価, @数量, @消費税額;
			
		END
		
		CLOSE 納品書cursor;
		DEALLOCATE 納品書cursor;

		/* 請求書単位の処理 */
		DECLARE 請求書cursor CURSOR FOR
			SELECT 
				uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID
				, um.商品ID, p.商品区分, um.単価, um.数量
				, dbo.fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) AS 消費税額 
			FROM
				売上 uh
				INNER JOIN 売上明細 um
					ON uh.売上ID = um.売上ID
				INNER JOIN 得意先 t
					ON uh.得意先ID = t.得意先ID
				INNER JOIN 商品 p
					ON um.商品ID = p.商品ID
			WHERE
				uh.売上日 BETWEEN @pFrom AND @pTo   
				AND t.丸め方法 = 3
			ORDER BY 
				uh.請求書NO, um.単価 * um.数量 DESC, uh.納品書NO, um.行NO;

		OPEN 請求書cursor;

		SET @配賦額 = 0;
		SET @差額 = 0;
		SET @before請求書NO = 0;
		
		FETCH NEXT FROM 請求書cursor
		INTO @請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
			, @商品ID, @商品区分, @単価, @数量, @消費税額;

		WHILE @@FETCH_STATUS = 0
		  BEGIN
			IF @before請求書NO != @請求書NO
			  BEGIN
				SET @カウンタ = 0;
				SET @before請求書NO = @請求書NO;
				 
				SELECT 
					@差額 
						= dbo.fn丸め(SUM(um.単価 * um.数量 * p.消費税率), MIN(t.丸め方法))
						- SUM(dbo.fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法))
				FROM 
					売上 uh
					INNER JOIN 売上明細 um
						ON uh.売上ID = um.売上ID
					INNER JOIN 得意先 t
						ON uh.得意先ID = t.得意先ID
					INNER JOIN 商品 p
						ON um.商品ID = p.商品ID
				WHERE 
					uh.請求書NO = @請求書NO;
			END;

			SET @カウンタ = @カウンタ + 1;

			IF @カウンタ <= ABS(@差額)
			  BEGIN
				SET @配賦額 = SIGN(@差額);
			END;
			ELSE
			  BEGIN
				SET @配賦額 = 0;
			END;
			
			INSERT INTO #WORK
				(請求書NO, 納品書NO, 行NO, 売上日, 得意先ID
				, 商品ID, 商品区分, 単価, 数量, 消費税額)
			VALUES
				(@請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
				, @商品ID, @商品区分, @単価, @数量, @消費税額 + @配賦額);
			
			FETCH NEXT FROM 請求書cursor
			INTO @請求書NO, @納品書NO, @行NO, @売上日, @得意先ID
				, @商品ID, @商品区分, @単価, @数量, @消費税額;
			
		END

		CLOSE 請求書cursor;
		DEALLOCATE 請求書cursor;

		-- 結果を返す。
		SELECT * 
		FROM #WORK
		ORDER BY 請求書NO, 納品書NO, 行NO
		;
		
		-- ワークテーブルを消す。
		DROP TABLE #WORK;

GO