mysql：在存储过程中传递sql语句作为参数来执行它

tjvv9vkg 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(542)

请帮我查询一下：我在下面提到了oracle中的存储过程，我需要把它转换成mysql

CREATE OR REPLACE procedure EAMP.EXEC_DDL (SQLSTMT VARCHAR2) AUTHID DEFINER
is
BEGIN
EXECUTE IMMEDIATE SQLSTMT
END EXEC_DDL;
/

这是我在mysql（5.7版）中创建的：

DROP PROCEDURE IF EXISTS EXEC_DDL;
CREATE DEFINER=`ampd_own`@`%`
PROCEDURE `ampd01`.`EXEC_DDL`(in SQLSTMT VARCHAR(4000))
BEGIN
set @sql = SQLSTMT;
PREPARE STMT FROM @sql;
EXECUTE STMT;
deallocate prepare STMT;
END;

但它给了我以下提到的错误：

20:08:43  START Executing, Database Connection: EAMP DEV MYSQL Database Type: MYSQL Catalog: ampd01 Schema: null
20:08:47  INFO  Physical database connection acquired for: EAMP DEV MYSQL  
20:08:47  WARNING  [DROP - 0 rows, 0.994 secs]  OK. No rows were affected
SQLWarning: 
Code: 1305 SQL State: 42000 --- PROCEDURE ampd01.EXEC_DDL does not exist  
DROP PROCEDURE IF EXISTS EXEC_DDL;
20:08:48  FAILED  [CREATE - 0 rows, 1.245 secs]  [Code: 1064, SQL State: 
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 4  
CREATE DEFINER=`ampd_own`@`%`
PROCEDURE `ampd01`.`EXEC_DDL`(in SQLSTMT VARCHAR(4000))
BEGIN
set @sql = SQLSTMT;
20:08:49  FAILED  [PREPARE - 0 rows, 1.243 secs]  [Code: 1064, SQL State: 
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 
'NULL' at line 1  
PREPARE STMT FROM @sql;
20:08:51  FAILED  [EXECUTE - 0 rows, 1.253 secs]  [Code: 1243, SQL State: 
HY000]  Unknown prepared statement handler (STMT) given to EXECUTE  
EXECUTE STMT;
20:08:52  FAILED  [DEALLOCATE - 0 rows, 1.255 secs]  [Code: 1243, SQL State: 
HY000]  Unknown prepared statement handler (STMT) given to DEALLOCATE 
PREPARE  
deallocate prepare STMT;
20:08:53  FAILED  [END - 0 rows, 1.253 secs]  [Code: 1064, SQL State: 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 'END' at line 1  
END;
20:08:55  END Execution 6 statement(s) executed, 0 row(s) affected, 
exec/fetch time: 7.243/0.000 secs   [1 successful, 1 Warnings, 5 errors]*/

mysql mysql-error-1064 oracle12c

来源：https://stackoverflow.com/questions/50820006/mysql-passing-sql-statement-as-parameter-in-stored-procedure-to-execute-it

1条答案

按热度按时间

r6vfmomb1#

如果过程主体中有分号，则需要更改会话的语句分隔符。
例如：

DELIMITER $$

CREATE PROCEDURE foo() 
BEGIN
  SELECT NOW();
END$$

DELIMITER ;

这样，存储过程中出现的分号就不会结束 CREATE 声明。完成后，我们可以将语句分隔符改回分号。
编辑
以下是对报告输出的分析：
本声明

DROP PROCEDURE IF EXISTS EXEC_DDL;

成功完成，引发警告“过程不存在”
执行的下一个语句是：

CREATE DEFINER=`ampd_own`@`%`
PROCEDURE `ampd01`.`EXEC_DDL`(in SQLSTMT VARCHAR(4000))
BEGIN
set @sql = SQLSTMT;

mysql返回语法错误；这是预期的行为，因为这不是有效的sql语句。执行的下一条语句：

PREPARE STMT FROM @sql;

返回错误，因为 @sql 计算结果为 NULL . 执行的下一条语句

EXECUTE STMT;

失败，因为stmt不是有效的语句句柄(因为准备失败。）
我们不想执行 SET 以及 PREPARE 作为单独的声明，我们希望它们成为 CREATE PROCEDURE 声明。
问题是分号字符是语句终止符；我们需要将语句终止符改为其他内容，改为不出现在语句体中的字符串 CREATE PROCEDURE .
后续行动
参考文献：https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html
下面是一个使用mysql命令行客户机的演示


# mysql --defaults-extra-file=/opt/mysql/scripts/config.cnf --database test

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS foo $$
Query OK, 0 rows affected (0.06 sec)

mysql> DROP PROCEDURE IF EXISTS foo $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE foo()
    -> BEGIN
    ->   SELECT NOW();
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL foo();
+---------------------+
| NOW()               |
+---------------------+
| 2018-06-13 13:33:33 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> exit

赞(0）回复(0）举报 2021-06-21

我来回答

mysql：在存储过程中传递sql语句作为参数来执行它

1条答案

相关问题

热门标签

最新问答