17,082
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE TEST
AS
I NUMBER(3);
BEGIN
FOR ROWDATA IN (select emp_id,END_DATE - BEGIN_DATE DAYNUM,BEGIN_DATE FROM T)LOOP
I:=0;
LOOP
INSERT INTO T_NEW VALUES(ROWDATA.EMP_ID,ROWDATA.BEGIN_DATE+I);
I:=I+1;
EXIT WHEN I>ROWDATA.DAYNUM;
END LOOP;
END LOOP;
COMMIT;
END;
-- 这个过滤条件,要用 start with 语句,不要写在 where 中
SQL>
SQL> create table t(emp_id varchar(10), begin_date date, end_date date);
Table created
SQL> begin
2 insert into t values(21021339, date'2017-05-31', date'2017-06-01');
3 insert into t values(21021227, date'2017-05-31', date'2017-06-04');
4 insert into t values(21021110, date'2017-05-31', date'2017-06-05');
5 end;
6 /
PL/SQL procedure successfully completed
SQL> select emp_id,begin_date + rownum -1
2 from t
3 start with emp_id ='21021227'
4 connect by rownum <= end_date - begin_date +1
5 and prior emp_id = emp_id
6 and prior dbms_random.value is not null;
EMP_ID BEGIN_DATE+ROWNUM-1
---------- -------------------
21021227 2017/5/31
21021227 2017/6/1
21021227 2017/6/2
21021227 2017/6/3
21021227 2017/6/4
SQL> drop table t purge;
Table dropped
SQL>