E4 基于Mysql的游标定义和应用

E4 基于Mysql的游标定义和应用

• 使用MySQL游标的定义和应用。

• 问题1：请写一个程序，根据输入的部门编号，得到部门所有员工的姓名和工资。(用游标实现)
  drop procedure if exists select_emps;

  DELIMITER // create procedure select_emps(in dcode varchar(30)) begin
  DECLARE empname varchar(30); DECLARE empsalary float; DECLARE done
  INT DEFAULT 0; DECLARE cur_emp cursor for select ename, sal from emp
  where deptno= dcode; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done
  = 1; OPEN cur_emp; REPEAT FETCH cur_emp INTO empname, empsalary; IF NOT done THEN Select empname, empsalary; END IF; UNTIL done END
  REPEAT; CLOSE cur_emp; end;/ DELIMITER call select_emps(2);

• 问题2：请写一个程序，根据输入的job，得到该岗位的所有员工姓名和工资。(用游标)

  drop procedure if exists select_emps; DELIMITER // create procedure
  select_emps(in dcode varchar(30)) begin DECLARE empname varchar(30);
  DECLARE empsalary float; DECLARE done INT DEFAULT 0; DECLARE cur_emp
  cursor for select ename, sal from emp where deptno= dcode; DECLARE
  CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur_emp; REPEAT
  FETCH cur_emp INTO empname, empsalary; IF NOT done THEN Select
  empname, empsalary; END IF; UNTIL done END REPEAT; CLOSE cur_emp; end;
  / DELIMITER call select_emps(2);

• 问题3：请写一个程序:根据输入的部门编号，得到这个部门所有员工的平均工资。(用游标)

  CREATE PROCEDURE sp_select_avgjob( IN dcode INT，OUT totalsalary
  DOUBLE) //创建存储过程 BEGIN DECLARE salary DOUBLE; Decalare declare done
  int default 0; //定义变量 declare cur_sal cursor for select avg(sal) from
  emp where deptno=dcode; //定义游标，将游标指向查询到的第一条记录 declare continue handler
  for not found set done=1; open cur_sal; repeat fetch cur_sal into
  salary; if not done then totalsalary= totalsalary +salary; end if;
  until done end repeat;//当查询不到记录时，关闭游标 close cur_sal; END

  Set @tsal=0; CALL sp_select_avgjob(1, @tsal); Select @tsal; Delimiter
  // create procedure calavgsall(in dno int) begin declare num int
  default 0; declare sumsal double default 0.0; declare done int default
  0; declare empsal double; declare cur_emp cursor for select sal from
  emp where deptno=dno; declare continue handler for not found set done
  =1; open cur_emp; repeat fetch cur_emp into empsal; if not done then sumsal=sumsal+empsal; num=num+1; end if; until done end repeat;
  select sumsal/num as avgsal ; close cur_emp; end; //

• 问题4：请写一个程序:更改所有员工的津贴:如果员工的原津贴小于100;则改为200;如果空值则赋100元。(用游标)

  CREATE DEFINER=root@localhost PROCEDURE sp_update_sal()//创建存储过程
  BEGIN declare v_name varchar(30);//定义变量 DECLARE v_comm DOUBLE; declare
  done int default 0; declare cur_sal cursor for select ename,comm from
  emp; declare continue handler for not found set done=1; open cur_sal;
  repeat fetch cur_sal into v_name,v_comm; if not done then if v_comm>0
  and v_comm<100 then//判断comm的值，做不同的update操作 UPDATE EXAMPLE.emp SET
  comm=200; end if; IF v_comm is null then UPDATE EXAMPLE.emp SET
  sal=100; END if; SELECT v_name,v_comm; end if; until done end repeat;
  close cur_sal; END