Oracle经典练习题很全面Word文档下载推荐.docx
《Oracle经典练习题很全面Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《Oracle经典练习题很全面Word文档下载推荐.docx（13页珍藏版）》请在冰豆网上搜索。
'
||pname||'
所在位置:
||ploc）;
exception–异常处理
whenno_data_found
thendbms_output.put_line（'
你输入的部门编号有误!
!
）;
whenothers
其他异常'
end;
方法二：
（使用%rowtype）
erowdept%rowtype;
begin
select*intoerowfromdeptwheredeptno=&
dbms_output.put_line（erow.dname||'
--'
||erow.loc）;
exception
你输入的部门号有误'
whenothers
end;
3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水（即，薪水加佣金）。
pempnoemp.empno%type;
totalSalemp.sal%type;
pempno:
请输入员工编号;
selectsal+nvl（comm,0）intototalSalfromempwhereempno=pempno;
该员工总共薪水'
||totalSal）;
你输入的员工编号有误!
4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水（即，薪水加佣金）。
erowemp%rowtype;
select*intoerowfromempwhereempno=&
dbms_output.put_line（erow.sal+nvl（m,0））;
exception
5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：
DesignationRaise
-----------------------
Clerk500
Salesman1000
Analyst1500
Otherwise2000
编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。
erowemp%rowtype;
select*intoerowfromempwhereename='
&
name'
iferow.job='
Clerk'
then
updateempsetsal=sal+500whereempno=erow.empno;
elsiferow.job='
Salesman'
updateempsetsal=sal+1000whereempno=erow.empno;
Analyst'
updateempsetsal=sal-1500whereempno=erow.empno;
else
updateempsetsal=sal+2000whereempno=erow.empno;
endif;
commit;
6.编写一个程序块，将emp表中雇员名全部显示出来。
cursorcsisselectenamefromemp;
forerowincsloop
dbms_output.put_line（erow.ename）;
endloop;
7.编写一个程序块，将emp表中前5人的名字显示出来。
方式一:
cursorcsisselectt.*from（selecte.ename,rownumrmfromempe）twheret.rmbetween1and6;
方式二:
--方式二
inumber:
=1;
i:
=i+1;
--迭代
exitwheni>
5;
--退出条件
8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。
pjobemp.job%type;
totalsalemp.sal%type;
selectjob,salintopjob,totalsalfromempwhereename='
请输入员工姓名'
dbms_output.put_line（pjob||'
----'
||totalsal）;
whenno_data_foundthen
你输入的员工姓名有误!
9.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”。
num1float;
num2float;
resfloat;
my_exceptionException;
num1:
被除数;
num2:
除数;
res:
=num1/num2;
raisemy_exception;
whenmy_exceptionthen
dbms_output.put_line（res）;
whenothersthen
除数不能为0'
二.声明和使用游标
------游标:
（集合），处理返回多行记录的问题
--声明游标
--语法：
cursor游标名isDQL;
--遍历游标
/*
1.打开游标,open游标名;
2.从游标中提取一行的记录：
fetch游标名into变量名,...;
3.使用循环，exitwhen游标名%notfound;
4.关闭游标，close游标名;
1.通过使用游标来显示dept表中的部门名称。
declare
cursorcoisselectdnamefromdept;
forvnameinco
loop
dbms_output.put_line（vname.dname）;
2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。
cursorc_empisselect*fromempwheredeptno=&
请输入部门号;
forerowinc_emploop
dbms_output.put_line（erow.ename||'
||erow.job||'
||erow.sal）;
输入的部门编号有误'
3.使用带参数的游标，实现第2题。
cursorc_cs（c_deptnonumber）isselect*fromempwheredeptno=c_deptno;
v_deptnonumber;
v_deptno:
forerowinc_cs（v_deptno）loop
4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。
cursorc_empisselectenamefromemp;
forerowinc_emploop
iferow.enamelike'
A%'
updateempsetsal=sal+sal*0.1whereename=erow.ename;
elsiferow.enamelike'
S%'
5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。
cursorc_empisselect*fromemp;
iferow.sal*1.1<
5000then
三存储过程
-----存储过程（dba声明,得授予dba权限）:
封装了一组sql语句，提前编译好，效率较高,存储在服务端
--场景：
网购：
数据库发生什么改变
--库存量-1（update）,订单增加（insert），钱（update）,物流（insert）,日志（insert）
---语法
create[orreplace]procedure存储过程名称（参数名in|out类型,....）
as|is
--声明变量
--过程化语言
*/
1.创建一个过程，能向dept表中添加一个新记录.（in参数）
createorreplaceprocedure
insert_dept（p_deptnoinnumber,p_dnameinvarchar2,p_locinvarchar2）
is
insertintodeptvalues（p_deptno,p_dname,p_loc）;
--调用该存储过程
insert_dept（50,'
DEVELOP'
'
SHENGZ'
2.创建一个过程，从emp表中输入雇员的姓名，返回该雇员的薪水值。
（out参数）
然后调用过程。
find_emp（p_nameinvarchar2,p_saloutnumber）
e_salemp.sal%type;
selectsalintoe_salfromempwhereename=p_name;
p_sal:
=e_sal;
=0;
--调用存储过程
msalnumber（5）;
find_emp（'
SCOTT'
msal）;
dbms_output.put_line（msal）;
3.编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比。
（利用过程，inout参数）
createorreplaceprocedureaddSal（p_empnoinnumber,p_numinfloat）
updateempsetsal=sal+sal*p_numwhereempno=p_empno;
输入的员工编号有误'
--访问存储过程
addSal（7788,0.5）;
存储函数
---存储函数:
封装了一组sql语句，提前编译好，效率较高,存储在服务端
---存储函数必须有一个返回值，存储函数可以用select语句中
/**
createorreplacefunction函数名（参数名in|out类型,....）
returntype
as|is
return值;
4.创建一个函数，它以部门号作为参数且返回那个部门的所有的雇员的整体薪水（其实就是该部门的平均工资）。
然后调用此函数。
createorreplacefunction
getAllSal（f_deptnoinnumber）
returnnumber
selectavg（sal）intoe_salfromempwheredeptno=f_deptno;
returne_sal;
return0;
--调用存储函数
selectgetAllSal（20）fromdual;
5.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。
然后调用此函数。
createorreplacefunction
showDnameAndLoc（f_deptnoinnumber）
returndept%rowtype
as
e_rowdept%rowtype;
select*intoe_rowfromdeptwheredeptno=f_deptno;
returne_row;
--访问存储函数
erow:
=showDnameAndLoc（20）;
dbms_output.put_line（erow.dname||'
||erow.loc）;
四触发器练习
-----触发器（监听器）:
监听表中的数据是否发生了改变
--增删改操作
/*
createorreplacetrigger触发器名
after|before在改变之前还是之后执行触发器
insert|delete|update监听表的哪个操作
on表对哪张表的监听
触发器的级别:
表级触发器，行级触发器
表级触发器不能使用old，new
行级触发器：
可以使用old，new
*/
1.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。
createorreplacetriggerdel_emp
after
delete
onemp
foreachrow
insertintoret_empvalues（:
old.empno,:
old.ename,:
old.job,
:
old.mgr,:
old.hiredate,:
old.sal,:
m,:
old.deptno）;
2.创建一个行级别触发器，停止用户删除雇员名为"
SMITH"
的记录。
createorreplacetriggernodel_SMITH
before
when（old.ename='
）
raise_application_error（20001,'
不能删除该用户信息'
3.创建一个语句级别触发器，不允许用户在"
Sundays"
使用emp表。
createorreplacetriggercontrol_emp
before
insertorupdateordeleteonemp
ifto_char（sysdate,'
DY'
nls_date_language=AMERICAN'
）--显示系统当前日期以美国格式表示的名字缩写的一天
in（'
SUN'
）then
raise_application_error（-20001,'
不允许在星期天操作emp表'
升级会员 