E3 基于Mysql的SQL应用和存储过程

E3 基于Mysql的SQL应用和存储过程

• 完成对程序的控制与管理，并根据要求写存储过程。

• Group1：Mysql基本代码

  • 问题1：根据输入变量deptcode，编写代码块来获得雇员人数。

    use example; set @deptcode =1; set @personnum=0; select count(*) into
    @personnum from emp where deptno=@deptcode; select @personnum;

  • 问题2：编写一个包含部分定义、执行的代码块:根据员工的empno，输出员工姓名、工资和部门名称。

    use example; SET @empcode =1; set @empname=’’; set @empsal=0; set
    @deptname=’’; select ename,sal,Dname INTO @empname,@empsal, @deptname
    from emp,dept WHERE empno=@empcode and emp.deptno=dept.deptno; select
    @empname,@empsal,@deptname;

  • 问题3：编写一个代码块,根据员工编号的变量(empno),得到他的年收入和并输出。

    use example; SET @empcode =3; SET @empsal=0; SELECT sal INTO @empsal
    from emp WHERE empno=@empcode; SELECT 12*@empsal annual_sal;

  • 问题4：编写一个包含定义、执行的代码块，实现以下功能:根据部门编号变量的值输出本部门所有员工的平均工资。

    use EXAMPLE; SET @dno=1; SET @deptavgsal=0; SET @deptname=""; SELECT
    dname,avg(sal) INTO @deptname,@deptavgsal FROM emp,dept WHERE
    dept.deptno=emp.deptno and deptno=@dno; SELECT @deptname, @deptavgsal;

• Group 2：存储过程的编写和应用

  Definite stored procedure. DELIMITER // create procedure
  update_salary(in empno varchar(30)) begin end; // DELIMITER

  • 问题1：请写一个存储过程：根据输入的参数员工编号显示该员工的姓名和工资。

    DELIMITER // CREATE PROCEDURE select_person(IN empcode
    VARCHAR(30)) BEGIN declare empsalary float; declare empname
    varchar(30); select ename,sal into empname , empsalary from
    example.emp where empno= empcode; select empname, empsalary; END //
    DELIMITER ;

    set @empcode=‘112211002’; call select_price (@empcode);

  • 问题2：编写一个存储过程完成以下功能:根据输入参数部门编号，得到该部门所有员工的平均工资。

    CREATE PROCEDURE avgsal( IN deptcode INT, OUT empsal_avg float, )
    BEGIN declare empname varchar(20); declare empsal_avg int; select
    avg(sal) into empsal_avg from emp where deptno=deptcode; select
    empsal_avg; END

    set @empsal_avg=0; call avgsal(4, @empsal_avg); select @empsal_avg;

  • 问题3：编写一个存储过程，根据输入的参数员工编号更改员工的工资:如果员工的工资大于100，则在原值的基础上增加100;如果工资为0或null，则将其设置为200。

    CREATE PROCEDURE sp_update_sal(IN empcode INT)//创建存储过程 BEGIN
    DECLARE salary DOUBLE;//查询员工工资，根据工资水平做相应的更新操作 SELECT sal INTO salary
    FROM emp WHERE empno=empcode; if salary>100 then UPDATE EXAMPLE.emp
    SET sal=sal+100 WHERE empno=empcode; end if; IF salary=0 or salary is
    null then UPDATE EXAMPLE.emp SET sal=200 WHERE empno=empcode; END if;
    END

    SET @empcode=2; CALL sp_update_sal(@empcode);

  • 问题 4：编写一个带有输入参数、输出参数的存储过程，根据输入mgr编号得到该领导所带的职工人数。

    DELIMITER // CREATE PROCEDURE sp_get_persons(IN leader INT, OUT
    persons INT) BEGIN SELECT count(*) INTO persons FROM emp WHERE
    mgr=leader; END // DELIMITER ;

    SET @mgrcode=1; SET @personNum; CALL
    sp_get_persons(@mgrcode,@personNum); Select @personNum

  • 问题5：编写一个存储过程，根据输入的部门编号更改该部门所有员工的工资:如果员工的工资大于1000，则在原值的基础上增加10%;如果工资大于1500，则提高8%。(需要在E5中使用光标)

    CREATE PROCEDUREsp_select_sal(INdeptcode INT) //创建存储过程 BEGIN
    DECLARE salary DOUBLE; //定义变量 declare done int default 0; declare
    cur_sal cursor for select sal from emp where deptno=deptcode;
    //定义游标，将游标指向查询到的第一条记录 declare continue handler for not found set
    done=1; open cur_sal; repeat fetch cur_sal into salary; if not done
    then SELECT sal INTO salary FROM emp WHERE empno=empcode;//查询某员工工资 end
    if; if salary>1000 and salary<=1500 then//按要求根据工资水平更新工资 UPDATE
    EXAMPLE.emp SET sal=sal1.1 WHERE empno=empcode; end if; IF
    salary>1500 then UPDATE EXAMPLE.emp SET sal=sal1.08 WHERE
    empno=empcode; END if; until done end repeat; close cur_sal; END

    SET @deptcode=2; CALL sp_select_sal(@deptcode);