create or replace procedure PRO_ALARM_SQLEXECUTE(dateIn in date,
daychr in varchar2,
rebound out varchar2) is
TYPE t_list_type IS TABLE OF varchar2(10000) INDEX BY BINARY_INTEGER;
TYPE t_listInt IS TABLE OF integer INDEX BY BINARY_INTEGER;
t_list t_list_type;
t_code t_listInt;
i integer := 0;
r integer := 0;
v_replace_sql varchar(10000);
begin
select t.sql, t.alarm_code bulk collect
into t_list, t_code
from t_alarm_alarmpolicy t
where t.alarm_datetype = daychr
order by t.pri;
i := t_list.count;
while i > 0 loop
r := t_code(i);
v_replace_sql := replace(t_list(i),
'{yyyy-mm}',
to_char(dateIn, 'yyyy-mm'));
v_replace_sql := replace(v_replace_sql,
'{yyyymm}',
to_char(dateIn, 'yyyymm'));
v_replace_sql := replace(v_replace_sql,
'{yyyymmdd}',
to_char(dateIn, 'yyyymmdd'));
v_replace_sql := replace(v_replace_sql,
'{last_yyyymm}',
to_char(add_months(dateIn, -1), 'yyyymm'));
v_replace_sql := replace(v_replace_sql,
'{yyyy-mm-dd}',
to_char(dateIn, 'yyyy-mm-dd'));
dbms_output.put_line(r);
execute immediate v_replace_sql;
i := i - 1;
end loop;
exception
when others then
rebound := sqlerrm || '错语alarm_code 为:' || r;
end PRO_ALARM_SQLEXECUTE;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22787229/viewspace-1029275/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22787229/viewspace-1029275/