|
需求:
給出如下條件進(jìn)行批處理編排
- 開始日期時(shí)間
- 重復(fù)間隔(分鐘)
- 重復(fù)次數(shù)
要求在檔期內(nèi)重復(fù)安排節(jié)目播出, 比如: 2003.01.01 08:00 開始每隔240分鐘播出一次, 一共播出100次
數(shù)據(jù)庫(kù)表格(CO_SCHEDULE)
------------------------------
N_PROGIDINT
DT_STARTTIMETIMESTAMP
DT_ENDTIMETIMESTAMP
存儲(chǔ)過程的實(shí)現(xiàn)：
create table co_schedule(n_progid int,dt_starttime timestamp,dt_endtime timestamp);
//創(chuàng)建函數(shù)：
create function add_program_time(int4,timestamp,int4,int4,int4) returns bool as '
declare
prog_id alias for $1;
duration_min alias for $3;
period_min alias for $4;
repeat_times alias for $5;
i int;
starttime timestamp;
ins_starttime timestamp;
ins_endtime timestamp;
begin
starttime :=$2;
i := 0;
while i<repeat_times loop
ins_starttime := starttime;
ins_endtime := timestamp_pl_span(ins_starttime,duration_min || ''mins'');
starttime := timestamp_pl_span(ins_starttime,period_min || ''mins'');
insert into co_schedule values(prog_id,ins_starttime,ins_endtime);
i := i+1;
end loop;
if i<repeat_times then
return false;
else
return true;
end if;
end;
'language 'plpgsql';
//執(zhí)行函數(shù)：
select add_program_time(1,'2002-10-20 0:0:0','5','60','5');
//查看結(jié)果：select * from co_schedule;
n_progid |dt_starttime| dt_endtime
----------+------------------------+------------------------
1 | 2002-10-20 00:00:00+08 | 2002-10-20 00:05:00+08
1 | 2002-10-20 01:00:00+08 | 2002-10-20 01:05:00+08
1 | 2002-10-20 02:00:00+08 | 2002-10-20 02:05:00+08
1 | 2002-10-20 03:00:00+08 | 2002-10-20 03:05:00+08
1 | 2002-10-20 04:00:00+08 | 2002-10-20 04:05:00+08
ps:
1.數(shù)據(jù)庫(kù)一加載 plpgsql語(yǔ)言。如沒有，
su - postgres
createlang plpgsql dbname
2.至于返回類型為bool,是因?yàn)槲也恢廊绾巫尯瘮?shù)不返回值。等待改進(jìn)。
|
