2022年实验五PLSQL高级编程 .pdf

《2022年实验五PLSQL高级编程 .pdf》由会员分享，可在线阅读，更多相关《2022年实验五PLSQL高级编程 .pdf（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验 4 PL/SQL 高级编程开发语言及实现平台或实验环境：Oracle 10g 实践目的(1)掌握存储过程、存储函数、包、触发器高级数据库对象的基本作用。(2)掌握存储过程、存储函数、包、触发器的建立、修改、查看、删除操作。实验要求(1)记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。(2)掌握存储过程、存储函数、包、触发器的命令。实验内容1创建存储过程(1)将下列的未命名的PL/SQL，转换成存储过程，存储过程名自己设定，注意比较未命名的PL/SQL 与命名的PL/SQL 的差别，如没有where current of是什么情况。declare cursor emp_

2、cursor is select*from emp where deptno=10 for update;begin for emp_record in emp_cursor loop dbms_output.put_line(emp_record.sal);update emp set sal=sal*1.1 where current of emp_cursor;end loop;end;/（2）（3）任选一个(2)创建存储过程“dept_count_pro”，通过传入参数传入部门号deptno（如10），显示员工表“emp”中不同部门的员工人数，并执行该存储过程。(3)创建存储过程“nu

3、m_pro”，通过传入参数传入3 个数，完成3 个数的从小到大排序，通过3 个传出参数保存排序后的3 个数，并执行该存储过程，显示排序结果。2查看存储过程(1)利用SQL*Plus 或 iSQL*Plus 从 user_source 数据字典中查看存储过程。3删除存储过程(1)利用 SQL*Plus 或 iSQL*Plus 删除某个存储过程。4创建函数(1)创建存储函数“emp_fun”，通过传入参数传入员工的编号，根据传入的员工编号，检查该员工是否存在。如果存在，则返回员工的姓名，否则返回“此员工不存在“，并执行该存储函数。(2)创建存储函数“dept_count_fun”，利用传入参数传入

4、部门号(如 10)，返回员工表“emp”中不同部门的员工人数，并执行该存储函数，注意比较与存储过程“dept_count_pro”的差别。5查看存储函数(1)从 user_source 数据字典中查看存储函数。名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 5 页 -6删除存储函数(1)删除存储函数“dept_count_fun”。7创建触发器（1）新建一个部门平均工资表，编写触发器实现当雇员表中新增、删除数据或者修改工资时，重新统计各部门平均工资。create table avg_sal(deptno,avg_s)as select deptno,avg(sal)from em

5、p group by deptno;参考代码：create or replace trigger dml_a after insert or delete or update on a begin if inserting then insert into mylog values(user,sysdate,I);elsif deleting then insert into mylog values(user,sysdate,D);else insert into mylog values(user,sysdate,U);end if;end;（2）创建一个替代触发器，通过更新视图来更新基本

6、表（如向通过向视图插入一条记录，来实现对部门表和员工表插入数据的操作。create view emp_dept(empno,ename,deptno,dname)as select empno,ename,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno;参考代码：create or replace trigger tr_v_e_d instead of insert on emp_dept for each row begin 触发体;end;/（3）（4）选做一个create or replace trigger de

7、l_deptid after delete on dept for each row begin delete from emp where deptno=:old.deptno;end del_deptid;/名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 5 页 -(3)利用 SQL*Plus 或 iSQL*Plus 创建行级触发器“update_row_tri”，当 dept表的某一“deptno”值更改时，emp 表中对应的“deptno”值也跟着进行相应的更改。更改“dept”表的某一“deptno”值，查看“emp”表中对应的“deptno”值是否发生变化。(4)利用

8、 SQL*Plus 或 iSQL*Plus 创建语句级触发器“delete_tri”，当删除dept表中某个部门编号时，将就 emp表中该员工的所有信息一并删除。删除“dept”表中某个员工的信息，查看“emp”表是否还有该部门员工的信息。8查看触发器(1)从 user_triggers 数据字典中查看触发器。9删除触发器(1)删除触发器“delete_tri”。10包(1)创建一个包，包体中包括上面创建过的一个过程，一个函数。（2）创建一个包体。（3）执行包。（4）删除刚才建立的包名和包体。DROP PACKAGE BODY 包名；DROP PACKAGE 包名；常见问题分析1创建或修改存储

9、过程/存储函数时出现“名称已由现有对象使用”，创建或修改触发器时出现触发器“XXX”已经存在数据库中已存在同名对象，修改数据库对象名称或在“CREATE”关键字后加上“OR REPLACE”即可。2查看数据字典信息时，SELECT 命令正确，却查不到数据虽然 Oracle 的命令中是不区分大小写的，但查看Oracle 系统数据字典信息时所有的字母均需大写，即便是用户定义的表名。例如，正确的命令是：SELECT*FROM DBA_SOURCE WHERE NAME=CSMONEY1_PRO;错误的命令是：SELECT*FROM DBA_SOURCE WHERE NAME=csmoney1_pro

10、;3定义相冲突功能的触发器时会出错，如定义两个触发器，都是对于同一个表，当更新被参照表时，参照表一个触发器是级联置空，一个是触发器是级联删除，则触发器在执行时会报错。是触发器只能完成不冲突的动作。4利用存储过程/触发器增强参照完整性约束参照完整性是指若两个表之间具有父子关系，当删除父表数据时，必须确保相关的子表数据已经被删除；当修改父表的主键列数据时，必须确保相关子表数据已经被修名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 5 页 -改。为了实现级联删除，可以在定义外键约束时指定ON DELETE CASCADE 关键字，或是创建存储过程/触发器完成，但使用约束却不能实现级联更

11、新，此时需要使用存储过程/触发器增强参照完整性约束。如果在级联更新的同时又想接收参数，那么只能使用存储过程了。5如何在Oracle中实现类似自动增加ID 的功能Oracle本身并未提供像Access中的自动编号类型，但同样也可以实现类似自动增加 ID 的功能，即字段值自动增长并自动插入到字段中，这时需要借助序列和触发器共同来实现。例如，水果表“fruit”中有两个字段“num”、“name”，分别记录序号和水果的名称，第一个字段值随着第二个字段值的插入自动按顺序添加并插入。CREATE TABLE fruit(num VARCHAR2(10)PRIMARY KEY,name V ARCHAR2

12、(10);首先，创建一个序列NUM。CREATE SEQUENCE num INCREMENT BY 1 START WITH 1 MAXV ALUE 9999 MINV ALUE 1 NOCYCLE CACHE 20 ORDER;其次，创建一个触发器。CREATE TRIGGER fruit_tri BEFORE INSERT ON fruit FOR EACH ROW BEGIN SELECT TO_CHAR(NUM.nextval)INTO:NEW.num FROM DUAL;/将序列的下一个取值存储到fruit 表中的“name”字段，DUAL 为系统表END;插入新记录后再查看“fr

13、uit”表中的现有记录。INSERT INTO fruit(name)VALUES(菠萝);SELECT*FROM fruit;6函数执行几种方法：1）Select function_name(参数)from dual;2）varible 变量 类型（长度）;exec:变量:=function_name(参数);print 变量;（或者为select:变量 from dual）7存储过程执行几种方法1)exec procedure_name(parameter_value)2)call procedure_name(parameter_value)2)begin procedure_name(parameter_value);名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 5 页 -end 3)带有输出参数的过程执行varible 变量 类型（长度）;exec procedure_name(parameter_value,:变量);print 变量;（或者为select:变量 from dual）名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 5 页 -