DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Cursors
Last Updated : 21 May, 2026
A cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on sets of rows,
Unit 2196 words8 headingsTables includedExamples included
What is a Cursor?
A cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on sets of rows, a cursor lets you process each row individually.
Set-based SQL
Operates on all matching rows at once. Usually faster for large data.
Cursor
Processes one row at a time. Useful for row-specific logic, slower at scale.
When to Use Cursors
- When you need to perform different operations on each row based on its values
- For complex conditional row-by-row logic not expressible in a single SQL statement
- For iterative calculations that depend on the previous row's result
- When generating reports that require per-row custom formatting or logic
Note: Cursors are slower than set-based SQL. Always try a set-based approach first; use cursors only when necessary.
Cursor Lifecycle
DECLARE→
OPEN→
FETCH in loop→
CLOSE
Cursor Syntax (MySQL)
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE CursorExample()
BEGIN
-- Step 1: Declare variables to hold fetched values
DECLARE v_emp_id INT;
DECLARE v_name VARCHAR(100);
DECLARE v_salary DECIMAL(10,2);
DECLARE done INT DEFAULT FALSE;
-- Step 2: Declare the cursor
DECLARE emp_cur CURSOR FOR
SELECT emp_id, name, salary FROM Employee WHERE dept_id = 1;
-- Step 3: Declare handler for end-of-result-set
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Step 4: Open the cursor
OPEN emp_cur;
-- Step 5: FETCH loop
fetch_loop: LOOP
FETCH emp_cur INTO v_emp_id, v_name, v_salary;
IF done THEN
LEAVE fetch_loop;
END IF;
-- Process each row here
SELECT CONCAT('Employee: ', v_name, ' | Salary: ', v_salary) AS Info;
END LOOP;
-- Step 6: Close the cursor
CLOSE emp_cur;
END //
DELIMITER ;
CALL CursorExample();Practical Example — Salary Increment with Cursor
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE IncrementSalaryByCursor()
BEGIN
DECLARE v_emp_id INT;
DECLARE v_salary DECIMAL(10,2);
DECLARE v_dept_id INT;
DECLARE v_increment DECIMAL(10,2);
DECLARE done INT DEFAULT FALSE;
DECLARE emp_cur CURSOR FOR
SELECT emp_id, salary, dept_id FROM Employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN emp_cur;
main_loop: LOOP
FETCH emp_cur INTO v_emp_id, v_salary, v_dept_id;
IF done THEN LEAVE main_loop; END IF;
-- Different increment by department
IF v_dept_id = 1 THEN
SET v_increment = v_salary * 0.15; -- CS gets 15%
ELSEIF v_dept_id = 2 THEN
SET v_increment = v_salary * 0.10; -- Math gets 10%
ELSE
SET v_increment = v_salary * 0.05; -- Others get 5%
END IF;
UPDATE Employee
SET salary = salary + v_increment
WHERE emp_id = v_emp_id;
END LOOP;
CLOSE emp_cur;
SELECT 'Salary increment applied successfully.' AS Result;
END //
DELIMITER ;Example — Generate a Report Using Cursor
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE GenerateSalaryReport()
BEGIN
DECLARE v_name VARCHAR(100);
DECLARE v_dept VARCHAR(100);
DECLARE v_sal DECIMAL(10,2);
DECLARE v_grade VARCHAR(20);
DECLARE done INT DEFAULT FALSE;
DECLARE report_cur CURSOR FOR
SELECT e.name, d.dept_name, e.salary
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id
ORDER BY e.salary DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Create temp table to store report
CREATE TEMPORARY TABLE IF NOT EXISTS SalaryReport (
emp_name VARCHAR(100),
dept_name VARCHAR(100),
salary DECIMAL(10,2),
grade VARCHAR(20)
);
OPEN report_cur;
rloop: LOOP
FETCH report_cur INTO v_name, v_dept, v_sal;
IF done THEN LEAVE rloop; END IF;
IF v_sal >= 90000 THEN SET v_grade = 'Grade A';
ELSEIF v_sal >= 70000 THEN SET v_grade = 'Grade B';
ELSEIF v_sal >= 50000 THEN SET v_grade = 'Grade C';
ELSE SET v_grade = 'Grade D';
END IF;
INSERT INTO SalaryReport VALUES (v_name, v_dept, v_sal, v_grade);
END LOOP;
CLOSE report_cur;
SELECT * FROM SalaryReport;
DROP TEMPORARY TABLE SalaryReport;
END //
DELIMITER ;Cursor Properties
| Property | Description |
|---|---|
| Forward-only | MySQL cursors can only FETCH forward, not backward |
| Read-only | MySQL cursors are read-only (can't UPDATE via cursor directly) |
| Sensitive | Cursor reflects changes made to base table during iteration |
Cursor Performance Considerations
Set-based SQL
Operates on all matching rows at once. Usually faster for large data.
Cursor
Processes one row at a time. Useful for row-specific logic, slower at scale.
Best Practice: Use cursors only when set-based SQL cannot accomplish the task. For bulk updates, always prefer set-based statements.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Cursors.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, cursors
Related DBMS Topics
Continue learning this concept
Unit 2Unit 2 — Relational Model & SQLThis unit covers the formal foundations of relational databases and the Structured Query Language SQL. Starting from the mathematical basis of relational algebra and calcUnit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — indepenUnit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.Unit 2DDL CommandsData Definition Language DDL commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands affect the structuUnit 2DML CommandsData Manipulation Language DML commands are used to manipulate the data stored in database tables. DML operates on the instance rows/data, not the schema.Unit 2Domain Relational Calculus DRCDomain Relational Calculus DRC is a non-procedural query language where variables range over individual attribute values domains rather than entire tuples.