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;练习 5、请在
2、EMP 表中查找部门号在 1030 之间的雇员的姓名、部门号、工资、工作。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;练习 10、在表 EMP 中
3、查询所有工资高于 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、查找工资在 10003000 之间的雇员所在部门的所有人员信息select * from emp where deptno in (select
4、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 工作的经理 MANAGER 和销售员 SALESMAN 的姓名、工资s
5、elect 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 translate(a.ename,b.dname,CHR(27)from m
6、y_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;commit8 新增列性别 SEX，字符型。alter table emp add(sex char(2);9 修改新雇员表中的 MGR 列，为字符型。该列数据必须为空alter table emp modify(mgr varchar2(20);1
7、0 试着去删除新表中的一个列。alter table my_emp drop (comm);实验四、1 查询部门号为 30 的所有人员的管理层次图。select level,ename from empconnect by mgr=prior empnostart with deptno=30 and job=MANAGER;2 查询员工 SMITH 的各个层次领导。select level,ename from empconnect by prior mgr= empnostart with ENAME=SMITH;3 查询显示 EMP 表各雇员的工作类型，并翻译为中文显示用 decode
8、函数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 定义序列 mySeq、myEMP、myV_emp 的同义词，能否用同义词对上述对象进行访问。8 在 myEMP 表中建立 ename 的唯
9、一性索引。9 如何在 sql*plus 中，运行 sql 的脚本（即后缀为.sql 的文件）实验五、1 观察下列 PL/SQL 的执行结果declares emp%rowtype;beginselect * into sfrom empwhere ename=KING;DBMS_OUTPUT.PUT_LINE(s.empno|s.ename|s.job|s.sal);END;2 编写一个 PL/SQL，显示 ASC 码值从 32 至 120 的字符。beginfor i in 32120loopdbms_output.put_line(chr(i);end loop;end;3 计算 myEM
10、P 表中 COMM 最高与最低的差值，COMM 值为空时按 0 计算。declarevar1 number;var2 number;val_comm number;beginselect max(nvl(comm,0) into var1 from myemp;select min(nvl(comm,0) into var2 from myemp;val_comm:=var1-var2;dbms_output.put_line(val_comm);end;4 根据表 myEMP 中 deptno 字段的值，为姓名为JONES的雇员修改工资；若部门号为 10，则工资加 100；部门号为 20，加
11、 200；其他部门加 400。declarec1 number;c2 number;beginselect deptno into c1 from emp where ename=JONES;if c1=10 thenc2:=100;elsif c1=20 thenc2:=200;else c2:=400;end if;update emp set sal=sal+c2 where ename=JONES;commit;end;5 计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、工资总和。6 计算 myEMP 中所有雇员的所得税总和。假设所得税为累进税率，所得税算
12、法为：工资收入为 01000 为免税；收入 10002000 者，超过 1000 的部分税率10；20003000 者超过 2000 部分按 20税率计算；30004000 者超过 3000 部分按30税率计算；4000 以上收入，超过 4000 部分按 40税率计算。 （请查阅累进税率的概念）declaresum_xx number:=0;xx number;begin-计算收入为 10002000 的所得税总额select sum(sal-1000)*0.1) into xx from emp where sal 1000 and sal2000 and sal3000 and sal40
13、00;sum_xx:=sum_xx+xx;dbms_output.put_line(sum_xx);end;7 *（可选做，难题）假设有个表如 myEMP，未建立主键，含有多条记录重复（列值完全相同） ，试编制一个 PL/SQL，将多余的重复记录删除。实验六、1 用外部变量，实现两个 PL/SQL 程序间的数据交换。SQL variable a1 number;SQL begin2 :a1:=1000;3 end;4 /PL/SQL 过程已成功完成。SQL begin2 dbms_output.put_line(:a1);3 end;4 /1000PL/SQL 过程已成功完成。2 插入 myE
14、MP 表中的数据记录，考虑可能出现的例外，并提示。主要的例外提示：唯一性索引值重复 DUP_VAL_ON_INDEX3 删除 myDEPT 表中的数据记录一条，考虑例外情况，并提示。主要的例外提示：违反完整约束条件4 将下列 PL/SQL 改为 FOR 游标declarecursor cur_myemp is select * from emp;r emp%rowtype;beginopen cur_myemp;fetch cur_myemp into r;while cur_myemp%foundloopdbms_output.put_line(r.ename);fetch cur_myem
15、p into r;end loop;close cur_myemp;end;5 工资级别的表 salgrade，列出各工资级别的人数。 （用游标来完成）declarev1 number;cursor cur1 is select * from salgrade;beginfor c1 in cur1loopselect 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 表中增加
16、一个字段，字段名为 EMPPASS，类型为可变长字符。2 建立一个存储过程，用于操作用户登录的校验，登录需要使用 EMPNO 和 EMPPASS，并需要提示登录中的错误，如是 EMPNO 不存在，还是 EMPNO 存在而是 EMPPASS错误等。create 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)isx1 emp.ename%type;x2 number;beginselect
17、 ename into x1 from emp where empno=in_empno;select count(*) into x2 from emp where empno=in_empno and emppass=in_emppass;if x2=1 thenout_code:=0;out_desc:=x1;elseout_code:=2;out_desc:=用户登陆密码错误！;end if;exceptionwhen NO_DATA_FOUND thenout_code:=1;out_desc:=该用户号存在!;when TOO_MANY_ROWS thenout_code:=3;o
18、ut_desc:=该用户号有重复值!;when others thenout_code:=100;out_desc:=其他错误!;end;3 建立一个存储过程，实现 myEMP 表中指定雇员的 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 VARC
19、HAR2)ISX1 NUMBER;BEGINSELECT COUNT(*) INTO X1 FROM EMP WHERE EMPNO=IN_EMPNO AND EMPPASS=IN_OLDPASS;IF X1=1 THENupdate emp set emppass=in_newpass where empno=in_empno;commit;OUT_CODE:=0;OUT_DESC:=修改口令成功;ELSEOUT_CODE:=1;OUT_DESC:=修改口令不成功;END IF;exceptionwhen others thenout_code:=100;out_desc:=其他错误;END
20、;4 建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。create or replace function f_leader(in_empno in emp.empno%type) return varchar2isv1 number;v2 number;v3 emp.ename%type;v4 emp.deptno%type;beginv1:=in_empno;v3:=未找到 ;select deptno into v4 from emp where empno=v1;loopselect mgr into v2 from emp where empno=v1;se
21、lect ename into v3 from emp where empno=v2 and deptno=v4;v1:=v2;end loop;exceptionwhen others thenreturn v3;end;5 试用上题函数，实现各雇员的同一部门最高级别上司的 SELECT 查询。select f_leader(7521) from dual;6 *编写实验五中第六题，关于各雇员工资的所得税计算函数实验八、1 建立一个触发器，当 myEMP 表中部门号存在时，该部门不允许删除。create or replace trigger dept_line_deletebefore de
22、lete on dept for each rowdeclarev1 number;beginselect 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 asprocedure change_dept(in_newdept in emp.deptno%type,out_co
23、de out number,out_desc 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 asprocedure change_dept(in_newdept in emp.deptno%type,out_code out numb
24、er,out_desc out varchar2)isbeginupdate 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)isbegin-登陆过程见实验七第 2 题mgmt_empno:=in_empno;out_code:=0;o
25、ut_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)isx emp.job%type;y number;beginselect
26、 job,deptno into x,y from emp where empno=mgmt_empno;if x=PRESIDENT thendelete from emp where empno=remove_empno and job=MANAGER;elsedelete from emp where empno=remove_empno and deptno=y and x=MANAGER;end ifif sql%found thenout_code:=0;out_desc:=ok;elseout_code:=1;out_desc:=未删除记录;end if;commit;end;4
27、 *用 DELPHIORACLE 实现上题的软件功能。实验十1 编写一段 PL/SQL，利用系统工具包，实现对 SERVER 端数据文件 D:DATAA.TXT 的读取输出至缓冲区。2 编写一个存储过程，就 myEMP 表，输入参数为字段名和匹配值（字符型） ，对符合匹配条件的工资加 100。3 编写一个存储过程，输入参数为一个表名，通过存储过程处理将该表删除 DROP，并返回是否成功的信息。实验十一1 以雇员作为对象类型，试根据 myEMP 表结构设计其属性，方法主要有雇员更换部门、更换工种、MAP 排序的定义。2 编制一个雇员类型的对象表 myOBJ_EMP。3 添加对象表 myOBJ_EMP 的数据 10 条。4 试对对象表排序输出。5 给对象表中部门号为 20 的记录的工资增加 10。6 显示每个雇员所在的雇员名、部门名称。
