上面包车型地铁视角是在选拔游标的长河中做的日志。小编也是率先次利用,假诺有如何窘迫的地点请争辨指正,大家1道全力。

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

1.

咱俩在进展pl/sql编制程序时打交道最多的正是积累进度了。存款和储蓄进度的结构是尤其的简易的,大家在此间除了读书存款和储蓄进程的基本结构外,还会学习编写存款和储蓄进程时有关的一些实用的文化。如:游标的拍卖,格外的拍卖,集结的挑选等等

  消息 16951,级别 16,状态 1,过程
usp_proc,第 16 行
      变量 ‘@myref’
无法用作参数,因为在试行该进程前,不得为 CU智跑SO途乐 OUTPUT
参数分配游标。

壹.囤积进度结构

  那几个难题是本身在调用3个递归的、输出cursor output
的囤积进程

一.一 第2个存储进程

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(  

1旦未有对输出的游标做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
借使系统中尚无此存储进度就新建一个,假若系统中有此存款和储蓄进程则把本来删除掉,重新成立2个积攒进程。

积存过程名定义:包含存款和储蓄进程名和参数列表。参数名和参数类型。参数名无法重新,
参数字传送递格局:IN, OUT, IN OUT

IN 代表输入参数,按值传递方式。

OUT
代表输出参数,能够精晓为按引用传递情势。能够作为存款和储蓄进度的出口结果,供外部调用者使用。

IN OUT 就可以作输入参数,也可作输出参数。

参数的数据类型只须要指明类型名就可以,不须要内定宽度。

参数的肥瘦由外部调用者决定。

经过能够有参数,也能够没有参数

变量表明块:紧跟着的as (is
)关键字,可以掌握为pl/sql的declare关键字,用于注明变量。

变量注明块用于申明该存款和储蓄进程须求运用的变量,它的作用域为该存款和储蓄进程。别的那里评释的变量必须钦定宽度。服从PL/SQL的变量表明标准。

进程语句块:从begin
关键字伊始为经过的语句块。存款和储蓄进度的具体逻辑在此间来得以完结。

充足处理块:关键字为exception ,为拍卖语句发生的要命。该部分为可选

得了块:由end关键字结果。

一.二 存款和储蓄过程的参数字传送递格局

积攒进度的参数传递有三种艺术: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
参数:作为出口参数,供给留意,当1个参数被钦点为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 是确实的按引用传递参数。就能够作为传播参数也能够当作传播参数。

一.三 存款和储蓄进度参数宽度  

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_para二被予以了十几个字符a.

而在表面包车型客车调用进度中,p贰这些参数仅仅被定义为varchar二(壹).

而把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传给参数p贰

  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;  

内需专注变量的功效域。

叁.囤积进度的常用本领

叁.一 哪一种集合?

咱俩在选择存款和储蓄过程的时候平时必要管理记录集,也正是多条数据记录。分为单列多行和多列多行,那个品种都能够叫做集合类型。大家在那边开始展览相比这一个聚集类型,以便于在编制程序时做出准确的选料。

索引表,也叫做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,下标从壹始发,并且必要伊始化

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.二 选拔何种游标?

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

上面以二个历程来开始展览认证

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
来开采3个查询。必要小心的是它能够频仍应用,用来打开不一样的查询。

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

在意,游标的定义只可以用使重大字IS,它与AS不通用。

三.三 游标循环最棒战略

我们在拓展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地图