Oracle存储过程介绍

「爱情、让人受尽委屈。」 2022-06-16 14:41 193阅读 0赞

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

--一个简单输出字符串的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循环是用来循环游标的最好方法。高效，简洁，安全。  
但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了。