实验五PLSQL高级编程

实验4 PL/SQL高级编程开发语言及实现平台或实验环境：Oracle 10g实践目的(1) 掌握存储过程、存储函数、包、触发器高级数据库对象的基本作用。

(2) 掌握存储过程、存储函数、包、触发器的建立、修改、查看、删除操作。

实验要求(1) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。

(2) 掌握存储过程、存储函数、包、触发器的命令。

实验内容1．创建存储过程(1) 将下列的未命名的PL/SQL，转换成存储过程，存储过程名自己设定，注意比较未命名的PL/SQL 与命名的PL/SQL 的差别，如没有where current of 是什么情况。

declarecursor emp_cursor is select * from emp where deptno=10 for update;beginfor emp_record in emp_cursor loopdbms_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) 创建存储过程“num_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”，利用传入参数传入部门号(如10)，返回员工表“emp”中不同部门的员工人数，并执行该存储函数，注意比较与存储过程“dept_count_pro”的差别。

5．查看存储函数(1) 从user_source 数据字典中查看存储函数。

6．删除存储函数(1) 删除存储函数“dept_count_fun”。

7．创建触发器（1）新建一个部门平均工资表，编写触发器实现当雇员表中新增、删除数据或者修改工资时，重新统计各部门平均工资。

create table avg_sal(deptno ,avg_s )as select deptno,avg(sal) from emp group by deptno;参考代码：create or replace trigger dml_aafter insert or delete or update on abeginif inserting theninsert into mylog values(user,sysdate,'I');elsif deleting theninsert into mylog values(user,sysdate,'D');elseinsert into mylog values(user,sysdate,'U');end if;end;（2）创建一个替代触发器，通过更新视图来更新基本表（如向通过向视图插入一条记录，来实现对部门表和员工表插入数据的操作。

create view emp_dept (empno,ename,deptno,dname)as select empno,ename,dept.deptno,dnamefrom emp,deptwhere dept.deptno=emp.deptno;参考代码：create or replace trigger tr_v_e_dinstead of insert on emp_deptfor each rowbegin触发体;end;/（3）（4）选做一个create or replace trigger del_deptidafter delete on deptfor each rowbegindelete from emp where deptno=:old.deptno;end del_deptid;/(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”表是否还有该部门员工的信息。

8．查看触发器(1) 从user_triggers 数据字典中查看触发器。

9．删除触发器(1) 删除触发器“delete_tri”。

10．包(1) 创建一个包，包体中包括上面创建过的一个过程，一个函数。

（2）创建一个包体。

（3）执行包。

（4）删除刚才建立的包名和包体。

DROP PACKAGE BODY 包名；DROP PACKAGE 包名；常见问题分析1．创建或修改存储过程/存储函数时出现“名称已由现有对象使用”，创建或修改触发器时出现触发器“XXX”已经存在数据库中已存在同名对象，修改数据库对象名称或在“CREATE”关键字后加上“ORREPLACE”即可。

2．查看数据字典信息时，SELECT 命令正确，却查不到数据虽然Oracle 的命令中是不区分大小写的，但查看Oracle 系统数据字典信息时所有的字母均需大写，即便是用户定义的表名。

例如，正确的命令是：SELECT * FROM DBA_SOURCE WHERE NAME='CSMONEY1_PRO';错误的命令是：SELECT * FROM DBA_SOURCE WHERE NAME='csmoney1_pro';3．定义相冲突功能的触发器时会出错，如定义两个触发器，都是对于同一个表，当更新被参照表时，参照表一个触发器是级联置空，一个是触发器是级联删除，则触发器在执行时会报错。

是触发器只能完成不冲突的动作。

4．利用存储过程/触发器增强参照完整性约束参照完整性是指若两个表之间具有父子关系，当删除父表数据时，必须确保相关的子表数据已经被删除；当修改父表的主键列数据时，必须确保相关子表数据已经被修改。

为了实现级联删除，可以在定义外键约束时指定ON DELETE CASCADE 关键字，或是创建存储过程/触发器完成，但使用约束却不能实现级联更新，此时需要使用存储过程/触发器增强参照完整性约束。

如果在级联更新的同时又想接收参数，那么只能使用存储过程了。

5．如何在Oracle中实现类似自动增加ID的功能Oracle本身并未提供像Access中的自动编号类型，但同样也可以实现类似自动增加ID的功能，即字段值自动增长并自动插入到字段中，这时需要借助序列和触发器共同来实现。

例如，水果表“fruit”中有两个字段“num”、“name”，分别记录序号和水果的名称，第一个字段值随着第二个字段值的插入自动按顺序添加并插入。

CREATE TABLE fruit( num V ARCHAR2(10) PRIMARY KEY,name V ARCHAR2(10));首先，创建一个序列NUM。

CREATE SEQUENCE numINCREMENT BY 1START WITH 1 MAXV ALUE 9999 MINV ALUE 1NOCYCLECACHE 20ORDER;其次，创建一个触发器。

CREATE TRIGGER fruit_triBEFORE INSERT ON fruitFOR EACH ROWBEGINSELECT TO_CHAR(NUM.nextval) INTO :NEW.num FROM DUAL;//将序列的下一个取值存储到fruit 表中的“name”字段，DUAL 为系统表END;插入新记录后再查看“fruit”表中的现有记录。

INSERT INTO fruit(name) V ALUES(‘菠萝’);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)beginprocedure_name(parameter_value);end3)带有输出参数的过程执行varible 变量类型（长度）;exec procedure_name(parameter_value,:变量); print 变量;（或者为select :变量from dual）。