oracle数据库常用的一些自定义函数

最新推荐文章于 2023-09-04 21:08:00 发布

原创最新推荐文章于 2023-09-04 21:08:00 发布 · 737 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #date #exception #c #null

本文提供了一系列实用的数据库函数，包括二进制、十六进制与十进制之间的转换，两个数字的最大值与最小值选取，判断是否为数字，以及两个日期间的时间差计算等。

1、二进制转十进制的函数create or replace FUNCTION "BINTOTEN" (strbin in varchar2) return NUMBER IS decvalue NUMBER(10); binlen NUMBER(10); indexa NUMBER(10); currbit CHAR; BEGIN binlen := LENGTH(strbin); decvalue := 0; indexa := 0; WHILE indexa < binlen LOOP indexa := indexa + 1; currbit := SUBSTR(strbin, indexa, 1); IF (currbit != '0' AND currbit != '1') THEN decvalue := -1; ELSE decvalue := decvalue * 2; IF(currbit = '1') THEN decvalue := decvalue + POWER(2, 0); END IF; END IF; END LOOP; RETURN decvalue; END BINTOTEN;

2、2个时间段的时间差的函数create or replace FUNCTION DATEDIFF (date1 IN DATE,date2 IN DATE) RETURN NUMBER IS datesx NUMBER(10); secondsx NUMBER(20); stra VARCHAR2(19); strb VARCHAR2(19); hh NUMBER(2); mi NUMBER(2); ss NUMBER(2); BEGIN stra := to_char(date1,'yyyy-mm-dd hh24:mi:ss'); -- 2010-12-12 00:00:00 strb := to_char(date2,'yyyy-mm-dd hh24:mi:ss'); hh := cast(substr(stra,12,2) as NUMBER) - cast(substr(strb,12,2) as NUMBER); mi := cast(substr(stra,15,2) as NUMBER) - cast(substr(strb,15,2) as NUMBER); ss := cast(substr(stra,18,2) as NUMBER) - cast(substr(strb,18,2) as NUMBER); datesx := (to_date(substr(stra,1,10),'yyyy-mm-dd') - to_date(substr(strb,1,10),'yyyy-mm-dd')); IF(datesx = 0) THEN secondsx := hh*3600 + mi*60 + ss; ELSE secondsx := datesx*24*3600 + hh*3600 + mi*60 + ss; END IF; RETURN secondsx; END DATEDIFF;

3、十六进制转十进制的函数create or replace FUNCTION "HXTOTEN" (str_a IN VARCHAR2) RETURN NUMBER IS str_b NUMBER(10); str_c VARCHAR2(100); BEGIN str_c:=REPLACE(str_a,'0x',''); str_b:=0; WHILE LENGTH(str_c) IS NOT NULL LOOP str_b:=str_b*16+CASE SUBSTR(str_c,0,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 ELSE CAST(SUBSTR(str_c,0,1) AS INT) END; str_c:=SUBSTR(str_c,-(LENGTH(str_c)-1),LENGTH(str_c)-1); END LOOP; RETURN str_b; END HXTOTEN;

4、判断是否是数字的函数create or replace FUNCTION "ISNUMERIC" (str IN VARCHAR2) RETURN NUMBER IS v_str FLOAT; BEGIN IF str IS NULL THEN RETURN 0; ELSE BEGIN SELECT TO_NUMBER (str) INTO v_str FROM DUAL; EXCEPTION WHEN INVALID_NUMBER THEN RETURN 0; END; RETURN 1; END IF; END isnumeric;

5、2个数字的最大的函数create or replace FUNCTION "MAX_TWO" (paramone IN NUMBER,paramtwo IN NUMBER) RETURN NUMBER IS max_param NUMBER(18,2); BEGIN IF paramone>=paramtwo THEN max_param:=paramone; ELSE max_param:=paramtwo; END IF; RETURN max_param; END MAX_TWO;

6、2个数字的最小的函数create or replace FUNCTION "MIN_TWO" (paramone IN NUMBER,paramtwo IN NUMBER) RETURN NUMBER IS min_param NUMBER(18,2); BEGIN IF paramone>0 AND paramtwo>0 THEN IF paramone>=paramtwo THEN min_param:=paramtwo; ELSE min_param:=paramone; END IF; ELSE min_param:=0; END IF; RETURN min_param; END MIN_TWO;

7、十进制转二进制的函数create or replace FUNCTION "TENTOBIN" (ix IN NUMBER) RETURN VARCHAR2 IS vars VARCHAR2(1000); xs NUMBER(10); BEGIN vars:=''; xs:=ix; WHILE xs>0 LOOP vars:=cast(MOD(xs,2) as VARCHAR2)||vars;--拼接字符串用“||” xs:=FLOOR(xs/2); END LOOP; RETURN vars; END TENTOBIN;

8、十进制转十六进制的函数create or replace FUNCTION "TENTOHX" (ix IN NUMBER) RETURN VARCHAR2 IS rx VARCHAR2(10); ixx NUMBER(10); BEGIN rx:=''; ixx:=ix; WHILE MOD(ixx,16)>0 LOOP rx:= (CASE WHEN MOD(ixx,16)<=9 THEN CAST(MOD(ixx,16) AS VARCHAR2) WHEN MOD(ixx,16)=10 THEN 'A' WHEN MOD(ixx,16)=11 THEN 'B' WHEN MOD(ixx,16)=12 THEN 'C' WHEN MOD(ixx,16)=13 THEN 'D' WHEN MOD(ixx,16)=14 THEN 'E' WHEN MOD(ixx,16)=15 THEN 'F' END)||rx; ixx:=FLOOR(ixx/16); END LOOP; IF ixx>0 THEN rx:= (CASE WHEN MOD(ixx,16)<=9 THEN CAST(MOD(ixx,16) AS VARCHAR2) WHEN MOD(ixx,16)=10 THEN 'A' WHEN MOD(ixx,16)=11 THEN 'B' WHEN MOD(ixx,16)=12 THEN 'C' WHEN MOD(ixx,16)=13 THEN 'D' WHEN MOD(ixx,16)=14 THEN 'E' WHEN MOD(ixx,16)=15 THEN 'F' END)||rx; END IF; RETURN rx; END TENTOHX;