Cursor with for loop in Oracle

Cursor with for loop in Oracle -Learn how to use cursors without using OPEN, FETCH and CLOSE  Statements and also learn how to use Select statement as a cursor in for loop.

Cursor with for loop in Oracle

Cursor FOR LOOP

we can use sql statement in for loops as cursors, instead of using OPEN, FETCH, and CLOSE statement in cursors.

using cursor in for loop can do in 2 ways.

1. SQL Cursor FOR LOOP-using select query in for loop.
2. Explicit Cursor FOR LOOP-defining the cursor in declaration block and using the same in the for loop

SQL Cursor FOR LOOP

Include the text of the query in the for loop.
PL/SQL creates a record variable with fields corresponding to the columns of the result set.
Cursor variable Refers to the fields inside the loop.

Here is an example to get the name and department name of employees

Example for SQL Cursor FOR LOOP

for table reference to the below examples please click on the following department_tbl1 link

BEGIN
  FOR crec IN
  (SELECT name,
    dept_name
  FROM employee_tbl1 e
  INNER JOIN department_tbl1 d
  ON d.id = e.departmentid
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Name = ' || crec.name || ', 
     dept_name = ' || crec.dept_name);
  END LOOP;
END;

Output

Name = Tom, dept_name = IT
Name = Pam, dept_name = HR
Name = John, dept_name = IT
Name = Sam, dept_name = Payroll
Name = Todd, dept_name = Payroll
Name = Ben, dept_name = IT
Name = Sara, dept_name = HR
Name = Valarie, dept_name = IT

• Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record.
• The sequence of statements inside the loop is executed once for each row that satisfies the query.
• When you leave the loop, the cursor is closes automatically.
• The cursor is closed even if you use an EXIT or GOTO statement to leave the loop before all rows are fetched, or an exception is raised inside the loop

Explicit Cursor FOR LOOP

Declare and define the cursor with sql statement in Declaration block.
Use this cursor anywhere in the execution block depends on requirement.

Example for Explicit Cursor FOR LOOP

DECLARE
  CURSOR c1
  IS
    SELECT name,
      dept_name
    FROM employee_tbl1 e
    INNER JOIN department_tbl1 d
    ON d.id = e.departmentid;
BEGIN
  FOR crec IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Name = ' || crec.name || ', 
    dept_name = ' || crec.dept_name);
  END LOOP;
END;

Output

Name = Tom, dept_name = IT
Name = Pam, dept_name = HR
Name = John, dept_name = IT
Name = Sam, dept_name = Payroll
Name = Todd, dept_name = Payroll
Name = Ben, dept_name = IT
Name = Sara, dept_name = HR
Name = Valarie, dept_name = IT