create or replace type smpl_metadata as object
(
   tmp_click_num int,
   tmp_reach_num int,
   tmp_trigger_num int
);

create or replace type smpl_metadata_table as table of smpl_metadata;

create or replace function fn_test
(
       v_startdate in varchar2,
       v_enddate   in varchar2
)return smpl_metadata_table pipelined
is
  results smpl_metadata_table := smpl_metadata_table();
  count1 int;
  count2 int;
  count3 int;
  CURSOR c IS
    select alliance.alliance_name       as alliance_name,
           click.alliance_id            as alliance_id,
           click.site_id                as site_id,
           click.game_id                as game_id,
           game.game_name               as game_name,
           click.server_id              as server_id,
           server.GSERVER_NAME          as game_serve,
           click.material_id            as material_id,
           material.MATERIAL_NAME       as material_id_name,
           alliance.ALLIANCE_PRICE      as price,
           alliance.input_alliance_time as alliance_time,
           game.input_game_time         as game_time,
           server.input_gserver_time    as server_time,
           material.input_material_time as material_time
      from wm_click          click,
           wm_input_alliance alliance,
           wm_input_game     game,
           wm_input_gserver  server,
           wm_input_material material
     where click.create_date > to_date(v_startdate, 'yyyy/mm/dd hh24')
       and click.create_date < to_date(v_enddate, 'yyyy/mm/dd hh24')
       and click.material_id = material.mid
       and click.alliance_id = alliance.aid
       and click.game_id = game.gid
       and click.server_id = server.gsid
       and game.gid = server.gid
     group by alliance.alliance_name,
              alliance.ALLIANCE_PRICE,
              click.alliance_id,
              click.site_id,
              click.game_id,
              click.server_id,
              click.material_id,
              game.game_name,
              server.GSERVER_NAME,
              material.MATERIAL_NAME,
              alliance.input_alliance_time,
              game.input_game_time,
              server.input_gserver_time,
              material.input_material_time;
  --定义记录变量
  v_records c%ROWTYPE;
begin
  OPEN c;
  LOOP
    FETCH c
      INTO v_records;
    EXIT WHEN c%NOTFOUND;
    IF c%FOUND THEN
      select count(ip)
        into count1
        from (select ip
                from wm_click click
               where click.create_date >
                     to_date(v_startdate, 'yyyy/mm/dd hh24')
                 and click.create_date <
                     to_date(v_enddate, 'yyyy/mm/dd hh24')
                 and click.alliance_id = v_records.alliance_id
                 and click.site_id = v_records.site_id
                 and click.material_id = v_records.material_id
                 and click.game_id = v_records.game_id
                 and click.server_id = v_records.server_id
               group by click.ip);
      --到达量
      select count(ip)
        into count2
        from (select reach.ip
                from wm_reach reach
               where reach.create_date >
                     to_date(v_startdate, 'yyyy/mm/dd hh24')
                 and reach.create_date <
                     to_date(v_enddate, 'yyyy/mm/dd hh24')
                 and reach.alliance_id = v_records.alliance_id
                 and reach.site_id = v_records.site_id
                 and reach.material_id = v_records.material_id
                 and reach.game_id = v_records.game_id
                 and reach.server_id = v_records.server_id
               group by reach.ip);
      --触发量
      --DBMS_OUTPUT.PUT_LINE(v_records.alliance_id ||','|| v_records.site_id||','|| v_records.material_id||','|| v_records.game_id||','|| v_records.server_id);
      select count(ip)
        into count3
        from (select trig.ip
                from wm_trigger trig
               where trig.create_date >
                     to_date(v_startdate, 'yyyy/mm/dd hh24')
                 and trig.create_date <
                     to_date(v_enddate, 'yyyy/mm/dd hh24')
                 and trig.alliance_id = v_records.alliance_id
                 and trig.site_id = concat(v_records.site_id, '')
                 and trig.material_id = v_records.material_id
                 and trig.game_id = v_records.game_id
                 and trig.server_id = v_records.server_id
               group by trig.ip);
     results :=smpl_metadata_table();
     results.extend;
     results(results.count).tmp_click_num := count1;
     results(results.count).tmp_reach_num := count2;
     results(results.count).tmp_trigger_num := count3;
     return(results);
    end if;
  end loop;
   
end fn_test;