PL-SQL Cursor based Records

PL-SQL Cursor based Records-Learn how to declare and define a Cursor with select statement. Also Learn use of cursor declaration with %ROWTYPE and cursor field declaration with %TYPE with Examples.

For Cursor based records structure is taken from the select query list of a cursor.
Each field in the select query list of cursor can access cursor base records.

Cursor based records are replica of select query list of a cursor.

yo can defines column aliases in the select query of a cursor.

Cursor-based record declaration

Syntax

DECLARE
<CURSOR_RECORD> CURSOR_NAME%ROWTYPE;

Cursor-based field declaration

Syntax

CURSOR_FILED CURSOR_RECORD%TYPE

Below are the programs to show how to use %ROWTYPE and %TYPE in cursors.

Exampe Program – 1 – Cursor_name With %ROWTYPE

DECLARE
  -- Cursor Declaration With Select statement
  CURSOR EMPLOYEE_CUR
  IS
    SELECT * FROM EMPLOYEE_TBL1 WHERE ID <=3;
-- declaring replica of cursor with Original Cursor.
  EMPLOYEE_REC EMPLOYEE_CUR%ROWTYPE;
BEGIN
  OPEN EMPLOYEE_CUR;
  LOOP
    FETCH EMPLOYEE_CUR INTO EMPLOYEE_REC;
    EXIT
  WHEN EMPLOYEE_CUR%NOTFOUND;
    dbms_output.put_line('Employee ID: ' || EMPLOYEE_REC.id);
    dbms_output.put_line('Employee Name: ' || EMPLOYEE_REC.name);
    dbms_output.put_line('Employee Gender: ' || EMPLOYEE_REC.Gender);
    dbms_output.put_line('Employee Salary: ' || EMPLOYEE_REC.salary);
    dbms_output.put_line(' ');
  END LOOP;
END;

Output

Employee ID: 1
Employee Name: Tom
Employee Gender: Male
Employee Salary: 4000

Employee ID: 2
Employee Name: Pam
Employee Gender: Female
Employee Salary: 3000

Employee ID: 3
Employee Name: John
Employee Gender: Male
Employee Salary: 3500

Exampe Program – 2 – Cursor_name With %ROWTYPE and Cursor_name.field_name with %TYPE

DECLARE
  CURSOR EMPLOYEE_CUR
  IS
    SELECT * FROM EMPLOYEE_TBL1;
  --DECLARATION OF CURSOR TYPE %ROWTYPE
  EMPLOYEE_REC EMPLOYEE_CUR%ROWTYPE;
  --DECLARATION CURSOR FIELD WITH %TYPE
  EMPLOYYEE_ID EMPLOYEE_REC.ID%TYPE;
BEGIN
  EMPLOYYEE_ID :=3;
  OPEN EMPLOYEE_CUR;
  LOOP
    FETCH EMPLOYEE_CUR INTO EMPLOYEE_REC;
    -- COMPARING THE % TYPE FILED VALUE WITH CURSOR%ROWTYPE Field value
    EXIT
  WHEN EMPLOYEE_REC.ID >
    EMPLOYYEE_ID;
    dbms_output.put_line('Employee ID: ' || EMPLOYEE_REC.id);
    dbms_output.put_line('Employee Name: ' || EMPLOYEE_REC.name);
    dbms_output.put_line('Employee Gender: ' || EMPLOYEE_REC.Gender);
    dbms_output.put_line('Employee Salary: ' || EMPLOYEE_REC.salary);
    dbms_output.put_line(' ');
  END LOOP;
END;

Output:

Employee ID: 1
Employee Name: Tom
Employee Gender: Male
Employee Salary: 4000

Employee ID: 2
Employee Name: Pam
Employee Gender: Female
Employee Salary: 3000

Employee ID: 3
Employee Name: John
Employee Gender: Male
Employee Salary: 3500

PL-SQL Cursor based Records