Scribd
Upload
5 views3 pages

Understanding PLSQL Cursors Explained

PLSQL Cursors are areas in SQL/Oracle for storing fetched data, with two types: implicit (automatically managed by Oracle) and explicit (user-defined). Cursors process data row by row and have attributes that return boolean values indicating the state of the cursor. The document provides syntax for declaring, opening, fetching from, and closing explicit cursors, along with an example demonstrating their use.

Uploaded by

Kiruba Shankar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views3 pages

Understanding PLSQL Cursors Explained

PLSQL Cursors are areas in SQL/Oracle for storing fetched data, with two types: implicit (automatically managed by Oracle) and explicit (user-defined). Cursors process data row by row and have attributes that return boolean values indicating the state of the cursor. The document provides syntax for declaring, opening, fetching from, and closing explicit cursors, along with an example demonstrating their use.

Uploaded by

Kiruba Shankar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

PLSQL Cursors:

Cursor is part of area in SQL or Oracle.


When a fetching data from oracle server, its create a place to store that data,
and once done processing of data, then closed that area.

Two types:

Implicit cursor - Oracle defined cursor.


- when we doing DML or DRL/DQL in oracle, the system will create a cursor area,
and after process done , aumatically system will close that cursor area.

Explicit cursor - User defined cursor.


- We are creating our own area instead of system to do DML or DRL operations.

Cursor always row by row process.

Cursor Atributes:(These are all normaly return booliean values)

1. %FOUND
2. %NOTFOUND
3. %ISOPEN
4. %ROWCOUNT

[ Boolean expressions:
-------------------
Boolean expressions are that expression that returns boolean datatype as result.

In SQL there are three values for boolean datatype,


those are:

TRUE
FALSE
UNKNOWN ]

Explicit cursor:

Syntax:

declare cursor
open cursor
fetch records
close cursor

Declaring cursor:

cursor c1 is select * from employees; -- cusrsor is always declaring with


select statement

c1 => select * from employees;

Open cursor :-

open < cursor name >;

open c1; --When you open the cursor c1, then the select statement submitted to
the oracle server.
-- then Oracle will execute this query. oracle will return the records
and will store the records
-- temporary memeory area(called context area) - context area created in
PGA(Program global area)
c1 => -- cursor c1 pointing to the context area.

--now retrived records are available in PGA, we have to fetch the


records from PGA using
--"FETCH" statement.

fetching records from cursor.:-

"Fetch" statement is used to fetch records from cursor.

Using FETCH:

FETCH <cursor_name> INTO <variable>;

fetch c1 into x,y,z,--; fetching records row by row and assiging to


the [Link] statement
fetch a one records at a time.

so keep the fecth statment inside the loop(like for,while)

closing cursor:-

close <cursor_name>;

close c1;

Example :

set serveroutput on;

declare
cursor c1 is select first_name,salary from employees;
vname employees.first_name%type;
vsal [Link]%type;
begin
open c1;
loop
fetch c1 into vname,vsal;
exit when c1%notfound;

dbms_output.put_line(vname||' '||vsal);

end loop;
close c1;
end;
/

You might also like