oracle 存储过程返回结果集

-- 启用服务器输出 --------------------- set serveroutput on -- 创建测试表 --------------------- create table test_pkg_test (  id number(10) constraint pk_test_pkg_test primary key,  name varchar2(30) ); -- 写入测试数据 --------------------- begin insert into test_pkg_test(id) values(1); insert into test_pkg_test(id) values(2); insert into test_pkg_test(id) values(3); insert into test_pkg_test(id) values(4); insert into test_pkg_test(id) values(5); insert into test_pkg_test(id) values(6); insert into test_pkg_test(id) values(7); insert into test_pkg_test(id) values(8); insert into test_pkg_test(id) values(9); insert into test_pkg_test(id) values(10); insert into test_pkg_test(id) values(11); insert into test_pkg_test(id) values(12); insert into test_pkg_test(id) values(13); insert into test_pkg_test(id) values(14); insert into test_pkg_test(id) values(15); insert into test_pkg_test(id) values(16); insert into test_pkg_test(id) values(17); insert into test_pkg_test(id) values(18); end; / update test_pkg_test set name='name of ' || to_char(id); commit; -- 声明程序包 --------------------- create or replace package pkg_test as  type  type_cursor is ref cursor;  procedure read_rows (header varchar2, result out type_cursor); end pkg_test; / -- 实现程序包 --------------------- create or replace package body pkg_test as  procedure read_rows (header varchar2, result out type_cursor)  is   sqlText varchar2(500);  begin   if header is null or length(header)=0 then    sqlText := 'select * from test_pkg_test';   else    sqlText := 'select * from test_pkg_test where substr(name,1,' || to_char(length(header)) || ')=''' || header || '''';   end if;   --dbms_output.put_line(sqlText);   open result for sqlText;  end read_rows; end pkg_test; / -- 在 sqlplus 中测试 --------------------- var result refcursor exec pkg_test.read_rows(null,:result); print result exec pkg_test.read_rows('name of 1', :result); print result; -- 在程序中测试(c#.Net) -- ***************************************     static class pkg_test     {         public static void Test()         {             using (OracleConnection conn = new OracleConnection())             {                 conn.ConnectionString = "Data Source=mydb;User Id=myuser;Password=mypassword";                 conn.Open();                 using (OracleCommand cmd = new OracleCommand("pkg_test.read_rows", conn))                 {                     cmd.CommandType = System.Data.CommandType.StoredProcedure;                     OracleParameter p = new OracleParameter("header", OracleType.VarChar);                     p.Value = "name of 1";                     //p.Value = DBNull.Value;                     cmd.Parameters.Add(p);                     p = new OracleParameter("result", OracleType.Cursor);                     p.Direction = System.Data.ParameterDirection.Output;                     cmd.Parameters.Add(p);                     OracleDataReader reader = cmd.ExecuteReader();                     while (reader.Read())                     {                         Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1));                     }                 }             }         } -- *************************************** -- 删除程序包和测试表 --------------------- drop package pkg_test; drop table test_pkg_test;