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.