Explicit Cursor and For Cursor loop with Examples in PL/SQL Anonymous Block #DOTC_mdb

 

Explicit Cursor and For Cursor loop with Examples in PL/SQL Anonymous Block.

Hello Everyone,

Today we will see some examples of both Explicit and FOR CURSOR loop with some practical examples in Oracle PL/SQL Anonymous Block. Please see my previous blogs where I discussed about syntax and some basic examples of Cursor and also idea about %type and % rowtype uses in PL/SQL block for your reference.

https://dheeraj60.blogspot.com/2020/05/cursor-in-database-and-importance-of.html

https://dheeraj60.blogspot.com/2020/05/benefits-and-advantage-of-using-type.html

 

Now let’s start first with explicit cursor with one Program, as we know in Explicit Cursor programmers are allowed to create named context area to execute their DML operations to get more control over it. Explicit cursor is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor for reference so that we can use that cursor name in PL/SQL block. The steps involved in creating Explicit Cursor in Anonymous PL/SQL block is as below:

Declare the Cursor: Name of this context area is same as the cursor name also we can declare variables in PL/SQL block as per requirement of the program .Also we can mark cursor name anything that can be used in that session.

Open the Cursor: Opening the cursor means it will instruct the PL/SQL to allocate the memory for this cursor and make the cursor ready to fetch the records.

Fetch the data from into PL/SQL variables or in tables in the execution section.

Close the Cursor : Once all the record is fetched , we need to close the cursor so that the memory allocated to this context area will be released

 Syntax for Explicit Cursor:

DECLARE

CURSOR <cursor_name> IS <SELECT statement^>

<cursor_variable declaration>

BEGIN

OPEN <cursor_name>;

FETCH <cursor_name> INTO <cursor_variable>;

CLOSE <cursor_name>;

END;

Let’s take one example of explicit cursor where we fetch some records by using cursor  from two tables and display that value by using PL/SQL block. Below is the program.

declare

v_id employee.emp_id%type;   

v_name employee.emp_name%type;

v_dept department.dept_name%type;

cursor c1 is select a.emp_id,a.emp_name,b.dept_name

from employee a , department b

where a.emp_id=b.emp_id;

begin

open c1;

loop

FETCH c1 into v_id,v_name,v_dept;

exit when c1%notfound;

dbms_output.put_line(v_id||' '||v_name||' '||v_dept);

end loop;

close c1;

end;

 

In above program we simply fetch the records from employee and department table by using Cursor name C1 and put that value in variables and display records by using dbms_output.put_line . I use %type in variable as it automatically takes the data type of particular column from different tables also we can use varchar and number instead of %type , but for programmer it’s good to use %type so that they don’t need to check table description again and again to see the data types of particular column.

Below is the output of above program in Oracle.

 

I am writing another PL/SQL block where I put the values in a table by using explicit cursor . Program will be same only thing we just keep the data in object . Below is the program.

declare

v_id employee.emp_id%type;   

v_name employee.emp_name%type;

v_dept department.dept_name%type;

cursor c1 is select a.emp_id,a.emp_name,b.dept_name from employee a , department b where a.emp_id=b.emp_id;

begin

open c1;

loop

FETCH c1 into v_id,v_name,v_dept;

exit when c1%notfound;

insert into temp2 values(v_id,v_name,v_dept);

end loop;

close c1;

end;

select * from temp2;

Here we just put the values in temp2 table same as we used cursor to display the value above.

Below is the output of above program.

 

Now we can do the same with using FOR CURSOR Loop in PL/SQL block in shortcut metod. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop that’s why FOR CURSOR loop is shortcut as compared to explicit cursor and also easy to use.

Syntax for FOR CURSOR LOOP is as below :

DECLARE

CURSOR <cursor_name> IS <SELECT statement>;

BEGIN

  FOR I IN <cursor_name>

  LOOP

  ..

  END LOOP;

END;

 

Let’s take the same example which we used in explicit cursor for inserting record in table and displaying records by using FOR CURSOR LOOP in shortcut method. Below is the Program.

// Anonymous PL/SQL block for inserting records in table by using for cursor loop

declare

cursor c1 is select a.emp_id,a.emp_name,b.dept_name from employee a , department b where a.emp_id=b.emp_id;

begin

for i in c1

loop

insert into temp2 values(i.emp_id,i.emp_name,i.dept_name);

end loop;

end;

select * from temp2;

Below is the output of above program in Oracle .

 

Displaying Records by using for cursor loop in simple method for same program.

//Anonymous PL/SQL block for displaying records from 2 tables by using for cursor loop.

declare

cursor c1 is select a.emp_id,a.emp_name,b.dept_name from employee a , department b where a.emp_id=b.emp_id;

begin

for i in c1

loop

dbms_output.put_line(i.emp_id||' '||i.emp_name||' '||i.dept_name);

end loop;

end;

Below is the output of above Program in Oracle.

 

Please go through above programs and try to write as many cursors as you can so that it will be good for you once you start writing PL/SQL procedures, functions , packages etc.

Go through this blog carefully and let me know if you have any questions. This blog is very important for PL/SQL developer as cursor plays important role in writing PL/SQL sub-programs/Named block and once you have good hands on Anonymous block and good experience in writing cursors. This blog is also good for candidate who is preparing for job interview as database developer. In next blog I will come up with another topic.

Thanks.