PL/SQL编程时会有一些常用的处理函数，写成一个包

create or replace
PACKAGE BODY        "PKG_PUBLIC"
IS

    FUNCTION is_exists
    (
    cur IN SYS_REFCURSOR--游标类似于“select 1 from ....where...”
    )
    RETURN BOOLEAN
    IS
        l_dummy   PLS_INTEGER;
        b_bool    BOOLEAN;
    BEGIN
        FETCH cur INTO l_dummy;
        b_bool:=cur%FOUND;
        CLOSE cur;
        RETURN b_bool;
        EXCEPTION
            WHEN OTHERS THEN
                CLOSE cur;
    END is_exists;
   
   
    PROCEDURE log_error
    (
     I_object_name IN VARCHAR2,
     I_code        IN PLS_INTEGER,
     I_msg         IN VARCHAR2
    )
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO error_log( object_name ,
                               error_code,
                               error_text,
                               created_date,
                               created_by
                              )
        VALUES(I_object_name,
               I_code,
               I_msg,
               SYSDATE,
               USER);
        COMMIT;      
    END log_error;

--------------------------------
    --DESCRIPTION:将以逗号作为分隔符的字符串(末尾没有逗号)转为嵌套表
------------------------------------------
    FUNCTION fun_str_to_tab
    (I_str IN  VARCHAR2)
    RETURN tab_obj_str_to_tab
    IS
    v_tab tab_obj_str_to_tab:=tab_obj_str_to_tab();
    v_counter INTEGER:=0;
    v_str     VARCHAR2(32767):='';
    v_index  INT:=0;
    BEGIN
        v_str:=I_str||',';
        v_index:=INSTR(v_str,',');
   
        WHILE v_index > 0
        LOOP
            v_counter:=v_counter+1;
            v_tab.EXTEND;
            v_tab(v_counter):=obj_str_to_tab(SUBSTR(v_str,1,v_index-1));
            v_str:=SUBSTR(v_str,v_index+1);
            v_index:=INSTR(v_str,',');
        END LOOP;
       
        RETURN v_tab;
    END fun_str_to_tab;
   
 --   将以特定符号作为分隔符的字符串(末尾没有逗号)转为嵌套表
FUNCTION fun_str_to_tab_all
(I_character IN VARCHAR2,
 I_str IN  VARCHAR2
)
RETURN tab_obj_str_to_tab
IS
v_tab tab_obj_str_to_tab:=tab_obj_str_to_tab();
    v_counter INTEGER:=0;
    v_str     VARCHAR2(32767):='';
    v_index  INT:=0;
    BEGIN
        v_str:=I_str||I_character;
        v_index:=INSTR(v_str,I_character);
   
        WHILE v_index > 0
        LOOP
            v_counter:=v_counter+1;
            v_tab.EXTEND;
            v_tab(v_counter):=obj_str_to_tab(SUBSTR(v_str,1,v_index-1));
            v_str:=SUBSTR(v_str,v_index+1);
            v_index:=INSTR(v_str,I_character);
        END LOOP;
       
        RETURN v_tab;
END; 

-- 获取以特定符号作为分隔符的字符串的特定位置的字符（串）。
FUNCTION fun_str_to_tab_part
(I_character IN VARCHAR2,
 I_str IN  VARCHAR2,
 I_NUM IN NUMBER
)
RETURN VARCHAR2
IS
    v_counter INTEGER:=0;
    v_str     VARCHAR2(32767):='';
    v_index  INT:=0;
    v_return VARCHAR2(100) := '';
BEGIN
    v_str:=I_str||I_character;
    v_index:=INSTR(v_str,I_character);
   
    WHILE v_index > 0
        LOOP
            v_counter:=v_counter+1;
            IF v_counter = I_NUM THEN
                v_return:=SUBSTR(v_str,1,v_index-1);
                EXIT;
            END IF;
            v_str:=SUBSTR(v_str,v_index+1);
            v_index:=INSTR(v_str,I_character);
        END LOOP;
    RETURN  v_return;  
END;

/*
md5加密
*/ 
FUNCTION fn_md5 (input_string IN VARCHAR2)
RETURN VARCHAR2
IS
raw_input RAW (128) := UTL_RAW.cast_to_raw (input_string);
decrypted_raw RAW (2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
DBMS_OBFUSCATION_TOOLKIT.md5 (input => raw_input,
checksum => decrypted_raw
);
RETURN LOWER (RAWTOHEX (decrypted_raw));
END;

/*
CLOB字符串的转成数组
*/
FUNCTION fun_clob_to_tab(i_clob clob)
    RETURN tab_obj_str_to_tab
    IS
    v_tab tab_obj_str_to_tab:=tab_obj_str_to_tab();
    v_counter INTEGER:=0;
    v_str     clob;
    v_index  INT:=0;
    BEGIN
        v_str := i_clob;
        dbms_lob.append(v_str,',');
        v_index:=dbms_lob.INSTR(v_str,',');
        WHILE v_index > 0
        LOOP
            v_counter := v_counter+1;
            v_tab.EXTEND;
            v_tab(v_counter) := obj_str_to_tab(dbms_lob.SUBSTR(v_str,v_index-1));
            v_str  := dbms_lob.substr(v_str,dbms_lob.getlength(v_str),v_index+1);
            v_index := dbms_lob.INSTR(v_str,',');
        END LOOP;
       
        RETURN v_tab;
    END fun_clob_to_tab;
END pkg_public;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23065269/viewspace-713967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23065269/viewspace-713967/