第5章 开发子程序和包(理论)01.doc

第5章开发子程序和包本章学习内容1. 过程2. 函数3. 程序包本章学习目标1. 掌握过程的创建与使用2. 掌握函数的创建与使用3. 掌握包的创建与使用本章简介在第4章中，我们已经学习了PL/SQL语言、程序结构、流程控制、异常处理、开发动态SQL等知识。但是到目前为止，所创建的PL/SQL块都是匿名的，其缺点是每次执行时都需要被重新编译且没有被存储在数据库中，因此不能被其他的PL/SQL块使用，为了让编写的PL/SQL语句成为数据库的存储单元并能够共享，实现代码重用必须要使用程序包、过程和函数这些对象。本章将学习过程、函数和程序包等概念。过程与函数是命名的PL/SQL块，可以被编译后存储在数据库中，以备执行。因此其他PL/SQL块可以按名称来调用它们。所以可以将商业逻辑、企业规则写成过程或函数保存到数据库中，以便共享。过程一般用于执行一个指定的操作，而函数一般用于计算并返回一个值。过程与函数也被称为子程序，因为它们是独立的、能够被父程序调用。程序包用于将逻辑相关的PL/SQL块或元素组织在一起，作为一个完整的单元存储在数据库中，用名称来标识程序包，它具有面向对象的程序设计语言的特点，是对PL/SQL块或元素的封装。程序包类似于JAVA语言中的类，其中的变量相当于类中的成员变量，过程和函数相当于类中的方法。5.1 开发子程序子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用。PL/SQL有两种类型的子程序：过程和函数。其中，过程用于执行特定操作，而函数则用于返回特定数据。通过将商业逻辑和企业规则集成到PL/SQL子程序中，可以简化客户端应用的开发和维护，提高应用的性能。5.1.1 开发过程过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程。语法：CREATE OR REPLACE PROCEDURE procedure_name(argument1 mode1 datatype1, argument2 mode2 datatype2, .)IS AS声明部分BEGIN执行部分EXCEPTION异常处理部分END;在语法中，procedure_name用于指定过程名称，argument1、argument2等则用于指定过程的参数，IS或AS用于开始一个PL/SQL块。注意，当指定参数数据类型时，不能指定其长度。另外，当创建过程时，既可以指定输入参数（IN），又可以指定输出参数（OUT）及输入输出参数（IN OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分。通过使用输出参数，可以将执行部分的数据传递到应用环境。定义子程序参数时，如果不指定参数模式，则默认为输入参数；如果需要定义输出参数，则必须指定OUT关键字；如果需要定义输入输出参数，则必须指定IN OUT关键字。以下通过示例说明创建过程和使用各种参数模式的方法。1. 创建过程：无参数以下通过删除表中重复记录为例，来说明创建该种过程的方法。假设存在表tb_test，表中的数据如图1.5.1所示。图1.5.1 表tb_test中的数据创建一个无参过程，完成删除此表中重复记录的操作，如图1.5.2所示。图1.5.2 创建无参过程创建了过程proc_del_dup_rec之后，即可调用该过程。在SQL*Plus环境中，调用过程有两种方法：使用exec命令或者使用call命令。图1.5.3中使用了exec命令调用过程proc_del_dup_rec，并显示执行效果。显示删除重复记录后的结果图1.5.3 使用exec命令调用过程，并显示执行效果调用过程时，也可以使用PL/SQL块的方式，以下代码将以PL/SQL块的方式来调用proc_del_dup_rec过程：SQL begin 2 proc_del_dup_rec; 3 end; 4 /PL/SQL过程已成功完成。注意：调用过程时，无需添加exec或call关键字。说明2. 创建过程：带有IN参数创建过程时，可以通过使用输入参数，将应用程序的数据传递到过程中。当为过程定义参数时，如果不指定参数模式，则默认为输入参数，另外也可使用IN关键字显式地定义输入参数。下面以查询公交线路为例，说明创建这种过程的方法。假设存在表tb_station，其表中的数据如图1.5.4所示。图1.5.4 表tb_station中的数据创建一个带有IN参数的过程proc_transit_station，用于让用户输入公交车次、起点站和终点站即可查询出中间经过的公交线路，如图1.5.5所示。为过程参数指定默认值图1.5.5 创建带有IN参数过程由图1.5.5可知，由于在创建过程proc_transit_station时所有参数都没有指定参数模式，所以这些参数全部都是输入参数。当调用该过程时，除了具有默认值的参数之外，其他参数必须提供数值，调用示例如图1.5.6所示。使用存储过程默认值图1.5.6 调用带有IN参数的存储过程注意创建过程时，无需为参数指定长度，因为过程中的参数长度最终是由传递给参数的外部数据长度来决定的。3. 创建过程：带有OUT参数过程不仅可以用于执行特定操作，还可以用于输出数据。在过程中输出数据时，需要使用OUT或IN OUT参数来完成。当定义输出参数时，必须提供OUT关键字。以下通过创建用于输出雇员名及其工资的过程为例，来说明创建带有OUT参数的过程的方法，如图1.5.7所示。图1.5.7 创建带有OUT参数的存储过程由图1.5.7可知，由于在创建proc_query_emp时，没有为参数param_empno指定参数模式，所以该参数是输入参数；由于参数param_ename和param_salary指定了OUT关键字，所以这两个参数是输出参数。当在应用中调用该过程时，必须定义变量接收输出参数的数据。调用proc_query_emp过程的示例如图1.5.8所示。通过输出参数返回结果图1.5.8 调用带有OUT输出参数的存储过程4. 创建过程：带有IN OUT参数创建过程时，不仅可以指定IN和OUT参数，也可以指定IN OUT参数。IN OUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该种参数传递数据，调用结束后，Oracle会通过该变量将过程结果传递给应用。以下通过计算两个数值相除结果的过程proc_compute为例，来说明在过程中使用IN OUT参数方法，如图1.5.9所示。图1.5.9 创建带有IN OUT参数的过程由图1.5.9可知，在过程proc_compute中，param_num1、param_num2为输入输出参数。当在应用中调用该过程时，必须提供两个变量临时存放数值，在运算结束之后会将两数相除的商和余数分别存放到这两个变量中。调用proc_compute过程的示例如图1.5.10所示。图1.5.10 调用带有IN OUT参数的过程5.1.2 在调用过程时为参数传递变量和数据在调用带有参数的子程序时，传递给形式参数（形参）的参数被称为实际参数（实参）。在过程内部，通过形参引用这些实参的值。为形参传递变量和数据可以采用位置传递、名称传递和组合传递3种方法。注意，如果在定义参数时带有默认值，则在调用子程序时可以不用为该形参提供数值。在介绍如何使用各种方法为参数传递数值之前，首先创建用于为dept表增加数据的过程，如图1.5.11所示。图1.5.11 创建用于为dept表中增加数据的过程1. 按位置传递按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递。在这种方法中，形参与实参的名称是相互独立、没有关系的，次序才重要。它比按名称传递方法在书写上简单，但如果更新了一个过程的形参的次序，则对应该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序的难度。使用按位置传递的方式来调用过程的示例如图1.5.12所示。图1.5.12 按位置传递的方式来调用过程由图1.5.12可知，第二次调用时，并未给参数param_loc指定值，原因是参数param_loc具有默认值（NULL）。2. 按名称传递按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递。在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要。它比按位置传递方法在书写上要复杂，但如果只更新了一个过程的形参的次序，则不需要对该过程的任何调用进行任何更新。但如果更新了一个过程的形参的名称，则对该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序的难度。名称传递在调用子程序时指定参数名，并使用关联符号“=”为其提供相应的数值或变量。按名称传递方式来调用子程序的示例如图1.5.13所示。图1.5.13 按名称传递的方式来调用子程序3. 组合传递根据应用的需要，可以将按位置传递、按名称传递两种方法在同一调用中混合使用。但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法。按组合传递的方式来调用子程序的示例如图1.5.14所示。先按名称传递，再按位置传递会出现错误先按位置传递，再按名称传递图1.5.14 按组合传递的方式来调用过程5.1.3 开发函数函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数。通过使用函数，不仅可以简化客户端应用程序的开发和维护，还可以提高应用程序的执行性能。语法： CREATE OR REPLACE FUNCTION function_name (argument1 model datatype1,argument2 mode2 datatype2,.)RETURN datatype IS|AS 声明部分 BEGIN 执行部分 EXCEPTION 异常处理部分END;在语法中，function_name用于指定函数名称；argument1、argument2等则用于指定函数的参数。注意，当指定参数数据类型时，不能指定其长度。RETURN子句用于指定函数返回值的数据类型，IS或AS用于开始一个PL/SQL块。当创建函数时，函数头部必须带有RETURN子句，在函数体内至少需要包含一条RETURN语句。另外，当创建函数时，既可以指定输入参数（IN），又可以指定输出参数（OUT）及输入和输出参数（IN OUT）。以下通过示例来说明创建函数以及使用各种参数模式的方法。1. 创建函数：不带任何参数当创建函数时，函数既可以带有参数，又可以不带参数。以下通过创建用于显示当前数据库用户名的函数为例，来说明创建该种函数的方法，如图1.5.15所示。图1.5.15 创建不带任何参数的函数创建了函数fun_get_user之后，即可在应用中调用该函数。由于函数有返回值，所以它只能作为表达式的一部分来调用，调用示例如下：（1）使用变量接收函数返回值，如图1.5.16所示。调用函数，并使用变量接收函数返回值图1.5.16 使用变量接收函数返回值（2）在SQL语句中直接调用函数，如图1.5.17所示。图1.5.17 在SQL语句中直接调用函数2. 创建函数：带有IN参数当创建函数时，通过使用输入参数，可以将应用的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中。当定义参数时，如果不指定参数模式，则默认为输入参数，所以IN关键字既可以指定，又可以不指定。以下通过创建用于返回雇员工资的函数为例，来说明创建带有输入参数函数的方法，如图1.5.18所示。图1.5.18 创建带有IN参数的函数创建了函数fun_get_sal之后，即可在应用中调用该函数，图1.5.19中的代码用于调用函数fun_get_sal。输入不存在的雇员，引发异常输入存在的雇员图1.5.19 调用fun_get_sal函数函数通过RETURN语句一次只能返回一个值，如果希望函数一次返回多个值，则应该如何处理？提问3. 创建函数：带有OUT参数一般情况下，函数只需要返回单个数据。如果希望使用函数时返回多个数据（如同时返回雇员名和工资），则可以使用OUT输出参数来实现此功能。为了在函数中使用输出参数，必须指定OUT参数模式。以下通过创建用于返回雇员所在部门名和岗位的函数为例，来说明创建带有OUT参数函数的方法，如图1.5.20所示。图1.5.20 创建带有OUT输出参数的函数在图1.5.20中，fun_get_emp_info函数中为了能够同时返回两个值，分别使用OUT输出参数（param_dname）和RETURN语句（v_ejob）来达到返回多值的目的。创建了该函数后，即可在应用中调用。注意，由于该函数带有OUT参数，所以不能在SQL语句中调用该函数，而必须定义变量接收OUT参数和函数的返回值，调用此函数的示例如图1.5.21所示。图1.5.21 调用带有OUT输出参数的函数4. 创建函数：带有IN OUT参数与创建过程类似，创建函数时也可以指定IN OUT参数。以下通过计算两个数值相除结果的函数fun_compute为例，来说明在函数中使用IN OUT参数方法，如图1.5.22所示。图1.5.22 创建带有IN OUT参数的函数由图1.5.22可知，在函数fun_compute中，param_num1为输入参数，而param_num2为输入输出参数。在函数内部将两数相除的结果存放于输入输出参数param_num2中，而将两数相除的结果通过RETRUN语句返回。调用fun_compute函数的示例如图1.5.23所示。图1.5.23 调用带有IN OUT参数的函数过程与函数都能够返回值，那么在编程时，应该在何时使用过程、何时使用函数呢？提问5.1.4 过程与函数的比较过程与函数有许多相同的功能及特性，主要有4点：（1）都使用IN模式的参数传入数据、OUT模式的参数返回数据。（2）输入参数都可以接收默认值，都可以传值。（3）调用时的实参都可以使用位置表示法或名称表示法。（4）都有声明部分、执行部分和异常处理部分。那么何时使用过程，何时使用函数呢？这通常取决于需要从子程序中返回多少个值，以及需要如何使用这些值。一般而言，如果需要返回多个值或不返回值，就使用过程；如果只需要返回一个值，就使用函数。虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格。过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。可以在SQL语句内部调用函数来完成复杂计算问题，因为函数一定会有一个值通过其名称返回给调用环境，但不能调用过程，因为过程的返回值与过程的名称无关。提问需要实现商品出库功能，且要求出库后返回操作状态：0表示出库成功；1表示库存不足，出库失败；2表示商品不存在，无法出库。如果使用子程序封装商品出库功能，则应该使用过程还是函数？5.1.5 管理子程序过程与函数被存储在数据库中，可以随时查看其源代码。如果需要，可以在创建过程与函数时随时查看更加详细的编译错误信息，不需要的过程与函数可以随时删除。1. 查看子程序的源码在创建子程序之后，Oracle会将子程序名及其源代码信息存放在数据字典中。通过查询数据字典USER_SOURCE，可以显示当前用户的所有子程序及其源代码，如图1.5.24所示。name字段指定子程序名，注意区分大小写子程序源代码图1.5.24 显示子程序的源码2. 列出当前用户的子程序数据字典视图USER_OBJECTS用于显示当前用户所包含的所有对象。它不仅可以用于列出用户的表、视图、索引等，还可以用于列出用户的过程、函数和包。列出当前用户所包含的过程和函数的示例如图1.5.25所示。图1.5.25 列出当前用户所包含的过程和函数在图1.5.25中，object_name用于标识对象名称；object_type用于标识对象类型；created用于标识对象创建时间；status用于标识对象当前状态，VALID表示对象有效，而INVALID则表示对象无效。3. 列出子程序编译错误当编写子程序时，如果对象编译成功，则会显示消息“过程（函数）已建立”；如果对象编译不成功，则会显示“警告：创建的过程（函数）带有编译错误”。那么，如何确定错误出现在哪行以及错误原因呢？此时可以使用SHOW ERRORS命令来实现。图1.5.26中的代码用于创建带有编译错误的过程。图1.5.26 创建带有编译错误的过程然后使用SHOW ERRORS命令确定错误原因和位置，如图1.5.27所示。图1.5.27 使用SHOW ERRORS命令确定错误原因和位置由图1.5.27可知，根据错误提示信息，可以确定错误原因发生在第4行，并且错误原因是SQL语句结束时缺少句号。4. 列出对象依赖关系创建存储对象（过程、函数、包、视图、触发器）时，往往需要引用其他对象。例如，在创建过程raise_salary时，在UPDATE语句中引用了emp表，因此将过程raise_salary称为对象依赖，而将表emp称为引用对象。当修改了被引用对象的结构时，都会使得相关依赖对象转变为INVALID状态。可以使用数据字典视图USER_DEPENDENCIES确定直接依赖关系。以下通过显示直接依赖于表emp的所有对象以及对象类型为例，来说明使用数据字典视图USER_DEPENDENCIES的方法，如图1.5.28所示。图1.5.28 使用数据字典视图USER_DEPENDENCIES列出对象依赖关系5. 重新编译子程序当修改了被引用对象的结构时，就会将相关依赖对象转变为无效（INVALID）状态。例如，当更新了emp表的结构后，对于依赖于它的FUN_GET_EMP_INFO函数，其状态就会变成无效状态，如图1.5.29所示。修改表结构依赖于表的对象变为无效状态图1.5.29 修改被引用对象结构后依赖对象转变为无效状态当下次调用FUN_GET_EMP_INFO函数时，PL/SQL引擎就会自动尝试重新编译（即惰性编译）该函数。如果FUN_GET_EMP_INFO函数没有引用被更新的列，这种尝试肯定会成功，否则就会失败。为了避免调用的失败，在更新表的结构后，应该重新编译依赖于它的对象，如图1.5.30所示。重新编译子程序子程序状态变为有效图1.5.30 重新编译子程序6. 删除子程序如果不再需要某个子程序，可以将其删除。删除proc_query_emp过程和fun_get_sal函数的示例如图1.5.31所示。图1.5.31 删除子程序5.2 开发包包（Package）用于组合逻辑相关的PL/SQL类型（例如TABLE类型和RECORD类型）、PL/SQL项（例如游标和游标变量）和PL/SQL子程序（例如过程和函数）。通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏、子程序重载等功能。5.2.1 创建包包由包规范和包体两部分组成。当创建包时，需要首先创建包规范，然后再创建包体。1. 创建包规范实际上，包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数。在包规范中所定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用。创建包规范时需要注意的是：为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，而应该只定义公用组件。在SQL*Plus中创建包规范时，需要使用CREATE PACKAGE命令来完成。语法：CREATE OR REPLACE PACKAGE package_nameIS|ASpublic type and item declarationssubprogram specificationsEND package_name;在语法中，package_name用于指定包名，而以IS或AS开始的部分用于定义公用组件。以下通过创建用于维护emp表的包emp_package为例，来说明创建包规范的方法。当定义该包规范时，需要定义公用变量g_deptno、公用过程proc_add_employee以及公用函数fun_get_sal，如图1.5.32所示。图1.5.32 创建包规范在执行了上述命令之后，就会创建包规范emp_package，并且定义所有的公用组件。但因为只定义了过程和函数的头部，没有编写过程和函数的执行代码，所以公用的过程和函数只有在创建了包体之后才能调用。2. 创建包体包体用于实现包规范所定义的过程和函数。当创建包体时，也可以单独定义私有组件，包括变量、常量、过程和函数等，但在包体中所定义的私有组件只能在包内使用，不能由其他子程序引用。在创建包时，为了实现信息隐藏，应该在包体内定义私有组件；为了实现包规范中所定义的公用过程和函数，必须创建包体。创建包体需要使用命令CREATE PACKAGE BODY来完成。语法：CREATE OR REPLACE PACKAGE BODY package_nameIS | ASprivate type and item declarationssubprogram bodiesEND package_name;在语法中，package_name用于指定包名，由IS或AS开始的部分定义私有组件，并实现包规范中所定义的公用过程和函数。注意，包体名称与包规范名称必须相同。以下通过实现包规范emp_package的公用组件以及私有组件fun_validate_deptno为例，来说明创建包体的方法，如图1.5.33所示。图1.5.33 创建包体在执行了以上命令之后，会创建包体emp_package，应用只能直接调用该包内的所有公用组件，而私有函数fun_validate_deptno则不能被应用调用。5.2.2 调用包的组件对于包的私有组件，只能在包内调用，并且可以直接调用；而对于包的公用组件，既可以在包内调用，又可以在其他应用中调用。但需要注意的是，当在其他应用中调用包的组件时，必须添加包名作为前缀（包名.组件名），以下通过举例来说明调用包组件的方法。（1）在同一包内调用包的组件。在调用同一包内的其他组件时，可以直接调用，不需要添加包名作为前缀。例如前面在proc_add_employee过程中直接调用同一包中的fun_validate_deptno函数组件。（2）调用包的公用变量。当在其他应用中调用包的公用变量时，必须在公用变量名前添加包名作为前缀，并且注意其数值在当前会话内一直生效，如图1.5.34所示。图1.5.34 调用包的公用变量（3）调用包的公用过程。当在其他应用中调用包的公用过程时，必须在公用过程名前添加包名作为前缀，如图1.5.35所示。图1.5.35 调用包公用过程当执行了图1.5.35中的命令之后，会为emp表增加两条记录（部门号分别为20和30）。（4）调用包的公用函数。当在其他应用中调用包的公用函数时，需要在函数名前添加包名作为前缀。因为函数能作为表达式的一部分来调用，所以应该定义变量接收函数的返回值，如图1.5.36所示。图1.5.36 调用包公用函数（5）以其他用户身份调用包的公用组件。当以其他用户身份调用包的公用组件时，必须为用户赋予能够执行包的权限，并且必须以“用户名.包名.组件名”的语法格式来调用，如图1.5.37所示。在HR用户会话中执行SCOTT用户emp_package包中的公用组件授予用户HR能够执行SCOTT用户emp_package包的权限图1.5.37 以其他用户身份调用包公用组件如何调用远程Oracle数据库包中的公用组件？思考5.2.3 管理包1. 查看包源代码当创建了包之后，Oracle会将包名及其源代码信息存放到数据字典中。通过查询数据字典USER_SOURCE，可以显示当前用户的包及其源代码，如图1.5.38所示。图1.5.38 查看包源代码2. 删除包当包不再需要时，可以删除包。如果只删除包体，则可以使用DROP PACKAGE BODY命令；如果同时删除包规范和包体，则可以使用DROP PACKAGE命令，如图1.5.39所示。图1.5.39 删除包本章总结 子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同