Post Jobs

澳门新葡亰平台游戏使用游标过程中出现的错误,Oracle存储过程使用技巧与经验总结

上边包车型地铁思想是在行使游标的经过中做的日志。笔者也是第叁次选取,假设有何样狼狈的地点请批评指正,我们一起努力。

oracle存款和储蓄过程常用技能

1.

我们在进展pl/sql编制程序时打交道最多的正是积存进程了。存款和储蓄进度的组织是可怜的精短的,我们在那除了读书存储过程的主导构造外,还也许会学习编写存款和储蓄进度时有关的一些实用的文化。如:游标的拍卖,极度的拍卖,集结的取舍等等

  消息 16951,级别 16,状态 1,过程
usp_proc,第 16 行
      变量 ‘@myref’
不可能用作参数,因为在实行该进程前,不得为 CU奇骏SOLX570 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被付与了19个字符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)就定义了变长数组的最大体素个数是21个

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

还要,变长数组的采用也急需事先开头化。

类型 可存款和储蓄于数据库 成分个数 是或不是需起头化 开首下标值

索引表 否 无限 不需

嵌套表 可 无限 需 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 ;  

发表评论

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

相关文章

网站地图xml地图