第17章函数与存储过程精选文档.ppt

《第17章函数与存储过程精选文档.ppt》由会员分享，可在线阅读，更多相关《第17章函数与存储过程精选文档.ppt（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第17章章 函数与存函数与存储过程程本讲稿第一页，共十九页17.1 17.1 自定义函数自定义函数Oracle中的自定义函数实际是一组中的自定义函数实际是一组PL/SQL的语句的组的语句的组合。函数的最大特征是必须返回值。通过自定义函数，用户合。函数的最大特征是必须返回值。通过自定义函数，用户可以极大扩展可以极大扩展PL/SQL编程的应用。自定义函数创建之后，编程的应用。自定义函数创建之后，其调用方法也非常简单，因此，自定义函数也成为常见的用其调用方法也非常简单，因此，自定义函数也成为常见的用户操作之一。户操作之一。本讲稿第二页，共十九页17.1.1 17.1.1 创建自定义函数创建自定义函

2、数创建函数应该使用创建函数应该使用create or replace function命令，命令，其使用格式如下所示。其使用格式如下所示。create or replace function 函数名函数名 return 返回值的返回值的类型类型 asbegin函数定义函数定义end 函数名函数名;1.创建自定义函数创建自定义函数2.在数据字典中查看自定义函数的信息在数据字典中查看自定义函数的信息3.调用自定义函数调用自定义函数本讲稿第三页，共十九页17.1.2 17.1.2 函数的参数函数的参数在示例在示例17-1中所创建和使用的函数，均没有参数。但中所创建和使用的函数，均没有参数。但是对于自

3、定义函数来说，带有参数的情形往往更加常见。当是对于自定义函数来说，带有参数的情形往往更加常见。当自定义函数带有参数时，只需在声明语句的函数名之后添加自定义函数带有参数时，只需在声明语句的函数名之后添加小括号，并在小括号内指定参数的数据类型和名称即可，其小括号，并在小括号内指定参数的数据类型和名称即可，其语法形式如下所示。语法形式如下所示。create or replace function 函数名函数名(数据类型数据类型1 参数参数1,数据类型数据类型2 参数参数2)return 返回值的类型返回值的类型 asbegin函数定义函数定义end 函数名函数名;本讲稿第四页，共十九页17.1.3

4、17.1.3 利用函数的确定性提高数据库效率利用函数的确定性提高数据库效率对于一个函数来说，其执行步骤和代码是固定不变的。有对于一个函数来说，其执行步骤和代码是固定不变的。有时，对于一个函数来说，只要传入的参数一定，那么返回值将时，对于一个函数来说，只要传入的参数一定，那么返回值将不会发生任何改变，这样的函数被称为具有确定性的函数。不会发生任何改变，这样的函数被称为具有确定性的函数。对于函数，尤其是运算和操作步骤较为复杂的函数，每次对于函数，尤其是运算和操作步骤较为复杂的函数，每次为其传入参数，并最终返回运算结果，是较为费时的。而函数为其传入参数，并最终返回运算结果，是较为费时的。而函数的确定

5、性，在于可以根据函数名和参数来缓存执行结果。当再的确定性，在于可以根据函数名和参数来缓存执行结果。当再次利用相同的参数调用函数时，次利用相同的参数调用函数时，Oracle将直接获得缓存值，而将直接获得缓存值，而不会执行函数的实际代码，从而提高工作效率。不会执行函数的实际代码，从而提高工作效率。本讲稿第五页，共十九页17.1.4 17.1.4 典型函数典型函数行转列行转列在实际开发中，行转列是一个非常典型的应用。例如，在实际开发中，行转列是一个非常典型的应用。例如，在表在表employees中，存储了员工信息。我们可以利用一条中，存储了员工信息。我们可以利用一条SQL语句获得表中所有员工姓名，但

6、要求在前台页面中作为语句获得表中所有员工姓名，但要求在前台页面中作为一个字符串出现，并且员工的姓名之间使用逗号进行分隔，一个字符串出现，并且员工的姓名之间使用逗号进行分隔，那么该问题即为行转列问题。对于该问题，我们可以创建一那么该问题即为行转列问题。对于该问题，我们可以创建一个函数，该函数接受一个单列查询语句，在函数内部，利用个函数，该函数接受一个单列查询语句，在函数内部，利用游标将查询语句获得的列值进行串联操作，最终返回串联之游标将查询语句获得的列值进行串联操作，最终返回串联之后的字符串。后的字符串。本讲稿第六页，共十九页17.2 17.2 存储过程存储过程存储过程和自定义函数非常相似，都是

7、预定义的、可存储过程和自定义函数非常相似，都是预定义的、可重用的重用的PL/SQL代码。但是函数更倾向于处理数据运算，并代码。但是函数更倾向于处理数据运算，并最终返回处理结果；而存储过程则更倾向于数据库操作，并最终返回处理结果；而存储过程则更倾向于数据库操作，并不返回值。本小节通过示例讲述存储过程的创建和使用。不返回值。本小节通过示例讲述存储过程的创建和使用。本讲稿第七页，共十九页17.2.1 17.2.1 创建存储过程创建存储过程创建存储过程应该使用创建存储过程应该使用create or replace procedure命令，其格式为，命令，其格式为，create or replace p

8、rocedure 存储过程名称存储过程名称 asbegin存储过程定义存储过程定义end 存储过程名称存储过程名称;其中，存储过程定义是一组其中，存储过程定义是一组PL/SQL代码。代码。1.创建存储过程创建存储过程2.查看存储过程信息查看存储过程信息3.执行存储过程执行存储过程本讲稿第八页，共十九页17.2.2 17.2.2 “只进不出的参数只进不出的参数”inin参数参数与函数相比，存储过程真正的复杂之处在于参数。存与函数相比，存储过程真正的复杂之处在于参数。存储过程的参数包括三种：储过程的参数包括三种：in参数、参数、out参数和参数和in out参数。本参数。本小节将讲述小节将讲述in

9、参数的使用。参数的使用。in参数，顾名思义，就是参数，顾名思义，就是“只进不出只进不出”的参数。这里的的参数。这里的“进进”是指，向存储过程传递参数；是指，向存储过程传递参数；“出出”是指，获得存储是指，获得存储过程内部处理的值。也就是说，一旦某个参数被声明为过程内部处理的值。也就是说，一旦某个参数被声明为in参参数，那么该参数值可以在存储过程内部被访问，但是并不能数，那么该参数值可以在存储过程内部被访问，但是并不能被修改。被修改。本讲稿第九页，共十九页17.2.3 17.2.3 “只出不进的参数只出不进的参数”outout参数参数从没有返回值的角度来看，存储过程似乎并不如函数从没有返回值的角

10、度来看，存储过程似乎并不如函数强大。其实不然，存储过程同样可以通过强大。其实不然，存储过程同样可以通过out参数将内部的参数将内部的处理结果传递给外部调用程序。与处理结果传递给外部调用程序。与in参数相反，参数相反，out参数是参数是只出不进的参数。只出不进的参数。本讲稿第十页，共十九页17.2.4 17.2.4 “可进可出的参数可进可出的参数”in outin out参数参数in参数和参数和out参数实际具有单向性，即要么用于传入参参数实际具有单向性，即要么用于传入参数值，要么用于输出存储过程的运算结果。除此之外，存储数值，要么用于输出存储过程的运算结果。除此之外，存储过程还可以使用过程还可

11、以使用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 f

12、irst_value:=first_value-second_value;6 end swap;7 /Procedure created本讲稿第十一页，共十九页17.2.5 17.2.5 调试存储过程调试存储过程调试程序和代码在开发中不可避免。相对于函数，存调试程序和代码在开发中不可避免。相对于函数，存储过程往往会复杂的多。当存储过程非常复杂时，往往需要储过程往往会复杂的多。当存储过程非常复杂时，往往需要调试存储过程。本小节将简要介绍如何在调试存储过程。本小节将简要介绍如何在PL/SQL Developer中调试存储过程。中调试存储过程。本讲稿第十二页，共十九页17.2.5 17.2.5 调试

13、存储过程调试存储过程本讲稿第十三页，共十九页17.3 17.3 程序包程序包对于大多数应用来说，程序包并非必需。但是有时候，应对于大多数应用来说，程序包并非必需。但是有时候，应用程序所创建的函数和存储过程过多，那么有效管理这些函数用程序所创建的函数和存储过程过多，那么有效管理这些函数和存储过程就显得非常必要。程序包正是解决这一问题的优秀和存储过程就显得非常必要。程序包正是解决这一问题的优秀策略。策略。程序包程序包(package)由两部分构成：规范（由两部分构成：规范（specification）和主体（和主体（body）。本节将介绍如何利用程序包来管理函数和存）。本节将介绍如何利用程序包来管

14、理函数和存储过程。储过程。本讲稿第十四页，共十九页17.3.117.3.1程序包的规范程序包的规范规范包含了所有需要实现的函数与存储过程。规范中规范包含了所有需要实现的函数与存储过程。规范中只是声明了函数与存储过程，而不对它们进行具体定义。规只是声明了函数与存储过程，而不对它们进行具体定义。规范类似于面向对象编程中的接口，只是标识了主体中必须要范类似于面向对象编程中的接口，只是标识了主体中必须要实现的函数与存储过程的集合。实现的函数与存储过程的集合。1.创建规范创建规范2.在数据字典中查看规范的信息在数据字典中查看规范的信息本讲稿第十五页，共十九页17.3.2 17.3.2 程序包的主体程序包

15、的主体单纯定义程序所需的规范是毫无意义的。规范需要与单纯定义程序所需的规范是毫无意义的。规范需要与主体一一对应。在规范中声明的所有函数和存储过程，必须主体一一对应。在规范中声明的所有函数和存储过程，必须在主体中进行具体定义。相形之下，主体非常类似于与面向在主体中进行具体定义。相形之下，主体非常类似于与面向对象编程中的类，类一旦实现某个接口，那么必须实现其中对象编程中的类，类一旦实现某个接口，那么必须实现其中所有方法。所有方法。主体与规范之间的一一对应是通过相同的包名来实现主体与规范之间的一一对应是通过相同的包名来实现的。在示例的。在示例17-14中，我们创建了规范中，我们创建了规范pkg_st

16、udents，那么，那么为其创建相应的主体，则仍需使用相同的名称。为其创建相应的主体，则仍需使用相同的名称。create or replace package body pkg_students asend pkg_students;本讲稿第十六页，共十九页17.3.3 17.3.3 调用程序包中的函数调用程序包中的函数/存储过程存储过程利用程序包可以使函数和存储过程的管理层次更加明利用程序包可以使函数和存储过程的管理层次更加明晰。但是，处于程序包中的函数和存储过程在调用时，必须晰。但是，处于程序包中的函数和存储过程在调用时，必须要添加程序包名，即要添加程序包名，即pkg_name.subpr

17、ogram_name。【示例【示例17-14】为了调用程序包】为了调用程序包pkg_students中的自中的自定义函数定义函数getStudentsName，可以利用如下，可以利用如下SQL语句。语句。SQL select pkg_students.getStudentsName from dual;本讲稿第十七页，共十九页17.4 17.4 本章实例本章实例【示例【示例17-15】在实际开发中，除了】在实际开发中，除了row2column函数，函数，还有另外一个比较常用的自定义函数还有另外一个比较常用的自定义函数判断某个变量是否为判断某个变量是否为有效的日期格式。有效的日期格式。SQL c

18、reate 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 17.5 本章小结本章小结本章简要讲述了本章简要讲述了Oracle中的自定义函数与存储过程。其中的自定义函数与存储过程。其实，自定义函数和存储过程与实，自定义函数和存储过程与Oracle内置函数及存储过程，内置函数及存储过程，在调用时完全相同，用户完全可以将它们同等对待。利用程序在调用时完全相同，用户完全可以将它们同等对待。利用程序包来管理函数和存储过程是一个好的习惯，当然，用户也应当包来管理函数和存储过程是一个好的习惯，当然，用户也应当结合实际情况进行划分，例如，将同一功能模块下的函数与存结合实际情况进行划分，例如，将同一功能模块下的函数与存储过程划分为同一个程序包。储过程划分为同一个程序包。本讲稿第十九页，共十九页