PL/SQL Step By Step（三）

1.什么是存储过程    

    这篇博客主要介绍存储过程(Stored Procedure)，简称过程。存储过程是Oracle PL/SQL中的一种程序单元。存储过程可以通过给一个PL/SQL语句块命名从而将这个语句块存储在数据库中，以便将来可以被反复的调用。

    存储过程与一般的匿名PL/SQL块的一个主要区别是有无确定的名称。此外，对于匿名块来说，每次提到到数据库进行执行时，PL/SQL解析程序都会对其进行一次解析，然后再运行；然而对于存储过程来说，PL/SQL的解析程序只在其创建时对其进行一次解析，后续的调用就不需要再次解析了。

    存储过程可以在其他耳朵可执行语句中被调用，比如说另外一个匿名块或者另一个存储过程等，并且，存储工程还可以带参数。下面我们先看一段存储过程的示例代码：

PROCEDURE PROC_UPDATE_CRUISE_STATUS IS
  v_today DATE
BEGIN
  SELECT TRUNC(SYSDATE)
  INTO v_today
  FROM DUAL;
  
  UPDATE CRUISES
    SET STATUS = 'DISABLED'
  WHERE TRUNC(START_DATE) < v_today
    AND TRUNC(END_DATE) < v_today
    AND STATUS <> 'CANCELED';
    
  UPDATE CRUISES
    SET STATUS = 'COMPLETE';
  WHERE TRUNC(END_DATE) < v_today
    AND STATUS <> 'CANCELED';
    
  COMMIT;
END;
/

    这段代码会根据表CRUISES中的START_DATE和END_DATE与当前系统时间的关系来更新CRUISES表。对于这个存储过程来说，它是没有一个明确的返回值的，但是，执行该存储过程之后，整个数据库都能体现出表中每一行数据的更新后的状态了。

    还有一点需要特别的指出来。当你给别的用户赋予执行某个存储过程的权限的时候，即使这个用户对于存储过程所操作的表的访问权限，该用户也是可以执行存储过程的，这一点与普通的PL/SQL块也是不一样的。存储过程存放最多的位置还是数据库中，这种情况下，任何能够访问该数据库并且具有执行这个存储过程权限的应用程序均可以调用它。除此之外，存储过程也可以存在于客户端应用程序中，比如说基于Oracle Form Builder构建的程序，但是，这样的程序中的存储过程是不能被网络上的其他用户调用的。

2.创建、更改、删除存储过程

    创建一个存储过程可以采用CREATE PROCEDURE proc_name语句。完整的语法可以参考Oracle的官方文档，我这里只贴一张截图：

    光看这个图会有一种一下被shock到的感觉。其实实际中咱们写的存储过程不太可能完整的用到上面的语法图表示的内容。因此，我们这里只讲解最主要的部分。先说创建存储过程，还是看例子吧：

CREATE OR REPLACE PROCEDURE proc_clear_log IS
BEGIN
  DELETE FROM ERRORS;
  COMMIT;
END;
/

    这是一个再简单不过的创建存储过程的例子，其它部分和前面讲到的块没什么区别，只是需要遵守格式的要求：上面的例子中OR REPLACE是可选的，意思就是字面的意思：当这个存储过程名称已经有了就把原来的替换掉。关键字IS也可以用AS代替，当然会有不同，我们后面讲到再说。我们第一个例子还不涉及到带参数的存储过程，参数部分我们接下来会专门讲。

    当提交一个CREATE PROCEDURE命令到数据库（比如说利用SQL*PLUS），会发生下面的过程：

• 代码被存储在数据字典中
• 代码语法解析，并且最终被判定为VALID或者INVALID
• 如果是VALID，那么数据库会返回"Procedure created"提示信息，并且该存储过程随时可以被调用        
• 如果是INVALID，那么数据库会返回相应的错误信息，比如说类似于ORA-12222的错误号，该存储过程不能被调用    

    需要注意的是，无论解析与否，通过与否，存储过程的代码都会保存在数据字典中。作为一个好的习惯来说，你还可以给存储过程的END加上标签，也就是过程的名称，如下：

CREATE OR REPLACE PROCEDURE proc_clear_log IS
BEGIN
  DELETE FROM ERRORS;
  COMMIT;
END proc_clear_log;

    下面我们再说说修改存储过程，修改存储过程会有两种情况，因此也有两种不同的对应方法。如果说你的处理逻辑需要变化，换句话说，存储过程本身需要调整，那么可以采用上文提到的OR REPLACE选项来完整的替换掉之前的那个存储过程。第二种情况是，当存储过程内部所引用的数据库对象发生了变化，这时，数据库会强制将引用该对象的存储过程设置为INVALID状态。这时需要使用ALTER PROCEDURE语法。这句话可能不好理解，我们举例说明：

    对于上面的例子，存储过程proc_clear_log使用到了一个表ERRORS，现在假设我们修改表的结构如下：

ALTER TABLE ERRORS ADD ERROR_SOURCE VARCHAR2(30);

    这时，数据库会自动将proc_clear_log标记为INVALID，也就不能被执行了。但是我们知道表的更改实际上不影响存储过程proc_clear_log正确运行，那么要使存储过程重新变回VALID状态，可以使用下面的语句：

ALTER PROCEDURE proc_clear_log COMPILE;

    这个语句会重新触发Orale PL/SQL的解析程序去重新编译存储过程proc_clear_log，如果确实表的更改实际上不会影响proc_clear_log，那么，它会将proc_clear_log状态设置回VALID。

    删除存储过程就非常简单，相信读者大概都猜到了：

DROP PROCEDURE proc_clear_log;

3.如何调用存储过程？

    调用存储过程有两种方法：在PL/SQL块中调用和使用SQL*PLUS命令调用。下面先讲讲第一种方法：

    可以在任何一个PL/SQL块中的执行语句中调用已有的存储过程。直接用存储过程的名称即可调用。直接看代码：

BEGIN
    proc_clear_log;
END;

    这样的一个匿名块中可以调用多个不同的存储过程，并且可以和普通的SQL语句混合使用。上面这个匿名代码块本身就可以定义为一个存储过程。换句话说，存储过程中也是可以调用其他存储过程的。

    另一种调用存储过程的办法是在SQL*PLUS中使用其特有的命令来执行：

--下面的两种是一样的
EXECUTE proc_clear_log;
EXEC proc_clear_log;

4.存储过程参数

    存储过程的参数定义在存储过程顶部，集中在一对小括号中。每一个参数的定义都包括以下几个方面：

• 参数名    
• 参数的类型，IN或者OUT或者IN OUT类型。默认是IN类型    
• 数据类型：只能给出类型，不能给出精度、长短等。比如你可以定义类型为varchar2，但是不能定义为varchar2(30)  
• 默认值(可选)：通过使用DEFAULT关键字给某个参数指定默认值。

    此外，参数之间使用逗号隔开。下面我们看一个带有参数的存储过程的定义：

CREATE OR REPLACE PROCEDURE proc_example( 
       p_start_date IN DATE DEFAULT SYSDATE
      ,p_days       IN NUMBER
      ,p_name       IN VARCHAR2 DEFAULT 'TOM'
)
IS
......

    参数可以接受任何PL/SQL变量能接受的类型。但是不允许提供长度、精度信息。此外，参数类型可以接受%TYPE。参数可以提供默认值，这一点是可选的。但是只能给IN类型的参数提供默认值。除了上面的提供默认值的方法，还可以不写DEFAULT，而通过:=来提供默认值。

    对于提供了默认值的存储过程参数来说，在调用的时候可以不提供这些参数的值，而只提供没有默认值的参数提供值。但是，如果最后面的参数有默认值，而前面的参数没有，那还好说，后面的参数不提供值就好。但是如果反过来呢，这就有点麻烦。我们后面会讲解决这个问题的办法。

5.参数类型

    有必要单独把参数类型IN、OUT、IN OUT单独列出来说一下。

• IN类型

    IN类型的参数必须由调用者为其提供参数值，当然，如果定义了默认值给不给参数值都可以。一旦给了参数值，并且存储过程开始运行，那么，该该参数的值就不可以改变，换句话说，IN参数是只读的。

    除此之外，在定义参数的时候，如果是IN类型，可以不写。因为IN是默认的参数类型。上面的那个例子就可以写成：

CREATE OR REPLACE PROCEDURE proc_example( 
       p_start_date DATE DEFAULT SYSDATE
      ,p_days       NUMBER
      ,p_name       VARCHAR2 DEFAULT 'TOM'
)
IS
......

    我们看一个完整的定义和调用的例子：

--定义开始
PROCEDURE PROC_SHECULE_CRUISE(
          p_start_date IN DATE DEFAULT SYSDATE,
          p_days       IN NUMBER,
          p_ship_id    IN NUMBER,
          p_cruise_id  IN VARCHAR2
)
IS
  v_cruise_type_id CRUISE_TYPES.CRUISE_TYPE_ID%TYPE
BEGIN
  ...
END;
--定义结束

--调用开始
DECLARE
  v_ship_id NUMBER(4):=1;
BEGIN 
  PROC_SHECULE_CRUISE('04-JAN-2012',3,v_ship_id,'Alex');
END;
/
--调用结束

• OUT类型

    OUT类型参数与IN类型参数则刚好相反，OUT类型的参数是不允许调用者向其提供参数值的。并且OUT类型的参数是不允许提供默认值的。除此之外，OUT类型的参数的值最终会被返回给调用者。正是因此，在涉及到OUT类型参数的存储过程的调用就不一样了。毕竟，OUT类型的参数是需要传递回调用者的，那么调用者必须有相应的参数来接受返回的OUT参数。我们看一个例子：

--定义开始
PROCEDURE PROC_GET_EMPLOYEE_INFO(
          p_employee_id   IN   NUMBER,
          p_first_name    OUT  VARCHAR2;
          p_last_name     OUT  VARCHAR2  
)
IS
BEGIN
  SELECT FIRST_NAME,LAST_NAME
  INTO p_first_name,p_last_name
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID=p_employee_id
END;
/
--定义结束

--调用开始
DECLARE
  v_first_name VARCHAR2(30),
  v_last_name VARCHAR2(30),
BEGIN
  PROC_GET_EMPLOYEE_INFO(15,v_first_name,v_last_name);

--现在v_first_name,v_last_name有值了。

END;
/
--调用结束

• IN OUT参数

    有的参数可以既具备IN类型参数的特点，又具备OUT类型参数的特点。也就是说，既可以被调用者传递值进来，执行完存储过程，修改了值之后再将参数值传递给调用者。但是IN OUT类型参数不允许定义默认值。

6.参数传递方式

    我们之前所有涉及到的参数传递，都是按照参数传递的顺序一个一个的指定参数的值。但是这种方法会遇到问题，我们在上面也讲到。假设定义四个参数，第一个给了默认值，第二个没给，第三个第四个都给了参数值。该怎么调用呢？

    可以使用一个新的操作符=>通过名称指定给第几个参数赋值，代码如下：

PROC_INVOKE(p_second=>20);

    以上基本上就是我认为的存储过程的主要内容，欢迎补充交流。

ps：又是一个周末，祝大家愉快！