oracle 游标的使用

曾作过的一个报表用到的游标很典型，在这里作个备份

 

CREATE OR REPLACE PACKAGE RefCursor
IS type t_RefCursor
IS  ref  CURSOR;
END RefCursor;

 

 

CREATE OR REPLACE PROCEDURE p_tj_collect(p_RCODE in VARCHAR2) as

  v_dqdm       VARCHAR2(10);
  v_SJC        VARCHAR2(100);
  v_sqlstr     varchar2(500);
  v_intstr     varchar2(500);
  v_insfield   varchar2(1000);
  v_dqdm_exist varchar2(3000);
  v_num        int;
  v_idx        int;
  cur_query    RefCursor.t_Refcursor;

  cursor cur_rs is
    select tablename from T_TJ_COLLECT_TAB order by id;

  v_tab T_TJ_COLLECT_TAB.tablename%type;

begin
  v_dqdm_exist := '*';
  v_idx        := -1;
  v_sqlstr     := '';
  --  创建表
  --p_tj_scsjc_crt('');
  delete from t_tj_collect;

  for v_tab in cur_rs loop
    /*    v_sqlstr := 'select T.DQDM,to_char (MAX(T.SJC),''YYYY-MM-DD'') ' ||
    v_tab.tablename || ' from t_scsjc t where t.tablename=''' ||
    v_tab.tablename || ''' and t.dqdm like ''' || p_RCODE ||
    '%'' GROUP BY T.DQDM ORDER BY T.DQDM';*/
 
    p_tj_collect_sql(p_RCODE, v_tab.tablename, v_sqlstr);
 
    OPEN cur_query FOR v_sqlstr;
    LOOP
      FETCH cur_query
        INTO v_dqdm, v_SJC;
      EXIT WHEN cur_query%notfound;
   
      select instr(v_dqdm_exist, '*' || v_dqdm || '*', 1)
        into v_idx
        from dual;
   
      if v_idx = 0 then
        v_intstr := 'insert into t_tj_collect(areacode) values (''' ||
                    v_dqdm || ''')';
        execute immediate (v_intstr);
       
        v_dqdm_exist := v_dqdm_exist || v_dqdm || '*';
      end if;
     
     
      v_intstr := 'update t_tj_collect set ' || v_tab.tablename || ' = ''' ||
                  v_SJC || ''' where areacode = ''' || v_dqdm || '''';
      execute immediate (v_intstr);
   
    END LOOP;
    CLOSE cur_query;
 
  end loop;
end p_tj_collect;