一、前言
随着公司的业务发展，不同类型数据库间的兼容问题也就自然形成了一个必须研究的课题。公司目前使用的是比较传统的SSH框架+Oracle数据库，但在Dao层大量使用自己封装的JDBC，使得原本就兼容多数据库的Hibernate力不从心。原本我是建议改用的MyBatis做持久层的，但是公司大多元老级的人物都不太懂这个技术，直接给否定了，而且只给两周时间将系统给改造完。当初一万个MMP就要脱口而出，后来想着还是直接硬编码改改算了，由此诞生第二方案——硬编码形式将代码中的SQL统一改成标准SQL。由于时间有限也就先将系统改成同时兼容Mysql和Oracle数据库了，先把能转换成标准SQL的语句全转了，不能转换的就从数据库表中读取一个参数(启动程序时放入缓存再从缓存中取出)用来区分数据库类型，再根据数据库类型不同分别写对应的sql语句。为了尽量减少代码的改造量，就将Oracle比较常用的函数在MySQL库中实现。
二、Oracle常用函数转换
1、仿Oracle序列的使用
/*创建序列表,用于模拟Oracle序列*/DROP TABLE IF EXISTS sequence;CREATE TABLE sequence ( name varchar(128) NOT NULL, current_value int(10) NOT NULL, increment int(10) NOT NULL DEFAULT '1', PRIMARY KEY (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*设置序列初始值*/DELIMITER ;;DROP FUNCTION IF EXISTS setval;;CREATE FUNCTION setval(seq_name VARCHAR(128),val INTEGER) RETURNS INT(11)BEGIN UPDATE sequence SET current_value = val WHERE NAME = seq_name; RETURN currval(seq_name); END ;;DELIMITER ;/*获取当前序列*/DELIMITER ;;DROP FUNCTION IF EXISTS currval;;CREATE FUNCTION currval(seq_name VARCHAR(128)) RETURNS INT(11)BEGIN DECLARE VALUE INTEGER; SET VALUE = 0; SELECT current_value INTO VALUE FROM sequence WHERE NAME = seq_name; RETURN VALUE; END ;;DELIMITER ;/*获取下一个序列值*/DELIMITER ;;DROP FUNCTION IF EXISTS nextval;;CREATE FUNCTION nextval(seq_name VARCHAR(128)) RETURNS INT(11)BEGIN UPDATE sequence SET current_value = current_value + increment WHERE NAME = LOWER(seq_name); RETURN currval(LOWER(seq_name)); END ;;DELIMITER ;
2、仿Oracle获取uuid函数sys_guid()
DELIMITER ;;DROP FUNCTION IF EXISTS sys_guid;;CREATE FUNCTION sys_guid() RETURNS VARCHAR(60) CHARSET utf8BEGIN RETURN REPLACE(UUID(),'-','');END ;;DELIMITER ;
3、仿Oracle中的decode函数(低级版)
DELIMITER ;;DROP FUNCTION IF EXISTS decode;;CREATE FUNCTION decode(expr VARCHAR(512),res VARCHAR(512),result1 VARCHAR(512),result2 VARCHAR(512)) RETURNS VARCHAR(512)BEGIN DECLARE var_name VARCHAR(512); IF expr = RES THEN SET var_name = result1; ELSE SET var_name = result2; END IF;RETURN var_name;END ;;DELIMITER ;
4、仿Oracle值为空时赋默认值函数:nvl(str,defstr)
DELIMITER ;;DROP FUNCTION IF EXISTS nvl;;CREATE FUNCTION nvl(param1 VARCHAR(512),param2 VARCHAR(512)) RETURNS VARCHAR(512) CHARSET utf8BEGIN RETURN IF(param1 IS NULL AND param1<>'',param1,param2); END ;;DELIMITER ;
5、仿Oracle值为空时赋默认值1否则赋默认值2函数:nvl2(str,nulldef,elsedef)
DELIMITER ;;DROP FUNCTION IF EXISTS nvl2;;CREATE FUNCTION nvl2(exp1 VARCHAR(512),exp2 VARCHAR(512),exp3 VARCHAR(512)) RETURNS VARCHAR(512) CHARSET utf8BEGIN RETURN IF(exp1 IS NULL OR exp1='',exp2,exp3);END ;;DELIMITER ;
6、仿Oracle字符串转换为数字函数:to_number(str)
DELIMITER ;;DROP FUNCTION IF EXISTS to_number;;CREATE FUNCTION to_number(param1 VARCHAR(40)) RETURNS DECIMAL(32,4) BEGIN RETURN CONVERT(param1,DECIMAL(32,4)); END;;DELIMITER ;
7、仿Oracle判断某个字符串是否为数字函数:isnumber(str)
DELIMITER ;;DROP FUNCTION IF EXISTS isnumber;;CREATE FUNCTION isnumber(c VARCHAR(40)) RETURNS TINYINT(2)BEGIN DECLARE v_param INT(10); DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0; SET v_param = to_number(c); RETURN 1;END ;;DELIMITER ;
8、仿Oracle日期格式转字符串函数:to_char(date,format)
DELIMITER ;;DROP FUNCTION IF EXISTS to_char;;CREATE FUNCTION to_char(formatdate DATETIME,formate VARCHAR(40)) RETURNS VARCHAR(40)BEGIN DECLARE temp VARCHAR(50); SET temp = LOWER(formate); SET temp = REPLACE(temp,'yyyy','%Y'); SET temp = REPLACE(temp,'mm','%m'); SET temp = REPLACE(temp,'dd','%d'); SET temp = REPLACE(temp,'hh24','%H'); SET temp = REPLACE(temp,'mi','%i'); SET temp = REPLACE(temp,'ss','%S'); SET temp = REPLACE(temp,'mon','%c月'); RETURN DATE_FORMAT(formatdate,temp);END ;;DELIMITER ;
9、仿Oracle日期格式字符串转日期类型函数:to_date(datestr,format)
DELIMITER ;;DROP FUNCTION IF EXISTS to_date;;CREATE FUNCTION to_date(formatdate VARCHAR(40),formate VARCHAR(40)) RETURNS VARCHAR(40)BEGIN DECLARE temp VARCHAR(50); SET temp = LOWER(formate); SET temp = REPLACE(temp,'yyyy','%Y'); SET temp = REPLACE(temp,'mm','%m'); SET temp = REPLACE(temp,'dd','%d'); SET temp = REPLACE(temp,'hh24','%H'); SET temp = REPLACE(temp,'mi','%i'); SET temp = REPLACE(temp,'ss','%S'); RETURN STR_TO_DATE(formatdate,temp);END ;;DELIMITER ;
三、编后语
本文提到的转换函数只是些皮毛而已，还有待今后的工作与学习中进一步完善...