oracle 动态游标

今天写了个动态游标 使用传入参数
关于游标分类以及用法：http://hi.baidu.com/edeed/blog/item/725749540ee73651574e006a.html
思路就是先拼好SQL 然后开动态游标
ORALCE10G也支持正则表达式 呵呵 刚刚好可以实现动态传入参数

procedure TJ_PDA_TESTDATA(v_indicator in varchar) is
type rc is ref cursor;
l_cursor rc;
l_sqlstr varchar(2000);
l_sqlstr_new varchar(2000);
l_raw_use raw_pda_testdata_tj%rowtype;
l_date varchar(20);
begin
select to_char(sysdate, 'YYYY-MM-DD') into l_date FROM DUAL;
l_sqlstr := 'select a.region_id,
sum(count) totle,
''$v_date'' creatdate,
''$v_indicator'' indicator,
sum(case lev when 1 then count else 0 end) lev1,
sum(case lev when 2 then count else 0 end) lev2,
sum(case lev when 3 then count else 0 end) lev3,
sum(case lev when 4 then count else 0 end) lev4,
sum(case lev when 5 then count else 0 end) lev5,
sum(case lev when 6 then count else 0 end) lev6
from (select zhiju_id region_id, $v_indicator lev, count(1) count
from raw_pda_testdata_use t
where creatdate like ''$v_date%''
and zhiju_id != ''-1''
group by zhiju_id, $v_indicator) a
group by region_id';

SELECT REGEXP_REPLACE(l_sqlstr, '\$v_date', l_date) into l_sqlstr FROM dual;
SELECT REGEXP_REPLACE(l_sqlstr, '\$v_indicator', v_indicator) into l_sqlstr_new FROM dual;
open l_cursor for l_sqlstr;
loop
fetch l_cursor
into l_raw_use;
exit when l_cursor%notfound;
begin
insert into raw_pda_testdata_tj
values
(l_raw_use.region_id,
l_raw_use.creatdate,
l_raw_use.indicator,
l_raw_use.totle,
l_raw_use.lev1,
l_raw_use.lev2,
l_raw_use.lev3,
l_raw_use.lev4,
l_raw_use.lev5,
l_raw_use.lev6);
commit;
EXCEPTION
WHEN others THEN
rollback;
end;
end loop;
close l_cursor;

EXCEPTION
WHEN others THEN
null;
END; -- Procedure