MYSQL存储过程（二）

类比jdbc的ResultSet
-- 声明语法DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法OPEN cursor_name
-- 取值语法FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法CLOSE cursor_name
-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))begin  declare e_no int;  declare e_name varchar(32);  declare e_sal decimal(7,2);  declare lp_flag boolean default true;  declare emp_cursor cursor for 
select e.empno,e.ename,e.salfrom emp e,dept d
where e.deptno= d.deptnoand d.dname= dept_name;-- handler 句柄  declare continue handler for NOT FOUND set lp_flag =false;  open emp_cursor;  emp_loop:loop
    fetch emp_cursor into e_no,e_name,e_sal;    if lp_flag then
select e_no,e_name,e_sal;    else
      leave emp_loop;    end if;  end loop emp_loop;set @end_falg ='exit_flag';  close emp_cursor;end$$
call sp_create_table02('RESEARCH');特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
handler_action:{    CONTINUE
| EXIT
| UNDO
}condition_value:{    mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
|NOT FOUND
| SQLEXCEPTION
}CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This istrue even if the condition occurs in an inner block.
SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do notbegin with '00','01',or'02'.
-- 各种写法：  DECLARE exit HANDLER FOR SQLSTATE '42S01'set @res_table ='EXISTS';  DECLARE continue HANDLER FOR 1050set @res_table ='EXISTS';  DECLARE continue HANDLER FOR not found set @res_table ='EXISTS';