Relational DBMS (OLD) - Interacting with Databases
CBSE Class 12 Informatics Practices
CHAPTER-13
INTERACTING WITH DATABASES
Cursor is a method that provides a way to select multiple rows of data from the database and then process each row individually inside the PL/SQL program.
OR
PL/SQL requires special capability to retrieve and process more than one rows of data from the table and this task is performed by Cursor.
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
Types of Cursors:
¦ Implicit Cursor: Declared for all DML & PL/SQL SELECT statement including queries that return only one row. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
¦ Explicit Cursor: For queries that return more than one row. Declared and named by programmer. Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block.
Explicit Cursor:
Following steps are followed when using the explicit cursor:
1. Declare the cursor.
2. Open the cursor.
3. Fetch the data from the cursor record by record.
4. Close the cursor.
Syntax for using cursor:
Declaring a Cursor in the Declaration Section:
DECLARE
CURSOR emp_cur IS
SELECT *
FROM emp_tbl
WHERE salary > 5000;
In the above example we are creating a cursor ‘emp_cur’ on a query which returns the records of all the employees with salary greater than 5000. Here ‘emp_tbl’ in the table which contains records of all the employees.
Explicit Cursor attributes:
The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement;
Example: CURSOR c_customers IS SELECT id, name, address FROM customers;
There are four cursor attributes:
¦ %ISOPEN: It returns true if cursor is open, otherwise it returns false.
Syntax:
if cursorname%isopen then
.....
else
.....
endif
¦ %FOUND: It returns true if record was fetched properly from the cursor, otherwise it returns false.
Syntax:
cursorname%found
¦ %NOTFOUND: It returns true if record is not successfully found, otherwise it returns false.
Syntax:
cursorname%notfound
¦ %ROWCOUNT: It returns the no. of records processed by the cursor.
Syntax: cursorname%rowcount
Example code to illustrate cursor:
DECLARE
cursor empdis is SELECT * FROM emp;
e emp%ROWTYPE;
BEGIN open empdis;
if empdis%ISOPEN then dbms_output.put_line(‘empno name salary’);
LOOP
FETCH empdis into e;
EXIT WHEN empdis%notfound; dbms_output.put_line(e.empno|| e.name|| e.sal);
ENDLOOP;
CLOSE empdis; else
dbms_output.put_line(‘Cannot open the cursor’);
END IF;
END;
Implicit Cursors:
These are also called as SQL cursors.
PL/SQL employs implicit cursors for following statements:
i. INSERT
ii. UPDTAE
iii. DELETE
iv. SELECT (only those SELECT queries that return exactly one row.)
Subqueries, IN WHERE clause, IN FROM clause, Aliases, Expressions, bind variables can be used with Explicit cursors.
Cursor Based Records:
Cursor FOR Loops:
In a Cursor FOR Loop, a declared cursor is OPENed, FETCHed and CLOSed automatically.
Syntax:
FOR <record_index> IN <cursor_name>
LOOP
<body of loop>
END LOOP;
Cursor FOR Loop with Parameters:
Syntax:
FOR <record_name>IN <cusor_name(<parameter_list_here>) LOOP
.
.
.
END LOOP;
Cursor FOR Loops Using Subqueries:
Syntax:
FOR <record_name> IN (query_expression)
LOOP
.
.
.
END LOOP;
