函数与存储过程.ppt

《函数与存储过程.ppt》由会员分享，可在线阅读，更多相关《函数与存储过程.ppt（19页珍藏版）》请在三一办公上搜索。

1、第17章 函数与存储过程,对应于其他编程语言中的自定义函数和过程，Oracle也提供了自定义函数和存储过程。Oracle中的函数和存储过程作为对象存储在数据库中，并可以被方便地调用执行，从而在很大程度上实现了PL/SQL代码的复用。本章的主要内容包括：Oracle中的自定义函数；Oracle中的存储过程；包装函数与存储过程程序包。通过本章的学习，读者可以掌握如何创建自定义函数与存储过程，并学习到如何利用包来封装自定义函数与存储过程。,17.1 自定义函数,Oracle中的自定义函数实际是一组PL/SQL的语句的组合。函数的最大特征是必须返回值。通过自定义函数，用户可以极大扩展PL/SQL编程的

2、应用。自定义函数创建之后，其调用方法也非常简单，因此，自定义函数也成为常见的用户操作之一。,17.1.1 创建自定义函数,创建函数应该使用create or replace function命令，其使用格式如下所示。create or replace function 函数名 return 返回值的类型 asbegin函数定义end 函数名;1.创建自定义函数2.在数据字典中查看自定义函数的信息3.调用自定义函数,17.1.2 函数的参数,在示例17-1中所创建和使用的函数，均没有参数。但是对于自定义函数来说，带有参数的情形往往更加常见。当自定义函数带有参数时，只需在声明语句的函数名之后添加小

3、括号，并在小括号内指定参数的数据类型和名称即可，其语法形式如下所示。create or replace function 函数名(数据类型1 参数1,数据类型2 参数2)return 返回值的类型 asbegin函数定义end 函数名;,17.1.3 利用函数的确定性提高数据库效率,对于一个函数来说，其执行步骤和代码是固定不变的。有时，对于一个函数来说，只要传入的参数一定，那么返回值将不会发生任何改变，这样的函数被称为具有确定性的函数。对于函数，尤其是运算和操作步骤较为复杂的函数，每次为其传入参数，并最终返回运算结果，是较为费时的。而函数的确定性，在于可以根据函数名和参数来缓存执行结果。当再次

4、利用相同的参数调用函数时，Oracle将直接获得缓存值，而不会执行函数的实际代码，从而提高工作效率。,17.1.4 典型函数行转列,在实际开发中，行转列是一个非常典型的应用。例如，在表employees中，存储了员工信息。我们可以利用一条SQL语句获得表中所有员工姓名，但要求在前台页面中作为一个字符串出现，并且员工的姓名之间使用逗号进行分隔，那么该问题即为行转列问题。对于该问题，我们可以创建一个函数，该函数接受一个单列查询语句，在函数内部，利用游标将查询语句获得的列值进行串联操作，最终返回串联之后的字符串。,17.2 存储过程,存储过程和自定义函数非常相似，都是预定义的、可重用的PL/SQL代

5、码。但是函数更倾向于处理数据运算，并最终返回处理结果；而存储过程则更倾向于数据库操作，并不返回值。本小节通过示例讲述存储过程的创建和使用。,17.2.1 创建存储过程,创建存储过程应该使用create or replace procedure命令，其格式为，create or replace procedure 存储过程名称 asbegin存储过程定义end 存储过程名称;其中，存储过程定义是一组PL/SQL代码。1.创建存储过程2.查看存储过程信息3.执行存储过程,17.2.2“只进不出的参数”in参数,与函数相比，存储过程真正的复杂之处在于参数。存储过程的参数包括三种：in参数、out参数

6、和in out参数。本小节将讲述in参数的使用。in参数，顾名思义，就是“只进不出”的参数。这里的“进”是指，向存储过程传递参数；“出”是指，获得存储过程内部处理的值。也就是说，一旦某个参数被声明为in参数，那么该参数值可以在存储过程内部被访问，但是并不能被修改。,17.2.3“只出不进的参数”out参数,从没有返回值的角度来看，存储过程似乎并不如函数强大。其实不然，存储过程同样可以通过out参数将内部的处理结果传递给外部调用程序。与in参数相反，out参数是只出不进的参数。,17.2.4“可进可出的参数”in out参数,in参数和out参数实际具有单向性，即要么用于传入参数值，要么用于输出

7、存储过程的运算结果。除此之外，存储过程还可以使用in out参数。in out参数既可以作为输入也可以作为输出。【示例17-11】in out参数的一个典型应用场景为交换两个变量的值。SQL create or replace procedure swap(first_value in out number,second_value in out number)as 2 begin 3 first_value:=first_value+second_value;4 second_value:=first_value-second_value;5 first_value:=first_value

8、-second_value;6 end swap;7/Procedure created,17.2.5 调试存储过程,调试程序和代码在开发中不可避免。相对于函数，存储过程往往会复杂的多。当存储过程非常复杂时，往往需要调试存储过程。本小节将简要介绍如何在PL/SQL Developer中调试存储过程。,17.2.5 调试存储过程,17.3 程序包,对于大多数应用来说，程序包并非必需。但是有时候，应用程序所创建的函数和存储过程过多，那么有效管理这些函数和存储过程就显得非常必要。程序包正是解决这一问题的优秀策略。程序包(package)由两部分构成：规范（specification）和主体（body

9、）。本节将介绍如何利用程序包来管理函数和存储过程。,程序包的规范,规范包含了所有需要实现的函数与存储过程。规范中只是声明了函数与存储过程，而不对它们进行具体定义。规范类似于面向对象编程中的接口，只是标识了主体中必须要实现的函数与存储过程的集合。1.创建规范2.在数据字典中查看规范的信息,17.3.2 程序包的主体,单纯定义程序所需的规范是毫无意义的。规范需要与主体一一对应。在规范中声明的所有函数和存储过程，必须在主体中进行具体定义。相形之下，主体非常类似于与面向对象编程中的类，类一旦实现某个接口，那么必须实现其中所有方法。主体与规范之间的一一对应是通过相同的包名来实现的。在示例17-14中，我

10、们创建了规范pkg_students，那么为其创建相应的主体，则仍需使用相同的名称。create or replace package body pkg_students asend pkg_students;,17.3.3 调用程序包中的函数/存储过程,利用程序包可以使函数和存储过程的管理层次更加明晰。但是，处于程序包中的函数和存储过程在调用时，必须要添加程序包名，即pkg_name.subprogram_name。【示例17-14】为了调用程序包pkg_students中的自定义函数getStudentsName，可以利用如下SQL语句。SQL select pkg_students.ge

11、tStudentsName from dual;,17.4 本章实例,【示例17-15】在实际开发中，除了row2column函数，还有另外一个比较常用的自定义函数判断某个变量是否为有效的日期格式。SQL create or replace function is_date(param varchar2)return varchar2 is 2 d date;3 begin 4 d:=to_date(nvl(param,),yyyy-mm-dd hh24:mi:ss);5 return Y;6 7 exception 8 when others then 9 return N;10 end;11/Function created,17.5 本章小结,本章简要讲述了Oracle中的自定义函数与存储过程。其实，自定义函数和存储过程与Oracle内置函数及存储过程，在调用时完全相同，用户完全可以将它们同等对待。利用程序包来管理函数和存储过程是一个好的习惯，当然，用户也应当结合实际情况进行划分，例如，将同一功能模块下的函数与存储过程划分为同一个程序包。,