SQL Server 2008写一个存储过程

nightgoblin 2012-04-12 02:33:56
写一个存储过程能自动生成产品合同编码的，先看基础数据：


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是合同的总数。*/


/*创建一个存储过程实现自动生成产品编码，例如产品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是合同的总数。*/
请问高手这样一个存储过程应该怎样来实现？？
...全文
469 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
nightgoblin 2012-04-16
  • 打赏
  • 举报
 回复
哎，我自己写了个实现了这个功能：


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;
a584666873a 2012-04-13
  • 打赏
  • 举报
 回复
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 VALUES(l_cur_a.a1,substr(l_cur_a.a3,1,3),l_new_contractNo);
COMMIT;
dbms_output.put_line(l_new_contractNo);
END IF;
END IF;
END LOOP;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Rrror');
END create_contractNo;

我这写了个，但是这会数据库编译很慢，不知道是不是你要的东西，可以参考下。
nightgoblin 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]
引用 8 楼 的回复:
b表中b2为'1,3,5,7,9,B' 就是每年的月份，有的产品有规律，有的产品没有规律，但我……

那为什么不是‘p1212’，跳过2012年12月是为什么？，还有12年的中间是不连续的，比如8月，10 月缺失.
[/Quote]
这个是p产品合同规定的必须是1、3、5、7、9、11月这6个月份的，其中A代表10月、B代表11月、C代表12月的。
tjs_125 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
b表中b2为'1,3,5,7,9,B' 就是每年的月份，有的产品有规律，有的产品没有规律，但我……
[/Quote]
那为什么不是‘p1212’，跳过2012年12月是为什么？，还有12年的中间是不连续的，比如8月，10 月缺失.
nightgoblin 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 的回复:]
b表中的 “产品合同月份”列值 '1,3,5,7,9,B' 是否有规律，不然感觉会有歧义，1,3按照你说的必须在2012年4月以后有效，我可以理解为2013年或者2014年后面年份的1月与3月，紧接着的5和9，是2012年还是2013年的也不是很明确？

能否说明
[/Quote]
b表中b2为'1,3,5,7,9,B' 就是每年的月份，有的产品有规律，有的产品没有规律，但我举的这个两个产品都是有规律的。然后后面b3为‘6’表示合同的有效数量，例如：产品p在2012年的合同编号应该为‘p1201’，‘p1203’，‘p1205’，‘p1207’，‘p1209’，‘p1211’ 这6个，但是由于1月和3月份已经过去了，那么‘p1201’，‘p1203’就是无效的，（这两个我没在a表中列出来），由于p的有效合同应该是6个，少了2个后，那么在2013年就是增加2个‘p1301’和‘p1303’，保持有效合同为6个。不知道我这有解释，你能否明白？？
tjs_125 2012-04-13
  • 打赏
  • 举报
 回复
b表中的 “产品合同月份”列值 '1,3,5,7,9,B' 是否有规律，不然感觉会有歧义，1,3按照你说的必须在2012年4月以后有效，我可以理解为2013年或者2014年后面年份的1月与3月，紧接着的5和9，是2012年还是2013年的也不是很明确？

能否说明
nightgoblin 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 的回复:]
对于rb应该是缺‘rb1212’，‘rb1302’，‘rb1303’吧，另外p的月份与产品合同编码有点矛盾啊，月份里面有没有02月啊？
[/Quote]
额，是的哦。。那个是我写错了，应该是把p1302改为p1303
nightgoblin 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 的回复:]
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……
[/Quote]

你这个是oracle里面的存储过程啊。我说的SQL2008里面的，不过感谢你的回答，能具体说下你的思路或是算法吗？？
tjs_125 2012-04-12
  • 打赏
  • 举报
 回复
对于rb应该是缺‘rb1212’，‘rb1302’，‘rb1303’吧，另外p的月份与产品合同编码有点矛盾啊，月份里面有没有02月啊？
leijunyuncyuyan 2012-04-12
  • 打赏
  • 举报
 回复
or,no
nightgoblin 2012-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 的回复:]
这样是楼主索要的吗

SQL code

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'+DATEPAR……
[/Quote]
不是啊，我是要自动更新的，不需要输入参数。
迪迦凹凸曼 2012-04-12
  • 打赏
  • 举报
 回复
这样是楼主索要的吗


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 行受影响)*/
nightgoblin 2012-04-12
  • 打赏
  • 举报
 回复
高手快来啊，在线等！1
3天掌握SQL Server 2012 数据库
SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），Microsoft SQL Server 2012 是一个全面的数据库平台，使用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序 。 黄强老师针比较稳定且较新的SQL SERVER  2012版本演示数据库的安装使用，SQL(Structured Query Language)语言、T-SQL语言，数据库的增删改查、函数、存储过程、事务等重要知识点，让学员轻松入门并快速掌握关系型数据库！
sqlserver2008 调试存储过程
sqlserver2008 调试存储过程 1、创建所需要调试的存储过程，并执行一下，也就是保存好 ，我要演示的存储过程名是 “usp_Passport_GetNewDepositary” 2、找到存储过程，右键“执行存储过程”，会弹出如下的界面，要输入存储过程所用的参数 3、参数输入后点击确定，系统会为你生成如下的代码到编辑器中 4、点击工具条上面绿色的小三角进入调试界面，注意，这里有一个关键点，当代码执行到EXEC这一行时一定要点击“调试” —》“逐语句”，这样才会进到这个存储过程中去 5、进去后
SQL Server数据库基础知识——数据库存储过程怎么写
SQL Server数据库基础知识 存储过程概述 什么是存储过程？ 存储过程的种类 如何创建、修改、删除、调用存储过程？ 存储过程的优缺点 存储过程和触发器的区别? 存储过程和函数的区别? 存储过程的使用 1. 什么是存储过程？ 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个“execute 存储过程名 参数”命令来调用存储过程。 2. 存储过程的种类
sqlserver存储过程
一、存储过程 1.预先存储好的sql程序，保存在sqlserver中，通过名称和参数执行。 2.存储过程中可以包含：insert语句，update语句，delete语句，select语句，逻辑控制语句以及变量。 二、存储过程的优点： 1.执行速度更快 2.允许模块化程序设计 3.提高系统安全性 4.减少网络流量 三、存储过程的分类： 1.系统存储过程 （1）一般以sp_开头，由sqlserver创...
SqlServer存储过程详解
看到别人总结的SQL server的存储过程觉得写的很好 ，就拿过来了，希望对大家有帮助。 1.创建存储过程的基本语法模板： if (exists (select * from sys.objects where name = 'pro_name')) drop proc pro_name go create proc pro_name @param_name param_t...
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章