《第17章函数与存储过程优秀课件.ppt》由会员分享，可在线阅读，更多相关《第17章函数与存储过程优秀课件.ppt（19页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第17章章 函数与存函数与存储过程程第1页，本讲稿共19页17.1 17.1 自定义函数自定义函数Oracle中的自定义函数实际是一组中的自定义函数实际是一组PL/SQL的语句的组的语句的组合。函数的最大特征是必须返回值。通过自定义函数，用户合。函数的最大特征是必须返回值。通过自定义函数，用户可以极大扩展可以极大扩展PL/SQL编程的应用。自定义函数创建之后，编程的应用。自定义函数创建之后，其调用方法也非常简单，因此，自定义函数也成为常见的用其调用方法也非常简单，因此，自定义函数也成为常见的用户操作之一。户操作之一。第2页，本讲稿共19页17.1.1 17.1.1 创建自定义函数创建自定义函
2、数创建函数应该使用创建函数应该使用create or replace function命令，命令，其使用格式如下所示。其使用格式如下所示。create or replace function 函数名函数名 return 返回值的返回值的类型类型 asbegin函数定义函数定义end 函数名函数名;1.创建自定义函数创建自定义函数2.在数据字典中查看自定义函数的信息在数据字典中查看自定义函数的信息3.调用自定义函数调用自定义函数第3页，本讲稿共19页17.1.2 17.1.2 函数的参数函数的参数在示例在示例17-1中所创建和使用的函数，均没有参数。但是中所创建和使用的函数，均没有参数。但是对于
3、自定义函数来说，带有参数的情形往往更加常见。当自对于自定义函数来说，带有参数的情形往往更加常见。当自定义函数带有参数时，只需在声明语句的函数名之后添加小定义函数带有参数时，只需在声明语句的函数名之后添加小括号，并在小括号内指定参数的数据类型和名称即可，其语括号，并在小括号内指定参数的数据类型和名称即可，其语法形式如下所示。法形式如下所示。create or replace function 函数名函数名(数据类型数据类型1 参数参数1,数据类型数据类型2 参数参数2)return 返回值的类型返回值的类型 asbegin函数定义函数定义end 函数名函数名;第4页，本讲稿共19页17.1.3
4、17.1.3 利用函数的确定性提高数据库效率利用函数的确定性提高数据库效率对于一个函数来说，其执行步骤和代码是固定不变的。有对于一个函数来说，其执行步骤和代码是固定不变的。有时，对于一个函数来说，只要传入的参数一定，那么返回值将时，对于一个函数来说，只要传入的参数一定，那么返回值将不会发生任何改变，这样的函数被称为具有确定性的函数。不会发生任何改变，这样的函数被称为具有确定性的函数。对于函数，尤其是运算和操作步骤较为复杂的函数，每次对于函数，尤其是运算和操作步骤较为复杂的函数，每次为其传入参数，并最终返回运算结果，是较为费时的。而函数为其传入参数，并最终返回运算结果，是较为费时的。而函数的确定
5、性，在于可以根据函数名和参数来缓存执行结果。当再的确定性，在于可以根据函数名和参数来缓存执行结果。当再次利用相同的参数调用函数时，次利用相同的参数调用函数时，Oracle将直接获得缓存值，而将直接获得缓存值，而不会执行函数的实际代码，从而提高工作效率。不会执行函数的实际代码，从而提高工作效率。第5页，本讲稿共19页17.1.4 17.1.4 典型函数典型函数行转列行转列在实际开发中，行转列是一个非常典型的应用。例如，在实际开发中，行转列是一个非常典型的应用。例如，在表在表employees中，存储了员工信息。我们可以利用一条中，存储了员工信息。我们可以利用一条SQL语句获得表中所有员工姓名，但
6、要求在前台页面中作为语句获得表中所有员工姓名，但要求在前台页面中作为一个字符串出现，并且员工的姓名之间使用逗号进行分隔，一个字符串出现，并且员工的姓名之间使用逗号进行分隔，那么该问题即为行转列问题。对于该问题，我们可以创建一那么该问题即为行转列问题。对于该问题，我们可以创建一个函数，该函数接受一个单列查询语句，在函数内部，利用个函数，该函数接受一个单列查询语句，在函数内部，利用游标将查询语句获得的列值进行串联操作，最终返回串联之游标将查询语句获得的列值进行串联操作，最终返回串联之后的字符串。后的字符串。第6页，本讲稿共19页17.2 17.2 存储过程存储过程存储过程和自定义函数非常相似，都是
7、预定义的、可重存储过程和自定义函数非常相似，都是预定义的、可重用的用的PL/SQL代码。但是函数更倾向于处理数据运算，并最代码。但是函数更倾向于处理数据运算，并最终返回处理结果；而存储过程则更倾向于数据库操作，并不终返回处理结果；而存储过程则更倾向于数据库操作，并不返回值。本小节通过示例讲述存储过程的创建和使用。返回值。本小节通过示例讲述存储过程的创建和使用。第7页，本讲稿共19页17.2.1 17.2.1 创建存储过程创建存储过程创建存储过程应该使用创建存储过程应该使用create or replace procedure命令，其格式为，命令，其格式为，create or replace p
8、rocedure 存储过程名称存储过程名称 asbegin存储过程定义存储过程定义end 存储过程名称存储过程名称;其中，存储过程定义是一组其中，存储过程定义是一组PL/SQL代码。代码。1.创建存储过程创建存储过程2.查看存储过程信息查看存储过程信息3.执行存储过程执行存储过程第8页，本讲稿共19页17.2.2 17.2.2 “只进不出的参数只进不出的参数”inin参数参数与函数相比，存储过程真正的复杂之处在于参数。存储与函数相比，存储过程真正的复杂之处在于参数。存储过程的参数包括三种：过程的参数包括三种：in参数、参数、out参数和参数和in out参数。本小参数。本小节将讲述节将讲述in
9、参数的使用。参数的使用。in参数，顾名思义，就是参数，顾名思义，就是“只进不出只进不出”的参数。这里的的参数。这里的“进进”是指，向存储过程传递参数；是指，向存储过程传递参数；“出出”是指，获得存储是指，获得存储过程内部处理的值。也就是说，一旦某个参数被声明为过程内部处理的值。也就是说，一旦某个参数被声明为in参参数，那么该参数值可以在存储过程内部被访问，但是并不能数，那么该参数值可以在存储过程内部被访问，但是并不能被修改。被修改。第9页，本讲稿共19页17.2.3 17.2.3 “只出不进的参数只出不进的参数”outout参数参数从没有返回值的角度来看，存储过程似乎并不如函数强从没有返回值的
10、角度来看，存储过程似乎并不如函数强大。其实不然，存储过程同样可以通过大。其实不然，存储过程同样可以通过out参数将内部的处参数将内部的处理结果传递给外部调用程序。与理结果传递给外部调用程序。与in参数相反，参数相反，out参数是只参数是只出不进的参数。出不进的参数。第10页，本讲稿共19页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
12、first_value:=first_value-second_value;6 end swap;7 /Procedure created第11页，本讲稿共19页17.2.5 17.2.5 调试存储过程调试存储过程调试程序和代码在开发中不可避免。相对于函数，存储调试程序和代码在开发中不可避免。相对于函数，存储过程往往会复杂的多。当存储过程非常复杂时，往往需要调过程往往会复杂的多。当存储过程非常复杂时，往往需要调试存储过程。本小节将简要介绍如何在试存储过程。本小节将简要介绍如何在PL/SQL Developer中调试存储过程。中调试存储过程。第12页，本讲稿共19页17.2.5 17.2.5 调
13、试存储过程调试存储过程第13页，本讲稿共19页17.3 17.3 程序包程序包对于大多数应用来说，程序包并非必需。但是有时候，应对于大多数应用来说，程序包并非必需。但是有时候，应用程序所创建的函数和存储过程过多，那么有效管理这些函数用程序所创建的函数和存储过程过多，那么有效管理这些函数和存储过程就显得非常必要。程序包正是解决这一问题的优秀和存储过程就显得非常必要。程序包正是解决这一问题的优秀策略。策略。程序包程序包(package)由两部分构成：规范（由两部分构成：规范（specification）和主体（和主体（body）。本节将介绍如何利用程序包来管理函数和存）。本节将介绍如何利用程序包来
14、管理函数和存储过程。储过程。第14页，本讲稿共19页17.3.117.3.1程序包的规范程序包的规范规范包含了所有需要实现的函数与存储过程。规范中只规范包含了所有需要实现的函数与存储过程。规范中只是声明了函数与存储过程，而不对它们进行具体定义。规范是声明了函数与存储过程，而不对它们进行具体定义。规范类似于面向对象编程中的接口，只是标识了主体中必须要实类似于面向对象编程中的接口，只是标识了主体中必须要实现的函数与存储过程的集合。现的函数与存储过程的集合。1.创建规范创建规范2.在数据字典中查看规范的信息在数据字典中查看规范的信息第15页，本讲稿共19页17.3.2 17.3.2 程序包的主体程序
15、包的主体单纯定义程序所需的规范是毫无意义的。规范需要与主单纯定义程序所需的规范是毫无意义的。规范需要与主体一一对应。在规范中声明的所有函数和存储过程，必须在体一一对应。在规范中声明的所有函数和存储过程，必须在主体中进行具体定义。相形之下，主体非常类似于与面向对主体中进行具体定义。相形之下，主体非常类似于与面向对象编程中的类，类一旦实现某个接口，那么必须实现其中所象编程中的类，类一旦实现某个接口，那么必须实现其中所有方法。有方法。主体与规范之间的一一对应是通过相同的包名来实现的。主体与规范之间的一一对应是通过相同的包名来实现的。在示例在示例17-14中，我们创建了规范中，我们创建了规范pkg_s
16、tudents，那么为其，那么为其创建相应的主体，则仍需使用相同的名称。创建相应的主体，则仍需使用相同的名称。create or replace package body pkg_students asend pkg_students;第16页，本讲稿共19页17.3.3 17.3.3 调用程序包中的函数调用程序包中的函数/存储过程存储过程利用程序包可以使函数和存储过程的管理层次更加明晰。利用程序包可以使函数和存储过程的管理层次更加明晰。但是，处于程序包中的函数和存储过程在调用时，必须要添但是，处于程序包中的函数和存储过程在调用时，必须要添加程序包名，即加程序包名，即pkg_name.subp
17、rogram_name。【示例【示例17-14】为了调用程序包】为了调用程序包pkg_students中的自中的自定义函数定义函数getStudentsName，可以利用如下，可以利用如下SQL语句。语句。SQL select pkg_students.getStudentsName from dual;第17页，本讲稿共19页17.4 17.4 本章实例本章实例【示例【示例17-15】在实际开发中，除了】在实际开发中，除了row2column函数，函数，还有另外一个比较常用的自定义函数还有另外一个比较常用的自定义函数判断某个变量是否为判断某个变量是否为有效的日期格式。有效的日期格式。SQL
18、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第18页，本讲稿共19页17.5 17.5 本章小结本章小结本章简要讲述了本章简要讲述了Oracle中的自定义函数与存储过程。其中的自定义函数与存储过程。其实，自定义函数和存储过程与实，自定义函数和存储过程与Oracle内置函数及存储过程，内置函数及存储过程，在调用时完全相同，用户完全可以将它们同等对待。利用程序在调用时完全相同，用户完全可以将它们同等对待。利用程序包来管理函数和存储过程是一个好的习惯，当然，用户也应当包来管理函数和存储过程是一个好的习惯，当然，用户也应当结合实际情况进行划分，例如，将同一功能模块下的函数与存结合实际情况进行划分，例如，将同一功能模块下的函数与存储过程划分为同一个程序包。储过程划分为同一个程序包。第19页，本讲稿共19页