Introduction to Cursors in PL/SQL
PL/SQL is one of the most widely used programming languages when it comes to database programming. Some important 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 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 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.
|%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.|
Scenario: To update the marks of all students in a table ‘student’ in English Subject having column ‘subject’ by 10.
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);
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.
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.
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.
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.
Scenario: Retrieve the name, address and overall percentage of students in a table
‘student’ having columns ‘name’, ‘address’ and ‘percentage’
stud_name student.name%type; stud_address student.address%type;
CURSOR stud IS SELECT name, address, percentage FROM student; BEGIN
FETCH stud into stud_name, stud_address, stud_percentage;
EXIT when stud%NOTFOUND;
dbms_ouput.put_line(stud_name || ‘ ‘ || stud_address || ‘ ‘ || stud_percentage);
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 the 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.
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.
This is a guide to Cursors in PL/SQL. Here we discuss the types of Cursors used in PL/SQL programming along with cursor actions and the importance of cursor. You may also have a look at the following articles to learn more –