oracle练习题-答案.doc

1、 实验一 练习 1、请查询表 DEPT 中所有部门的情况。 select * from dept; 练习 2、查询表 DEPT 中的部门号、部门名称两个字段的所有信息。 select deptno,dname from dept; 练习 3、请从表 EMP 中查询 10 号部门工作的雇员姓名和工资。 select ename,sal from emp where deptno=10; 练习 4、请从表 EMP 中查找工种是职员 CLERK或经理 MANAGER 的雇员姓名、工资。 select ename,sal from emp where job=CLERK or job=MANAGER;

2、 练习 5、请在 EMP 表中查找部门号在 10 30 之间的雇员的姓名、部门号、工资、工作。 select ename,deptno,sal,job from emp where deptno between 10 and 30; 练习 6、请从表 EMP 中查找姓名以 J 开头所有雇员的姓名、工资、职位。 select ename,sal,job from emp where ename like J%; 练习 7、请从表 EMP 中查找工资低于 2000 的雇员的姓名、工作、工资，并按工资降序排列。 select ename,job,sal from emp where sal=2000

3、; 练习 10、在表 EMP 中查询所有工资高于 JONES 的所有雇员姓名、工作和工资。 select ename,job,sal from emp where sal(select sal from emp where ename=JONES); 练习 11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号 。 select ename,job,deptno from emp where deptno not in (select deptno from dept); 练习 12、查找工资在 1000 3000 之间的雇员所在部门的所有人员信息 select * from emp w

4、here deptno in (select distinct deptno from emp where sal between 1000 and 3000); 练习 13、雇员中谁的工资最高。 select ename from emp where sal=(select max(sal) from emp); select ename from (select * from emp order by sal desc) where rownum=to_date(1981-01-01,yyyy-mm-dd) group by deptno; 4 查询所有在 CHICAGO 工作的经理 MA

5、NAGER 和销售员 SALESMAN 的姓名、工资 select ename,sal from emp where (job=MANAGER or job=SALES) and deptno in (select deptno from dept where loc=CHICAGO); 5 查询列出来公司就职时间超过 24 年的员工名单 select ename from emp where hiredate(select hiredate from emp where empno=7521) order by hiredate ) where rownum( select translat

6、e(a.ename,b.dname,CHR(27) from my_dept b where b.deptno=a.deptno ); -a.deptno 与 b.deptno 必须有主外键连接，否则可能出错，为什么？ commit; 7 删除部门号为 30 的记录，并删除该部门的所有成员。 delete from emp where deptno=30; delete from dept where deptno=30; commit 8 新增列性别 SEX，字符型。 alter table emp add(sex char(2); 9 修改新雇员表中的 MGR 列，为字符型。 该列数据必须

7、为空 alter table emp modify(mgr varchar2(20); 10 试着去删除新表中的一个列。 alter table my_emp drop (comm); 实验四、 1 查询部门号为 30 的所有人员的管理层次图。 select level,ename from emp connect by mgr=prior empno start with deptno=30 and job=MANAGER; 2 查询员工 SMITH的各个层次领导。 select level,ename from emp connect by prior mgr= empno start w

8、ith ENAME=SMITH; 3 查询显示 EMP 表各雇员的工作类型，并翻译为中文显示 用 decode 函数 4 *查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法） 用 decode 函数 5 建立一个视图 myV_emp，视图包括 myEMP 表的 empno、 ename、 sal，并按 sal从大到小排列。 create view myV_EMP as select empno,ename,sal from emp; 6 定义一个 mySeq，对 select mySeq.nextval,my_emp.* from my_emp 的执行结果进行说明。 7 定义序列 m

9、ySeq、 myEMP、 myV_emp 的同义词，能否用同义词对上述对象进行访问。 8 在 myEMP 表中建立 ename 的唯一性索引。 9 如何在 sql*plus 中，运行 sql的脚本（即后缀为 .sql的文件） 实验五、 1 观察下列 PL/SQL的执行结果 declare s emp%rowtype; begin select * into s from emp where ename=KING; DBMS_OUTPUT.PUT_LINE(s.empno|s.ename|s.job|s.sal); END; 2 编写一个 PL/SQL，显示 ASC 码值从 32 至 120 的

10、字符。 begin for i in 32.120 loop dbms_output.put_line(chr(i); end loop; end; 3 计算 myEMP 表中 COMM最高与最低的差值， COMM值为空时按 0 计算。 declare var1 number; var2 number; val_comm number; begin select max(nvl(comm,0) into var1 from myemp; select min(nvl(comm,0) into var2 from myemp; val_comm:=var1-var2; dbms_output.p

11、ut_line(val_comm); end; 4 根据表 myEMP 中 deptno 字段的值，为姓名为 JONES的雇员修改工资；若部门号为10，则工资加 100；部门号为 20，加 200；其他部门加 400。 declare c1 number; c2 number; begin select deptno into c1 from emp where ename=JONES; if c1=10 then c2:=100; elsif c1=20 then c2:=200; else c2:=400; end if; update emp set sal=sal+c2 where e

12、name=JONES; commit; end; 5 计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门 号、人数、工资总和。 6 计算 myEMP 中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资收入为 0 1000 为免税；收入 1000 2000 者，超过 1000 的部分税率 10； 2000 3000者超过 2000 部分按 20税率计算； 3000 4000 者超过 3000 部分按 30税率计算； 4000以上收入，超过 4000 部分按 40税率计算。（请查阅累进税率的概念） declare sum_xx number:=0; xx numbe

13、r; begin -计算收入为 1000 2000 的所得税总额 select sum(sal-1000)*0.1) into xx from emp where sal 1000 and sal2000 and sal3000 and sal4000; sum_xx:=sum_xx+xx; dbms_output.put_line(sum_xx); end; 7 *（可选做，难题）假设有个表如 myEMP，未建立主键，含有多条记录重复（列值 完全相同），试编制一个 PL/SQL，将多余的重复记录删除。 实验六、 1 用外部变量，实现两个 PL/SQL程序间的数据交换。 SQL variabl

14、e a1 number; SQL begin 2 :a1:=1000; 3 end; 4 / PL/SQL 过程已成功完成。 SQL begin 2 dbms_output.put_line(:a1); 3 end; 4 / 1000 PL/SQL 过程已成功完成。 2 插入 myEMP 表中的数据记录，考虑可能出现的例 外，并提示。 主要的例外提示：唯一性索引值重复 DUP_VAL_ON_INDEX 3 删除 myDEPT 表中的数据记录一条，考虑例外情况，并提示。 主要的例外提示：违反完整约束条件 4 将下列 PL/SQL改为 FOR 游标 declare cursor cur_myemp

15、 is select * from emp; r emp%rowtype; begin open cur_myemp; fetch cur_myemp into r; while cur_myemp%found loop dbms_output.put_line(r.ename); fetch cur_myemp into r; end loop; close cur_myemp; end; 5 工资级别的表 salgrade，列出各工资级别的人数。（用游标来完成） declare v1 number; cursor cur1 is select * from salgrade; begin

16、for c1 in cur1 loop select count(*) into v1 from emp where sal between c1.losal and c1.hisal; dbms_output.put_line(grade|c1.grade| |v1); end loop; end; 实验七、 1 在 myEMP 表中增加一个字段，字段名为 EMPPASS，类型为可变长字符。 2 建立一个存储过程，用于操作用户登录的校验，登录需要使用 EMPNO 和 EMPPASS，并需要提示登录中的错误，如是 EMPNO 不存在，还是 EMPNO 存在而是 EMPPASS 错误等。 cre

17、ate or replace procedure p_login( in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2) is x1 emp.ename%type; x2 number; begin select ename into x1 from emp where empno=in_empno; select count(*) into x2 from emp where empno=in_empno and emppass=in_em

18、ppass; if x2=1 then out_code:=0; out_desc:=x1; else out_code:=2; out_desc:=用户登陆密码错误！ ; end if; exception when NO_DATA_FOUND then out_code:=1; out_desc:=该用户号存在 !; when TOO_MANY_ROWS then out_code:=3; out_desc:=该用户号有重复值 !; when others then out_code:=100; out_desc:=其他错误 !; end; 3 建立一个存储过程，实现 myEMP 表中指定

19、雇员的 EMPPASS 字段的修改，修改前必须进行 EMPPASS 旧值的核对。 CREATE OR REPLACE PROCEDURE P_CHANGEPASS( IN_EMPNO IN EMP.EMPNO%TYPE, IN_OLDPASS IN EMP.EMPPASS%TYPE, IN_NEWPASS IN EMP.EMPPASS%TYPE, OUT_CODE OUT NUMBER, OUT_DESC OUT VARCHAR2) IS X1 NUMBER; BEGIN SELECT COUNT(*) INTO X1 FROM EMP WHERE EMPNO=IN_EMPNO AND EMP

20、PASS=IN_OLDPASS; IF X1=1 THEN update emp set emppass=in_newpass where empno=in_empno; commit; OUT_CODE:=0; OUT_DESC:=修改口令成功 ; ELSE OUT_CODE:=1; OUT_DESC:=修改口令不成功 ; END IF; exception when others then out_code:=100; out_desc:=其他错误 ; END; 4 建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。 create or replace functi

21、on f_leader( in_empno in emp.empno%type) return varchar2 is v1 number; v2 number; v3 emp.ename%type; v4 emp.deptno%type; begin v1:=in_empno; v3:=未找到 ; select deptno into v4 from emp where empno=v1; loop select mgr into v2 from emp where empno=v1; select ename into v3 from emp where empno=v2 and dept

22、no=v4; v1:=v2; end loop; exception when others then return v3; end; 5 试用上题函数，实现各雇员的同一部门最高级别上司的 SELECT 查询。 select f_leader(7521) from dual; 6 *编写实验五中第六题，关于各雇员工资的所得税计算函数 实验八、 1 建立一个触发器，当 myEMP 表中部门号存在时，该部门不允许删除。 create or replace trigger dept_line_delete before delete on dept for each row declare v1 n

23、umber; begin select count(*) into v1 from emp where deptno=:old.deptno; if v1=1 then RAISE_APPLICATION_ERROR(-20000,错误 ); end if; end; 实验九、 1 建立一个示例包 emp_mgmt 中，新增一个修改雇员所在部门的过程。 create or replace package emp_mgmt as procedure change_dept( in_newdept in emp.deptno%type, out_code out number, out_desc

24、out varchar2); mgmt_empno emp.empno%type; procedure mgmt_login( in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2); end; create or replace package body emp_mgmt as procedure change_dept( in_newdept in emp.deptno%type, out_code out number, out_desc

25、 out varchar2) is begin update emp set deptno=in_newdept where empno=mgmt_empno; commit; out_code:=0; out_desc:=ok; end; procedure mgmt_login( in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2) is begin -登陆过程见实验七第 2 题 mgmt_empno:=in_empno; out_cod

26、e:=0; out_desc:=ok; end; end; 2 假设 myEMP 表中有口令字段 password，试在包 emp_mgmt 中建立一个登录的过程，并将登录成功的雇员号存入包变量。 见前一题 3 示例包 emp_mgmt 中，将 remove_emp 操作设限，只有本部门经理操作才能删除本部门雇员记录，只有公司头头 PRESIDENT 才能删除部门经理的雇员记录。 - procedure remove_emp( remove_empno emp.empno%type, out_code number, out_desc varchar2) is x emp.job%type; y number; begin select job,deptno into x,y from emp where empno=mgmt_empno;