mysql 存储过程 sqlstate_mysql 存储过程 sqlstate

"

这是典型的游标应用，给你个例子

这里是mysql 存储过程的官方文档，请参考http://www.ostools.net/apidocs/apidoc?api=mysql-5.1-zh

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE _emp_id int;

DECLARE _emp_name varchar(30);

declare _travel_days int default 0; -- 出差天数

declare _add_hour decimal(7,2) default 0.0; -- 加班工时

declare _travle_cost decimal(10,2) default 0.0; -- 差旅成本

-- 根据deptid查询部门所有员工

DECLARE cursor_emps CURSOR FOR select emp_id,emp_name from emp where dept_id = deptid and sts = 'A' and srv_dept_id = 30;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

-- 创建临时表存储结果集

create TEMPORARY table tmp_return_lst(

empname varchar(30) not null

,traveldays int not null

,addhour decimal(7,2) not null

,travlecost decimal(10,2) not null

);

-- 清除表数据

truncate table tmp_return_lst;

OPEN cursor_emps;

REPEAT

FETCH cursor_emps INTO _emp_id, _emp_name;

IF NOT done THEN

-- 查询每名员工的出差天数

select f_travel_day(_emp_id,sd , ed) into _travel_days;

-- 查询每名员工的实际加班工时

select f_actual_add_hour(_emp_id,sd , ed) into _add_hour;

-- 获得每名员工的出差报销金额

select sum(t.tm) into _travle_cost from (

select

distinct b.est_id

,round(b.total_price/b.empcount,2) as tm

from expenseaccount_check_repeat a, expenseaccount_travel b

where a.emp_id = _emp_id and a.check_date between sd and ed

and a.est_id = b.est_id) as t;

-- 将整理的数据insert临时表

insert into tmp_return_lst values(_emp_name,ifnull(_travel_days,0),ifnull(_add_hour,0.0),ifnull(_travle_cost,0.0));

END IF;

UNTIL done END REPEAT;

CLOSE cursor_emps;

-- 返回结果

select * from tmp_return_lst;

END######

引用来自“恺哥”的答案

这是典型的游标应用，给你个例子

这里是mysql 存储过程的官方文档，请参考http://www.ostools.net/apidocs/apidoc?api=mysql-5.1-zh

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE _emp_id int;

DECLARE _emp_name varchar(30);

declare _travel_days int default 0; -- 出差天数

declare _add_hour decimal(7,2) default 0.0; -- 加班工时

declare _travle_cost decimal(10,2) default 0.0; -- 差旅成本

-- 根据deptid查询部门所有员工

DECLARE cursor_emps CURSOR FOR select emp_id,emp_name from emp where dept_id = deptid and sts = 'A' and srv_dept_id = 30;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

-- 创建临时表存储结果集

create TEMPORARY table tmp_return_lst(

empname varchar(30) not null

,traveldays int not null

,addhour decimal(7,2) not null

,travlecost decimal(10,2) not null

);

-- 清除表数据

truncate table tmp_return_lst;

OPEN cursor_emps;

REPEAT

FETCH cursor_emps INTO _emp_id, _emp_name;

IF NOT done THEN

-- 查询每名员工的出差天数

select f_travel_day(_emp_id,sd , ed) into _travel_days;

-- 查询每名员工的实际加班工时

select f_actual_add_hour(_emp_id,sd , ed) into _add_hour;

-- 获得每名员工的出差报销金额

select sum(t.tm) into _travle_cost from (

select

distinct b.est_id

,round(b.total_price/b.empcount,2) as tm

from expenseaccount_check_repeat a, expenseaccount_travel b

where a.emp_id = _emp_id and a.check_date between sd and ed

and a.est_id = b.est_id) as t;

-- 将整理的数据insert临时表

insert into tmp_return_lst values(_emp_name,ifnull(_travel_days,0),ifnull(_add_hour,0.0),ifnull(_travle_cost,0.0));

END IF;

UNTIL done END REPEAT;

CLOSE cursor_emps;

-- 返回结果

select * from tmp_return_lst;

END

感谢。

"