创建了一个存储过程带一个参数反回多条记录集合
我调用的方法是:
this.DataConnect(); //数据库连接
OleDbCommand mycmd=new OleDbCommand();
mycmd.Connection=mycon;
mycmd.CommandType=System.Data.CommandType.StoredProcedure;
mycmd.CommandText=varProcName;
mycmd.Parameters.Add(parName,parType,parSize).Value=parValue;
OleDbDataAdapter myda=new OleDbDataAdapter(mycmd);
DataSet dataset1=new DataSet();
myda.Fill(dataset1);
mycon.Close();
return dataset1;
==========================================================================
this.DataConnect(); //数据库连接
OleDbCommand mycmd=new OleDbCommand();
mycmd.Connection=mycon;
mycmd.CommandType=System.Data.CommandType.StoredProcedure;
mycmd.CommandText=varProcName;
mycmd.Parameters.Add(parName,parType,parSize).Value=parValue;
OleDbDataAdapter myda=new OleDbDataAdapter(mycmd);
DataTable dt=new DataTable();
myda.Fill(dt);
return dt;
在存储过程中定义个游标,然后将游标打开。就可以返回记录集。
====================================================================
CREATE PROCEDURE administrator.GetUsersByLoginID(IN login_ID varchar(10))
LANGUAGE SQL
BEGIN
declare cur CURSOR WITH RETURN FOR
select * from administrator.BMKIFAPERSON where loginID = login_ID;
OPEN cur;
END @
=======================================================================
oracle 中假设要用system用户访问hr.employees表
1 创建包 mypackage
create or repalce package mypackage
as
begin
type mycurtype is ref cursor return hr.eployees%rowtype;
procedure getemployeesinfo(mycurvar out mycurtype);
end mypackage;
2 定义包体
create or replace package body mypackage
begin
procedure getemployeesinfo(mycurvar out mycurtype)
as
bgein
open mycurvar for select * from hr.employees;
end getemployeesinfo;
end mypackage;
在C#中调用
...
mycmd.CommandText=mypackage.getemployees;
...