金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Server存储过程,使用游标过程中出现的错误

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

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-06 03:22

上面包车型地铁观点是在选择游标的经过中做的日志。笔者也是率先次使用,假诺有啥样难堪的地点请探究指正,大家一块儿全力。

  一,定义:Sql Server的蕴藏进度是二个被取名的蕴藏在服务器上的Transacation-Sql语句集合,是包裹重复性工作的一种方法.

oracle存款和储蓄进度常用手艺

大家在实行pl/sql编制程序时打交道最多的就是积存进程了。存款和储蓄进程的协会是这几个的回顾的,大家在此地除了学习存款和储蓄进度的中央结构外,还有恐怕会学习编写存款和储蓄进程时有关的有个别实用的学识。如:游标的拍卖,十分的管理,集合的抉择等等 

1.

  二,存款和储蓄进程的优点:

咱俩在拓宽pl/sql编制程序时打交道最多的正是积存进程了。存储进程的构造是非常的简约的,大家在此地除了学习存款和储蓄进程的骨干结构外,还有只怕会学习编写存款和储蓄进度时有关的部分实用的知识。如:游标的管理,非凡的管理,集结的选料等等

1.积攒进度结构 
1.1 第4个存储进度 

  消息 16951,级别 16,状态 1,过程 usp_proc,第 16 行
      变量 '@myref' 不能够用作参数,因为在推行该进度前,不得为 CU锐界SOXC60 OUTPUT 参数分配游标。

   1,重复使用。存款和储蓄进度能够重复使用,进而得以减小数据库开荒人士的工作量。

1.囤积进度结构

  1. create or replace procedure proc1(   
  2.   p_para1 varchar2,   
  3.   p_para2 out varchar2,   
  4.   p_para3 in out varchar2   
  5. )as    
  6.  v_name varchar2(20);   
  7. begin   
  8.   v_name := '张三丰';   
  9.   p_para3 := v_name;   
  10.   dbms_output.put_line('p_para3:'||p_para3);   
  11. end;  

  这么些主题材料是自身在调用三个递归的、输出cursor output 的积攒进度

   2,提升质量。存款和储蓄进度在开立的时候就开展了编写翻译,未来使用的时候绝不再重复编写翻译。一般的SQL语句每实践一回就必要编写翻译一次,所以使用存款和储蓄进程提升了功效。

1.1 第二个存款和储蓄过程

下面就是二个最轻便易行的积累过程。三个储存进程差异常少分为这么多少个部分: 
创立语句: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关键字结果。 

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            

     3,收缩互联网流量。存款和储蓄进程位于服务器上,调用的时候只须求传递存款和储蓄进程的名称以及参数就足以了,由此裁减了互连网传输的数据量。

create or replace procedure proc1(  

1.2 存款和储蓄进度的参数字传送递情势 
仓库储存进度的参数字传送递有二种格局:IN,OUT,IN OUT . 
IN 按值传递,何况它分歧目的在于蕴藏进度中被另行赋值。即使存储进程的参数未有一点名存参数字传送递类型,默以为IN 

设若未有对输出的游标做close、deallocate管理就能冒出上面错误。

   4,安全性。参数化的存储进度能够免止SQL注入式的攻击,并且能够将Grant、Deny以及Revoke权限应用于积累进程。

 p_para1 varchar2,  

  1. create or replace procedure proc1(   
  2.   p_para1 varchar2,   
  3.   p_para2 out varchar2,   
  4.   p_para3 in out varchar2   
  5. )as    
  6.  v_name varchar2(20);   
  7. begin   
  8.   p_para1 :='aaa';   
  9.   p_para2 :='bbb';   
  10.   v_name := '张三丰';   
  11.   p_para3 := v_name;   
  12.   dbms_output.put_line('p_para3:'||p_para3);   
  13.   null;   
  14. end;   
  15.        
  16. Warning: Procedure created with compilation errors   
  17.   
  18. SQL> show error;   
  19. Errors for PROCEDURE LIFEMAN.PROC1:   
  20.   
  21. LINE/COL ERROR   
  22. -------- ----------------------------------------------------------------------   
  23. 8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target   
  24. 8/3      PL/SQL: Statement ignored  

2.

  三,语法,创制存款和储蓄过程:  

 p_para2 out varchar2,  

那点与其余高档语言都不可同日而语。它一定于java在参数前边加上final关键字。 

  未有为@cur,分配游标

语法

 p_para3 in out varchar2  

OUT 参数:作为出口参数,须要注意,当二个参数被钦定为OUT类型时,固然在调用存款和储蓄进度从前对该参数实行了赋值,在积存进程中该参数的值仍旧是null. 

  这一个难点是本人在运用存款和储蓄进程再次来到的游标 cursor output 发生的

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 语句中得以声多美滋(Aptamil)个或八个参数。客商必须在施行进程时提供每一个所注解参数的值(除非定义了该参数的暗中认可值,恐怕该值设置为等于另二个参数)。存储进程最多能够有 2.100 个参数。

运用 @ 符号作为第一个字符来钦命参数名称。参数名称必需符合标记符的条条框框。各类进度的参数仅用于该进程自个儿;同样的参数名称可以用在其余进程中。暗许意况下,参数只可以取代常量,而无法用于替代表名、列名或别的数据库对象的名目。

data_type

    参数的数据类型。除 table 之外的别的全体数据类型均能够当作存款和储蓄进程的参数。然而,cursor 数据类型只能用来 OUTPUT 参数。若是钦赐 cursor 数据类型,则还必需钦定VA途观YING 和 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 语句文本的条规。使用 ENCOdysseyYPTION 可幸免将经过作为 SQL Server 复制的一局地发表。

FOR REPLICATION

    钦赐不能够在订阅服务器上实行为复制创设的囤积进度。.使用 FO中华V 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

实践结果

        金沙棋牌app手机下载 1

2.基于传入的参数实行询问并回到一个数据集

       CREATE PROCEDURE mysp_para
            @CityName varchar(255),

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

进行结果

        金沙棋牌app手机下载 2

3.包蕴输出参数的囤积进度(重返前两条记下的ID的和)

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

实践结果

         金沙棋牌app手机下载 3

4.在存款和储蓄进程中采用游标

  有这么一个表,存款和储蓄的是各超阶级市上面包车型客车县级市的消息.如图:

   金沙棋牌app手机下载 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

 

施行结果

       金沙棋牌app手机下载 5

 

    好了,关于存款和储蓄进程先写到这里.以上多少个例证基本上完成了平凡所用到的大部成效.至于复杂的仓储进程,所用到的明亮根本是SQL的语法,以及SQL中放到函数的使用.已不属于本文所要研究的限定了.

)as    

  1. create or replace procedure proc1(   
  2.   p_para1 varchar2,   
  3.   p_para2 out varchar2,   
  4.   p_para3 in out varchar2   
  5. )as    
  6.  v_name varchar2(20);   
  7. begin   
  8.   v_name := '张三丰';   
  9.   p_para3 := v_name;   
  10.   dbms_output.put_line('p_para1:'||p_para1);   
  11.   dbms_output.put_line('p_para2:'||p_para2);   
  12.   dbms_output.put_line('p_para3:'||p_para3);   
  13. end;   
  14.   
  15. SQL> var p1 varchar2(10);   
  16. SQL> var p2 varchar2(10);   
  17. SQL> var p3 varchar2(10);   
  18. SQL> exec :p1 :='aaaa';   
  19. SQL> exec :p2 :='bbbb';   
  20. SQL> exec :p3 :='cccc';   
  21. SQL> exec proc1(:p1,:p2,:p3);   
  22. p_para1:aaaa   
  23. p_para2:   
  24. p_para3:张三丰   
  25. SQL> exec dbms_output.put_line(:p2);   
  26.   
  27.   
  28. PL/SQL procedure successfully completed   
  29. p2   
  30. ---------  

  

v_name varchar2(20);  

INOUT 是实在的按援引传递参数。就可以作为传播参数也得以看做传播参数。 

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  

  1. 1.3 存款和储蓄进程参数宽度   
  2. create or replace procedure proc1(   
  3.   p_para1 varchar2,   
  4.   p_para2 out varchar2,   
  5.   p_para3 in out varchar2   
  6. )as    
  7.  v_name varchar2(2);   
  8. begin   
  9.   v_name := p_para1;   
  10. end;   
  11.   
  12. SQL> var p1 varchar2(10);   
  13. SQL> var p2 varchar2(20);   
  14. SQL> var p3 varchar2(30);   
  15. SQL> exec :p1 :='aaaaaa';   
  16. SQL> exec proc1(:p1,:p2,:p3);   
  17.        
  18.        
  19. ORA-06502: PL/SQL: numeric or value error: character string buffer too small   
  20. ORA-06512: at "LIFEMAN.PROC1", line 8  
  21. ORA-06512: at line 1  

出现上述错的来由便是概念游标后要求开荒 open @mycur

 v_name := '张三丰';  

第一,大家要驾驭,大家鞭长莫及在蕴藏进度的定义中钦点期存款款和储蓄参数的增加率,也就招致了我们力无法支在仓库储存进程中决定传入变量的宽度。这么些增长幅度是全然由外界传入时间调控制的。 
小编们再来看看OUT类型的参数的增长幅度。 

 p_para3 := v_name;  

  1. create or replace procedure proc1(   
  2.   p_para1 varchar2,   
  3.   p_para2 out varchar2,   
  4.   p_para3 in out varchar2   
  5. )as    
  6.  v_name varchar2(2);   
  7. begin   
  8.   p_para2 :='aaaaaaaaaaaaaaaaaaaa';   
  9. end;   
  10. SQL> var p1 varchar2(1);   
  11. SQL> var p2 varchar2(1);   
  12. SQL> var p3 varchar2(1);   
  13. SQL> exec :p2 :='a';   
  14. SQL> exec proc1(:p1,:p2,:p3);  

 dbms_output.put_line('p_para3:'||p_para3);  

在该进程中,p_para2被予以了二十个字符a. 
而在外表的调用进程中,p2这几个参数仅仅被定义为varchar2(1). 
而把p2作为参数调用这些进度,却并未报错。何况它的真实值便是二十一个a 

end;  

  1. SQL> select dump(:p2) from dual;   
  2. DUMP(:P2)   

  3.   
  4. Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97  
  5. p2   
  6. ---------   
  7. aaaaaaaaaaaaaaaaaaaa   
  8.        
  9.     再来看看IN OUT参数的大幅度   
  10. create or replace procedure proc1(   
  11.   p_para1 varchar2,   
  12.   p_para2 out varchar2,   
  13.   p_para3 in out varchar2   
  14. )as    
  15.  v_name varchar2(2);   
  16. begin   
  17.   p_para3 :='aaaaaaaaaaaaaaaaaaaa';   
  18. end;   
  19.   
  20. SQL> var p1 varchar2(1);   
  21. SQL> var p2 varchar2(1);   
  22. SQL> var p3 varchar2(1);   
  23. SQL> exec proc1(:p1,:p2,:p3);  

上边便是贰个最简易的仓库储存进程。四个囤积进程大概分为这么多少个部分:

推行这几个过程,照旧精确奉行。 

创立语句:create or replace procedure 存储进程名

足见,对于IN参数,其调幅是由外界调整。 
对于OUT 和IN OUT 参数,其调幅是由存款和储蓄进程之中央调整制。 
就此,在写存款和储蓄进度时,对参数的宽度进行求证是特别有不可缺少的,最明智的法子便是参数的数据类型使用%type。那样双方就到达了一样。 

假若未有or replace语句,则独自是新建三个仓库储存进程。假使系统设有该存款和储蓄进程,则会报错。Create or replace procedure 纵然系统中尚无此存款和储蓄进程就新建贰个,倘若系统中有此存款和储蓄进度则把原本删除掉,重新创立二个仓储进程。

1.3 参数的暗中同意值 
存款和储蓄进程的参数能够设置暗中同意值 

仓库储存进程名定义:包涵存款和储蓄进度名和参数列表。参数名和参数类型。参数名不可能重新, 参数字传送递格局:IN, OUT, IN OUT

  1. create or replace procedure procdefault(p1 varchar2,   
  2.                                         p2 varchar2 default 'mark')   
  3. as    
  4. begin   
  5.   dbms_output.put_line(p2);   
  6. end;   
  7.   
  8. SQL> set serveroutput on;   
  9. SQL> exec procdefault('a');  

IN 代表输入参数,按值传递格局。

mark 
可以透过default 关键字为存款和储蓄进度的参数钦命暗许值。在对存款和储蓄进度调用时,就能够省略默许值。 
亟待静心的是:暗中认可值仅仅帮忙IN传输类型的参数。OUT 和 IN OUT不能够内定暗中同意值 

OUT 代表输出参数,可以清楚为按援引传递格局。能够当作存款和储蓄进度的出口结果,供外界调用者使用。

对于有暗许值的参数不是排在最终的场所。 

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

  1. create or replace procedure procdefault2(p1 varchar2 default 'remark',   
  2.                                         p2 varchar2 )   
  3. as    
  4. begin   
  5.   dbms_output.put_line(p1);   
  6. end;  

参数的数据类型只要求指明类型名就可以,没有要求钦命宽度。

率先个参数有暗许值,第三个参数未有。假使我们想行使第三个参数的默许值时 
exec procdefault2('aa'); 
这么是会报错的。 
那怎么变呢?能够钦赐参数的值。 

参数的宽度由外界调用者决定。

  1. SQL> exec procdefault2(p2 =>'aa');  

经过能够有参数,也足以未有参数

remark 
那般就OK了,内定aa传给参数p2 

变量注明块:紧跟着的as (is )关键字,能够领会为pl/sql的declare关键字,用于证明变量。

  1. 仓库储存进度里面块 
    2.1 内部块 
    我们精通了积累进程的构造,语句块由begin发轫,以end截至。这么些块是能够嵌套。在语句块中得以嵌套任何以下的块。 

  2. Declare … begin … exception … end;   

  3. create or replace procedure innerBlock(p1 varchar2)   
  4. as    
  5.   o1 varchar2(10) := 'out1';   
  6. begin   
  7.   dbms_output.put_line(o1);   
  8.   declare    
  9.     inner1 varchar2(20);   
  10.   begin   
  11.     inner1 :='inner1';   
  12.     dbms_output.put_line(inner1);   
  13.   
  14.     declare    
  15.       inner2 varchar2(20);   
  16.     begin   
  17.       inner2 := 'inner2';   
  18.       dbms_output.put_line(inner2);   
  19.     end;   
  20.   exception    
  21.     when others then   
  22.       null;   
  23.   end;   
  24. end;  

变量评释块用于证明该存款和储蓄进度必要动用的变量,它的效能域为该存款和储蓄进度。别的这里注解的变量必得钦点宽度。遵守PL/SQL的变量申明规范。

亟需注意变量的效用域。 

经过语句块:从begin 关键字起首为经过的语句块。存款和储蓄进度的切切实实逻辑在那边来兑现。

3.存款和储蓄进度的常用技术 
3.1 哪类群集? 
我们在行使存款和储蓄进度的时候日常必要管理记录集,相当于多条数据记录。分为单列多行和多列多行,这一个体系都堪称集结类型。大家在此间开展相比较那一个集中类型,以便于在编制程序时做出精确的选拔。 
索引表,也堪当pl/sql表,不能积累于数据库中,成分的个数未有界定,下标可认为负值。 

非常管理块:关键字为exception ,为管理语句发生的要命。该部分为可选

  1. type t_table is table of varchar2(20) index by binary_integer;   
  2.  v_student t_table;  

停止块:由end关键字结果。

varchar2(20)表示贮存成分的数据类型,binary_integer代表成分下标的数据类型。 
嵌套表,索引表未有 index by子句正是嵌套表,它能够寄放于数据中,元素个数Infiniti,下标从1开头,并且要求早先化 

1.2 存款和储蓄进度的参数字传送递情势

  1. type t_nestTable is table of varchar2(20);   
  2. v_class t_nestTable ;  

累积进度的参数字传送递有二种艺术:IN,OUT,IN OUT .

仅是这么申明是不可能选取的,必需对嵌套表进行伊始化,对嵌套表实行开始化能够利用它的布局函数 

IN 按值传递,何况它分歧目的在于蕴藏进程中被再一次赋值。如若存款和储蓄进度的参数未有一点名存参数字传送递类型,默以为IN

  1. v_class :=t_nestTable('a','b','c');  

create or replace procedure proc1(  

变长数组,变长数组与高级语言的数组类型特别相似,下标以1起来,成分个数有限。 

 p_para1 varchar2,  

  1. type t_array is varray (20) of varchar2(20);  

 p_para2 out varchar2,  

varray(20)就定义了变长数组的最大体素个数是18个 
变长数组与嵌套表一样,也得以是数据表列的数据类型。 
再者,变长数组的选择也亟需事先初叶化。 

 p_para3 in out varchar2  

项目 可存款和储蓄于数据库 成分个数 是还是不是需伊始化 开头下标值 
索引表 否 无限 不需 
嵌套表 可 无限 需 1 
可变数组 可 有限(自定义) 需 1 

)as    

综上说述,借使一味是在积累进程中作为集结变量使用,索引表是最好的选择。 

v_name varchar2(20);  

3.2 接纳何种游标? 
来得游标分为:普通游标,参数化游标和游标变量二种。 
上边以二个进度来进展表达 

begin  

  1. create or replace procedure proccursor(p varchar2)   
  2. as    
  3. v_rownum number(10) := 1;   
  4. cursor c_postype is select pos_type from pos_type_tbl where rownum =1;   
  5. cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;   
  6. cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;   
  7. type t_postype is ref cursor ;   
  8. c_postype3 t_postype;   
  9. v_postype varchar2(20);   
  10. begin   
  11.   open c_postype;   
  12.   fetch c_postype into v_postype;   
  13.   dbms_output.put_line(v_postype);   
  14.   close c_postype;   
  15.   open c_postype1;   
  16.   fetch c_postype1 into v_postype;   
  17.   dbms_output.put_line(v_postype);   
  18.   close c_postype1;   
  19.   open c_postype2(1);   
  20.   fetch c_postype2 into v_postype;   
  21.   dbms_output.put_line(v_postype);   
  22.   close c_postype2;   
  23.   open c_postype3 for select pos_type from pos_type_tbl where rownum =1;   
  24.   fetch c_postype3 into v_postype;   
  25.   dbms_output.put_line(v_postype);   
  26.   close c_postype3;   
  27. end;  

 p_para1 :='aaa';  

cursor c_postype is select pos_type from pos_type_tbl where rownum =1 
这一句是概念了叁个最常见的游标,把任何查询已经写死,调用时不能作其余改变。 
cursor c_postype1 is select pos_type from pos_金沙棋牌app手机下载,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不通用。 

 p_para2 :='bbb';  

3.3 游标循环最好计谋 
大家在展开PL/SQL编制程序时,平时须求循环读取结果集的数额。进行逐行管理,那一个进程就必要对游标进行巡回。对游标进行巡回的法子有各个,大家在此一一分析。 

 v_name := '张三丰';  

  1. create or replace procedure proccycle(p varchar2)   
  2. as    
  3. cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;   
  4. v_postype varchar2(20);   
  5. v_description varchar2(50);   
  6. begin   
  7. open c_postype;   
  8.   if c_postype%found then   
  9.     dbms_output.put_line('found true');   
  10.   elsif c_postype%found = false then   
  11.     dbms_output.put_line('found false');   
  12.   else  
  13.     dbms_output.put_line('found null');   
  14.   end if;   
  15.   loop   
  16.    fetch c_postype into v_postype,v_description ;   
  17.    exit when c_postype%notfound;   
  18.    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
  19.   end loop;   
  20.   close c_postype;   
  21. dbms_output.put_line('---loop end---');   
  22.   open c_postype;   
  23.     fetch c_postype into v_postype,v_description;   
  24.     while c_postype%found loop   
  25.       dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
  26.       fetch c_postype into v_postype,v_description ;   
  27.     end loop;   
  28.   
  29.   close c_postype;   
  30. dbms_output.put_line('---while end---');   
  31.   for v_pos in c_postype loop   
  32.     v_postype := v_pos.pos_type;   
  33.     v_description := v_pos.description;   
  34.     dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
  35.   end loop;   
  36.   dbms_output.put_line('---for end---');   
  37. end;  

 p_para3 := v_name;  

接纳游标此前须要开打游标,open cursor,循环完后再关闭游标close cursor. 
那是行使游标应该慎记于心的准绳。 
地点的进程演示了游标循环的二种艺术。 
在商议循环方法在此之前,大家先看看%found和%notfound那些游标的习性。 

 dbms_output.put_line('p_para3:'||p_para3);  

  1. open c_postype;   
  2.  if c_postype%found then   
  3.    dbms_output.put_line('found true');   
  4.  elsif c_postype%found = false then   
  5.    dbms_output.put_line('found false');   
  6.  else  
  7.    dbms_output.put_line('found null');   
  8.  end if;  

 null;  

在展开多个游标之后,立即检查它的%found或%notfound属性,它得到的结果即不是true亦不是false.而是null.必需实行一条fetch语句后,那些属性才有值。 

end;  

第一种采纳loop 循环 

     

  1. loop   
  2.    fetch c_postype into v_postype,v_description ;   
  3.    exit when c_postype%notfound;   
  4.    ……   
  5. end loop  

Warning: Procedure created with compilation errors  

此地必要留心,exit when语句必供给紧跟在fetch之后。必防止多余的多寡管理。 
管理逻辑须求跟在exit when之后。这点须要多加小心。 
巡回停止后要记得关闭游标。 

 

其次种采取while循环。 

SQL> show error;  

  1.    fetch c_postype into v_postype,v_description;   
  2. while c_postype%found loop   
  3.    ……   
  4.       fetch c_postype into v_postype,v_description ;   
  5. end loop;  

Errors for PROCEDURE LIFEMAN.PROC1:  

作者们领略了多个游标张开后,必得实践一次fetch语句,游标的特性才会起功效。所以利用while 循环时,就需求在循环在此之前开展二遍fetch动作。 
再者数量管理动作必得放在循环体内的fetch方法以前。循环体内的fetch方法要放在最终。不然就可以多管理三回。那或多或少也要特别的小心。 
一句话来讲,使用while来循环处理游标是最复杂的方法。 

 

第三种 for循环 

LINE/COL ERROR  

  1. for v_pos in c_postype loop   
  2.    v_postype := v_pos.pos_type;   
  3.    v_description := v_pos.description;   
  4.    …   
  5.  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这种特别,未有严重到要让程序中断的地步,能够完全交给由程序举行拍卖。 

8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target  

  1. create or replace procedure procexception(p varchar2)   
  2. as    
  3.   v_postype varchar2(20);   
  4. begin   
  5.    select pos_type into v_postype from pos_type_tbl where 1=0;   
  6.     dbms_output.put_line(v_postype);   
  7. end;   
  8.       

8/3      PL/SQL: Statement ignored  

实践这么些进度 

那或多或少与任何高档语言都不及。它也等于java在参数前边加上final关键字。

  1. SQL> exec procexception('a');   
  2. 报错   
  3. ORA-01403: no data found   
  4. ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6  
  5. ORA-06512: at line 1  

OUT 参数:作为出口参数,需求专一,当一个参数被钦点为OUT类型时,即使在调用存款和储蓄进度在此以前对该参数举行了赋值,在仓库储存进度中该参数的值如故是null.

拍卖这么些有多个措施 
1. 一向抬Gott别管理。 

create or replace procedure proc1(  

  1. create or replace procedure procexception(p varchar2)   
  2. as    
  3.   v_postype varchar2(20);   
  4.      
  5. begin   
  6.    select pos_type into v_postype from pos_type_tbl where 1=0;   
  7.     dbms_output.put_line(v_postype);   
  8. exception    
  9.   when no_data_found then   
  10.     dbms_output.put_line('没找到数据');   
  11. end;  

 p_para1 varchar2,  

如此那般做换汤不换药,程序如故被中断。或然这么不是我们所想要的。 

 p_para2 out varchar2,  

  1. select into做为一个独立的块,在那几个块中张开丰硕管理 

  2. create or replace procedure procexception(p varchar2)   

  3. as    
  4.   v_postype varchar2(20);   
  5.      
  6. begin   
  7.   begin   
  8.    select pos_type into v_postype from pos_type_tbl where 1=0;   
  9.     dbms_output.put_line(v_postype);   
  10.  exception    
  11.   when no_data_found then   
  12.     v_postype := '';   
  13.   end;   
  14.   dbms_output.put_line(v_postype);   
  15. end;  

 p_para3 in out varchar2  

那是一种比较好的管理格局了。不会因为那几个特别而孳生程序中断。 
3.行使游标 

)as    

  1. create or replace procedure procexception(p varchar2)   
  2. as    
  3.   v_postype varchar2(20);   
  4.   cursor c_postype is select pos_type  from pos_type_tbl where 1=0;   
  5. begin   
  6.   open c_postype;   
  7.     fetch c_postype into v_postype;   
  8.   close c_postype;   
  9.   dbms_output.put_line(v_postype);   
  10. end;  

v_name varchar2(20);  

这般就全盘的防止了no_data_found格外。完全交由程序猿来拓宽支配了。 

begin  

第二种境况是too_many_rows 十分的主题材料。 
Too_many_rows 这些难题比起no_data_found要复杂一些。 
给一个变量赋值时,可是查询结果有七个记录。 
管理这种难题也是有三种状态: 
1. 多条数据是足以接受的,也便是说从结果集中随意取二个值就行。这种状态应该很极端了啊,假如出现这种情景,也印证了程序的严苛性存在难点。 
2. 多条数据是不得以被接受的,在这种境况自然是程序的逻辑出了难点,也说是说原本根本就不会想到它会发出多条记下。 
对此第一种情况,就务须利用游标来拍卖,而对此第三种处境就非得采纳当中块来拍卖,重新抛出极度。 
多条数据足以承受,随意取一条,那个跟no_data_found的管理格局同样,使用游标。 
自己那边仅说第二种情状,不可承受多条数据,然则绝不忘了拍卖no_data_found哦。那就不能够利用游标了,必需利用在那之中块。 

 v_name := '张三丰';  

  1. create or replace procedure procexception2(p varchar2)   
  2. as    
  3.   v_postype varchar2(20);   
  4.     
  5. begin   
  6.   begin   
  7.     select pos_type into v_postype from pos_type_tbl where rownum < 5;   
  8.   exception   
  9.     when no_data_found then   
  10.       v_postype :=null;   
  11.     when too_many_rows then   
  12.       raise_application_error(-20000,'对v_postype赋值时,找到多条数据');   
  13.   end;   
  14.  dbms_output.put_line(v_postype);   
  15. end;  

 p_para3 := v_name;  

亟需注意的是自然要加上对no_data_found的拍卖,对出现多条记下的事态则一连抛出万分,让上一层来管理。 
简单的讲对于select into的言辞供给稳重那二种景况了。供给稳当管理啊。 

 dbms_output.put_line('p_para1:'||p_para1);  

3.5 在积存进度中回到结果集 
我们使用存款和储蓄进度都是重回值都是单纯的,有的时候大家必要从进度中回到三个集中。即多条数据。那有三种减轻方案。比较简单的做法是写一时表,不过这种做法不利索。并且爱戴麻烦。大家得以接纳嵌套表来达成.没有三个成团类型能够与java的jdbc类型匹配。那就是目的与关周到据库的抵御吧。数据库的指标并不可见统统转变为编制程序语言的靶子,还必需运用关全面据库的管理格局。 

 dbms_output.put_line('p_para2:'||p_para2);  

  1. create or replace package procpkg is   
  2.    type refcursor is ref cursor;   
  3.    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);   
  4. end procpkg;   
  5.   
  6. create or replace package body procpkg is   
  7.   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)   
  8.   is   
  9.     v_posTypeList PosTypeTable;   
  10.   begin   
  11.     v_posTypeList :=PosTypeTable();--初叶化嵌套表   
  12.     v_posTypeList.extend;   
  13.     v_posTypeList(1) := PosType('A001','顾客资料退换');   
  14.     v_posTypeList.extend;   
  15.     v_posTypeList(2) := PosType('A002','团体资料更换');   
  16.     v_posTypeList.extend;   
  17.     v_posTypeList(3) := PosType('A003','收益人退换');   
  18.     v_posTypeList.extend;   
  19.     v_posTypeList(4) := PosType('A004','续期交费格局更改');   
  20.     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));   
  21.   end;   
  22. end procpkg;  

 dbms_output.put_line('p_para3:'||p_para3);  

在海口中定义了八个游标变量,并把它当做存款和储蓄进度的参数类型。 
在积攒进度中定义了贰个嵌套表变量,对数据写进嵌套表中,然后把嵌套表举行类型转变为table,游标变量从那个嵌套表中进行查询。外界程序调用这么些游标。 
所以那一个进度要求定义四个等级次序。 

end;  

  1. create or replace type PosType as Object (   
  2.   posType varchar2(20),   
  3.   description varchar2(50)   
  4. );  

 

create or replace type PosTypeTable is table of PosType; 
急需稳重,那八个品类不能够定义在常德中,必得独立定义,这样java层技艺应用。 

SQL> var p1 varchar2(10);  

在表面通过pl/sql来调用那个进程特别轻易。 

SQL> var p2 varchar2(10);  

  1. set serveroutput on;   
  2. declare    
  3.   type refcursor is ref cursor;   
  4.   v_ref_postype refcursor;   
  5.   v_postype varchar2(20);   
  6.   v_desc varchar2(50);   
  7. begin   
  8.   procpkg.procrefcursor('a',v_ref_postype);   
  9.   loop   
  10.     fetch  v_ref_postype into v_postype,v_desc;   
  11.     exit when v_ref_postype%notfound;   
  12.     dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);   
  13.   end loop;   
  14. end;  

SQL> var p3 varchar2(10);  

小心:对于游标变量,不可能利用for循环来管理。因为for循环会隐式的奉行open动作。而经过open for来展开的游标%isopen是为true的。约等于暗中同意展开的。Open三个业已open的游标是不当的。所以不能够动用for循环来管理游标变量。 

SQL> exec :p1 :='aaaa';  

咱俩入眼钻探的是哪些通过jdbc调用来拍卖这几个输出参数。 

SQL> exec :p2 :='bbbb';  

  1. conn = this.getDataSource().getConnection();   
  2. CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");   
  3. call.setString(1, null);   
  4. call.registerOutParameter(2, OracleTypes.CURSOR);   
  5. call.execute();   
  6. ResultSet rsResult = (ResultSet) call.getObject(2);   
  7. while (rsResult.next()) {   
  8.   String posType = rsResult.getString("posType");   
  9.   String description = rsResult.getString("description");   
  10.   ......   
  11. }  

SQL> exec :p3 :='cccc';  

那正是jdbc的管理情势。 

SQL> exec proc1(:p1,:p2,:p3);  

Ibatis管理办法: 
1.参数配置 

p_para1:aaaa  

  1. <parameterMap id="PosTypeMAP" class="java.util.Map">    
  2.  <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />    
  3.  <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />    
  4. </parameterMap>   
  5.   
  6. 2.调用经过   
  7.   <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">   
  8.       {call procpkg.procrefcursor(?,?)}   
  9.   </procedure>   
  10.   
  11. 3.定义自身的管理器   
  12.   public class CursorHandlerCallBack implements TypeHandler{   
  13.     public Object getResult(CallableStatement cs, int index) throws SQLException {   
  14.         ResultSet rs = (ResultSet)cs.getObject(index);   
  15.         List result = new ArrayList();   
  16.         while(rs.next()) {   
  17.             String postype =rs.getString(1);   
  18.             String description = rs.getString(2);   
  19.             CodeTableItemDTO posTypeItem = new CodeTableItemDTO();   
  20.             posTypeItem.setCode(postype);   
  21.             posTypeItem.setDescription(description);   
  22.             result.add(posTypeItem);   
  23.         }   
  24.         return result;   
  25.     }   
  26.   
  27.   
  28.   
  29. 4. dao方法   
  30.     public List procPostype() {   
  31.         String p = "";   
  32.         Map para = new HashMap();   
  33.         para.put("p",p);   
  34.         para.put("p_ref_postypeList",null);   
  35.          this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);   
  36.          return (List)para.get("p_ref_postypeList");   
  37.     }  

p_para2:  

其一跟jdbc的办法特别的相似. 
咱俩选拔的是ibatis的2.0版本,相比较麻烦。 
万一是应用2.2以上版本就非常轻便的。 
因为能够在parameterMap中定义一个resultMap.那样就没有须求要团结定义管理器了。 
能够从解析2.0和2.0的dtd文件知道。 

p_para3:张三丰  

上边的二种艺术都以老大的千头万绪,假若一味是亟需回到一个结实集,那就完全能够运用函数来促成了。 

SQL> exec dbms_output.put_line(:p2);  

  1. create or replace package procpkg is   
  2.    type refcursor is ref cursor;   
  3.    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);   
  4.    function procpostype(p varchar2) return PosTypeTable;    
  5. end procpkg;   
  6.   
  7. create or replace package body procpkg is   
  8.   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)   
  9.   is   
  10.     v_posTypeList PosTypeTable;   
  11.   begin   
  12.     v_posTypeList :=PosTypeTable();--初步化嵌套表   
  13.     v_posTypeList.extend;   
  14.     v_posTypeList(1) := PosType('A001','顾客资料更改');   
  15.     v_posTypeList.extend;   
  16.     v_posTypeList(2) := PosType('A002','团体资料更动');   
  17.     v_posTypeList.extend;   
  18.     v_posTypeList(3) := PosType('A003','收益人改造');   
  19.     v_posTypeList.extend;   
  20.     v_posTypeList(4) := PosType('A004','续期交费形式退换');   
  21.     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));   
  22.   end;   
  23.   
  24.   function procpostype(p varchar2) return PosTypeTable   
  25.   as   
  26.    v_posTypeList PosTypeTable;   
  27.   begin   
  28.       v_posTypeList :=PosTypeTable();--初步化嵌套表   
  29.     v_posTypeList.extend;   
  30.     v_posTypeList(1) := PosType('A001','客商资料改换');   
  31.     v_posTypeList.extend;   
  32.     v_posTypeList(2) := PosType('A002','团体资料更换');   
  33.     v_posTypeList.extend;   
  34.     v_posTypeList(3) := PosType('A003','收益人改变');   
  35.     v_posTypeList.extend;   
  36.     v_posTypeList(4) := PosType('A004','续期交费格局更动');   
  37.     return  v_posTypeList;   
  38.   end;   
  39. end procpkg;  

 

ibatis配置 

 

  1. <resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">   
  2.    <result property="code" column="posType"/>   
  3.    <result property="description" column="description"/>   
  4.  </resultMap>   
  5.   
  6.   <select id="procPostype" resultMap="posTypeResultMap">   
  7.     select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))   
  8.   </select>  

PL/SQL procedure successfully completed  

Dao的写法跟普通查询同一 

p2  

  1. public List queryPostype() {   
  2.   return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);   
  3. }  

---------  

有几点需求小心,这里无法运用索引表,而是嵌套表。 
另外就是把嵌套表强制转换为普通表。

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)就定义了变长数组的最概况素个数是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 ;  

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

}  

有几点必要潜心,这里不可能利用索引表,而是嵌套表。

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

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Server存储过程,使用游标过程中出现的错误

关键词: