MySQL存储过程中使用循环处理数据

存储过程就像一份写好的由多条SQL组合的代码，这份SQL就像函数一样，可以接受传入参数，可以返回结果。传入参数使用in，传出参数使用out，参数名不要使用关键字！

存储过程传入参数

create procedure maxprice(IN _title varchar(20), OUT maxPrice int)
begin
    select max(bill.repertory*bill.price) into maxPrice
    from bill where title = _title;
end;

存储过程传出参数

create procedure maxprice(OUT maxPrice int)
begin
    select max(bill.repertory*bill.price) into maxPrice
    from bill;
end;

调用上述存储过程

call maxprice(@maxPrice);
select @maxPrice;

存储过程中使用游标，处理数据

重点
declare my_cursor cursor for申明游标
open my_cursor ;close my_cursor 打开游标，关闭游标
repeat; until done end repeat;循环，直到done为1停止
declare done boolean default 0; 申明监视sqlstate变量
declare continue handler for sqlstate '02000' set done = 1;done是一个监视sqlstate的变量

create
    definer = root@localhost procedure prls()
begin
    declare done boolean default 0;
    declare _name varchar(20);
    declare _time datetime;
    declare _password varchar(20);

    declare ordernum cursor for
    select user_name, create_time, user_password from user_copy;
    -- 根据sqlstate设置继续与否
    declare continue handler for sqlstate '02000' set done = 1;
    open ordernum;
    repeat
    fetch ordernum into _name,_time,_password;
    insert into user(user_name, create_time, user_password) value (_name,_time,_password);
    until done end repeat;
    close ordernum;
end;