sybase---oracle2sybase procedure
Oracle的存储过程create or replace procedure PROC_GSLH_GETLINKERNAMEAREA(p_callerPhoneVARCHAR2,--主叫号码p_linkerAreaout VARCHAR2--主叫所属地区)asm_callerPhoneVARCHAR2( 20 );--去掉前面0后的主叫号码m_phonePrefixVARCHAR2( 20 );--号码前缀，用来判断主叫所属地区begin--初始化数据p_linkerArea := 'NULL';--如果主叫前面有0，刚将0去掉m_callerPhone := p_callerPhone;if( substr( p_callerPhone, 1, 1 ) = '0' ) thenm_callerPhone := substr( p_callerPhone, 2, length( p_callerPhone ) );end if;--获取联系人所属地区m_phonePrefix := substr( m_callerPhone, 1, 8 );--移动号码号段(（8位）beginselect MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;exception when no_data_found thenp_linkerArea := 'NULL';end;if( p_linkerArea = 'NULL' ) thenm_phonePrefix := substr( m_callerPhone, 1, 7 );--移动号码号段(（7位）beginselect MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;exception when no_data_found thenp_linkerArea := 'NULL';end;end if;if( p_linkerArea = 'NULL' ) thenm_phonePrefix := substr( m_callerPhone, 1, 3 );--固话区号beginselect MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;exception when no_data_found thenp_linkerArea := 'NULL';end;end if;if( p_linkerArea = 'NULL' ) thenm_phonePrefix := substr( m_callerPhone, 1, 2 );--固话区号beginselect MM_FREE into p_linkerArea from AAA_MOBILE_MAP where MM_MOBILE = m_phonePrefix;exception when no_data_found thenp_linkerArea := 'NULL';end;end if;end;/ 变成sybase:CREATE PROCEDURE PROC_GSLH_GETLINKERNAMEAREA(@p_callerPhone VARCHAR,--主叫号码@p_linkerArea VARCHAR output--主叫所属地区)as BEGIN declare @m_callerPhoneVARCHAR( 20 ), --去掉前面0后的主叫号码 @m_phonePrefixVARCHAR( 20 ) --号码前缀，用来判断主叫所属地区 --初始化数据 select @p_linkerArea = 'NULL' --如果主叫前面有0，刚将0去掉 select @m_callerPhone = @p_callerPhoneif 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 endif not exists(select MM_FREE from AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix) begin select @p_linkerArea = 'NULL' endif@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 endif 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_FREEfrom AAA_MOBILE_MAP where MM_MOBILE = @m_phonePrefix end if not exists(select MM_FREEfrom 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' endend
页:
[1]