精通Oracle核心技术和项目实战之存储过程

1、第14章 存储过程前面章节中讲解了PL/SQL有关的内容。事实上，PL/SQL主要包括匿名块、存储过程和存储函数三种形式的PL/SQL块。存储过程是一段存储在数据库中执行某功能的程序。使用存储过程可以方便用户使用数据库中的应用程序。本章重点介绍如何创建存储过程以及如何在PL/SQL块中使用过程访问数据库中的数据。14.1 存储过程简介在PL/SQL块中常把一些功能相对独立、需要经常执行的代码定义为一个子程序，在需要时可以根据子程序的名字进行调用，从而简化操作。这样不仅便于程序设计，而且有利于程序的调试。与其他PL/SQL块一样，在子程序中也可以定义变量、类型、游标等，同样可以进行异常处理操作。

2、需要注意的是有时必须向子程序提供参数（向子程序传递参数）。PL/SQL的子程序有两种形式，即过程和函数。过程与函数的区别在于函数有返回值，可以向调用者返回执行结果，而过程没有返回值。14.1.1 认识存储过程在Oracle数据库中，可以将一些固定的操作集中起来由Oracle数据库服务器来完成，以实现某个任务，这就是存储过程。存储过程是Oracle开发者在数据转换或查询报表时经常使用的方式之一。一旦在数据库管理系统中创建了存储过程对象，用户就可以使用应用程序，通过简单命令执行存储过程。存储过程在第一次执行时，进行语法检查和编译，执行后，它的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接

3、收和输出参数，返回执行存储过程的状态值，还可以嵌套调用。详细内容在下面讲解。14.1.2 存储过程的作用存储过程的编写相对复杂，但是很多人都在使用它。这是因为它有着一系列的优点。简化复杂操作。增加数据独立性。提高安全性。实现表字段完整性。14.2 创建存储过程和执行存储过程更倾向于数据库操作。存储过程的创建有固定的模式。我们可以使用语句的方法创建，也可以使用PL/SQL工具创建，本节主要学习使用语句的方法创建执行存储过程。14.2.1 创建存储过程在PL/SQL语句中，可以使用create or replace procedure命令创建用户自定义存储过程。存储过程包括无参数的存储过程以及各类

4、有参数的存储过程。创建无参数的存储过程语法结构如图所示。14.2.1 创建存储过程1.创建无参数存储过程无参数的存储过程就是在创建的存储过程中不带任何参数，通过这种存储过程用做数据转换的几率比较大。【示例14-1】创建一个简单的存储过程，输出信息“人生追求情趣，年轻追求个性。”。【示例14-2】在数据表customersnew中，存储了顾客信息。【示例14-3】创建一个无参数存储过程，用于修改表customersnew中列credit_limit的数据，为status是“gold”的credit_limit增加10000。14.2.1 创建存储过程2.查看存储过程信息存储过程一旦创建，就存储在

5、数据库服务器上，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.显示存储过程的错误编写存储过程时由于各种原因都有可能出现错误而导致过程编译失败，这种情况下，可以

6、利用视图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 有参数的存储过程无参数的存储过程不会接受参数的传入和传出，

7、是针对表或者视图的查询或者删除操作，适合进行数据的转换操作。但是存储过程可以带参数，实现特定的功能。参数的使用将增加存储过程的灵活性，给数据库编程带来极大的方便。14.2.2 有参数的存储过程存储过程的参数有三种：in（输入类型）参数、out（输出类型）参数和in out（输入输出类型）参数。在过程中可以定义参数，在调用该过程时，可以向过程传递实际参数。如果没有参数，则过程名后面的圆括号及参数列表可以省略。参数的定义形式如图所示。14.2.2 有参数的存储过程三种参数传递模式的比较如表所示。参数默认值的作用是在调用过程时，如果没有提供实际参数，则将此默认值作为实际参数传递给过程。数据类型用来指

8、定参数的类型，在参数定义中不能指定对参数的约束条件，即不能指定参数的长度和是否为空等属性。 inoutin out是否默认是否默认默认必须明确指定必须明确指定必须明确指定参数传递方向参数传递方向从调用者到过程从过程到调用者两个方向两个方向形式参数的作用形式参数的作用一个常量没有初始化的变量经过初始化的变经过初始化的变量量实际参数的形式实际参数的形式常量、表达式、变量常量、表达式、变量必须是一个变量必须是一个变量必须是一个变量必须是一个变量14.2.3 in参数in参数，是输入类型的参数，表示这个参数输入给过程，供过程使用。本小节将讲述in参数的使用。前面我们讲解到使用存储过程可以检查数据的完整

9、性。下面就通过示例加深理解。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】前面我们创建了一个无参数存储过程pr

10、o_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参数是输出类型的参数，表示这个参数在存储过程中被复制，可以传给过

11、程提以外的部分。与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.

12、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

13、提供了创建存储过程的模版，该模版允许输入存储过程名和参数，然后自动创建脚本。该脚本是一个简单框架，细节则需要用户自己开发。【示例14-21】利用SQL Developer工具创建存储过程exch1，实现交换两个变量的值。14.3.2 调试存储过程存储过程创建之后，调试存储过程是很有必要的，它和其他语言的调试功能类似，可以设置断点，可以分步执行。本小节将简要介绍如何在PL/SQL Developer中调试修改存储过程。【示例14-22】在SQL Developer中调试存储过程。14.4 管理存储过程存储过程创建之后，可以对其进行调用。但是在后期使用中，如果有不合适的地方，需要对存储过程进行修改

14、和删除。本小节主要讲解图和修改存储过程、重新编译存储过程以及删除存储过程。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 删除存储过程除了使用语句方法外