Oracle PL/SQL - %ISOPEN Explicit Cursor Attribute

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor Attributes

Introduction

The following code opens the explicit cursor c1 only if it is not open and closes it only if it is open.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w w . j  a  va2s.  c  o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, salary FROM emp
  4      WHERE ROWNUM < 11;
  5
  6    the_name emp.last_name%TYPE;
  7    the_salary emp.salary%TYPE;
  8  BEGIN
  9    IF NOT c1%ISOPEN THEN
 10      OPEN c1;
 11    END IF;
 12
 13    FETCH c1 INTO the_name, the_salary;
 14
 15    IF c1%ISOPEN THEN
 16      CLOSE c1;
 17    END IF;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>

SQL>

Related Topic