PL/SQL Cursors

Advertisements

Exception Handling in PLSQL PLSQL Package

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;

Exception Handling in PLSQL PLSQL Package

Google Advertisment

Buy This Ad Space @$20 per Month, Ad Size 600X200 Contact on: hitesh.xc@gmail.com or 9999595223

Magenet is best Adsense Alternative here we earn $2 for single link, Here we get links ads. Magenet

For Projects 9999595223

Google Advertisements


Buy Websites 9999595223

Buy College Projects with Documentation Contact on whatsapp 9999595223. Contact on: hitesh.xc@gmail.com or 9999595223 Try this Keyword C++ Programs

Advertisements