Tuesday 23 June 2015

PLSQL ||Cursor and Type of Cursor

Cursor: A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
                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 of cursor.

There are two type cursor in PLSQL:
1. Implicit Cursor
2. Explicit Cursor

We will see type of cursor in detail.

1. Implicit Cursor: These type of cursor created default whenever DML statement like INSERT, UPDATE, DELETE is executed.
Oracle provides some cursor attributes for implicit type cursor. Cursor attribute available for implicit type of cursor is %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.

e.g.:

DECLARE
l_variable number;
--
BEGIN
DELETE dept
WHERE deptno=20;
--
l_variable:= SQL%ROWCOUNT;
--
dbms_output.put_line('Total number of record deleted '|| l_variable);
--
end;

Explicit Cursor: As per name of cursor we need to create this cursor explicitly. It is created on select statement which return more than one rows.  Below given are syntax for explicit cursor.
DECLARE cursor in declaration section before BEGIN section.
OPEN the cursor in BEGIN section.
FETCH the data in declared variable in declare section of plsql block. Data should be fetch in Execution section.
CLOSE the cursor in execution section.

e.g.:

DECLARE
inv_rec ap_invoices_all%rowtype;
--
CURSOR inv_cur is
SELECT *
FROM ap_invoices_all
WHERE supplier_name='SMITH.K';
--
 BEGIN
OPEN inv_cur;
FETCH inv_cur into inv_rec;
dbms_output.put_line('Invoice Number'||inv_rec.invoice_num||' '|| 'Invoice Amount'||Amount);
CLOSE inv_cur;
END;

No comments:

Post a Comment

How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>

 How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>  Sometime these xml data tags automatically chang...