47查询部门人数大于5的部门的员工信息。
select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5)
48查询所有员工工资都大于2000的部门的信息。
select * from dept where deptno not in (select deptno from emp where sal<2000) 49查询所有员工工资都大于2000的部门的信息及其员工信息。 select dept.deptno,dname,loc,ename,empno,sal from emp ,dept
where emp.deptno=dept.deptno and deptno not in (select deptno from emp where sal<2000)
50查询所有员工工资都在2000∽3000之间的部门的信息。 select * from dept where deptno not in
(select deptno from emp where sal not between 2000 and 3000)
51查询所有工资在2000∽3000之间的员工所在部门的员工的信息。
select * from emp where deptno in (select distinct deptno from emp where sal between 2000 and 3000)
查询每个员工的领导所在部门的信息。
select detp.deptno,dname,loc from dept, emp worker,emp manager
where dept.deptno=manager.deptno and worker.mgr=manager.empno 52查询人数最多的部门信息。 select * from dept
where deptno in (select deptno from emp group by deptno having count(*)>=all(select max(count(*)) from emp group by deptno))
53查询30号部门中工资排序前3名的员工的信息。
54查询所有员工中工资排序在5∽10之间的员工的信息。 查询SMITH员工及其所有直接、间接下属员工的信息。 查询SCOTT员工及其直接、间接上级员工的信息。 以树状结构查询所有员工与领导之间的层次关系。
55向emp中插入一条记录，员工号为1357，员工名为Oracle，工资为2050，部门号为20，入职日期为2002年5月10日。 Insert into emp(empno,ename,sal,deptno,hiredate) values(1357,’oracle’,2050,20,TO_DATE(‘2002-5-10’,’YYYY-MM-DD’); 56向emp中插入一条记录，员工号为8000，员工名为FAN，其他信息与SMITH员工的信息相同。
insert into emp(ename,empno,mgr,job,hirdate,sal, comm.,deptno) select ‘FAN’,8000,mgr,job,hirdate,sal,comm.,deptno from emp where ename=’SMITH’
57将各个部门员工的工资修改为该员工所在部门平均工资加1000
update emp e set sal=1000+(select avg(sal) from emp where deptno=e.deptno)
3．选择题 （1）B （2）B、D （3）A （4）A、B （5）C （6）E （7）B （8）D （9）A、C （10）D
第15章PL/SQL程序设计
1．简答题 （1）
PL/SQL语言是Oracle数据库专用的一种高级程序设计语言，是对标准SQL语言进行了过程化扩展的语言。具有如下特点： ? 与SQL语言紧密集成，所有的SQL语句在PL/SQL中都得到支持； ? 减小网络流量，提高应用程序的运行性能。 ? 模块化的程序设计功能，提高系统可靠性。 ? 服务器端程序设计，可移植性好。 （2）
PL/SQL程序的基本单元是语句块，所有的PL/SQL程序都是由语句块构成的，语句块之间可以相互嵌套，每个语句块完成特定的功能。 ? 声明部分：以关键字DECLARE开始，BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。 ? 执行部分：是PL/SQL块的功能实现部分，以关键字BEGIN开始，EXCEPTION或END结束（如果PL/SQL块中没有异常处理部分，则以END结束）。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。 ? 异常处理部分：以关键字EXCEPTION开始，END结束。该部分用于处理该块执行过程中产生的异常。 （3）
PL/SQL程序中的选择结构有两种类型，分别为IF系列和CASE系列。循环结构包括简单循环、WHILE循坏、FOR循环三种。 （4）
游标的作用是将数据库的中数据检索出来后缓存，可以被PL/SQL程序一行一行的读取并处理。支持一条、多条、零条记录的处理。游标的基本操作步骤为声明游标、打开游标、检索游标、关闭游标。 （5）
Oracle中的命名块经过编译后可以存储在数据库服务器端，供用户从不同的客户端调用。常用的命名块包括存储过程、函数、包和触发器。其中，过程用于实现某特定操作；函数用户将运行结果返回给用户；包是一系列过程、函数、常量等集合；触发器是特定的事件处理器，当特定的事件发生时，系统自动进行调用执行。
（6）
触发器包括DML触发器、INSTEAD-OF触发器和系统触发器。其中，DML触发器主要作用于表，其事件有INSERT、UPDATE、DELETE；INSTEAD-OF触发器主要主用于视图，其事件有INSERT、UPDATE、DELETE；系统触发器主要是DML事件和系统事件发生时调用的触发器，其中DML事件包括CREATE、DROP、ALTER等，系统事件包括LOGON、LOGOFF、STARTUP、SHUTDOWN、SERVERERROR等。 （7）
DML触发器执行顺序为：
? 如果存在，执行语句级前触发器。 ? 对于受触发事件影响的每一个记录： ? 如果存在，执行行级前触发器； ? 执行当前记录的DML操作（触发事件）； ? 如果存在，执行行级后触发器。 ? 如果存在，执行语句级后触发器。 （8）
DML触发器中的语句级触发器是激发触发器的SQL语句不管涉及到多少记录，触发器只执行一次，而行级触发器是激发触发器的SQL语句涉及到多少记录，触发器就执行多少次。通常，如果要获取当前操作记录的信息，需要采用行级触发器，而如果需要获取整个操作之前或操作之后的信息，可以采用语句级触发器。 （9）
基于数据库的系统触发器是只要数据库中相应事件发生，触发器就执行，与用户没有关系；而模式级别的触发器只有特定用户操作中的特定事件发生时触发器才执行。 （10）
PL/SQL命名块可以一次编译多次执行，可以放在服务器端由不同客户端用户调用；而匿名块只能一次性执行，不能被其他用户调用。 2． 实训题 1.前两题：
SQL> conn system/aaa as sysdba 已连接。
SQL> alter user scott identified by tiger account unlock; 用户已更改。
SQL> conn scott/tiger; 已连接。 SQL> declare
2 cursor c_emp is 3 select * from emp;
4 v_emp c_emp%rowtype; 5 begin
6 open c_emp;loop
7 fetch c_emp into v_emp; 8 exit when c_emp%notfound;
9 dbms_output.put_line(v_emp.ename||' '||v_emp.empno||' '||v_emp.deptno||' '||v_emp.sal); 10 end loop; 11 close c_emp; 12 end; 13 /
PL/SQL 过程已成功完成。
SQL> set serveroutput on SQL> declare
2 cursor c_emp is 3 select * from emp;
4 v_emp c_emp%rowtype; 5 begin
6 open c_emp;loop
7 fetch c_emp into v_emp; 8 exit when c_emp%notfound;
9 dbms_output.put_line(v_emp.ename||' '||v_emp.empno||' '||v_emp.deptno||' '||v_emp.sal); 10 end loop; 11 close c_emp; 12 end; 13 /
SMITH 7369 20 800 ALLEN 7499 30 1600 WARD 7521 30 1250 JONES 7566 20 2975 MARTIN 7654 30 1250 BLAKE 7698 30 2850 CLARK 7782 10 2450 SCOTT 7788 20 3000 KING 7839 10 5000 TURNER 7844 30 1500 ADAMS 7876 20 1100 JAMES 7900 30 950 FORD 7902 20 3000 MILLER 7934 10 1300
PL/SQL 过程已成功完成。 SQL> set serveroutput on
SQL> v_emp emp%ROWTYPE;
SP2-0734: 未知的命令开头 \忽略了剩余的行。 SQL> declare
2 v_emp emp%ROWTYPE; 3 BEGIN
4 SELECT * INTO v_emp FROM EMP WHERE ename='SMITH';
5 DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.sal||' '||v_emp.deptno); 6 EXCEPTION
7 WHEN NO_DATA_FOUND THEN 8 INSERT INTO EMP(EMPNO,ENAME,SAL,DEPTNO) VALUES(2007,'SMITH',1500,10);
9 WHEN TOO_MANY_ROWS THEN
10 FOR v IN (SELECT * FROM EMP WHERE ENAME='SMITH') LOOP 11 DBMS_OUTPUT.PUT_LINE(v.empno||' '||v.sal||' '||v.deptno); 12 END LOOP; 13 END; 14 /
7369 800 20
PL/SQL 过程已成功完成。 （3）
CREATE OR REPLACE PROCEDURE SHOWSAL(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); END;
begin
showsal(7844); end; （4） CREATE OR REPLACE PROCEDURE UPDATESAL (p_empno emp.empno%TYPE) AS
v_deptno emp.deptno%TYPE; v_inc emp.sal%TYPE; BEGIN
SELECT deptno INTO v_deptno FROM emp WHERE empno=p_empno; CASE v_deptno
WHEN 10 THEN v_inc:=150; WHEN 20 THEN v_inc:=200; WHEN 30 THEN v_inc:=250; ELSE v_inc:=300; END CASE;
UPDATE emp SET sal=sal+v_inc WHERE empno=p_empno; END; （5）