实现自动插入产品的存储过程

/*创建一个存储过程实现自动生成产品编码，例如产品rb合同总数应该是12，但是a表中只有9个，还差3个通过b表中b2合同月份判断缺少6个，
也就是‘rb1205’，‘rb1209’，‘rb1212’，‘rb1302’，‘rb1303’，‘rb1304’，让它能够自动生成。
注：a表中a3产品编码规则是‘产品编码’+‘年份后两位’+‘两位月份’
（如：2012年5月的合同，rb1205）,2012年4月份之后的合同的才是有效。
b表b2是合同月份，而A代表10，B代表11，C代表12，b3是合同的总数。*/

/****************************************************************

算法：

1.通过游标将a表中a1，a3的值插入到@ta表中。

2.判断a3中合同数量是不是小于b3，如果小于则需要添加合同，否则跳出。

3.将b2中值提取出来生成月分，并组成合同编码插入到@tb表中。

4.比较@ta与@tb中a2,b2的值，将少a2中缺少的在插入到a表。

*******************************************************************/

基础数据源码：

USE tempdb;
GO
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b;
GO
IF OBJECT_ID('a') IS NOT NULL 
DROP TABLE a;
GO
--创建a表
CREATE TABLE a
(a1 INT , -- 产品ID
 a2 VARCHAR(10), -- 产品编码
 a3 VARCHAR(20) -- 产品合同编码
 );
--插入数据
INSERT INTO a VALUES (1,'rb','rb1206');
INSERT INTO a VALUES (1,'rb','rb1207');
INSERT INTO a VALUES (1,'rb','rb1208');
INSERT INTO a VALUES (1,'rb','rb1210');
INSERT INTO a VALUES (1,'rb','rb1211');
INSERT INTO a VALUES (1,'rb','rb1301');
INSERT INTO a VALUES (3,'p','p1205');
INSERT INTO a VALUES (3,'p','p1207');
INSERT INTO a VALUES (3,'p','p1301');
GO
--创建b表
CREATE TABLE b
( b1 INT , --产品ID
  b2 VARCHAR(50), --产品合同月份
  b3 INT --产品合同总数
);
--插入数据
INSERT INTO b VALUES (1,'1,2,3,4,5,6,7,8,9,A,B,C',12);
INSERT INTO b VALUES (3,'1,3,5,7,9,B',6);
GO
IF TYPE_ID('tya') IS NOT NULL 
DROP TYPE tya;
GO
--创建tya表类型
CREATE TYPE tya AS TABLE 
(a1 INT,
 a2 VARCHAR(50)
  );
GO
IF TYPE_ID('tyb') IS NOT NULL 
DROP TYPE tyb;
GO
--创建tyb表类型
CREATE TYPE tyb AS TABLE 
( b1 INT ,
  b2 VARCHAR(50)
);

存储过程源码：

USE tempdb;
GO
IF OBJECT_ID('spUpdatea') IS NOT NULL
DROP PROCEDURE spUpdatea;
GO
CREATE PROCEDURE spUpdatea
AS 
BEGIN
	DECLARE @a1 INT,
	        @a2 VARCHAR(10),
	        @a3 VARCHAR(20),
	        @na3 INT ,--a表中a3的数量
	        @b1 INT ,
	        @b2 VARCHAR(50),
	        @b3 INT,
	        @nyear INT ,--年份
	        @nmoth INT ,--月份
	        @m CHAR(5) ,--b表中b2列的月份
	        @n INT , --b表中b2列的字符串间隔
	        @nb2 INT, --b表中b2列字符串的长度
	        @ta AS tya,
	        @tb AS tyb,
	        @ntb INT, --@tb表中b2的数量
	        @tb1 INT, --@tb表中的b1
	        @tb2 VARCHAR(20) --@tb表中的b2
	 DECLARE cur CURSOR FOR SELECT * FROM a;
	 OPEN cur;
	 FETCH NEXT FROM cur INTO @a1,@a2,@a3;
	 WHILE @@FETCH_STATUS=0
	   BEGIN
	     /*将a1和a3插入到@ta表*/
	     INSERT INTO @ta SELECT a1,a3 FROM a WHERE a1=@a1;
	     /*查找a3的数量*/
	   	 SELECT @na3=COUNT(a3) FROM a WHERE a1=@a1;
	   	 SELECT @b1=b1,@b2=b2,@b3=b3,@nb2=LEN(b2) FROM b WHERE b1=@a1;
	   	 IF @na3<@b3
	   	    BEGIN
	   	        SET @nyear=YEAR(GETDATE());
	   	        SET @nmoth=MONTH(GETDATE());
	   	    	SET @n=1;
	   	    	WHILE @n<=@nb2 
	   	    	    BEGIN
	   	    	        /*提取b2中月份*/
	   	    	    	SET @m=SUBSTRING(@b2,@n,1);
	   	    	    	/*将b2中A,B,C转换成10,11,12再转换成整型*/
	   	    	    	SET @m=CAST((CASE @m 
	   	    	    	                 WHEN 'A' THEN '10'
	   	    	    	                 WHEN 'B' THEN '11'
	   	    	    	                 WHEN 'C' THEN '12'
	   	    	    	              ELSE @m END) AS INT);
	   	    	    	/*比较@m与@nmoth，如果大于则生成@a3*/              
	   	    	    	IF @m>@nmoth
	   	    	    	   BEGIN
	   	    	    	   	  SET @a3=@a2+RIGHT(@m+1000000+@nyear*100,4);
	   	    	    	   	  INSERT INTO @tb VALUES(@b1,@a3);
	   	    	    	   END;
	   	    	    	ELSE
	   	    	    	   BEGIN
	   	    	    	   	  SET @a3=@a2+RIGHT(@m+1000000+(@nyear+1)*100,4);
	   	    	    	      INSERT INTO @tb VALUES(@b1,@a3);
	   	    	    	   END; 
	   	    	    	SET @n=@n+2;                 
	   	    	    END;
	   	    	--SELECT @ntb=COUNT(b2) FROM @tb;
	   	    	/*判断@tb中b2的数量是否小于b表中b3的值，小于则需要添加@a3*/
	   	    	--IF @ntb<@b3
	   	    	--    BEGIN
	   	    	--      WHILE @n<=@nb2 
	   	    	--			BEGIN
	   	    	--				/*提取b2中月份*/
	   	    	--    			SET @m=SUBSTRING(@b2,@n,1);
	   	    	--    			/*将b2中A,B,C转换成10,11,12再转换成整型*/
	   	    	--    			SET @m=CAST((CASE @m 
	   	    	--    							 WHEN 'A' THEN '10'
	   	    	--    							 WHEN 'B' THEN '11'
	   	    	--    							 WHEN 'C' THEN '12'
	   	    	--    						  ELSE @m END) AS INT);
	   	    	--    			/*比较@m与@nmoth，如果小于则生成@a3*/              
	   	    	--    			IF @m<=@nmoth
	   	    	--    			   BEGIN
	   	    	--    	   			  SET @a3=@a2+RIGHT(@m+1000000+(@nyear+1)*100,4);
	   	    	--    	   			  INSERT INTO @tb VALUES(@b1,@a3);
	   	    	--    			   END;
	   	    	--    			SET @n=@n+2;                 
	   	    	--			END;  	
	   	    	--    END;
	   	    	/*比较@ta与@tb中相差数据的然后再插入到a表中*/    
	   	    	DECLARE cur2 CURSOR FOR SELECT b1,b2 FROM @tb WHERE b2 NOT IN (SELECT a2 FROM @ta);
	   	    	OPEN cur2;
	   	    	FETCH NEXT FROM cur2 INTO @tb1,@tb2;
	   	    	WHILE @@FETCH_STATUS=0
	   	    	     BEGIN
	   	    	     	INSERT INTO a VALUES(@tb1,@a2,@tb2);
	   	    	     	FETCH NEXT FROM cur2 INTO @tb1,@tb2; 
	   	    	     END;
	   	    	CLOSE cur2;
	   	    	DEALLOCATE cur2;        
	   	    END;
	   	 FETCH NEXT FROM cur INTO @a1,@a2,@a3;
	   END; 
	   CLOSE cur;
	   DEALLOCATE cur;       
END;

调用此存储过程:

EXEC spUpdatea;

查询结果：

SELECT * FROM a ORDER BY a3;

a1 a2 a3
3 p p1205
3 p p1207
3 p p1209
3 p p1211
3 p p1301
3 p p1303
1 rb rb1205
1 rb rb1206
1 rb rb1207
1 rb rb1208
1 rb rb1209
1 rb rb1210
1 rb rb1211
1 rb rb1212
1 rb rb1301
1 rb rb1302
1 rb rb1303
1 rb rb1304