sqlserver 使用游标进程中冒出的失实

作者:数据库

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

as    

  风姿浪漫,定义:Sql Server的仓库储存进度是三个被取名的存款和储蓄在服务器上的Transacation-Sql语句集结,是包裹重复性工作的意气风发种方法.

2.

exception    

     3,减少网络流量。存款和储蓄进度位于服务器上,调用的时候只必要传递存款和储蓄进度的名目以至参数就足以了,由此下落了网络传输的数据量。

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 

SQL> exec dbms_output.put_line(:p2);  

   1,重复使用。存款和储蓄进程能够重复使用,进而能够减少数据库开拓职员的职业量。

  未有为@cur,分配游标

 p_para2 out varchar2,  

  二,存储进程的优点:

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            

declare    

   2,进步品质。存款和储蓄进程在创设的时候就展开了编写翻译,今后使用的时候绝不再重新编译。经常的SQL语句每实施一回就需求编写翻译一遍,所以采纳存款和储蓄过程升高了频率。

  

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

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 语句中能够声多美滋个或八个参数。客商必需在施行进度时提供每一个所表明参数的值(除非定义了该参数的私下认可值,只怕该值设置为等于另一个参数卡塔 尔(英语:State of Qatar)。存款和储蓄进度最多能够有 2.100 个参数。

动用 @ 符号作为第二个字符来钦赐参数名称。参数名称必需契合标志符的平整。各个进程的参数仅用于该进度自身;相符的参数名称能够用在别的进程中。默许情状下,参数只好替代常量,而无法用于替代表名、列名或任何数据库对象的称号。

data_type

    参数的数据类型。除 table 之外的别样兼具数据类型均能够看作存款和储蓄进程的参数。但是,cursor 数据类型只好用来 OUTPUT 参数。假使钦定 cursor 数据类型,则还必需钦命VA奥迪Q5YING 和 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奔驰G级YPTION 可卫戍将经过作为 SQL Server 复制的大器晚成有的公布。

FOR REPLICATION

    钦定不可能在订阅服务器上实践为复制作而成立的蕴藏进度。.使用 FO奇骏 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中寄存函数的使用.已不归于本文所要探讨的限量了.

现身上述错的缘由就是概念游标后须求开发 open @mycur

create or replace procedure procdefault2(p1 varchar2 default 'remark',  

  三,语法,创设存款和储蓄进程:  

上边包车型的士眼光是在使用游标的历程中做的日记。笔者也是首先次利用,借使有怎么着窘迫之处请商量指正,大家风度翩翩道全力。

   while c_postype%found loop  

语法

如若未有对输出的游标做close、deallocate管理就能产出上边错误。

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

   4,安全性。参数化的存放进度能够免卫SQL注入式的口诛笔伐,并且能够将Grant、Deny以至Revoke权限应用于积攒进度。

  那个主题素材是自家在应用存款和储蓄进度重返的游标 cursor output 发生的

 p_para1 varchar2,  

1.

大家运用的是ibatis的2.0本子,比较费心。

  这些难题是本身在调用叁个递归的、输出cursor output 的存放过程

SQL> exec :p3 :='cccc';  

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

 

 description varchar2(50)  

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

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

   v_postype := v_pos.pos_type;  

  ……  

1. 多条数据是足以负责的,相当于说从结果集中随意取一个值就能够。这种景观应该很极端了啊,假设出现这种境况,也证实了程序的严苛性存在难题。

从而这一个历程要求定义三个门类。

在该进程中,p_para2被付与了二十一个字符a.

 begin  

       }  

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

as    

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

  1. dao方法  

           result.add(posTypeItem);  

v_student t_table;  

begin  

 loop  

 

仓库储存进程的参数字传送递有二种情势:IN,OUT,IN OUT .

进度能够有参数,也得以没有参数

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

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

)as    

索引表 否 无限 不需

oracle存款和储蓄进程常用本事

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

 p_para2 :='bbb';  

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

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

仅是那样注明是无法运用的,必需对嵌套表张开最早化,对嵌套表张开发轫化能够应用它的构造函数

嵌套表,索引表未有 index by子句便是嵌套表,它能够寄放于数据中,成分个数Infiniti,下标从1始发,况且需求开始化

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

loop  

   declare    

 fetch c_postype1 into v_postype;  

end;  

end;  

简来讲之,使用while来循环管理游标是最复杂的措施。

   end;  

SQL> exec procdefault('a');  

  v_description := v_pos.description;  

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

 end;  

对于OUT 和IN OUT 参数,其调幅是由存款和储蓄进度之中央调节制。

as    

 exception  

 end loop;  

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

end loop;  

咱们精通在pl/sql中要想从数据表中向变量赋值,要求选拔select into 子句。

 

 loop  

   when too_many_rows then  

 v_postype varchar2(20);  

 fetch c_postype2 into v_postype;  

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

 close c_postype1;  

remark

 


本条跟jdbc的法子要命的相同.

   v_posTypeList(4) := PosType('A004','续期交费方式更动');  

begin  

v_name varchar2(2);  

   v_posTypeList.extend;  

           posTypeItem.setCode(postype);  

之所以,在写存款和储蓄进程时,对参数的肥瘦举行验证是十二分有要求的,最明智的方法正是参数的数据类型使用%type。这样两侧就高达了平等。

           posTypeItem.setDescription(description);  

 v_postype varchar2(20);  

 dbms_output.put_line(o1);  

end;  

Dao的写法跟日常查询同豆蔻梢头

begin  

call.execute();  

巡回甘休后要记得关闭游标。

   fetch c_postype into v_postype;  

 v_ref_postype refcursor;  

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

 p_para3 := v_name;  

v_name varchar2(2);  

   dbms_output.put_line(v_postype);  

咱俩驾驭了多个游标打开后,必得举行贰回fetch语句,游标的天性才会起效果。所以采取while 循环时,就须求在循环此前开展二次fetch动作。

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

while c_postype%found loop  

 when no_data_found then  

begin  

type t_postype is ref cursor ;

   v_posTypeList.extend;  

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

SQL> var p3 varchar2(30);  

第生龙活虎,它会自动open和close游标。化解了您忘记张开或关闭游标的烦心。

3.3 游标循环最好战术

 procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  

 begin  

   v_posTypeList.extend;  

     

create or replace package body procpkg is  

   dbms_output.put_line('found true');  

Too_many_rows 那么些主题素材比起no_data_found要复杂一些。

 close c_postype;  

 p_para3 in out varchar2  

在座谈循环方法早先,我们先看看%found和%notfound这一个游标的性质。

 function procpostype(p varchar2) return PosTypeTable  

 end if;  

);  

 p_para1 varchar2,  

   v_posTypeList :=PosTypeTable();--初叶化嵌套表  

可是它会推动来一些主题材料,就算查询未有记录时,会抛出no_data_found异常。

                                       p2 varchar2 )  

内需注意的是早晚要增长对no_data_found的拍卖,对现身多条记下的事态则持续抛出至极,让上黄金年代层来拍卖。

type t_nestTable is table of varchar2(20);  

实施那一个历程

 fetch c_postype into v_postype;  

OUT 代表输出参数,能够清楚为按援引传递方式。能够看作存储进度的输出结果,供外部调用者使用。

v_name varchar2(2);  

c_postype3 t_postype;  

其次种选取while循环。

call.setString(1, null);  

 open c_postype2(1);  

           String postype =rs.getString(1);  

上面正是贰个最简便易行的存款和储蓄进度。一个存款和储蓄进度大致分为这么多少个部分:

create or replace procedure proccycle(p varchar2)  

此地必要潜心,exit when语句一定要紧跟在fetch之后。必幸免多余的多少管理。

 end;  

   dbms_output.put_line(v_postype);  

 procpkg.procrefcursor('a',v_ref_postype);  

 p_para1 :='aaa';  

1.存储进度结构

create or replace type PosTypeTable is table of PosType;

       String p = "";  

在信阳中定义了二个游标变量,并把它当做存款和储蓄进度的参数类型。

create or replace procedure proc1(  

进度语句块:从begin 关键字最初为经过的语句块。存款和储蓄进程的生气勃勃逻辑在此处来达成。

而把p2作为参数调用那一个进程,却并不曾报错。并且它的真实值就是十八个a

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

1.3 参数的私下认可值

何况,变长数组的应用也亟需事先开端化。

begin  

   v_posTypeList(1) := PosType('A001','顾客资料改动');  

   再来看看IN OUT参数的大幅  

 p_para1 varchar2,  

p2  

  v_postype := v_pos.pos_type;  

begin  

SQL> show error;  

报错  

                                       p2 varchar2 default 'mark')  

 when no_data_found then  

自家这边仅说第两种意况,不可接收多条数据,然则绝不忘记了处理no_data_found哦。这就不可能选取游标了,必需接受此中块。

end loop  

因为能够在parameterMap中定义一个resultMap.那样就无必要团结定义微型机了。

---------  

1. 直接助长极度管理。

  procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  

   v_posTypeList.extend;  

  type refcursor is ref cursor;  

   v_posTypeList.extend;  

end;  

v_postype varchar2(20);  

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

   when no_data_found then  

     inner2 varchar2(20);  

           CodeTableItemDTO posTypeItem = new CodeTableItemDTO();  

大家根本商讨的是哪些通过jdbc调用来拍卖这一个输出参数。

然后再用open for 来开荒一个查询。须求小心的是它可以频繁施用,用来张开不一致的查询。

那些管理块:关键字为exception ,为拍卖语句爆发的特别。该有的为可选

这一句并不曾写死,查询参数由变量v_rownum来决定。须要潜心的是v_rownum必得在这里个游标定义在此以前宣称。

SQL> set serveroutput on;  

 p_para3 in out varchar2  

  procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  

begin  

1.1 第三个存款和储蓄进度

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

 close c_postype3;  

SQL> exec :p1 :='aaaaaa';  

 v_desc varchar2(50);  

v_name varchar2(20);  

v_name varchar2(20);  

DUMP(:P2)  

   dbms_output.put_line(v_postype);  

第三个参数有默许值,第一个参数未有。假若大家想使用第二个参数的暗中认可值时

Ibatis管理措施:

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

create or replace procedure procexception(p varchar2)  

SQL> var p2 varchar2(1);  

       Map para = new HashMap();  

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

索引表,也叫做pl/sql表,不能够积存于数据库中,成分的个数未有节制,下标可认为负值。

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

第三种 for循环

本文由ca88发布,转载请注明来源

关键词: ca88网址 日记本 C#基础 ca888亚洲娱乐城