《Oracle技术》实验报告5(答案)1.doc

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(v_emp.ename| | v_emp.empno| |v_emp.sal| | v_emp.deptno); END LOOP; END; /（2） 编写一个PL/SQL块，查询名为“SMITH”的员工信息，并输出其员工号、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10。如果存在多个名为“SMITH”的员工，则输出所有名为“SMITH”的员工号、工资和部门号。DECLARE V_empno emp.empno%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(v_emp.empno| | v_emp.sal| |v_emp.deptno); END LOOP; END; /（3） 创建一个存储过程，以员工号为参数，输出该员工的工资。CREATE OR REPLACE PROCEDURE OUTPUT_SAL(p_empno emp.empno%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；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300。CREATE OR REPLACE PROCEDURE UPDATE_SAL(p_empno emp.empno%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 emp.empno%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(v_emp.ename); FETCH c_emp INTO v_emp; IF c_emp%found THEN DBMS_OUTPUT.PUT_LINE(v_emp.ename); ELSE DBMS_OUTPUT.PUT_LINE(该部门只有一个员工！); END IF;ELSE DBMS_OUTPUT.PUT_LINE(无此部门!); END IF; END; /（7） 创建一个函数，以员工号为参数，返回该员工的工资。CREATE OR REPLACE FUNCTION RET_SAL(p_empno emp.empno%type) RETURN emp.sal%typeAS 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%typeAS 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 emp.empno%type) RETURN emp.sal%typeAS 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 emp.empno%type; BEGIN V_empno:=&x; DBMS_OUTPUT.PUT_LINE(RET_AVGSAL_EMPNO(v_empno); END; /（10） 创建一个包，包中包含一个过程和一个游标。游标返回所有员工的信息；存储过程实现每次输出游标中的5条记录。（11） 在emp表上创建一个触发器，保证每天8：0017：00之外的时间禁止对该表进行DML操作。CREATE OR REPLACE TRIGGER tr_empBEFORE INSERT OR UPDATE OR DELETEON empBEGIN 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_emp1AFTER INSERT OR UPDATE OR DELETEON empBEGIN 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