GreatSQL高兼容

从GreatSQL 8.0.32-25版本开始，在Oracle兼容方面有了巨大提升，除了OCI、DBlink、包之外，几乎支持绝大多数常用的Oracle语法、数据类型、函数、存储过程等功能。

1. Oracle兼容设计思路概述

GreatSQL的Oracle的兼容处理的优先原则如下：

  1. GreatSQL风格的SQL和存储过程可以直接在默认SQL MODE模式下工作。
  2. 与GreatSQL风格的SQL和存储过程不冲突的Oracle兼容功能也可以直接在默认SQL MODE模式下工作。
  3. 与GreatSQL风格的SQL和存储过程存在语法或语义冲突的功能，需要用户显式切换到Oracle MODE模式下才能工作。

GreatSQL对Oracle的兼容主要通过以下三种不同方案来实现：

  • 原生模式：Oracle、GreatSQL都支持或部分支持通用的SQL标准，对于部分简单语句，无需调整即可兼容。
  • 兼容扩展：GreatSQL对于部分Oracle独有的函数和语法，在GreatSQL的Server层实现对其扩展，如：MERGE INTO、CONNECT BY，这写类型的兼容特性无需额外设定SQL MODE，直接使用即可。
  • 兼容模式：在GreatSQL中设置 SET sql_mode = ORACLE; 即可将当前会话切换到Oracle兼容模式。在该模式下，当Oracle语法与GreatSQL语法存在语法或语义上的冲突时，GreatSQL会自行选择Oracle兼容模式。

更多关于Oracle兼容模式的说明请查看文档：Oracle mode。

2. 数据类型兼容

在GreatSQL中，采用映射方式实现数据类型兼容，这属于 扩展兼容(无需设定 sql_mode) 方案。具体实现方式为：简单别名，即：在解析阶段将关键词进行替换。例如：如果使用CLOB创建的表，在系统内会被转换成LONGTEXT。

具体实现的映射包括：

Oracle类型GreatSQL类型兼容程度
CLOBLONGTEXT简单别名
NUMBERDECIMAL简单别名
VARCHAR2VARCHAR简单别名
PLS_INTEGERINT简单别名

示例：

  1. greatsql> CREATE TABLE t1(
  2. id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3. c1 CLOB NOT NULL DEFAULT '', 
  4. c2 VARCHAR2(30) NOT NULL DEFAULT '');
  5. ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'c1' can't have a default value
  7. greatsql> CREATE TABLE t1(
  8. id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  9. c1 CLOB NOT NULL, 
  10. c2 VARCHAR2(30) NOT NULL DEFAULT '');
  11. Query OK, 0 rows affected (0.25 sec)
  13. greatsql> SHOW CREATE TABLE t1\G
  14. *************************** 1. row ***************************
  15.        Table: t1
  16. Create Table: CREATE TABLE `t1` (
  17.   `id` int unsigned NOT NULL AUTO_INCREMENT,
  18.   `c1` longtext NOT NULL,
  19.   `c2` varchar(30) NOT NULL DEFAULT '',
  20.   PRIMARY KEY (`id`)
  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  22. 1 row in set (0.01 sec)

更详细信息请参考 数据类型兼容。

3. SQL语法兼容

3.1 扩展兼容(无需设定 sql_mode)支持的SQL语法

3.2 兼容模式(需设定 sql_mode)支持的SQL语法

4. 函数兼容

4.1 扩展兼容(无需设定 sql_mode)支持的函数

4.2 兼容模式(需设定 sql_mode)支持的函数

注意：以上函数在设定 sql_mode = ORACLE 后，行为与Oracle会更加接近；反之则保持GreatSQL的原生行为。

5. 存储过程/函数兼容

GreatSQL支持Oracle风格的存储过程使用方式，部分存储过程/函数部分在 ORACLE 模式下做了基础结构改造，详见：存储过程基础结构改造说明。

5.1 扩展兼容(无需设定 sql_mode)支持的存储过程/函数用法

4.2 扩展兼容(无需设定 sql_mode)支持的存储过程/函数用法GreatSQL实现的兼容模式(需设定 sql_mode = ORACLE)，包括：

示例:

  • Oracle环境下的存储过程用法:
  1. CREATE OR REPLACE EDITIONABLE FUNCTION f0(delta INT DEFAULT 0) RETURN TIMESTAMP AS
  2.     cnt INT := 10;
  3. BEGIN
  4.     RETURN SYSDATE + delta*cnt;
  5. END;
  7. SELECT f0(2) FROM DUAL ;
  8. SELECT f0() FROM DUAL ;
  • GreatSQL 原生模式下的存储过程用法:
  1. CREATE OR REPLACE FUNCTION f1(delta INT) RETURNS TIMESTAMP
  2. BEGIN
  3.     DECLARE cnt INT DEFAULT 10;
  4.     SET delta = cnt * delta;
  5.     RETURN DATE_ADD(SYSDATE(), INTERVAL delta DAY);
  6. END;
  8. SELECT f0(2) FROM DUAL ;
  • GreatSQL 兼容模式 下存储过程用法:
  1. SET sql_mode = ORACLE;
  3. CREATE OR REPLACE FUNCTION f0(delta INT DEFAULT 0) RETURN TIMESTAMP AS
  4.     cnt INT := 10;
  5. BEGIN
  6.     RETURN SYSDATE + delta*cnt;
  7. END;
  9. SELECT f0(2) FROM DUAL ;
  10. SELECT f0() FROM DUAL ;

6. Oracle兼容常见问题

6.1 可以不显式使用 sql_mode = ORACLE 来触发 兼容模式 吗

  • 在GreatSQL配置文件my.cnf中添加 sql_mode = ORACLE 修改默认 sql_mode 值将导致GreatSQL功能异常，因为GreatSQL所依赖的基础存储过程并没有被执行兼容改造。
  • 在GreatSQL初始化完成后，在配置文件my.cnf中添加 sql_mode = ORACLE 后并重启GreatSQL，新建的连接默认都会切换到Oracle兼容模式下。
  • 在GreatSQL数据库进行时，修改全局设置 SET GLOBAL sql_mode = ORACLE 后，新建的连接默认都会切换到Oracle兼容模式下。GreatSQL重启后该默认行为失效，如果想要让这个设置持久化，可以改成 SET PERSIST sql_mode = ORACLE。
  • 还可以在连接会话配置中固化设置 sql_mode = ORACLE。例如，在JDBC连接串中添加 session_variables = "sql_mode = ORACLE"，这样连接中SQL就无需显式设定兼容模式。

6.2 可以在存储过程内切换兼容模式吗

在GreatSQL中，兼容模式 或者说 sql mode 是存储过程的固有属性，决定了该存储过程如何被解析和运行。在存储过程中修改 sql_mode 可以改变当前的兼容模式，但当该存储过程运行结束返回后，上层上下文当中的兼容模式会被还原（与存储过程运行前一致）。

举例说明：

  1. -- sql mode演示代码
  3. SET sql_mode = DEFAULT;
  5. DROP PROCEDURE mode_test;
  7. DELIMITER $ ;
  8. CREATE PROCEDURE mode_test() BEGIN
  9.  SELECT LENGTH('测试'), @@sql_mode;
  10.  SET sql_mode = ORACLE;
  11.  SELECT LENGTH('测试'), @@sql_mode;
  12. END ;
  13. $
  15. DELIMITER ; $
  17. SET sql_mode = DEFAULT;
  18. SELECT 'before test 1', LENGTH('测试'), @@sql_mode;
  19. CALL mode_test();
  20. SELECT 'after test 1', LENGTH('测试'), @@sql_mode;

该存储过程运行结果如下：

  1. greatsql> SET sql_mode = DEFAULT;
  3. -- 执行测试前调用LENGTH，输出按照GreatSQL默认行为，显示字符字节长度=6
  4. greatsql> SELECT 'before test 1', LENGTH('测试'), @@sql_mode;
  5. | before test 1 | LENGTH('测试')   | @@sql_mode | 
  6. | before test 1 |                6 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION |
  8. greatsql> CALL mode_test();
  9. -- 存储过程中，前调用LENGTH，输出按照GreatSQL默认行为，显示字符字节长度=6
  10. | LENGTH('测试')   | @@sql_mode  |
  11. |                6 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
  13. -- 存储过程中，设定Oracle兼容模式，调用LENGTH，输出按照Oracle兼容行为，显示字符字节长度=2  
  14. | LENGTH('测试')   | @@sql_mode                                                     |
  15. |                2 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,SYSDATE_IS_NOW |
  18. greatsql> SELECT 'after test 1', LENGTH('测试'), @@sql_mode;
  19. -- 返回存储过程后，调用LENGTH，输出按照GreatSQL默认行为，显示字符字节长度=6
  20. | before test 1 |                6 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION |

6.3 可以在触发器（trigger）和事件（event）中切换SQL兼容模式吗

在GreatSQL中，定义触发器前设定SQL兼容模式或修改sql_mode将影响触发器内SQL语法或函数的兼容风格。

6.4 为什么有些案例结果不同或无法运行通过

有可能是因为个别选项参数或sql_mode设置不同，导致结果不同，甚至无法运行通过。

例如在某个存储过程中，可能用到了 RAND() 函数，它的输出结果是随机值，那么结果可能就不同了。

又或者在存储过程REF CURSOR, SYS_REFCURSOR用法案例中，需要确保先设置 sql_generate_invisible_primary_key = 0，否则可能导致存储过程运行异常，报告类似下面的错误：

  1. greatsql> CALL p1()//
  2. ERROR 1328 (HY000): Incorrect number of FETCH variables

可以通过执行 SELECT @@sql_mode、SHOW CREATE TABLE ... 或 SHOW CREATE PROCEDURE ... 来确认 sql_mode、表结构、存储过程、触发器、视图等的定义是否和手册中的案例一致。

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx