§ Oracle兼容-语法-EXECUTE IMMEDIATE


§ 1. 语法

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

USING [ IN | OUT | IN OUT ] bind_argument
  [ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...
 
1
2
3
4
5
into_clause ::=

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

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

bulk_collect_into_clause ::=

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

§ 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 支持存储过程赋值的情况,将会限制只能使用变量或者系统变量作为参数。

> EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT var;  <--允许
> EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT @var; <--允许
> EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT TO_CAHR(@var); <-- 被禁止
1
2
3
  • 语句 USING OUT 将被视为 USING IN OUT
greatsql> DROP PROCEDURE IF EXISTS p1;
greatsql> DELIMITER //
greatsql> CREATE PROCEDURE IF NOT EXISTS p1(i INT)
AS
 BEGIN
  i:= 10;
END;

EXECUTE IMMEDIATE 'CALL p1(?)' USING OUT a; 
1
2
3
4
5
6
7
8
9

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

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

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

  • 同时使用 USING IN OUTINTO 属于未定义行为,OUT 不一定能够正确赋值。

  • 在使用 USING 字段的时候,预处理的数据内容需要根据数据类型进行判断的时候,默认将会使用字符串类型,部分函数需要类型转换后才可以使用。

greatsql> DELIMITER //
greatsql> DECLARE
     v DATETIME;
 BEGIN
      v:= TO_DATE('22:31:23', 'HH24:MI:SS');
     EXECUTE IMMEDIAtE 'SELECT TO_ChAR(?, ''HH24:MI'')' USING v;
END; //
 
ERROR 3064 (HY000): Incorrect type for argument args 0 in function to_char.
 
greatsql> DELIMITER //
greatsql> DECLARE
    v DATETIME;
BEGIN
    v:= TO_DATE('22:31:23', 'hh24:mi:ss');
    EXECUTE IMMEDIATE 'SELECT TO_CHAR(CAST( ? AS DATETIME) , ''hh24:mi'')' USING v;
END; //
+-------------------------------------------+
| TO_CHAR(CAST( ? AS DATETIME) , 'hh24:mi') |
+-------------------------------------------+
| 22:31                                     |
+-------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

§ 3. 示例

先创建基本测试表t1:

greatsql> DROP TABLE IF EXISTS t1;
greatsql> CREATE TABLE IF NOT EXISTS t1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
a INT,
PRIMARY KEY(id));
1
2
3
4
5
6
  • 支持DDLDMLTCL(事务控制语言)语句。
EXECUTE IMMEDIATE 'CREATE  TABLE t1 (a INT DEFAULT 10, b varchar(255) NOT NULL DEFAULT 1);';
EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS t1;';
EXECUTE IMMEDIATE 'ALTER TABLE \`t1\` ADD COLUMN \`b\` varchar(255) NOT NULL DEFAULT 1;';
EXECUTE IMMEDIATE 'CREATE VIEW  v1 AS SELECT * FROM t1;';
EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS v1;';
EXECUTE IMMEDIATE 'ALTER TABLE t1 ADD INDEX k1 (a) ;';
EXECUTE IMMEDIATE 'DROP INDEX k1 ON t1;';

EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (2,3)';
EXECUTE IMMEDIATE 'UPDATE t1 SET a=3 WHERE a=2;';
EXECUTE IMMEDIATE 'DELETE FROM t1 WHERE a=2;';
EXECUTE IMMEDIATE 'SELECT * FROM t1;';
EXECUTE IMMEDIATE 'ROLLBACK';
EXECUTE IMMEDIATE 'COMMIT;';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
  • 支持 SET 赋值临时变量。
EXECUTE IMMEDIATE 'SET @param=(SELECT a FROM t1 LIMIT 1);';
SELECt @param;
1
2
  • 支持利用 USING 绑定参数与表达式。
greatsql> SET @a=1;
greatsql> EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,1)' USING @a;
Query OK, 1 row affected (0.00 sec)

greatsql> EXECUTE IMMEDIATE 'SELECT ?' USING TO_CHAR(1);
+------+
| ?    |
+------+
| 1    |
+------+

-- 切换sql_mode为ORACLE
greatsql> SET sql_mode = ORACLE;
greatsql> DELIMITER //
greatsql> DECLARE
 v1 INT:= 2;
BEGIN
 EXECUTE IMMEDIATE 'SELECT ?' USING v1;
END; //
+------+
| ?    |
+------+
|    2 |
+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  • 支持存储过程中使用 EXECUTE IMMEDIATE。
greatsql> DROP PROCEDURE IF EXISTS p1;
greatsql> DELIMITER //
CREATE PROCEDURE IF NOT EXISTS p1 (x int)
BEGIN
 SET @a = x;
 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a = ?;' USING @a;
 EXECUTE IMMEDIATE 'ALTER TABLE \`t1\` add column \`c\` VARCHAR(255) NOT NULL DEFAULT 123456;';
 EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a = ?;' USING @a;
END; //

greatsql> CALL p1(1); //
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

+----+------+--------+
| id | a    | c      |
+----+------+--------+
|  1 |    1 | 123456 |
+----+------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  • 调用存储过程。
greatsql> DROP PROCEDURE IF EXISTS p2;
greatsql> DELIMITER //
greatsql> CREATE PROCEDURE IF NOT EXISTS p2(OUT a INT)
BEGIN
 SET a:= 10;
 SELECT CONCAT("IN a=", a);
END; //

greatsql> DELIMITER ;
greatsql> SET @a = 2;
greatsql> EXECUTE IMMEDIATE 'CALL p2(?)' USING @a;
+--------------------+
| CONCAT("IN a=", a) |
+--------------------+
| IN a=10            |
+--------------------+

greatsql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+

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

greatsql> SELECT @a;
+------+
| @a   |
+------+
|   10 |
+------+

greatsql> DROP PROCEDURE IF EXISTS p2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
  • 变量作为语句。
greatsql> SET @v = 'SELECT "GreatSQL"';

greatsql> EXECUTE IMMEDIATE @v;
+----------+
| GreatSQL |
+----------+
| GreatSQL |
+----------+
1
2
3
4
5
6
7
8
  • 支持存储过程中的变量作为语句。
-- 切换sql_mode为ORACLE
greatsql> SET sql_mode = ORACLE;
greatsql> DELIMITER //
greatsql> DECLARE 
 v VARCHAR(200);
BEGIN 
 v:= 'SELECT \'GreatSQL\', ' || TO_CHAR(3306);

 EXECUTE IMMEDIATE v;
END; //

+----------+------+
| GreatSQL | 3306 |
+----------+------+
| GreatSQL | 3306 |
+----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  • 支持用 INTO/BULK COLLECT INTO 获取查询结果。
greatsql> EXECUTE IMMEDIATE 'SELECT \'GreatSQL\'' INTO @v;

greatsql> SELECT @v;
+----------+
| @v       |
+----------+
| GreatSQL |
+----------+

greatsql> SET sql_mode = ORACLE;
greatsql> DELIMITER //
greatsql> DECLARE
 v1 INT := 2;
BEGIN
 EXECUTE IMMEDIATE 'SELECT ?' INTO v1 USING 1;
 SELECT v1;
END; //

+------+
| v1   |
+------+
|    1 |
+------+

greatsql> SET sql_mode = ORACLE;
greatsql> DELIMITER //
greatsql> DECLARE
 TYPE 1type IS RECORD
 (
  a INT
 );
 TYPE array_param_type IS TABLE OF 1type;
 ary array_param_type;
BEGIN
 EXECUTE IMMEDIATE 'SELECT a FROM t1' BULK COLLECT INTO ary;
 SELECT ary;
END; //

+----------------------------+
| ary                        |
+----------------------------+
| ARRAY_PARAM_TYPE(1type(1)) |
+----------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
  • 不支持使用非定义参数。
greatsql> CREATE TABLE t3 (a INT DEFAULT 10);
greatsql> EXECUTE IMMEDIATE 'INSERT INTO t3 VALUES (?)' USING DEFAULT;
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
2
3
  • 不支持数据库切换语句。
greatsql> EXECUTE IMMEDIATE 'USE greatsql;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
  • 不支持创建/删除存储过程。
greatsql> EXECUTE IMMEDIATE 'CREATE PROCEDURE p5(x int) BEGIN SET @a = x; END;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

greatsql> EXECUTE IMMEDIATE 'drop PROCEDURE p1;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
3
4
5
  • 不支持创建/删除函数。
greatsql> EXECUTE IMMEDIATE 'CREATE FUNCTION f1(a int) RETURNS INT BEGIN RETURN a * 10; END;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

greatsql> EXECUTE IMMEDIATE 'DROP FUNCTION f1;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
3
4
5
  • 不支持创建/删除触发器。
greatsql> EXECUTE IMMEDIATE 'CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.c0;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

greatsql> EXECUTE IMMEDIATE 'drop TRIGGER IF EXISTS trig1;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
3
4
5
  • 不支持handler使用。
greatsql> EXECUTE IMMEDIATE 'handler test_number open;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
  • 不支持开始事务操作。
greatsql> EXECUTE IMMEDIATE 'START TRANSACTION;';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
1
2
  • 不支持语句中带有INTO。
greatsql> EXECUTE IMMEDIATE 'SELECT 1 INTO @v;';
ERROR HY000: Misplaced INTO clause, INTO is not allowed inside EXECUTE IMMEDIATE query string
1
2

扫码关注微信公众号

greatsql-wx