oracle 返回结果集

oracle SQL代码  使用包返回结果集

--存储过程查询结果集
--创建包
create or replace package packselect
as
type t_cursor is ref cursor;
PROCEDURE testprocHYKHYXX
(
 cur_name out t_cursor
);
end packselect ;
;

--创建包体
create or replace package body packselect
as
PROCEDURE testprocHYKHYXX
(
 cur_name out t_cursor
)
is 
begin 
 open cur_name for
 select H.HYID, H.HYK_NO,H.HY_NAME,G.SEX,G.E_MAIL FROM HYK_HYXX_1 H,HYK_GRXX_1 G WHERE H.HYID = G.HYID and rownum<20;
 end testprocHYKHYXX;
 end packselect;
 ;
 

 --创建包
 create or replace package testpackage
 as 
 type t_cursor is ref cursor
 procedure  testproc
 (
   id int , 
   c_cursor out t_cursor
 );
 end testpackage;
 ;
 
 --创建包体
 create or replace package body testpackage
 as
 procedure testproc
 (
   id  int, 
   c_cursor out t_cursor
 )
 as
 begin
   select * from test1 where testid := id;
   end testproc;
   end testpackage;
   ;

使用存储过程返回结果集

CREATE OR REPLACE Procedure Procedure_Name (
c_cursor Out Sys_Refcursor
)
Is
Begin
Open c_cursor For
Select * From bu_config;
End Procedure_Name;;

 

C# 调用代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("测试Oracle");
            TestOracle();
            Console.WriteLine("测试成功完成");
        }

        public static void TestOracle()
        {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dt3 = new DataTable();
            try
            {
                //包返回结果集 不带输入参数 查询返回一个结果集
                OracleConnection conn = new OracleConnection("Data Source=DB; uid=USER; pwd=PWD");
                
                OracleCommand cmd = new OracleCommand("packselect.testprocHYKHYXX", conn);
                
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter p = new OracleParameter("cur_name", OracleType.Cursor);
                p.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(p);

                OracleDataAdapter da = new OracleDataAdapter(cmd);

                da.Fill(dt1);


                //带一个输入参数 查询一条记录 作为结果集返回
                //包返回结果集的
                OracleCommand cmd2 = new OracleCommand("testpackage.testproc", conn);

                cmd2.CommandType = CommandType.StoredProcedure;
                OracleParameter p1 = new OracleParameter("tid" , OracleType.Number);
                p1.Value = 2;
                p1.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(p1);

                OracleParameter p2 = new OracleParameter("c_cursor", OracleType.Cursor);
                p2.Direction = ParameterDirection.Output;
                cmd2.Parameters.Add(p2);

                OracleDataAdapter da2 = new OracleDataAdapter(cmd2);

                da2.Fill(dt2);
                
                
                
                //存储过程返回结果集
                OracleCommand cmd3 = new OracleCommand("procedureName", conn);
                
                cmd3.CommandType = CommandType.StoredProcedure;

                OracleParameter p2 = new OracleParameter("c_cursor", OracleType.Cursor);
                p2.Direction = ParameterDirection.Output;
                cmd3.Parameters.Add(p2);

                OracleDataAdapter da3 = new OracleDataAdapter(cmd3);

                da3.Fill(dt3);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                for (int j = 0; j < dt1.Columns.Count; j++)
                {
                    Console.Write(dt1.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
            
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                for (int j = 0; j < dt2.Columns.Count; j++)
                {
                    Console.Write(dt2.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
            
            for (int i = 0; i < dt3.Rows.Count; i++)
            {
                for (int j = 0; j < dt3.Columns.Count; j++)
                {
                    Console.Write(dt3.Rows[i][j].ToString() + "   ");
                }
                Console.WriteLine("");
            }
        }
    }
}