Oracle 自定义函数

oracle自定义函数 (2012-12-25 15:36:01）

oracle自定义函数 (整理的几个可能平时会用到)
注：有些函数功能很简单，就是转换成Oracle的过程里面。。。

CREATE OR REPLACE Package PKG_COMMON IS

FUNCTION F_Str_Split (in_text IN VARCHAR2, in_delimiter IN VARCHAR2) RETURN TYPE_STR;

FUNCTION F_Str_Have_Chinese(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Have_Numeric(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Have_English(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Is_Numeric(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Is_Date(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Is_Phone(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Is_Mail(in_text IN VARCHAR2) RETURN number;

FUNCTION F_Str_Get_NongLi(i_SolarDay DATE) RETURN VARCHAR2;

FUNCTION F_Str_Get_SubstrCount(in_text IN VARCHAR2,in_substr IN VARCHAR2) RETURN NUMBER;

FUNCTION F_Str_Get_SubstrValue(in_text IN VARCHAR2,in_separator IN VARCHAR2,in_times1 IN NUMBER,in_times2 IN NUMBER) RETURN VARCHAR2;

FUNCTION F_Str_Get_GUID RETURN VARCHAR2;

FUNCTION F_Str_Get_Prime(in_num IN NUMBER) RETURN TYPE_INT;

FUNCTION F_Str_Order (in_text IN VARCHAR2,in_delimiter IN VARCHAR2,in_order IN VARCHAR2) RETURN VARCHAR2;
End PKG_COMMON;
/
CREATE OR REPLACE Package body PKG_COMMON IS

FUNCTION F_Str_Split (in_text IN VARCHAR2, in_delimiter IN VARCHAR2) RETURN TYPE_STR

IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (30000);
str_split TYPE_STR := TYPE_STR ();

BEGIN

len := LENGTH (in_text);
len1 := LENGTH (in_delimiter);

WHILE j < len
LOOP
j := INSTR (in_text, in_delimiter, i);

IF j = 0
THEN
    j := len;
    str := SUBSTR (in_text, i);
    str_split.EXTEND;
    str_split (str_split.COUNT) := str;

    IF i >= len
    THEN
      EXIT;
    END IF;
ELSE
    str := SUBSTR (in_text, i, j - i);
    i := j + len1;
    str_split.EXTEND;
    str_split (str_split.COUNT) := str;
END IF;

END LOOP;

RETURN str_split;
END F_Str_Split;

FUNCTION F_Str_Have_Chinese(in_text IN VARCHAR2) RETURN number

IS

BEGIN
IF LENGTH(in_text)=LENGTHB(in_text) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END F_Str_Have_Chinese;

FUNCTION F_Str_Have_Numeric(in_text IN VARCHAR2) RETURN number
IS

l_num number;

BEGIN
SELECT REGEXP_INSTR (in_text,’[0-9]+’) INTO l_num FROM DUAL;
IF l_num=0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END F_Str_Have_Numeric;

FUNCTION F_Str_Have_English(in_text IN VARCHAR2) RETURN number
IS

l_num number;

BEGIN
SELECT REGEXP_INSTR (lower(in_text),’[a-z]+’) INTO l_num FROM DUAL;
IF l_num=0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END F_Str_Have_English;

FUNCTION F_Str_Is_Numeric(in_text IN VARCHAR2) RETURN number
IS
l_str varchar2(10);

BEGIN
BEGIN
SELECT * INTO l_str FROM dual where regexp_like(in_text, ‘^[0123456789]+$’);
RETURN 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 1;
END;

END F_Str_Is_Numeric;

FUNCTION F_Str_Is_Date(in_text IN VARCHAR2) RETURN number
IS
val DATE;

BEGIN
val := TO_DATE(NVL(in_text, ‘a’), ‘yyyy-mm-dd hh24:mi:ss’);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END F_Str_Is_Date;

FUNCTION F_Str_Is_Phone(in_text IN VARCHAR2) RETURN number
IS
l_begin varchar2(1);
l_str varchar2(2);
l_length number;
l_number number;

BEGIN
l_number:=F_Str_Is_Numeric(in_text);
l_begin:=substr(in_text,1,1);
l_length:=length(in_text);

   IF l_number =0 THEN
        IF l_begin =1  THEN  --1开头的数字可能是手机号码
               IF LENGTH(in_text)=11 THEN   --长度为11位,继续判断
                    BEGIN
                        SELECT  * INTO l_str FROM dual where REGEXP_LIKE(in_text,'^(134|135|136|137|138|139|147|150|151|152|157|158|159|182|187|188)') ;
                        RETURN 4;        
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN                        
                               BEGIN
                                        SELECT  * INTO l_str FROM dual where REGEXP_LIKE(in_text,'^(130|131|132|155|156|185|186)') ;
                                        RETURN 5;        
                               EXCEPTION
                                    WHEN NO_DATA_FOUND THEN                        
                                        BEGIN
                                                SELECT  * INTO l_str FROM dual where REGEXP_LIKE(in_text,'^(133|180|189)') ;
                                                RETURN 6;        
                                        EXCEPTION
                                            WHEN NO_DATA_FOUND THEN                        
                                                RETURN 2;              
                                        END;                  
                               END;               
                    END;
               ELSE --长度没有11位,非法手机号码
                        RETURN 2;   
               END IF;

        ELSIF l_begin<>1 AND (l_length=7 OR l_length=8 OR l_length=11 OR l_length=12) THEN--可能是固定电话                  
              RETURN 7;    
        ELSE
              RETURN 3;                        
        END IF;
   ELSE  --含有非数字
        RETURN 1;
   END IF;                    

END F_Str_Is_Phone;

FUNCTION F_Str_Is_Mail(in_text IN VARCHAR2) RETURN number
IS
l_str varchar2(10);

BEGIN
BEGIN
SELECT * INTO l_str FROM dual where REGEXP_LIKE(in_text,’^\w+((-\w+)|(.\w+))\@[A-Za-z0-9]+((.|-)[A-Za-z0-9]+).[A-Za-z0-9]+$’);
RETURN 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 1;
END;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END F_Str_Is_Mail;

FUNCTION F_Str_Get_NongLi(i_SolarDay DATE) RETURN VARCHAR2
AS

v_OffSet INT;
v_Lunar INT; – 农历年是否含闰月,几月是闰月,闰月天数,其它月天数
v_YearDays INT; – 农历年所含天数
v_MonthDays INT; – 农历月所含天数
v_LeapMonthDays INT; – 农历闰月所含天数
v_LeapMonth INT; – 农历年闰哪个月 1-12 , 没闰传回 0
v_LeapFlag INT; – 某农历月是否为闰月 1:是 0:不是
v_MonthNo INT; – 某农历月所对应的2进制数 如农历3月: 001000000000
i INT;
j INT;
k INT;
v_Year INT; – i_SolarDay 对应的农历年
v_Month INT; – i_SolarDay 对应的农历月
v_Day INT; – i_SolarDay 对应的农历日

v_temp VARCHAR2(25);

o_OutputDate VARCHAR2(25); – 返回值 格式：农历 *年 (闰)月 *日

e_ErrMsg VARCHAR2(200);
e_ErrDate EXCEPTION;

BEGIN

    --输入参数判断
    IF i_SolarDay=TO_DATE('2050-01-23','YYYY-MM-DD') THEN
        RAISE e_ErrDate; 
    END IF ;

    -- i_SolarDay 到 1900-01-30(即农历1900-01-01的前一天) 的天数
    v_OffSet := TRUNC(i_SolarDay, 'DD') - TO_DATE('1900-01-30', 'YYYY-MM-DD');

    -- 确定农历年开始
    i := 1900;
    WHILE i < 2050 AND v_OffSet > 0 LOOP
        v_YearDays := 348;    --  29*12 以每年12个农历月,每个农历月含29个农历日为基数
        v_LeapMonthDays := 0;

        -- 取出农历年是否含闰月,几月是闰月,闰月天数,其它月天数
        -- 如农历2001年: 0x0d954(16进制) -> 55636(10进制) -> 0 110110010101 0100(2进制)
        -- 1,2,4,5,8,10,12月大, 3,6,7,9,11月小, 4月为闰月，闰月小
        SELECT Data10 INTO v_Lunar FROM T_BASE_SolarData WHERE YearId = i;

        -- 传回农历年的总天数
        j := 32768;

        -- 依次判断v_Lunar年个月是否为大月，是则加一天 
        WHILE j > 8 LOOP       -- 闰月另行判断 8 -> 0 000000000000 1000    

            IF BITAND(v_Lunar, j) + 0 > 0 then
                v_YearDays := v_YearDays + 1; 
            END IF;            
           j := j/2;            -- 判断下一个月是否为大

        END LOOP;

        -- 传回农历年闰哪个月 1-12 , 没闰传回 0   15 -> 1 0000
        v_LeapMonth := BITAND(v_Lunar, 15) + 0; 
        -- 传回农历年闰月的天数 ,加在年的总天数上
        IF v_LeapMonth > 0 THEN

            -- 判断闰月大小 65536 -> 1 000000000000 0000 
            IF BITAND(v_Lunar, 65536)+0 > 0 THEN
                v_LeapMonthDays := 30;
            ELSE 
                v_LeapMonthDays := 29;
            END IF;
            v_YearDays := v_YearDays + v_LeapMonthDays;
        END IF;

        v_OffSet := v_OffSet - v_YearDays; 
        i := i + 1;

    END LOOP;

    IF v_OffSet <= 0 THEN
        -- i_SolarDay 在所属农历年(即i年)中的第 v_OffSet 天 
        v_OffSet := v_OffSet + v_YearDays;  
        i := i - 1;

    END IF;

    -- 确定农历年结束
    v_Year := i;

    -- 确定农历月开始
    i := 1;

    SELECT Data10 INTO v_Lunar FROM T_BASE_SolarData WHERE YearId = v_Year; 

    -- 判断那个月是润月
    -- 如农历2001年 (55636,15 -> 0 1101100101010100, 1111 -> 4) 即润4月,且闰月小
    v_LeapMonth := BITAND(v_Lunar, 15) + 0; 
    v_LeapFlag := 0;

    WHILE i < 13 AND v_OffSet > 0 LOOP 
        -- 判断是否为闰月
        v_MonthDays := 0;
        IF (v_LeapMonth > 0 AND i = (v_LeapMonth + 1) AND v_LeapFlag = 0) THEN 
            -- 是闰月
            i := i - 1;
            k := i;                -- 保存是闰月的时i的值
            v_LeapFlag := 1;

            -- 传回农历年闰月的天数
            IF BITAND(v_Lunar, 65536)+0 > 0 THEN
                v_MonthDays := 30;
            ELSE
                v_MonthDays := 29; 
            END IF;    
        ELSE
            -- 不是闰月
            j := 1;
            v_MonthNo := 65536;
            -- 计算 i 月对应的2进制数 如农历3月: 001000000000
            WHILE j<= i LOOP
                v_MonthNo := v_MonthNo/2; 
                j := j + 1;
            END LOOP;

            -- 计算农历 v_Year 年 i 月的天数
            IF BITAND(v_Lunar, v_MonthNo)+0 > 0 THEN
                v_MonthDays := 30;
            ELSE
                v_MonthDays := 29;
            END IF;
        END IF;

        -- 解除闰月 
        IF v_LeapFlag = 1 AND i = v_LeapMonth +1 THEN
            v_LeapFlag := 0;
        END IF;

        v_OffSet := v_OffSet - v_MonthDays;
        i := i + 1;

    END LOOP;

    IF v_OffSet <= 0 THEN
        -- i_SolarDay 在所属农历月(即i月)中的第 v_OffSet 天 
        v_OffSet := v_OffSet + v_MonthDays;
        i := i - 1;
    END IF;

    -- 确定农历月结束
    v_Month := i;

    -- 确定农历日结束
    v_Day := v_OffSet;

    -- 格式化返回值
    o_OutputDate := TO_CHAR(v_Year)||'年';

    IF k = i THEN
        o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month), 2, '0');
    ELSE
        o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month), 2, '0'); 
    END IF;

    o_OutputDate := o_OutputDate||'月' || LPAD(TO_CHAR(v_Day), 2, '0');
    RETURN o_OutputDate;

EXCEPTION

WHEN e_Errdate THEN

RETURN '日期错误! 有效范围(阳历): 1900/01/31 - 2050/01/22';

WHEN OTHERS THEN

e_ErrMsg :=SUBSTR(SQLERRM,1,200);

RETURN e_ErrMsg;

END;

FUNCTION F_Str_Get_SubstrCount(in_text IN VARCHAR2,in_substr IN VARCHAR2) RETURN NUMBER
IS
l_count number :=0;
BEGIN
SELECT LENGTH(REGEXP_REPLACE(REPLACE(in_text, in_substr, ‘@’), ‘[^@]+’, ”)) COUNT INTO l_count FROM DUAL;
IF l_count IS NULL THEN
RETURN 0;
ELSE
RETURN l_count;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;

END F_Str_Get_SubstrCount;

FUNCTION F_Str_Get_SubstrValue(in_text IN VARCHAR2,in_separator IN VARCHAR2,in_times1 IN NUMBER,in_times2 IN NUMBER) RETURN VARCHAR2
IS
l_total number;
l_begin number;
l_end number;

BEGIN
l_total:=F_Str_Get_SubstrCount(in_text,in_separator);
IF in_times1<=0 THEN
l_begin:=0;
ELSE
SELECT INSTR(in_text,in_separator,1,in_times1)+1 INTO l_begin FROM DUAL;
END IF;

    IF in_times2>l_total THEN
        l_end:=LENGTH(in_text);
    ELSE
        SELECT INSTR(in_text,in_separator,1,in_times2) INTO l_end FROM DUAL;
    END IF;

    RETURN SUBSTR(in_text,l_begin,(l_end-l_begin));

END F_Str_Get_SubstrValue;

FUNCTION F_Str_Get_GUID RETURN VARCHAR2
IS
l_guid varchar2(40);
BEGIN
select sys_guid() into l_guid from dual;
return l_guid;

END F_Str_Get_GUID;

FUNCTION F_Str_Get_Prime(in_num IN NUMBER) RETURN TYPE_INT
IS
my_arr_prime TYPE_INT:=TYPE_INT();

n_cur_mult number(10):=1;
n_prime_1 number(10):=1;
n_prime_2 number(10):=2;
n_max number(10):= floor((in_num+1)/6);
n_arr_ind number(10):=1;
b_prime number(1):=0;
n_arr_max number(10):=2;
begin
my_arr_prime.EXTEND;
my_arr_prime(1):=2;
my_arr_prime.EXTEND;
my_arr_prime(2):=3;
for i in 1..n_max loop
n_prime_1 := i*6-1;
n_prime_2 := i*6+1;
n_cur_mult := ceil(sqrt(n_prime_2));

 for j in 1..2 loop
      if j =2 then
        n_prime_1:= n_prime_2;
      end if;

     if n_prime_1 > in_num then
        exit;
      end if;
      n_arr_ind:=1;
      b_prime :=0;
      while n_arr_ind <= n_arr_max and my_arr_prime(n_arr_ind)<= n_cur_mult loop
        if mod(n_prime_1, my_arr_prime(n_arr_ind)) =0 then
           b_prime :=1;
           exit;
        end if;
        n_arr_ind := n_arr_ind+1;
      end loop;

     if b_prime = 0 then
         n_arr_max := n_arr_max+1;
         my_arr_prime.EXTEND;
         my_arr_prime(n_arr_max) := n_prime_1;

     end if;
  end loop;

end loop;

RETURN my_arr_prime;

END F_Str_Get_Prime;

FUNCTION F_Str_Order (in_text IN VARCHAR2,in_delimiter IN VARCHAR2,in_order IN VARCHAR2) RETURN VARCHAR2
IS
l_result varchar2(4000);

BEGIN

    IF in_order='0' THEN
        SELECT WMSYS.WM_CONCAT(id) INTO l_result 
        FROM 
        (
            SELECT substr(a, (instr(a, in_delimiter, 1,LEVEL) + 1),  (instr(a, in_delimiter, 1,LEVEL+1) - (instr(a, in_delimiter, 1,LEVEL) + 1)) ) id
            FROM 
            (
                SELECT in_delimiter||in_text||in_delimiter AS a  FROM DUAL 
            )  CONNECT  BY  LEVEL <= (length(a) - nvl(length(REPLACE(a, in_delimiter, '')), 0) -1)
            ORDER BY to_number(ID) ASC 
        );
    ELSE
        SELECT WMSYS.WM_CONCAT(id) INTO l_result 
        FROM 
        (
            SELECT substr(a, (instr(a, in_delimiter, 1,LEVEL) + 1),  (instr(a, in_delimiter, 1,LEVEL+1) - (instr(a, in_delimiter, 1,LEVEL) + 1)) ) id
            FROM 
            (
                SELECT in_delimiter||in_text||in_delimiter AS a  FROM DUAL 
            )  CONNECT  BY  LEVEL <= (length(a) - nvl(length(REPLACE(a, in_delimiter, '')), 0) -1)
            ORDER BY to_number(ID) DESC 
        );
    END IF;

    RETURN l_result;

END F_Str_Order;

End PKG_COMMON;
/