关于MYSQL存储过程 求帮助,数据库报错

"

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

这里是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

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