使用游标过程中出现的错误,Oracle存储过程使用

日期:2019-11-02编辑作者:数据大全

上边包车型地铁意见是在选用游标的进度中做的日志。作者也是首先次利用,假如有怎样窘迫的地点请斟酌指正,我们风流倜傥道全力。

oracle存款和储蓄进度常用技术

  风姿罗曼蒂克,定义:Sql Server的积累进度是叁个被取名的储存在服务器上的Transacation-Sql语句集结,是包裹重复性职业的黄金时代种方法.

1.

大家在进展pl/sql编制程序时打交道最多的正是积攒进度了。存款和储蓄进度的构造是不行的大致的,大家在这间除了读书存款和储蓄过程的基本结构外,还有大概会学习编写存款和储蓄进程时有关的后生可畏部分实用的学问。如:游标的拍卖,卓殊的拍卖,集结的筛选等等

  二,存款和储蓄进程的长处:

  消息 16951,级别 16,状态 1,过程 usp_proc,第 16 行
      变量 '@myref' 无法用作参数,因为在执行该进程前,不得为 CULANDSORAV4 OUTPUT 参数分配游标。

1.囤积进程结构

   1,重复使用。存款和储蓄进程能够重复使用,进而得以减少数据库开拓人士的专门的学问量。

  那一个主题材料是本人在调用三个递归的、输出cursor output 的仓储进程

1.1 第叁个存款和储蓄进程

   2,提升质量。存款和储蓄进程在创建的时候就进行了编写翻译,现在选用的时候不要再重新编写翻译。平时的SQL语句每推行一次就必要编写翻译二回,所以选拔存款和储蓄进程进步了频率。

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(  

     3,收缩网络流量。存储进程位于服务器上,调用的时候只供给传递存款和储蓄进度的名称甚至参数就可以了,由此裁减了网络传输的数据量。

若果未有对出口的游标做close、deallocate管理就会见世上面错误。

 p_para1 varchar2,  

   4,安全性。参数化的储存进度可以堤防SQL注入式的抨击,并且能够将格兰特、Deny以至Revoke权限应用于储存进程。

2.

 p_para2 out varchar2,  

  三,语法,创设存储进程:  

  没有为@cur,分配游标

 p_para3 in out varchar2  

语法

  这么些标题是自己在动用存款和储蓄进度重返的游标 cursor output 产生的

)as    

CREATE PROC[ EDURE ] [ owner**. ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ] [ WITH     { RECOMPILE | ENCRYPTION | RECOMPILE ,* ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n* ]

参数

owner

    具备存款和储蓄进度的客户 ID 的称谓。owner 必得是时下客户的名号或当前客商所属的剧中人物的称号。

procedure_name

    新存款和储蓄进度的名称。进程名必得符合标记符准则,且对于数据库及其主人必需唯豆蔻年华。

;*number*

    是可选的整数,用来对同名的长河分组,以便用一条 DROP PROCEDURE 语句就能够将同组的进度一齐除去。举个例子,名叫 orders 的应用程序使用的经过能够命名称为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除了整个组。假诺名称中隐含定界标志符,则数字不应包蕴在标记符中,只应在 procedure_name 前后接纳方便的定界符。

@parameter

    进程中的参数。在 CREATE PROCEDURE 语句中能够声贝因美个或多个参数。顾客必得在施行进程时提供各类所注解参数的值(除非定义了该参数的私下认可值,或许该值设置为等于另三个参数)。存款和储蓄进程最多可以有 2.100 个参数。

选取 @ 符号作为第叁个字符来钦点参数名称。参数名称必得符合标记符的平整。每种进度的参数仅用于该进度本人;形似的参数名称能够用在别的进度中。私下认可情况下,参数只好代替常量,而不能够用来替代表名、列名或任何数据库对象的名称。

data_type

    参数的数据类型。除 table 之外的别的具备数据类型均能够作为存款和储蓄进度的参数。不过,cursor 数据类型只好用来 OUTPUT 参数。假诺钦命 cursor 数据类型,则还必须内定VAOdysseyYING 和 OUTPUT 关键字。对于能够是 cursor 数据类型的输出参数,未有最大数量的界定。

VARYING

    钦命作为出口参数扶持的结果集(由存款和储蓄进程动态构造,内容能够调换)。仅适用于游标参数。

default

    参数的私下认可值。若是定义了暗中同意值,不必内定该参数的值就能够实施进程。暗中同意值必得是常量或 NULL。假设经过将对该参数使用 LIKE 关键字,那么暗许值中能够蕴含通配符(%、_、[] 和 [^])。

OUTPUT

    评释参数是回去参数。该选取的值能够回去给 EXEC[UTE]。使用 OUTPUT 参数可将消息再次回到给调用进程。Textntextimage 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的出口参数能够是游标占位符。

n

    表示最多能够钦命 2.100 个参数的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

    RECOMPILE 声明 SQL Server 不会缓存该过程的布署,该进度就要运作时再也编写翻译。在选用非规范值或有时值而不愿意覆盖缓存在内部存款和储蓄器中的施行布署时,请使用 RECOMPILE 选项。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中隐含 CREATE PROCEDURE 语句文本的家有家规。使用 ENC奥德赛YPTION 可制止将经过作为 SQL Server 复制的风流倜傥有个别发布。

FOR REPLICATION

    钦赐不可能在订阅服务器上进行为复制创造的蕴藏进程。.使用 FOSportage REPLICATION 选项创立的蕴藏进度可用作存款和储蓄进度筛选,且只可以在复制进程中推行。本选项不能和 WITH RECOMPILE 选项一同利用。

AS

   钦点进程要实践的操作。

sql_statement

   进程中要包罗的随便数目和类型的 Transact-SQL 语句。但有点范围。

n

   是意味此进程能够饱含多条 Transact-SQL 语句的占位符。

  四,使用格局:

  

**********************************************

注:*所包围部分来源于MS的一路丛书.

 

                           多少个实例

                        (AjaxCity表中内容)

     ID        CityName   Short

             1         苏州市     SZ  

             2     无锡市     WX

             3         常州市     CZ

1.抉择表中全数内容并回到三个数据集

        CREATE PROCEDURE mysp_All
        AS
           select * from AjaxCity
        GO

实施结果

        澳门金莎娱乐手机版 1

2.基于传入的参数进行询问并赶回多个数据集

       CREATE PROCEDURE mysp_para
            @CityName varchar(255),

            @Short    varchar(255)
       AS
         select * from AjaxCity where CityName=@CityName And Short=@Short
       GO

实施结果

        澳门金莎娱乐手机版 2

3.满含输出参数的蕴藏进程(重返前两条记下的ID的和)

CREATE PROCEDURE mysp_output
       @SUM int  output
 AS
       select @SUM=sum([ID]) from (select top 2 * from AjaxCity) as tmpTable
GO

施行结果

         澳门金莎娱乐手机版 3

4.在积攒进程中运用游标

  有这么二个表,存款和储蓄的是各超阶级市上边包车型地铁省级市的消息.如图:

   澳门金莎娱乐手机版 4

   以后想计算出各样地级市上面包车型客车省级市的个数,并整合三个字符串.结果应该是"5,2,2".

 

CREATE PROCEDURE mysp_Cursor
    @Result varchar(255) output//评释输出变量
AS
    declare city_cursor cursor for//申明游标变量
    select [ID] from AjaxCity

set @Result=''
declare @Field int//注明有时存放CityID的变量
open city_cursor //张开游标
fetch next from city_cursor into @Field//将实际ID赋给变量
while(@@fetch_status=0)//循环开头
begin
       if @Result = ''
           select @Result = convert(nvarchar(2),count(*))  from AjaxCounty where CityID=@Field
       else
           select @Result = @Result + ',' + convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field
      
       fetch next from city_cursor into @Field//下一个CityID
end
close city_cursor//关闭游标
deallocate city_cursor//释放游标援引
GO

 

推行结果

       澳门金莎娱乐手机版 5

 

    好了,关于存款和储蓄进度先写到这里.以上多少个例子基本上完结了平庸所用到的绝大相当多功用.至于复杂的储存进度,所用到的驾驭根本是SQL的语法,以至SQL中贮存函数的使用.已不属于本文所要探讨的限量了.

  

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被给与了拾七个字符a.

而在外界的调用进程中,p2那么些参数仅仅被定义为varchar2(1).

而把p2作为参数调用这一个进度,却并不曾报错。况且它的真实值便是二十一个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 ;  

   end loop;  

 

 close c_postype;  

dbms_output.put_line('---while end---');  

 for v_pos in c_postype loop  

   v_postype := v_pos.pos_type;  

   v_description := v_pos.description;  

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

 end loop;  

 dbms_output.put_line('---for end---');  

end;  

应用游标早先须要开打游标,open cursor,循环完后再关闭游标close cursor.

那是行使游标应该慎记于心的法规。

上边包车型大巴进度演示了游标循环的三种艺术。

在座谈循环方法在此之前,大家先看看%found和%notfound那几个游标的品质。

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;  

在开荒一个游标之后,即刻检查它的%found或%notfound属性,它获得的结果即不是true也不是false.而是null.必需施行一条fetch语句后,这个属性才有值。

首先种采纳loop 循环

loop  

  fetch c_postype into v_postype,v_description ;  

  exit when c_postype%notfound;  

  ……  

end loop  

此处须求在意,exit when语句必供给紧跟在fetch之后。必防止多余的多寡管理。

拍卖逻辑须要跟在exit when其后。那或多或少内需多加小心。

循环截止后要记得关闭游标。

第三种选取while循环。

  fetch c_postype into v_postype,v_description;  

while c_postype%found loop  

  ……  

     fetch c_postype into v_postype,v_description ;  

end loop;  

大家理解了三个游标张开后,必需实行贰回fetch语句,游标的性质才会起效果。所以使用while 循环时,就须求在循环早先行行二回fetch动作。

再正是数量管理动作必需放在循环体内的fetch方法以前。循环体内的fetch方法要放在最终。不然就能够多管理一次。这或多或少也要十一分的当心。

综上说述,使用while来循环管理游标是最复杂的方法。

第三种 for循环

for v_pos in c_postype loop  

  v_postype := v_pos.pos_type;  

  v_description := v_pos.description;  

  …  

end loop;  

可以预知for循环是比较轻便实用的主意。

先是,它会自行open和close游标。解决了您忘掉展开或关闭游标的忧愁。

其余,自动定义了三个笔录类型及评释该品种的变量,并活动fetch数据到那个变量中。

咱俩必要注意v_pos 这些变量不供给要在循环外举行宣示,不须求要为其钦定数据类型。

它应当是三个记下类型,具体的结构是由游标决定的。

那一个变量的功效域仅仅是在循环体内。

把v_pos看作二个笔录变量就足以了,如果要博取某三个值就如调用记录后生可畏致就足以了。

如v_pos.pos_type

同理可得,for循环是用来循环游标的最佳办法。高效,简洁,安全。

但缺憾的是,平时看见的却是第后生可畏种格局。所以今后得更正这几个习于旧贯了。

3.4 select into不可乎视的标题

笔者们领略在pl/sql中要想从数据表中向变量赋值,须求动用select into 子句。

不过它会带来来部分标题,假设查询未有记录时,会抛出no_data_found异常。

若是有多条记下时,会抛出too_many_rows异常。

其一是非常差的。风流倜傥旦抛出了拾分,就能让进程中断。特别是no_data_found这种极度,未有严重到要让程序中断的境界,能够完全交由由程序开展拍卖。

create or replace procedure procexception(p varchar2)  

as    

 v_postype varchar2(20);  

begin  

  select pos_type into v_postype from pos_type_tbl where 1=0;  

   dbms_output.put_line(v_postype);  

end;  

     

试行那么些历程

SQL> exec procexception('a');  

报错  

ORA-01403: no data found  

ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6  

ORA-06512: at line 1  

管理那么些有多个方法

1. 平素助长极其管理。

create or replace procedure procexception(p varchar2)  

as    

 v_postype varchar2(20);  

   

begin  

  select pos_type into v_postype from pos_type_tbl where 1=0;  

   dbms_output.put_line(v_postype);  

exception    

 when no_data_found then  

   dbms_output.put_line('没找到数据');  

end;  

那样做换汤不换药,程序依旧被中断。可能那样不是我们所想要的。

  1. select into做为多个独门的块,在此个块中进行特别管理

create or replace procedure procexception(p varchar2)  

as    

 v_postype varchar2(20);  

   

begin  

 begin  

  select pos_type into v_postype from pos_type_tbl where 1=0;  

   dbms_output.put_line(v_postype);  

exception    

 when no_data_found then  

   v_postype := '';  

 end;  

 dbms_output.put_line(v_postype);  

end;  

那是生机勃勃种相比较好的管理形式了。不会因为这么些那多少个而滋生程序中断。

3.选拔游标

create or replace procedure procexception(p varchar2)  

as    

 v_postype varchar2(20);  

 cursor c_postype is select pos_type  from pos_type_tbl where 1=0;  

begin  

 open c_postype;  

   fetch c_postype into v_postype;  

 close c_postype;  

 dbms_output.put_line(v_postype);  

end;  

这么就全盘的防止了no_data_found非常。完全交由程序猿来进展调整了。

第二种情状是too_many_rows 万分的难题。

Too_many_rows 这些主题材料比起no_data_found要复杂一些。

给四个变量赋值时,不过查询结果有多少个记录。

管理这种主题材料也会有三种情状:

1. 多条数据是足以承担的,相当于说从结果聚集随意取一个值就行。这种状态相应很极端了吗,纵然现身这种地方,也验证了程序的严俊性存在问题。

2. 多条数据是不能被选用的,在此种景色确定是程序的逻辑出了难点,也说是说原来根本就不会想到它会发出多条记下。

对此第大器晚成种情景,就亟须使用游标来管理,而对于第三种情形就非得接纳当中块来拍卖,重新抛出拾分。

多条数据能够肩负,随意取一条,这些跟no_data_found的管理格局同样,使用游标。

自家这边仅说第二种意况,不可选取多条数据,然则毫无忘了拍卖no_data_found哦。那就不可能采纳游标了,必需接收此中块。

create or replace procedure procexception2(p varchar2)  

as    

 v_postype varchar2(20);  

   

begin  

 begin  

   select pos_type into v_postype from pos_type_tbl where rownum < 5;  

 exception  

   when no_data_found then  

     v_postype :=null;  

   when too_many_rows then  

     raise_application_error(-20000,'对v_postype赋值时,找到多条数据');  

 end;  

dbms_output.put_line(v_postype);  

end;  

亟待介怀的是应当要加多对no_data_found的管理,对现身多条记下的状态则继续抛出非常,让上后生可畏层来管理。

简单来讲对于select into的言语需求在乎这两种情景了。需求安妥管理啊。

3.5 在蕴藏进程中回到结果集

我们采纳存款和储蓄进度都是再次回到值都以单风度翩翩的,一时大家要求从进度中回到贰个成团。即多条数据。那有两种缓和方案。比较简单的做法是写一时表,但是这种做法不灵敏。而且爱慕麻烦。大家得以应用嵌套表来完成.未有二个汇聚类型能够与java的jdbc类型相配。那就是指标与关全面据库的反抗吧。数据库的对象并不可以完全调换为编制程序语言的靶子,还必得使用关周密据库的管理情势。

create or replace package procpkg is  

  type refcursor is ref cursor;  

  procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  

end procpkg;  

 

create or replace package body procpkg is  

 procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  

 is  

   v_posTypeList PosTypeTable;  

 begin  

   v_posTypeList :=PosTypeTable();--开端化嵌套表  

   v_posTypeList.extend;  

   v_posTypeList(1) := PosType('A001','客商资料改换');  

   v_posTypeList.extend;  

   v_posTypeList(2) := PosType('A002','团体资料改换');  

   v_posTypeList.extend;  

   v_posTypeList(3) := PosType('A003','受益人更改');  

   v_posTypeList.extend;  

   v_posTypeList(4) := PosType('A004','续期交费格局更改');  

   open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  

 end;  

end procpkg;  

在常德中定义了二个游标变量,并把它作为存款和储蓄进度的参数类型。

在仓库储存进程中定义了一个嵌套表变量,对数据写进嵌套表中,然后把嵌套表举办类型转变为table,游标变量从这几个嵌套表中打开查询。外界程序调用那些游标。

从而那几个进度必要定义五个品类。

create or replace type PosType as Object (  

 posType varchar2(20),  

 description varchar2(50)  

);  

create or replace type PosTypeTable is table of PosType;

急需潜心,那八个档案的次序无法定义在岳阳中,必得独立定义,那样java层技术使用。

在外界通过pl/sql来调用那个进度非常轻松。

set serveroutput on;  

declare    

 type refcursor is ref cursor;  

 v_ref_postype refcursor;  

 v_postype varchar2(20);  

 v_desc varchar2(50);  

begin  

 procpkg.procrefcursor('a',v_ref_postype);  

 loop  

   fetch  v_ref_postype into v_postype,v_desc;  

   exit when v_ref_postype%notfound;  

   dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);  

 end loop;  

end;  

只顾:对于游标变量,无法使用for循环来管理。因为for循环会隐式的实践open动作。而透过open for来展开的游标%isopen是为true的。也正是暗中认可张开的。Open贰个曾经open的游标是大错特错的。所以不能够使用for循环来管理游标变量。

我们任重先生而道远商量的是怎么通过jdbc调用来管理那些输出参数。

conn = this.getDataSource().getConnection();  

CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");  

call.setString(1, null);  

call.registerOutParameter(2, OracleTypes.CURSOR);  

call.execute();  

ResultSet rsResult = (ResultSet) call.getObject(2);  

while (rsResult.next()) {  

 String posType = rsResult.getString("posType");  

 String description = rsResult.getString("description");  

 ......  

}  

那便是jdbc的拍卖方法。

Ibatis管理方法:

1.参数配置

<parameterMap id="PosTypeMAP" class="java.util.Map">    

<parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />    

<parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />    

</parameterMap>  

 

2.调用经过  

 <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">  

     {call procpkg.procrefcursor(?,?)}  

 </procedure>  

 

3.概念本人的管理器  

 public class CursorHandlerCallBack implements TypeHandler{  

   public Object getResult(CallableStatement cs, int index) throws SQLException {  

       ResultSet rs = (ResultSet)cs.getObject(index);  

       List result = new ArrayList();  

       while(rs.next()) {  

           String postype =rs.getString(1);  

           String description = rs.getString(2);  

           CodeTableItemDTO posTypeItem = new CodeTableItemDTO();  

           posTypeItem.setCode(postype);  

           posTypeItem.setDescription(description);  

           result.add(posTypeItem);  

       }  

       return result;  

   }  

 

 

 

  1. dao方法  

   public List procPostype() {  

       String p = "";  

       Map para = new HashMap();  

       para.put("p",p);  

       para.put("p_ref_postypeList",null);  

        this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);  

        return (List)para.get("p_ref_postypeList");  

   }  

以此跟jdbc的法子丰盛的近似.

笔者们利用的是ibatis的2.0本子,相比困苦。

借使是采纳2.2上述版本就非常简单的。

因为能够在parameterMap中定义贰个resultMap.那样就不须求要协调定义管理器了。

能够从解析2.0和2.0的dtd文件知道。

地点的二种方法都以不行的目眩神摇,如若生龙活虎味是内需重临八个结果集,那就全盘能够使用函数来落实了。

create or replace package procpkg is  

  type refcursor is ref cursor;  

  procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  

  function procpostype(p varchar2) return PosTypeTable;    

end procpkg;  

 

create or replace package body procpkg is  

 procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  

 is  

   v_posTypeList PosTypeTable;  

 begin  

   v_posTypeList :=PosTypeTable();--伊始化嵌套表  

   v_posTypeList.extend;  

澳门金莎娱乐手机版,   v_posTypeList(1) := PosType('A001','顾客资料改动');  

   v_posTypeList.extend;  

   v_posTypeList(2) := PosType('A002','团体资料更换');  

   v_posTypeList.extend;  

   v_posTypeList(3) := PosType('A003','受益人改变');  

   v_posTypeList.extend;  

   v_posTypeList(4) := PosType('A004','续期交费情势改换');  

   open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  

 end;  

 

 function procpostype(p varchar2) return PosTypeTable  

 as  

  v_posTypeList PosTypeTable;  

 begin  

     v_posTypeList :=PosTypeTable();--早先化嵌套表  

   v_posTypeList.extend;  

   v_posTypeList(1) := PosType('A001','客商资料改造');  

   v_posTypeList.extend;  

   v_posTypeList(2) := PosType('A002','团体资料更换');  

   v_posTypeList.extend;  

   v_posTypeList(3) := PosType('A003','收益人更动');  

   v_posTypeList.extend;  

   v_posTypeList(4) := PosType('A004','续期交费格局更改');  

   return  v_posTypeList;  

 end;  

end procpkg;  

ibatis配置

<resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">  

  <result property="code" column="posType"/>  

  <result property="description" column="description"/>  

</resultMap>  

 

 <select id="procPostype" resultMap="posTypeResultMap">  

   select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))  

 </select>  

Dao的写法跟日常查询同生龙活虎

public List queryPostype() {  

 return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);  

}  

有几点须求专心,这里不能使用索引表,而是嵌套表。

除此以外就是把嵌套表强制转变为普通表。

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:使用游标过程中出现的错误,Oracle存储过程使用

关键词:

查询各分类中最大自增ID,百度音乐接口的使用

假如现在有表结构: 百度公开有一个音乐接口,里面分好了频道类别。下面是3个接口的一个url地址。 查找事物处理来...

详细>>

交叉连接,mysql数据库操作

1.连接 有时候需要将连个表的数据合并成一个结果集来显示。为了解决这个问题,就需要用到JOIN连接。   mysql数据库操...

详细>>

Yum编译安装MySQL,MySQL数据库基于Centos7【澳门金莎

一、MySQL数据库的官方网址: https://www.oracle.com/ http://dev.mysql.com/doc/refman/5.7/en/linux-installation.html       //不同版本的...

详细>>

王者荣耀每天可以拿多少竞技值,值替换为指定

转自:http://www.maomao365.com/?p=6965   英雄战迹为啥小编分享活动后未有获取比赛值? 时间:2018-12-05 勇气竞赛场:5v5比赛...

详细>>