oracle 存儲過程返回結果集 （轉載）

好久沒上來了, 難道今天工作時間稍有空閒, 研究了一下oracle存儲過程返回結果集.

       配合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;