Cursor: – Cursor is used in PL/SQL programming to define record pointer. It is used when we want to manipulate data of many rows. When SELECT_ _ _INTO or any DML statement is issued then oracle opens the cursor. There are two type of cursor used in PL/SQL programming. They are called implicit cursor and explicit cursor.
Implicit cursor: – The implicit cursor attributes are test outcome of DML statement and SELECT_ _ _INTO statement. These attributes are always referred to recent SQL statement. Before any SQL statement the attribute are having null value, the name of cursor in SQL and attributes are referred to as SQL%ISOPEN. There are number of implicit cursor attribute are %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.
%FOUND: – It provides information about recent fetch operation. If recent fetch statement result in successful fetching of row, it returns true otherwise it returns false. If referred before first operation, %FOUND return NULL. If referred before opening cursor or after closing cursor, it raises exception INVALID_CURSOR. Syntax of %FOUND attribute is –
If <Cursor Name %FOUND> then
%NOTFOUND: – %NOTFOUND attribute is complement of %FOUND attribute. %NOTFOUND return false when %FOUND returns true and vice-versa. Like FETCH, %NOTFOUND returns NULL before first FETCH statement. Syntax of %NOTFOUND attribute is –
If <Cursor Name %NOTFOUND> then
%ISOPEN: – This attribute is used to check cursor is open or not. %ISOPEN returns true if cursor is open, otherwise it returns false. It is used for global cursor when programmer is not sure whether cursor is open or not. Syntax of the %ISOPEN attribute is –
If <Cursor Name %ISOPEN> then Statement Else Open <Cursor Name>; End if;
%ROWCOUNT: – The %ROWCOUNT provide number of rows which are currently fetched from cursor. When cursor is opened %ROWCOUNT is equal to zero. %ROWCOUNT is used before opening or after closing a cursor, it raises exception INVALID_CURSOR. Syntax of the %ROWCOUNT attribute is –
To_char(<Cursor Name %ROWCOUNT>);
Explicit cursor: – Explicit cursor is used to declare a cursor when it is necessary. When queries return more than one row than cursor has to be defined explicitly. This type of cursor is called explicit cursor. The explicit cursor can be declared in declaration part of PL/SQL block, such program and package. The explicit cursor is temporary work area in which we can store result of the SQL statement. The explicit cursor is used as sequential file. To manipulate explicit cursor, we have to open the cursor using OPEN statement, we can fetch the rows from table using FETCH statement and after fetching the rows the cursor is closed using CLOSE statement. Syntax of explicit cursor declaration is –
DECLARE CURSOR <Cursor Name> is <Select Statement>;
An example of declaration of explicit cursor is –
DECLARE CURSOR C1 IS Select * from batch;
Here, c1 is an explicit cursor which is declare before fetching the row from existing table. We can also specify return data type in cursor declaration as fallows –
DECLARE CURSOR C1 Return batch %ROWTYPE is Select * from batch
Here, cursor name is c1 which is declared for table batch and it fetch table batch in row wise format.
Open cursor: – After declaration of a cursor and before batching rows from cursor, it has to be opened first. Opening cursor execute the query and select the rows which satisfy query criteria. If cursor is opened for updating using FOR UPDATE statement then rows are locked when cursor is opened. Syntax of OPEN statement is –
OPEN <Cursor Name>;
Closing cursor: – The CLOSE statement is used to close a cursor. This statement is used at the end of PL/SQL block and after fetching rows from open cursor. Syntax of the close statement is –
Close <Cursor Name>;
FETCH statement: – This statement is used to fetch or retrieve a row from existing table. One row can be fetch by the statement using OPEN cursor name. fetching of rows gets current row of cursor and advances cursor pointer to the next row. Syntax of the FETCH statement is –
FETCH <Cursor Name> INTO <Variable Name/Column Name>;