What is

Stored Procedure, translated Stored Procedure, is a library of SQL commands for use next to the database. It stores repetitive tasks and accepts input parameters so that the task is performed according to individual needs.

A Stored Procedure can reduce network traffic, improve database performance, create scheduled tasks, reduce risk, create processing routines, and more. For all these and other features, stored procedures are of the utmost importance to DBAs and developers.

When to use procedures

• When we have several applications written in different languages, or run on different platforms, but perform the same function.
• When we prioritize consistency and security.

Source: link

Examples

MySQL

Imagine the following scenario, I have a table with the name, id, and salary fields. Every semester end the company provides a standard increase of 300 reais and a raise of 500 reais for all outstanding employees who receive positive remarks, you could elaborate a procedure to make this change in salaries, as below:

DELIMITER $$

CREATE PROCEDURE AumentarSalario(IN quantidadeObservacoesPositivas INT, codigoFuncionario INT)
BEGIN
    IF quantidadeObservacoesPositivas = 0 THEN
            <update que aumenta salario em 300 usando o codigoFuncionario recebido>
    ELSE
            <update que aumenta salario em 500 usando o codigoFuncionario recebido>
    END IF
END $$
DELIMITER ;

So you could use it every time you run this account for an employee.

Summary

Procedure are sql command blocks, we use to concentrate codes, especially when we will use them more than once.