Stored Procedures:

They, in their turn, can be composed of several SQL statements, conditional structures, and variables.

Using stored procedures leads to the executing of the procedures on the database server, and thereby avoiding the network traffic and improving the performance.

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

                                  
                                

Functions:

They may take arguments and do numeric or logical computation returning a result afterwards.

There is plenty of function that can be used in SQL query, expressions, and other functions.

                                  
DELIMITER //
CREATE FUNCTION function_name(param1 INT, param2 INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    -- Calculate result
    RETURN result;
END //
DELIMITER ;

                                  
                                

Calling and Executing Stored Procedures and Functions:

• Calling Stored Procedures:

  Use the CALL statement to execute a stored procedure.

                                
                                CALL procedure_name();
                                
                              

• Calling Functions:

  Use the SELECT statement to call a function and retrieve its result.

                                
                                SELECT function_name(param1, param2);
                                
                              

Example Usage:

Create a stored procedure to calculate the total salary of an employee based on their ID:

                                
DELIMITER //
CREATE PROCEDURE calculate_total_salary(employee_id INT)
BEGIN
    DECLARE total_salary DECIMAL(10, 2);
    SELECT SUM(salary) INTO total_salary FROM salaries WHERE employee_id = employee_id;
    SELECT total_salary;
END //
DELIMITER ;

                                
                              

Call this stored procedure to calculate the total salary for a specific employee:

                                
                                CALL calculate_total_salary(123);