实验四 PLSQL高级编程.docx
- 文档编号:24037603
- 上传时间:2023-05-23
- 格式:DOCX
- 页数:18
- 大小:230.17KB
实验四 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)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验四 PLSQL高级编程 实验 PLSQL 高级 编程