Sql server 2005遷移至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%'
原文地址：http://www.cnblogs.com/jinzhenshui/articles/1360209.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-598135/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Sql server 2005遷移至Oracle系列之一：生成表SQLServerOracle
- Sql Server系列：儲存過程SQLServer儲存過程
- SQL SERVER 2005分頁儲存過程SQLServer儲存過程
- 【stswordman】SQL Server 2005: 儲存過程簽名SQLServer儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- Sql server 2005遷移至Oracle系列之三：在Oracle中建立sql中的常見函式SQLServerOracle函式
- SQL Server 2005的複製儲存過程選項BYSQLServer儲存過程
- Sql server 2005遷移至Oracle系列之五：角色、使用者、及許可權SQLServerOracle
- SQL Server基礎：儲存過程SQLServer儲存過程
- sql server儲存過程語法SQLServer儲存過程
- Sql server 2005遷移至Oracle系列之四：在Oracle中建立位或運算函式-bitorSQLServerOracle函式
- Q&A：在SQL Server 2005中編寫儲存過程RVSQLServer儲存過程
- SQL Server 儲存過程的運用SQLServer儲存過程
- SQL Server基礎之儲存過程SQLServer儲存過程
- (SQL Server)分頁的儲存過程SQLServer儲存過程
- 使用SQL SERVER儲存過程實現歷史資料遷移SQLServer儲存過程
- SQL 2000 2005通用儲存過程SQL儲存過程
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- SQL Server 禁用擴充套件儲存過程SQLServer套件儲存過程
- SQL Server 資料備份儲存過程SQLServer儲存過程
- ms sql server儲存過程目前使用模板SQLServer儲存過程
- SQL Server儲存過程中raiserror的使用SQLServer儲存過程AIError
- 使用SQL Server 2005的新函式構造分頁儲存過程SQLServer函式儲存過程
- sql server 2005常用的系統存貯過程SQLServer
- SER SERVER儲存過程Server儲存過程
- Oracle SQL Developer Debug儲存過程OracleSQLDeveloper儲存過程
- oracle動態sql儲存過程示例OracleSQL儲存過程
- SQL SERVER儲存過程AS和GO的含義SQLServer儲存過程Go
- Java呼叫SQL Server的儲存過程詳解JavaSQLServer儲存過程
- 【fosoyo】SQL Server儲存過程/函式加/解密SQLServer儲存過程函式解密
- 幾個實用SQL Server取儲存過程SQLServer儲存過程
- 使用SQL Server2005的新函式構造分頁儲存過程SQLServer函式儲存過程
- 生成sql server2000物件建立指令碼的儲存過程(轉)SQLServer物件指令碼儲存過程
- Oracle 通過儲存過程來生成CSV檔案Oracle儲存過程
- SQL Server 2005/2008 對With Encryption選項建立的儲存過程解密SQLServer儲存過程解密