Oracle自定义MySQL同名函数

想实现一条SQL同时适配MySQL和Oracle的同学有福了

一、在Oracle中定义

1.DATE_FORMAT函数

CREATE OR REPLACE
FUNCTION DATE_FORMAT(p_date IN TIMESTAMP, mysql_format IN VARCHAR2) RETURN VARCHAR2
IS formatted_date VARCHAR2(30);
oracle_format VARCHAR2(30);
BEGIN
	oracle_format := REPLACE(mysql_format, '%Y', 'yyyy');
	oracle_format := REPLACE(oracle_format, '%m', 'mm');
	oracle_format := REPLACE(oracle_format, '%d', 'dd');
	oracle_format := REPLACE(oracle_format, '%H', 'HH24');
	oracle_format := REPLACE(oracle_format, '%i', 'MI');
	oracle_format := REPLACE(oracle_format, '%s', 'SS');
SELECT TO_CHAR(p_date, oracle_format) INTO formatted_date FROM dual;
RETURN formatted_date;
END DATE_FORMAT;

2.NOW函数

CREATE OR REPLACE
FUNCTION NOW RETURN DATE AS
BEGIN
	RETURN SYSDATE;
END NOW;

3.QUARTER函数

CREATE OR REPLACE
FUNCTION QUARTER(var1 DATE) RETURN NUMBER AS
BEGIN
	RETURN TO_CHAR(var1, 'Q');
END QUARTER;

4.YEAR函数

CREATE OR REPLACE
FUNCTION YEAR (input_date IN DATE)
RETURN VARCHAR2
IS
   output_year VARCHAR2(4);
BEGIN
	SELECT TO_CHAR(input_date, 'YYYY') INTO output_year FROM DUAL;
RETURN output_year;
END YEAR;

5.STR_TO_DATE函数

CREATE OR REPLACE
FUNCTION STR_TO_DATE(p_str IN VARCHAR2, mysql_format IN VARCHAR2) RETURN DATE
IS
   oracle_format VARCHAR2(100);
BEGIN
	oracle_format := REPLACE(mysql_format, '%Y', 'yyyy');
	oracle_format := REPLACE(oracle_format, '%m', 'mm');
	oracle_format := REPLACE(oracle_format, '%d', 'dd');
	oracle_format := REPLACE(oracle_format, '%H', 'HH24');
	oracle_format := REPLACE(oracle_format, '%i', 'MI');
	oracle_format := REPLACE(oracle_format, '%s', 'SS');
RETURN TO_DATE(p_str, oracle_format);
END STR_TO_DATE;

6.SUBSTRING_INDEX函数

CREATE OR REPLACE
FUNCTION substring_index(f_string IN varchar2, f_delimiter IN varchar2, f_position IN NUMBER) RETURN varchar2 
AS
o_string varchar2(4000);
v_direction int;
v_instr_pos int;
BEGIN
IF f_position = 0 THEN 
	RETURN ''; 
END IF;
IF f_position > 0 THEN
	v_direction := 1; 
	SELECT INSTR(f_string, f_delimiter, v_direction, f_position) INTO v_instr_pos FROM dual;
	SELECT SUBSTR(f_string, 1, CASE WHEN v_instr_pos = 0 THEN LENGTH(f_string) ELSE v_instr_pos-1 END) INTO o_string FROM dual;
ELSE
    v_direction := 1;
	SELECT INSTR(f_string, f_delimiter, v_direction, ABS(f_position)) INTO v_instr_pos FROM dual;
	SELECT SUBSTR(f_string, v_instr_pos + 1, LENGTH(f_string)) INTO o_string FROM dual;
END IF;
RETURN o_string;
END substring_index;

7.FIND_IN_SET函数

CREATE OR REPLACE
FUNCTION FIND_IN_SET ( arg1 IN VARCHAR2, arg2 IN VARCHAR2 ) RETURN VARCHAR2
IS RESULT NUMBER;
temp1 VARCHAR2(32767);
temp2 NUMBER;
BEGIN
	temp2 := instr( ',' || arg2 || ',', ',' || arg1 || ',' );
	temp1 := SUBSTR(',' || arg2 || ',', 0, temp2);
	RESULT := LENGTH(temp1)-(
        CASE WHEN
        	LENGTH(REPLACE(temp1, ',', '')) IS NULL THEN 0
        ELSE
        	LENGTH(REPLACE(temp1, ',', ''))
        END);
RETURN (RESULT);
END FIND_IN_SET;

二、MySQL中定义

1.NLSSORT函数

CREATE FUNCTION NLSSORT(PARAM1 VARCHAR(1024), PARAM2 VARCHAR(128))
    RETURNS VARCHAR(1024) CHARACTER SET
GBK
BEGIN
	RETURN PARAM1;
END

目前业务中用到的主要就这些了，可以说把数据库适配的工作量降到了很低。而关于Java后台将MySQL数据库换成Oracle数据库要做的工作远不止这些，尤其是业务当中有大量的String类型的日期格式参数，Oracle对这些参数的类型要求要比MySQL严格很多。
有问题可以评论区问我，工作日每天我都在