Delete from table where current of cursor

  


SQL>
SQL> CREATE TABLE myTable(
  2      e INTEGER,
  3      f INTEGER
  4  );

Table created.

SQL>
SQL> DELETE FROM myTable;

0 rows deleted.

SQL> INSERT INTO myTable VALUES(1, 3);

1 row created.

SQL> INSERT INTO myTable VALUES(2, 4);

1 row created.

SQL>
SQL> DECLARE
  2        a myTable.e%TYPE;
  3        b myTable.f%TYPE;
  4        CURSOR myTableCursor IS
  5            SELECT e, f
  6            FROM myTable
  7            WHERE e < f
  8            FOR UPDATE;
  9    BEGIN
 10       OPEN myTableCursor;
 11       LOOP
 12           FETCH myTableCursor INTO a, b;
 13           EXIT WHEN myTableCursor%NOTFOUND;
 14           DELETE FROM myTable WHERE CURRENT OF myTableCursor;
 15           INSERT INTO myTable VALUES(b, a);
 16       END LOOP;
 17
 18       CLOSE myTableCursor;
 19
 20   END;
 21
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>

Related examples in the same category

1.	Explicit Cursor Demo
2.	Implicit and Explicit Cursors
3.	an explicit cursor that selects data
4.	An explicit cursor fetch loop.
5.	Use cursor to store the row count
6.	Column value indexed cursor
7.	Combine for loop and if statement to check the value in cursor
8.	Cursor performance
9.	If statement and single piece value in cursor
10.	Use explicit cursor to fetch and store value to number variable
11.	Write an explicit cursor in a FOR loop and use the data

Delete from table where current of cursor : Explicit Cursor « Cursor « Oracle PL / SQL

Related examples in the same category