mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL); Query OK, 1 row affected (0.06 sec)
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec) BLOG：
ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。
视图
Mysql视图限制（1）SELECT语句不能包含FROM子句中的子查询。 （2）SELECT语句不能引用系统或用户变量。 （3）SELECT语句不能引用预处理语句参数。
（4）在存储子程序内，定义不能引用子程序参数或局部变量。
（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。
（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。 （7）在视图定义中命名的表必须已存在。
（8）不能将触发程序与视图关联在一起。
我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。
例子：
Oracle中带子查询的视图：
CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS
SELECT USER_ID AS ID, USER_NAME AS NAME, CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE,'00000001.10000000'), '.00.'), PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,'0')) AS CODE, NVL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM (SELECT PERSON.USER_ID , PERSON.USER_NAME, NVL(POSITION.ORG_ID, -1) AS ORG_ID, NVL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, NVL(POSITION.PERSON_LEVEL, '64') AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON WHERE PERSON.USER_ID = POSITION.USER_ID (+) AND PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='A') PERSON_POSITION, MOCHA_IM_ORG_VIEW ORG
WHERE PERSON_POSITION.ORG_ID = ORG.ORG_ID (+) UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='I'
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE
,1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A' UNION ALL
SELECT LPAD(ORG_ID,20,' ') AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW
UNION ALL
SELECT '-1' AS ID, '未分派人员
' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-2' AS ID, '待删除人员
' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-3' AS ID, '系统管理员
' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL
/
转为mysql：
CREATE OR REPLACE VIEW PERSON_POSITION AS
SELECT PERSON.USER_ID , PERSON.USER_NAME, IFNULL(POSITION.ORG_ID, -1) AS ORG_ID, IFNULL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, IFNULL(POSITION.PERSON_LEVEL, '64') AS PERSON_LEVEL
FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN MOCHA_IM_PERSON PERSON ON PERSON.USER_ID = POSITION.USER_ID
WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='A'; /
CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS SELECT USER_ID AS ID, USER_NAME AS NAME,
CONCAT(CONCAT(CONCAT(IFNULL(ORG.ORG_CODE,'00000001.10000000'), '.00.'),
PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,'0')) AS CODE,
IFNULL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, 'PERSON' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW ORG LEFT JOIN PERSON_POSITION ON PERSON_POSITION.ORG_ID = ORG.ORG_ID
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='I'
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE
,1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A' UNION ALL
SELECT LPAD(ORG_ID,20,' ') AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW
UNION ALL
SELECT '-1' AS ID, '未分派人员
' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-2' AS ID, '待删除人员
' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-3' AS ID, '系统管理员
' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL
/
触发器，函数，存储过程
语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql控制流程等等。
对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个
BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。就是说mysql不支持oracle中create trigger *** before insert or
Update or delete on ***.此时应该把这个触发器拆分为3个触发器。 例子：
Oracle下触发器：
CREATE OR REPLACE TRIGGER MOCHA_IM_DUTY_TRG BEFORE INSERT OR UPDATE OR DELETE ON MOCHA_IM_DUTY FOR EACH ROW DECLARE
befImg VARCHAR2(2000);
百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库Oracle到mysql转换的问题总结(2)在线全文阅读。
相关推荐：