After the cursor is opened, the book table is dropped prior to looping through the cursor. : Close Cursor « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Close Cursor

After the cursor is opened, the book table is dropped prior to looping through the cursor.

  

SQL> CREATE TABLE book (
  2    isbn         CHAR(10),
  3    status       VARCHAR2(25) CHECK (status IN ('IN STOCK', 'BACKORDERED', 'FUTURE')),
  4    status_date  DATE,
  5    amount       NUMBER
  6  );

Table created.

SQL>
SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ('1', 'BACKORDERED', TO_DATE('06-JUN-2004', 'DD-MON-YYYY'), 1000);

1 row created.

SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ('2', 'IN STOCK', NULL, 5000);

1 row created.

SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ('3', 'IN STOCK', NULL, 1000);

1 row created.

SQL> INSERT INTO book (isbn, status, status_date, amount)VALUES ('7', 'IN STOCK', NULL, 1000);

1 row created.

SQL>
SQL>
SQL>
SQL> DECLARE
  2
  3     v_isbn book.ISBN%TYPE;
  4
  5     CURSOR book_cur IS SELECT isbn FROM book;
  6
  7  BEGIN
  8     OPEN book_cur;
  9
 10     EXECUTE IMMEDIATE ('DROP TABLE book');
 11
 12     LOOP
 13        FETCH book_cur INTO v_isbn;
 14        EXIT WHEN book_cur%NOTFOUND;
 15
 16        DBMS_OUTPUT.PUT_LINE(v_isbn);
 17
 18     END LOOP;
 19
 20     CLOSE book_cur;
 21
 22  END;
 23  /
1
2
3
7

   
    
  

Related examples in the same category