第14章 存储过程前面章节中讲解了PL/SQL有关的内容。事 实上，PL/SQL主要包括匿名块、存储过程 和存储函数三种形式的PL/SQL块。存储过 程是一段存储在数据库中执行某功能的程 序。使用存储过程可以方便用户使用数据 库中的应用程序。本章重点介绍如何创建 存储过程以及如何在PL/SQL块中使用过程 访问数据库中的数据。14.1 存储过程简介在PL/SQL块中常把一些功能相对独立、需要 经常执行的代码定义为一个子程序，在需要时 可以根据子程序的名字进行调用，从而简化操 作。这样不仅便于程序设计，而且有利于程序 的调试。与其他PL/SQL块一样，在子程序中 也可以定义变量、类型、游标等，同样可以进 行异常处理操作。需要注意的是有时必须向子 程序提供参数（向子程序传递参数）。PL/SQL的子程序有两种形式，即过程和函 数。过程与函数的区别在于函数有返回值，可 以向调用者返回执行结果，而过程没有返回 值。14.1.1 认识存储过程在Oracle数据库中，可以将一些固定的操作集中起 来由Oracle数据库服务器来完成，以实现某个任务 ，这就是存储过程。存储过程是Oracle开发者在数 据转换或查询报表时经常使用的方式之一。一旦在数据库管理系统中创建了存储过程对象，用 户就可以使用应用程序，通过简单命令执行存储过 程。存储过程在第一次执行时，进行语法检查和编译， 执行后，它的执行计划就驻留在高速缓存中，用于 后续调用。存储过程可以接收和输出参数，返回执 行存储过程的状态值，还可以嵌套调用。详细内容 在下面讲解。14.1.2 存储过程的作用存储过程的编写相对复杂，但是很多人都 在使用它。这是因为它有着一系列的优 点。简化复杂操作。增加数据独立性。提高安全性。实现表字段完整性。14.2 创建存储过程和执行存储过程更倾向于数据库操作。存储过程 的创建有固定的模式。我们可以使用语句 的方法创建，也可以使用PL/SQL工具创建 ，本节主要学习使用语句的方法创建执行 存储过程。14.2.1 创建存储过程在PL/SQL语句中，可以使用create or replace procedure命令创建用户自定义存储过程。存储过 程包括无参数的存储过程以及各类有参数的存储 过程。创建无参数的存储过程语法结构如图所 示。14.2.1 创建存储过程1.创建无参数存储过程无参数的存储过程就是在创建的存储过程中不 带任何参数，通过这种存储过程用做数据转换 的几率比较大。【示例14-1】创建一个简单的存储过程，输出 信息“人生追求情趣，年轻追求个性。”。【示例14-2】在数据表customersnew中，存储 了顾客信息。【示例14-3】创建一个无参数存储过程，用于 修改表customersnew中列credit_limit的数据， 为status是“gold”的credit_limit增加10000。14.2.1 创建存储过程2.查看存储过程信息存储过程一旦创建，就存储在数据库服务 器上，Oracle允许开发人员查看已经存在的 存储过程脚本，这可以通过数据字典（视 图）user_objects和user_source进行查看。当从视图中查询过程时，需要把名称大 写。14.2.1 创建存储过程（1）通过user_objects查看存储过程。【示例14-4】前面创建了创建一个无参数 存储过程pro_print，现在通过数据字典 user_objects查看存储过程信息。【示例14-5】利用user_source查看存储过程 pro_print的详细信息。14.2.1 创建存储过程3.显示存储过程的错误编写存储过程时由于各种原因都有可能出现错 误而导致过程编译失败，这种情况下，可以利 用视图user_errors查看具体的错误提示。【示例14-6】创建一个简单的带有错误的存储 过程，输出信息“人生追求情趣，年轻追求个 性。”。【示例14-7】利用user_errors查看存储过程的 具体的错误提示。14.2.1 创建存储过程4.执行存储过程存储过程一旦创建，便可以被其他用户调 用。执行存储过程非常简单。当处于 PL/SQL代码块中时，可以直接利用存储过 程名称来调用和执行存储过程如下所示。【示例14-8】调用和执行存储过程pro_print 。【示例14-9】使用execute命令调用存储过 程pro_credit。14.2.2 有参数的存储过程无参数的存储过程不会接受参数的传入和 传出，是针对表或者视图的查询或者删除 操作，适合进行数据的转换操作。但是存 储过程可以带参数，实现特定的功能。参 数的使用将增加存储过程的灵活性，给数 据库编程带来极大的方便。14.2.2 有参数的存储过程存储过程的参数有三种：in（输入类型）参数、 out（输出类型）参数和in out（输入输出类型）参 数。在过程中可以定义参数，在调用该过程时， 可以向过程传递实际参数。如果没有参数，则过 程名后面的圆括号及参数列表可以省略。参数的 定义形式如图所示。14.2.2 有参数的存储过程三种参数传递模式的比较如表所示。参数默认值 的作用是在调用过程时，如果没有提供实 际参数，则将此默认值 作为实际 参数传递给过 程。数 据类型用来指定参数的类型，在参数定义中不能指定 对参数的约束条件，即不能指定参数的长度和是否为 空等属性。inoutin out是否默认认默认必须明确指定必须须明确指定参数传递传递 方向从调用者到过程从过程到调用者两个方向形式参数的作用一个常量没有初始化的变 量经过经过 初始化的变变 量实际 参数的形式常量、表达式、变变量必须须是一个变变量必须是一个变量14.2.3 in参数in参数，是输入类型的参数，表示这个参数 输入给过程，供过程使用。本小节将讲述in 参数的使用。前面我们讲解到使用存储过程可以检查数 据的完整性。下面就通过示例加深理解。14.2.3 in参数1.创建包含in参数的存储过程【示例14-10】对于表customersnew的插入 操作，我们希望对提供的信息， account_mgr_id做判断，如果符合要求，则 可以执行插入操作，否则，禁止添加顾客 信息。14.2.3 in参数2.在存储过程中为in参数赋值如果在存储过程内部，尝试修改in参数，那 么将引起Oracle编译错误。【示例14-11】对于已经创建的存储过程 insert_cus，在存储过程内部，尝试将输入 参数mgr_id赋值为145。14.2.3 in参数3.重建带输入参数的存储过程【示例14-12】前面我们创建了一个无参数存储过程 pro_credit，用于修改表customersnew中列credit_limit的 数据，为status是“gold”的credit_limit增加10000。现在将 该存储过程修改为带输入参数的存储过程，可以指定顾 客编号对credit_limit进行相应处理。4.执行存储过程【示例14-13】执行存储过程pro_credit，将所有顾客的 credit_limit增加10000。【示例14-14】在上述例子中，将所有顾客的credit_limit 增加10000，现在将顾客编号为101的顾客增加10000。14.2.4 out参数out参数是输出类型的参数，表示这个参数 在存储过程中被复制，可以传给过程提以 外的部分。与in参数相反，out参数是只出 不进的参数。14.2.4 out参数1.创建一个带out参数的存储过程，并执行【示例14-15】对于前面示例中的存储过程 insert_cus，在调用插入数据之后，我们通 过查询表中数据才能验证是否执行成功。 如果使用out参数，更方便。【示例14-16】执行存储过程out_cus。14.2.4 out参数2.创建包含in参数、out参数的存储过程【示例14-17】创建存储过程total_order，计 算orders数据表中所有的订单总和，参数设 置时，我们设置一个in参数，一个out参 数。【示例14-18】执行存储过程total_order。14.2.5 in out参数in out参数综合了上述两种参数类型，既向 过程体传值，在过程中，也被赋值而传到 过程体外。in out参数既可以作为输入也可 以作为输出。【示例14-19】结合使用in out参数的，实现 交换两个变量的值。【示例14-20】执行存储过程exch，实现交 换两个变量的值。14.3 SQL Developer工具创建存储 过程使用SQL Developer创建存储过程是比较简 单的。该工具提供一个相对便利的操作环 境，尤其对于存储过程的调试和错误的查 找都比语句创建存储过程要方便。本节介 绍在SQL Developer中创建和调试存储过 程。14.3.1 创建存储过程SQL Developer提供了创建存储过程的模版 ，该模版允许输入存储过程名和参数，然 后自动创建脚本。该脚本是一个简单框架 ，细节则需要用户自己开发。【示例14-21】利用SQL Developer工具创建 存储过程exch1，实现交换两个变量的值。14.3.2 调试存储过程存储过程创建之后，调试存储过程是很有 必要的，它和其他语言的调试功能类似， 可以设置断点，可以分步执行。本小节将 简要介绍如何在PL/SQL Developer中调试修 改存储过程。【示例14-22】在SQL Developer中调试存储 过程。14.4 管理存储过程存储过程创建之后，可以对其进行调用。 但是在后期使用中，如果有不合适的地方 ，需要对存储过程进行修改和删除。本小 节主要讲解图和修改存储过程、重新编译 存储过程以及删除存储过程。14.4.1 修改存储过程当存储过程中出现错误时，可以修改存储 过程。修改存储过程可以人为地把原来的 存储过程删除，然后建立新的存储过程， 也可以使用Oracle提供的语法直接完成修 改。14.4.1 修改存储过程修改存储过程使用replace关键字，即覆 盖。我们一般在创建存储过程时，就将该 关键字写上。【示例14-23】创建存储过程，完成一个数 的加倍。【示例14-24】修改存储过程double1，将存 储过程的参数设置为in out参数类型，并修 改赋值方式。14.4.2 重新编译存储过程存储过程在某些情况下是需要重新编译的，重 新编译可以获得存储过程的最新状态。重新编 译存储过程的语法结构如图所示。【示例14-25】重新编译存储过程double1，完 成一个数的加倍。【示例14-26】执行存储过程double1。14.4.3 删除存储过程不再需要存储过程时，可以将其删除。利 用语句删除存储过程的语法结构如图所 示。【示例14-27】删除存储过程double1。14.4.3 删除存储过程除了使用语句方法外，还可以使用工具删 除存储过程。这种方式是在PL/SQL Developer中选中要删除的存储过程，右击 该过程，在弹出的快捷菜单中选择“删除”选 项，此时会出现确认删除窗口，单击“是”按 钮即可。14.5 小结存储过程的使用十分普遍，是Oracle的重点 知识。本章不仅介绍了存储过程的概念和 作用，而且详细讲解了如何让创建和操作 各种类型的存储过程。本章的重点是如何 根据需求创建合适的操作过程，难点是对 存储过程参数的设置，以及如何执行存储 过程。下一章将学习函数和程序包。