MySQL Stored Procedures and Functions

1. Creating a Stored Procedure

A Stored Procedure is a saved block of SQL code that you can execute multiple times. It helps reduce code duplication and improves efficiency in database operations.

General Syntax:

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

Example:

CREATE PROCEDURE GetAllCustomers()
BEGIN
  SELECT * FROM customers;
END;

Calling the Procedure:

CALL GetAllCustomers();

2. Creating a Stored Function

A Stored Function is similar to a procedure, but it always returns a single value.
Functions can be used directly inside SQL queries.

General Syntax:

CREATE FUNCTION function_name(parameters)
RETURNS datatype
DETERMINISTIC
BEGIN
   -- SQL statements
   RETURN value;
END;

Example:

CREATE FUNCTION total_price(price DECIMAL(10,2), qty INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * qty;
END;

Using the Function in a Query:

SELECT total_price(100.5, 3); 

Output: 301.50

PreviousNext