PL/SQL Cursors

What is Cursor?

  • A cursor is a pointer to the private memory area allocated by the Oracle server.
  • A cursor is used to handle the result set of a SELECT statement.
  • A Cursor can hold rows selected in SQL statement.

There are two types of cursors:

  1. Implicit: Created and managed internally by the Oracle server to process SQL statements
  2. Explicit: Declared explicitly by the programmer

SQL Cursor Attributes for Implicit Cursors

Example:

  DECLARE
  v_rows_deleted VARCHAR2(30)
  v_empno employees.employee_id%TYPE := 101;
  BEGIN
  DELETE FROM employees 
  WHERE employee_id = v_empno;
  v_rows_deleted := (SQL%ROWCOUNT ||' row deleted.');
  DBMS_OUTPUT.PUT_LINE (v_rows_deleted);
  END;

Explicit Cursor


1.Declaring the Cursor

   CURSOR cursor_name IS
        select_statement;

Example:

DECLARE
CURSOR c_emp_cursor IS 
SELECT employee_id, last_name FROM employees WHERE employee_id=101;

2. Open Cursor

 Open Cursor_name;

Example:

DECLARE
CURSOR c_emp_cursor IS 
SELECT employee_id, last_name FROM employees WHERE employee_id=101;
BEGIN
OPEN c_emp_cursor;

3.Fetch Current row

DECLARE
CURSOR c_emp_cursor IS 
SELECT employee_id, last_name FROM employees WHERE employee_id =101;

v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN

OPEN c_emp_cursor;

FETCH c_emp_cursor INTO v_empno, v_lname;

DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname); 
END;
/

4. Close cursor

 Close cursor_name

Example:

DECLARE
CURSOR c_emp_cursor IS 
SELECT employee_id, last_name FROM employees WHERE employee_id =101;

v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN

OPEN c_emp_cursor;

FETCH c_emp_cursor INTO v_empno, v_lname;

DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname); 
Close c_emp_cursor
END;
/

Output:

What if select query return more than one rows?

If query returns multiple rows then you have to fetch data using LOOP.

Example:

DECLARE
  CURSOR emp_cursor
  IS
    SELECT employee_id, last_name FROM employees WHERE department_id =30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_empno, v_lname;
    EXIT
  WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( v_empno ||' '||v_lname);
  END LOOP;
END;

Fetching data using PL/SQL Record (with the help of cursor)

DECLARE
  CURSOR emp_cursor
  IS
    SELECT employee_id, last_name FROM employees WHERE department_id =20;
  v_emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp_record;
    EXIT
  WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( v_emp_record.employee_id ||' '||v_emp_record.last_name);
  END LOOP;
  CLOSE emp_cursor;
END;

Fetching data using for LOOP(with the help of cursor)

DECLARE
  CURSOR emp_cursor
  IS
    SELECT employee_id, last_name FROM employees WHERE department_id =20;
BEGIN
  FOR emp_record IN emp_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE( emp_record.employee_id||' ' ||emp_record.last_name);
  END LOOP;
END;
/

Cursor Attributes

Leave a Reply

Your email address will not be published. Required fields are marked *