sybase---oracle2sybase procedure

最新推荐文章于 2021-02-24 11:27:38 发布

原创最新推荐文章于 2021-02-24 11:27:38 发布 · 131 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Sybase #Mobile #Oracle

Oracle 专栏收录该内容

37 篇文章

订阅专栏

本文介绍了一个从Oracle到Sybase的存储过程移植案例，该过程用于通过电话号码确定主叫者的地区。通过逐步检查不同长度的电话号码前缀，并从特定映射表中检索地区信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[code]
Oracle的存储过程

create or replace procedure PROC_GSLH_GETLINKERNAMEAREA
(
p_callerPhone VARCHAR2, --主叫号码
p_linkerArea out VARCHAR2 --主叫所属地区
)
as
m_callerPhone VARCHAR2( 20 ); --去掉前面0后的主叫号码
m_phonePrefix VARCHAR2( 20 ); --号码前缀，用来判断主叫所属地区
begin
--初始化数据
p_linkerArea := 'NULL';

--如果主叫前面有0，刚将0去掉
m_callerPhone := p_callerPhone;
if( substr( p_callerPhone, 1, 1 ) = '0' ) then
m_callerPhone := substr( p_callerPhone, 2, length( p_callerPhone ) );
end if;

--获取联系人所属地区
m_phonePrefix := substr( m_callerPhone, 1, 8 ); --移动号码号段(（8位）
begin
select MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;
exception when no_data_found then
p_linkerArea := 'NULL';
end;

if( p_linkerArea = 'NULL' ) then
m_phonePrefix := substr( m_callerPhone, 1, 7 ); --移动号码号段(（7位）
begin
select MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;
exception when no_data_found then
p_linkerArea := 'NULL';
end;
end if;

if( p_linkerArea = 'NULL' ) then
m_phonePrefix := substr( m_callerPhone, 1, 3 ); --固话区号
begin
select MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;
exception when no_data_found then
p_linkerArea := 'NULL';
end;
end if;

if( p_linkerArea = 'NULL' ) then
m_phonePrefix := substr( m_callerPhone, 1, 2 ); --固话区号
begin
select MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;
exception when no_data_found then
p_linkerArea := 'NULL';
end;
end if;
end;
/

变成sybase:

CREATE PROCEDURE PROC_GSLH_GETLINKERNAMEAREA
(
@p_callerPhone VARCHAR, --主叫号码
@p_linkerArea VARCHAR output --主叫所属地区
)
as
BEGIN

declare @m_callerPhone VARCHAR( 20 ), --去掉前面0后的主叫号码
@m_phonePrefix VARCHAR( 20 ) --号码前缀，用来判断主叫所属地区
--初始化数据
select @p_linkerArea = 'NULL'

--如果主叫前面有0，刚将0去掉
select @m_callerPhone = @p_callerPhone

if SUBSTRING(@p_callerPhone,1,1) = '0'
select @m_callerPhone = SUBSTRING(@p_callerPhone,2,CHAR_LENGTH(@p_callerPhone))

--获取联系人所属地区
select @m_phonePrefix = SUBSTRING( @m_callerPhone, 1, 8 ) --移动号码号段(（8位）
begin
select @p_linkerArea=MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix
end
if not exists(select MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix)

begin
select @p_linkerArea = 'NULL'

end

if @p_linkerArea = 'NULL'
select @m_phonePrefix = SUBSTRING( @m_callerPhone, 1, 7 ) --移动号码号段(（7位）
begin
select @p_linkerArea=MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix
end
if not exists ( select MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix)
begin
select @p_linkerArea = 'NULL'
end

if @p_linkerArea = 'NULL'
select @m_phonePrefix = SUBSTRING( @m_callerPhone, 1, 3 ) --固话区号
begin
select @p_linkerArea=MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix
end
if not exists(select MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix)
begin
select @p_linkerArea = 'NULL'
end

if @p_linkerArea = 'NULL'
select @m_phonePrefix = SUBSTRING( @m_callerPhone, 1, 2 ) --固话区号
begin
select @p_linkerArea=MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix
end
if not exists(select MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix)
begin
select @p_linkerArea='NULL'
end
end

[/code]