Oracle存储过程介绍

工作过程可能需要编写存储过程，下面稍微讲解一下创建存储过程的基本语法，先附上一个例子

--一个简单输出字符串的pl/sql语句
create or replace procedure sp_pro1 is
begin
dbms_output.put_line('Hello World!');
end;

set serveroutput on;--设置可以输出
exec sp_spro1;--调用存储过程

上面的例子创建了一个简单的存储过程并在命令窗口执行。下面讲解一下语法：

 

一：基本语法

存储过程大体分为这么几个部分:
1.创建语句：create or replace procedure 存储过程名。如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。

2.存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT
        IN：表示输入参数，按值传递方式。并且它不允许在存储过程中被重新赋值。
        OUT：表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。
        IN OUT：即可作输入参数，也可作输出参数。
参数的数据类型只需要指明类型名即可，不需要指定宽度。过程可以有参数，也可以没有参数。

3.变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

4.过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。

5.异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选

6.结束块：由end关键字结果。

 

二：存储过程内部块

我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块。

create or replace procedure innerBlock(p1 varchar2)   
as    
  o1 varchar2(10) := 'out1';   
begin   
  dbms_output.put_line(o1);   
  declare    
    inner1 varchar2(20);   
  begin   
    inner1 :='inner1';   
    dbms_output.put_line(inner1);   
  
    declare    
      inner2 varchar2(20);   
    begin   
      inner2 := 'inner2';   
      dbms_output.put_line(inner2);   
    end;   
  exception    
    when others then   
      null;   
  end;   
end;  

 

三：游标的使用

游标分为：普通游标，参数化游标和游标变量三种。 下面以一个过程来进行说明

    create or replace procedure proccursor(p varchar2)   
    as    
    v_rownum number(10) := 1;   
    cursor c_postype is select pos_type from pos_type_tbl where rownum =1;   
    cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;   
    cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;   
    type t_postype is ref cursor ;   
    c_postype3 t_postype;   
    v_postype varchar2(20);   
    begin   
      open c_postype;   
      fetch c_postype into v_postype;   
      dbms_output.put_line(v_postype);   
      close c_postype;   
      open c_postype1;   
      fetch c_postype1 into v_postype;   
      dbms_output.put_line(v_postype);   
      close c_postype1;   
      open c_postype2(1);   
      fetch c_postype2 into v_postype;   
      dbms_output.put_line(v_postype);   
      close c_postype2;   
      open c_postype3 for select pos_type from pos_type_tbl where rownum =1;   
      fetch c_postype3 into v_postype;   
      dbms_output.put_line(v_postype);   
      close c_postype3;   
    end;  

cursor c_postype is select pos_type from pos_type_tbl where rownum =1
这一句是定义了一个最普通的游标，把整个查询已经写死,调用时不可以作任何改变。

cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
这一句并没有写死，查询参数由变量v_rownum来决定。需要注意的是v_rownum必须在这个游标定义之前声明。

ursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
这一条语句与第二条作用相似，都是可以为游标实现动态的查询。但是它进一步的缩小了参数的作用域范围。但是可读性降低了不少。

type t_postype is ref cursor ;
c_postype3 t_postype;
先定义了一个引用游标类型，然后再声明了一个游标变量。
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
然后再用open for 来打开一个查询。需要注意的是它可以多次使用,用来打开不同的查询。
从动态性来说，游标变量是最好用的，但是阅读性也是最差的。
注意，游标的定义只能用使关键字IS，它与AS不通用。

 

使用游标注意事项：使用游标之前需要开打游标,open cursor，循环完后再关闭游标close cursor。这是使用游标应该慎记于心的法则。

上面的过程演示了游标循环的三种方法。
在讨论循环方法之前，我们先看看%found和%notfound这些游标的属性。

    open c_postype;   
     if c_postype%found then   
       dbms_output.put_line('found true');   
     elsif c_postype%found = false then   
       dbms_output.put_line('found false');   
     else  
       dbms_output.put_line('found null');   
     end if;  

在打开一个游标之后，马上检查它的%found或%notfound属性，它得到的结果即不是true也不是false.而是null.必须执行一条fetch语句后，这些属性才有值。

第一种使用loop 循环

loop   
   fetch c_postype into v_postype,v_description ;   
   exit when c_postype%notfound;   
   ……   
end loop 

这里需要注意，exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。
处理逻辑需要跟在exit when之后。这一点需要多加小心。
循环结束后要记得关闭游标。

第二种使用while循环。

       fetch c_postype into v_postype,v_description;   
    while c_postype%found loop   
       ……   
          fetch c_postype into v_postype,v_description ;   
    end loop;  

我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while 循环时，就需要在循环之前进行一次fetch动作。
而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。
总之，使用while来循环处理游标是最复杂的方法。

第三种 for循环

for v_pos in c_postype loop   
   v_postype := v_pos.pos_type;   
   v_description := v_pos.description;   
   …   
 end loop; 

可见for循环是比较简单实用的方法。
首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。
其它,自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。
我们需要注意v_pos 这个变量无需要在循环外进行声明，无需要为其指定数据类型。
它应该是一个记录类型，具体的结构是由游标决定的。
这个变量的作用域仅仅是在循环体内。
把v_pos看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。
如v_pos.pos_type
由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。
但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了。