Cursors in DBMS – Definition, Types, Attributes, Uses

Database Management System(DBMS) supports Data Manipulation Language statements like Insert, Update and Delete queries. Each database syntactically differs but the functionality remains the same in the mentioned operations. In this article, let us see about performing insert, update and delete queries using the cursor.

Cursors

Whenever DML statements are executed, a temporary work area is created in the system memory and it is called a cursor. A cursor can have more than one row, but processing wise only 1 row is taken into account. Cursors are very helpful in all kinds of databases like Oracle, SQL Server, MySQL, etc. They can be used well with DML statements like Update, Insert and Delete. Especially Implicit cursors are there with these operations. From time to time it changes the values and hence the implicit cursor attribute values need to be assigned in a local variable for further use. In PL/SQL, two different types of cursors are available.

• Implicit cursors 
• Explicit cursors

Explicit cursors

Explicit cursors are defined by the programmers to have more control area on the context area. It has to be defined in the declaration section of the PL/SQL Block. Usually, It is defined on a SELECT Statement and it returns more than one row as output. We can iterate over the rows of data and perform the required operations.

Steps involved in creating explicit cursors:

• Cursor Declaration for initializing the memory

CURSOR <cursorName> IS 
   SELECT <Required fields> FROM <tableName>;

• Cursor Opening to allocate the memory

OPEN <cursorName>; 

• Cursor Fetching to retrieve the data

FETCH <cursorName> INTO <Respective columns>

• Cursor Closing to release the allocated memory

CLOSE <cursorName>;

Example:

DECLARE 
   empId employees.EMPLOYEEID%type; 
   empName employees.EMPLOYEENAME%type; 
   empCity employees.EMPLOYEECITY%type; 
   CURSOR c_employees is 
      SELECT EMPLOYEEID, EMPLOYEENAME, EMPLOYEECITY FROM employees; 
BEGIN 
   OPEN c_employees; 
   LOOP 
   FETCH c_employees into empId , empName , empCity; 
      EXIT WHEN c_employees %notfound; 
      dbms_output.put_line(empId || ' ' || empName || ' ' || empCity); 
   END LOOP; 
   CLOSE c_employees ; 
END; 
/

Output:

Implicit cursors

For DML statements, implicit cursors are available in PL/SQL i.e. no need to declare the cursor, and even for the queries that return 1 row, implicit cursors are available. Through the cursor attributes, we can track the information about the execution of an implicit cursor.

Attributes of Implicit Cursors:

Implicit cursor attributes provide the results about the execution of INSERT, UPDATE, and  DELETE. We have different Cursor attributes like “%FOUND”, “%ISOPEN”, “%NOTFOUND”, and %ROWCOUNT. The most recently executed SQL statement result will be available in Cursor. Initially cursor value will be null.

Let us see the different cursor attributes one by one with regards to the DML statements. So let us create a sample table named “employees” in oracle:

CREATE TABLE employees
( EMPLOYEEID number(10) NOT NULL,  
  EMPLOYEENAME varchar2(50) NOT NULL,  
  EMPLOYEECITY varchar2(50)  
); 

Insert the records in “employees” table

INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (1,'XXX','CHENNAI');
INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (2,'XYZ','MUMBAI');
INSERT INTO employees (employeeId,employeeName,employeeCity) VALUES (3,'YYY','CALCUTTA');

Existence of records in “employees” table

SELECT * FROM employees;

%FOUND attribute

CREATE TABLE tempory_employee  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee WHERE employeeId = employeeNo ;
  IF SQL%FOUND THEN  -- delete succeeded
    INSERT INTO tempory_employee  (employeeId,employeeName,employeeCity)  VALUES (2, 'ZZZ', 'Delhi');
  END IF;
END;
/

Now check for the details present in the tempory_employee  table

SELECT * FROM tempory_employee;

Output:

%FOUND attribute and performing update operation example

CREATE TABLE tempory_employee1  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee WHERE employeeId = employeeNo ;
  IF SQL%FOUND THEN  -- delete succeeded
     UPDATE employees SET employeeCity = 'Chandigarh' WHERE employeeId = 1;
  END IF;
END;
/

Output from SELECT * FROM employees:

%FOUND attribute upon update operation and then performing delete operation example

CREATE TABLE tempory_employee2  AS SELECT * FROM employees;
DECLARE
  employeeNo NUMBER(4) := 2;
BEGIN
  UPDATE tempory_employee2 SET employeeCity = 'Gurgaon' WHERE employeeId = employeeNo;
  IF SQL%FOUND THEN  -- update succeeded
     DELETE FROM tempory_employee2 WHERE employeeId = 1 ;  -- Then delete a specific row
  END IF;
END;
/

Output:

After doing above operations

%ISOPEN Attribute:  For Implicit Cursors, always the result is False. The reason is Oracle closes immediately after executing the DML result. Hence the result is FALSE.

%NOTFOUND Attribute: It is just the opposite of %FOUND. %NOTFOUND is the logical opposite of %FOUND. %NOTFOUND results in TRUE value for an INSERT, UPDATE, or DELETE statement which affected no rows. By default, it returns False.

%ROWCOUNT Attribute: A number of rows affected by an INSERT, UPDATE or DELETE statement are given by %ROWCOUNT. When there are no rows are affected, %ROWCOUNT gives 0 as the result, otherwise, it returns the number of rows that have been deleted.

CREATE TABLE tempory_employee3  AS SELECT * FROM employees;
DECLARE  employeeNo NUMBER(4) := 2;
BEGIN
  DELETE FROM tempory_employee3 WHERE employeeId = employeeNo ;
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;

Output:

The values of the cursor attribute have to be saved in a local variable and those variables can be used in future uses. The reason is while doing multiple database operations in different blocks, cursor attribute values keep on changing and hence this is much required.

 The %NOTFOUND attribute is better used only with DML statements but not with SELECT INTO statement.