oracle 游标的使用

来源：互联网发布：带移动端的开源cms 编辑：程序博客网时间：2024/06/06 13:24

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

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;

0 0