丐帮资源网

oracle存储过程常用的技巧（详）
站长资源 2024/12/23 佚名
2 0

我们在进行pl/sql编程时打交道最多的就是存储过程了。存储过程的结构是非常的简单的，我们在这里除了学习存储过程的基本结构外，还会学习编写存储过程时相关的一些实用的知识。如：游标的处理，异常的处理，集合的选择等等

1.存储过程结构

1.1 第一个存储过程 

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(20);  
begin  
 v_name := '张三丰';  
 p_para3 := v_name;  
 dbms_output.put_line('p_para3:'||p_para3);  
end;

上面就是一个最简单的存储过程。一个存储过程大体分为这么几个部分:

创建语句：create or replace procedure 存储过程名

如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。

存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT

IN 表示输入参数，按值传递方式。

OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。

IN OUT 即可作输入参数，也可作输出参数。

参数的数据类型只需要指明类型名即可，不需要指定宽度。

参数的宽度由外部调用者决定。
过程可以有参数，也可以没有参数

变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。

变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

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

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

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

1.2 存储过程的参数传递方式

存储过程的参数传递有三种方式:IN,OUT,IN OUT .

IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(20);  
begin  
 p_para1 :='aaa';  
 p_para2 :='bbb';  
 v_name := '张三丰';  
 p_para3 := v_name;  
 dbms_output.put_line('p_para3:'||p_para3);  
 null;  
end;  
    
Warning: Procedure created with compilation errors  
 
SQL> show error;  
Errors for PROCEDURE LIFEMAN.PROC1:  
 
LINE/COL ERROR  
-------- ----------------------------------------------------------------------  
8/3   PLS-00363: expression 'P_PARA1' cannot be used as an assignment target  
8/3   PL/SQL: Statement ignored

这一点与其它高级语言都不同。它相当于java在参数前面加上final关键字。

OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null. 

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(20);  
begin  
 v_name := '张三丰';  
 p_para3 := v_name;  
 dbms_output.put_line('p_para1:'||p_para1);  
 dbms_output.put_line('p_para2:'||p_para2);  
 dbms_output.put_line('p_para3:'||p_para3);  
end;  
 
SQL> var p1 varchar2(10);  
SQL> var p2 varchar2(10);  
SQL> var p3 varchar2(10);  
SQL> exec :p1 :='aaaa';  
SQL> exec :p2 :='bbbb';  
SQL> exec :p3 :='cccc';  
SQL> exec proc1(:p1,:p2,:p3);  
p_para1:aaaa  
p_para2:  
p_para3:张三丰  
SQL> exec dbms_output.put_line(:p2);  
 
 
PL/SQL procedure successfully completed  
p2  
---------

INOUT 是真正的按引用传递参数。即可作为传入参数也可以作为传出参数。

1.3 存储过程参数宽度   

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(2);  
begin  
 v_name := p_para1;  
end;  
SQL> var p1 varchar2(10);  
SQL> var p2 varchar2(20);  
SQL> var p3 varchar2(30);  
SQL> exec :p1 :='aaaaaa';  
SQL> exec proc1(:p1,:p2,:p3);  
ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
ORA-06512: at "LIFEMAN.PROC1", line 8 
ORA-06512: at line 1

首先，我们要明白，我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。

这个宽度是完全由外部传入时决定的。

我们再来看看OUT类型的参数的宽度。

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(2);  
begin  
 p_para2 :='aaaaaaaaaaaaaaaaaaaa';  
end;  
SQL> var p1 varchar2(1);  
SQL> var p2 varchar2(1);  
SQL> var p3 varchar2(1);  
SQL> exec :p2 :='a';  
SQL> exec proc1(:p1,:p2,:p3);

 在该过程中，p_para2被赋予了20个字符a.

而在外部的调用过程中，p2这个参数仅仅被定义为varchar2(1).

而把p2作为参数调用这个过程，却并没有报错。而且它的真实值就是20个a

SQL> select dump(:p2) from dual;  
DUMP(:P2)  
---------------------------------------------------------------------------  
Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97 
p2  
---------  
aaaaaaaaaaaaaaaaaaaa

 再来看看IN OUT参数的宽度   

create or replace procedure proc1(  
 p_para1 varchar2,  
 p_para2 out varchar2,  
 p_para3 in out varchar2  
)as  
 v_name varchar2(2);  
begin  
 p_para3 :='aaaaaaaaaaaaaaaaaaaa';  
end;  
SQL> var p1 varchar2(1);  
SQL> var p2 varchar2(1);  
SQL> var p3 varchar2(1);  
SQL> exec proc1(:p1,:p2,:p3);

执行这个过程，仍然正确执行。

可见，对于IN参数，其宽度是由外部决定。

对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。

因此，在写存储过程时，对参数的宽度进行说明是非常有必要的，最明智的方法就是参数的数据类型使用%type。这样双方就达成了一致。

1.3 参数的默认值

存储过程的参数可以设置默认值

create or replace procedure procdefault(p1 varchar2,  
                    p2 varchar2 default 'mark')  
as  
begin  
 dbms_output.put_line(p2);  
end;  
SQL> set serveroutput on;  
SQL> exec procdefault('a'); 
mark

可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。
需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值

对于有默认值的参数不是排在最后的情况。

create or replace procedure procdefault2(p1 varchar2 default 'remark',  
                    p2 varchar2 )  
as  
begin  
 dbms_output.put_line(p1);  
end;

第一个参数有默认值，第二个参数没有。如果我们想使用第一个参数的默认值时

exec procdefault2('aa');

这样是会报错的。

那怎么变呢？可以指定参数的值。

SQL> exec procdefault2(p2 =>'aa'); 
remark

这样就OK了，指定aa传给参数p2

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 哪种集合"htmlcode"> 

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');

以上叙述就是本文的全部内容，希望对大家学习oracle存储过程有所帮助。

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
www.yaopr.com 丐帮资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接