必赢官网使用游标过程中出现的错误,Oracle存储过程使用技巧与经验总结

下边包车型客车眼光是在运用游标的经过中做的日志。笔者也是率先次利用,倘若有哪些狼狈之处请商量指正,我们齐声全力。

oracle存款和储蓄进程常用才具

1.

咱俩在扩充pl/sql编制程序时打交道最多的便是积存进程了。存储进度的构造是充足的简要的,我们在此边除了学习存款和储蓄进度的主旨结构外,还有恐怕会学习编写存款和储蓄进度时有关的片段实用的学问。如:游标的管理,十分的管理,集结的挑精拣肥等等

  消息 16951,级别 16,状态 1,过程
usp_proc,第 16 行
      变量 ‘@myref’
不可能用作参数,因为在实行该进程前,不得为 CU奥迪Q5SO奥迪Q5 OUTPUT
参数分配游标。

1.存储进度结构

  那个主题素材是自身在调用七个递归的、输出cursor output
的仓库储存进程

1.1 第一个存款和储蓄进程

create proc usp_proc(
@level int
@myref cursor varying output
)
as
begin
    if @level=3
        begin
             set @myref=cursor local static for
            select * from table
            open @myref
        end
     if @level<3
        begin
        declare @cur cursor
        exec usp_proc 2 @cur output --递归
        --
        --对输出游标@cur做一些操作
        --
        --使用完游标
        close @cur  --关闭游标
        deallocate @cur --删除游标
        end
end            

create or replace procedure proc1(  

举例未有对输出的游标做close、deallocate管理就能够并发上面错误。

 p_para1 varchar2,  

2.

 p_para2 out varchar2,  

  未有为@cur,分配游标

 p_para3 in out varchar2  

  那些主题材料是自身在采纳存款和储蓄进程再次来到的游标 cursor
output 发生的

)as    

  

v_name varchar2(20);  

create proc myproc(
@mycur cursor varying output
)
as
begin
set @mycur=cursor local static  for
select * from table

open @mycur --打开游标
end

--调用myproc
declare @cur cursor
exec myproc @cur output
fetch next from @cur
while @@fetch_status=0
    begin
    --使用游标
    fetch next from @cur
    end 

begin  

现身上述错的原由正是概念游标后要求展开 open @mycur

 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被付与了21个字符a.

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

而把p2作为参数调用那个进程,却并不曾报错。而且它的真实值正是19个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

  1. 仓库储存进程之中块

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子句就是嵌套表,它能够贮存于数据中,成分个数Infiniti,下标从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)就定义了变长数组的最大因素个数是十多少个

变长数组与嵌套表同样,也足以是数据表列的数据类型。

再者,变长数组的行使也急需事先早先化。

花色 可存款和储蓄于数据库 成分个数 是或不是需开头化 开头下标值

索引表 否 无限 不需

嵌套表 可 无限 需 1

可变数组 可 有限(自定义) 需 1

有鉴于此,要是单单是在储存进度中作为集结变量使用,索引表是最棒的精选。

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_tbl where rownum
=1

这一句是概念了二个最平时的游标,把全副查询已经写死,调用时不得以作别的更改。

cursor c_postype1 is select pos_type from pos_type_tbl where rownum
= v_rownum;

这一句并没有写死,查询参数由变量v_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;

先定义了二个援引游标类型,然后再注解了三个游标变量。

open c_postype3 for select pos_type from pos_type_tbl where rownum
=1;

然后再用open for
来展开二个询问。要求专一的是它能够屡次施用,用来开拓不一致的询问。

从动态性来讲,游标变量是最棒用的,不过阅读性也是最差的。

只顾,游标的概念只好用使重大字IS,它与AS不通用。

3.3 游标循环最棒计谋

笔者们在进展PL/SQL编程时,通常索要循环读取结果集的数量。实行逐行管理,这一个历程就须要对游标实行巡回。对游标进行巡回的艺术有八种,大家在这里生龙活虎一分析。

create or replace procedure proccycle(p varchar2)  

as    

cursor c_postype is select pos_type, description from pos_type_tbl
where rownum < 6;  

v_postype varchar2(20);  

v_description varchar2(50);  

begin  

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;  

 loop  

  fetch c_postype into v_postype,v_description ;  

  exit when c_postype%notfound;  

 
dbms_output.put_line(‘postype:’||v_postype||’,description:’||v_description);
 

 end loop;  

 close c_postype;  

dbms_output.put_line(‘—loop end—‘);  

 open c_postype;  

   fetch c_postype into v_postype,v_description;  

   while c_postype%found loop  

   
 dbms_output.put_line(‘postype:’||v_postype||’,description:’||v_description);
 

     fetch c_postype into v_postype,v_description ;  

发表评论

电子邮件地址不会被公开。 必填项已用*标注