注意：本脚本只是完成了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%'