Question

racle 游标触发器问题忘高手回答，如有oracle 10g或11g请spool下操作记录给我看看，谢谢

创建PL/SQL块，结合游标编程
1、利用带参的显示游标，查询emp表中每个部门的员工号，员工名，工资和部门名。
2、利用带参的显示游标，任意输入一个员工的名字，查询该员工信息，并输出其员工号、工资，所在部门。如果该员工不存在，则提示该员工不存在。
3、利用带参的显式游标，任意输入一个员工的员工号，返回某员工所在部门的平均工资。
4、根据用户输入的工作，从emp表中逐条输出从事这一工作的每位员工的编号、姓名和受雇如期。
*5、列出工资高于平均工资的所有雇员的员工号，员工名和工资值。
6、利用带参的显式游标，任意输入一个部门号，逐条输出scott.emp表中该部门每位员工的编号、姓名和受雇日期。
触发器的创建和使用
1、在emp表上创建一个触发器，不允许删除雇员号为7934雇员，并提示，禁止删除该名雇员。
2、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出部门名称。

Answer 1

以下纯手打，并验证（有输出）。 有问题欢迎追问
1利用带参的显示游标，查询emp表中每个部门的员工号，员工名，工资和部门名。
declare
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARCHAR2) IS
SELECT ename,sal,empno
FROM emp
WHERE deptno=p_dept
ORDER BY ename;
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SAL%TYPE;
v_empno EMP.EMPNO%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary,v_empno;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO'|| v_empno ||'Name:'|| v_ename||' salary:'||v_salary);
END LOOP;
CLOSE c_emp;
END LOOP;
CLOSE c_dept;
END;
输出：
Department:10-ACCOUNTING
EMPNO7782Name:CLARK salary:2450
EMPNO7839Name:KING salary:5000
EMPNO7934Name:MILLER salary:1300
Department:20-RESEARCH
EMPNO7876Name:ADAMS salary:1100
EMPNO7902Name:FORD salary:3000
EMPNO7566Name:JONES salary:2975
EMPNO7788Name:SCOTT salary:3000
EMPNO7369Name:SMITH salary:800
Department:30-SALES
EMPNO7499Name:ALLEN salary:1600
EMPNO7698Name:BLAKE salary:2850
EMPNO7900Name:JAMES salary:950
EMPNO7654Name:MARTIN salary:1250
EMPNO7844Name:TURNER salary:1500
EMPNO7521Name:WARD salary:1250
Department:40-OPERATIONS
PL/SQL procedure successfully completed
2 2、利用带参的显示游标，任意输入一个员工的名字，查询该员工信息，并输出其员工号、工资，所在部门。如果该员工不存在，则提示该员工不存在。
declare
CURSOR c_emp (p_ename VARCHAR2) IS
SELECT deptno,sal,empno
FROM emp
WHERE ename=p_ename;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SAL%TYPE;
v_empno EMP.EMPNO%TYPE;
v_deptno EMP.DEPTNO%TYPE;
v_count number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Please input ename : ');
v_ename:='&input';
v_count:=0;
OPEN c_emp(v_ename);
LOOP
FETCH c_emp INTO v_deptno,v_salary,v_empno;
if c_emp%notfound then
if v_count=0 then
DBMS_OUTPUT.PUT_LINE('NO Data Found');
end if;
exit;
end if;
DBMS_OUTPUT.PUT_LINE('EMPNO'|| v_empno ||' DEPT:'|| v_deptno||' salary:'||v_salary);
v_count:=v_count+1;
END LOOP;
CLOSE c_emp;
END;

输出：
Please input ename : SCOTT
EMPNO7788 DEPT:20 salary:3000

PL/SQL procedure successfully completed

3、利用带参的显式游标，任意输入一个员工的员工号，返回某员工所在部门的平均工资。
declare
CURSOR c_emp (p_empno EMP.EMPNO%TYPE) IS
SELECT avg(sal)
FROM emp
WHERE deptno=(select deptno from emp where empno=p_empno);
v_empno EMP.EMPNO%TYPE;
v_avg_sal EMP.SAL%TYPE;
v_count number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Please input empno : ');
v_empno:=&input;
v_count:=0;
OPEN c_emp(v_empno);
LOOP
FETCH c_emp INTO v_avg_sal;
if c_emp%notfound then
if v_count=0 then
DBMS_OUTPUT.PUT_LINE('NO Data Found');
end if;
exit;
end if;
DBMS_OUTPUT.PUT_LINE('AVG SAL :' || v_avg_sal);
v_count:=v_count+1;
END LOOP;
CLOSE c_emp;
END;
Please input empno : 7566
AVG SAL :2175
4、根据用户输入的工作，从emp表中逐条输出从事这一工作的每位员工的编号、姓名和受雇如期。
declare
CURSOR c_emp (p_job EMP.JOB%TYPE) IS
SELECT empno,ename,hiredate
FROM emp
WHERE job=p_job;
v_job EMP.JOB%TYPE;
v_empno EMP.EMPNO%TYPE;
v_ename EMP.Ename%TYPE;
v_hiredate EMP.HIREDATE%TYPE;
v_count number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Please input JOB : ');
v_job:='&input';
v_count:=0;
OPEN c_emp(v_job);
LOOP
FETCH c_emp INTO v_empno,v_ename,v_hiredate;
if c_emp%notfound then
if v_count=0 then
DBMS_OUTPUT.PUT_LINE('NO Data Found');
end if;
exit;
end if;
DBMS_OUTPUT.PUT_LINE('EMPNO' || v_empno || ' ENAME: ' || v_ename || ' HIREDATE: '|| v_hiredate);
v_count:=v_count+1;
END LOOP;
CLOSE c_emp;
END;

Please input JOB : CLERK
EMPNO7369 ENAME: SMITH HIREDATE: 17-12月-80
EMPNO7876 ENAME: ADAMS HIREDATE: 23-5月 -87
EMPNO7900 ENAME: JAMES HIREDATE: 03-12月-81
EMPNO7934 ENAME: MILLER HIREDATE: 23-1月 -82

PL/SQL procedure successfully completed

5、列出工资高于平均工资的所有雇员的员工号，员工名和工资值。
此题不需要游标，硬要套用游标，可参考上面的几个题目的代码
select empno,ename,sal from emp where sal> (select avg(sal) from emp);

6、利用带参的显式游标，任意输入一个部门号，逐条输出scott.emp表中该部门每位员工的编号、姓名和受雇日期。
这个题和第四题完全一样啊~

触发器的创建和使用
1、在emp表上创建一个触发器，不允许删除雇员号为7934雇员，并提示，禁止删除该名雇员。
CREATE OR REPLACE TRIGGER emp_trig
BEFORE DELETE
ON scott.emp
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (old.empno=7934 )
BEGIN
RAISE_APPLICATION_ERROR(-20000,'该员工不能删除');
END emp_trig;
SQL> delete from emp where empno=7934;

delete from emp where empno=7934

ORA-20000: 该员工不能删除
ORA-06512: at "SCOTT.EMP_TRIG", line 2
ORA-04088: error during execution of trigger 'SCOTT.EMP_TRIG'

2、在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出部门名称。
此题，仅通过触发器，无法实现，可否再给出一些条件？比如可以使用中间表什么的。。