实验5 过程函数和程序包V.docx
《实验5 过程函数和程序包V.docx》由会员分享，可在线阅读，更多相关《实验5 过程函数和程序包V.docx（30页珍藏版）》请在冰豆网上搜索。
实验5过程函数和程序包V
实验5 过程、函数和程序包
【实验目的与要求】
⏹掌握过程的创建与调用
⏹掌握PL/SQL函数的编写与调用
⏹熟悉程序包的使用
【实验内容与步骤】
5.0．实验准备工作:
PL/SQL程序文件的编辑与执行
1．使用文档编辑器编辑以下文件，并保存为aa.sql：
2．以scott身份登录，在SQLPlus中执行＠aa命令运行程序：
注：
测试时，文件名请用全名（即包含路径，如：
@c:
\aa）
给出运行结果：
5.1．存储过程
1．最简单的存储过程编写与执行
（1）创建测试表
droptableExam_Table;
createtableExam_Table（
e_idnumber（5）,
e_namevarchar2（20）,
e_salarynumber（8,2）
）;
（2）创建存储过程
createorreplaceprocedureinsert_salary（v_idnumber,v_namevarchar2,v_salarynumber）
is
begin
insertintoExam_Tablevalues（v_id,v_name,v_salary）;
commit;
dbms_output.put_line（'数据插入成功'）;
end;
/
（3）执行（调用）存储过程
execinsert_salary（6,'g',2000）;
（4）查询执行结果
select*fromExam_Table;
给出执行的最后结果：
2．参数的使用：
in/out/inout参数
阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。
（1）用两个参数：
in,out传入一个姓名，输出：
某某人你好：
createorreplaceproceduremp（v_invarchar2,v_outoutvarchar2）
is
begin
v_out:
=v_in||'你好';
end;
/
declare
v_namevarchar2（10）;
begin
mp（'scott',v_name）;
dbms_output.put_line（v_name）;
end;--输出：
scott你好
给出运行结果：
（2）--inout类型参数
createorreplaceproceduremp（name_ininvarchar2,
name_outoutvarchar2,
name_in_outinoutvarchar2）
is
begin
dbms_output.put_line（name_in）;
name_out:
='返回的参数name_out是'||name_in;
name_in_out:
='name_in_out是'||name_in||name_in_out;
end;
给出运行结果：
（3）定义一个返回多个值的存储过程。
createorreplaceprocedurep_test（nameoutvarchar2,ageoutnumber,sexoutvarchar2,saloutnumber）
is
begin
name:
='scott';
age:
=26;
sex:
='男';
sal:
=8000;
end;
/
declare
v_namevarchar2（20）;
v_agenumber（10）;
v_sexvarchar2（5）;
v_salnumber（10）;
begin
p_test（v_name,v_age,v_sex,v_sal）;
dbms_output.put_line（v_name）;
dbms_output.put_line（v_age）;
dbms_output.put_line（v_sex）;
dbms_output.put_line（v_sal）;
end;
给出运行结果：
3.练习：
根据测试表完成下列程序的编写，并给出测试结果：
（1）已知有如下表和相应的数据，请根据要求完成实验。
createtabledepartments（
DEPARTMENT_IDNUMBER（4）primarykey,
DEPARTMENT_NAMEVARCHAR2（30）,
MANAGER_IDNUMBER（6）,
LOCATION_IDNUMBER（4）
）;
insertintodepartmentsvalues（1,'技术部',1,1）;
insertintodepartmentsvalues（2,'人事部',2,2）;
insertintodepartmentsvalues（3,'市场部',3,3）;
insertintodepartmentsvalues（4,'财务部',4,4）;
根据上表结构编写存储过程，实现以下功能，并给出测试结果。
1）根据指定的部门更新指定部门名（DEPARTMENT_NAME）的管理者（MANAGER_ID）id。
执行前的数据：
执行后的数据：
2）根据部门ID（DEPARTMENT_ID）删除指定部门
执行前的数据：
执行后的数据：
（2）编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
（操作数据库表为EMP）.
1）编写存储过程CHANGE_SALARY，给出程序代码：
2）调用存储过程：
EXECUTE CHANGE_SALARY（7788,80）
5.2．函数
1.最简单的函数：
--简单函数
（1）创建函数
createfunctionf（nameinvarchar2）returnvarchar2
is
begin
returnname;
end;
（2）调用函数
declare
v_namevarchar2（10）;
begin
v_name:
=f（'scott'）;
dbms_output.put_line（v_name）;
end;
给出运行结果：
2．稍微复杂的函数
--编写函数
createorreplacefunctionsalarylevel（salarynumber）returnvarchar2
is
begin
ifsalary<1000then
return'工资太低了,要加油了';
elsifsalary<3000then
return'还可以,但是也要努力啊';
elsifsalary<5000then
return'这个还可以';
else
'你的工资现在来说,是比较高了';
endif;
end;
--调用函数
setserveroutputon
declare
v_salarylevelvarchar2（50）;
begin
v_salarylevel:
=salarylevel（1000）;
dbms_output.put_line（v_salarylevel）;
end;
给出运行结果：
3．函数练习：
（1）.定义一个函数接收三个参数，算出最大值。
而后调用该函数，给出测试结果。
给出求三个数最大值函数getMax（num1,num2,num3）程序源码：
--调用测试：
declare
v_maxnumber（10,2）;
begin
v_max:
=getMax（10.2,34.4,34.6）;
dbms_output.put_line（v_max）;
end;
给出运行结果：
（2）.根据房屋的相关数据（长，宽），编写函数find_area，根据公式求出其建筑面积和使用面积，并写入到数据库表中，完成后，使用Select语句查询表中数据，以确认程序编写的正确性。
1）创建表：
建立房屋表House（房屋名称，长，宽，建筑面积，使用面积）；
给出相应代码：
2）插入三行记录测试，插入值时忽略建筑面积和使用面积
'1号机房',20,5.5
'2号机房',25,5.5
'卧室',200,5.5
给出相应代码：
3）利用find_area函数，更新建筑面积和使用面积字段（注：
可考虑使用游标逐行更新）
--建筑面积=长*宽+2.5
--使用面积=长*宽
给出相应代码：
4）在select查询表中数据。
给出运行结果：
执行前的数据：
执行后的数据：
5.3．包的声明和使用
阅读以下程序，理解包的声明和使用。
-----------包的声明和使用1-------------------------
DROPTABLEExam_Emps;
CREATETABLEExam_Emps（
idnumber（5）primarykey,
namevarchar2（30）,
commission_pctnumber（3,2）
）;
insertintoExam_Empsvalues（1,'张一',0.13）;
insertintoExam_Empsvalues（2,'张二',0.23）;
insertintoExam_Empsvalues（3,'张三',0.33）;
insertintoExam_Empsvalues（4,'张四',0.43）;
commit;
select*fromExam_Emps;
--创建包头
CREATEORREPLACEPACKAGEcomm_package
IS
g_commNUMBER:
=0.10;
PROCEDUREreset_comm（p_commINNUMBER）;
ENDcomm_package;
/
--创建包体
CREATEORREPLACEPACKAGEBODYcomm_package
IS
-------------在包体中定义的局部函数--------------
/*
如果输入的参数p_comm大于Exam_Emps表中最大的commission_pct
字段,则函数返回FALSE,否则函数返回TRUE
*/
FUNCTIONvalidate_comm（p_commINNUMBER）
RETURNBOOLEAN
IS
v_max_commNUMBER;
BEGIN
SELECTMAX（commission_pct）
INTOv_max_comm
FROMExam_Emps;
IFp_comm>v_max_commTHEN
RETURNFALSE;
ELSE
RETURNTRUE;
ENDIF;
ENDvalidate_comm;
---------在包体中定义的局部函数:
结束------------
------------完成在包体中声明的过程--------------
PROCEDUREreset_comm（p_commINNUMBER）
IS
BEGIN
IFvalidate_comm（p_comm）THEN
g_comm:
=p_comm;
ELSE
RAISE_APPLICATION_ERROR（-20210,'不合理的表达式'）;
ENDIF;
ENDreset_comm;
----------完成在包体中声明的过程:
结束------------
ENDcomm_package;
--测试包
EXECUTEcomm_package.reset_comm
（1）;
EXECUTEcomm_package.reset_comm（.33）;
给出运行结果：
begin
comm_package.reset_comm（0.15）;
dbms_output.put_line（'g_comm='||comm_package.g_comm）;
end;
/
给出运行结果：
-----------包的声明和使用2-------------------------
droptableExam_Emp;
createtableExam_Emp（
idnumber（5）,
namevarchar2（30）,
salarynumber（8,2）
）;
insertintoExam_Empvalues（1,'张一',3000）;
insertintoExam_Empvalues（2,'张二',3400）;
insertintoExam_Empvalues（3,'张三',5600）;
commit;
createorreplacepackagetax_pkgas
functiontax（v_valueinnumber）returnnumber;
endtax_pkg;
/
createorreplacepackagebodytax_pkg
as
-------------包体中的函数执行部分---------------
functiontax（v_valueinnumber）returnnumber
is
begin
ifv_value<1000then
return（v_value*0）;
elsifv_value<5000then
return（v_value*0.10）;
elsifv_value<10000then
return（v_value*0.15）;
else
return（v_value*0.20）;
endif;
endtax;
-------------包体中的函数:
结束-----------------
endtax_pkg;
/
--测试包中定义的函数
selectsalary,tax_pkg.tax（salary）fromExam_Emp;
给出运行结果：
5.4作业与思考练习题
以为实验中用到的表除作特殊说明外，均为scott模式下的表，如Emp表，请在实验时，连接到scott模式。
1.创建一个存储过程show_emp（），以员工号为参数，输出该员工的工资。
执行测试结果应如下：
（1）请给出程序源码：
（2）请给出测试结果：
2.创建一个存储过程，以员工号为参数，修改该员工的工资：
若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300.
（1）请给出程序源码：
createorreplaceprocedurep_changesal（p_empnoemp.empno%type）
as
v_deptnoemp.deptno%type;
v_incrementemp.sal%type;
v_salemp.sal%type;
begin
selectdeptnointov_deptnofromempwhereempno=p_empno;
case
whenv_deptno=10thenv_increment:
=150;
whenv_deptno=20thenv_increment:
=200;
whenv_deptno=30thenv_increment:
=250;
elsev_increment:
=300;
endcase;
selectsalintov_salfromempwhereempno=p_empno;
dbms_output.put_line（v_sal+v_increment）;
end;
（2）完成后，请执行存储过程，并查询表中数据确认是否完成规定要求。
3.创建一个存储过程maxSal（），以一个整数为参数，输出工资最高的前几个（以参数值为标准）员工的信息。
执行测试结果应如下：
（1）请给出程序源码：
createorreplaceproceduremaxsal（v_numinnumber）
is
cursorcsr_salisselect*fromemporderbysaldesc;
v_empcsr_sal%rowtype;
begin
opencsr_sal;
if（csr_sal%notfound）then
dbms_output.put_line（'Sorry,thereisnodata'）;
else
loop
fetchcsr_salintov_emp;
dbms_output.put_line（v_emp.empno||''||v_emp.ename||''||v_emp.sal）;
exitwhencsr_sal%rowcount=v_num;
endloop;
endif;
closecsr_sal;
endmaxsal;
/
（2）请给出测试结果：
4.创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。
执行测试结果应如下：
（1）请给出程序源码：
createorreplaceprocedureselect_emp（v_numinnumber,m_numinnumber）
is
cursorcsr_salisselect*fromempwheresalbetweenv_numandm_num;
v_empcsr_sal%rowtype;
begin
opencsr_sal;
if（csr_sal%notfound）then
dbms_output.put_line（'Sorry,thereisnodata'）;
else
loop
fetchcsr_salintov_emp;
dbms_output.put_line（v_emp.empno||''||v_emp.ename||''||v_emp.sal）;
exitwhencsr_sal%rowcount=v_num;
endloop;
endif;
closecsr_sal;
endselect_emp;
/
（2）请给出测试结果：
5.编写一个存储过程checkSal（），用以检查所指定雇员，传入员工工号作为参数，检查该员工的薪水是否在有效范围内。
不同职位的薪水范围为：
DesignationRaise
Clerk1500-2500
Salesman2501-3500
Analyst3501-4500
Others4501andabove.
如果薪水在此范围内，则显示消息"SalaryisOK"，否则，更新薪水为该范围内的最低值。
请给出程序源码：
请给出程序运行结果：
6.编写一个PL/SQL函数count_by_Salary，接受传入参数Salary_min和Salary_max，据此统计并返回薪水值在Salary_min和Salary_max之间的员工人数。
请给出程序源码：
createorreplaceprocedurecount_by_salary（salary_mininnumber,salary_maxinnumber）
as
numnumber:
=0;
begin
selectcount（*）intonum
fromemp
wheresalbetweensalary_minandsalary_max
orderbysaldesc;
dbms_output.put_line（num）;
end;
请给出程序运行结果：
7.创建一个函数getAveSal（），以员工号为参数，返回该员工所在部门的平均工资。
请给出程序源码：
createorreplaceproceduregetAveSal（v_empnoemp.empno%type）
returnemp.sal%type
as
v_salemp.sal%type
begin
selectavg（sal）intov_sal
fromemp
wheredeptno=（selectdeptno
fromemp
whereempno=v_empno）;
returnv_sal;
end;
请给出程序运行结果：
8.创建一个包pkg_emp，包中包含一个函数和一个存储过程。
函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。
要求完成后，可以以如下方式调用包：
（1）调用包中函数：
（2）调用包中存储过程
（1）请给出程序源码：
createorreplacepackagepkg_emp
is
functionreturn_maxsal（v_deptnodept.deptno%type）
returnnumber;
procedurep_maxsal（p_v_deptnodept.deptno%type）;
endpkg_emp;
/
createorreplacepackagebodypkg_emp
as
functionreturn_maxsal（v_deptnodept.deptno%type）
returnnumber
as
out_salnumber;
begin
selectmax（sal）intoout_salfromempwheredeptno=v_deptno;
return（out_sal）;
endreturn_maxsal;
procedurep_maxsal（p_v_deptnodept.deptno%type）
is
max_salnumber;
cursorcsr_salisselect*fromempwheredeptno=p_v_deptnoorder
bysaldesc;
v_empcsr_sal%rowtype;
begin
selectmax（sal）intomax_salfromem
升级会员 