oracle练习答案.docx
《oracle练习答案.docx》由会员分享，可在线阅读，更多相关《oracle练习答案.docx（17页珍藏版）》请在冰点文库上搜索。
oracle练习答案
实验一
练习1、请查询表DEPT中所有部门的情况。
select*fromdept;
练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。
selectdeptno,dnamefromdept;
练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。
selectename,salfromempwheredeptno=10;
练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
selectename,salfromempwherejob='CLERK'orjob='MANAGER';
练习5、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
selectename,deptno,sal,jobfromempwheredeptnobetween10and30;
练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
selectename,sal,jobfromempwhereenamelike'J%';
练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
selectename,job,salfromempwheresal<=2000orderbysaldesc;
练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
selectename,sal,emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandjob=’CLERK’;
练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno（+）anda.sal>=2000;
练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。
selectename,job,salfromempwheresal>（selectsalfromempwhereename=’JONES’）;
练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。
selectename,job,deptnofromempwheredeptnonotin（selectdeptnofromdept）;
练习12、查找工资在1000～3000之间的雇员所在部门的所有人员信息
select*fromempwheredeptnoin（selectdistinctdeptnofromempwheresalbetween1000and3000）;
练习13、雇员中谁的工资最高。
selectenamefromempwheresal=（selectmax（sal）fromemp）;
selectenamefrom（select*fromemporderbysaldesc）whererownum<=1;
*练习14、雇员中谁的工资第二高（考虑并列第一的情况，如何处理）。
selectenamefrom（selectename,salfrom（select*fromemporderbysaldesc）whererownum<=2orderbysal）whererownum<=1;
实验二
1．查询所有雇员的姓名、SAL与COMM之和。
selectename,sal+nvl（comm,0）“sal-and-comm”fromemp;
2．查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字
selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptnoandhiredate<=to_date（‘1981-07-01’,’yyyy-mm-dd’）;
3．查询各部门中81年1月1日以后来的员工数
selectdeptno,count（*）fromempwherehiredate>=to_date（‘1981-01-01’,’yyyy-mm-dd’）groupbydeptno;
4．查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资
selectename,salfromempwhere（job=’MANAGER’orjob=’SALES’）anddeptnoin（selectdeptnofromdeptwhereloc=’CHICAGO’）;
5．查询列出来公司就职时间超过24年的员工名单
selectenamefromempwherehiredate<=add_months（sysdate,-288）;
6．查询于81年来公司所有员工的总收入（SAL和COMM）
selectsum（sal+nvl（comm,0））fromempwhereto_char（hiredate,’yyyy’）=’1981’;
7．查询显示每个雇员加入公司的准确时间，按××××年××月××日时分秒显示。
selectename,to_char（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）fromemp;
8．查询公司中按年份月份统计各地的录用职工数量
selectto_char（hiredate,'yyyy-mm'）,loc,count（*）fromemp,dept
whereemp.deptno=dept.deptnogroupbyto_char（hiredate,'yyyy-mm'）,loc;
9．查询列出各部门的部门名和部门经理名字
selectdname,enamefromemp,deptwhereemp.deptno=dept.deptnoandjob=’MANAGER’;
10．查询部门平均工资最高的部门名称和最低的部门名称
selectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal））whererownum<=1）
unionallselectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal）desc）whererownum<=1）;
11．*查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名
selectename,dname
from（selectename,deptnofrom
（selectename,deptnofromempwherehiredate>（selecthiredatefromempwhereempno=7521）orderbyhiredate）whererownum<=1）e,dept
wheree.deptno=dept.deptno
实验三、
1．建立一个表（表名自定），表结构与EMP相同，没有任何记录。
createtablemy_empasselect*fromemp;
2．用INSERT语句输入5条记录，并提交。
3．扩大该表的记录数到约40条，并使雇员号不重复；每个雇员都有所属部门，雇员在同一部门的经理是同一人。
insert….
update…
commit
4．建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。
altertablemy_deptadd（constraints1primarykey（deptno））;
altertablemy_empadd（constraints2foreignkey（deptno）referencesdept（deptno））;
5．对在‘NEWYORK’工作的雇员加工资，每人加200。
6．*如果雇员姓名与部门名称中有一个或一个以上相同的字母，则该雇员的COMM增加500。
updatemy_empa
setcomm=NVL（comm,0）+500
wherea.ename<>（
selecttranslate（a.ename,b.dname,CHR（27））
frommy_deptbwhereb.deptno=a.deptno
）;
--a.deptno与b.deptno必须有主外键连接，否则可能出错，为什么？
commit;
7．删除部门号为30的记录，并删除该部门的所有成员。
deletefromempwheredeptno=30;
deletefromdeptwheredeptno=30;
commit
8．新增列性别SEX，字符型。
altertableempadd（sexchar
（2））;
9．修改新雇员表中的MGR列，为字符型。
该列数据必须为空
altertableempmodify（mgrvarchar2（20））;
10．试着去删除新表中的一个列。
altertablemy_empdrop（comm）;
实验四、
1．查询部门号为30的所有人员的管理层次图。
selectlevel,enamefromemp
connectbymgr=priorempno
startwithdeptno=30andjob='MANAGER';
2．查询员工SMITH的各个层次领导。
selectlevel,enamefromemp
connectbypriormgr=empno
startwithENAME='SMITH';
3．查询显示EMP表各雇员的工作类型，并翻译为中文显示
用decode函数
4．*查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法）
用decode函数
5．建立一个视图myV_emp，视图包括myEMP表的empno、ename、sal，并按sal从大到小排列。
createviewmyV_EMPasselectempno,ename,salfromemp;
6．定义一个mySeq，对selectmySeq.nextval,my_emp.*frommy_emp的执行结果进行说明。
7．定义序列mySeq、myEMP、myV_emp的同义词，能否用同义词对上述对象进行访问。
8．在myEMP表中建立ename的唯一性索引。
9．如何在sql*plus中，运行sql的脚本（即后缀为.sql的文件）
实验五、
1．观察下列PL/SQL的执行结果
declare
semp%rowtype;
begin
select*intos
fromemp
whereename='KING';
DBMS_OUTPUT.PUT_LINE（s.empno||s.ename||s.job||s.sal）;
END;
2．编写一个PL/SQL，显示ASC码值从32至120的字符。
begin
foriin32..120
loop
dbms_output.put_line（chr（i））;
endloop;
end;
3．计算myEMP表中COMM最高与最低的差值，COMM值为空时按0计算。
declare
var1number;
var2number;
val_commnumber;
begin
selectmax（nvl（comm,0））intovar1frommyemp;
selectmin（nvl（comm,0））intovar2frommyemp;
val_comm:
=var1-var2;
dbms_output.put_line（val_comm）;
end;
4．根据表myEMP中deptno字段的值，为姓名为‘JONES’的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加400。
declare
c1number;
c2number;
begin
selectdeptnointoc1fromempwhereename=’JONES’;
ifc1=10then
c2:
=100;
elsifc1=20then
c2:
=200;
elsec2:
=400;
endif;
updateempsetsal=sal+c2whereename=’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_xxnumber:
=0;
xxnumber;
begin
--计算收入为1000－2000的所得税总额
selectsum（（sal-1000）*0.1）intoxxfromempwheresal>1000andsal<=2000;
sum_xx:
=sum_xx+xx;
--计算收入为2000－3000的所得税总额
selectsum（（sal-2000）*0.2+100）intoxxfromempwheresal>2000andsal<=3000;
sum_xx:
=sum_xx+xx;
--计算收入为3000－4000的所得税总额
selectsum（（sal-3000）*0.3+300）intoxxfromempwheresal>3000andsal<=4000;
sum_xx:
=sum_xx+xx;
--计算收入为4000以上的所得税总额
selectsum（（sal-4000）*0.4+600）intoxxfromempwheresal>4000;
sum_xx:
=sum_xx+xx;
dbms_output.put_line（sum_xx）;
end;
7．*（可选做，难题）假设有个表如myEMP，未建立主键，含有多条记录重复（列值完全相同），试编制一个PL/SQL，将多余的重复记录删除。
实验六、
1．用外部变量，实现两个PL/SQL程序间的数据交换。
SQL>variablea1number;
SQL>begin
2:
a1:
=1000;
3end;
4/
PL/SQL过程已成功完成。
SQL>begin
2dbms_output.put_line（:
a1）;
3end;
4/
1000
PL/SQL过程已成功完成。
2．插入myEMP表中的数据记录，考虑可能出现的例外，并提示。
主要的例外提示：
唯一性索引值重复DUP_VAL_ON_INDEX
3．删除myDEPT表中的数据记录一条，考虑例外情况，并提示。
主要的例外提示：
违反完整约束条件
4．将下列PL/SQL改为FOR游标
declare
cursorcur_myempisselect*fromemp;
remp%rowtype;
begin
opencur_myemp;
fetchcur_myempintor;
whilecur_myemp%found
loop
dbms_output.put_line（r.ename）;
fetchcur_myempintor;
endloop;
closecur_myemp;
end;
5．工资级别的表salgrade，列出各工资级别的人数。
（用游标来完成）
declare
v1number;
cursorcur1isselect*fromsalgrade;
begin
forc1incur1
loop
selectcount（*）intov1fromempwheresalbetweenc1.losalandc1.hisal;
dbms_output.put_line（'grade'||c1.grade||''||v1）;
endloop;
end;
实验七、
1．在myEMP表中增加一个字段，字段名为EMPPASS，类型为可变长字符。
2．建立一个存储过程，用于操作用户登录的校验，登录需要使用EMPNO和EMPPASS，并需要提示登录中的错误，如是EMPNO不存在，还是EMPNO存在而是EMPPASS错误等。
createorreplaceprocedurep_login（
in_empnoinemp.empno%type,
in_emppassinemp.emppass%type,
out_codeoutnumber,
out_descoutvarchar2）
is
x1emp.ename%type;
x2number;
begin
selectenameintox1fromempwhereempno=in_empno;
selectcount（*）intox2fromempwhereempno=in_empnoandemppass=in_emppass;
ifx2=1then
out_code:
=0;
out_desc:
=x1;
else
out_code:
=2;
out_desc:
=’用户登陆密码错误！
’;
endif;
exception
whenNO_DATA_FOUNDthen
out_code:
=1;
out_desc:
=’该用户号存在!
’;
whenTOO_MANY_ROWSthen
out_code:
=3;
out_desc:
=’该用户号有重复值!
’;
whenothersthen
out_code:
=100;
out_desc:
=’其他错误!
’;
end;
3．建立一个存储过程，实现myEMP表中指定雇员的EMPPASS字段的修改，修改前必须进行EMPPASS旧值的核对。
CREATEORREPLACEPROCEDUREP_CHANGEPASS（
IN_EMPNOINEMP.EMPNO%TYPE,
IN_OLDPASSINEMP.EMPPASS%TYPE,
IN_NEWPASSINEMP.EMPPASS%TYPE,
OUT_CODEOUTNUMBER,
OUT_DESCOUTVARCHAR2）
IS
X1NUMBER;
BEGIN
SELECTCOUNT（*）INTOX1FROMEMPWHEREEMPNO=IN_EMPNOANDEMPPASS=IN_OLDPASS;
IFX1=1THEN
updateempsetemppass=in_newpasswhereempno=in_empno;
commit;
OUT_CODE:
=0;
OUT_DESC:
=’修改口令成功’;
ELSE
OUT_CODE:
=1;
OUT_DESC:
=’修改口令不成功’;
ENDIF;
exception
whenothersthen
out_code:
=100;
out_desc:
=’其他错误’;
END;
4．建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。
createorreplacefunctionf_leader（
in_empnoinemp.empno%type）returnvarchar2
is
v1number;
v2number;
v3emp.ename%type;
v4emp.deptno%type;
begin
v1:
=in_empno;
v3:
='未找到';
selectdeptnointov4fromempwhereempno=v1;
loop
selectmgrintov2fromempwhereempno=v1;
selectenameintov3fromempwhereempno=v2anddeptno=v4;
v1:
=v2;
endloop;
exception
whenothersthen
returnv3;
end;
5．试用上题函数，实现各雇员的同一部门最高级别上司的SELECT查询。
selectf_leader（7521）fromdual;
6．*编写实验五中第六题，关于各雇员工资的所得税计算函数
实验八、
1．建立一个触发器，当myEMP表中部门号存在时，该部门不允许删除。
createorreplacetriggerdept_line_delete
beforedeleteondeptforeachrow
declare
v1number;
begin
selectcount（*）intov1fromempwheredeptno=:
old.deptno;
ifv1>=1thenRAISE_APPLICATION_ERROR（-20000,’错误’）;
endif;
end;
实验九、
1．建立一个示例包emp_mgmt中，新增一个修改雇员所在部门的过程。
createorreplacepackageemp_mgmtas
procedurechange_dept（
in_newdeptinemp.deptno%type,
out_codeoutnumber,
out_descoutvarchar2）;
mgmt_empnoemp.empno%type;
proceduremgmt_login（
in_empnoinemp.empno%type,
in_emppassinemp.emppass%type,
out_codeoutnumber,
out_descoutv
升级会员 