Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as RCOTRPR1
SQL>
SQL> CREATE TABLE idletest(ID NUMBER, NAME VARCHAR2(20), alarm_start_dt DATE, alarm_end_dt DATE);
Table created
SQL> INSERT INTO idletest VALUES(79270, 'XY_HJ',to_date('2014-01-10 20:57:02','yyyy-mm-dd HH24:MI:SS'),
2 to_date('2014-01-11 10:08:29','yyyy-mm-dd HH24:MI:SS'));
1 row inserted
SQL> INSERT INTO idletest VALUES(979734, 'XY_HJ',to_date('2014-01-08 17:23:17','yyyy-mm-dd HH24:MI:SS'),
2 to_date('2014-01-11 11:54:23','yyyy-mm-dd HH24:MI:SS'));
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT ID, NAME, to_char(alarm_start_dt,'yyyy-mm-dd HH24:MI:SS'),to_char(alarm_end_dt,'yyyy-mm-dd HH24:MI:SS') FROM idletest;
ID NAME TO_CHAR(ALARM_START_DT,'YYYY-M TO_CHAR(ALARM_END_DT,'YYYY-MM-
---------- -------------------- ------------------------------ ------------------------------
79270 XY_HJ 2014-01-10 20:57:02 2014-01-11 10:08:29
979734 XY_HJ 2014-01-08 17:23:17 2014-01-11 11:54:23
SQL> DECLARE
2 v_start DATE;
3 v_end DATE;
4 v_delimter NUMBER;
5 v_incre NUMBER;
6 BEGIN
7 for i IN (SELECT * FROM idletest) LOOP
8 v_start := i.alarm_start_dt;
9 v_end := i.alarm_end_dt;
10 v_delimter := TRUNC(v_end) - TRUNC(v_start);
11 IF v_delimter > 0 THEN
12 v_incre := 1;
13 UPDATE idletest SET alarm_end_dt = TRUNC(v_start) + 86399/86400 WHERE ID = i.id AND name = i.name
14 AND alarm_start_dt = i.alarm_start_dt AND alarm_end_dt = i.alarm_end_dt;
15 WHILE v_incre < v_delimter LOOP
16 INSERT INTO idletest VALUES(i.id, i.name, TRUNC(v_start) + v_incre, TRUNC(v_start) + v_incre + 86399/86400);
17 v_incre := v_incre + 1;
18 END LOOP;
19 INSERT INTO idletest VALUES(i.id, i.name, TRUNC(v_start) + v_delimter, v_end);
20 end IF;
21 END LOOP;
22 COMMIT;
23 END;
24 /
PL/SQL procedure successfully completed
SQL> SELECT ID, NAME, to_char(alarm_start_dt,'yyyy-mm-dd HH24:MI:SS'),to_char(alarm_end_dt,'yyyy-mm-dd HH24:MI:SS') FROM idletest order BY ID,alarm_start_dt ;
ID NAME TO_CHAR(ALARM_START_DT,'YYYY-M TO_CHAR(ALARM_END_DT,'YYYY-MM-
---------- -------------------- ------------------------------ ------------------------------
79270 XY_HJ 2014-01-10 20:57:02 2014-01-10 23:59:59
79270 XY_HJ 2014-01-11 00:00:00 2014-01-11 10:08:29
979734 XY_HJ 2014-01-08 17:23:17 2014-01-08 23:59:59
979734 XY_HJ 2014-01-09 00:00:00 2014-01-09 23:59:59
979734 XY_HJ 2014-01-10 00:00:00 2014-01-10 23:59:59
979734 XY_HJ 2014-01-11 00:00:00 2014-01-11 11:54:23
6 rows selected
SQL> DROP TABLE idletest;
Table dropped
SQL>
Connected as RCOTRPR1
SQL>
SQL> CREATE TABLE idletest(ID NUMBER, NAME VARCHAR2(20), alarm_start_dt DATE, alarm_end_dt DATE);
Table created
SQL> INSERT INTO idletest VALUES(79270, 'XY_HJ',to_date('2014-01-10 20:57:02','yyyy-mm-dd HH24:MI:SS'),
2 to_date('2014-01-11 10:08:29','yyyy-mm-dd HH24:MI:SS'));
1 row inserted
SQL> INSERT INTO idletest VALUES(979734, 'XY_HJ',to_date('2014-01-08 17:23:17','yyyy-mm-dd HH24:MI:SS'),
2 to_date('2014-01-11 11:54:23','yyyy-mm-dd HH24:MI:SS'));
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT ID, NAME, to_char(alarm_start_dt,'yyyy-mm-dd HH24:MI:SS'),to_char(alarm_end_dt,'yyyy-mm-dd HH24:MI:SS') FROM idletest;
ID NAME TO_CHAR(ALARM_START_DT,'YYYY-M TO_CHAR(ALARM_END_DT,'YYYY-MM-
---------- -------------------- ------------------------------ ------------------------------
79270 XY_HJ 2014-01-10 20:57:02 2014-01-11 10:08:29
979734 XY_HJ 2014-01-08 17:23:17 2014-01-11 11:54:23
SQL> DECLARE
2 v_start DATE;
3 v_end DATE;
4 v_delimter NUMBER;
5 v_incre NUMBER;
6 BEGIN
7 for i IN (SELECT * FROM idletest) LOOP
8 v_start := i.alarm_start_dt;
9 v_end := i.alarm_end_dt;
10 v_delimter := TRUNC(v_end) - TRUNC(v_start);
11 IF v_delimter > 0 THEN
12 v_incre := 1;
13 UPDATE idletest SET alarm_end_dt = TRUNC(v_start) + 86399/86400 WHERE ID = i.id AND name = i.name
14 AND alarm_start_dt = i.alarm_start_dt AND alarm_end_dt = i.alarm_end_dt;
15 WHILE v_incre < v_delimter LOOP
16 INSERT INTO idletest VALUES(i.id, i.name, TRUNC(v_start) + v_incre, TRUNC(v_start) + v_incre + 86399/86400);
17 v_incre := v_incre + 1;
18 END LOOP;
19 INSERT INTO idletest VALUES(i.id, i.name, TRUNC(v_start) + v_delimter, v_end);
20 end IF;
21 END LOOP;
22 COMMIT;
23 END;
24 /
PL/SQL procedure successfully completed
SQL> SELECT ID, NAME, to_char(alarm_start_dt,'yyyy-mm-dd HH24:MI:SS'),to_char(alarm_end_dt,'yyyy-mm-dd HH24:MI:SS') FROM idletest order BY ID,alarm_start_dt ;
ID NAME TO_CHAR(ALARM_START_DT,'YYYY-M TO_CHAR(ALARM_END_DT,'YYYY-MM-
---------- -------------------- ------------------------------ ------------------------------
79270 XY_HJ 2014-01-10 20:57:02 2014-01-10 23:59:59
79270 XY_HJ 2014-01-11 00:00:00 2014-01-11 10:08:29
979734 XY_HJ 2014-01-08 17:23:17 2014-01-08 23:59:59
979734 XY_HJ 2014-01-09 00:00:00 2014-01-09 23:59:59
979734 XY_HJ 2014-01-10 00:00:00 2014-01-10 23:59:59
979734 XY_HJ 2014-01-11 00:00:00 2014-01-11 11:54:23
6 rows selected
SQL> DROP TABLE idletest;
Table dropped
SQL>
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货