7. Procedure

What is a Procedure in MySQL?

A procedure (also called a stored procedure) is a group of SQL statements stored in the database that can be executed (called) whenever needed. It is mainly used to perform tasks (like insert, update, delete, or multiple queries together).

Syntax:

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
   -- SQL statements
END //

DELIMITER ;

Simple Example:

Let’s create a procedure that returns all rows from a students table:

DELIMITER //

CREATE PROCEDURE GetAllStudents()
BEGIN
   SELECT * FROM students;
END //

DELIMITER ;

To call the procedure:

CALL GetAllStudents();

Difference Between Procedure and Function:

Feature	Procedure	Function
Purpose	Performs an action (task)	Returns a value
Return Type	Does not return a value (can use OUT params)	Must return a value
Use in SQL	Cannot be used inside SELECT	Can be used inside SELECT
Call Syntax	`CALL proc_name()`	`SELECT func_name()`
Example Use Case	Insert records, update table	Calculate total marks, tax, etc.

Function Example (For Comparison):

DELIMITER //

CREATE FUNCTION getTotalMarks(m1 INT, m2 INT)
RETURNS INT
BEGIN
   RETURN m1 + m2;
END //

DELIMITER ;

Use:

SELECT getTotalMarks(40, 50); -- returns 90

Let me know if you want one with input/output parameters.