PL/SQL Cursors


Prev Tutorial Next Tutorial

Cursors in PL/SQL

A Cursors is a temporary work area created in the system memory when a SQL statement is executed. It is a temporary memory which is used to fetch more than one record at a time from existing table.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Why use Cursor ?

It is a temporary memory which is used to fetch more than one record at a time from existing table.

Type of cursor

Cursor are broadly divided into two types;

  • Implicit cursor
  • Explicit cursor

Implicit cursor

This type of cursor is perform by the system internally those cursor can be called as implicit cursor.

Explicit cursor

This type of cursor is performed by the user manually or programatically those cursor can be called as explicit cursor.

Steps to perform cursor

StepsSyntax
Declare the cursoropen cursor_name;
Open the cursoropen cursor_name;
Fetch the record from the cursorfetch cursor_name into variables;
Close the cursorclose cursor_name;

Declare the cursor

Syntax

declare
a emp %rowtype;
cursor c is select * from emp where depno=&deptno;
begin
open c;
fetch c into a;
dbms_output.put_line(a.empno || ' ' a.ename || ' ' || a.sal);
close c;
end;

Note: In above exaple ruturn only one record because the statements are lies within the scopes.

% found

This attribute is used for check whether the record is found or not in the cursor. It return boolean value either true or false. If the record found then it return ture otherwise false.

Syntax

declare
a emp %rowtype;
cursor c is select * from emp where depno=&deptno;
begin
open c;
loop fetch c into a;
if c % found then
dbms_output.put_line(a.empno || ' ' a.ename || ' ' || a.sal);
else
exit;
end if;
end loop;
close c;
end;

Prev Tutorial Next Tutorial

Advertisements




Facebook Likes

Advertisements