/*创建一个存储过程实现自动生成产品编码,例如产品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