Sqlserver2005迁移至Oracle系列之二：生成存储过程

核心提示： --创建临时表begin --全局临时表 if not object_id('tempdb..##sp') is null drop table ##sp create table ##sp([id] int identity(1,1),[definition] nvarc

--创建临时表

begin
　　　 --全局临时表
　　　 if not object_id('tempdb..##sp') is null
　　　　　　　 drop table ##sp
　　　 create table ##sp([id] int identity(1,1),[definition] nvarchar(4000))
end

--定义存储过程

if @iscreateprocedure = 1
begin
　　　 begin
　　　　　　　 declare cr_procedure_name cursor for
　　　　　　　　　　　 select [name],[object_id]
　　　　　　　　　　　 from sys.objects
　　　　　　　　　　　 where (type = 'P') and (is_ms_shipped = 0) and ([name] not like 'sp_%') and ([name] like @name)
　　　　　　　　　　　 --where (type = 'fn') and (is_ms_shipped = 0) and ([name] not like 'sp_%') and ([name] like @name)
　　　　　　　　　　　 order by [name]

　　　　　　　 open cr_procedure_name
　　　　　　　 fetch next from cr_procedure_name into @procedure_name,@procedure_id


　　　　　　　 --游标循环处理
　　　　　　　 while @@fetch_status = 0
　　　　　　　 begin
　　　　　　　　　　　 insert into ##sp([definition]) select 'create procedure ' +　 left(@procedure_name,30)

　　　　　　　　　　　 --取出procedure的定义
　　　　　　　　　　　 --将tab替换为空格。极其重要的一步
　　　　　　　　　　　 select @definition = replace(definition,char(9),replicate(char(32),4)) from sys.sql_modules where object_id = @procedure_id

　　　　　　　　　　　 --预处理
　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　 --处理[dbo].dbo.
　　　　　　　　　　　　　　　 set @definition = replace(replace(@definition,'[dbo].',''),'dbo.','')
　　　　　　　　　　　　　　　 --处理[]
　　　　　　　　　　　　　　　 set @definition = replace(replace(@definition,'[',''),']','')
　　　　　　　　　　　　　　　 --处理@@rowcount
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'@@rowcount','SQL%ROWCOUNT')
　　　　　　　　　　　　　　　 --处理@@
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'@@','MSSQL_')
　　　　　　　　　　　　　　　 --处理datatype
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'tinyint','INT')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'smallint','INT')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'bigint','NUMERIC(64)')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'datetime','DATE')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'varchar','VARCHAR2')
　　　　　　　　　　　　　　　 --处理函数
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'isnull(','nvl(')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'getdate()','sysdate')
　　　　　　　　　　　　　　　 set @definition = replace(@definition,'len(','length(')
　　　　　　　　　　　　　　　 --set @definition = replace(@definition,'cast(','to_char(')
　　　　　　　　　　　 end

　　　　　　　　　　　 set @isdeclare = 0
　　　　　　　　　　　 set @definition_preline = ''
　　　　　　　　　　　 set @definition_curline = ''
　　　　　　　　　　　 set @pos_char10 = charindex(nchar(10),@definition)
　　　　　　　　　　　 while @pos_char10 > 0
　　　　　　　　　　　 begin
　　　　　　　
　　　　　　　　　　　　　　　 set @definition_pre = rtrim(left(@definition,@pos_char10))　　　　　　　 --char（10）在右侧
　　　　　　　　　　　　　　　 set @definition = right(@definition,len(@definition) - @pos_char10)
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --取出下一行语句，供分析用
　　　　　　　　　　　　　　　 set @pos_char101 = charindex(char(10),@definition)
　　　　　　　　　　　　　　　 if @pos_char101 = 0 and len(@definition) > 0
　　　　　　　　　　　　　　　　　　　 set @definition_curline = @definition
　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　 set @definition_curline = rtrim(left(@definition,@pos_char101))　　　　　　　 --char（10）在右侧

　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 --处理create procedure部分
　　　　　　　　　　　　　　　　　　　 if (@isdeclare = 0) and (@definition_pre like 'create%proc%')
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 set @isdeclare = 1
　　　　　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　　　　　 --insert into ##sp([definition]) select 'create procedure ' +　 left(@procedure_name,30)
　　　　　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select '('
　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = null
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　 --处理声明部分的变量
　　　　　　　　　　　　　　　　　　　 if (@isdeclare = 1) and (@definition_pre like '%@%')
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = rtrim(replace(@definition_pre,char(9),char(32)))
　　　　　　　　　　　　　　　　　　　　　　　 set @pos = charindex('output',@definition_pre)
　　　　　　　　　　　　　　　　　　　　　　　 if @pos > 0
　　　　　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = replace(@definition_pre,'output','')
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @pos = charindex(char(32),@definition_pre,charindex('@',@definition_pre))
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,@pos) + ' out ' + right(@definition_pre,len(@definition_pre)-@pos)
　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　　　　　 set @pos = charindex('(',@definition_pre)
　　　　　　　　　　　　　　　　　　　　　　　 set @pos2 = charindex(')',@definition_pre)
　　　　　　　　　　　　　　　　　　　　　　　 if @pos > 0
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,@pos - 1) + right(@definition_pre,len(@definition_pre)-@pos2)
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　
　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　 --处理as部分，完成声明部分的处理
　　　　　　　　　　　　　　　　　　　 if (@isdeclare = 1) and (ltrim(@definition_pre) like 'AS%')
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 set @isdeclare = 0
　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select replicate(char(32),4) + 'rs' + replicate(char(32),8) + 'out SYS_REFCURSOR　　　　　　　 --返回结果集'
　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select ')'
　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select @definition_pre
　　　　　　　　　　　　　　　　　　　　　　　 --insert into ##sp([definition]) select replicate(char(32),4) + 'v_count INT := 0;'
　　　　　　　　　　　　　　　　　　　　　　　 --insert into ##sp([definition]) select replicate(char(32),4) + 'v_ErrorRemark VARCHAR2(1024);'
　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select 'BEGIN'
　　　　　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select replicate(char(32),4) + 'OPEN rs FOR SELECT * FROM DUAL WHERE 0>1;'
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = null
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　 end

　　　　　　　　　　　　　　　 --处理begin-end内的变量execute
　　　　　　　　　　　　　　　 if (@definition_pre like '%exec%') or (@definition_pre like '%execute%')
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @tpstr = ltrim(replace(replace(@definition_pre,'execute ','exec '),'exec ',''))
　　　　　　　　　　　　　　　　　　　 set @tpstr = left(@tpstr,charindex(char(32),@tpstr))
　　　　　　　　　　　　　　　　　　　 set @pos = charindex(@tpstr,@definition_pre) + len(@tpstr)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,@pos) + '(' + right(@definition_pre,len(@definition_pre)-@pos) + ');'
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --处理begin-end内的变量declare
　　　　　　　　　　　　　　　 if @definition_pre like '%declare%@%'
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @definition_pre = rtrim(@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @pos = charindex('--',@definition_pre)
　　　　　　　　　　　　　　　　　　　 if @pos > 0
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,@pos - 1) + ';' + right(@definition_pre,len(@definition_pre) - (@pos - 1))
　　　　　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = @definition_pre + ';'
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --处理begin-end内的SET @ErrorRemark
　　　　　　　　　　　　　　　 if (@definition_pre like '%set%@ErrorRemark%=%')
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @pos = charindex('=',@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = right(@definition_pre,len(@definition_pre) - @pos)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = replicate(char(32),8) + '@ErrorRemark = ' + @definition_pre
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --处理begin-end内的变量set
　　　　　　　　　　　　　　　 if (@definition_pre like '%set %@%') and (@definition_pre not like '%update % set %') and (@definition_preline not like '%update%')
　　　　　　　　　　　　　　　　　　　 and (@definition_pre not like '%if%') and (@definition_pre not like '%select%') and (@definition_pre not like '%delete%')
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @definition_pre = rtrim(@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @pos = charindex('--',@definition_pre)
　　　　　　　　　　　　　　　　　　　 if @pos > 0
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,@pos - 1) + ';' + right(@definition_pre,len(@definition_pre) - (@pos - 1))
　　　　　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = @definition_pre + ';'
　　　　　　　　　　　　　　　　　　　 set @definition_pre = replace(replace(@definition_pre,'set',''),'=',':=')
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --处理begin-end内的select @var = value from ...
　　　　　　　　　　　　　　　 if ((@definition_pre like '%select%@%=%') and (@definition_pre not like '%exists%select%@%=%') and (@definition_curline like '%from %'))
　　　　　　　　　　　　　　　　　　　 or ((@definition_pre like '%select%@%=%from%') and (@definition_pre not like '%exists%select%@%=%'))
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @tpstr3 = 'select '
　　　　　　　　　　　　　　　　　　　 set @pos = charindex(@tpstr3,@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @tpstr = left(@definition_pre,@pos - 1 + len(@tpstr3) )　　　 --select头部
　　　　　　　　　　　　　　　　　　　 set @tpstr1 = ltrim(right(@definition_pre,len(@definition_pre) - (@pos -1 + len(@tpstr3))))
　　　　　　　　　　　　　　　　　　　 set @tpstr3 = ' from '
　　　　　　　　　　　　　　　　　　　 set @pos = charindex(@tpstr3,@tpstr1)
　　　　　　　　　　　　　　　　　　　 if @pos = 0
　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr2 = ''
　　　　　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr2 = ltrim(right(@tpstr1,len(@tpstr1) - @pos + 1 )) --from尾部
　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr1 = rtrim(left(@tpstr1,@pos - 1))
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　 set @tpstr4 = ''　　　 --into列表
　　　　　　　　　　　　　　　　　　　 set @tpstr5 = ''　　　 --select列表
　　　　　　　　　　　　　　　　　　　 set @tpstr6 = ''
　　　　　　　　　　　　　　　　　　　 set @pos = charindex('=',@tpstr1)
　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　 declare @i　 int
　　　　　　　　　　　　　　　　　　　 set @i = 0
　　　　　　　　　　　　　　　　　　　 while @pos > 0
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 set @i = @i + 1
　　　 --select @i, @pos,@tpstr1,@tpstr4,@tpstr5
　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr4 = @tpstr4 + left(@tpstr1,@pos -1) + ' ,'
　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr1 = ltrim(right(@tpstr1,len(@tpstr1)-@pos))
　　　　　　　　　　　　　　　　　　　　　　　 set @pos = charindex('=',@tpstr1)
　　　　　　　　　　　　　　　　　　　　　　　 if @pos = 0
　　　　　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr5 = @tpstr5 + @tpstr1
　　　 --select @i,@pos,@tpstr1,@tpstr4,@tpstr5
　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr6 = reverse(left(@tpstr1,@pos - 1))
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @pos1 = charindex(',',@tpstr6)
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr5 = @tpstr5 + reverse(right(@tpstr6,len(@tpstr6)-@pos1 + 1)) + ' '
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @tpstr1 = ltrim(right(@tpstr1,len(@tpstr1)-@pos　 +　 @pos1 ))
　　　 --select @i,'',@tpstr1,@tpstr4,@tpstr5,@pos1,@tpstr6
　　　　　　　　　　　　　　　　　　　　　　　　　　　 set @pos = charindex('=',@tpstr1)
　　　　　　　　　　　　　　　　　　　　　　　 end
　　　 --select @i, @pos,@tpstr1,@tpstr4,@tpstr5
　　　　　　　　　　　　　　　　　　　 end
　　　 --select @definition_pre,@tpstr4
　　　　　　　　　　　　　　　　　　　 if len(@tpstr4)>0
　　　　　　　　　　　　　　　　　　　　　　　 set @definition_pre = @tpstr + ' ' + @tpstr5 + ' INTO ' + left(@tpstr4,len(@tpstr4)-1) + ' ' + @tpstr2
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --处理begin-end内的变量select as result
　　　　　　　　　　　　　　　 if ((@definition_pre like '%select%as%result%') or (@definition_pre like '%select%as%state%')) and (@definition_pre not like '%,%')
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @definition_pre = 'OPEN rs FOR ' + @definition_pre + ' FROM DUAL;'
　　　　　　　　　　　　　　　 end

　　　　　　　　　　　　　　　 --处理begin-end内的变量if exists select from
　　　　　　　　　　　　　　　 if @definition_pre like '%if%exists%select%from%'
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @pos = charindex('from',@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = 'SELECT COUNT(1) INTO v_count ' + right(@definition_pre,len(@definition_pre) - @pos + 1)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = rtrim(@definition_pre)
　　　　　　　　　　　　　　　　　　　 set @definition_pre = left(@definition_pre,len(@definition_pre) -1) + ';' + '　 ' + 'IF v_count > 0 '
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　 --为可能的完整语句添加分号
　　　　　　　　　　　　　　　 if (@definition_pre like '%values%(%)%')
　　　　　　　　　　　　　　　　　　　 or( @definition_pre like '%update%set%=%' and @definition_curline not like '%where%')
　　　　　　　　　　　　　　　　　　　 or( @definition_pre like '%delete%from%=%' and @definition_curline not like '%where%')
　　　　　　　　　　　　　　　　　　　 or( @definition_pre like '%order%by%')
　　　　　　　　　　　　　　　　　　　 or( @definition_pre like '%select%into%' and @definition_pre not like '%v_count%' and @definition_curline not like '%from%')
　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　 set @definition_pre = @definition_pre + ' ;'

--print @definition_pre
　　　　　　　　　　　　　　　 --如果该行语句有效，插入到表里，准备输出
　　　　　　　　　　　　　　　 if not @definition_pre is null
　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select @definition_pre
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 --取出下一行语句，如果是最后一句，直接插入到表里，准备输出
　　　　　　　　　　　　　　　 set @pos_char10 = charindex(nchar(10),@definition)
　　　　　　　　　　　　　　　 if @pos_char10 = 0 and len(@definition) > 0
　　　　　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 @definition

　　　　　　　　　　　　　　　 set @definition_preline = @definition_pre
　　　　　　　　　　　 end

　　　　　　　　　　　 --生成存储过程
　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 replicate(char(32),4) + 'EXCEPTION'
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 replicate(char(32),8) + 'WHEN OTHERS THEN'
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 ''　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 replicate(char(32),12) + 'PRINT(SQLERRM);'
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 replicate(char(32),12) + 'OPEN rs FOR SELECT 1 AS STATE FROM DUAL;'
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 ''
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 'END ' +　 left(@procedure_name,30) + ';'
　　　　　　　　　　　　　　　 insert into ##sp([definition]) select　 '/'
　　　　　　　　　　　 end
　　　　　　　　　　　 fetch next from cr_procedure_name into @procedure_name,@procedure_id

　　　　　　　 end

　　　　　　　 close cr_procedure_name
　　　　　　　 deallocate cr_procedure_name

　　　 end
end