6. Procedimentos Armazenados (Stored Procedure)

Procedimentos armazenados são rotinas definidas no banco de dados, visando um melhor desempenho de aplicações. Um procedimento armazenado, uma vez definido no banco, é executado no servidor toda vez que solicitado, reduzindo assim, o tráfego na rede. Não há uma padronização para tal, cabendo a cada banco uma especificação diferente.

No MS SQL Server, define-se uma Stored Procedure da seguinte forma:

Create procedure <nomestoreproc> (@varparam1 tipo, ..., @varparam1 tipo)

as

<comandos SQL>

go

exemplo:

create procedure impressao @Param char(20)

as

            if  @Param = ‘1’

                        print ‘valor = 1’

            else

                        print ‘valor = 2’

go

Executando um procedimento armazenado

exec impressao ‘1’

Exemplo 2:

Fazer uma procedure para transferir dinheiro de uma conta A para uma conta B. Os números das contas de origem e destino deverão ser informados, assim como o valor a ser transferido. Após o débito, verificar o saldo da conta e retorná-lo, caso seja negativo.

if exists (select * from sysobjects where id = object_id('dbo.up_transfere_dinheiro'))

                drop procedure dbo.up_transfere_dinheiro

GO

/**********************************************************************************

comentários

***********************************************************************************/

Create Procedure up_transfere_dinheiro (@Agencia_Origem int, @Conta_Origem char(10),

   @Cliente_Origem int, @Agencia_Destino int,@Conta_Destino int,

   @Cliente_Destino int, @Valor numeric(16,8), @mensagem varchar(100)   output, @Saldo numeric(16,8) output)

AS

                -- Declarando variáveis locais

                Declare @Saldo_Auxiliar numeric(16,8)

                BEGIN TRANSACTION

                Update Depositos set Saldo = Saldo - @Valor

                   Where Agencia_Cod = @Agencia_Origem and Conta_Numero = @Conta_Origem and

                   Cliente_Cod = @Cliente_Origem

                if (@@error <> 0)

                Begin

                               Rollback Transaction       

                               Select @Mensagem = 'Erro debitando dinheiro'

                               return

                End

-- Pegando o valor do saldo após a atualização

Select @Saldo_Auxiliar = Saldo

   From Depositos

                   Where Agencia_Cod = @Agencia_Origem and Conta_Numero = @Conta_Origem and

                   Cliente_Cod = @Cliente_Origem

-- Tratando se o saldo é negativo

If @Saldo_Auxiliar < 0

    Select @Saldo = @Saldo_Auxiliar

                Update Depositos set Saldo = Saldo + @Valor

                   Where Agencia_Cod = @Agencia_Destino and Conta_Numero = @Conta_Destino and

                   Cliente_Cod = @Cliente_Destino

                if (@@error <> 0)

                Begin

                               Rollback Transaction       

                               Select @Mensagem = 'Erro creditando dinheiro'

                               return

                End

                COMMIT TRANSACTION

Return

GO

6.1. Gatilhos (tipo especial de procedimento armazenado)

Um gatilho é uma instrução executada automaticamente pelo sistema como um efeito colateral de uma modificação no banco de dados.

Para projetar um gatilho, precisamos:

v     Especificar as condições sob as quais o gatilho deve ser acionado.

v     Especificar as ações a serem tomadas quando um gatilho é executado.

Existem três formas de implementação de Trigger:

·      Trigger de Update: São ativados sempre que ocorrer uma ação de atualização(update) numa determinada tabela;

·      Trigger de Insert: São ativados sempre que ocorrer uma ação de inclusão (Insert) numa determinada tabela;

·      Trigger de Delete: São ativados sempre que ocorrer uma ação de exclusão (Delete) numa determinada tabela.

Suponha que, em vez de permitir saldos negativos, o banco trate o saque descoberto ajustando o saldo para zero e criando um empréstimo na quantia do saldo negativo. A este empréstimo é dado um número igual ao número da conta estourada. Para o exemplo anterior, a condição para executar o gatilho é uma atualização na relação depositos que resulte em um valor de saldo negativo. Seja d a tupla com valor negativo do saldo. As ações a serem tomadas são as seguintes:

v     Inserir uma nova tupla s na relação empréstimos com:

s[Cod_Agencia]=d[Cod_Agencia]

s[Numero_Emprestimo]=d[Conta_Numero]

s[quantia] = -d[saldo]

s[Cod_Cliente]=d[Cod_Cliente]

v     Negamos d[saldo] para inserirmos uma quantia de emprestimo positiva

v     Ajustar d[saldo] para 0

A SQL padrão não inclui os gatilhos, embora o System R SQL original incluísse um recurso limitado de gatilho. Diversos sistemas existentes têm seus próprios recursos não padrões de gatilhos. A seguir, será ilustrado como o gatilho saldo negativo poderia ser escrito na versão original da SQL:

define trigger saldo_negativo

on update of depositos D

( if new D.saldo < 0 then

(insert into Emprestimos values (D.Cod_Agencia,D.Conta_Numero,D.Cod_Cliente, -new D.saldo)

update depositos S

set S.saldo = 0

where S.Conta_Numero=D.Conta_Numero))

No MS SQL Server a sintaxe para um trigger dá-se como se segue:

create trigger <nome trigger>

on <tabela>

for <insert, delete, update>

as <comandos SQL>

ou, usando a cláusula if update

create trigger <nome trigger>

on <tabela>

for <insert, delete, update>

as

[if update (coluna1) or/and update (coluna2)...]

<comandos SQL>

Duas tabelas especiais (lógicas) são usadas em comandos create triggers: Deleted e Inserted. Elas são estruturalmente criadas quando um trigger é definido - A tabela Inserted contém tuplas inseridas pelo usuário. A tabela Deleted contém tuplas excluídas pelo usuário. No caso de atualização de valores de atributos, as duas tabelas são usadas. Uma tupla é removida e outra inserida.

Para o exemplo anterior, o gatilho poderia ser definido da seguinte forma usando o banco MS SQL Server.

create trigger saldo_negativo

on depositos D

for update

as

 if (select saldo from inserted) < 0 then

 Begin

insert into Emprestimos (Cod_Agencia, Cod_Cliente, Conta_Numero, quantia)

select Cod_Agencia, Cod_Cliente, Conta_Numero, saldo

from inserted

update Depositos

set saldo = 0

where exists (Select * from from Inserted as I

           where Depositos.Cod_Agencia = I.Cod_Agencia and Depositos.Cod_Cliente =

I.Cod_Cliente and Depositos.Conta_Numero = I.Conta_Numero)

End

Caso o usuário possa manipular várias linhas em um só comando SQL é necessário o uso de cursores. Cursores são definidos na SQL ANSI, e podem ser usados embutidos em linguagens de programação, tais como Fortran, Cobol, etc... O SQL Server incorpora este conceito e este pode ser usado na construção de gatilhos e procedimentos armazenados.

Um cursor é composto essencialmente de um tipo de ponteiro que pode ser usado para percorrer um grupo ordenado de linhas, apontando para cada uma das linhas nesse grupo por sua vez e fornecendo um modo de se endereçar essas linhas uma de cada vez. Se o cursor C estiver apontando para a linha L, diz-se que ele está posicionado na linha L. A linha L pode então ser atualizada ou excluida por meio da forma “posicionada”, usando as operações (update/delete ...where current of c).

Definições:

Declare <nome cursor>  [scroll / Insensitive] cursor for <comandos seleção SQL)> [for update] à Declara um cursor. A cláusula scroll possibilita o uso de previous,last,first e a cláusula for update, possibilita atualização via cursor.

Open  <nome cursor> à Abre um cursor declarado

Fetch <next,previous,last,first> from <nome cursor> into  <var1, var2, ..., varn> à Recupera uma linha específica do cursor

Close <nome cursor> à Fecha o cursor

Deallocate <nome cursor> à Libera a memória alocada para o cursor

SQLCODE à indica se houve ou não erro acessando o cursor. Quando o ponteiro aponta para o final do cursor, SQLCODE = 0 (varia de uma linguagem para outra)

No SQL Server, a variável SQLCODE, referenciada como @@Fetch_status, é usada para monitorar o status de um cursor. Se @@Fetch_status = 0, o fetch no cursor foi efetuado com sucesso, se for -1, houve um erro ao ler uma linha de um cursor, podendo indicar o fim do cursor.

Lista de exercícios à Comandos de atualização (banco PUBS)

1)  Escreva um comando para aumentar em 30% o preço de todos os livros escritos por autores residentes na “Califórnia”.

2)  Escreva um gatilho que seja capaz de realizar a inserção automática de um novo editor, sempre que na inserção de um livro (título) o editor informado for inexistente. Especifique para o novo editor a mesma cidade e estado do autor do livro. O nome do editor deve ser “Nome Indefinido”. Considere que na inserção poderá ter somente uma linha.

3)  Escreva um procedimento armazenado para adicionar à tabela Titles um novo campo phone, que deverá conter o número do telefone do autor principal do livro. Escreva também um gatilho para atualizar automaticamente o valor do campo phone em titles sempre que o valor do campo phone em authors for alterado.

4)  Escreva um procedimento armazenado que seja capaz de gerar/regerar uma tabela com os resultados obtidos a partir da consulta especificada na questão 13 da lista (trabalho) anterior, como se segue. (Escreva um comando que seja capaz de  identificar os autores que venderam pelo menos um livro em lojas situadas no estado de código “CA” (Califórnia). A consulta deverá retornar o nome do autor, o título e a quantidade de vendas efetuadas por título).

5)  Considere que o campo pub_id (da tabela publishers) seja auto incrementado a cada inserção de um novo autor. Faça um gatilho para fazer este incremento. Considere que poderá ser inserida uma linha à cada comando de inserção.

6)  Faça um gatilho para, ao remover um autor, remover todos os títulos à ele associados. Faça um outro gatilho para, ao ser atualizado o au_id de um autor, atualizar também na tabela titleauthor.

Obs: sugere-se eliminar a integridade referencial.

Segundo Trabalho

      Gerar um banco de dados conforme a especificação abaixo:

      Criar um diagrama para o esquema recém criado.

Implementar Integridade Referencial via Triggers

Outros Triggers

      Bloquear a tentativa de matricula de aluno em disciplina com pré-requisito não cursado;

      Bloquear a tentativa de matricula de aluno em turma com conflito de horário;

      Fazer auto incremento do campo Codigo_Período  em Período. Considerar que podem ser cadastradas mais de um período em um único comando SQL. (Usar cursores).

Obs.: Lembrar que o trigger no  MS SqlServer é disparado após a modificação no banco de dados.

Stored Procedures

      Oferta de turmas por curso, com descrição do horário, para um curso e período definidos;

      Lista de disciplinas, com detalhamento dos seus pré-requisitos, para um dado curso;

      Horário individual, para todos os alunos de um dado curso, em um dado período (retornar: nome do curso, nome do aluno, nome da disciplina, turma e horário);

      Nota mínima, nota máxima por disciplina, para um dado curso;

      Lista de disciplinas obrigatórias que um aluno ainda deve cursar para formar.

Visões

      Histórico dos alunos (retornar todas as disciplinas cursadas e aprovadas, para cada aluno);

      Relação de alunos e disciplinas nas quais cada um deles foi reprovado;