Comparison of stored procedures for returned datasets ..........

In SQL Server2000, if a stored procedure has a separate SQL statement, use C # To Call The Stored Procedure and use the adapter fill dataset (Dataset) the number of tables in the dataset exactly corresponds to the number of SQL statements that run separately in the stored procedure.
Create procedure jisentest
@ Qwtgw varchar (16 ),
@ AAA int,
@ ABC varchar (16) Out
As

Delete distest where [name] = @ qwtgw;
Set @ abc = '20140901 ';
Select * From distest;
Select * From distest;
Select * From distest;
Return 1;
Go
C # after this stored procedure is called, there will be 3 datatable data sets in the dataset
In Oracle, it is not that simple to return a dataset. After finding the data, it seems that only the cursor can be used to return the dataset. (currently, I do not know how to output multiple cursors in a stored procedure .)
Create a package declaration first. In the package declaration
Create or replace package jisen. Test
Is
Type cur_type is ref cursor;
Procedure pageprocedure (
Sqltext in varchar2,
Pagecount in number,
Pagenumber in number,
Sqlcondition in varchar2,
Icount out number,
MYCS out cur_type );
End;
Next, create a package subject that includes the declared Implementation of the stored procedure
Create or replace package body jisen. Test
Is
Procedure pageprocedure
(Sqltext in varchar2,
Pagecount in number,
Pagenumber in number,
Sqlcondition in varchar2,
Icount out number,
MYCS out cur_type
)
As
Tempsql varchar2 (2000 );
Bbegin number (10 );
Bend number (10 );
Find_rs_cur cur_type;
Begin

If sqlcondition is null then
Begin
Tempsql: = 'select count (*) from ('| sqltext | ')';
End;
Elsif length (sqlcondition)> 0 then
Begin
Tempsql: = 'select count (*) from ('| sqltext |') Where '| sqlcondition;
End;
Else
Begin
Tempsql: = 'select count (*) from ('| sqltext | ')';
End;
End if;
Execute immediate tempsql into icount;

Bbegin: = pagecount * pagenumber;
Bend: = (pagenumber + 1) * pagecount;
 
Tempsql: = 'select * from (
Select rownum as Tempa, T. * from ('
| Sqltext | ') t where rownum <' | bend | ')
Where Tempa> = '| bbegin;
Open find_rs_cur for tempsql;
MYCS: = find_rs_cur;
-- Execute immediate tempsql; the query results cannot be obtained from C # using this statement.
End pageprocedure;
End pk_wt;
In this stored procedure, rownum is used for paging.
I wonder if the experts have a simple stored procedure for returning a dataset? Don't forget to give me some advice!