教学材料《Oracle数据库》-项目七 程序单元在数据库中的应用

知识目标掌握利用OEM管理存储过程、函数、触发器的方式01.02.掌握利用命令行管理存储过程、函数、触发器的方式能力目标会利用OEM创建和使用存储过程、函数、触发器01.02.会利用命令行创建和使用存储过程、函数、触发器任务1存储过程010203创建和查看存储过程执行存储过程修改和删除存储过程创建和查看存储过程0102OEM方式创建和查看存储过程命令行方式创建和查看存储过程删除用户任务1存储过程-01创建和查看存储过程01OEM方式创建和查看存储过程任务1存储过程-01创建和查看存储过程任务：使用OEM方式创建数据库存储过程“pro_insertDept”，该存储过程能向scott用户的表dept添加一条新记录。01OEM方式创建和查看存储过程任务1存储过程-01创建和查看存储过程02任务1存储过程-01创建和查看存储过程命令行方式创建和查看存储过程基本语法CREATE[ORREPLACE]PROCECURE存储过程名AS|ISBEGINPL/SQL语句;[EXCEPTION][异常处理语句] END[存储过程名]02任务1存储过程-01创建和查看存储过程命令行方式创建和查看存储过程操作步骤如下1)首先以soctt身份连接数据库

connscott/tiger2)之后使用SQL语句方式创建或修改存储过程“pro_insertDept”createorreplaceprocedurepro_insertDeptasbegininsertintodeptvalues(77,’后勤部’,’NANJING’);commit;end;任务：使用SQL语句方式创建数据库存储“pro_insertDept”，该存储过程能向scott用户的表dept添加一条新记录。02任务1存储过程-01创建和查看存储过程命令行方式创建和查看存储过程3)输入“/”直接运行，运行结果为“过程已创建”。4)同样，我们可以使用命令行方式查看scott方案中所创建的存储过程。使用代码：selectobject_namefromuser_objectswhereobject_type=’PRCEDURE’;

注意，对象类型PRCEDURE必须大写。02任务1存储过程-01创建和查看存储过程命令行方式创建和查看存储过程拓展任务：创建一个存储过程，并定义3个输入变量，将这3个变量插入到表的dept中。

Orcale为了增强存储过程的灵活性，可以实现向存储过程传入参数，参数是一种向程序单元输入和输出数据的机制。运行结果如图7-8所示。createorreplaceprocedureinsert_dept(num_deptnoinnumber,var_enameinvarchar2,var_locinvarchar2)isbegininsertintodeptvalues(num_deptno,var_ename,var_loc);commit;endinsert_dept ;执行存储过程0102执行无参的存储过程执行有参的存储过程删除用户任务1存储过程-02执行存储过程01执行无参的存储过程任务1存储过程-02执行存储过程executepro_insertDept;运行结果为“过程已经成功完成”。接下来我们通过查找dept表中的数据，发现新记录已经添加成功。任务：执行无参的存储过程“pro_insertDept”02执行有参的存储过程任务1存储过程-02执行存储过程方法1：指定名称传递任务：执行有参的存储过程“insert_dept”方法2：按位置传递修改和删除存储过程0102OEM方式修改和删除存储过程命令行方式修改和删除存储过程删除用户任务1存储过程-03修改和删除存储过程01OEM方式修改和删除存储过程删除用户任务1存储过程-03修改和删除存储过程02命令方式修改和删除存储过程删除用户任务1存储过程-03修改和删除存储过程删除过程语法：DROPPROCEDURE[<方案名>.]<存储过程名>;修改过程有两种方法，一种是删除过程，然后重新创建；另一种采用CREATEORREPLACEPROCEDURE方式重新创建并替换原有的存储过程，在此不再赘述。任务2函数010203创建和查看函数执行函数修改和删除函数01任务2函数-01创建和查看函数命令行方式创建函数创建函数的基本语法CREATE[ORREPLACE]FUNCTION函数名(参数列表)RETURN返回值类型AS[函数内部变量]BEGIN

函数体;[EXCEPTION][异常处理语句]END[函数名];01任务2函数-01创建和查看函数命令行方式创建函数任务：创建一个函数“getsal”，以雇员号查询雇员薪水。

createfunctiongetsal(snonumber)returnnumberisvalnumber;beginselectsalintovalfromempwhereempno=sno;returnval;end;02任务2函数-01创建和查看函数命令行方式创建查看函数查看函数与查看存储过程类似，只要将object_type修改为’FUCTION’，即使用代码：selectobject_namefromuser_objectswhereobject_type=’FUCTION’;注意，对象类型FUNCTION必须大写。。01任务2函数-02执行函数通过函数查询雇员信息select*fromempwheresal=getsal(7369);02任务2函数-02执行函数SQ语句中调用getsal，显示雇员号为7369的雇员薪水selectgetsal(7369)fromdual;在这里，我们使用了表dual。dual确实是一张表，是一张只有一个字段,一行记录的表。习惯上,我们称之为‘伪表’，因为它不存储主题数据。它的存在,是为了操作上的方便，因为select都是要有特定对象的，表dual用来构成select的语法规则，oracle保证dual里面永远只有一条记录任务2函数-03删除和修改函数命令行方式授予用户对象权限删除函数的操作比较简单，使用dropfunction命令，其后面跟着要删除的函数名称，其语法格式如下：DROPFUNCTION函数名；任务：删除函数getsal修改函数有两种方法，一种是删除函数，然后重新创建；另一种采用CREATEORREPLACEFUNCTION方式重新创建并替换原有的函数，在此不再赘述。任务3触发器010203创建和查看触发器测试触发器修改和删除触发器01任务3触发器-01创建和查看触发器命令行方式创建触发器创建触发器的基本语法CREATE[ORREPLACE]TIGGER触发器名触发时间触发事件ON表名[FOREACHROW]BEGINPL/SQL语句END01任务3触发器-01创建和查看触发器命令行方式创建触发器任务：在emp表上建立触发器。在对emp表插入、更新、删除之前触发，目的是不允许在周末更改表。REATEORREPLACETRIGGERemp_secure BEFOREINSERTORUPDATEORDELETEONscott.empBEGINIF(TO_CHAR(SYSDATE,'DY')='星期日')THENRAISE_APPLICATION_ERROR(-20600,'不能在周末修改表EMP');ENDIF;END;02任务3触发器-01创建和查看触发器命令行方式查看触发器查看触发器与查看存储过程类似，只要将object_type修改为’TIGGER，即使用代码：selectobject_namefromuser_objectswhereobject_type=’TRIGGE’;注意，对象类型TRIGGE必须大写。01任务3触发器-02测试触发器合理的时间内修改表在系统日期为周一到周六的任一时间对emp表进行修改，都能成功。02任务3触发器-02测试触发器不合理的时间内修改表将系统时间改为某个星期日，然后对emp表进行修改，会失败01任务3触发器-03修改和删除触发器修改触发器修改触发器有两种方法，一