最近公司数据库迁移,由Oracle迁移到MySQL,主要是迁移架构中xml的SQL语句,架构比较老套(ibatis)
整理对比如下
0.月份 日期 add 或者minus
博客地址:http://www.cnblogs.com/chenpi/p/6027542.html
1.首先切换数据源,由Ora切换到MySQL
2.序列修改,由于MySQL支持NEXTVAL,需要修正xml
<insert id="test" >
INSERT
INTO
test
(
name
)
VALUES
(
#1#
)
<selectKey resultClass="int" >
SELECT LAST_INSERT_ID()
</selectKey>
</insert>
3.日期修正
SYSDATE 修改为 NOW()
to_date(#1#, 'yyyy-mm-dd hh24:mi:ss') 改为 str_to_date(#1#, '%m.%d.%Y %h:%i:%s')
to_char(sysdate 'yyyy-mm-dd hh24:mi:ss')) 改为 DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')
4.存储过程
转载文章:http://www.cnblogs.com/HondaHsu/p/3641258.html
编号 | 类别 | ORACLE | MYSQL | 注释 |
1 | 创建存储过程语句不同 | create or replace procedure P_ADD_FAC( id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) is | DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`; create procedure P_ADD_FAC( id_fac_cd varchar(100)) | 1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程. oracle使用create or replace. mysql使用先删除老的存储过程,然后再创建新的存储过程. 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. Mysql 存储过程中不可以定义多个存储过程. 3. oracle中字符串类型可以使用varchar2. Mysql 需要使用varchar 4. Oracle中参数varchar长度不是必须的, Mysql中参数varchar长度是必须的, 比如varchar(100) |
2 | 创建函数语句不同 | CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP( ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE ) RETURN VARCHAR2 | DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`; CREATE FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`( ii_role_int_key INTEGER(10) ) RETURNS varchar(1000) | 1.在创建函数时如果存在同名的函数,会删除老的函数. oracle使用create or replace. mysql使用先删除老的函数,然后再创建新的函数. 2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数. Mysql Functions不可以定义多个函数. 3. oracle返回值用return. Mysql返回值用returns. |
3 | 传入参数写法不同 | procedure P_ADD_FAC( id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) | create procedure P_ADD_FAC( (in) id_fac_cd varchar(100)) | 1. oracle存储过程参数可以定义为表的字段类型. Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度. 2. oracle 参数类型in/out/inout写在参数名后面. Mysql 参数类型in/out/inout写在参数名前面. 3. oracle 参数类型in/out/inout 都必须写. Mysql 参数类型如果是in,则可以省略. 如果是out或inout则不能省略. 注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。(FUNCTION参数总是被认为是IN参数) RETURNS字句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。 |
function func_name( gw_id in(out) varchar2 ) | create function func_name( gw_id varchar(100)) | |||
4 | 包的声明方式 | create or replace package/package body package name | 拆分成多个存储过程或函数 | oracle可以创建包,包中可以包含多个存储过程和方法. mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 例1: 方法命名 oracle 中SD_FACILITY_PKG.F_SEARCH_FAC to mysql SD_FACILITY_F_SEARCH_FAC 例2: 过程命名 oracle 中SD_FACILITY_PKG.P_ADD_FAC to mysql SD_FACILITY_P_ADD_FAC |
5 | 存储过程返回语句不一样 | return; | LEAVE proc; (proc 代表最外层的begin end) | oracle存储过程和方法都可以使用return退出当前过程和方法. Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. Mysql方法可以使用return退出当前方法. |
6 | 存储过程异常处理不一样 | EXCEPTION WHEN OTHERS THEN ROLLBACK ; ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'|| TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100); | DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK ; set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):', TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100)); END; | oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分. oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获. mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. 目前mysql不支持自定义异常. |
7 | 过程和函数的声明变量的位置不同 | 声明变量在begin…end体之前 | 声明变量在begin...end体内,begin之后其他任何内容之前 | |
8 | NO_DATA_FOUND异常处理 | EXCEPTION WHEN NO_DATA_FOUND THEN oi_rtn_cd := 1; ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016', li_sub_rtn_cd, lv_sub_rtn_msg ); | 使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释. | oracle中: NO_DATA_FOUND是游标的一个属性. 当select没有查到数据就会出现 no data found 的异常,程序不会向下执行. Mysql: 没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑. |
9 | 在存储过程中调用存储过程方式的不同 | Procedure_Name(参数); | Call Procedure_Name(参数); | MYSQL存储过程调用存储过程,需要使用Call pro_name(参数). Oracle调用存储过程直接写存储过程名就可以了. |
10 | 抛异常的方式不同 | RAISE Exception_Name; | 见备注 | 详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分 |
日期转换SQL
SELECT DATE_FORMAT(DATETIME - 1 / 24,'%Y-%c-%d %H') FROM DUAL
SELECT DATE_FORMAT(NOW() - 1 / 24,'%Y-%c-%d %H') FROM `act_ge_property`;
SELECT STR_TO_DATE(DATE_FORMAT(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d %H'),':59:59'),'%Y-%c-%d %H:%m:%s'),'%Y-%c-%d %H:%m:%s') FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d %H'),':59:59') AS DATETIME) FROM `act_ge_property`;
SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d'),' 23:59:59'),'%Y-%c-%d %H:%m:%s') FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR),'%Y-%c-%d %H'),':00:00') AS DATETIME) FROM `act_ge_property`;
SELECT STR_TO_DATE(DATE_FORMAT(NOW() - 1 / 24,'%Y-%c-%d %H'),'%Y-%c-%d %H') FROM `act_ge_property`;
SELECT CAST(CONCAT((DATE_FORMAT(inday - 1 / 24,'%Y-%c-%d %H'),'%Y-%c-%d %H'),) AS DATETIME) FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(NOW()- 1 / 24,'%Y-%c-%d %H'),':00:00') AS DATETIME) FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR),'%Y-%c-%d %H'),':00:00') AS DATETIME) FROM `act_ge_property`;
SELECT STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),'%Y-%c-%d'),'%Y-%c-%d') FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d'),'23:59:59') AS DATETIME) FROM `act_ge_property`;
SELECT CAST(CONCAT(DATE_FORMAT(NOW(),'%Y-%c-%d'),' 23:59:59') AS DATETIME) FROM `act_ge_property`;