简单创建和删除存储过程

1.创建存储过程的语法

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END [过程名];

其中：

可选关键字OR REPLACE 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。 关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。(注意：大小写不敏感)。

存储过程参数说明

三种形式的参数

1. IN 定义一个输入参数变量，用于传递参数给存储过程

2. OUT 定义一个输出参数变量，用于从存储过程获取数据

3. IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

参数的定义形式和作用如下：

 a) IN参数

语法：参数名 IN 数据类型 DEFAULT 值;

定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

 b) OUT参数

语法：参数名 OUT 数据类型;

定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。

在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。

c) IN OUT参数

语法：参数名 IN OUT 数据类型 DEFAULT 值;

定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。DEFAULT 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

练习1：创建一个显示雇员总人数的存储过程。

CREATE OR REPLACE PROCEDURE EMP_COUNT   
AS  
V_TOTAL NUMBER(10);   
BEGIN  
SELECT COUNT(*) INTO V_TOTAL FROM EMP;   
DBMS_OUTPUT.PUT_LINE('employ sum:'||V_TOTAL);   
END;  
/

如何调用该过程：exec 过程名（参数值1，参数值2... ...）;
调用上面的过程：exec  EMP_COUNT;

SQL> set serverout on
SQL> exec emp_count;
employ sum:14

PL/SQL procedure successfully completed.

2.存储过程的查看

可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。

2.1查看存储过程的脚本

Select text from user_source where name = 'EMP_COUNT';

(这里的过程名必须大写)

SQL> Select text from user_source where name = 'EMP_COUNT';

TEXT
--------------------------------------------------------------------------------
PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM EMP;
DBMS_OUTPUT.PUT_LINE('employ sum:'||V_TOTAL);
END;

7 rows selected

2.2 查看存储过程的状态

select status from user_objects where object_name = 'EMP_COUNT';

SQL> select status from user_objects where object_name = 'EMP_COUNT';

STATUS
-------
VALID

说明：VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成VALID并执行，否则给出错误信息。

当一个存储过程编译成功，状态变为VALID，会不会在某些情况下变成INVALID。结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。

2.3 删除存储过程

语法：

DROP PROCEDURE 存储过程名;

如：drop procedure EMP_COUNT;

 

练习2：编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。

CREATE OR REPLACE PROCEDURE EMP_LIST  
        AS  
         CURSOR emp_cursor IS   
        SELECT empno,ename FROM emp;  
        BEGIN  
FOR Emp_record IN emp_cursor LOOP     
    DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);  
        END LOOP;  
        EMP_COUNT;  
        END;  
/

调用上面的过程：exec  EMP_LIST;

SQL> exec EMP_LIST;
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
employ sum:14

PL/SQL procedure successfully completed.

附：练习2涉及游标

  使用显示游标
   ⑴声明游标：划分存储区域，注意此时并没有执行Select 语句。
    CURSOR 游标名( 参数 列表)   [返回值类型]   IS   Select 语句；
   ⑵打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头， 而不是第一条记录。
    Open 游标名( 参数 列表);
   ⑶获取记录：移动游标取一条记录
    Fetch  游标名 InTo  临时记录或属性类型变量；
   ⑷关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
    Close  游标名；
 遍历循环游标
  ⑴For 循环游标
   循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
     For  变量名  In  游标名  
     Loop
      数据处理语句；
     End Loop；
   ⑵Loop循环游标
     。。。
    Loop 
     Fatch  游标名 InTo  临时记录或属性类型变量；
     Exit  When   游标名%NotFound；
    End   Loop；
     。。。