MySQL存储过程简单记录

 游标嵌套的例子

drop procedure if exists p_test;
create procedure p_test(in id int, out name VARCHAR(100))
BEGIN

  declare v_uid int;
  declare v_uname VARCHAR(100);
  declare v_done int default 0; 
  declare cur_t_user cursor for (select uid,uname from t_user); 
  declare continue handler for not found set v_done = 1; 
  
  open cur_t_user; 
    loop_user:loop 
    fetch cur_t_user into v_uid,v_uname; 
      if v_done = 1 then 
        leave loop_user;
      end if;
    select CONCAT(v_uid,'#',v_uname);

      BEGIN
        declare v_done_inner int default 0;
        declare v_ret varchar(200) default '***';
        declare v_uid_inner int;
        declare cur_user_inner cursor for (select uid from t_user);
        declare continue handler for not found set v_done_inner = 1;
        
        open cur_user_inner;
          loop_user_inner:LOOP
            fetch cur_user_inner into v_uid_inner;
             if v_done_inner = 1 then
							 leave loop_user_inner;
						 end if;
             set v_ret = CONCAT(v_ret,'#',v_uname,'#',v_uid_inner);
          END LOOP;
        close cur_user_inner;
        select v_ret;
      END;
    end loop;
  close cur_t_user;
END;

5. 在命令行中使用大段SQL

mysql> DELIMITER //
drop procedure if exists p_test;
create procedure p_test(in id int, out name VARCHAR(100))
BEGIN
  select id;
  select uname into name from t_user where uid = id;
end;//
DELIMITER;
mysql> 

使用 DELIMITER // .. // DELIMITER ;  作为标记，就可以命令行模式下使用大段SQL 不至于被当做单条SQL执行。

有oracle基础的可以参考下 http://www.cnblogs.com/HondaHsu/p/3641258.html

MySQL存储过程简单记录

标签：