Stored Routines

Routine in a context other than a computer, a usual, fixed action, or series of actions, repeated periodically.
Stored routine
An SQL statement, or a set of SQL statements, that can be stored on the database server
Whenever a user needs to run the query in question, they can call, reference, or invoke the routine.
Types of Store routines
1. Stored procedures
2. functions = user define functions not built-in functions like aggregate function ,date function
The MySQL Syntax for Stored Procedures
DELIMITER $$
CREATE or replace PROCEDURE procedure_name()
BEGIN
SQL QUERY
END$$
DELIMITER;  From this moment on, $$ will not act as a delimiter.
How can you call the Stored Procedure?
call employees.select_employees();
call employees.select_employees;
call select_employees();
call select_employees;
Here employees is the database name and select_employees is the stored procedure name.
Stored Procedures with an Input Parameter
A stored routine can perform a calculation that transforms an input value in output value.
Stored procedures can take an input value and then use it in the query, or queries, written in the body of the procedure, this value is represented by the IN parameter.
For example:
DELIMITER $$
CREATE PROCEDURE procedure_name
(in parameter)
BEGIN
SELECT * FROM employees
LIMIT 1000;
END$$
DELIMITER ;
After that calculation is ready, a result will be returned.
Stored Procedures with an Output Parameter
DELIMITER $$
CREATE PROCEDURE procedure_name
(in parameter, out parameter)
BEGIN
SELECT * FROM employees
LIMIT 1000;
END$$
DELIMITER ;
Note : 
Output Parameter: It will represent the variable containing the output value of the operation executed by the query of the stored procedure.
Every time you create a procedure containing both an IN and an OUT parameter, remember that you must use the SELECT INTO structure in the query of this object’s body.
For Example
DELIMITER $$
CREATE PROCEDURE emp_info(in p_first_name varchar(255), in p_last_name varchar(255), out p_emp_no integer)
BEGIN
SELECT
e.emp_no
INTO p_emp_no FROM
employees e
WHERE
e.first_name = p_first_name
AND e.last_name = p_last_name;
END$$
DELIMITER ;
Variables
when you are defining a program, such as a stored procedure, for instance, you can say you are using parameters
Parameters are a more abstract term
Once the structure has been solidified, then it will be applied to the database. The input value you insert is typically referred to as the argument, while the obtained output value is stored in a variable.
IN-OUT parameters
input = output
User-Defined Functions in MySQL
DELIMITER $$
CREATE FUNCTION function_name(parameter data_type) RETURNS data_type
DECLARE variable_name data_type
BEGIN
SELECT …
RETURN variable_name
END$$
DELIMITER ;
Note : 
Parameters: Here you have no OUT parameters to define between the parentheses after the object’s name, all parameters are IN, and since this is well known, you need not explicitly indicate it with the word, ‘IN’.
Although there are no OUT parameters, there is a return value
It is obtained after running the query contained in the body of the function.
How we can call the function in MySQL
SELECT function_name(input_value);

Difference between Stored Procedure and User-defined Function

Stored Procedure	User-defined function
It doesn’t return a value.	Returns a value.
CALL Procedure;	SELECT function;
It can have multiple OUT parameters.	It can return a single value only.
You can use SELECT, INSERT, UPDATE, DELETE with stored procedures.	But you can use only select statement with function.
You can’t include the procedure in a SELECT statement.	You can easily include function as a column in a SELECT statement.

Next Topic: VIEWS