Oracle兼容-语法-EXECUTE IMMEDIATE

Oracle兼容-语法-EXECUTE IMMEDIATE

1. 语法

1. EXECUTE IMMEDIATE dynamic_sql_stmt
2. [ { into_clause  | bulk_collect_into_clause } [ using_clause ]
3. | using_clause
4. ] ;

1. using_clause ::=

3. USING [ IN | OUT | IN OUT ] bind_argument
4. [ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...

1. into_clause ::=

3. INTO { variable [, variable ]... | record )

当 sql_mode 切换到 ORACLE 模式下时，在存储过程中支持如下用法：

1. bulk_collect_into_clause ::=

3. BULK COLLECT INTO { collection | :host_array }
4. [, { collection | :host_array } ]...

2. 定义和用法

EXECUTE IMMEDIATE 用于动态执行SQL语句，是指先把一个SQL命令保存到一个字符串中，然后通过 EXECUTE IMMEDIATE 命令动态执行字符串中的SQL语句，以实现SQL语句的动态生成。

EXECUTE IMMEDIATE 使用时有以下几点注意事项：

1. 参数 USING 支持使用表达式作为预处理变量。
1. 参数 USING OUT / USING IN OUT 支持对变量进行赋值。
1. 支持使用 INTO 将返回结果赋给变量。
1. 在 DYNAMIC_SQL_STMT 子句中支持使用变量。
1. 在 DYNAMIC_SQL_STMT 子句中不支持含有 INTO 的语法。

在GreatSQL和Oracle中的差异点及其他注意事项：

DYNAMIC_SQL_STMT 子句不支持匿名存储过程（例如：BEGIN SELECT :1, :2; END;）运行。
在Oracle中，作为 USING OUT 时在语法上允许 USING OUT TO_CHAR(...) 这种语句运行，但会导致Oracle单个进程断开连接，因此如果用到需要 USING OUT 支持存储过程赋值的情况，将会限制只能使用变量或者系统变量作为参数。

1. > EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT var;  <--允许
2. > EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT @var; <--允许
3. > EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT TO_CAHR(@var); <-- 被禁止

语句 USING OUT 将被视为 USING IN OUT。

1. greatsql> DROP PROCEDURE IF EXISTS p1;
2. greatsql> DELIMITER //
3. greatsql> CREATE PROCEDURE IF NOT EXISTS p1(i INT)
4. AS
5. BEGIN
6. i:= 10;
7. END;

9. EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT a;

在GreatSQL中允许这种方式运行，而在Oracle中会报错，要求 USING IN OUT var 或者 USING IN var;

1. > EXECUTE IMMEDIATE 'SELECT ?' USING OUT a;
2. ERROR HY000: Incorrect arguments to using OUT param var only use for call

上述SQL命令在GreatSQL运行时将会引发报错。

同时使用 USING IN OUT 与 INTO 属于未定义行为，OUT 不一定能够正确赋值。
在使用 USING 字段的时候，预处理的数据内容需要根据数据类型进行判断的时候，默认将会使用字符串类型，部分函数需要类型转换后才可以使用。

1. greatsql> DELIMITER //
2. greatsql> DECLARE
3. v DATETIME;
4. BEGIN
5. v:= TO_DATE('22:31:23', 'HH24:MI:SS');
6. EXECUTE IMMEDIAtE 'SELECT TO_ChAR(?, ''HH24:MI'')' USING v;
7. END; //

9. ERROR 3064 (HY000): Incorrect type for argument args 0 in function to_char.

11. greatsql> DELIMITER //
12. greatsql> DECLARE
13. v DATETIME;
14. BEGIN
15. v:= TO_DATE('22:31:23', 'hh24:mi:ss');
16. EXECUTE IMMEDIATE 'SELECT TO_CHAR(CAST( ? AS DATETIME) , ''hh24:mi'')' USING v;
17. END; //
18. +-------------------------------------------+
19. | TO_CHAR(CAST( ? AS DATETIME) , 'hh24:mi') |
20. +-------------------------------------------+
21. | 22:31                                     |
22. +-------------------------------------------+

3. 示例

先创建基本测试表t1：

1. greatsql> DROP TABLE IF EXISTS t1;
2. greatsql> CREATE TABLE IF NOT EXISTS t1
3. (
4. id INT UNSIGNED NOT NULL AUTO_INCREMENT,
5. a INT,
6. PRIMARY KEY(id));

支持DDL、DML、TCL（事务控制语言）语句。

1. EXECUTE IMMEDIATE 'CREATE  TABLE t1 (a INT DEFAULT 10, b varchar(255) NOT NULL DEFAULT 1);';
2. EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS t1;';
3. EXECUTE IMMEDIATE 'ALTER TABLE \`t1\` ADD COLUMN \`b\` varchar(255) NOT NULL DEFAULT 1;';
4. EXECUTE IMMEDIATE 'CREATE VIEW  v1 AS SELECT * FROM t1;';
5. EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS v1;';
6. EXECUTE IMMEDIATE 'ALTER TABLE t1 ADD INDEX k1 (a) ;';
7. EXECUTE IMMEDIATE 'DROP INDEX k1 ON t1;';

9. EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (2,3)';
10. EXECUTE IMMEDIATE 'UPDATE t1 SET a=3 WHERE a=2;';
11. EXECUTE IMMEDIATE 'DELETE FROM t1 WHERE a=2;';
12. EXECUTE IMMEDIATE 'SELECT * FROM t1;';
13. EXECUTE IMMEDIATE 'ROLLBACK';
14. EXECUTE IMMEDIATE 'COMMIT;';

支持 SET 赋值临时变量。

1. EXECUTE IMMEDIATE 'SET @param=(SELECT a FROM t1 LIMIT 1);';
2. SELECt @param;

支持利用 USING 绑定参数与表达式。

1. greatsql> SET @a=1;
2. greatsql> EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,1)' USING @a;
3. Query OK, 1 row affected (0.00 sec)

5. greatsql> EXECUTE IMMEDIATE 'SELECT ?' USING TO_CHAR(1);
6. +------+
7. | ?    |
8. +------+
9. | 1    |
10. +------+

12. -- 切换sql_mode为ORACLE
13. greatsql> SET sql_mode = ORACLE;
14. greatsql> DELIMITER //
15. greatsql> DECLARE
16. v1 INT:= 2;
17. BEGIN
18. EXECUTE IMMEDIATE 'SELECT ?' USING v1;
19. END; //
20. +------+
21. | ?    |
22. +------+
23. |    2 |
24. +------+

支持存储过程中使用 EXECUTE IMMEDIATE。

1. greatsql> DROP PROCEDURE IF EXISTS p1;
2. greatsql> DELIMITER //
3. CREATE PROCEDURE IF NOT EXISTS p1 (x int)
4. BEGIN
5. SET @a = x;
6. EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a = ?;' USING @a;
7. EXECUTE IMMEDIATE 'ALTER TABLE \`t1\` add column \`c\` VARCHAR(255) NOT NULL DEFAULT 123456;';
8. EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a = ?;' USING @a;
9. END; //

11. greatsql> CALL p1(1); //
12. +----+------+
13. | id | a    |
14. +----+------+
15. |  1 |    1 |
16. +----+------+
17. 1 row in set (0.00 sec)

19. +----+------+--------+
20. | id | a    | c      |
21. +----+------+--------+
22. |  1 |    1 | 123456 |
23. +----+------+--------+

调用存储过程。

1. greatsql> DROP PROCEDURE IF EXISTS p2;
2. greatsql> DELIMITER //
3. greatsql> CREATE PROCEDURE IF NOT EXISTS p2(OUT a INT)
4. BEGIN
5. SET a:= 10;
6. SELECT CONCAT("IN a=", a);
7. END; //

9. greatsql> DELIMITER ;
10. greatsql> SET @a = 2;
11. greatsql> EXECUTE IMMEDIATE 'CALL p2(?)' USING @a;
12. +--------------------+
13. | CONCAT("IN a=", a) |
14. +--------------------+
15. | IN a=10            |
16. +--------------------+

18. greatsql> SELECT @a;
19. +------+
20. | @a   |
21. +------+
22. |    2 |
23. +------+

25. -- 注意和上例多了个 OUT 关键字后，返回结果不同
26. greatsql> EXECUTE IMMEDIATE 'CALL p2(?)' USING OUT @a;
27. +--------------------+
28. | CONCAT("IN a=", a) |
29. +--------------------+
30. | IN a=10            |
31. +--------------------+

33. greatsql> SELECT @a;
34. +------+
35. | @a   |
36. +------+
37. |   10 |
38. +------+

40. greatsql> DROP PROCEDURE IF EXISTS p2;

变量作为语句。

1. greatsql> SET @v = 'SELECT "GreatSQL"';

3. greatsql> EXECUTE IMMEDIATE @v;
4. +----------+
5. | GreatSQL |
6. +----------+
7. | GreatSQL |
8. +----------+

支持存储过程中的变量作为语句。

1. -- 切换sql_mode为ORACLE
2. greatsql> SET sql_mode = ORACLE;
3. greatsql> DELIMITER //
4. greatsql> DECLARE
5. v VARCHAR(200);
6. BEGIN
7. v:= 'SELECT \'GreatSQL\', ' || TO_CHAR(3306);

9. EXECUTE IMMEDIATE v;
10. END; //

12. +----------+------+
13. | GreatSQL | 3306 |
14. +----------+------+
15. | GreatSQL | 3306 |
16. +----------+------+

支持用 INTO/BULK COLLECT INTO 获取查询结果。

1. greatsql> EXECUTE IMMEDIATE 'SELECT \'GreatSQL\'' INTO @v;

3. greatsql> SELECT @v;
4. +----------+
5. | @v       |
6. +----------+
7. | GreatSQL |
8. +----------+

10. greatsql> SET sql_mode = ORACLE;
11. greatsql> DELIMITER //
12. greatsql> DECLARE
13. v1 INT := 2;
14. BEGIN
15. EXECUTE IMMEDIATE 'SELECT ?' INTO v1 USING 1;
16. SELECT v1;
17. END; //

19. +------+
20. | v1   |
21. +------+
22. |    1 |
23. +------+

25. greatsql> SET sql_mode = ORACLE;
26. greatsql> DELIMITER //
27. greatsql> DECLARE
28. TYPE 1type IS RECORD
29. (
30. a INT
31. );
32. TYPE array_param_type IS TABLE OF 1type;
33. ary array_param_type;
34. BEGIN
35. EXECUTE IMMEDIATE 'SELECT a FROM t1' BULK COLLECT INTO ary;
36. SELECT ary;
37. END; //

39. +----------------------------+
40. | ary                        |
41. +----------------------------+
42. | ARRAY_PARAM_TYPE(1type(1)) |
43. +----------------------------+

不支持使用非定义参数。

1. greatsql> CREATE TABLE t3 (a INT DEFAULT 10);
2. greatsql> EXECUTE IMMEDIATE 'INSERT INTO t3 VALUES (?)' USING DEFAULT;
3. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

不支持数据库切换语句。

1. greatsql> EXECUTE IMMEDIATE 'USE greatsql;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持创建/删除存储过程。

1. greatsql> EXECUTE IMMEDIATE 'CREATE PROCEDURE p5(x int) BEGIN SET @a = x; END;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

4. greatsql> EXECUTE IMMEDIATE 'drop PROCEDURE p1;';
5. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持创建/删除函数。

1. greatsql> EXECUTE IMMEDIATE 'CREATE FUNCTION f1(a int) RETURNS INT BEGIN RETURN a * 10; END;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

4. greatsql> EXECUTE IMMEDIATE 'DROP FUNCTION f1;';
5. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持创建/删除触发器。

1. greatsql> EXECUTE IMMEDIATE 'CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.c0;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

4. greatsql> EXECUTE IMMEDIATE 'drop TRIGGER IF EXISTS trig1;';
5. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持handler使用。

1. greatsql> EXECUTE IMMEDIATE 'handler test_number open;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持开始事务操作。

1. greatsql> EXECUTE IMMEDIATE 'START TRANSACTION;';
2. ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

不支持语句中带有INTO。

1. greatsql> EXECUTE IMMEDIATE 'SELECT 1 INTO @v;';
2. ERROR HY000: Misplaced INTO clause, INTO is not allowed inside EXECUTE IMMEDIATE query string

问题反馈

问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号