Cursor in DBMS

Last Updated on April 14, 2023 by Prepbytes

A cursor in DBMS allows programmers to work with individual records rather than entire sets of data. A Cursor in DBMS is particularly useful when dealing with complex queries or when working with data that needs to be updated in real-time.

What is Cursor in DBMS?

A cursor in DBMS is a programming construct that provides a way to traverse and manipulate records within a database. It is essentially a mechanism used to retrieve and manipulate data one row at a time rather than processing an entire set of data at once. A Cursor in DBMS allows programmers to work with individual records, making them particularly useful when dealing with complex queries or when working with data that needs to be updated in real-time.

Cursor in DBMS is used to fetch the results of a SQL query, which can then be accessed and manipulated in the application code. A cursor is essentially a pointer to a specific row within the query result set, allowing the programmer to fetch each row one by one and perform operations on it. A Cursor in DBMS is particularly useful when dealing with large result sets, as they allow the programmer to access data in a more efficient manner without having to retrieve the entire data set at once.

Types of Cursor in DBMS

There are two types of cursor in DBMS: implicit cursor and explicit cursor.

• Explicit Cursor
  Explicit cursors are created by the programmer and provide more control over the result set compared to implicit cursors. Explicit cursors are commonly used in situations where the result set contains multiple rows and the programmer needs to manipulate the data in a more flexible manner.

  Here are the steps involved in creating an explicit cursor in DBMS:

  • Step 1 – Declare the cursor: To declare a cursor, the programmer must use the DECLARE statement followed by the name of the cursor and the SELECT statement that will be used to fetch the data. For example:

    DECLARE cursor_name CURSOR FOR
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions;

    In this example, the cursor_name is the name given to the cursor, and the SELECT statement is used to fetch the data from the table_name based on the specified conditions.

  • Step 2 – Open the cursor: Once the cursor is declared, the programmer needs to open the cursor using the OPEN statement. The OPEN statement initializes the cursor and makes it ready to fetch data. For example:

    OPEN cursor_name;

  • Step 3 – Fetch the data: The FETCH statement is used to retrieve data from the result set. It fetches the next row from the result set and stores it in a set of variables. For example:

    FETCH cursor_name INTO variable1, variable2, ...;

    In this example, variable1, variable2, etc. are the variables used to store the data retrieved by the FETCH statement.

  • Step 4 – Process the data: After fetching the data, the programmer can process it in any way required, such as updating or deleting the data, or performing calculations on it.
    Repeat steps 3-4: The FETCH and process steps need to be repeated until all the rows in the result set have been processed.

  • Step 5 – Close the cursor: Once all the rows have been processed, the programmer needs to close the cursor using the CLOSE statement. This releases the resources used by the cursor. For example:

    CLOSE cursor_name;

  • Step 6 – Deallocate the cursor: After closing the cursor, the programmer needs to deallocate it using the DEALLOCATE statement. This removes the cursor from memory. For example:

    DEALLOCATE cursor_name;

• Implicit Cursor
  Implicit cursors are created automatically by the database system whenever a SQL statement is executed. They are used to execute single-row queries and are not visible to the programmer. The system automatically opens, fetches, and closes these cursors.

  The attributes of an implicit cursor in DBMS include the following:

  • SQL%FOUND: This attribute returns a Boolean value that indicates whether the most recent SQL statement executed by the implicit cursor affected any rows. If the statement affected at least one row, SQL%FOUND returns TRUE, otherwise, it returns FALSE.
  • SQL%NOTFOUND: This attribute is the opposite of SQL%FOUND. It returns a Boolean value that indicates whether the most recent SQL statement executed by the implicit cursor did not affect any rows. If the statement did not affect any rows, SQL%NOTFOUND returns TRUE, otherwise, it returns FALSE.
  • SQL%ROWCOUNT: This attribute returns the number of rows affected by the most recent SQL statement executed by the implicit cursor. This attribute is useful for determining the number of rows affected by an UPDATE, DELETE, or INSERT statement.
  • SQL%ISOPEN: This attribute returns a Boolean value that indicates whether the implicit cursor is currently open. If the cursor is open, SQL%ISOPEN returns TRUE, otherwise it returns FALSE.

  Note: SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT can also be used with an explicit cursor in DBMS.

Example of Cursor in DBMS

Here’s an example of creating a sample table named "employees" in Oracle, along with using cursor attributes with DML statements:

CREATE TABLE employees
( emp_id number(10) NOT NULL,  
  emp_name varchar2(50) NOT NULL,  
  emp_city varchar2(50)  
); 

-- Inserting data into the table
INSERT INTO employees VALUES (1, 'Abhishek', 'Delhi');
INSERT INTO employees VALUES (2, 'Manoj', 'Udaipur');
INSERT INTO employees VALUES (3, 'Himanshu', 'Kanpur');

emp_id	emp_name	emp_city
1	Abhishek	Delhi
2	Manoj	Udaipur
3	Himanshu	Kanpur

Now let’s see the different cursor attributes one by one with regard to the DML statements:

• SQL%FOUND: This attribute returns TRUE if the DML statement (INSERT, UPDATE, DELETE) affected at least one row, and FALSE if no rows were affected. For example:

  CREATE TABLE temp_employee  AS SELECT * FROM employees;
  DECLARE
    emp_no NUMBER(4) := 2;
  BEGIN
    DELETE FROM temp_employee WHERE emp_id = emp_no;
    IF SQL%FOUND THEN  -- delete succeeded
      INSERT INTO temp_employee  (emp_id,emp_name,emp_city)  VALUES (2, 'Raju', 'Nagpur');
    END IF;
  END;

  Output:

  Table Created.
  Statement Processed.

• SQL%ISOPEN: This attribute returns TRUE if the cursor is open, and FALSE if it is closed. This attribute is not used with DML statements.

• SQL%NOTFOUND: This attribute returns TRUE if the DML statement did not affect any rows, and FALSE if at least one row was affected.

• SQL%ROWCOUNT: This attribute returns the number of rows affected by the DML statement. For example:

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

  Output:

  Table Created.
  Statement Processed.
  Number of employees deleted: 1

Note: The cursor attribute values must be saved in a local variable, and those variables can be used in the future. The reason for this is that while performing several database transactions in various blocks, cursor attribute values change, therefore it is required.

Uses of Cursor in DBMS

Here are some common uses of a cursor in DBMS:

• Iterating through records: Cursors are used to loop through records in a table, one at a time, to perform specific operations on each record.
• Data filtering: Cursors can be used to select only specific records from a table based on certain criteria, such as selecting all records where a certain column value matches a specific value.
• Data manipulation: Cursors can be used to update, delete, or insert new records into a table.
• Data processing: Cursors can be used to perform complex calculations or transformations on data in a table.
• Record locking: Cursors can be used to lock records in a table, preventing other users or processes from modifying them.

Conclusion
In conclusion, the cursor in DBMS is an essential component that allows us to retrieve and manipulate data from database tables in a controlled and efficient manner. Implicit cursors are easy to use and require less coding, while explicit cursors provide more flexibility and control. Understanding cursor attributes and how they can be used to retrieve information about the result set and the state of the cursor is crucial for effective PL/SQL programming. With its power and versatility, the cursor in DBMS is a very valuable tool for any developer working with database systems.

FAQs

Here are some frequently asked questions on cursor in DBMS:

Q1: What is a cursor in DBMS?
Ans: A cursor in DBMS is a database object that allows us to retrieve and manipulate data from database tables in a controlled and efficient manner.

Q2: What is the difference between an implicit and explicit cursor in DBMS?
Ans: Implicit cursors are automatically created by the database system for each SQL statement executed, while explicit cursors are defined and used explicitly by the developer in PL/SQL code.

Q3: What is the purpose of using a cursor in DBMS?
Ans: The cursor in DBMS provides a way to fetch and process a set of rows from a table one by one in a controlled manner. This allows developers to write more complex SQL queries and perform more advanced data manipulation operations.

Q4: Can you update data in a table using a cursor in DBMS?
Ans: Yes, you can use a cursor in DBMS to update data in a table using the UPDATE statement, which modifies the values of one or more columns in a specified row or rows.

Q5: Can a cursor in DBMS be used to retrieve data from multiple tables?
Ans: Yes, a cursor in DBMS can be used to retrieve data from multiple tables.

Q6: Can a cursor be used to execute a stored procedure in DBMS?
Ans: Yes, a cursor can be used to execute a stored procedure in DBMS using the EXECUTE statement, which executes the specified stored procedure and returns any output parameters or result sets.