the SQL that establishes the stored procedure is as follows:
CREATE PROCEDURE proc_variable () BEGIN DECLARE dec_var_ VARCHAR (+); DECLARE rep_num INT; DECLARE Dec_var CURSOR for SELECT id_ from act_ru_variable WHERE name_ = ' yjdqsj ' GROUP by Execution_id_ have Coun T (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;
If the above statement is executed directly, it encounters the first '; ' It's over, and then all the rest will get an error. The reason is that the default MySQL command terminator is a semicolon and requires a modified terminator to execute.
MySQL ([email protected]: Jjsworkflow) >delimiter//#修改命令终止符为//(random) MySQL ([email protected]: jjsworkflow) >create PROCEDURE proc_variable () BEGIN DECLARE dec_var_ VARCHAR (+); - DECLARE rep_num INT; - DECLARE Dec_var CURSOR for SELECT id_ from act_ru_variable WHERE name_ = ' yjdqsj ' GROUP by Execution_id_ have 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; / /# #语句最后需要已上面声明的//End Enter to be executed
that is, the contents of//'//are encapsulated together to execute. This modification is session level and expires after exiting
Create a stored procedure under Mysql command line