MySQL数据库实现Oracle常用函数

Wesley13
• 阅读 457

一、前言

  随着公司的业务发展,不同类型数据库间的兼容问题也就自然形成了一个必须研究的课题。公司目前使用的是比较传统的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 utf8
BEGIN
 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 utf8
BEGIN
  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 utf8
BEGIN
  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 ;

三、编后语

  本文提到的转换函数只是些皮毛而已,还有待今后的工作与学习中进一步完善...

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这