转载地址:https://blog.csdn.net/MonkeySun123321/article/details/83306030
库表转换和数据转换
操作步骤:1.下载 PowerDesigner和Navicat Premium这2个软件
2.通过PL/SQL导出需要导出的用户建表脚本
3.通过PowerDesigner将oracle的建表脚本转换为mysql的建表脚本
参考链接如下 https://blog.csdn.net/qq_28194001/article/details/79124090
4.在通过Navicat Premium这个软件执行建表脚本并同步数据
参考链接如下 https://blog.csdn.net/superit401/article/details/51547745
Navcat安装包及破解工具下载: https://download.csdn.net/download/monkeysun123321/10354491
PowerDesigner安装包下载 :https://download.csdn.net/download/monkeysun123321/10739179
2.数据库差异总结
2.1 sequence
oracle有自增序列sequence,但mysql没有。需要模拟相同的效果。
建表:
--- 建表
CREATE TABLE `sequence` (
`name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
`current_value` int(11) NOT NULL COMMENT '序列的当前值',
`increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
----创建 获取当前值的 function
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END
$
DELIMITER ;
----创建 获取下一个值的 function
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
----创建 设置当前值的 function (基本不用)
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
UPDATE sequence
SET current_value = value
WHERE name = seq_name;
RETURN currval(seq_name);
END
$
DELIMITER ;
INSERT INTO sequence VALUES ('SMS_SEQID_SEQUENCE_MQ', 1, 1); -- 添加一个sequence名称和初始值,以及自增幅度
SELECT SETVAL('SMS_SEQID_SEQUENCE_MQ', 10); -- 设置指定sequence的初始值
SELECT CURRVAL('SMS_SEQID_SEQUENCE_MQ'); -- 查询指定sequence的当前值
SELECT NEXTVAL('SMS_SEQID_SEQUENCE_MQ') ; -- 查询指定sequence的下一个值
2.2 多级递归调用
Oracle的写法:
START WITH mcht_code = :parentMchtCode CONNECT BY NOCYCLE PRIOR mcht_code = parent_mcht_code
使用语法: start with .. connect by nocycle prior ..
Mysql没有类似函数,需要进行自定义。方法如下:
2.2.1 主键和递归关联的外键为int等数字类型
------主键和递归关联的外键为int等数字类型
CREATE TABLE `province` (
`id` int(10) NOT NULL,
`name` varchar(10) NOT NULL,
`pid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of province
-- ----------------------------
INSERT INTO `province` VALUES ('1', '福建', '0');
INSERT INTO `province` VALUES ('2', '湖南', '0');
INSERT INTO `province` VALUES ('3', '湖北', '0');
INSERT INTO `province` VALUES ('4', '长沙', '2');
INSERT INTO `province` VALUES ('5', '郴州', '2');
INSERT INTO `province` VALUES ('6', '武汉', '3');
INSERT INTO `province` VALUES ('7', '武昌', '3');
INSERT INTO `province` VALUES ('8', '厦门', '1');
INSERT INTO `province` VALUES ('9', '福州', '1');
INSERT INTO `province` VALUES ('10', '泉州', '1');
INSERT INTO `province` VALUES ('11', '闽侯', '9');
INSERT INTO `province` VALUES ('12', '长乐', '9');
INSERT INTO `province` VALUES ('13', '安溪', '10');
INSERT INTO `province` VALUES ('14', '晋江', '10');
INSERT INTO `province` VALUES ('15', '凤城', '13');
INSERT INTO `province` VALUES ('16', '参内', '13');
INSERT INTO `province` VALUES ('17', '龙湖', '15');
---执行 查询所有子类别的函数
delimiter //
CREATE FUNCTION `getChildrenList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM province where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//
//执行查询所有父类别的函数
delimiter //
CREATE FUNCTION `getParentList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp ='';
SET sTempPar =rootId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个,不加的话第一个会为空
IF sTemp !='' THEN
SET sTemp =concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp =concat(sTemp,',',sTempPar);
SELECT group_concat(pid) INTO sTempPar FROM province where pid <> id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
//
----查询该ID所有父数据
select * from province where FIND_IN_SET(id,getParentList(17));
---查询该ID所有子数据
select * from province where FIND_IN_SET(id,getChildrenList(1));
2.2.2 主键和递归关联的外键为varchar等数字类型
-----getChildrenList_mcht
delimiter //
CREATE FUNCTION `getChildrenList_mcht`(rootId varchar(80))
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd = rootId;
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(mcht_code) INTO sTempChd FROM Organization where FIND_IN_SET(parent_mcht_code,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//
--- getParentList_mcht
delimiter //
CREATE FUNCTION `getParentList_mcht`(rootId varchar(80))
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp ='';
SET sTempPar =rootId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个,不加的话第一个会为空
IF sTemp !='' THEN
SET sTemp =concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp =concat(sTemp,',',sTempPar);
SELECT group_concat(parent_mcht_code) INTO sTempPar FROM Organization where mcht_code <> parent_mcht_code and FIND_IN_SET(mcht_code,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
//
---查询所有子类别
select * from Organization where FIND_IN_SET(mcht_code, getChildrenList_mcht('M002'));
---查询所有父类别
select * from Organization where FIND_IN_SET(mcht_code,getParentList_mcht('M002001'));
其他差异见另一篇博文:
https://blog.csdn.net/tuesdayma/article/details/78414511?utm_source=blogxgwz0
---------------------
版权声明:本文为CSDN博主「hunterZhao333」的原创文章,遵循CC 4.0 by-sa版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/monkeysun123321/article/details/83306030