《Oracle技术》实验报告
成绩
实验名称
PL/SQL编程（选作10题，程序另加附页）
姓名
学号
日期
专业
班级
实验
目的
（1）了解PL/SQL块的基本结构和分类。
（2）熟练掌握编写PL/SQL块的方法。
（3）了解PL/SQL存储过程、触发器、游标的作用。
（4）熟练掌握编写PL/SQL存储过工程的方法及维护过程。
（5）熟练掌握创建触发器的方法及维护过程。
（6）孰练掌握显式游标和隐式游标的使用。
实
验
内
容
（1） 编写一个PL/SQL块，输出所有员工的员工名、员工号、工资和部门号。
BEGIN
        FOR  v_emp  IN  (SELECT  *  FROM  EMP)  LOOP
            DBMS_OUTPUT.PUT_LINE(ame||’  ‘|| pno|| ’  ‘||v_emp.sal||’  ‘|| v_emp.deptno);
        END  LOOP;
      END;
      /
（2） 编写一个PL/SQL块，查询名为“SMITH”的员工信息，并输出其员工号、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10。如果存在多个名为“SMITH”的员工，则输出所有名为“SMITH”的员工号、工资和部门号。
DECLARE
  V_empno  pno%type;
  V_sal  emp.sal%type;
  V_deptno  emp.deptno%type;
BEGIN
        SELECT  empno,sal,deptno  INTO  V_empno,v_sal,v_deptno  FROM  emp  WHERE  ename=’SMITH’;
DBMS_OUTPUT.PUT_LINE(v_empno||’  ’||v_sal||’  ’||v_deptno);
      EXCEPTION
        WHEN  NO_DATA_FOUND  THEN
            INSERT  INTO  EMP(empno, ename,sal,deptno) VALUES(2007,’SMITH’,1500,10);
        WHEN  TOO_MANY_ROWS  THEN
            FOR  v_emp  IN  (SELECT  empno,sal,deptno  FROM  emp  WHERE  ename=’SMITH’)  LOOP
              DBMS_OUTPUT.PUT_LINE(pno||’  ‘|| v_emp.sal|| ’  ‘||v_emp.deptno);
            END  LOOP;
      END;
      /
（3） 创建一个存储过程，以员工号为参数，输出该员工的工资。
CREATE  OR  REPLACE  PROCEDURE  OUTPUT_SAL(p_empno  pno%type) 
AS
  V_sal  emp.sal%type;
BEGIN
  SELECT  sal  INTO  v_sal  FROM  emp  WHERE  empno=p_empno;
  DBMS_OUTPUT.PUT_LINE(v_sal);
EXCEPTION
  WHEN  NO_DATA_FOUND  THEN
      DBMS_OUTPUT.PUT_LINE(‘无此员工’);
END;
（4） 创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20oracle游标的使用号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300。
CREATE  OR  REPLACE  PROCEDURE  UPDATE_SAL(p_empno  pno%type)
AS
        v_deptno  emp.deptno%type;
        v_increment  NUMBER(4);       
      BEGIN   
        SELECT  deptno  INTO  v_deptno FROM  emp
                WHERE  empno=p_empno;
        CASE  v_deptno
            WHEN  10  THEN  v_increment:=150;
            WHEN  20  THEN  v_increment:=200;
            WHEN  30  THEN  v_increment:=250;
            ELSE  v_increment:=300;
        END  CASE;
        UPDATE  emp  SET  sal=sal+v_increment
                WHERE  empno=p_empno;
      EXCEPTION
  WHEN  NO_DATA_FOUND  THEN
      DBMS_OUTPUT.PUT_LINE(‘无此员工’);
      END;
      /
（5） 创建一个存储过程，以员工号为参数，返回该员工的工作年限(以参数形式返回)。
CREATE  OR  REPLACE  PROCEDURE  RETURN_GZNX(p_empno  pno%type,p_gznx  out  number)
AS
        v_gznx  NUMBER(2);       
      BEGIN   
        SELECT  trunc(months_between(sysdate,hiredate)/12)
  INTO  v_gznx FROM  emp  WHERE  empno=p_empno;
        p_gznx:=v_gznx;     
      EXCEPTION
  WHEN  NO_DATA_FOUND  THEN
      DBMS_OUTPUT.PUT_LINE(‘无此员工’);
      END;
      /
调用上面的存储过程的PL/SQL块
DECLARE 
  v_gznx  number(2);
BEGIN 
  Return_gznx(7900,v_gznx);
  DBMS_OUTPUT.PUT_LINE(v_gznx);
END;
/
（6） 创建一个存储过程，以部门号为参数，输出该部门入职日期最早的2个员工姓名。
CREATE  OR  REPLACE  PROCEDURE  OUTPUT_EMPINFO(p_deptno  emp.deptno%type)
AS
        CURSOR  c_emp  IS  SELECT  *  FROM  emp
WHERE  deptno=p_deptno  ORDER  BY  hiredate;
        V_emp  emp%rowtype;
      BEGIN   
        OPEN  c_emp;
        FETCH  c_emp  INTO  v_emp;
        IF  c_emp%found  THEN
  DBMS_OUTPUT.PUT_LINE(ame);
  FETCH  c_emp  INTO  v_emp;
  IF  c_emp%found  THEN
    DBMS_OUTPUT.PUT_LINE(ame);
  ELSE
    DBMS_OUTPUT.PUT_LINE(‘该部门只有一个员工！’);
  END IF;
ELSE
  DBMS_OUTPUT.PUT_LINE(‘无此部门!’);
        END  IF;
      END;
      /
（7） 创建一个函数，以员工号为参数，返回该员工的工资。
CREATE  OR  REPLACE  FUNCTION  RET_SAL(p_empno  pno%type) 
RETURN  emp.sal%type
AS
  v_sal  emp.sal%type;
BEGIN
  SELECT  sal  INTO  v_sal  FROM  emp  WHERE  empno=p_empno;
  RETURN  v_sal;
EXCEPTION
  WHEN  NO_DATA_FOUND  THEN     
      RETURN 0;
END;
      /
（8） 创建一个函数，以部门号为参数，返回该部门的平均工资。
CREATE  OR  REPLACE  FUNCTION  RET_AVGSAL(p_deptno  emp.deptno%type) 
RETURN  emp.sal%type
AS
  v_avgsal  emp.sal%type;
BEGIN
  SELECT  avg(sal)  INTO  v_avgsal  FROM  emp  WHERE  deptno=p_deptno;
  IF  v_avgsal  IS  NULL  THEN
      DBMS_OUTPUT.PUT_LINE('无此部门！');
      RETURN 0;
  ELSE
      RETURN v_avgsal;
  END IF; 
END;
/
调用上面函数的PL/SQL块
DECLARE
  V_deptno  emp.deptno%type;
BEGIN
  V_deptno:=&x;        DBMS_OUTPUT.PUT_LINE(RET_AVGSAL(v_deptno));       
END;
/
（9） 创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。
CREATE  OR  REPLACE  FUNCTION  RET_AVGSAL_EMPNO(p_empno  pno%type) 
RETURN  emp.sal%type
AS
  v_deptno  emp.deptno%type;
  v_avgsal  emp.sal%type;
BEGIN
  SELECT  deptno  INTO  v_deptno  FROM  emp
      WHERE  empno=p_empno;
  SELECT  avg(sal)  INTO  v_avgsal  FROM  emp 
WHERE  deptno=v_deptno;
  RETURN  v_avgsal;
EXCEPTION
  WHEN  NO_DATA_FOUND  THEN
      DBMS_OUTPUT.PUT_LINE(‘无此员工!’);
      RETURN  0;
END;
      /
    调用上面函数的PL/SQL块
DECLARE
        V_empno  pno%type;
      BEGIN
        V_empno:=&x;
    DBMS_OUTPUT.PUT_LINE(RET_AVGSAL_EMPNO(v_empno));
      END;
      /
（10） 创建一个包，包中包含一个过程和一个游标。游标返回所有员工的信息；存储过程实现每次输出游标中的5条记录。
（11） 在emp表上创建一个触发器，保证每天8：00~17：00之外的时间禁止对该表进行DML操作。
CREATE  OR  REPLACE  TRIGGER  tr_emp
BEFORE  INSERT  OR  UPDATE  OR  DELETE
ON  emp
BEGIN
  IF  ( to_number(to_char(sysdate, 'hh24')) 
            not  between  8  and  16)
  THEN
      RAISE_APPLICATION_ERROR(-20600, '现在是非工作时间，请退出!!! ');
  END  IF;
END;
/
（12） 在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各部门的人数及平均工资，并输出。
CREATE  OR  REPLACE  TRIGGER  tr_emp1
AFTER  INSERT  OR  UPDATE  OR  DELETE
ON  emp
BEGIN
  FOR  v_emp  IN  (SELECT deptno,count(*)  rs,avg(sal)  avgsal
FROM  emp  GROUP  BY  deptno)  LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp.deptno||’  ‘||v_emp.rs
||’  ‘||trunc(v_emp.avgsal)); 
  END  LOOP; 
END;
/
（13） 在emp表上创建一个触发器，保证修改员工工资时，修改后的工资低于该部门最高工资，同时高于该部门最低工资。
（14） 创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。如果修改成功，则显示“员工由…号部门调入…号部门”；如果不存在该员工，则显示“员工号不存在，请输入正确的员工号”；如果不存在该部门，则显示“该部门不存在，请输入正确的部门号”。
（15） 创建一个函数，返回各个部门的部门号、员工人数以及员工平均工资信息。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。