What is a database database?

Question

For some time I have been following issues involving the term procedure in database, then doubts:

1. What is procedure?
2. Where to use it, what’s the point?

If possible examples of use clear the mind of the layman(my).

Answer 1

What is

Stored Procedure, translated Stored Procedure, is an SQL command library 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 the performance of a database, create scheduled tasks, decrease risk, create process routines, etc. For all these and other features is that stored procedures are of 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: http://codigofonte.uol.com.br/artigos/tudo-sobre-procedures

Examples

Mysql

Imagine the following scenario, I have a table with the fields name, id and salary. Every end of semester the company provides a standard increase of 300 reais and an increase of 500 reais for all employees with highlights, who receive positive observations, you could draft a precedent to carry out this change in wages, 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 whenever you hold this account for an employee.

Summary

Procedure are blocks of sql commands, we use to concentrate code, especially when we use them more than once.

Answer 2

According to Rodrigo Dornel:

One procedure is a block of commands or SQL statements organized to perform one or more tasks. It can be used to be triggered by a simple call that executes a series of other commands.

Example:

CREATE PROCEDURE uspRetornaIdade
@CodigoCliente int
AS
SELECT Clientes.ClienteNome, YEAR(GETDATE())-YEAR(ClienteNascimento) AS IDADE
FROM Clientes
INNER JOIN Contas ON Clientes.ClienteCodigo=Contas.ClienteCodigo
WHERE Clientes.ClienteCodigo = @CodigoCliente;

And this is a definition on the Microsoft website:

Stored procedures are similar to procedures in other programming languages in the sense that they can:

• Accept input parameters and return multiple values in the output parameters form for call or batch procedure.
• Contain programming instructions that perform operations in the database, including calling other procedures.
• Return a status value to a call or batch procedure to indicate success or failure (and the reason for failure).