CREATE TABLE TEST_DATA(ID NUMBER, VALUE_DATE DATE, SERIAL NUMBER);
SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;
SET DEFINE OFF;
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
BEGIN
FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
LOOP
FOR I IN (SELECT ROWNUM SERIAL_NUM,
ID,
VALUE_DATE,
ROWID UPDATE_NUMBER
FROM ( SELECT ID, VALUE_DATE, ROWID
FROM TEST_DATA
WHERE ID = S.SL
ORDER BY 1, 2))
LOOP
UPDATE TEST_DATA
SET SERIAL = I.SERIAL_NUM
WHERE ROWID = I.UPDATE_NUMBER;
END LOOP;
END LOOP;
END;
SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;
SQL> CREATE TABLE TEST_DATA(ID NUMBER, VALUE_DATE DATE, SERIAL NUMBER);
Table created.
SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
2 FROM TEST_DATA
3 ORDER BY 1, 2,3;
no rows selected
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> BEGIN
2 FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
3 LOOP
4 FOR I IN (SELECT ROWNUM SERIAL_NUM,
5 ID,
6 VALUE_DATE,
7 ROWID UPDATE_NUMBER
8 FROM ( SELECT ID, VALUE_DATE, ROWID
9 FROM TEST_DATA
10 WHERE ID = S.SL
11 ORDER BY 1, 2))
12 LOOP
13 UPDATE TEST_DATA
14 SET SERIAL = I.SERIAL_NUM
15 WHERE ROWID = I.UPDATE_NUMBER;
16 END LOOP;
17 END LOOP;
18 END;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
2 FROM TEST_DATA
3 ORDER BY 1, 2,3;
ID VALUE_DAT SERIAL
---------- --------- ----------
1 01-SEP-14 1
1 01-SEP-14 2
1 01-SEP-14 3
1 01-SEP-14 4
1 02-SEP-14 5
1 02-SEP-14 6
2 01-SEP-14 1
2 01-SEP-14 2
2 03-SEP-14 3
2 03-SEP-14 4
3 01-SEP-14 1
ID VALUE_DAT SERIAL
---------- --------- ----------
3 02-SEP-14 2
12 rows selected.
SQL>
SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;
SET DEFINE OFF;
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(ID, VALUE_DATE)
Values
(3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
BEGIN
FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
LOOP
FOR I IN (SELECT ROWNUM SERIAL_NUM,
ID,
VALUE_DATE,
ROWID UPDATE_NUMBER
FROM ( SELECT ID, VALUE_DATE, ROWID
FROM TEST_DATA
WHERE ID = S.SL
ORDER BY 1, 2))
LOOP
UPDATE TEST_DATA
SET SERIAL = I.SERIAL_NUM
WHERE ROWID = I.UPDATE_NUMBER;
END LOOP;
END LOOP;
END;
SELECT ID, VALUE_DATE, SERIAL
FROM TEST_DATA
ORDER BY 1, 2,3;
SQL> CREATE TABLE TEST_DATA(ID NUMBER, VALUE_DATE DATE, SERIAL NUMBER);
Table created.
SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
2 FROM TEST_DATA
3 ORDER BY 1, 2,3;
no rows selected
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (1, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (3, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (2, TO_DATE('09/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into TEST_DATA
2 (ID, VALUE_DATE)
3 Values
4 (3, TO_DATE('09/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> BEGIN
2 FOR S IN (SELECT DISTINCT ID SL FROM TEST_DATA)
3 LOOP
4 FOR I IN (SELECT ROWNUM SERIAL_NUM,
5 ID,
6 VALUE_DATE,
7 ROWID UPDATE_NUMBER
8 FROM ( SELECT ID, VALUE_DATE, ROWID
9 FROM TEST_DATA
10 WHERE ID = S.SL
11 ORDER BY 1, 2))
12 LOOP
13 UPDATE TEST_DATA
14 SET SERIAL = I.SERIAL_NUM
15 WHERE ROWID = I.UPDATE_NUMBER;
16 END LOOP;
17 END LOOP;
18 END;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT ID, VALUE_DATE, SERIAL
2 FROM TEST_DATA
3 ORDER BY 1, 2,3;
ID VALUE_DAT SERIAL
---------- --------- ----------
1 01-SEP-14 1
1 01-SEP-14 2
1 01-SEP-14 3
1 01-SEP-14 4
1 02-SEP-14 5
1 02-SEP-14 6
2 01-SEP-14 1
2 01-SEP-14 2
2 03-SEP-14 3
2 03-SEP-14 4
3 01-SEP-14 1
ID VALUE_DAT SERIAL
---------- --------- ----------
3 02-SEP-14 2
12 rows selected.
SQL>
No comments:
Post a Comment