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
Both functions and stored procedures are named, reusable blocks of SQL/PL-SQL code stored inside the database. They encapsulate business logic that can be...
Overview
Both functions and stored procedures are named, reusable blocks of SQL/PL-SQL code stored inside the database. They encapsulate business logic that can be executed repeatedly without rewriting the same statements. Think of them as the database equivalent of functions in a programming language — they accept inputs, perform processing, and return results.
The key difference is their intent: functions compute and return a single value (used inside expressions), while procedures execute complex operations that may modify data, return multiple outputs, and control transactions.
Stored Procedures
Basic Procedure (No Parameters)
sql example
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT emp_id, name, salary, dept_id
FROM Employee
ORDER BY name;
END //
DELIMITER ;
-- Execute it
CALL GetAllEmployees();Procedure with IN Parameter
The IN parameter passes a value into the procedure (read-only inside the procedure):
sql example
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN p_dept_id INT)
BEGIN
SELECT emp_id, name, salary
FROM Employee
WHERE dept_id = p_dept_id
ORDER BY salary DESC;
END //
DELIMITER ;
-- Call with department ID 3
CALL GetEmployeesByDept(3);Procedure with OUT Parameter
The OUT parameter sends a value back to the caller:
sql example
DELIMITER //
CREATE PROCEDURE GetDeptStats(
IN p_dept_id INT,
OUT p_avg_sal DECIMAL(10,2),
OUT p_count INT,
OUT p_max_sal DECIMAL(10,2)
)
BEGIN
SELECT AVG(salary), COUNT(*), MAX(salary)
INTO p_avg_sal, p_count, p_max_sal
FROM Employee
WHERE dept_id = p_dept_id;
END //
DELIMITER ;
-- Call and retrieve output
CALL GetDeptStats(1, @avg, @cnt, @max);
SELECT @avg AS AvgSalary, @cnt AS HeadCount, @max AS MaxSalary;Procedure with INOUT Parameter
The INOUT parameter is both read and written — the value comes in, gets modified, and goes back:
sql example
DELIMITER //
CREATE PROCEDURE ApplyBonus(INOUT p_salary DECIMAL(10,2), IN p_percent DECIMAL(5,2))
BEGIN
SET p_salary = p_salary + (p_salary * p_percent / 100);
END //
DELIMITER ;
SET @sal = 75000;
CALL ApplyBonus(@sal, 10);
SELECT @sal; -- Returns 82500Procedure with Conditional Logic
sql example
DELIMITER //
CREATE PROCEDURE CategorizeSalary(IN p_emp_id INT, OUT p_category VARCHAR(20))
BEGIN
DECLARE v_salary DECIMAL(10,2);
SELECT salary INTO v_salary FROM Employee WHERE emp_id = p_emp_id;
IF v_salary >= 100000 THEN
SET p_category = 'Senior';
ELSEIF v_salary >= 60000 THEN
SET p_category = 'Mid-Level';
ELSE
SET p_category = 'Junior';
END IF;
END //
DELIMITER ;Procedure with Loop and Cursor
sql example
DELIMITER //
CREATE PROCEDURE GiveAnnualRaise(IN p_dept_id INT, IN p_percent DECIMAL(5,2))
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_emp_id INT;
DECLARE emp_cursor CURSOR FOR
SELECT emp_id FROM Employee WHERE dept_id = p_dept_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO v_emp_id;
IF v_done THEN LEAVE read_loop; END IF;
UPDATE Employee
SET salary = salary * (1 + p_percent / 100)
WHERE emp_id = v_emp_id;
END LOOP;
CLOSE emp_cursor;
END //
DELIMITER ;
-- Give all CS dept employees a 5% raise
CALL GiveAnnualRaise(1, 5.0);Functions
Scalar Function (Returns Single Value)
Example
DELIMITER //
CREATE FUNCTION CalculateBonus(p_salary DECIMAL(10,2), p_rating INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE bonus DECIMAL(10,2);
CASE p_rating
WHEN 5 THEN SET bonus = p_salary * 0.20;
WHEN 4 THEN SET bonus = p_salary * 0.15;
WHEN 3 THEN SET bonus = p_salary * 0.10;
ELSE SET bonus = p_salary * 0.05;
END CASE;
RETURN bonus;
END //
DELIMITER ;
-- Use inside a query (unlike procedures!)
SELECT name, salary, CalculateBonus(salary, rating) AS bonus
FROM Employee
WHERE dept_id = 1;
Function for Data Formatting
sql example
DELIMITER //
CREATE FUNCTION FormatName(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(110)
DETERMINISTIC
BEGIN
RETURN CONCAT(UPPER(LEFT(p_last, 1)), LOWER(SUBSTRING(p_last, 2)),
', ',
UPPER(LEFT(p_first, 1)), LOWER(SUBSTRING(p_first, 2)));
END //
DELIMITER ;
-- Use in SELECT
SELECT FormatName(first_name, last_name) AS formatted FROM Employee;
-- Output: "Smith, John"Function for Business Logic
sql example
DELIMITER //
CREATE FUNCTION GetTaxBracket(p_annual_income DECIMAL(12,2))
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
IF p_annual_income <= 250000 THEN RETURN 'No Tax';
ELSEIF p_annual_income <= 500000 THEN RETURN '5% Bracket';
ELSEIF p_annual_income <= 1000000 THEN RETURN '20% Bracket';
ELSE RETURN '30% Bracket';
END IF;
END //
DELIMITER ;When to Use Procedures vs. Functions
| Scenario | Use |
|---|---|
| Calculate a derived value for SELECT | Function |
| Process multiple records, modify data | Procedure |
| Implement a business transaction (transfer money) | Procedure |
| Format/transform a column value | Function |
| Generate reports with multiple result sets | Procedure |
| Validate data before insert | Function (in CHECK or trigger) |
Advantages of Stored Procedures and Functions
- Performance: Precompiled execution plan — no re-parsing on each call
- Security: Grant EXECUTE without exposing table structure; prevents SQL injection
- Maintainability: Change logic in one place; all callers get the update
- Network efficiency: One CALL sends complex logic server-side instead of multiple round-trips
- Encapsulation: Business rules live in the database, not scattered across applications
Dropping and Modifying
sql example
-- Remove
DROP PROCEDURE IF EXISTS GetAllEmployees;
DROP FUNCTION IF EXISTS CalculateBonus;
-- Modify (MySQL requires DROP + CREATE; some DBMS support ALTER)
-- Oracle/PostgreSQL support CREATE OR REPLACE:
CREATE OR REPLACE FUNCTION CalculateBonus(...) ...Understanding functions and procedures is essential for building production-grade database applications where performance, security, and maintainability are paramount.
Continue the lesson path
Study next in this module
01Unit 2Domain Relational Calculus (DRC) - Complete TutorialIn-depth guide to Domain Relational Calculus including formulas, expressions, quantifiers, safe formulas, practical examples, and detailed comparison with Tuple Relational Calculus and SQL.02Unit 2Joins in SQLA join combines rows from two or more tables based on a related column between them. Joins are the primary mechanism for querying data spread across...03Unit 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.04Unit 2Nested Queries (Subqueries)A subquery (also called a nested query or inner query) is a SELECT statement embedded inside another SQL statement. The outer query uses the result of the...