统一SQL支持改写建表语句自增序列
【统一sql】:https://www.light-pg.com/docs/LTSQL/current/index.html
LightDB 统一SQL 是一款基于 Go 开发的 SQL 转换中间件,支持将 Oracle 常用 SQL 语法翻译转换为其他数据库的 SQL 。以主流数据库Oracle的SQL为基准,将SQL语句转换为其他信创数据库(LightDB、PostgreSQL、TDSQL、DM8、OceanBase、openGauss等)的SQL语句。目标是降低业务部门适配信创数据库的成本,让用户尽可能少地修改数据访问层代码,使基于Hibernate、JPA、MyBatis等框架的Java应用程序,基于oci、libmysqlclient、cres开发工具、hsdb的c/c++应用程序以及其它能够调用c函数的程序都能够直接切换到目标信创数据库,实现信创数据库之间的平滑迁移。
前言
Oracle2DM、Oracle2TDSQL-MYSQL5.7、Oracle2OceanBase-Oracle、Oracle2PostgreSQL、Oracle2LightDB-Oracle支持改写Oracle的DDL中列定义默认值使用expr表达式的用法支持函数、序列。
序列用法至少支持NEXTVAL、CURRVAL。
Oracle2TDQL-MySQL5.7/Oracle2OceanBase-MySQL的SYSDATE函数需要改写成CURRENT_TIMESTAMP()
自增序列、函数
转化
oracle2postgresql:
nextval\currval 关键字转为 nextval()\currval()函数
oracle2DM8、oracle2OceanBase、oracle2LightDB-Oracle
nextval\currval 关键字转为 自定义的nextval\currval关键字
oracle2TDSQL-Mysql5.7
nextval\currval 关键字转为 自定义nextval()\currval()函数
Oracle:
create table table_name ( ... default sequence_name.nextval, ...default sequence_name.currval)
转PostgreSQL:
create table table_name ( ... default nextval(‘sequence_name‘), ...default currval(‘sequence_name‘))
转DM8/ OceanBase-Oracle /LightDB-Oracle
create table table_name ( ... default sequence_name.nextval, ...default sequence_name.currval)
转TDSQL-MySQL5.7
create table table_name ( ... default `unisql`.nextval(‘sequence_name‘), ...default `unisql`.currval(‘sequence_name‘))
oracle
sysdate 关键字
转TDSQL-MySQL5.7
current_timestamp() 函数
其中对转TDSQL-Mysql5.7 自定义函数如下所示
DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`CURRVAL` $$
CREATE FUNCTION unisql.`CURRVAL` (V_SEQ_CODE VARCHAR (64))
RETURNS BIGINT (16)
READS SQL DATA
BEGIN
DECLARE VALUE BIGINT;
SELECT CURRENT_VAL INTO VALUE FROM unisql.`UNISQL_SEQUENCE` WHERE SEQ_CODE = V_SEQ_CODE;
if VALUE is null then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
end if;
RETURN VALUE;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS unisql.`NEXTVAL`$$
CREATE FUNCTION unisql.`NEXTVAL`(V_SEQ_CODE VARCHAR(64))
RETURNS BIGINT(16)
MODIFIES SQL DATA
BEGIN
UPDATE unisql.UNISQL_SEQUENCE SET CURRENT_VAL = CURRENT_VAL + INCREMENT_VAL WHERE SEQ_CODE = V_SEQ_CODE;
if ROW_COUNT() = 0 then
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Sequence not found';
end if;
RETURN unisql.CURRVAL(V_SEQ_CODE);
END$$
DELIMITER ;
结果
通过统一sql转化之后的结果:
数据准备:
drop table table_name;
create sequence table_name_seq;
-- 转换前Oracle SQL:
create table table_name (
id NUMBER DEFAULT table_name_seq.NEXTVAL PRIMARY KEY,
column2 NUMBER,
column3 NUMBER DEFAULT table_name_seq.CURRVAL
)
-- 转换后PostgreSQL SQL:
CREATE TABLE table_name (id decimal DEFAULT nextval('table_name_seq') PRIMARY KEY,column2 decimal,column3 decimal DEFAULT currval('table_name_seq'))
-- 转换后DM8/ OceanBase-Oracle /LightDB-Oracle
CREATE TABLE table_name (id number DEFAULT table_name_seq.NEXTVAL PRIMARY KEY,column2 number,column3 number DEFAULT table_name_seq.CURRVAL)
-- 转换后TDSQL-Mysql5.7
CREATE TABLE `table_name` (`id` decimal DEFAULT `unisql`.nextval('table_name_seq') PRIMARY KEY,`column2` decimal,`column3` decimal DEFAULT `unisql`.currval('table_name_seq'))
-- 转换前Oracle SQL:
select sysdate from dual
-- 转换后TDSQL-Mysql5.7
SELECT current_timestamp() FROM dual
总结
当currval,nextval作为表字段和sequence_name.currval,sequence_name.nextval出现在同一个SQL语句时,建议表字段使用双引号引起来,否则转换后可能得到和预期不一致的结果。 同时注意表的别名和sequence_name不要相同,会造成一定的歧义。
-- 如果表结构定义如下:
DROP TABLE unisql_key_test_2;
CREATE TABLE unisql_key_test_2(
id NUMBER,
currval varchar(10),
nextval varchar(10)
);
INSERT INTO unisql_key_test_2(currval,nextval) values('currval1','nextval1');
-- 同时使用不支持,转换结果可能和预期不一致。
SELECT unisql_seq_1.nextval,nextval,unisql_seq_1.currval,currval FROM unisql_key_test_2;
-- 推荐使用方式:
-- 如果需要同时使用,表字段请用双引号包裹起来
CREATE TABLE unisql_key_test_3(
id NUMBER,
"currval" varchar(10),
"nextval" varchar(10)
);
INSERT INTO unisql_key_test_3("currval","nextval") values('currval1','nextval1');
SELECT unisql_seq_1.nextval,"nextval",unisql_seq_1.currval,"currval" FROM unisql_key_test_3;
-- 注意以下语句转换会得到和预期不一样的结果
SELECT unisql_seq_1.nextval,unisql_seq_1.lnextval,unisql_seq_1.currval,unisql_seq_1.currval FROM unisql_key_test_1 unisql_seq_1;
Oracle2TDSQL-MySQL5.7如何创建序列,详见:https://www.light-pg.com/docs/LTSQL/current/develop/Oracle2TDSQL-MySQL/sql-syntax.html#sequence