Mysql转为Oracle

前言

一、Mysql和Oracle

二、部分语法区别

1. CREATE DATABASE IF NOT EXISTS  

2. DROP TABLE IF EXISTS

3.  CREATE TABLE xxx( ...bigint...varchar...char...COMMENT...PRIMARY KEY...KEY)ENGINE

4. INSERT INTO...VALUES/SELECT

 5. FUNCTION/PROCEDURE

总结

---

---

前言

最近需要将Mysql脚本转为Oracle脚本，脚本一大堆，好在都是相对简单的语句。虽然没有SQL一点经验，第一次接触，大概率也是最后一次接触，但是还是将我遇到的语法、踩过的坑记录一下，算是没有白干一场吧。

---

一、Mysql和Oracle

简述一下二者：

Mysql：关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle旗下产品；具有体积小、速度快、成本低等优势，开放源码更是使其大受欢迎。详细参见官方白皮书：https://www.mysql.com/cn/why-mysql/

Oracle：关系型数据库管理系统，是甲骨文公司的产品；具有移植性好，使用方便、功能强等优势，各种相应的开发工具或者可视化工具都比Mysql多些，基本就是数据库领域的大哥。详细参见官方描述：https://www.oracle.com/cn/database/technologies/

从我改的脚本看，二者语法上的差异并不是很大，都是一些细枝末节，但是对于一个没有基础的小白来讲，找不同也挺恶心的。由于脚本基本围绕增、删、改、查等基本操作，所以对于这种脚本转换，第一个想到的方法就是找现有软件帮助转换，按照网上流传的各种方法，我使用了Navicat软件，转化后的脚本是可以运行的，基本的东西也有，但是被删除的东西更多……导致功能缺失严重，具体原因我不清楚是给我的Mysql脚本写得太垃圾还是我转的方法缺了点仪式感，逼不得已才逐行阅读寻找语法不同点，结合python批量操作。

二、部分语法区别

1. CREATE DATABASE IF NOT EXISTS  

Mysql直接判断是否存在数据库X，若不存在就建立该数据库，通过 Use X，使用该数据库。Oracle通常使用可视化工具Database Configuration Assistant（数据库配置助理，DBCA）创建数据库，由于我只是想照虎画犬跑接下来的Oracle语句，所以使用以下代码“暂替”：

create tablespace "test" datafile'datafile.dbf'size 200m autoextend on next 50m maxsize 20480m extent management local;
create user lx identified by lx123 default tablespace "test";
grant dba, connect to lx;

对此我的理解是在Mysql中创建数据库时，没有特殊说明时该数据库的“内存”不需要用户插手管理，但是Oracle不同，不论是可视化工具建库还是脚本建库都需要详细指明划分的内存大小，这个表空间“tablespace”是指明实际内存的，该文件（*.dbf）可通过以下命令查询当前已有文件及实际存储位置，

select name from v$datafile;

文件大小由“size”决定，可更加情况自动扩展，每次扩展增加多少及最多可扩展到多少都是可以用户自定义的；表空间生成后，类似于Excel中已经新建文件了，设置用户名及密码，通过“grant”赋予用户权限，权限划分很细，都可以指明是否赋予，暂不赘述。建成表空间后，就可以自由操作了，毕竟内存归你了。

2. DROP TABLE IF EXISTS

Mysql中大量使用该语句，简单实用安全可靠，通常搭配后面紧跟的“CREATE TABLE”，但是Oracle不支持该语法，只有DROP TABLE，这样就缺失了判断的重要性，毕竟没有该表的时候直接DROP是会报错的，通过不断尝试，总结了网上给出的两种方法：1、begin+end 代码块的方式先判断再删除

declare
      num number;
begin
    select count(1) into num from user_tables where table_name = upper('TempTable');
    if num > 0 then
        execute immediate 'drop table TempTable' ;
    end if;
end;
/

使用这种方法要注意两点：1、upper()，因为Oracle中没有特殊指明时，是不区分大小写的，统一默认大写，想区分大小写定义时要加双引号 ；2、/ 该符号代表这块代码段结束了，只有到这里，前面的代码段才可使执行，如果确实该符号，后面的脚本语句会报错；

2、创建存储过程，调用过程进行判断删除（通过 “CALL proc_dropifexists(tablename);”）

create or replace procedure proc_dropifexists(   
    p_table in varchar2  
) is   
    v_count number;   
begin   
   select count(*) into v_count from user_tables where table_name = upper(p_table);

   if v_count > 0 then  
      execute immediate 'drop table ' || p_table ||' purge';  
   end if;   
end proc_dropifexists;

上面两种方法都是在“user_tables”中查询，也就是当前用户，也可以彻底一点，只需要将其改为“all_tables”。对于删除视图、函数或者存储过程，都可以使用上述两种方法，对应的改动就是“user_tables”=>“user_views”=>“user_objects”，“drop table/view/function/procedure”。
 

3.  CREATE TABLE xxx( ...bigint...varchar...char...COMMENT...PRIMARY KEY...KEY)ENGINE

Mysql建表过程中，上述形式很常见，转为Oracle时对于定义的数据类型这种关键字可以直接替代（如下python示例）：Oracle不支持自增长“AUTO_INCREMENT”，原脚本里没有，这个地方的替代没有研究；Oracle不支持表级的字符集定义“CHARACTER”，在Oracle建库/表空间的过程中会默认字符集，也可以自己设置，所以这个地方直接删除了；“PRIMARY KEY” Oracle直接支持，可以不用改；对于注释“COMMENT”，Oracle需要定义到表外部；索引“KEY”的定义Oracle在表内外都可以，但是关键字要替换；存储引擎“ENGINE”，Oracle不支持。

line = line.replace("AUTO_INCREMENT","")
line = line.replace("varchar","VARCHAR2")
line = line.replace("bigint","NUMBER")
line = line.replace("BIGINT","NUMBER")
line = line.replace("longtext","CLOB")
line = line.replace("text","CLOB")
line = line.replace("CHARACTER SET utf8 COLLATE utf8_bin","")
line = line.replace("CHARACTER SET utf8","")
line = line.replace("datetime","DATE")
line = line.replace("char","NCHAR")
line = line.replace("int","NUMBER")
line = line.replace("DECIMAL","NUMBER")

以下为一个完整的Mysql表转为Oracle的示例：

CREATE TABLE `testa` (
  `id` int(11) NOT NULL,
  `test_id` bigint(20) NOT NULL,
  `COLUMN_DESC` text,
  `creationdate` datetime DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `isorder` char(1) CHARACTER SET utf8 DEFAULT 'N',
  `ISACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `APPS` varchar(255) DEFAULT 'abcd' COMMENT '适用程序',
  PRIMARY KEY (`id`),
  UNIQUE KEY `0035408` (`id`),
  UNIQUE KEY `udx_column` (`name`),
  KEY `idx_column_tableid` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE testa (
  id NUMBER(11) NOT NULL,               -- primary key,
  test_id NUMBER(20) NOT NULL,
  COLUMN_DESC CLOB,
  creationdate DATE DEFAULT NULL,
  name VARCHAR2(40) NOT NULL,
  isorder NCHAR(1) DEFAULT 'N',
  ISACTIVE NCHAR(1) DEFAULT 'Y' NOT NULL,
  APPS VARCHAR2(255) DEFAULT 'abcd',
  PRIMARY KEY (`id`)                    --constraint udx_action_base unique (name)
) tablespace "test";
COMMENT ON COLUMN testa.APPS is '适用程序';
CREATE UNIQUE INDEX udx_column on testa(name);
CREATE INDEX idx_column_tableid on testa(test_id);

其中有默认值的时候“DEFAULT 'Y' NOT NULL”，这个NOT NULL的约束一定要放在默认值的后面；对于“ UNIQUE KEY `0035408` (`id`)”，Oracle的主键会自动生成一个唯一索引，所以不需要再添加。

4. INSERT INTO...VALUES/SELECT

插入语句的转化由下例给出，Oracle不支持 “\”作为转义符；DATE类型需要说明格式；当使用查询“SELECT”时，没有括号；注意Mysql的插入值是一对多（VALUES值有多个）还是一对一，Oracle不支持一对多。

--Mysql
INSERT INTO `testa` (`id`, `test_id`, `COLUMN_DESC`, `creationdate`, `name`, `isorder`, `ISACTIVE`, `APPS`) 
VALUES ('10', '20', '\'C:\Users\Desktop\sql\'', '2008-05-09 11:48:43','test', 'Y','Y','abcd');

--Oracle
INSERT INTO testa (id, test_id, COLUMN_DESC, creationdate, name, isorder, ISACTIVE, APPS) 
VALUES ('10', '20', '''C:\Users\Desktop\sql''', to_date('2008-05-09 11:48:43', 'yyyy-mm-dd hh24:mi:ss'),'test', 'Y','Y','abcd');

--Oracle prevent repeated insertion
INSERT INTO testa (id, test_id, COLUMN_DESC, creationdate, name, isorder, ISACTIVE, APPS) 
SELECT '10', '20', '''C:\Users\Desktop\sql''', to_date('2008-05-09 11:48:43', 'yyyy-mm-dd hh24:mi:ss'),'test', 'Y','Y','abcd'
FROM DUAL WHERE NOT EXISTS (SELECT id FROM testa WHERE id=1 OR name='test');

 5. FUNCTION/PROCEDURE

Mysql和Oracle对于函数/存储过程的定义语法是不同，示例如下：

--Mysql
CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
);
--Oracle
CREATE OR REPLACE FUNCTION isFieldExisting(table_name_IN VARCHAR, field_name_IN VARCHAR) 
return number
is
	v_num number;
BEGIN
    SELECT COUNT(*) INTO v_num FROM all_tab_columns
    WHERE TABLE_NAME = table_name_IN 
	AND COLUMN_NAME = field_name_IN;

		return v_num;
END;
--Mysql
CREATE PROCEDURE pro_test (
    IN table_name_IN VARCHAR(100)
)
BEGIN
	DECLARE v_num INT DEFAULT 0;
	SELECT count(1) INTO v_num FROM user_tables WHERE table_name=table_name_IN;

END;
--Oracle
CREATE PROCEDURE pro_test (
     table_name_IN VARCHAR2
)IS
	v_num number;
BEGIN

	SELECT count(1) INTO v_num FROM user_tables WHERE table_name=table_name_IN;

END;

总结

主要遇见的语法区别就以上这些，期间遇见一些坑，使用“show err”命令，可以在sqlplus中看到错误的位置，感觉这点相对NAVICAT好一些（不知道这个软件怎么看详细错误，比如错误行数和错误位置）；在NAVICAT中运行Oracle脚本时，还遇见过脚本是正确的（在sqlplus中正常），但是就是报错，选中错误的地方运行已选择时有时正确的，不知道这是怎么了。