Oracle作业

第四次实验

【实验内容】

以scott用户登录到数据库默认实例，完成以下工作：

1、查询名为“SMITH”的员工信息，输出其员工号、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10.

2、创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300.

3、创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

4、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。

5、在dept表上创建触发器，使得当DEPT表的deptno（主键）发生变化时，EMP表的相关行也要跟着进行适当的修改。

6、创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。

7、创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。

注意：scott用户下已有表emp（员工信息表）和dept（部门表），这两个表的主要字段如下：

emp表：empno（员工编号）、ename（员工姓名）、sal（工资）、deptno（员工所在部门号）……

dept表：deptno（部门编号）、dname（部门名称）、loc（部门位置）

  如有需要，可以用desc命令查看关系表的结构。

答案

代码亲自测试：

--1、查询名为“SMITH”的员工信息，输出其员工号、工资、部门号。
--如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10.
DECLARE 
  v_ename scott.emp.ename%TYPE; 
  v_empno scott.emp.empno%TYPE;     --定义变量：员工号
  v_sal   scott.emp.sal%TYPE;       --定义变量：薪水
  v_deptno scott.emp.deptno%TYPE;   --定义部门号    int a; 
BEGIN   
  SELECT ename,empno,sal,deptno
         INTO v_ename,v_empno,v_sal,v_deptno
         FROM scott.emp e 
         WHERE e.ename = 'SMITH';    --根据条件查数据放进变量里   
  dbms_output.put_line(v_ename);
--该员工不存在 咋判断？  
  dbms_output.put_line(v_empno ||' ' || v_sal || ' ' ||v_deptno);        
EXCEPTION
  WHEN no_data_found THEN 
    INSERT INTO scott.emp VALUES(2007,'SMITH',NULL,NULL,SYSDATE,1500,NULL,10);--没有数据则插入
    COMMIT;
END;  
  


/*2、创建一个存储过程，以员工号为参数，修改该员工的工资。
若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；
若属于其他部门，则工资增加300.*/

CREATE OR REPLACE PROCEDURE CHANGE_SAL(P_EMPNO IN SCOTT.EMP.EMPNO%TYPE) IS
  V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
BEGIN
  SELECT DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE EMPNO = P_EMPNO;
  CASE V_DEPTNO
    WHEN 10 THEN
      UPDATE SCOTT.EMP EE
         SET EE.SAL = EE.SAL + 150
       WHERE EE.EMPNO = P_EMPNO;
    WHEN 20 THEN
      UPDATE SCOTT.EMP EE
         SET EE.SAL = EE.SAL + 200
       WHERE EE.EMPNO = P_EMPNO;
    WHEN 30 THEN
      UPDATE SCOTT.EMP EE
         SET EE.SAL = EE.SAL + 250
       WHERE EE.EMPNO = P_EMPNO;
    ELSE
      DBMS_OUTPUT.PUT_LINE('error;');
  END CASE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('empno is no data;');
END CHANGE_SAL;

BEGIN 
  change_sal(2007);
END;

--3、创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。测试员工号：7934
  
create or replace function GetAvgSal(p_empno in scott.emp.empno%TYPE) return scott.emp.sal%type is
  v_getAvgSal scott.emp.sal%type;
begin
      --以员工号为参数，返回该员工所在部门的平均工资
      SELECT AVG(e.sal)
             INTO  v_getAvgSal   --得到形参所传员工编号所在部门所有员工的平均工资               
             FROM scott.emp e
             WHERE e.deptno IN (
                   SELECT deptno
                          FROM scott.emp
                          WHERE empno = p_empno
             );
  return(v_getAvgSal);
EXCEPTION 
  WHEN no_data_found THEN 
    dbms_output.put_line('The empno is not in Oracle!');  
end GetAvgSal;
--调用
DECLARE 
    v_avgSal scott.emp.sal%TYPE;
    v_empno  scott.emp.empno%TYPE;
BEGIN
   v_empno := &NO;
   v_avgSal := GetAvgSal(v_empno);
   dbms_output.put_line(v_avgSal);
END;
SELECT AVG(sal) FROM scott.emp WHERE deptno = '10'; --与测试结果一致
--4、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。
create or replace trigger GetNumAndAvgSal
  before insert or update or delete
  on scott.emp 
  for each row
begin
  IF inserting OR updating OR deleting THEN 
     FOR v_emp IN (SELECT e.deptno   deptno,
                       COUNT(1)   deptSum,
                       AVG(e.sal) avgSal 
                       FROM scott.emp e 
                       GROUP BY e.deptno) LOOP
 dbms_output.put_line(v_emp.deptno || ' ' || v_emp.deptsum  || ' ' || TRUNC(v_emp.avgsal));      
 END LOOP;
    --raise_application_error(-20001,'操作错误！');
  END IF;  
end GetNumAndAvgSal;

--5、在dept表上创建触发器，使得当DEPT表的deptno（主键）发生变化时，EMP表的相关行也要跟着进行适当的修改。
create or replace trigger update_deptno
  after update
  on dept 
  for each row
--行级触发器
BEGIN      
  UPDATE scott.emp e
  SET e.deptno = :NEW.DEPTNO
  WHERE e.deptno = :old.Deptno;
end update_deptno;
--6、创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。
-- Created on 2020/10/28 by JACKYANG 

CREATE OR REPLACE PROCEDURE GETEMPINFO(NUM1 IN NUMBER,
                                       NUM2 IN NUMBER) IS
BEGIN
  BEGIN
    FOR V_SAL IN (SELECT * FROM SCOTT.EMP E) LOOP
      IF V_SAL.SAL > NUM1 AND V_SAL.SAL < NUM2 THEN
        DBMS_OUTPUT.PUT_LINE(V_SAL.EMPNO || ' ' || V_SAL.ENAME || ' ' ||
                             V_SAL.SAL);
      END IF;
    END LOOP;
  
  END;
END GETEMPINFO;

begin
  scott.getempinfo(9000,10000);  
end;
/*7、创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，
返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。*/

CREATE OR REPLACE PACKAGE PKG_GETMAXSALRETURNEMPNO IS
   FUNCTION getMaxSal(f_deptno scott.emp.deptno%TYPE) return scott.emp.sal%TYPE;
   PROCEDURE getMaxSalEmpnoEmpname(p_deptno scott.emp.deptno%TYPE);
END PKG_GETMAXSALRETURNEMPNO;

create or replace package body PKG_GETMAXSALRETURNEMPNO is

  -- Function and procedure implementations
  function getMaxSal(f_deptno scott.emp.deptno%TYPE) return scott.emp.sal%TYPE
    AS v_maxSal scott.emp.sal%TYPE;  --局部变量
  begin
    SELECT MAX(e.sal)
           INTO v_maxSal
           FROM scott.emp e
           WHERE e.deptno = f_deptno;
    RETURN v_maxSal;
    dbms_output.put_line(v_maxSal);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('The empno is not in Oracle!');
  end getMaxSal;

--过程实现
PROCEDURE getMaxSalEmpnoEmpname(p_deptno scott.emp.deptno%TYPE)
AS
  v_empno   scott.emp.empno%TYPE;
  v_empname scott.emp.ename%TYPE;
  v_sal     scott.emp.sal%TYPE;        
BEGIN
   SELECT empno,ename,sal
    INTO v_empno ,v_empname,v_sal 
    FROM SCOTT.EMP
   WHERE SAL = ((SELECT MAX(SAL) FROM SCOTT.EMP E WHERE E.DEPTNO = p_deptno));  
   dbms_output.put_line(v_empno || ' ' || v_empname || ' ' || v_sal );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('error!');
END getMaxSalEmpnoEmpname;

end PKG_GETMAXSALRETURNEMPNO;

--调用过程
BEGIN
  DBMS_OUTPUT.PUT_LINE(SCOTT.PKG_GETMAXSALRETURNEMPNO.GETMAXSAL(10));
  SCOTT.PKG_GETMAXSALRETURNEMPNO.GETMAXSALEMPNOEMPNAME(10);
END;