表 : given_dates
BOOK_DT1 BOOK_DT2 USERID
--------- --------- --------------------
22-SEP-17 12-NOV-17 rahul
20-JAN-17 10-FEB-17 ABC
02-MAR-17 10-MAR-17 ZXC
13-NOV-17 14-NOV-17 tth
当我插入一行时:
insert into given_dates('01-JAN-2017','05-JAN-2017','AAA');
应调用触发器以将详细信息插入另一个表中,如下所示
表 : all_dates_used
表 : given_dates
DATET1 USERID
--------- ---------
01-JAN-17 AAA
02-JAN-17 AAA
03-JAN-17 AAA
04-JAN-17 AAA
05-JAN-17 AAA
如果我很好地理解您的需求,这可能是一种方法:
create or replace trigger insertDates
after insert on given_dates
for each row
begin
insert into all_dates_used(DATET1 , USERID)
select :new.BOOK_DT1 + level -1, :new.USERID
from dual
connect by :new.BOOK_DT1 + level -1 <= :new.BOOK_DT2;
end;
测试:
SQL> select * from all_dates_used;
no rows selected
SQL> select * from given_dates;
no rows selected
SQL> insert into given_dates (BOOK_DT1, BOOK_DT2, USERID) values (date '2017-02-01', date '2017-02-08', 'USR');
1 row created.
SQL> select * from all_dates_used;
DATET1 USERID
--------- ----------
01-FEB-17 USR
02-FEB-17 USR
03-FEB-17 USR
04-FEB-17 USR
05-FEB-17 USR
06-FEB-17 USR
07-FEB-17 USR
08-FEB-17 USR
8 rows selected.
SQL>