- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Cursors in DBMS
A temporary work area known as a cursor is established in the system memory whenever DML statements are performed. Although a cursor may contain many rows, processing−wise, only one row is taken into consideration. Cursors are very helpful to the kind of DBMSs like Oracle, Mysql, SQL servers, etc. A cursor is a control structure to go through database records and is used in databases.
Cursors are of two types
Implicit Cursors
Explicit Cursors
Implicit Cursors
If you don't use an explicit cursor for the statement, Oracle will create implicit cursors while the SQL statement is performed. Some attributes used for Implicit cursors to check the status of DML operation are %ROWCOUNT, %ISOPEN, %FOUND and %NOTFOUND.
%ROWCOUNT
It displays the number of rows produced by a SELECT INTO command or affected by DML operations like INSERT, DELETE, and UPDATE.
%ISOPEN
Given that the SQL cursor is automatically closed when the related SQL statements have been executed, it always returns FALSE for implicit cursors.
%FOUND
Its return result is TRUE if a SELECT INTO command produced one or more rows, or if DML actions like INSERT, DELETE, and UPDATE influence at least one row or more rows. Otherwise, FALSE is returned.
%NOTFOUND
Its return result is TRUE if a SELECT INTO command produced one or more rows, or if DML actions like INSERT, DELETE, and UPDATE influence at least one row or more rows. Otherwise, FALSE is returned.
%NOTFOUND
Its return result is TRUE if no rows are affected by DML operations like INSERT, DELETE, and UPDATE, or if a SELECT INTO command returns no rows. Otherwise, FALSE is returned. It is directly opposed to %FOUND.
Examples
Make a table for Students and add records.
Algorithm
Step 1: Declaring the total number of rows to be affected.
Step 2: Using BEGIN
Step 3: Updating student's marks by 5.
Step 4: Use the if condition for %notfound
Step 5: Print the output for records not updated.
Step 6: Using the else if condition for %found
Step 7: Declaring the number of %rowcount is equal to the total number of rows.
Step 8: Displaying the total number of rows updated inside the total rows.
Step 9: Closing END
Step10: showing the updated table
Input
Students
Student ID First Name Last Name Address Marks 1 Rahul Raj Delhi 92 2 Saksham Pandey Lucknow 81
3 Amresh Rao Panipat 91 4 Sumit Patil Pune 61 5 Sneha Singh Kochi 75
Updating the table and increasing the marks of each student by 5 marks using the SQL%ROWCOUNT attribute.
Example
DECLARE
total_rows number(2); #Declaring the total number of rows to be affected
BEGIN
update students marks= marks + 5; #Updating students marks by 5
if sql%notfound then #Using if condition for %notfound
dbms_output.put_line('Records not updated’); #Printing the result of %notfound
elseif sql%found then #Using elseif for %found
total_rows:= sql%rowcount; #Total rows will be equal to the total row count
dbms_output.put_line(‘total rows updated: ’ || total_rows ); #Printing the result of total rows updated in total rows
end if;
END; #Closing the END
SELECT * FROM Students;#to show the updated table
Output
Student ID First Name Last Name Addres Marks 1 Rahul Raj Delhi 97 2 Saksham Pandey Lucknow 86 3 Amresh Rao Panipat 96 4 Sumit Patil Pune 66 5 Sneha Singh Kochi 80
Explicit Cursors
Programmers define explicit cursors to have a larger control area on the context area. It must be defined in the PL/SQL Block's declaration section.
Syntax
CURSOR cursor_name IS select_statement;
Steps for creating explicit cursors:−
Cursors for initializing the memory
CURSOR <cursorName> IS SELECT <Required fields> FROM <tableName>;
Cursors for allocating the memory
OPEN <cursorName>;
Cursors for fetching the retrieval data
FETCH <cursorName> INTO <Respective columns>;
Cursors for releasing the allocated memory
CLOSE <cursorName>;
Example
In this example, we are going to see how the explicit cursor is used.
Algorithm
Step 1: Declare the details that are affected
Step 2: Initialise the memory
Step 3: Use Begin
Step 4: Allocate the memory
Step 5: Create a loop
Step 6: Fetch the data
Step 7: Condition if data not found and end the loop
Step 8: Release the allocated memory
Input
Student
Student ID Name Address Marks 1 Rahul Delhi 97 2 Saksham Lucknow 86 3 Amresh Panipat 96 4 Sumit Pune 66 5 Sneha Kochi 80
Example
DECLARE
s_id student.id%type; #declaring the id
s_name student.name%type; #declaring the name
s_addr student.address%type; #declaring the address
CURSOR s_student is SELECT id, name, address FROM student; #initialising the memory
BEGIN
OPEN s_student; #memory allocating
LOOP #creating a loop
FETCH s_student into s_id, s_name, s_addr; #fetching the data
EXIT WHEN s_student%notfound; #condition if data not found
END LOOP; #closing the loop
Output
Student ID Name Address 1 Rahul Delhi 2 Saksham Lucknow 3 Amresh Panipat 4 Sumit Pune 5 Sneha Kochi
Conclusion
This article consists of cursors used in DBMS. There are two types of cursors. First is the implicit cursor which is created when we don't use an explicit cursor. Attributes such as %rowcount,%isopen,%found, and %notfound are used under the implicit cursor. Second, the explicit cursor controls the context area on a large scale. The steps for creating an explicit cursor are initializing, allocating the memory, fetching the data, and releasing the allocated memory.
719 Views
