《Oracle数据库管理 开发与实践 教学课件 ppt 作者 杨永健 刘尚毅 第7章 过程 函数 触发器和包》由会员分享，可在线阅读，更多相关《Oracle数据库管理 开发与实践 教学课件 ppt 作者 杨永健 刘尚毅 第7章 过程 函数 触发器和包（33页珍藏版）》请在金锄头文库上搜索。
1、,本章要求：,第7章 过程、函数、触发器和包,掌握存储过程的创建及执行 掌握函数的定义及调用 掌握触发器的定义及执行 了解程序包的定义及使用,主要内容,1.存储过程 2.函数 3.触发器 4.程序包 5.综合实例创建带参数的存储过程,第7章 过程、函数、触发器和包,7.1.1 创建存储过程 7.1.2 存储过程的参数 7.1.3 IN参数的默认值,7.1 存储过程,创建一个存储过程与编写一个普通的PL/SQL程序块有很多相似的地方，比如，也包括声明部分、执行部分和异常处理三部分。但这二者之间的实现细节还是有很多差别的，比如，创建存储过程需要使用PROCEDURE关键字，在关键字后面就是过程名和
2、参数列表；创建存储过程不需要使用DECLARE关键字，转而是使用CREATE或REPLACE关键字，其基本语法格式如下： create or replace procedure pro_name (parameter1,parameter2) is|as begin plsql_sentences; exception dowith _ sentences; end pro_name; 参数说明如下： pro_name：存储过程的名称，如果数据库中已经存在了此名称，则可以指定“or replace”关键字，这样新的存储过程将覆盖掉原来的存储过程。 parameter1：存储过程的参数，若是输入
3、参数，则需要在其后指定“in”关键字；若是输出参数，则需要在其后面指定“out”关键字。在in或out关键字的后面是参数的数据类型，但不能指定该类型的长度。 plsql_sentences：PL/SQL语句，它是存储过程功能实现的主体。 dowith _ sentences：异常处理语句，也是PL/SQL语句，这是一个可选项。 注意：上面语法中的parameter1是存储过程被调用/执行时用到的参数，而不是存储过程内定义的内部变量，内部变量要在“is|as”关键字后面定义，并使用分号（;）结束。,7.1.1 创建存储过程,前面所创建的存储过程都是简单的存储过程，它们都没有涉及到参数。Oracl
4、e为了增强存储过程的灵活性，可以实现向存储过程传入参数。参数是一种向程序单元输入和输出数据的机制，存储过程可以接受多个参数，参数模式包括IN、OUT和IN OUT三种，下面分别进行讲解。 1IN模式参数 这是一种输入类型的参数，参数值由调用方传入，并且只能被存储过程读取。这种参数模式是最常用的，也是默认的参数模式，关键字in位于参数名称之后。例如，就下面将声明一个带有IN模式的存储过程。 【例7-5】 创建一个存储过程，并定义3个in模式的变量，然后将这3个变量的值插入到dept表中，代码及运行结果如下。 SQL create or replace procedure insert_dept(
5、 2 num_deptno in number, -定义in模式的变量，它存储部门编号 3 var_ename in varchar2, -定义in模式的变量，它存储部门名称 4 var_loc in varchar2) is 5 begin 6 insert into dept 7 values(num_deptno,var_ename,var_loc); -向dept表中插入记录 8 commit; -提交数据库 9 end insert_dept; 10 / 过程已创建。,7.1.2 存储过程的参数,这样上面的代码就成功创建了一个存储过程，需要注意的是：参数的类型不能指定长度。在调用或执
6、行这种in模式的存储过程时，用户需要向存储过程中传递若干参数值，以保证执行部分（即begin部分）有具体的数值参与数据操作。向存储过程传入参数可以有如下3种方式： （1）指定名称传递 指定名称传递是指在向存储过程传递参数时需要指定参数名称，即参数名称在左侧，中间是赋值符号“=”，右侧是参数值，其语法格式如下： pro_name(parameter1=value1,parameter2=value2) 参数说明如下： parameter1：参数名称，在传递参数值时，这个参数名称与存储过程中定义的参数顺序无关。 value1：参数值，在它的左侧不是常规的赋值符号“=”，而是一种新的赋值符号“=”，
7、需要注意参数值的类型要与参数的定义类型兼容。 接下来以上面的insert_dept存储过程为例，使用“指定名称传递”的方式向其中传递参数。,前面的IN参数的值都是在调用存储过程时传入的，实际上，Oracle支持在声明IN参数的同时给其初始化默认值，这样在存储过程调用时，如果没有向IN参数传入值，则存储过程可以使用默认值进行操作，下面来看一个例子。 【例7-16】 创建一个存储过程，定义三个IN参数，并将其中的两个参数设置初始默认值，然后将这个三个IN参数的值插入到dept表中，代码如下。 SQL create or replace procedure insert_dept( 2 num_de
8、ptno in number, -定义存储部门编号的IN参数 3 var_dname in varchar2 default 综合部, -定义存储部门名称的IN参数，并初始默认值 4 var_loc in varchar2 default 北京) is 5 begin 6 insert into dept values(num_deptno,var_dname,var_loc); -插入一条记录 7 end; 8 / 过程已创建。 在上面的存储过程中，IN参数var_dname和var_loc都有默认值，所以在调用insert_dept存储过程时，可以不向这两个参数传入值，而是使用其默认值（当
9、然也可以传入值）。那么用户可能会出现这样一种困惑，当给一些带有默认值的参数传入值，而对另一些带默认值的参数不传值，并且传值的顺序不固定，该怎么办呢？对于这种情况，由于顺序的不固定，建议使用“指定名称传递”的方式传值，这样程序就不会出现混乱，下面来看一个实例。,7.1.3 IN参数的默认值,【例7-17】 在PL/SQL块中调用insert_dept存储过程，并且只向该存储过程传入两个参数值，代码如下。 SQL declare 2 row_dept dept%rowtype; -定义行变量，与dept表的一行类型相同 3 begin 4 insert_dept(57,var_loc=太原); -
10、调用insert_dept存储过程，传入参数 5 commit; -提交数据库 6 select * into row_dept from dept where deptno=57; -建新插入的记录 7 dbms_output.put_line(部门名称是：|row_dept.dname|,位置是：|row_dept.loc|); 8 end; 9 / 本例运行结果如图7-5所示。 图7-5 调用带有默认值的存储过程 在上面的代码中，存储过程insert_dept有3个IN参数，这里只传入两个参数（num_deptno和var_loc）的值，而var_dname参数的值使用默认值“综合部”。
11、,7.2 函数,7.2.1 创建函数 7.2.2 调用函数 7.2.3 删除函数,7.2.1 创建函数,函数的创建语法与存储过程比较类似，它也是一种存储在数据库中的命名程序块，函数可以接受零或多个输入参数，并且函数必须有返回值（而这一点存储过程是没有的），其定义语法格式如下： create or replace function fun_name(parameter1,parameter2) return data_type is inner_variable begin plsql_ sentence; exception dowith _ sentences; end fun_name;
12、参数说明如下： fun_name：函数名称，如果数据库中已经存在了此名称，则可以指定“or replace”关键字，这样新的函数将覆盖掉原来的函数。 parameter1：函数的参数，这是个可选项，因为函数可以没有参数。 data_type：函数的返回值类型，这是个必选项。在返回值类型的前面要使用return关键字来标明。 inner_variable：函数的内部变量，它有别于函数的参数，这是个可选项。 plsql_ sentence：PL/SQL语句，它是函数主要功能的实现部分，也就是函数的主体。 dowith _ sentences：异常处理代码，也是PL/SQL语句，这是一个可选项 由于
13、函数有返回值，所以在函数主体部分（即begin部分）必须使用return语句返回函数值，并且要求返回值的类型要与函数声明时的返回值类型（即data_type）相同。,根据上面的语法分析，下面来创建一个函数。 【例7-18】 定义一个函数，用于计算emp表中指定某个部门的平均工资，代码如下。 SQL create or replace function get_avg_pay(num_deptno number) return number is -创建一个函数，该函数实现计算某个部门的平均工资，传入部门编号参数 2 num_avg_pay number; -保存平均工资的内部变量 3 begi
14、n 4 select avg(sal) into num_avg_pay from emp where deptno=num_deptno; -某个部门的平均工资 5 return(round(num_avg_pay,2); -返回平均工资 6 exception 7 when no_data_found then -若此部门编号不存在 8 dbms_output.put_line(该部门编号不存在); 9 return(0); -返回平均工资为0 10 end; 11 / 函数已创建。,由于函数有返回值，所以在调用函数时，必须使用一个变量来保存函数的返回值，这样函数和这个变量就组成了一个赋值
15、表达式。以上面的get_avg_pay函数为例，看看如何调用函数。 【例7-19】 调用函数get_avg_pay，计算部门编号为10的雇员平均工资并输出，代码如下。 SQL set serveroutput on SQL declare 2 avg_pay number; -定义变量，存储函数返回值 3 begin 4 avg_pay:=get_avg_pay(10); -调用函数，并获取返回值 5 dbms_output.put_line(平均工资是：|avg_pay);-输出返回值，即员工平均工资 6 end; 7 / 本例运行结果如图7-6所示。 图7-6 使用函数计算平均工资,7.2
16、.2 调用函数,删除函数的操作比较简单，使用drop function命令，其后面跟着要删除的函数名称，其语法格式如下： drop function fun_name; 参数fucn_name表示要删除的函数名称。下面以删除get_avg_pay函数为例，来了解如何删除函数。 【例7-20】 使用drop function命令删除get_avg_pay函数，代码及运行结果如下。 SQL drop function get_avg_pay; 函数已删除。,7.2.3 删除函数,7.3 触发器,7.3.1 触发器简介 7.3.2 语句级触发器 7.3.3 行级别触发器 7.3.4 替换触发器 7.3.5 用户事件触发器,在触发器中有一个不得不提的概念触发事件，触发
