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
Functions and Stored Procedures
Last Updated : 21 May, 2026
Both functions and stored procedures are named, reusable blocks of SQL/PL-SQL code stored in the database. They encapsulate logic that can be executed repeatedly.
Unit 2130 words14 headingsTables includedExamples included
Overview
Both functions and stored procedures are named, reusable blocks of SQL/PL-SQL code stored in the database. They encapsulate logic that can be executed repeatedly.
Stored Procedure vs. Function
| Feature | Stored Procedure | Function |
|---|---|---|
| Return value | 0 or more (via OUT parameters) | Exactly one value |
| Called with | CALL procedure_name() | SELECT / used in expressions |
| DML inside | Yes (INSERT, UPDATE, DELETE) | Generally No |
| Transaction control | COMMIT/ROLLBACK allowed | Not allowed |
| Error handling | Supported | Limited |
| Purpose | Complex business logic, multi-step | Computation/calculation |
Stored Procedures
Basic Procedure (No Parameters)
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT emp_id, name, salary, dept_id FROM Employee ORDER BY name;
END //
DELIMITER ;
-- Call it
CALL GetAllEmployees();Procedure with IN Parameter
sql exampleWoHoTech
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 GetEmployeesByDept(1);Procedure with OUT Parameter
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE GetDeptAvgSalary(
IN p_dept_id INT,
OUT p_avg_sal DECIMAL(10,2),
OUT p_count INT
)
BEGIN
SELECT AVG(salary), COUNT(*)
INTO p_avg_sal, p_count
FROM Employee
WHERE dept_id = p_dept_id;
END //
DELIMITER ;
-- Call with output variables
CALL GetDeptAvgSalary(1, @avg, @cnt);
SELECT @avg AS AvgSalary, @cnt AS HeadCount;Procedure with INOUT Parameter
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE ApplyBonus(INOUT p_salary DECIMAL(10,2), IN p_pct DECIMAL(5,2))
BEGIN
SET p_salary = p_salary + (p_salary * p_pct / 100);
END //
DELIMITER ;
SET @sal = 75000;
CALL ApplyBonus(@sal, 10);
SELECT @sal; -- 82500Procedure with Conditional Logic
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE GradeEmployee(IN p_emp_id INT)
BEGIN
DECLARE v_salary DECIMAL(10,2);
DECLARE v_grade VARCHAR(10);
SELECT salary INTO v_salary FROM Employee WHERE emp_id = p_emp_id;
IF v_salary >= 90000 THEN
SET v_grade = 'A';
ELSEIF v_salary >= 70000 THEN
SET v_grade = 'B';
ELSEIF v_salary >= 50000 THEN
SET v_grade = 'C';
ELSE
SET v_grade = 'D';
END IF;
SELECT v_grade AS Grade;
END //
DELIMITER ;Procedure with Loop
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE IncrementSalaries(IN p_dept_id INT, IN p_pct DECIMAL(5,2))
BEGIN
DECLARE 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 done = TRUE;
OPEN emp_cursor;
loop1: LOOP
FETCH emp_cursor INTO v_emp_id;
IF done THEN LEAVE loop1; END IF;
UPDATE Employee SET salary = salary * (1 + p_pct/100)
WHERE emp_id = v_emp_id;
END LOOP;
CLOSE emp_cursor;
SELECT CONCAT('Updated salaries in dept ', p_dept_id) AS Result;
END //
DELIMITER ;
CALL IncrementSalaries(1, 10);Functions
Scalar Function (Returns a Single Value)
sql exampleWoHoTech
DELIMITER //
CREATE FUNCTION CalculateBonus(p_salary DECIMAL(10,2), p_years INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE v_bonus DECIMAL(10,2);
SET v_bonus = p_salary * 0.05 * p_years;
RETURN v_bonus;
END //
DELIMITER ;
-- Use inside SELECT
SELECT name, salary, CalculateBonus(salary, 5) AS Bonus
FROM Employee;Function with Conditional Logic
sql exampleWoHoTech
DELIMITER //
CREATE FUNCTION GetSalaryGrade(p_salary DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
IF p_salary >= 90000 THEN RETURN 'Executive';
ELSEIF p_salary >= 70000 THEN RETURN 'Senior';
ELSEIF p_salary >= 50000 THEN RETURN 'Mid-Level';
ELSE RETURN 'Junior';
END IF;
END //
DELIMITER ;
SELECT name, salary, GetSalaryGrade(salary) AS Level FROM Employee;Managing Procedures and Functions
sql exampleWoHoTech
-- List all procedures
SHOW PROCEDURE STATUS WHERE Db = 'university';
-- List all functions
SHOW FUNCTION STATUS WHERE Db = 'university';
-- View procedure code
SHOW CREATE PROCEDURE GetAllEmployees;
-- Drop
DROP PROCEDURE IF EXISTS GetAllEmployees;
DROP FUNCTION IF EXISTS CalculateBonus;Error Handling in Procedures
sql exampleWoHoTech
DELIMITER //
CREATE PROCEDURE SafeInsertEmployee(
IN p_id INT, IN p_name VARCHAR(100), IN p_salary DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Error occurred. Transaction rolled back.' AS Message;
END;
START TRANSACTION;
INSERT INTO Employee (emp_id, name, salary)
VALUES (p_id, p_name, p_salary);
COMMIT;
SELECT 'Employee inserted successfully.' AS Message;
END //
DELIMITER ;Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Functions and Stored Procedures.
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, functions, and, procedures
Related DBMS Topics
Continue learning this concept
Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.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 2CursorsA 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 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.