Oracle 查询，返回记录集，不是用游标，不用创建临时表

1.首先要创建一个类型集合；

create or replace type row_month_report as object---声明一种类型
(
m_month varchar2(30),
m_SluiceName varchar2(30),
m_OneSluiceCount number,
m_TwoSluiceCount number,
m_OneUpEmptyCount number,
m_OneDownemptyCount number,
m_TwoUpemptyCount number,
m_TwoDownemptyCount number,
------------------------这是写要返回的字段集变量
)

2. create or replace type table_month_report as table of row_month_report;-----把类型当作表使用

3. 编写Oracle 方法：

create or replace function fun_MontnReport(SLUICEID IN VARCHAR2,

                                        D in VARCHAR2) return table_month_report ---返回自定义的类型
                                        pipelined as
                                        vv  row_month_report;

 m_SluiceName        varchar2(30);

  daychar                       VARCHAR2(50); --日期
  yearchar                      VARCHAR2(50); --年份

  m_OneSluiceCount    number := 0;
  m_TwoSluiceCount    number := 0;
  m_OneUpEmptyCount   number := 0;
  m_OneDownemptyCount number := 0;
  m_TwoUpemptyCount   number := 0;
  m_TwoDownemptyCount number := 0;

begin

---查询语句，并把值赋给变量如：

 select nvl(sum(t.SLUICECOUNT), 0)
      into m_OneSluiceCount
      from sluicedispatch t
     where to_char(t.overtime, 'YYYY-MM') = daychar
       and t.sonsluiceoid = '1#'
       and t.sluiceoid = SLUICE; --1#闸运行闸次

.....................

4.最后把查到的值赋给自定义的类型( 就相当于给表格插入数据一样）

  vv:=row_month_report(

       daychar,
       m_SluiceName,
       m_OneSluiceCount,
       m_TwoSluiceCount,
       m_OneUpEmptyCount,
       m_OneDownemptyCount,
       m_TwoUpemptyCount,
       m_TwoDownemptyCount

.........................

)values

(

daychar,

m_OneSluiceCount  ,

   m_TwoSluiceCount  ,

   m_OneUpEmptyCount 

  m_OneDownemptyCount  ,

m_TwoUpemptyCount ,

   m_TwoDownemptyCount  

...............

);

      pipe row(vv);
      return;

end ;