第5章子程序和程序包教程.doc

第5章 子程序和程序包 子程序是已命名的PL/SQL块，它们存储和在数据库中，可以为它们指定参数，可以从任何数据库客户端和应用程序中调用它们。命名的PL/SQL程序包括存储过程和函数，程序包是存储过程和函数的集合。 一、子程序 子程序具有声明部分、可执行部分和可选的异常处理部分。声明部分包含类型、游标、常量、变量、嵌套子程序的声明。可执行部分包含赋值、控制执行过程和操纵数据库的语句。异常处理部分包含异常处理程序。 子程序的优点。 （1）模块化；（2）可重用性；（3）可维护性；（4）安全性； 子程序有两种类型： （1）过程；（2）函数； 一般使用过程来完成某种操作，使用函数完成操作并返回值。 1、过程。 Oracle的过程很类似sql server中的存储过程。他可以被赋予参数，存储在数据库中，由其他应用程序或PL/SQL调用。 创建过程的格式如下： 例一 2、执行过程 在sql提示符下，使用execute语句来执行过程。语法如下。 3、调用程序是通过参数向被调用的过程传递值的。参数传递的模式有3种。In、out、in out，分别对应输入、输出、输入输出。默认是输入参数。如例一。 例二．演示如何创建带in和out参数的过程。 可以在一个匿名的PL/SQL程序中执行max_sal_emp过程，以显示输出参数的结果。当过程返回值时，只能通过另一个程序查看该值。因此需要编写一个程序，显示out参数的值。以下代码用于显示max_sal_emp过程的返回值。 再看一个例子，也是带in和out 参数，用了游标。 例三。演示如何创建带in out参数的过程。 要执行上述swap过程，请输入以下代码。 调用过程swap，并查看结果，可以使用匿名的pl/sql语句来执行，如下。 4、过程创建之后，只有创建该过程的用户或管理员才能调用它。其他用户如果要调用该过程，需要得到过程的execute权限。下面代码将权限赋给某个用户或角色。 5、删除过程。 格式：drop procedure 过程名; 二、函数 函数与过程相似，也是数据库中存储的已命名的PL/SQL程序块。函数的主要特性是必须返回一个值。用return语句指定函数返回值的数据类型。在函数体的任何地方都可以通过return语句从函数返回一个值。 1、定义函数的格式。 /*注意*/ （1）函数只能带有in参数，不能带有in out参数和out参数。 （2）形式参数只能使用数据库类型，不能使用PL/SQL类型。 （3）函数的返回语句中返回表达式的值的类型必须与定义的类型一致。 例四．下列语句创建了一个简单函数。 与过程不同，函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。比如： 例五。下面的例子说明了如何使用参数及定义返回类型。 要调用上述函数，我们还得写出一个PL/SQL块，如下。 当然，具体到上述函数item_price_range，用PL/SQL块完成了调用，也可以直接写select item_price_range(3000) from dual; 效果一样。 2、函数的授权。 与过程相同，用户要调用其他用户的函数也需要得到授权。权限的名称也是execute。下面语句将fun_hello函数的调用权限授予用户martin。 3、删除函数 4、使用函数的限制。 （1）从select语句调用的任何函数均不能修改数据库表； （2）函数调用执行update的存储过程，则该函数不能在sql语句内使用。 5、函数和过程的比较。 过程 函数 作为PL/SQL语句执行 作为表达式的一部分被调用 在规范中不包括return子句 必须包含return子句 不返回任何值 必须返回单个的值 可以包含return语句，但不能用于返回值，需要返回值就采用out参数。 必须包含至少一条return语句 三、自主事务处理 见如下例子。 例六．创建一个过程P2 再创建一个过程P1 执行。 上述例子中，过程p1调用了过程P2，但由于过程P2中有回滚事务rollback的操作，使得过程P1的更新操作也失败。 如果在过程p2中加入PRAGMA AUTONOMOUS_TRANSACTION ,如下所示，就不会出现上述现象了。我们称P2使用了自主提交事务。