oracle块编程,Oracle块编程返回结果集详解

一、概述

在Oracle块

编程(begin系列)中，由于其不支持select .... from

...返回结果集的形式，因此就只能通过输出参数的形式返回结果。游标作为一种将结果集封装成以指针单调向下读取数据的结构，类似于只有出队并删除操作的

队列，正好作为输出参数的类型。而为了使用这种方式，必须保证存储过程的参数在声明与调用时的统一，因此不得不使用程序包。所以程序包+游标+存储过程或

函数就成了块编程返回结果集的方法。下面来谈pl/SQL与ODP.net实现的方法。

二、定义

1、表结构

create table Grade

(

GradeId,number,

GradeName varchar2(20)

)

2、块编程

--定义程序包规范

create package Grades is

type Result is ref cursor;

procedure FenYe_Grade(p_PageSize in number,p_PageIndex in number,p_Result out Result);

end Grades;

--定义程序包体

create package body Grades is

procedure FenYe_Grade(p_PageSize in number,p_PageIndex in number,p_Result out Result) is

begin

open p_Result for select GradeId,GradeName

from (select GradeId,GradeName,rownum rn from Grade where rownum<=p_Page*p_PageSize) a

where rn>p_PageSize*(p_Page-1);

end FenYe_

既然谈到了rownum，那就来详细说说，rownum是读取数据时的顺序号，记得"读取数据时的顺序号"，任何读取都算。就好像把所有数据放入

一个房间，门口有一个涂编号的机器(起始号为1，递增量为1)，房间里出来一个，该机器就在它身上涂上一个数字，机器涂号不能被打断，一被打断，又从1开

始。因为它一被打断就从1开始，所以rownum>n或rownum=n

(n>1)总是为false的，根据这个比喻，用rownum>2来说:首先第一个出来为1，不满足筛选条件，抛弃掉，此时涂号器被打断，从

1开始，于是第二个又是1，如此循环下去，所有的数都是1.再拿上面代码实例“where

rn>p_pageSize*(p_Page-1)”来说，讨论其一种情况:

a、改为"where rownum>p_pageSize*(p_Page-1)"

这样改后，结果一定是空的，为什么呢?用rownum判断就意味着又要对a结果集重新编号了，根据上面的推论，结果一定是空的。那用rn为什么又可

以呢？rn是a结果集rownum的别名，当把rownum用别名输出后，就等于rownum物化了，已经作为结果集的一列，因此用rn判断时就不要重新编号了。

三、调用

1、PL/SQL

declare

pagesize number :=3;

page number :=2;

indexx number:=0;

result Grades.Result;

grad Grade%rowtype;

begin

loop

fetch result into grad;

dbms_output.putLine(Grad.GradeName);

indexx:=indexx+1;

exit when indexx

end;

注意：

不能有游标的%notfount属性做判断，因为引用游标类型不支持。

没法用for 游标，也不支持.

2、ADP.net(C#)

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

OracleConnection conn=new OracleConnection("连接字符串");

OracleCommand cmd=new OracleCommand("FenYe_Grade",conn);

OracleParameter re=new OracleParameter(":Result",OracleDbType.RefCursor);

re.Direction=ParameterDirection.Output;

cmd.Parameters.Add(new OracleParameter(":p_PageSize",3));

cmd.Parameters.Add(new OracleParameter(":p_Page",2));

cmd.Parameters.Add(re);

conn.Open();

cmd.ExecuteNonQuery();

OracleDataReader read=(re.Value as OracleRefCursor).GetDataReader();

//读数据

//......

read.Close();

conn.Close();

Oracle中的游标类型由ODP.net中的OracleRefCursor类和OracleDataAdapter类解析,用

OracleRefCursor的GetDataReader()可以获得OracleDataReader。用OracleDataAdapter解

析，在调用Fill()方法时，该类会将输出的游标对应为一张

张DataTable.当只有一个输出游标时，如上面的代码实例，可以直接用OracleCommand的ExcuteReader()获得

OracleDataReader。函数返回游标与存储过程返回接收一样。

讲到这里，再讲讲ADP.net里的多行插入吧，这和插入到sql server中有点不同。

2.1、多行插入.

利用ADO.net向sql server插入多行数据，有两种方法：1.拼一个insert

into字符串集执行.2、利用DataSet做Fill或批处理.而在ADP.net，也有两种便捷的方法：1.拼insert

into集，但要注意，不能纯是"inert into ...;insert into",必须要用          begin

end包起来才能成功提交。2、便捷高效的批处理:

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

int[] GradeIds={1,2,3};

string[] GradeNames={"C","C++","C#"};

OracleConnection conn=new OracleConnection("连接字符串");

OracleCommand cmd=new OracleCommand("insert into Grade values(:GradeId,:GradeName)",conn);

OracleParameter re=new OracleParameter(":GradeId",OracleDbType.Int32);

re.Value=GradeIds;

OracleParameter name=new OracleParameter(":GradeName",OracleDbType.Varchar2);

name.Value=GradeNames;

cmd.Parameters.Add(re);

cmd.Parameters.Add(name);

cmd.ArrayBindCount=GradeIds.Length;//批处理量

conn.Open();

int count=cmd.ExecuteNonQuery();

conn.Close();