Updated March 17, 2023
Introduction to Cursors in PL/SQL
PL/SQL is one of the most widely used programming languages when it comes to database programming. However, some essential concepts of PL/SQL need to learn and understand clearly by a PL/SQL programmer to use them properly. The cursor is one of them. In the PL/SQL program, SQL statements need to be executed at the end. The cursor is just like a pointer which is used to point to the context area created by the Oracle to execute the SQL statement. A Cursor holds all the rows returned after the processing of SQL statements. The set of the row which is held by the Cursor is referred to as Active Set. The cursor can also be named to use it further in the program by a programmer. In this topic, we are going to learn about Cursors in PL/SQL.
Types of Cursors
There are 2 types of Cursors used in PL/SQL programming:
1. Implicit Cursors
As the name indicates, Implicit cursors are those cursors that are automatically created by Oracle when any DML statements like INSERT, DELETE, UPDATE are executed. When the programmer does not create any cursor, Oracle by itself creates it to hold the rows affected by the DML statements. These cursors cannot be named by the programmer and hence cannot be referred and used at some other place in code. Though Oracle provides some attributes to perform some operations on it like
%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
| Attribute | Description |
| %FOUND | Returns True if DML statements like INSERT,
DELETE, UPDATE affect one or more rows or SELECT statement returns one or more rows. Otherwise, it returns False |
| %NOTFOUND | It is the opposite of %FOUND attribute, It returns True if none of the rows are affected by DML statement or SELECT statement returns no results. Otherwise, it returns False. |
| %ISOPEN | In the case of Implicit cursors, it always returns False because Oracle closes the cursor just after the execution of SQL statements. |
| %ROWCOUNT | It returns the row count. I.e. the count of the number of rows that are affected by the mentioned DML statements by the programmer in the PL/SQL code like INSERT, DELETE and UPDATE or the number of rows that are returned by SELECT INTO statement. |
Example
Scenario: To update the marks of all students in a table ‘student’ in English Subject having column ‘subject’ by 10.
DECLARE
affected_rows number(4);
BEGIN
UPDATE students SET marks = marks+10 where subject = 'English';
IF sql%NOTFOUND THEN dbms_output.put_line ('No records of English subject are updated');
ELSIF sql%FOUND THEN affected rows: = affected rows%rowcount
dbms_output.put_line('Congrats ..Records Updated' || affected_rows);
END IF;
END;
/2. Explicit Cursors
Explicit Cursors are those cursors that are defined by programmers to have more control over the Context Area (where the results of SQL queries are stored). These cursors need to be first defined in the declaration block of the PL/SQL program. It is created for the SQL statements that return more than one row after processing them. There is a specific procedure that needs to be followed for using an Explicit Cursor. Steps to use Explicit Cursor are mentioned below:
1. Declare the cursor: It is used to give a name to the context area/cursor along with the select statement that needs to be executed.
Syntax
CURSOR cursor_name IS SELECT statement;2. Open the cursor: Opening a cursor, allocates the memory to it and make it available to fetch the records returned by the SQL statement.
Syntax
OPEN cursor_name;3. Fetch the cursor: In this process, one row is accessed at a time. The SELECT statement is executed and the fetched rows are stored in the context area. It fetches the records and assigns them to a variable defined.
Syntax
FETCH cursor_name INTO variable;4. Close the cursor: This step is used to close the cursor opened above to deallocate the memory once all the rows stored are fetched successfully.
Syntax
CLOSE cursor_name;Example
Scenario: Retrieve the name, address and overall percentage of students in a table
‘student’ having columns ‘name’, ‘address’ and ‘percentage’
DECLARE
stud_name student.name%type; stud_address student.address%type;
stud_percentage student.percentage%type;
CURSOR stud IS SELECT name, address, percentage FROM student; BEGIN
Open stud;
LOOP
FETCH stud into stud_name, stud_address, stud_percentage;
EXIT when stud%NOTFOUND;
dbms_ouput.put_line(stud_name || ' ' || stud_address || ' ' || stud_percentage);
END LOOP;
CLOSE stud;
END
/Cursor Actions
Unlike SQL which works on all the rows in a result set at a time, the cursor is mainly used in the scenarios when the programmer wants to process and retrieve the data of one row at a time.
Below mentioned are some of the Cursor Actions:
- Declare a Cursor: It is important to declare a cursor before using it. A cursor is declared by defining the SQL statement that needs to be processed.
- Open a Cursor: After declaration, a cursor is opened and populated by the data return by the processing of SQL statement.
- Fetch a Cursor: After the cursor is opened, the output rows need to be fetched one by one to perform any manipulation if required.
- Close a Cursor: After all the data manipulation. A cursor created needs to be closed
- Deallocate: This step includes deleting the cursor and frees all the resources held by it.
Importance of Cursor in PL/SQL
Pointing to the memory location and performing actions accordingly is one of the important tasks in any programming language. In PL/SQL, it is done by Cursors. Cursors play a crucial role when it comes to performing different task by giving a name to the memory area (context area) where the result of SQL queries are saved. We can access the records one by one and perform any manipulations in it if required or display it on the console accordingly. Explicit Cursors are more efficient, give more programmatic control, and less vulnerable to data errors so they are very useful in PL/SQL programming than Implicit ones.
Conclusion
Database programming is very popular these days and PL/SQL is one of the languages that should be very well used in it. Cursors give more control to the programmer to access the data retrieved. For a programmer to work on PL/SQL, it is important to know the use and importance of Cursor to work effectively.
Recommended Articles
We hope that this EDUCBA information on “Cursors in PL/SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
