存储过程之用返回多条数据一

要求：拼接数据，作为多条数据返回。

1、创建类型：

create or replace type bb_ptyxztqk_Type as object(
  d_index number,
  d_name varchar2(100),
  d_this number,
  d_last number,
  d_minus number
)

2、创建表类型：

create or replace type bb_ptyxztqk_Table as table of bb_ptyxztqk_type

3、package：

create or replace package bb_ptyxztqk is

  type type_cursor is ref cursor;

  PROCEDURE pro_getdata(enddate in varchar2, cur out type_cursor);

end bb_ptyxztqk;

4、packagebody：

create or replace package body bb_ptyxztqk is

  -- Function and procedure implementations  cur out type_cursor
  PROCEDURE pro_getdata(enddate in varchar2, cur out type_cursor) is
    Type RecType Is Record(
      r_index number,
      r_name  varchar2(100),
      r_this  number,
      r_last  number,
      r_minus number);
    Type TabType Is Table Of RecType Index By Binary_Integer;
    myTab        TabType;
    obj_type_tab bb_ptyxztqk_table := bb_ptyxztqk_table();
    l_index      number := 1;
    p_enddate date := to_date(enddate,'yyyy-mm-dd hh24:mi:ss');
    l_value number(18,0);
    l_value_last number(18,0);
  begin
    --------------------------------1-------------------------------
      myTab(l_index).r_index := l_index;
      myTab(l_index).r_name := '第一条数据';
      --查询数据
      select count(1) into l_value
         from t_company c;
      myTab(l_index).r_this := l_value;

      select count(1) into l_value_last
         from t_company c
         where (c.c_createdate <= p_enddate or c.c_createdate is null);
      myTab(l_index).r_last := l_value_last;

      myTab(l_index).r_minus := l_value - l_value_last;
      l_index := l_index + 1;

      --------------------------------2-------------------------------
      myTab(l_index).r_index := l_index;
      myTab(l_index).r_name := '第二条数据';
      --查询数据
      select count(1) into l_value from t_supplier;
      myTab(l_index).r_this := l_value;

      select count(1) into l_value_last from t_supplier s
         where s.c_createdate <= p_enddate;
      myTab(l_index).r_last := l_value_last;

      myTab(l_index).r_minus := l_value - l_value_last;
      l_index := l_index + 1;

    --赋值
    l_index := myTab.First;
    for l_in in l_index .. myTab.count Loop
      obj_type_tab.EXTEND;
      obj_type_tab(obj_type_tab.LAST) := bb_ptyxztqk_type(myTab(l_index).r_index,
                                                          myTab(l_index).r_name,
                                                          myTab(l_index).r_this,
                                                          myTab(l_index).r_last,
                                                          myTab(l_index).r_minus);
      l_index := myTab.Next(l_index);
    end Loop;
    open cur for
      select *
        from table(obj_type_tab);
  end;
end bb_ptyxztqk;