作者leo_wyn·2020-04-28 17:07

商业智能工程师·Security

Data Migration Architecture of SSMA for Oracle

字数 2875阅读 1254941评论 0赞 0

几个通过SSMA 转换到 SQLSERVER中的ORACLE FUNCTION , 发现 SSMA 竟然使用了SP 重载 ！！！

create function [ssma_oracle].[db_error_get_oracle_exception_id]

(

@message nvarchar(4000),

@number int

)

returns nvarchar(4000)

begin

if(@number = 2627)

return N'ORA-00001'

if(@number = 8134)

return N'ORA-01476'

if(@number = 16915)

return N'ORA-06511'

if(@number = 16917)

return N'ORA-01001'

if(@number = 512)

return N'ORA-01422'

if(@number = 547)

return N'ORA-02291'

if(@number = 59999)

begin

declare @start int

set @start = CHARINDEX(N'[', @message)

if(@start > 0)

begin

declare @end int

set @end = CHARINDEX(N']', @message, @start)

if(@end > 0)

begin

return SUBSTRING(@message, @start + 1, @end - @start - 1)

end

return null

end

if (@number = 59998)

return @message

return null

end

/****** Object: UserDefinedFunction [ssma_oracle].[instr4_nvarchar] Script Date: 8/21/2018 3:13:47 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create FUNCTION [ssma_oracle].[instr4_nvarchar](@str1 as nvarchar(max), @str2 as nvarchar(max), @pos as int, @occurrence as int)

returns int

begin

if( @str1 is NULL) or (@str2 is NULL) or (@pos is NULL) or (@pos = 0) or (@occurrence is NULL) or ( @occurrence < 1 )

return NULL

if( @pos < 0 )

begin

while( ([ssma_oracle].[length_nvarchar](@str2)-@pos-1) <= [ssma_oracle].[length_nvarchar](@str1) )

begin

if( substring(@str1, [ssma_oracle].[length_nvarchar](@str1) + @pos + 1, [ssma_oracle].[length_nvarchar](@str2)) = @str2 )

begin

SET @occurrence = @occurrence-1

if @occurrence < 1

return [ssma_oracle].[length_nvarchar](@str1) + @pos + 1

end

SET @pos = @pos-1

end

return 0

end

SET @pos = @pos-1

while @occurrence > 0

begin

SET @pos = charindex(@str2, @str1, @pos+1)

if @pos = 0

return 0

SET @occurrence = @occurrence-1

end

return @pos

end

/****** Object: UserDefinedFunction [ssma_oracle].[length_nvarchar] Script Date: 8/21/2018 3:13:47 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create FUNCTION [ssma_oracle].[length_nvarchar](@s nvarchar(max))

returns int

begin

return len(replace(@s, ' ', '.'))

end

/****** Object: UserDefinedFunction [ssma_oracle].[substr2_nvarchar] Script Date: 8/21/2018 3:13:47 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create FUNCTION [ssma_oracle].[substr2_nvarchar](@src as nvarchar(max), @pos as int)

returns nvarchar(max)

begin

declare

@strlen as int

if( @src is NULL) or (@pos is NULL )

return NULL;

SET @strlen=[ssma_oracle].[length_nvarchar](@src)

if abs(@pos) > @strlen

return NULL;

if @pos = 0

SET @pos = 1

if @pos > 0

begin

return substring(@src, @pos, @strlen-@pos+1)

end

return substring(@src, @strlen + @pos+1, -@pos)

end

SQL Server

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Data Migration Architecture of SSMA for Oracle

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料