Cursors are said to be a pointer, in the context area. PL/SQL controls the context area through the cursor. Cursors holds the rows may be one or more that are returned by SQL statement. These set of rows; the cursor holds are referred to as the active set. There are two types of cursors:
1.Implicit Cursors
These cursors are automatically created by Oracle, whenever there is an no explicit cursor for the statement. Implicit cursors are not controlled by the programmers. When there is a DML statement i.e., INSERT, UPDATE, DELETE is issued, an implicit cursor is associated with the statement. There are different types of attributes available to the implicit cursors, they are:
- %FOUND – This attribute returns true if insert, delete and update statements are affected by one or more rows or if the select into statement returns one or more rows, else it returns false.
- %NOTFOUND – this attribute is opposite of %FOUND attribute, returns true if insert, delete and update statements are not affected by one or more rows or if the select into statement returns no rows, else it returns false.
- %ISOPEN – this attribute always returns false for the implicit cursors, since Oracle closes SQL Cursor automatically after completing the execution of its associated SQL statement.
- %ROWCOUNT – this returns number of rows that are affected by INSERT, DELETE and UPDATES statements or returned by the SELECT INTO statement.
We can access any of the SQL cursor attribute by sql%attribute_name.
Example:
Refer to the table created in the functions section.
SELECT * from teachers;
DECLARE
total_rows number(2);
BEGIN
UPDATE teachers
SET salary = salary + 100;
IF sql%notfound THEN
dbms_output.put_line('no teachers selected');
ELSEIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' teachers selected ');
END IF;
END;
/
Output:
5 teachers selected
PL/SQL procedure successfully completed.
We can check the rows; they will be updated like below.
SELECT * from teachers.
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ranjitha |
34 |
Bangalore |
2100 |
2 |
Sai |
30 |
Hyderabad |
2600 |
3 |
Shakthi |
34 |
Pune |
3100 |
4 |
Gayathri |
36 |
Bangalore |
4100 |
5 |
Vignesh |
34 |
Mumbai |
4600 |
2.Explicit Cursors – These cursors are programmer-defined cursors for gaining control over the context area. These cursors are defined in the declaration section of the block. And this is created on a select statement that returns more than one row.
Syntax:
CURSOR cursor_name IS select_statement;
When we are working with the Explicit cursors, following steps are included:
- Cursor should be declared for initializing the memory.
- Cursor should be opened for allocating the memory.
- Fetch the cursor to retrieve the data.
- And then close the cursor for releasing the allocated memory.
Declaring the cursor: This defines the cursors with a name and also with associated select statement.
CURSOR t_teachers IS
SELECT id, name, address FROM teachers;
Opening the cursor: This allocates the memory for the cursor and also makes it ready for fetching the rows.
OPEN t_teachers;
Closing the cursors: It means releasing the allocated memory.
CLOSE t_teachers;
Fetching the cursors: This means accessing one row at a time.
FETCH t_teachers INTO t_id, t_name;
Example:
DECLARE
t_id teachers.id%type;
t_name teachers.name%type;
t_addr teachers.address%type;
CURSOR t_teachers is
SELECT id, name FROM teachers;
BEGIN
OPEN t_teachers;
LOOP
FETCH t_teachers into t_id, t_name;
EXIT WHEN t_teachers%notfound;
dbms_output.put_line(t_id || ' ' || t_name || ' );
END LOOP;
CLOSE t_teachers;
END;
/
Output:
1 ranjitha
2 sai
3 shakthi
4 gayathri
5 vignesh
PL/SQL procedure successfully completed.