When you work with Oracle database, you work with a complete set of rows returned from an SQL SELECT statement.
However the application in some cases cannot work effectively with the entire result set, therefore, the database server needs to provide a mechanism for the application to work with one row or a subset of the result set at a time.
When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.
Points to remember while fetching a row: · We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
statements might credit one bank account and debit another.
It is important not to allow one operation to succeed while the other fails.
The following picture describes steps that you need to follow when you work with a PL/SQL cursor: .
For information on the use of PL/SQL records with SQL to update and insert data, see "Inserting PL/SQL Records into the Database" and "Updating the Database with PL/SQL Record Values".
· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
· If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
General Form of using an explicit cursor is: In the above example, first we are creating a record ‘emp_rec’ of the same structure as of table ‘emp_tbl’ in line no 2.
We can also create a record with a cursor by replacing the table name with the cursor name.
An explicit cursor is defined in the declaration section of the PL/SQL Block. General Syntax to open a cursor is: When a cursor is opened, the first row becomes the current row.