Oracle实用自定义函数

除法处理

1. 创建语句

CREATE OR REPLACE FUNCTION F_Divide (
	v_dividend IN NUMBER,
	v_dividen IN NUMBER,
	v_onzero IN NUMBER,
	v_point IN NUMBER 
) RETURN NUMBER 
IS 
/* 函数说明：除法处理
 * 参数1：v_dividend 被除数
 * 参数2：v_dividen 除数
 * 参数3：v_onzero 当除数为0时返回的值
 * 参数4：v_point 保留小数位数,NULL值则返回默认位数
 */
	v_res NUMBER := CASE v_dividen WHEN 0 THEN v_onzero ELSE v_dividend/v_dividen END;
BEGIN
	IF v_point IS NULL THEN 
		RETURN v_res;
	ELSE 
		RETURN ROUND(v_res , v_point);
	END IF;
END;

2. 调用示例

SELECT 
	F_Divide(100, 30, 0 , NULL) r1,
	F_Divide(100, 30, 0 , 2) r2,
	F_Divide(100, 3 , 0 , -1) r3,
	F_Divide(100, 3 , 0 , 0) r4,
	F_Divide(100, 0 , 1 , NULL) r5
FROM dual;

R1                                      |R2  |R3|R4|R5|
----------------------------------------+----+--+--+--+
3.33333333333333333333333333333333333333|3.33|30|33| 1|

计算周岁

1. 创建语句

CREATE OR REPLACE FUNCTION F_GetAge(v_bir DATE) 
RETURN NUMBER 
IS
/* 函数说明：传入日期计算周岁年龄，精确到日
 * 参数1: v_bir  出生日期
 */
	v_now DATE 	:= SYSDATE ;
	v_year INT 	:= EXTRACT(YEAR FROM v_now) - EXTRACT(YEAR FROM v_bir);
	v_month INT := EXTRACT(MONTH FROM v_now) - EXTRACT(MONTH FROM v_bir);
	v_day INT 	:= EXTRACT(DAY FROM v_now) - EXTRACT(DAY FROM v_bir);
BEGIN
	-- 如果日期差小于0，月份差-1
	IF v_day < 0 THEN 
		v_month :=  v_month - 1;
	END IF;

	-- 如果月份差小于0，年份差-1
	IF v_month < 0 THEN 
		v_year := v_year -1 ;
	END IF;

	-- 如果年份差小于0，年份差设置为0
	IF v_year < 0 THEN 
		v_year := 0 ;
	END IF;

	RETURN v_year ;

END;

2. 调用示例

SELECT 
	F_GetAge(TO_DATE('2000-10-22','yyyy-mm-dd')) age1,
	F_GetAge(TO_DATE('2000-12-23','yyyy-mm-dd')) age2,
	F_GetAge(TO_DATE('2023-12-31','yyyy-mm-dd')) age3
FROM dual;

AGE1|AGE2|AGE3|
----+----+----+
  22|  22|   0|

FindInSet

1. 创建语句

CREATE OR REPLACE FUNCTION F_FindInSet (
	v_str IN VARCHAR2,
	v_str_set IN VARCHAR2,
	v_split IN VARCHAR2
) RETURN NUMBER
IS
/* 函数说明：查找字符串在指定集合中是否存在，返回值大于0即存在
 * 参数1：v_str 查找的字符串
 * 参数2：v_str_set 目标字符串集合
 * 参数3：v_split 字符串集合分隔符
 */
BEGIN
	RETURN INSTR(v_split || v_str_set || v_split , v_split || v_str || v_split);
END;

2. 调用示例

SELECT F_FindInSet('a', 'b,c,d,e,f' , ',') idx1,
	   F_FindInSet('a', 'ab,c,d,e,f', ',') idx2,
	   F_FindInSet('a', 'c,d,e,a,g' , ',') idx3
FROM dual;

IDX1|IDX2|IDX3|
----+----+----+
   0|   0|   7|

指定分隔符截取字符串

1. 创建语句

CREATE OR REPLACE FUNCTION F_SplitOfIndex(
  	v_Str NVARCHAR2,
  	v_cut NVARCHAR2,
  	v_index INTEGER
) RETURN NVARCHAR2
IS 
/* 函数说明：字符串按照指定分隔符拆分，截取第?个元素
 * 参数1：v_Str 要分割的字符串
 * 参数2：v_cut 分隔符
 * 参数3：v_index 取第几个元素
 */
	v_String NVARCHAR2(4000) := CONCAT(v_Str, v_cut);
  	v_cutLen INTEGER := LENGTH(v_cut);			-- 分隔符长度
	v_star INTEGER := 1;						-- 开始位置下标
  	v_next INTEGER := 1;						-- 下次位置下标
BEGIN
  	-- 如果分隔符出现次数,即拆分后元素数量小于元素下标，返回NULL
  	IF ((LENGTH(v_String) - LENGTH(REPLACE(v_String,v_cut,'')))/v_cutLen < v_index) 
  	THEN
  		RETURN NULL;
  	END IF;
  	FOR v_step IN 1..v_index 
  	LOOP 
    	v_star := v_next ;
    	v_next := INSTR(v_String,v_cut,v_next) + v_cutLen;
  	END LOOP;
  	RETURN SUBSTR(v_String, v_star, v_next - v_star - v_cutLen);
END;

2. 调用示例

SELECT 
	F_SplitOfIndex('内科服务--霍乱--西药组','--',3) str1,
	F_SplitOfIndex('内科服务/霍乱/西药组','/',2) str2,
	F_SplitOfIndex('内科服务~霍乱~西药组','~',1) str3,
	F_SplitOfIndex('内科服务~霍乱~西药组','~',4) str4
FROM DUAL;

STR1|STR2|STR3|STR4|
----+----+----+----+
西药组 |霍乱  |内科服务|   

日期与毫秒数互相转换

1. 创建语句

-- 日期转毫秒数
CREATE OR REPLACE FUNCTION F_DATETIMETOLONG(v_date in TIMESTAMP)
RETURN INTEGER
IS
/* 函数说明：日期转换为毫秒数，初始计算时间为 1970-01-01 08:00:00
 * 参数1：v_date 日期
 */
	v_sdt	   TIMESTAMP := to_timestamp('1970-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.ff');
	v_str      VARCHAR2(50);
    v_misecond INTEGER;
    v_seconds  INTEGER;
    v_minutes  INTEGER;
    v_hours    INTEGER;
    v_days     INTEGER;
BEGIN
    v_str := to_char(v_date - v_sdt);
	v_misecond := to_number(SUBSTR(v_str, INSTR(v_str, '.') + 1, 3));
    v_seconds := to_number(SUBSTR(v_str,  INSTR(v_str, ':',1,2) + 1, instr(v_str, '.', 1) - instr(v_str, ':', 1,2) - 1));
    v_minutes := to_number(SUBSTR(v_str, INSTR(v_str, ':',1,1) + 1,    (instr(v_str, ':', 1,2)  )- instr(v_str, ':', 1)-1 ));
    v_hours := to_number(SUBSTR(v_str, INSTR(v_str, ' ') + 1 ,  (instr(v_str, ':', 1)  )- instr(v_str, ' ', 1)-1));
    v_days := to_number(SUBSTR(v_str, 1, INSTR(v_str, ' ')));
    RETURN v_days * 24 * 60 * 60 * 1000 + v_hours * 60 * 60 * 1000 + v_minutes * 60 * 1000 + v_seconds * 1000 + v_misecond - (8 * 60 * 60 * 1000);
END;

-- 毫秒数转日期
CREATE OR REPLACE FUNCTION F_LONGTODATETIME(v_long in NUMBER)
RETURN TIMESTAMP
IS
/* 函数说明：毫秒数转换为日期，初始计算时间为 1970-01-01 08:00:00
 * 参数1：v_long 毫秒数
 */
	v_ms   NUMBER;
	v_ful  VARCHAR2(10);
	v_date VARCHAR2(50);
BEGIN
	v_ms := to_char(mod(v_long,1000)/1000);
	v_ful := '.' || SUBSTR(v_ms,INSTR(v_ms,'.')+1);
	v_date := to_char(to_timestamp('1970-01-01 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.ff')+v_long/1000/24/60/60 + (8/24),'yyyy-mm-dd HH24:MI:SS') || v_ful;
	RETURN ( to_timestamp(v_date,'YYYY-MM-DD HH24:MI:SS.ff'));
END;

2. 调用示例

SELECT 
	F_DateTimeToLong(to_timestamp('2022-01-01 01:07:32.005', 'YYYY-MM-DD HH24:MI:SS.ff')) lg1,
	F_DateTimeToLong(to_timestamp('2022-10-31', 'YYYY-MM-DD HH24:MI:SS.ff')) lg2,
	F_LongToDateTime(1640970452005)  dt1,
	F_LongToDateTime(1667145600000)  dt2
FROM dual;

LG1          |LG2          |DT1                    |DT2                    |
-------------+-------------+-----------------------+-----------------------+
1640970452005|1667145600000|2022-01-01 01:07:32.005|2022-10-31 00:00:00.000|

类似数组字符串去重排序

1. 创建语句

CREATE OR REPLACE FUNCTION F_DistinctStr(
  	v_oldstr 	VARCHAR2, 
  	v_cut 		VARCHAR2,
  	v_desc 		INTEGER
)RETURN VARCHAR2 
IS  
/* 函数说明：类似数组的字符串，去掉重复的元素，在排序处理
 * 参数1：v_oldstr 要处理的字符串
 * 参数2：v_cut 字符串分隔符
 * 参数3：v_desc 排序标识，1正序，2降序，其他不排序
 */
    v_str          	VARCHAR2(4000);  
    v_temp			VARCHAR2(4000); 
    v_currentIndex 	NUMBER;  
    v_startIndex   	NUMBER;  
    v_endIndex     	NUMBER;  
	v_lencut 		NUMBER := LENGTH(v_cut);
    TYPE v_str_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; 
    v_arr 			v_str_type;  
    v_Result 		VARCHAR2(4000);  
BEGIN  
	/* 
	 * 如果分隔符长度v_lencut为0，直接返回原字符串
	 * 如果原字符串是空，直接返回
	 * 如果原字符串长度为0，直接返回
	 * 如果原字符串不包含分隔符，直接返回
	 */
	IF v_lencut = 0 THEN 
		RETURN(v_oldstr);
  	ELSIF v_oldstr IS NULL THEN  
    	RETURN(v_oldstr);
    ELSIF NVL(LENGTH(v_oldstr),0) = 0 THEN 
  	  	RETURN(v_oldstr);
  	ELSIF (LENGTH(v_oldstr)-LENGTH(REPLACE(v_oldstr,v_cut,''))) / v_lencut = 0 THEN 
  		RETURN(v_oldstr);
  	END IF;
  
  	v_str := v_oldstr;      
  	v_currentIndex := 0 ;  
  	v_startIndex   := 1 ;  
      
  	LOOP
	  	-- v_currentIndex计数器，计算分隔符次数
		v_currentIndex := v_currentIndex + 1;
		-- v_endIndex，分隔符下标
		v_endIndex := instr(v_str,v_cut,1,v_currentIndex);
		-- 如果v_endIndex为0，即找不到对应次数的分隔符，跳出循环
		IF (v_endIndex <= 0) THEN  
			EXIT;
		END IF;
		-- 向数组添加当前位置分隔符之间的字符串
		v_arr(v_currentIndex) := trim( substr(v_cut || v_str , v_startIndex + v_lencut , v_endIndex - v_startIndex) );
		v_startIndex := v_endIndex + v_lencut ;
  	END	LOOP;
     
  	v_arr(v_currentIndex) := substr(v_str, v_startIndex , length(v_str));  
      
  	-- 排序,去重
  	v_temp := '';
  	CASE v_desc
		WHEN 1 THEN 
			FOR i IN 1 .. v_currentIndex - 1 LOOP 
				FOR j IN i + 1 .. v_currentIndex LOOP
  					IF v_arr(i) > v_arr(j) THEN
						v_temp   := v_arr(i);
						v_arr(i) := v_arr(j);
						v_arr(j) := v_temp;
    				ELSIF v_arr(i) = v_arr(j) THEN 
						v_arr(j) := '';
  					END IF;
				END LOOP;
			END LOOP; 
		WHEN 2 THEN 
			FOR i IN 1 .. v_currentIndex - 1 LOOP 
				FOR j IN i + 1 .. v_currentIndex LOOP
  					IF v_arr(i) < v_arr(j) THEN
						v_temp   := v_arr(i);
						v_arr(i) := v_arr(j);
						v_arr(j) := v_temp;
    				ELSIF v_arr(i) = v_arr(j) THEN 
						v_arr(j) := '';
  					END IF;
				END LOOP;
			END LOOP;
		ELSE 
			FOR i IN 1 .. v_currentIndex - 1 LOOP
		  		FOR j IN i + 1 .. v_currentIndex LOOP
	    			IF v_arr(i) = v_arr(j) THEN  
	      		  		v_arr(j) := '';
	    			END IF;
		  		END LOOP;
			END LOOP;
  	END CASE;
  
  	-- 拼接字符串
  	v_str := '';  
  	FOR i IN 1 .. v_currentIndex LOOP
		IF v_arr(i) IS NOT NULL THEN  
  			v_str := v_str || v_cut || v_arr(i);
  			v_arr(i) := '';
		END	IF;
  	END	LOOP;
      
  	v_Result := substr(v_str, v_lencut + 1, length(v_str));  
      
  	RETURN(v_Result);

END;

2. 调用示例

SELECT 
	F_DISTINCTSTR('AAA,CCC,BBB,DDD,AAB,BBB,CCC,DDE',',',0) RES0,
	F_DISTINCTSTR('AAA,BBB,CCC,DDD,AAB,BBB,CCC,DDE',',',1) RES1,
	F_DISTINCTSTR('AAA,BBB,CCC,DDD,AAB,BBB,CCC,DDE',',',2) RES2
FROM DUAL;

RES0                   |RES1                   |RES2                   |
-----------------------+-----------------------+-----------------------+
AAA,CCC,BBB,DDD,AAB,DDE|AAA,AAB,BBB,CCC,DDD,DDE|DDE,DDD,CCC,BBB,AAB,AAA|

Left和Right函数

1. 创建语句

-- 字符串左侧截取 left函数
CREATE OR REPLACE FUNCTION F_LEFT (
	v_str IN VARCHAR2, 
	v_sublen IN INTEGER
) RETURN VARCHAR2 
IS
/* 函数说明：截取字符串左侧?位的字符串
 * 参数1：v_str 原字符串  
 * 参数2：v_sublen 截取位数
 */	
	v_strlen INTEGER := LENGTH(v_str);
BEGIN
	IF v_strlen <= 0 THEN
		RETURN '';
	ELSIF v_strlen <= v_sublen THEN
		RETURN v_str;
	END IF;
	RETURN SUBSTR(v_str, 1, v_sublen);
END;


-- 字符串右侧截取 right函数
CREATE OR REPLACE FUNCTION F_RIGHT (
	v_str IN VARCHAR2, 
	v_sublen IN INTEGER
) RETURN VARCHAR2 
IS
/* 函数说明：截取字符串右侧?位的字符串
 * 参数1：v_str 原字符串  
 * 参数2：v_sublen 截取位数
 */	
	v_strlen INTEGER := LENGTH(v_str);
BEGIN
	IF v_sublen <= 0 THEN	
		RETURN '';
	ELSIF v_strlen <= v_sublen THEN
		RETURN v_str;
	END IF;
	RETURN SUBSTR(v_str, v_strlen-v_sublen + 1, v_sublen);
END;

2. 调用示例

SELECT 
	F_LEFT('2022-01-01',4) l_str,
	F_RIGHT('ABCDEFG',5) r_str
FROM DUAL;

L_STR|R_STR|
-----+-----+
2022 |CDEFG|