DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 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 (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in 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 (MCQs)DBMS Lab Programs
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...
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.
Cursor Lifecycle
DECLARE→
OPEN→
FETCH in loop→
CLOSE
Cursor Syntax (MySQL)
sql example
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 example
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 example
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.
Continue the lesson path
Study next in this module
01Unit 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...02Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.03Unit 2DDL CommandsData Definition Language (DDL) commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands...04Unit 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.