oracle 函数的定义,oracle自定义函数

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;

/