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