【Oracle-PLsql】使用存储过程，利用table集合类型开发复杂业务报表

 

在一般的项目中，都需要开发一些报表，少则几个字段，多则几十个字段，需要关联的表可能多达十几、几十张表，如果想要使用一个SQL语句将这几十张表关联起来

查询所需要的字段，当你听到这里的时候，你的脑子可能已经晕掉了，这得多麻烦啊，不光多麻烦，你甚至可能还写不出来，即使你写出来了，也肯定是bug一堆。等你过

接个月再来看看这段SQL，估计已经看不明白了。等到后人来维护你这段SQL的时候，他估计会咬牙切齿，恨不得要把这段代码的作者撕个粉碎。

那么接下来，就给大家介绍一个Oracle的功能，使用table集合类型返回结果集，顾名思义你就可以把它看成是一个表，本来结果集也可以看成是一张表嘛。话不多说，直接来看代码。

1.首先定义一个type，包括返回结果集所需要显示的字段。

 

CREATE OR REPLACE TYPE query_rpt_test force is object
(
      full_name varchar2(200),
      birth_date date,
      gender  varchar2(200)
)

2.接着定义table类型

 

 

CREATE OR REPLACE TYPE t_query_rpt_test is table of query_rpt_test

3.然后定义package

 

 

create or replace package PKG_REPORT_QUERY_TEST is


  --定义取数的function,传入参数分别是开始日期和截止日期
  function f_get_query_report(i_begin_date date, i_end_date date)
    --返回类型为t_query_rpt_test
    return t_query_rpt_test;

end PKG_REPORT_QUERY_TEST;

4.定义package body

 

create or replace package body PKG_REPORT_QUERY_TEST is

  --定义取数的function,传入参数分别是开始日期和截止日期
  function f_get_query_report(i_begin_date date, i_end_date date)
    return t_query_rpt_test as
    --返回类型为t_query_rpt_test
    m_table t_query_rpt_test;

    --结果集行数初始值
    m_row_num number := 0;

    --一些变量的定义
    n_list_id   number;
    v_full_name varchar2(200);
    v_birthday  date;
    v_gender    varchar2(20);

    --游标的定义
    --这边的业务逻辑要看的该商品的购买者的姓名，生日和性别
    --此处举例比较简单，只有t_product和t_customer两张表的关联关系，完全可以两者表关联起来放在同一个cursor中，
    --这边先查询出在该区间段内的所有商品信息的主键List_id
    cursor c_product is
      select p.list_id
        from t_product p
       where p.buy_date >= i_begin_date
         and p.buy_date <= i_end_date;

  begin
    --结果集初始化
    m_table := t_query_rpt_test();

    --打开游标
    open c_product;
    loop
      --循环遍历游标内的每一个数据
      fetch c_product
        into n_list_id;
      exit when c_product%notfound;

        --以下就是每个字段的取数逻辑了，除此之外，整个package的function格式都是固定的。
      --根据遍历的数据n_list_id在t_customer中查询客户信息
      select c.full_name, c.birthday, c.gender
        into v_full_name, v_birthday, v_gender
        from t_customer c
       where c.product_id = n_list_id;

      --扩充结果集
      m_table.extend;
      --结果集行数自增1
      m_row_num := m_row_num + 1;
      --设置结果集中每个字段的值
      m_table(m_row_num) := query_rpt_test(v_full_name,
                                           v_birthday,
                                           v_gender);
    end loop;
    --循环结束，关闭游标
    close c_policy;
    --返回结果集
    return m_table;
  end f_get_query_report;

end PKG_REPORT_QUERY_TEST;

 

好了，整个代码都编写完成了，那么我们如何来使用呢,直接编写如下语句就能返回一个结果集了。

 

 

select * from table(PKG_REPORT_QUERY_TEST.f_get_query_report(to_date('2013-01-01','yyyy-MM-dd'),to_date('2013-01-01','yyyy-MM-dd')))；

 

 

 

这样的话在Java中使用也非常方便，就不用再在java代码中写一大堆SQL了。

OK，总结一下，整个开发流程就是：

1.定义cursor。cursor的作用只需要查询最精简最有用的信息，至于其他，通通放到取数逻辑中去。

2.打开cursor，根据cursor中的值进行相应的取数逻辑。

3.结果集的扩充，自增1，然后设置结果集的值。

4.最后当然要有返回值：结果集。

很明显，优点就是：简单方便，便于维护，以后只需要在type类型中增加相应的字段，在package中增加相应的取数逻辑，最后设上值即可。

转自：http://blog.csdn.net/andn_pan/article/details/16946365