MYSQL 存储过程疑问

[kangnan008]

各位大大：
脚本如下：
SET @isqlScript = concat('drop procedure if exists sp3_',tblname,'_i;');
select @isqlScript;
prepare stmt FROM @isqlScript;
select stmt;
EXECUTE stmt using @isqlScript;
DEALLOCATE prepare stmt1;

实现的目标：
我一个SP里面需要动态拼接脚本，然后执行DROP语句
了解下来 prepare 是不支持DDL语句的
This command is not supported in the prepared statement protocol yet

请问有什么破解办法吗？

[wwwwb]

MYSQL不支持，什么版本？

[kangnan008]

引用 1 楼 wwwwb 的回复:

MYSQL不支持，什么版本？

5.6.21 我是一个SP根据动态传进来的参数，然后去动态CREATE一些SP。 好像在SP里面实现不了

[知道就是你]

可动态创建啊,存储过程不支持动态游标查询 ，

CREATE  
PROCEDURE ps_create_table_more(IN i INT)
BEGIN
  DECLARE k INT;

  SET k = 1;
  WHILE k < i
  DO
    SET @t = k;
    SET @tname = concat('sp3_', @t, 'k');
    SET @dwhe = '(id int,name varchar(255));';
    SET @sql_text := concat('create table ', @tname, @dwhe);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    SET k = k + 1;
  END WHILE;
END

[kangnan008]

引用 3 楼 JenMinZhang 的回复:

可动态创建啊,存储过程不支持动态游标查询 ，

CREATE  
PROCEDURE ps_create_table_more(IN i INT)
BEGIN
  DECLARE k INT;

  SET k = 1;
  WHILE k < i
  DO
    SET @t = k;
    SET @tname = concat('sp3_', @t, 'k');
    SET @dwhe = '(id int,name varchar(255));';
    SET @sql_text := concat('create table ', @tname, @dwhe);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    SET k = k + 1;
  END WHILE;
END

TABLE是可以的。我指的是drop procedure和create procedure不行

[ACMAIN_CHM]

The following SQL statements can be used as prepared statements: ALTER TABLE ANALYZE TABLE CACHE INDEX CALL CHANGE MASTER CHECKSUM {TABLE | TABLES} COMMIT {CREATE | DROP} DATABASE {CREATE | RENAME | DROP} USER CREATE INDEX CREATE TABLE DELETE DO DROP INDEX DROP TABLE FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES} GRANT INSERT INSTALL PLUGIN KILL LOAD INDEX INTO CACHE OPTIMIZE TABLE RENAME TABLE REPAIR TABLE REPLACE RESET {MASTER | SLAVE | QUERY CACHE} REVOKE SELECT SET SHOW BINLOG EVENTS SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW} SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS} SHOW {MASTER | BINARY} LOGS SHOW {MASTER | SLAVE} STATUS SLAVE {START | STOP} UNINSTALL PLUGIN UPDATE Other statements are not yet supported. .

[WWWWA]

MYSQL不支持，换数据库吧

[知道就是你]

引用 4 楼 kangnan008 的回复:

[quote=引用 3 楼 JenMinZhang 的回复:] 可动态创建啊,存储过程不支持动态游标查询 ，

CREATE  
PROCEDURE ps_create_table_more(IN i INT)
BEGIN
  DECLARE k INT;

  SET k = 1;
  WHILE k < i
  DO
    SET @t = k;
    SET @tname = concat('sp3_', @t, 'k');
    SET @dwhe = '(id int,name varchar(255));';
    SET @sql_text := concat('create table ', @tname, @dwhe);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    SET k = k + 1;
  END WHILE;
END

TABLE是可以的。我指的是drop procedure和create procedure不行[/quote] Sorry 的确不支持 ........