When we want to retrieve certain records, we use SELECT statements. They retrieve all the matching records for the filter conditions in WHERE clause. But when this SELECT statement used in the PL/SQL block is restricted to return only one row. This is not always welcome in a program. There will be situations where we need to select multiple rows by using SELECT query. In order do this; we use cursors where we write a SELECT query to retrieve multiple rows. These cursors are the named SELECT query and the result of which will be traversed one by one in the program using loops.
These are the cursors which are automatically created and used by the DBMS. These cursors are created when we use DML statements like INSERT, DELETE or UPDATE or when we use SELECT query. Even for single row SELECT statement DBMS creates implicit cursors. It allocates some space in the memory to hold the data. Even when we fire DML statements, the implicit cursor is fired to select the matching records and the space is reserved for those records in the memory.
%ISOPEN - It returns TRUE or FALSE depending on the cursor is still open for execution or not. It returns always FALSE for implicit cursor because DBMS would have executed and closed the cursor automatically.
%FOUND - It returns TRUE if the DML statement affects any of the rows or the SELECT statement retrieves one or more row. Otherwise it returns FALSE. If the statements are not executed then it returns NULL.
%NOTFOUND - It is opposite of %FOUND. It returns TRUE if the DML statement does not affect any of the rows or the SELECT statement does not retrieve any row. Otherwise it returns FALSE.
%ROWCOUNT - It returns the number of rows affected by the DML statements or the number of rows retrieved by the SELECT statement.
These cursors are created by the developer in his program. In this type of cursors, developer will have the control on it. Explicit cursors have to be declared in the DECLARE section and can be used as any other variable, but it will hold one or more rows of data.
OPEN the Cursor - Cursor is opened for reading the records from it. When we open the cursor it gets executed and memory space is allocated for the records.
FETCH the records - This step will start traversing the records in the cursor. Rows will be fetched one at a time from this cursor. We can perform any calculation or manipulation on these fetched records.
CLOSE the Cursor - Once the cursor is used and no more required in the program, close the cursor. This will release the allocated memory for this cursor.
In the above four steps, FETCH step will retrieve only one record at a time and it will assign individual column values into respective variables. Also a separate loop has to be created to iterate through all the records. If we use For loop for cursors, then we can iterate and traverse the whole records (all the columns) of cursor query using the for loop variable. This is useful when all the records of a table need to be used in the program. The cursor will automatically be opened when it is called in the FOR loop and closed when for loop ends. No need to explicitly OPEN or CLOSE the cursor. Below example shows how cursor for loop is different from above four steps.
We can even pass parameters to the cursors like we do in procedures and functions. These parameters are used in the cursor query to retrieve the data. The scope of the cursor parameter is localized to cursor alone.
These are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.
We can see the difference between cursor and refcursor in below program.