mysql 存储过程创建临时表 mysql创建存储过程命令

建立存储过程的sql如下：

CREATE PROCEDURE  proc_variable ()


BEGIN


DECLARE dec_var_ VARCHAR(100);


DECLARE rep_num INT;


DECLARE dec_var CURSOR  for SELECT ID_  FROM ACT_RU_VARIABLE WHERE NAME_ = 'yjdqsj' GROUP BY EXECUTION_ID_ HAVING COUNT(NAME_) >1;


OPEN dec_var;


REPEAT


FETCH dec_var INTO dec_var_;


DELETE FROM ACT_RU_VARIABLE WHERE ID_ = dec_var_;


UNTIL rep_num END REPEAT;


CLOSE dec_var;


end;

如果把上面的语句直接执行的话，遇到第一个‘;'就结束了，然后剩下的都会报错。原因是默认mysql命令结束符是分号，需要修改结束符才可以执行。

mysql(root@localhost:jjsworkflow)>delimiter //    #修改命令终止符为//（random）
mysql(root@localhost:jjsworkflow)>CREATE PROCEDURE proc_variable ()
-> BEGIN
-> DECLARE dec_var_ VARCHAR(100);
-> DECLARE rep_num INT;
-> DECLARE dec_var CURSOR for SELECT ID_ FROM ACT_RU_VARIABLE WHERE NAME_ = 'yjdqsj' GROUP BY EXECUTION_ID_ HAVING COUNT(NAME_) >1;
-> OPEN dec_var;
-> REPEAT
-> FETCH dec_var INTO dec_var_;
-> DELETE FROM ACT_RU_VARIABLE WHERE ID_ = dec_var_;
-> UNTIL rep_num END REPEAT;
-> CLOSE dec_var;
-> end;
-> //    ##语句最后需要已上面声明的//结尾enter才能被执行

即把//’''//之间的内容封装到一起执行。此修改是session级别，退出后即失效