Oracle存储过程常用技巧(1)，互联网分布式系统架构

需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值

2. 存储过程内部块

2.1 内部块

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

Declare … begin … exception … 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;

需要注意变量的作用域。

3.存储过程的常用技巧

3.1 哪种集合?

我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。我们在这里进行比较这些集合类型，以便于在编程时做出正确的选择。

索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。

type t_table is table of varchar2(20) index by binary_integer;

v_student t_table;

varchar2(20)表示存放元素的数据类型，binary_integer表示元素下标的数据类型。

嵌套表，索引表没有 index by子句就是嵌套表，它可以存放于数据中，元素个数无限，下标从1开始，并且需要初始化

type t_nestTable is table of varchar2(20);

v_class t_nestTable ;

仅是这样声明是不能使用的，必须对嵌套表进行初始化，对嵌套表进行初始化可以使用它的构造函数

v_class :=t_nestTable(‘a’,‘b’,‘c’);

变长数组，变长数组与高级语言的数组类型非常相似，下标以1开始，元素个数有限。

type t_array is varray (20) of varchar2(20);

varray(20)就定义了变长数组的最大元素个数是20个

变长数组与嵌套表一样，也可以是数据表列的数据类型。

同时，变长数组的使用也需要事先初始化

由此可见，如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。

3.2 选用何种游标?

显示游标分为：普通游标，参数化游标和游标变量三种。

下面以一个过程来进行说明

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_