Cursors with its Types
- Cursors are database objects used to traverse the results of a select SQL query.
- It is a temporary work area created in the system memory when a select SQL statement is executed.
- Similalry, This temporary work area is used to store the data retrieved from the database, and manipulate this data.
- Also, It points to a certain location within a recordset and allows the operator to move forward (and sometimes backward, depending upon the cursor type).
- We can process only one record at a time.
- Moreover, The set of rows the cursor holds which called the active set (active dataset).
- Cursors often criticized for their high overhead.
Types of Cursors
There are two types of cursors in PL/SQL
- These are created by default by ORACLE itself when DML statements like, insert, update, and delete statements are executed.
- They also created when a SELECT statement that returns just one row executed.
- We cannot use implicit cursors for user-defined work.
- Explicit cursors user-defined cursors written by the developer.
- They can created when a SELECT statement that returns more than one row is executed.
- Even though the cursor stores multiple records, only one record can be processed at a time, which is called as the current row.
- When you fetch a row, the current row position moves to next row.
Attributes of Cursor
Steps to manage explicit cursor
Declare a cursor
- A cursor defined in the declaration section of PL/SQL block.
CURSOR cursorname IS SELECT ………
Open a cursor
- Once a cursor declared we can open it.
- When the cursor opened following operations performed.
- Memory allocated to store the data.
- Execute SELECT statement associated with cursor
- Create active data set by retrieving data from table
- Set the cursor row pointer to point to the first record in an active data set.
- Syntax:- OPEN cursorname;
- We cannot process selected row directly. We have to fetch column values of a row into memory variables. Also, This done by FETCH statement.
- Syntax:- FETCH cursorname INTO variable1, variable2…..
- This step involves actual processing of current row.
- Moreover, A cursor should close after the processing of data completes. Once you close the cursor it will release memory allocated for that cursor.
Example of Cursor
DECLARE CURSOR emp_cur IS SELECT emp_rec FROM emp_tbl WHERE salary > 10000; BEGIN
FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.first_name || ‘ ‘ || emp_rec.last_name);
- In the above Cursors with its Types example, first, we are declaring a cursor whose name is emp_cur with the select query.
- So, In the select query, we have used where condition such that salary<10000. So active data set contain only such records whose salary less than 10000.
- Then, we are opening the cursor in the execution section.
- Moreover, Then we are fetching the records from cursor to the variable named emp_rec.
- Also, Then we are displaying the first_name and last_name of records fetched in a variable.
- At last, as soon as our work completed we are closing the cursor.