我沒有環境,簡單寫一下: create or replace procedure SP_UpdateActivityT as v_period_no ActivityT.period_no%type; v_create_date ActivityT.period_no%type; cursor c is select period_no,create_date from ActivityT; begin open c; fetch c into v_period_no,v_create_date; while c%found loop
--Update Record if v_period_no=1000 then
end if; if ... fetch c into v_period_no,v_create_date; end loop; commit; exception when others then rollback; raise; end SP_UpdateActivityT;
我也只有SQL SERVER 环境
create or replace procedure sp_activity_date is v_activity_no activityt.activity_no%type; v_start_date activityt.start_date%type; v_period_no activityt.period_no%type; cursor cur_get_data is select activity_no,start_date,period_no from ActivityT; begin if not cur_get_data%isopen then open cur_get_data; end if; LOOP fetch cur_get_data into v_activity_no,v_start_date,v_period_no; if cur_get_data%notfound then close cur_get_data; exit; end if; begin if substr(v_period_no,1,1) = 1 then update activityt set start_date = to_char(sysdate,'YYYYMMDD') where activity_no = v_activity_no; end if;
if substr(v_period_no,1,1) = 2 then update activityt set start_date = to_char(sysdate-to_char(sysdate,'d')+substr(v_period_no,5,5)+1,'YYYYMMDD') where activity_no = v_activity_no; end if;
if substr(v_period_no,1,1) = 3 then update activityt set start_date = to_char(sysdate-to_char(sysdate,'dd')+substr(v_period_no,4,5),'YYYYMMDD') where activity_no = v_activity_no; if not (to_char(sysdate,'mm') = to_char(sysdate-to_char(sysdate,'dd')+substr(v_period_no,4,5),'mm')) then update activityt set start_date = to_char(last_day(sysdate),'YYYYMMDD') where activity_no = v_activity_no; end if; end if;
if substr(v_period_no,1,1) = 4 then update activityt set start_date = to_char(sysdate,'yyyy')||substr(v_period_no,2,5) where activity_no = v_activity_no; if substr(v_period_no,2,5) = '0229' then if not (((mod(to_char(sysdate,'yyyy'),4) = 0) and mod(to_char(sysdate,'yyyy'),400) > 0) or (mod(to_char(sysdate,'yyyy'),100) = 0)) then update activityt set start_date = to_char(sysdate,'yyyy')||'0228' where activity_no = v_activity_no; end if; end if; end if; exception when others then dbms_output.put_line(substrb(sqlerrm,1,190)); end; END LOOP; commit; end sp_activity_date; /
create or replace function getdate(p_date in date,i in varchar2) return date as j number; begin
select to_char(p_date,'D') into j from dual; if i>j then return p_date+(i-j); elsif i=j then return p_date; else return p_date+(7-j+i); end if; end; /create or replace procedure get_start_date as cursor t_sor is select activity_no,create_date,substr(period_no,1,1) p1,period_no from ActivityT where start_date is null; begin for v_sor in t_sor loop if p1='1' then update ActivityT set start_date=v_sor.create_date where activity_no=v_sor.activity_no; elsif p1='2' then update ActivityT set start_date=getdate(v_sor.create_date,substr(v_sor.period_no,length(v_sor.period_no)-1,1)) where activity_no=v_sor.activity_no; elsif p1='3' then update ActivityT set start_date=to_date(to_char(v_sor.create_date,'yyyymm')||substr(v_sor.period_no,length(v_sor.period_no)-2,2),'yyyymmdd') where activity_no=v_sor.activity_no; elsif p1='4' then null; --此处要做一个函数,计算闰年的问题。 end if; end; /declare jobno number; begin dbms_job.submit(jobno,'get_start_date;',sysdate,'trunc(sysdate,''mm'')+1');--每天零点执行 commit; end; /
来更新start_date,也只更新start_date
(要考虑闰年的问题)
create or replace procedure SP_UpdateActivityT
as
v_period_no ActivityT.period_no%type;
v_create_date ActivityT.period_no%type;
cursor c is select period_no,create_date from ActivityT;
begin
open c;
fetch c into v_period_no,v_create_date;
while c%found loop
--Update Record
if v_period_no=1000 then
end if;
if ...
fetch c into v_period_no,v_create_date;
end loop;
commit;
exception
when others then
rollback;
raise;
end SP_UpdateActivityT;
is
v_activity_no activityt.activity_no%type;
v_start_date activityt.start_date%type;
v_period_no activityt.period_no%type;
cursor cur_get_data is
select activity_no,start_date,period_no
from ActivityT;
begin
if not cur_get_data%isopen then
open cur_get_data;
end if;
LOOP
fetch cur_get_data into v_activity_no,v_start_date,v_period_no;
if cur_get_data%notfound then
close cur_get_data;
exit;
end if;
begin
if substr(v_period_no,1,1) = 1 then
update activityt
set start_date = to_char(sysdate,'YYYYMMDD')
where activity_no = v_activity_no;
end if;
if substr(v_period_no,1,1) = 2 then
update activityt
set start_date = to_char(sysdate-to_char(sysdate,'d')+substr(v_period_no,5,5)+1,'YYYYMMDD')
where activity_no = v_activity_no;
end if;
if substr(v_period_no,1,1) = 3 then
update activityt
set start_date = to_char(sysdate-to_char(sysdate,'dd')+substr(v_period_no,4,5),'YYYYMMDD')
where activity_no = v_activity_no;
if not (to_char(sysdate,'mm') = to_char(sysdate-to_char(sysdate,'dd')+substr(v_period_no,4,5),'mm')) then
update activityt
set start_date = to_char(last_day(sysdate),'YYYYMMDD')
where activity_no = v_activity_no;
end if;
end if;
if substr(v_period_no,1,1) = 4 then
update activityt
set start_date = to_char(sysdate,'yyyy')||substr(v_period_no,2,5)
where activity_no = v_activity_no;
if substr(v_period_no,2,5) = '0229' then
if not (((mod(to_char(sysdate,'yyyy'),4) = 0) and mod(to_char(sysdate,'yyyy'),400) > 0)
or (mod(to_char(sysdate,'yyyy'),100) = 0)) then
update activityt
set start_date = to_char(sysdate,'yyyy')||'0228'
where activity_no = v_activity_no;
end if;
end if;
end if;
exception
when others then
dbms_output.put_line(substrb(sqlerrm,1,190));
end;
END LOOP;
commit;
end sp_activity_date;
/
create or replace function getdate(p_date in date,i in varchar2)
return date
as
j number;
begin
select to_char(p_date,'D') into j from dual;
if i>j then
return p_date+(i-j);
elsif i=j then
return p_date;
else
return p_date+(7-j+i);
end if;
end;
/create or replace procedure get_start_date
as
cursor t_sor is
select activity_no,create_date,substr(period_no,1,1) p1,period_no from ActivityT where start_date is null;
begin
for v_sor in t_sor loop
if p1='1' then
update ActivityT set start_date=v_sor.create_date where activity_no=v_sor.activity_no;
elsif p1='2' then
update ActivityT set start_date=getdate(v_sor.create_date,substr(v_sor.period_no,length(v_sor.period_no)-1,1)) where activity_no=v_sor.activity_no;
elsif p1='3' then
update ActivityT set start_date=to_date(to_char(v_sor.create_date,'yyyymm')||substr(v_sor.period_no,length(v_sor.period_no)-2,2),'yyyymmdd') where activity_no=v_sor.activity_no;
elsif p1='4' then
null; --此处要做一个函数,计算闰年的问题。
end if;
end;
/declare
jobno number;
begin
dbms_job.submit(jobno,'get_start_date;',sysdate,'trunc(sysdate,''mm'')+1');--每天零点执行
commit;
end;
/
明天还会上网,有问题直接发信息给我