存储过程和函数

张哥同步视频：https://edu.csdn.net/course/play/7940

本章简介

我们已经学习了PL/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.2 过程

5.2.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.创建无参过程

以下通过更新员工的薪水删除表中重复记录为例，说明创建该种过程的方法。

 

过程pro_update_rec创建之后可以进行调用，在sql /plus环境中可以使用call或者exec两个命令。如果不使用exec或者call命令的话，我们也可以使用pl/sql块的方式来调用。

2.创建in参数过程

创建过程时，可以使用输入参数将应用程序的数据传递到过程中。当为过程定义参数时，如果不指定参数模式，则默认为输入参数。另外，可以使用IN关键字显式地定义输入参数。下面以查询指定编号的员工为例说明创建该过程的方法,

 

该过程pro_query_emp有一个in参数v_no。当调用该过程时，由于该参数没有默认值，所以必须提供数值

 

3.创建out参数过程

过程不仅可以用于执行特定操作，还可以输出数据。在过程输出数据时，需要使用OUT或IN OUT参数来完成。当定义输出参数时，必须提供OUT关键字。以下通过创建用于输出雇员名的过程为例，说明创建带有OUT参数的过程的方法

 

，由于在创建pro_querysal_emp时，没有为参数param_empno指定参数模式，所以该参数是输入参数；参数param_sal指定了OUT关键字，所以这个参数是输出参数。当在应用中调用该过程时，必须定义变量接收输出参数的数据。

 

4.创建in  out参数过程

创建过程时，不仅可以指定IN和OUT参数，还可以指定IN OUT参数。IN OUT参数也称为输入输出参数，当使用此参数时，在调用过程之前需要通过变量给参数传递数据；调用结束后，Oracle会通过该变量将过程结果传递给应用。以下通过计算两个数值相除结果的过程proc_compute为例，说明在过程中使用IN OUT参数方法，

 

在过程proc_testinout中，param_num为输入输出参数。当在应用中调用该过程时，必须提供变量临时存放数值，在运算结束之后会将将结果存放到这个变量中。

 

 

 

5.2.2 使用过程时多参传递

在调用带有参数的子程序时，传递给形式参数（形参）的参数被称为实际参数（实参）。在过程内部，通过形参引用这些实参的值。为形参传递变量和数据可以采用位置传递、名称传递和组合传递3种方法。如果在定义参数时带有默认值，则在调用子程序时可以不为该形参提供数值。为说明多个参数传递的问题，我们编写一个计算3个整数和的过程。

 

1.按位置传递

按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递。在这种方法中，形参与实参的名称是相互独立的、没有关系的，次序才重要。如果更新了一个过程的形参的次序，则对应该过程的所有调用都必须进行相应的更新，所以会增加维护应用程序的难度。

 

2.按名称传递

按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递。在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要。它比按位置传递方法在书写上要复杂，如果只更新了一个过程的形参的次序，则不需要对该过程的任何调用进行任何更新。但如果更新了一个过程的形参的名称，则对该过程的所有调用都必须进行相应的更新，会增加维护应用程序的难度。名称传递在调用子程序时指定参数，并使用关联符号“=>”为其提供相应的数值或变量。

 

3.组合传递

根据应用的需要，可以将按位置传递、按名称传递两种方法在同一调用中混合使用。但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法。。

 

5.3 函数

函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，可以基于这些操作创建特定的函数。使用函数不仅可以简化客户端应用程序的开发和维护，还可以提高应用程序的执行性能。

语法:

Create  [or  replace] function  function_name

(argument1  [mode1]  datatype1,

Aargument2  [dode2]   datatype2,

……)

Return  datatype

is|as

声明部分

Begin

执行部分

Exception

异常处理部分

End;

在上述语法中，上述语法中，function_name用于指定函数名称，argLlmentlargument1、argLlment2 argument2等用于指定函数的参数。当指定参数数据类型时，不能指定其长度。RETURN子句用于指定函数返回值的数据类型，is或as用于开始一个PL/SQL块。当创建函数时，函数头部必须带有RETURN子句，在函数体内至少需要包含一条RETURN语句。另外，当创建函数时，既可以指定输入参数 (IN)，又可以指定输出参数 (OUT)及输入和输出参数 (IN OUT)。以下通过示例来说明函数的用法。

5.3.1 创建和使用函数

当创建函数时，函数既可以带有参数，又可以不带参数。以下通过创建用于产生一个随机数的函数为例，说明创建无参函数的方法

 

函数创建完之后，我么可以通过一个pl/sql块调用

 

在图4.1.15所示代码中，num用来接收函数的返回值。函数的使用除了在pl/sql块中调用外，还可以在sql语句中使用

 

函数像过程一样可以有参数，创建函数时也可以指定in、out、in out参数。我们在使用函数的时候传入参数即可。

5.3.2 函数和过程的比较

过程与函数有许多相同的功能及特性，主要包括以下4个方面：

(1)都使用IN模式的参数传入数据、OUT模式的参数返回数据。

(2)输入参数都可以接收默认值，都可以传值。

(3)调用时的实参都可以使用位置表示法或名称表示法。

(4)都有声明部分、执行部分和异常处理部分。

使用过程和函数的时机通常取决于需要从子程序中返回多少个值以及需要如何使用这些值。一般而言，返回多个值或不返回值时使用过程，只需要返回一个值时使用函数。虽然带OUT模式的参数的函数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格。过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。可以在 SQL语句内部调用函数来完成复杂计算问题，因为函数一定会有一个值通过其名称返回给调用环境；但不能调用过程，因为过程的返回值与过程的名称无关。

5.4 包

包 (Package)用于组合逻辑相关的 PL/SQL类型 (例如TABLE类型和 RECORD类型)、PL/SQL项 (例如游标和游标变量)和PL/SQL子程序 (例如过程和函数)。通过使用 PL/SQL包，不仅可以简化应用设计、提高应用性能，还可以实现信息隐藏、子程序重载等功能。

5.4.1 创建包

包由包规范和包体两部分组成。当创建包时，需要首先创建包规范，然后创建包体。

1. 创建包规范

实际上，包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数。在包规范中所定义的公用组件不仅可以在包内引用，而且可以由其他的子程序引用。创建包规范时需要注意，为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，应该只定义公用组件。在SQL Plus 中创建包规范时，需要使用CREATE PACKAGE命令来完成。

语法：

Create  [or  replace]  package  package_name

IS|AS

Public  type  and   item   declarations

Subprogram    specifications

End  package_name;

上述语法中，package_name用于指定包名，而以IS或AS开始的部分用于定义公用组件。以下通过创建用于计算圆面积的包dbutil_package为例，说明创建包规范的方法。当定义该包规范时，定义常量pi、公用过程print_area以及公用函数getarea

 

在执行上述命令后，就会创建包规范dbutil_package，并且定义所有的公用组件。但因为只定义了过程和函数的头部，没有编写过程和函数的执行代码，所以公用的过程和函数只有在创建了包体之后才能调用。

2.创建包体

包体用于实现包规范所定义的过程和函数。当创建包体时，也可以单独定义私有组件，包括变量、常量、过程和函数等，但在包体中定义的私有组件只能在包内使用，不能由其他子程序引用。在创建包时，为了实现信息隐藏，应该在包体内定义私有组件。为了实现包规范中所定义的公用过程和函数，必须创建包体。创建包体需要使用命令CREATE PACKAGE BODY来完成。

语法：

Create  [or  replace]  package  body  package_name

IS|AS

Private type  and item  declarations

Subprogram  bodies

End package_name;

 

上述语法中，package_name用于指定包名，由 IS或AS 开始的部分定义私有组件，并实现包规范中所定义的公用过程和函数。此外，包体名称与包规范名称必须相同。以下通过实现包规范dbutil_package的公用组件为例，说明创建包体的方法

 

5.4.2 使用包中组件

包的私有组件只能在包内调用，并且可以直接调用；而包的公用组件既可以在包内调用，又可以在其他应用中调用。但需要注意的是，当在其他应用中调用包的组件时，必须添加包名作为前缀(包名.组件名)，以下将举例说明调用包组件的方法。

1. 在同一包内调用包的组件

在调用同一包内的其他组件时，可以直接调用，不需要添加包名作为前缀。

2. 调用包的公用变量

当在其他应用中调用包的公用变量时，必须在公用变量名前添加包名作为前缀，并且其数值在当前会话内一直生效

 

3. 调用包的公用子程序

当在其他应用中调用包的公用过程时，必须在公用过程名前添加包名作为前缀

 

5.5 子程序和包的管理

5.5.1 子程序的管理

过程与函数被存储在数据库中，可以随时查看源代码。如果需要，可以在创建过程与函数时随时查看更加详细的编译错误信息，不需要的过程与函数可以随时删除。

1. 查看子程序的源码

在创建子程序之后，Oracle会将子程序名及其源代码信息存放在数据字典中。通过查询数据字典USER_SOURCE，显示当前用户的所有子程序及其源代码，如图4.1.21所示。

 

2. 删除子程序

如果不再需要某个子程序，可以将其删除。

 

5.5.2 包的管理

1. 查看包源代码

当创建了包之后，Oracle会将包名及其源代码信息存放到数据字典中。通过查询数据字典US_ER_SOURCE，显示当前用户的包及其源代码，包代码的查询和过程代码查看区别就在type字段，包的type字段值为”PACKAGE”,包体的type字段值为”PACKAGE BODY”。

2. 删除包

当包不再需要时，可以删除包。如果只删除包体，则可以使用 DROP PACHAGE BODY命令；如果同时删除包规范和包体，则可以使用DROP PACKAGE命令。

 

 

 

 

Ø 本章总结

Ø 子程序包括过程和函数，过程用于执行特定操作，而函数则用于返回特定数据。

Ø 包 (Package)用于组合逻辑相关的 PL/SQL类型 (例如TABLE类型和 RECORD类型)、PL/SQL项 (例如游标和游标变量)和PL/SQL子程序 (例如过程和函数)。

Ø 包由包规范和包体两部分组成。当创建包时，需要首先创建包规范，然后创建包体。

 

任务实训部分

1：无参存储过程

训练技能点

Ø 无参存储过程的创建和使用

需求说明

向部门表dept中插入两条部门信息，要求创建存储过程proc _ insert _ dept实现

 

实现思路

(1) 创建并执行存储过程“proc _ insert _ dept”，用于向表中插入数据

 

（2）调用存储过程

 

 

2：带IN和OUT参数的存储过程

训练技能点

Ø 带IN和OUT参数的存储过程的创建和使用

需求说明

编写过程，命名为“proc_storage”,向该存储过程传入需要出库的商品编号和出库数量，执行出库操作并返回完成状态、商品名称、原库存和现有库存。其中“00”表示出库成功，“1”表示找不到该商品，“2”表示库存不足。原始数据表tb_storage和tb_shop初始数据如图 5.2.4所示。

实现思路

（1） 创建存储过程proc_storage，,该过程包含两个IN 参数，分别用于接收需要出库的商品和出库数量；包含4个OUT参数，分别返回完成状态、商品名称、原库存和现有库存，如图5.2.5所示。

 

(2) 在PL/SQL块中测试并运行过程

 

3：带IN和OUT参数的函数

训练技能点

Ø 创建和使用带IN和OUT参数的函数

需求说明

根据雇员名称查询雇员所在部门名称和岗位的函数。

实现思路

（1） 创建函数名“fun_getEmpInfo”,该函数包含一个输入参数雇员名称param_ename、一个输出参数部门名称param_dname和一个返回值返回岗位信息。

 

（2） 调用函数

 

4：包的创建和使用

训练技能点

Ø 创建和使用包

需求说明

定义包规范emp_package,，然后定义公有变量v_deptno、公用过程proc_add_employee以及公用函数fun_get_sal，，然后根据该包规范定义包体，并定义私有过程fun_validate_deptno来验证员工是否存在。

实现思路

（1）创建包规范emp_package

 

（2）创建emp_package的包体

 

（3）调用包中的变量、过程及函数

 

 

巩固练习

一．、选择题

1. 下列选项中，必须返回数据的程序单元是 (  )。

    A. 触发器

    B. 函数

    C. 过程

    D. 包

2. 当创建过程时，可以实现输出数据的参数有 (  )。

    A. IN参数

    B. OUT参数

    C. IN OUT参数

    D. 任何参数都不能输出数据

3. 下列选项中，关于子程序组合传递方式的描述正确的是 (  )。

    A. 组合传递中必须同时包括位置传递和名称传递

    B. 位置传递必须位于名称传递之前

    C. 名称传递必须位于位置传递之前

    D. 以上说法都不正确

4. 下列选项中，关于Oracle程序包的描述正确的是 (  )。

    A. 在包规范部分定义公用组件

    B. 在包体部分定义公用组件

    C. 在包规范部分实现公用组件

    D. 在包体部分实现公用组件

 

二、上机练习

创建用于操纵ORD 表的包 ORD_PACKAGE，并调用该包的公用过程和函数。当创建包ORD_PACKAGE时，应该实现以下需求：

（1）定义私有函数 fun_valid_cust，检查客户号是否存在于 CUSTOMBR表中。如果存在则返回TRUE，否则返回 FALSE。

（2）定义公用过程proc_add_odd，根据输入的订单号、预订日期、客户号、交付日期、订单总价为 ORD表增加订单。如果订单已经存在，则显示自定义错误消息“ORA-20001：该订单已经存在!”；如果客户号不存在，则显示自定义错误消息“ORA-20002：该客户不存在!”；如果交付日期小于预定日期，则显示自定义错误消息“ORA-20003：交付日期不能小于预订日期!”。

文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。

原文链接：aaaedu.blog.csdn.net/article/details/60602880