ORACLE SP

USE [rDelta-iKnow]
GO
/****** 对象:  StoredProcedure [dbo].[sp_E3_ModuleCodeTrace_Dr_Statistic]    脚本日期: 09/17/2012 09:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lKF43181
-- Create date: 2012-9-13
-- Description: 模块代码跟踪dr统计
-- =============================================
ALTER PROCEDURE [dbo].[sp_E3_ModuleCodeTrace_Dr_Statistic]
(
@version varchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
--统计表
declare @mctTable varchar(255);
--link表
declare @linkTable varchar(255);
declare @StoryTable varchar(255);
declare @RMTable varchar(255);
--如果所需要的表不存在，则存储过程结束
set @mctTable = 'E3_MODULE_STATISTIC_' + @version;
set @linkTable = 'E3_LINK_' + @version;
set @StoryTable = 'IKNOW_STORY_' + @version;
set @RMTable = 'IKNOW_RMPUBLISHTREE_' + @version;
IF not EXISTS (SELECT * FROM sysobjects WHERE name = @mctTable)
return;
else if not EXISTS (SELECT * FROM sysobjects WHERE name = @linkTable)
return;
else if not EXISTS (SELECT * FROM sysobjects WHERE name = @StoryTable)
return;
--以下开始统计数据，然后把统计数据同步到E3_MODULE_STATISTIC_+version表中
DECLARE @RunSQL nvarchar(MAX);
SET @RunSQL = '
--表变量,用于存dr信息
declare @drTable table(drguid varchar(255),drcode varchar(255),jirakey varchar(255),code int);

with story
as (
select distinct drguid,drcode,JIRAKEY,b.code from ' + @StoryTable + ' a
inner join ' +  @RMTable + ' b on a.drguid=b.ITEMGUID or a.drcode=b.rmid
where JIRAKEY in(
select distinct DESTID from ' + @linkTable + '
where DESTTYPE=''STORY''
)and ( drguid != '' or drcode!='')
)
insert into @drTable select * from story;

--统计的结果表变量
declare @resultTable table(vob varchar(4000),dr_count int,dr_code int);
insert into @resultTable select vob,count(*)as dr ,sum(code) as dr_code from
(
select distinct a.vob,c.drguid,c.code from  ' + @mctTable +' a
inner join ' + @linkTable + ' b on b.SOURCEID like a.REALVOB + ''%''
inner join @drTable  c on c.jirakey = b.DESTID
where b.DESTTYPE=''STORY''
)d group by d.vob;


update ' + @mctTable + '
set ' + @mctTable + '.dr_count = b.dr_count,' + @mctTable + '.dr_loc = b.dr_code
from @resultTable  b
where  ' + @mctTable + '.vob=b.vob;'
EXECUTE SP_EXECUTESQL @RunSQL;

END