填充指定字符串的每一节到指定长度，不够的在左恻用0补足，并将分隔符"-"去除，可用于排序的函数

最新推荐文章于 2022-04-14 18:40:58 发布

ccsky001

最新推荐文章于 2022-04-14 18:40:58 发布

阅读量1.5k

点赞数

分类专栏： JAVA编程文章标签： c null fun function

本文链接：https://blog.csdn.net/ccsky001/article/details/1836593

版权

JAVA编程专栏收录该内容

28 篇文章 0 订阅

订阅专栏

create or replace function Fun_Digit_Replace(P_C_InStr varchar2 ,P_C_SepStr varchar2 ,P_N_LenPerSite number)
return varchar2 is
/*==========================================================================

   功能：填充指定字符串的每一节到指定长度，不够的在左恻用0补足，并将分隔符去除

============================================================================*/
    Result        varchar2(150);
    V_N_Site1     number;
    V_N_Site2     number;
    V_C_InStr     varchar2(150);
    V_C_LeftStr   varchar2(150);
    V_C_RightStr varchar2(150);
    V_C_TempStr   varchar2(150);
    begin

Result := null;
V_C_InStr := P_C_InStr;

    if P_C_InStr is null or length(P_C_InStr) < 1 then
       return Result;
    end if;

    if P_C_InStr is null or length(P_C_InStr) < 1 then
       return P_C_InStr;
    end if;

    IF P_N_LenPerSite IS NULL OR P_N_LenPerSite < 1 THEN
       return Result;
    END IF;
dbms_output.put_line(V_C_InStr);
dbms_output.put_line(P_N_LenPerSite);
    LOOP
        EXIT WHEN INSTR(V_C_InStr,P_C_SepStr) < 1;
        V_C_LeftStr   := substr(V_C_InStr,1,INSTR(V_C_InStr,P_C_SepStr)-1);
        V_C_TempStr := null;
        select substr(V_C_InStr
                               ,INSTR(V_C_InStr,P_C_SepStr) + Length(P_C_SepStr)
                               ,Decode( INSTR(V_C_InStr,P_C_SepStr,1,2)
                                      ,0
                                       ,Length(V_C_InStr) + 1
                                       ,INSTR(V_C_InStr,P_C_SepStr,1,2) - INSTR(V_C_InStr,P_C_SepStr) - 1
                                       )
                               ) into V_C_TempStr
          from dual;

        V_C_RightStr := null;
        select substr(V_C_InStr
                               ,Decode( INSTR(V_C_InStr,P_C_SepStr,1,2)
                                       ,0
                                       ,Length(V_C_InStr) + 1
                                       ,INSTR(V_C_InStr,P_C_SepStr,1,2)
                                      ) --+ Length(P_C_SepStr)
                               ,Length(V_C_InStr)
                               ) into V_C_RightStr
          from dual;

        if V_C_TempStr is null or Length(V_C_TempStr) < P_N_LenPerSite then
           if V_C_TempStr is null then
              V_C_TempStr := Substr( '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 '
                                   ,1
                                   ,P_N_LenPerSite);
            else
              V_C_TempStr := Substr( '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 '
                                   ,1
                                   ,P_N_LenPerSite - Length(V_C_TempStr)
                                   )||V_C_TempStr;
           end if;
        end if;

        if V_C_LeftStr is null then
           V_C_LeftStr := V_C_TempStr;
         else

           if Length(V_C_LeftStr) < P_N_LenPerSite then
              V_C_LeftStr := Substr( '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 '
                                   ,1
                                   ,P_N_LenPerSite - Length(V_C_LeftStr)
                                   ) || V_C_LeftStr;
           end if;

V_C_LeftStr := V_C_LeftStr || V_C_TempStr;

end if;

        if V_C_RightStr is null then
           V_C_LeftStr := V_C_LeftStr;
         else
           V_C_LeftStr := V_C_LeftStr || V_C_RightStr;
        end if;

V_C_InStr := V_C_LeftStr;

END LOOP;

    if Length(V_C_InStr) < P_N_LenPerSite then
      V_C_InStr := Substr( '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 '
                           ,1
                           ,P_N_LenPerSite - Length(V_C_InStr)
                           ) || V_C_InStr;
    end if;
   return V_C_InStr;

end Fun_Digit_Replace;