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

注意：本脚本只是完成了80%左右的转换工作，还需要对转换后的存储过程进行处理。其目的在于保留sqlserver的基本逻辑机构，将繁琐的重复工作解放出来

--宁可不处理也不要错误处理，尤其是避免删除处理;尽量不改变原结构，利于oraclez中的可视化处理！！！
--2008-10-23重新构建转换脚本，仅生成存储过程
--2008-10-23完善基本的转换结构。去掉对exists的处理，增加exception处理
--2008-10-27去掉对字段的加“"”处理，只处理[uid]字段
--2008-10-27增加了datediff、str、datepart函数
--2008-10-27增加了对%else if%的后续处理%elsif%
--2008-10-28增加了stuff、substring函数
--2008-10-28修正了charindex函数
--2008-10-28将存储过程的说明包含进oralce的创建说明里
--2008-10-29修正对uid和set两个字符串的错误处理
--2008-10-29完善对select @v = val from 的处理
--2008-10-29增加了bitor函数
--2008-10-29增加了对index关键字的处理
--2008-10-29完善处理“begin-end内的SET @ErrorRemark”对as关键字的处理
--2008-10-29增加了为可能的完整语句添加分号
--2008-10-30完善对select @v = val from 的处理
--2008-10-30增加了host_name(),suser_sname(),app_name()三个安全函数的虚定义
--2008-10-30完善处理%set %（赋值）
--2008-10-30完善处理return，添加上";"语句结束符
--2008-10-31增加了replicate函数
--2008-10-31完善end加";"的bug，该bug可能造成创建存储过程失败！
--2008-10-31完善对存储过程声明部分as标识的处理的bug，该bug可能造成创建存储过程失败、或丢失数据！
--2008-11-2增加了day,year,month函数
--2008-11-4增加了对mode，type关键字的处理
--2008-11-4增加了print过程
--2008-11-4为最后select结果 增加order by id排序功能，主要是发现sqlserver在无order by的情况下 select输出并不是严格按照表的插入顺序！！
--2008-11-13修正对select @v = val from 的处理的一处bug
--2008-11-13在存储过程定义的异常处理部分加入 ‘PRINT(SQLERRM)’;

set nocount on
--declare变量
begin
    declare @iscreateprocedure    int                --是否生成存储过程(0:no,1:yes)
        set @iscreateprocedure = 1
    declare @iscreatefunction    int                --是否生成函数(0:no,1:yes)--尚未实现
        set @iscreatefunction = 0
    declare @name                sysname            --生成存储过程的名称，支持通配符%
        set @name = 'job%'
    declare @proc_name            sysname
    declare @para_name            sysname
    declare @type_name            sysname
    declare @length                bigint
    declare @is_output            int
    declare @default_value        sql_variant

    declare @oratypename        sysname
    declare @col_define            nvarchar(4000)

    declare @procedure_name        sysname
    declare @procedure_id        int
    declare @definition            nvarchar(max)
    declare @definition_pre        nvarchar(4000)    --处理过程中指 当前行
    declare @definition_preline    nvarchar(4000)    --处理过程中指 上一行
    declare @definition_curline    nvarchar(4000)    --处理过程中指 下一行
    declare @var                sysname
    declare @vars                sysname
    declare @pos_char10            int    --'\l'
    declare @pos_char101        int    --'\l'
    declare @isdeclare            int
   
    declare @tpstr                nvarchar(4000)
    declare @tpstr1                nvarchar(4000)
    declare @tpstr2                nvarchar(4000)
    declare @tpstr3                nvarchar(4000)
    declare @tpstr4                nvarchar(4000)
    declare @tpstr5                nvarchar(4000)
    declare @tpstr6                nvarchar(4000)
    declare @pos                int
    declare @pos1                int
    declare @pos2                int
    declare @pos3                int

end

--创建临时表
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



--进一步处理，注意各步骤的顺序不可随意调换
begin
    --处理tab（换成空格）   
    update ##sp set [definition] = replace(replace(replace([definition],char(9),replicate(char(32),4)),char(10),char(32)),char(13),char(32))
    --处理return
    update ##sp set [definition] = [definition] + ';'  where rtrim(ltrim([definition])) like 'return%'
    --处理%create%procedure%
    update ##sp set [definition] = replace([definition],'procedure','OR REPLACE PROCEDURE') where [definition] like '%create%procedure%'
    --处理%exec%
    update ##sp set [definition] = 'null;--' + replace(replace([definition],'execute ',' '),'exec ',' ')  where [definition] like '%exec%'
    --处理%end%
    update ##sp set [definition] = [definition] + ';' where ltrim(rtrim([definition])) = 'end'
    --处理%set%nocount%
    update ##sp set [definition] = '' where [definition] like '%set%nocount%'
    --处理%insert%values%（补足";"号）
    update ##sp set [definition] = [definition] + ';' where [definition] like '%insert%values%'
    --处理%if %（补足then）
    update ##sp set [definition] = [definition] + ' then' where [definition] like '%if %'
    --处理%else if%
    update ##sp set [definition] = replace([definition],'else if','elsif') where [definition] like '%else if%'
    --处理%declare %
    update ##sp set [definition] = replace([definition],'declare','') where [definition] like '%declare %'
    --替换不规范关键字' uid,'、',uid'、' uid '
    update ##sp set [definition] = replace(replace(replace([definition],' uid ',' "UID" '),',uid ',',"UID" '),' uid,',' "UID",')
    --替换不规范关键字' index,'、',index'、' index '
    update ##sp set [definition] = replace(replace(replace([definition],' index ',' "INDEX" '),',index ',',"INDEX" '),' index,',' "INDEX",')
    --替换不规范关键字' mode,'、',mode'、' mode '
    update ##sp set [definition] = replace(replace(replace([definition],' mode ',' "MODE" '),',mode ',',"MODE" '),' mode,',' "MODE",')
    --替换不规范关键字' type,'、',type'、' type '
    update ##sp set [definition] = replace(replace(replace([definition],' type ',' "TYPE" '),',type ',',"TYPE" '),' type,',' "TYPE",')

    --替换非法字母%@%（为v_）
    update ##sp set [definition] = replace([definition],'@','v_') where [definition] like '%@%'
    --处理%ErrorRemark%
    update ##sp set [definition] = replace(replace(replace([definition],'+','||'),'=',':='),'set','') + ';' where [definition] like '%ErrorRemark%'
    --处理';;'
    update ##sp set [definition] = replace(replace([definition],';;',';'),';;',';') where [definition] like '%;;%'
    --处理连续空格、标点符号空格
    update ##sp set [definition] = replace([definition],' ;',';')
    update ##sp set [definition] = replace([definition],' ,',',')
    update ##sp set [definition] = replace([definition],'-- ','--')

    --删除空行
    delete from ##sp where [definition] = ''
    --删除--行注释
    --delete from ##sp where [definition] like '--%'
end


--将语句变为小写
--update ##sp set [definition] = upper([definition])

--输出结果
select ([definition]) + '' from ##sp order by id
--where [definition] like '%select%v_%=%' and [definition] not like '%where%'