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.
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 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
- Cursor Fetching to retrieve the data
FETCH <cursorName> INTO <Respective columns>
- Cursor Closing to release the allocated memory
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; /
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;
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;
%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; /
%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;
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.