• 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} {1}", reader.GetValue(0), reader.GetValue(1));
                        }
                    }
                }
            }

    -- ***************************************

    -- 删除程序包和测试表
    ---------------------
    drop package pkg_test;
    drop table test_pkg_test;

  • 相关阅读:
    Got05
    Git07
    Git09
    Git11
    Git10
    Git13
    Git12
    Git14
    Listview点击跳转页面
    《三个和尚》观后感
  • 原文地址:https://www.cnblogs.com/aaa6818162/p/4150438.html
Copyright © 2020-2023  润新知