mysql8 create procedure

头像
码农笔录
2021-10-15 运维 阅读量 145

The query ID is stored as a parameter in other tables.

Note: Key words can not be less, the following code can be copied directly, the following code is my case

delimiter //
drop procedure if exists nelsonTest;
CREATE PROCEDURE nelsonTest()
begin
    declare temp_id varchar(36);
    declare flag int default 0;
    declare s_list cursor for Query your data;
    # Create an exit flag for the following while loop, and set the value of flag to 1 when the cursor is traversed.
    declare continue handler for not found set flag=1;
    open s_list;  # Open cursor
    # Assign the value in the cursor to the defined variable to realize the main points of the for loop.
        fetch s_list into temp_id;
        while flag <> 1 do

	  Write your business code here

          fetch s_list into temp_id;
        end while;
    close s_list;  # Cloecursor
end
//
delimiter ;  # Redefine; it is the end mark of a sentence of sql

call nelsonTest(); # call PROCEDURE

Case study

delimiter //
drop procedure if exists nelsonTest;
CREATE PROCEDURE nelsonTest()
begin
    declare temp_id varchar(36);
    declare flag int default 0;
    declare s_list cursor for SELECT id FROM baseline_auth.user_account ;
    # Create an exit flag for the following while loop, and set the value of flag to 1 when the cursor is traversed.
    declare continue handler for not found set flag=1;
    open s_list;  # Open cursor
    # Assign the value in the cursor to the defined variable to realize the main points of the for loop.
        fetch s_list into temp_id;
        while flag <> 1 do

	  INSERT INTO app_user`(app_id,user_id,user_source) VALUES ('2c953719-7bce7dc0-017b-ce827d26', temp_id, 3);

          fetch s_list into temp_id;
        end while;
    close s_list;  # Cloecursor
end
//
delimiter ;  # Redefine; it is the end mark of a sentence of sql

call nelsonTest(); # call PROCEDURE