实验四 PLSQL高级编程.docx
《实验四 PLSQL高级编程.docx》由会员分享，可在线阅读，更多相关《实验四 PLSQL高级编程.docx（18页珍藏版）》请在冰点文库上搜索。
实验四PLSQL高级编程
实验四PL/SQL高级编程
开发语言及实现平台或实验环境：
Oracle11g
实践目的
（1）掌握游标、存储过程、存储函数、包、触发器的基本作用。
（2）掌握存储过程、存储函数、包、触发器的建立、修改、查看、删除操作。
实验要求
（1）记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。
（2）掌握游标、存储过程、存储函数、包、触发器的命令。
实验内容
1．创建存储过程
（1）将下列的未命名的PL/SQL，转换成存储过程，存储过程名自己设定，注意比较未命名的PL/SQL与命名的PL/SQL的差别，如没有wherecurrentof是什么情况。
declare
cursoremp_cursorisselect*fromempwheredeptno=10forupdate;
begin
foremp_recordinemp_cursorloop
dbms_output.put_line（emp_record.sal）;
updateempsetsal=sal*1.1wherecurrentofemp_cursor;
endloop;
end;
/
执行sal=sal*1.1语句时，只对当前游标所指向的对象进行操作。
执行sal=sal*1.1语句时，对所有找到的游标对象进行操作。
（2）（3）任选一个
（2）创建存储过程“dept_count_pro_学号后四位”，实现显示scott方案中dept表中各本门编号与名称，并显示对应部门的员工姓名和工资。
显示结果可如下图，也可自己设计：
（3）创建存储过程“num_pro_学号后四位”，通过传入参数传入3个数，完成3个数的从小到大排序，通过3个传出参数保存排序后的3个数，并执行该存储过程，显示排序结果。
（4）通过user_source数据字典中查看存储过程。
2．创建函数
（1）
（2）任选一个
（1）创建存储函数“emp_fun_学号后四位”，通过传入参数传入员工的编号，根据传入的员工编号，检查该员工是否存在。
如果存在，则返回员工的姓名，否则返回“此员工不存在”，并执行该存储函数。
（2）假定某电信公司电话收费表call_fee_account，包括缴费号码（telno，字符型）、客户交费日期（pay_date，日期型）、话费（charge，数值型）和滞纳金（late_fee，数值型），不是每笔交费记录都有滞纳金。
创建一个存储函数，返回指定日期的收费总和。
（3）从user_source数据字典中查看存储函数。
3．包
（1）创建一个包，包体中包括上面创建过的一个过程，一个函数。
（2）创建一个包体。
s
（3）执行包。
（4）删除刚才建立的包名和包体。
DROPPACKAGEBODY包名；
DROPPACKAGE包名；
4．创建触发器
（1）新建一个部门平均工资表，编写触发器实现当雇员表中新增、删除数据或者修改工资时，重新统计各部门平均工资。
（2）创建一个替代触发器，通过更新视图来更新基本表（如向通过向视图插入一条记录，来实现对部门表和员工表插入数据的操作。
（3）（4）选做一个
（3）利用SQL*Plus或iSQL*Plus创建行级触发器“update_row_tri_学号后四位”，当dept表的某一“deptno”值更改时，emp表中对应的“deptno”值也跟着进行相应的更改。
更改“dept”表的某一“deptno”值，查看“emp”表中对应的“deptno”值是否发生变化。
（4）利用SQL*Plus或iSQL*Plus创建语句级触发器“delete_tri_学号后四位”，当删除dept表中某个部门编号时，将就emp表中该员工的所有信息一并删除。
删除“dept”表中某个员工的信息，查看“emp”表是否还有该部门员工的信息。
（5）从user_triggers数据字典中查看触发器。
函数的递归调用：
求整数N的阶乘。
createorreplacefunctionfact（nint）
returnint
is
begin
ifn=1then
return1;
else
returnfact（n-1）*n;
endif;
end;
/
存储过程的递归调用
createorreplaceproceduremanager（v_empnoemp.empno%type）
is
nameemp.ename%type;
manager_noemp.empno%type;
manager_nameemp.ename%type;
begin
selectename,mgrintoname,manager_nofromempwhereempno=v_empno;
ifmanager_noisnotnullthen
selectenameintomanager_namefromempwhereempno=manager_no;
dbms_output.put_line（name||'-->'||manager_name）;
manager（manager_no）;
else
dbms_output.put_line（name||'是最高层领导'）;
endif;
end;
/
Setserveroutputon
execmanager（7369）;
常见问题分析说明
1．创建或修改存储过程/存储函数时出现“名称已由现有对象使用”，创建或修改触发器时出现触发器“XXX”已经存在数据库中已存在同名对象，修改数据库对象名称或在“CREATE”关键字后加上“ORREPLACE”即可。
2．查看数据字典信息时，SELECT命令正确，却查不到数据,虽然Oracle的命令中是不区分大小写的，但查看Oracle系统数据字典信息时所有的字母均需大写，即便是用户定义的表名。
例如，正确的命令是：
SELECT*FROMDBA_SOURCEWHERENAME=
'CSMONEY1_PRO';错误的命令是：
SELECT*FROMDBA_SOURCEWHERENAME='csmoney1_pro';
3．定义相冲突功能的触发器时会出错，如定义两个触发器，都是对于同一个表，当更新被参照表时，参照表一个触发器是级联置空，一个是触发器是级联删除，则触发器在执行时会报错。
是触发器只能完成不冲突的动作。
4．利用存储过程/触发器增强参照完整性约束
参照完整性是指若两个表之间具有父子关系，当删除父表数据时，必须确保相关的子表数据已经被删除；当修改父表的主键列数据时，必须确保相关子表数据已经被修改。
为了实现级联删除，可以在定义外键约束时指定ONDELETECASCADE关键字，或是创建存储过程/触发器完成，但使用约束却不能实现级联更新，此时需要使用存储过程/触发器增强参照完整性约束。
如果在级联更新的同时又想接收参数，那么只能使用存储过程了。
5．如何在Oracle中实现类似自动增加ID的功能
Oracle本身并未提供像Access中的自动编号类型，但同样也可以实现类似自动增加ID的功能，即字段值自动增长并自动插入到字段中，这时需要借助序列和触发器共同来实现。
例如，水果表“fruit”中有两个字段“num”、“name”，分别记录序号和水果的名称，第一个字段值随着第二个字段值的插入自动按顺序添加并插入。
CREATETABLEfruit
（numVARCHAR2（10）PRIMARYKEY,
nameVARCHAR2（10）
）;
首先，创建一个序列NUM。
CREATESEQUENCEnum
INCREMENTBY1
STARTWITH1MAXVALUE9999MINVALUE1
NOCYCLE
CACHE20
ORDER;
其次，创建一个触发器。
CREATETRIGGERfruit_tri
BEFOREINSERTONfruit
FOREACHROW
BEGIN
SELECTTO_CHAR（NUM.nextval）INTO:
NEW.numFROMDUAL;
//将序列的下一个取值存储到fruit表中的“name”字段，DUAL为系统表
END;
插入新记录后再查看“fruit”表中的现有记录。
INSERTINTOfruit（name）VALUES（‘菠萝’）;
SELECT*FROMfruit;
6．函数执行几种方法：
1）Selectfunction_name（参数）fromdual;
2）varible变量类型（长度）;
exec:
变量:
=function_name（参数）;
print变量;（或者为select:
变量fromdual）
7．存储过程执行几种方法
1）execprocedure_name（parameter_value）
2）callprocedure_name（parameter_value）
2）begin
procedure_name（parameter_value）;
end
3）带有输出参数的过程执行
varible变量类型（长度）;
execprocedure_name（parameter_value,:
变量）;
print变量;（或者为select:
变量fromdual）
升级会员 