这个存储过程还可不可以优化?pengdali(大力 V2.0)、 zjcxc(邹建)等高手请进!!!
cnwhm 2003-08-21 01:31:29 在我现有的数据库中有以下若干表:
单位表(序号,单位名称,模块号)
另有大量的类似的下面结构的表
产品表1(单位,品名,日产,月产,年产),产品表2(单位,.......
其中产品表.单位=单位表.序号
不同的模块对不同的(产品表)有录入权限,且一个模块可包含多个不同的单位。现要做下面的统计:
分别根据统计的日期,求出月产,年产;
其逻辑为:
1、若为当月1日,则将产品表1中的月产赋为当日的日产,年产则为上日的年产+今日的日产。
2、若为当年的1日,则将产品表1中的月产和年产均赋为当日的日产。
3、其于日子,月产=昨日月产+今日日产,年产=昨日年产+今日日产。
现我是这样实现的
将单位表存入表变量,通过游标顺序读取,再通过表变量(模块号)判断该单位对哪张表有录入权限,若有,则调用另一存储过程:Cal_Add(传递参数:表名,单位序号),汇总该单位各种产品的当日的月产、年产。
我的Cal_Add是这样工作的:
根据传来的表名,单位序号,并取出今日的日期。
1,创建视图1和2分别存储该单位昨日和今日的数据。
2,根据今日的日期执行上述的逻辑,在视图中进行计算,更新表记录。
示例代码见下:
--用于存储临时数据(序号,单位名称,模块号)
declare @temptable table(xuhao int,dwmc varchar(30),code int);
--定义游标用到的变量 @tempid (序号) @unitname (单位名称) @tempcode (模块号)
declare @tempid int
declare @unitname varchar(30)
declare @tempcode int
declare @icount int
declare @today datetime
declare @yesterday datetime
set @today=convert(varchar(10),getdate(),120)
set @yesterday=convert(varchar(10),dateadd(dd,-1,getdate()),120)
insert into @temptable
select 序号,单位名称,模块号 from 系统_单位表
declare mycursor cursor for select xuhao,dwmc,code from @temptable
--打开游标
open mycursor
fetch next from mycursor
into @tempid,@unitname,@tempcode
--进入表循环
while @@fetch_status=0
begin
if(@tempcode=8 or @tempcode=7 or @tempcode=10 or @tempcode=6)
exec('cal_huizong ''产品表1'','+@tempid)
--移动游标到下一条记录
fetch next from mycursor
into @tempid,@unitname,@tempcode
end
--关闭游标
close mycursor
--释放游标
deallocate mycursor
--清除临时表数据
delete from @temptable
Cal_Add:
declare @today varchar(10)
declare @yesterday varchar(10)
set @today=convert(varchar(10),getdate(),120)
set @yesterday=convert(varchar(10),dateadd(dd,-1,getdate()),120)
if(@TableName='产品表1')
begin
IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID('OldDateView'))
DROP VIEW OldDateView
IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID('NewDateView'))
DROP VIEW NewDateView
--创建视图,分别保存新旧数据
exec('CREATE VIEW OldDateView AS SELECT 日产,月产,年产,品名 FROM 产品表1 WHERE 单位='+@UnitID+' AND 日期='''+@yesterday+'''')
exec('CREATE VIEW NewDateView AS SELECT 日产,月产,年产,品名 FROM 产品表1 WHERE 单位='+@UnitID+' AND 日期='''+@today+'''')
if(day(getdate())=1)
begin
UPDATE NewDateView SET 月产 = 日产
end
else
begin
UPDATE NewDateView SET 月产 = 日产 + ISNULL((SELECT 月产 FROM OldDateView WHERE NewDateView.品名 = OldDateView.品名),0)
end
if(getdate()=(year(getdate())+'01'+'01'))
UPDATE NewDateView SET 年产 = 日产
else
UPDATE NewDateView SET 年产 = 日产 + ISNULL((SELECT 年产 FROM OldDateView WHERE NewDateView.品名 = OldDateView.品名),0)
DROP VIEW OldDateView
DROP VIEW NewDateView
end
问还有没有办法将上面的存储过程再优化一下或还有什么办法加快执行速度?我现在的表中的记录有上万条,最多的上了十万条。