使用PL/SQL根据条件,对数据库中的表自动进行更新的SP,这是在原来的SP上进行修改后的,原来的比较繁琐,内容冗长,执行效率不高.由于对开发这块涉足不深,许多知识点容易忘记,所以要记录下来,方便以后.

create or replace procedure timer_onoffshelf as
interval NUMBER := 5/1440;
v number(1);
v_clause varchar2(255);
v_sql varchar2(400);
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_id number;
v_bgn_enabled char(1);
v_end_enabled char(1);
v_type number(1);
v_starttime char(5);
v_endtime char(5);
v_now date;
begin
select case
when TO_CHAR(SYSDATE, 'D') = 1 then
7
else
TO_CHAR(SYSDATE, 'D') - 1
end AS weekday
into v
from dual;
v_clause := '(start_time_' || v || '_enabled = ''Y'' or stop_time_' || v ||'_enable = ''Y'')
and abs(TO_DATE(start_time_' || v ||',''HH24:MI'')-TO_DATE(stop_time_' || v ||',''HH24:MI''))>1/24';
v_sql := 'SELECT agent_card_type_id, type, start_time_' || v || '_enabled as bgn_enabled,stop_time_'
|| v || '_enable as end_enabled,start_time_'
|| v || ' as starttime,stop_time_'
|| v || ' as endtime,sysdate as now FROM agent_settime_onself a WHERE '
||v_clause;

--dbms_output.put_line(v_sql);
execute immediate v_sql;

open l_alldata for v_sql;
loop
FETCH l_alldata INTO v_id, v_type,v_bgn_enabled, v_end_enabled,v_starttime,v_endtime,v_now;
EXIT WHEN l_alldata%NOTFOUND;
IF v_bgn_enabled = 'Y' and
ABS(TO_DATE(v_starttime,'HH24:MI') -
TO_DATE(TO_CHAR(v_now,'HH24:MI'),'HH24:MI')) <= interval THEN

IF v_type = 1 THEN
UPDATE agent_card_type SET is_saling = 'Y' WHERE agent_card_type_id = v_id;

ELSIF v_type = 2 THEN
UPDATE agent_card_type SET provider_saling = 'Y' WHERE agent_card_type_id = v_id;

END IF;
END IF;

IF v_end_enabled = 'Y'
AND ABS(TO_DATE(v_endtime,'HH24:MI') - TO_DATE(TO_CHAR(v_now,'HH24:MI'),'HH24:MI')) <= interval THEN
IF v_type = 1 THEN
UPDATE agent_card_type SET is_saling = 'N' WHERE agent_card_type_id = v_id;

ELSIF v_type = 2 THEN
UPDATE agent_card_type SET provider_saling = 'N' WHERE agent_card_type_id = v_id;

END IF;
END IF;
end loop;
commit;
close l_alldata;
end;
/