Mysql 命令行下建立存储过程

时间：2022-03-13 23:45

建立存储过程的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(:jjsworkflow)>delimiter // #修改命令终止符为//（random） mysql(: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级别，退出后即失效