Procedures armazenadas definidas pelo usuário em SQL

Você usará o Microsoft SQL Server como banco de dados. O conceito abordado neste tutorial é o mesmo para a maioria dos sistemas de gerenciamento de banco de dados relacionais (RDBMS), mas as sintaxes variam — ainda assim, a ideia apresentada aqui funciona bem em todos os bancos.

Procedure armazenada é um bloco de instruções SQL em um sistema de gerenciamento de banco de dados relacional (RDBMS). Normalmente é escrita por programadores, administradores de banco (DBAs) e analistas de dados, ficando salva para reutilização em vários programas. Também pode existir em diferentes tipos, a depender do RDBMS. No entanto, há dois tipos essenciais presentes em qualquer RDBMS:

• user-defined stored procedure (definida pelo usuário)
• system stored procedure (do sistema)

Uma stored procedure é um código pré-compilado que fica armazenado (em cache) para reutilização. Ter código em cache facilita muito a manutenção — você não precisa alterar a mesma lógica em vários lugares — e também ajuda a manter a segurança.

Criando uma tabela

Agora você vai criar uma tabela chamada table_Employees assim:

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)

A tabela reúne informações sobre funcionários de uma empresa e inclui as seguintes colunas:

1. EmployeeId: chave primária, única, do tipo inteiro. Em muitos cenários, esse valor é auto-incrementado.
2. EmployeeFirstName: primeiro nome do funcionário, do tipo caractere, e não pode ficar em branco.
3. EmployeeLast Name: sobrenome do funcionário, também do tipo caractere e obrigatório.
4. EmployeeGender: gênero do funcionário, também do tipo caractere.
5. EmployeeDepartmentId: id do departamento do funcionário, do tipo inteiro.

Você pode escrever uma query para inserir valores na tabela. O padrão para inserir valores é:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)

Depois de inserir os valores, a table_Employees estará criada e populada.

Como criar uma stored procedure definida pelo usuário

Uma stored procedure permite que você escreva uma query uma vez, dê um nome a ela e execute quantas vezes precisar. Ao executar as queries, você verá a pasta Programmability->Stored Procedures e um arquivo chamado dbo.uprocGetEmployees.

A sintaxe para criar uma stored procedure é:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END

Você pode criar uma stored procedure usando um dos comandos abaixo:

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Evite criar procedures com o prefixo "sp_". No exemplo, a procedure foi criada com o nome: uprocGetEmployees.

Como executar a stored procedure

Para executar uma stored procedure, você pode usar três abordagens e rodar assim:

• EXEC <>
• EXECUTE <>
• <>

No exemplo acima, use o nome da procedure uprocGetEmployees e execute a query.

Stored procedure com parâmetros

Uma stored procedure pode aceitar um ou vários parâmetros. Entender múltiplos parâmetros ajuda a compreender bem o caso de um único parâmetro.

A sintaxe para criar uma stored procedure com múltiplos parâmetros é:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END

No exemplo, houve uma alteração na pasta:
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

O conceito é parecido com funções em linguagens como Python ou Java. A procedure acima se chama "uprocGetEmployeesGenderAndDepartment" e recebe os parâmetros @EmployeeGender e @EmployeeDepartmentId. Eles são "chamados" ao passarmos os valores necessários para a procedure — o que pode ser feito de duas formas, explicadas a seguir.

Os parâmetros do programa são @EmployeeGender e @EmployeeDepartmentId, que recebem valores quando a procedure é executada.

Os valores podem ser especificados de duas maneiras. Você pode usar qualquer uma para executar a stored procedure com múltiplos parâmetros.

• Especificando os nomes dos parâmetros na query e passando os valores desejados:

• Ou pela posição, na mesma ordem dos parâmetros da stored procedure, passando os valores correspondentes:

Alterando a stored procedure

Você também pode alterar uma stored procedure usando o comando ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END

Excluindo uma stored procedure

Stored procedures podem ser removidas rapidamente com os seguintes comandos:

DROP PROC <> DROP PROCEDURE <>

Adicionando segurança com criptografia

O cadeado indicado na imagem à esquerda mostra que a stored procedure está criptografada, garantindo acesso apenas a pessoas autorizadas.

Conclusão

Você acabou de aprender o básico sobre stored procedures definidas pelo usuário, em conceitos que valem para qualquer RDBMS. Quer ir além? Acesse a DataCamp e faça o curso Intermediate SQL Server. Este tutorial da Microsoft sobre Stored Procedures também é uma ótima referência.