实验四 PLSQL高级编程

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

2、are cursor emp_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;/执行sal=sal*1.1语句时，只对当前游标所指向的对象进行操作。执行sal=sal*1.1语句时，对所有找到的 游标对象进行操作。（2）（3）任选一个 (2

3、) 创建存储过程“dept_count_pro_学号后四位”，实现显示scott方案中dept表中各本门编号与名称，并显示对应部门的员工姓名和工资。显示结果可如下图，也可自己设计： (3) 创建存储过程“num_pro_学号后四位”，通过传入参数传入 3个数，完成 3 个数的从小到大排序，通过 3 个传出参数保存排序后的 3 个数，并执行该存储过程，显示排序结果。(4) 通过user_source 数据字典中查看存储过程。 2创建函数 （1）（2）任选一个 (1) 创建存储函数“emp_fun_学号后四位”，通过传入参数传入员工的编号，根据传入的员工编号，检查该员工是否存在。如果存在，则返回员

4、工的姓名，否则返回“此员工不存在”，并执行该存储函数。 (2) 假定某电信公司电话收费表call_fee_account，包括缴费号码（telno，字符型）、客户交费日期（pay_date，日期型）、话费（charge，数值型）和滞纳金（late_fee，数值型），不是每笔交费记录都有滞纳金。创建一个存储函数，返回指定日期的收费总和。 (3) 从 user_source 数据字典中查看存储函数。 3包 (1) 创建一个包，包体中包括上面创建过的一个过程，一个函数。 （2）创建一个包体。s （3）执行包。 （4）删除刚才建立的包名和包体。 DROP PACKAGE BODY 包名； DROP P

5、ACKAGE 包名；4创建触发器 （1）新建一个部门平均工资表，编写触发器实现当雇员表中新增、删除数据或者修改工资时，重新统计各部门平均工资。（2）创建一个替代触发器，通过更新视图来更新基本表（如向通过向视图插入一条记录，来实现对部门表和员工表插入数据的操作。（3）（4）选做一个(3) 利用 SQL*Plus或 iSQL*Plus创建行级触发器“update_row_tri_学号后四位”，当dept表的某一“deptno”值更改时，emp表中对应的“deptno”值也跟着进行相应的更改。更改“dept”表的某一“deptno”值，查看“emp”表中对应的“deptno”值是否发生变化。 (4)

6、 利用 SQL*Plus 或 iSQL*Plus 创建语句级触发器“delete_tri_学号后四位”，当删除dept表中某个部门编号时，将就emp表中该员工的所有信息一并删除。删除“dept”表中某个员工的信息，查看“emp”表是否还有该部门员工的信息。 (5) 从 user_triggers 数据字典中查看触发器。 函数的递归调用：求整数N的阶乘。create or replace function fact(n int) return int is begin if n=1 then return 1; else return fact(n-1)*n; end if;end;/存储过程的

7、递归调用create or replace procedure manager(v_empno emp.empno%type)is name emp.ename%type; manager_no emp.empno%type; manager_name emp.ename%type;begin select ename,mgr into name,manager_no from emp where empno=v_empno; if manager_no is not null then select ename into manager_name from emp where empno=m

8、anager_no; dbms_output.put_line(name|'->'|manager_name); manager(manager_no);else dbms_output.put_line(name|'是最高层领导');end if;end;/Set serveroutput onexec manager(7369);常见问题分析说明 1创建或修改存储过程/存储函数时出现“名称已由现有对象使用”，创建或修改触发器时出现触发器“XXX”已经存在 数据库中已存在同名对象，修改数据库对象名称或在“CREATE”关键字后加上“OR REPLACE”

9、即可。 2查看数据字典信息时，SELECT 命令正确，却查不到数据,虽然 Oracle 的命令中是不区分大小写的，但查看 Oracle 系统数据字典信息时所有的字母均需大写，即便是用户定义的表名。 例如，正确的命令是： SELECT * FROM DBA_SOURCE WHERE NAME= 'CSMONEY1_PRO'错误的命令是：SELECT * FROM DBA_SOURCE WHERE NAME= 'csmoney1_pro' 3定义相冲突功能的触发器时会出错，如定义两个触发器，都是对于同一个表，当更新被参照表时，参照表一个触发器是级联置空，一个是触发器

10、是级联删除 ，则触发器在执行时会报错。 是触发器只能完成不冲突的动作。 4利用存储过程/触发器增强参照完整性约束 参照完整性是指若两个表之间具有父子关系，当删除父表数据时，必须确保相关的子表数据已经被删除；当修改父表的主键列数据时，必须确保相关子表数据已经被修改。为了实现级联删除，可以在定义外键约束时指定 ON DELETE CASCADE 关键字，或是创建存储过程/触发器完成，但使用约束却不能实现级联更新，此时需要使用存储过程/触发器增强参照完整性约束。如果在级联更新的同时又想接收参数，那么只能使用存储过程了。 5如何在 Oracle中实现类似自动增加 ID的功能 Oracle本身并未提供像

11、 Access中的自动编号类型，但同样也可以实现类似自动增加 ID的功能，即字段值自动增长并自动插入到字段中，这时需要借助序列和触发器共同来实现。 例如，水果表“fruit”中有两个字段“num”、“name”，分别记录序号和水果的名称，第一个字段值随着第二个字段值的插入自动按顺序添加并插入。 CREATE TABLE fruit ( num VARCHAR2(10) PRIMARY KEY, name VARCHAR2(10) ); 首先，创建一个序列 NUM。 CREATE SEQUENCE num INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MIN

12、VALUE 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; 插入新记录后再查看“fruit”表中的现有记录。 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_