想实现一条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严格很多。
有问题可以评论区问我,工作日每天我都在