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','rb1205');
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','rb1209');
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 (1,'rb','rb1304');
INSERT INTO a VALUES (3,'p','p1205');
INSERT INTO a VALUES (3,'p','p1207');
INSERT INTO a VALUES (3,'p','p1211');
INSERT INTO a VALUES (3,'p','p1301');
INSERT INTO a VALUES (3,'p','p1302');
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);

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

CREATE PROCEDURE sp_id
(
    @a1 int ,
    @a2 varchar(20),
    @a3 varchar(20) output
)
AS
begin
    SET @a3=@a2+RIGHT(DATEPART(year,GETDATE()),2)+RIGHT('100'+DATEPART(MONTH,GETDATE()),2)
END

DECLARE @a NVARCHAR(20)
EXEC sp_id 3,'rb',@a OUTPUT
SELECT @a
/*
--------------------
rb1204

(1 行受影响)*/

------解决方案--------------------
对于rb应该是缺‘rb1212’，‘rb1302’，‘rb1303’吧，另外p的月份与产品合同编码有点矛盾啊，月份里面有没有02月啊？

------解决方案--------------------

CREATE OR REPLACE PROCEDURE create_contractNo IS
      l_cur_month VARCHAR2(50);
      l_cur_year VARCHAR2(6);
      l_new_contractNo VARCHAR2(20);
      l_cur_index VARCHAR2(5);
      l_cur_count VARCHAR2(10);
      l_total_count VARCHAR2(10);
      CURSOR cur_a IS
             SELECT a.a1,a.a2,a.a3 FROM a a;
      CURSOR cur_b IS
             SELECT b.b1,b.b2,b.b3 FROM b b;
            
BEGIN
       FOR l_cur_b IN cur_b LOOP
          FOR i IN 1..l_cur_b.b3 LOOP
              SELECT COUNT(a.a3),b.b3 INTO l_cur_count,l_total_count FROM a a,b b WHERE a.a1=b.b1 AND b.b1=l_cur_b.b1 GROUP BY a.a3,b.b3;
              l_cur_index := 0;
              l_cur_month := REPLACE(l_cur_b.b2,',');
              IF instr(l_cur_month,'A') = 0 THEN
                  l_cur_month := REPLACE(l_cur_b.b2,'A','10');
              ELSIF instr(l_cur_month,'B') = 0 THEN
                  l_cur_month := REPLACE(l_cur_b.b2,'B','11');
              ELSIF instr(l_cur_month,'C') = 0 THEN
                  l_cur_month := REPLACE(l_cur_b.b2,'C','12');
              END IF;
              l_cur_month := substr(l_cur_month,i,1);
              FOR l_cur_a IN cur_a LOOP
                 IF l_cur_a.a1=l_cur_b.b1 THEN
                    l_cur_index := l_cur_index+1;
                    l_cur_year := substr(l_cur_a.a3,3,2);
                    IF l_cur_b.b3 = l_cur_count AND substr(l_cur_a.a3,5)-l_cur_month != 0 THEN
                        l_new_contractNo := substr(l_cur_a.a3,1,3) || l_cur_year || l_cur_month;
                        INSERT INTO a V