除法处理
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
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
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
IF v_day < 0 THEN
v_month := v_month - 1;
END IF;
IF v_month < 0 THEN
v_year := v_year -1 ;
END IF;
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
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
v_String NVARCHAR2(4000) := CONCAT(v_Str, v_cut);
v_cutLen INTEGER := LENGTH(v_cut);
v_star INTEGER := 1;
v_next INTEGER := 1;
BEGIN
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
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
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
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
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 + 1;
v_endIndex := instr(v_str,v_cut,1,v_currentIndex);
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. 创建语句
CREATE OR REPLACE FUNCTION F_LEFT (
v_str IN VARCHAR2,
v_sublen IN INTEGER
) RETURN VARCHAR2
IS
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;
CREATE OR REPLACE FUNCTION F_RIGHT (
v_str IN VARCHAR2,
v_sublen IN INTEGER
) RETURN VARCHAR2
IS
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|