从零开始学习Oracle之存储过程

存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。

1.创建存储过程

存储过程是指在Oracle数据库中，一组为了完成特定功能的 SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

在数据转换或查询报表时经常使用存储过程，它的作用是SQL语句不可替代的。

相对于直接使用SQL语句，在应用程序中直接调用存储过程有以下好处：

减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条地调用SQL语句要高得多。

执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

更强的适应性。由于存储过程对数据库的访问是通过存储过程接口来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

分布式工作。应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

创建存储过程，需要使用CREATEPROCEDURE语句，基本语法格式如下：

CREATE PROCEDURE为用来创建存储函数的关键字；

OR REPLACE表示如果指定的过程已经存在，则覆盖同名的存储过程；

schema表示该存储过程的所属机构；

procedure_name为存储过程的名称；

parameter_name为存储过程的参数名称；

[IN]datatype[{：=DEFAULT}expression]设置传入参数的数据类型和默认值；

表示存储过程的连接词；

BODY：表示函数体，是存储过程的具体操作部分，可以用BEGIN.….END来表示SQL代码的开始和结束。

2.调用存储过程

直接调用存储过程的方法如下：

也可以缩写成如下：

在Oracle SQL Developer中调用存储过程，如果想让DBMS_OUTPUT.PUT_LINE成功输出，需要把SERVEROUTPUT选项设置为ON状态。默认情况下，它是OFF状态。

可以使用以下语句查看 SERVEROUTPUT 选项的状态：

在Oracle SQL Developer中运行下面的代码调用存储过程HELLO:

运行结果如下：

另外，也可以在BEGIN....END中直接调用存储过程，调用语法如下：

3.查看存储过程

Oracle存储了存储过程的状态信息，用户可以查看已经存在的存储过程，还可以到视图USER_SOURCE中查看。

查看HELLO存储过程的SQL语句如下：

在查看存储过程中，需要把存储过程的名称大写，如果小写，则无法查询到任何内容。如果想查看所有的存储过程，可以在ALLSOURCE视图中查询。

4.存储过程的参数

存储过程可以带参数，也可以不带参数。在数据转换时经常使用不带参数的存储过程。

4.1无参数的存储过程

在Oracle中，存储过程是一段预先编译好的可重复使用的PL/SQL代码块。存储过程可以接受参数，也可以不接受参数。

4.2 有参数的存储过程

存储过程可以带有参数，使用参数可以增加存储过程的灵活性，为数据库编程带来很大的便利。存储过程的参数可以是常量、变量和表达式等。一旦为存储过程使用了参数，在执行存储过程时，必须指定对应的参数。

5.修改存储过程

在Oracle中，如果要修改存储过程，使用CREATE OR REPLACE PROCEDURE语句，也就是覆盖原始的存储过程。

修改存储过程HELLO，代码如下：

6.删除存储过程

删除存储过程，可以使用DROP语句，其语法结构如下：

Schema 表示存储过程所属的机构；procedure_name为要移除的存储过程的名称。

删除存储过程HELLO，代码如下：

7.查看存储过程的错误

编写的存储过程难免会出现各种错误而导致编译失败，为了减少排查错误的范围，Oracle提供了查看存储过程错误的方法，语法结构如下：

创建一个有错误的存储过程：SQL语句如下：

查看错误的具体细节，代码如下：

结果如下：

从错误提示可以看出来，错误出现在第4行，正确的写法如下：