Oracle PL/SQL - PL SQL Cursor Explicit Cursors

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

Introduction

An explicit cursor is a session cursor.

You must declare and define an explicit cursor by giving it a name and associating it with a query.

You can open the explicit cursor with the OPEN statement, fetch rows from the result set with the FETCH statement, and close the explicit cursor with the CLOSE statement.

You can also use the explicit cursor in a cursor FOR LOOP statement.

Declaring Explicit Cursors

An explicit cursor declaration has this syntax: 

CURSOR cursor_name [ parameter_list ] RETURN return_type; 

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ] 
IS select_statement;

The following code declares and defines three explicit cursors.

Demo

SQL>
SQL> drop table departments;

Table dropped.--   w w  w  . ja v a2s .c om

SQL> CREATE TABLE departments(
  2  department_id NUMBER(4),
  3  department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,
  4  manager_id NUMBER(6),
  5  location_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO departments VALUES( 10, 'Administration', 200, 1700);
SQL> INSERT INTO departments VALUES( 20, 'Marketing', 201, 1800);
SQL> INSERT INTO departments VALUES( 30, 'Purchasing', 114, 1700);
SQL> INSERT INTO departments VALUES( 40, 'Human Resources', 203, 2400);
SQL> INSERT INTO departments VALUES( 50, 'Shipping', 121, 1500);
SQL>
SQL> drop table emp;

Table dropped.

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-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL>
SQL> DECLARE
  2    CURSOR c1 RETURN departments%ROWTYPE;    -- Declare c1
  3
  4    CURSOR c2 IS           -- Declare and define c2
  5      SELECT empid, job_id, salary FROM emp
  6      WHERE salary > 2000;
  7
  8    CURSOR c1 RETURN departments%ROWTYPE IS  -- Define c1,
  9      SELECT * FROM departments              -- repeating return type
 10      WHERE department_id = 110;
 11
 12    CURSOR c3 RETURN locations%ROWTYPE;      -- Declare c3
 13
 14    CURSOR c3 IS           -- Define c3,
 15      SELECT * FROM locations                -- omitting return type
 16      WHERE country_id = 'JP';
 17  BEGIN
 18    NULL;
 19  END;
 20  /


SQL>

Related Topics