Banco de Dados Prof. MSc Wagner Siqueira Cavalcante

1. Banco de Dados Prof. MSc Wagner Siqueira Cavalcante

2. Banco de Dados: Stored Procedures e Triggers • Stored Procedure é um conjunto de instruções escrito numa linguagem própria para procedures (procedimentos) e triggers (gatilhos) do SGBD, e que é armazenado como parte do banco de dados. • Stored Proceduresreduzem o tráfego na rede, pois são executadas pelo SGBD na máquina servidora de banco de dados. • Stored Proceduresnão permitem instruções DDL... • Triggerssão quase a mesma coisa que stored procedures, exceto pelo modo como são chamadas (e mais alguns pequenos detalhes): • Stored procedures podem ser chamadas por aplicações cliente, outras stored proceduresou triggers. • Triggerssão chamados automaticamente quando uma alteração em uma linha da tabela em questão ocorre. BD – Stored Procedures e Triggers

3. Stored Procedures • CREATE or ALTER PROCEDURE NomedaProcedure • ( <parâmetros de entrada> ) • RETURNS • ( <parâmetros de saída> ) • AS • <declaração de variáveis locais> • BEGIN • <comandos da procedure> • END • Sintaxe de Stored Procedures: Parâmetros de entrada: Valores iniciais, que servem para estabelecer o comportamento do procedimento (todos os tipos, exceto BLOB ou ARRAY). Parâmetros de saída: Valores que retornam os resultados desejados, executados pelo procedimento (idem ao acima). Comandos da procedure: Conjunto de instruções SQL... BD – Stored Procedures

4. Código Matrícula Nome Nome CPF Data de nascimento Endereço nacionalidade Editora Preço Autor Lançamento publica 1,N 0,1 Autor-Livro Livro Assunto 0,N 1,N Descrição Código 1,1 Sigla 0,N Data de contrato é de um Um livro é escrito por um ou mais escritores, e os registram em uma única Editora, que pode publicar nenhum, um ou vários livros. Porém, nem todos estes livros conseguem ser lançados, embora já estejam registrados. Cada livro está associado a um determinado assunto, e pode ser escrito por um ou vários autores, os quais, por sua vez, podem escrever tantos livros quantos queiram. BD – Stored Procedures

5. Um livro é escrito por um ou mais escritores, e os registram em uma única Editora, que pode publicar nenhum, um ou vários livros. Porém, nem todos estes livros conseguem ser lançados, embora já estejam registrados. Cada livro está associado a um determinado assunto, e pode ser escrito por um ou vários autores, os quais, por sua vez, podem escrever tantos livros quantos queiram. Por enquanto, considere apenas a tabela Autor... BD – Stored Procedures

6. Exemplo 01: • Projete o nome, nascimento e nacionalidade do autor, cujo código será digitado em uma caixa de diálogo: • Select nome, nascim, nacional • From Autor • Where Matricula = :”Código” Experimente, por exemplo, com 505 BD – Stored Procedures

7. Exemplo 01 (continuação): • Projete o nome, nascimento e nacionalidade do autor, cujo código será digitado em uma caixa de diálogo: • Select nome, nascim, nacional • From Autor • Where Matricula = :”Código” BD – Stored Procedures

8. Exemplo 01 (continuação): • Projete o nome, nascimento e nacionalidade do autor, cujo código será digitado em uma caixa de diálogo: • Select nome, nascim, nacional • From Autor • Where Matricula = :”Código” Há só UMA resposta! BD – Stored Procedures

9. Exemplo 01 (continuação): • A seguir, crie um procedimento para executar esta situação: • Inicie o SQL Editor, digite as seguintes instruções SQL, salve-a com o nome “SP_Dados_Autor”. • Select nome, nascim, nacional • From Autor • Where Matricula = :entra_Codigo • CREATE or ALTER PROCEDURE SP_Dados_Autor • (entra_Codigosmallint) • RETURNS (sai_Nomevarchar(80), • sai_Nascim date, • sai_Nacionalvarchar(30) • ) • AS • BEGIN • Select nome, nascim, nacional • From Autor • Where Matricula = :entra_Codigo • INTO :sai_Nome, :sai_Nascim, :sai_Nacional; • SUSPEND; • END BD – Stored Procedures

10. Exemplo 01 (final): • E para usar o procedimento criado? • SELECT * • FROMSP_Dados_Autor ( ‘505’ ); • CREATE or ALTER PROCEDURE SP_Dados_Autor • (entra_Codigo smallint) • RETURNS (sai_Nome varchar(80), • sai_Nascim date, • sai_Nacional varchar(30) • ) • AS • BEGIN • Select nome, nascim, nacional • From Autor • Where Matricula = :entra_Codigo • INTO :sai_Nome, :sai_Nascim, :sai_Nacional; • SUSPEND; • END • CREATE or ALTER PROCEDURE SP_Dados_Autor • (entra_Codigo smallint) • RETURNS (sai_Nome varchar(80), • sai_Nascim date, • sai_Nacional varchar(30) • ) • AS • BEGIN • Select nome, nascim, nacional • From Autor • Where Matricula = :entra_Codigo • INTO :sai_Nome, :sai_Nascim, :sai_Nacional; • SUSPEND; • END BD – Stored Procedures

11. Exemplo 02: • Agora, experimente projetar os nomes, nascimento e nacionalidades do autores, cujas nacionalidades serão lidas na caixa de diálogo: • Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid Experimente, por exemplo, com brasileira BD – Stored Procedures

12. Exemplo 02 (continuação): • Agora, experimente projetar os nomes, nascimento e nacionalidades do autores, cujas nacionalidades serão lidas na caixa de diálogo: • Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid Há MAIS QUE UMA resposta! BD – Stored Procedures

13. Exemplo 02 (continuação): • Crie, então, o procedimento armazenado para executar esta situação: • Inicie o SQL Editor, digite as seguintes instruções SQL, salve-a com o nome “dadosproprietario2” e a execute. • Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid • CREATE or ALTER PROCEDURE SP_Dados_Autor2 • (In_nacionalid varchar(30)) • RETURNS (out_Nome varchar(80), • out_Nascim date, • out_Nacional varchar(30) • ) • AS • BEGIN • INTO :out_Nome, :out_Nascim, :out_Nacional; • SUSPEND; • END BD – Stored Procedures

14. Exemplo 02 (continuação): • Crie, então, o procedimento armazenado para executar esta situação: • Inicie o SQL Editor, digite as seguintes instruções SQL, salve-a com o nome “dadosproprietario2” e a execute. • CREATE or ALTER PROCEDURE SP_Dados_Autor2 • (In_nacionalid varchar(30)) • RETURNS (out_Nome varchar(80), • out_Nascim date, • out_Nacional varchar(30) • ) • AS • BEGIN • Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid • INTO :out_Nome, :out_Nascim, :out_Nacional; • SUSPEND; • END Oops! Problemas, já que há mais que uma resposta! Usar: FOR SELECT ... DO SUSPEND; BD – Stored Procedures

15. Exemplo 02 (continuação): • Crie, então, o procedimento armazenado para executar esta situação: • Inicie o SQL Editor, digite as seguintes instruções SQL, salve-a com o nome “dadosproprietario2” e a execute. • CREATE or ALTER PROCEDURE SP_Dados_Autor2 • (In_nacionalid varchar(30)) • RETURNS (out_Nome varchar(80), • out_Nascim date, • out_Nacional varchar(30) • ) • AS • BEGIN • FOR Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid • INTO :out_Nome, :out_Nascim, :out_Nacional • DO • SUSPEND; • END Agora, sem o “;” aqui BD – Stored Procedures

16. Exemplo 02 (continuação): • E para usar o procedimento criado? • SELECT * • FROMSP_Dados_Autor2 ( ‘brasileira’ ); • CREATE or ALTER PROCEDURE SP_Dados_Autor2 • (In_nacionalid varchar(30)) • RETURNS (Out_Nome varchar(80), • Out_nascim date, • Out_nacional varchar(30) • ) • AS • BEGIN • FOR Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid • INTO :Out_nome, :Out_nascim, :Out_nacional • DO • SUSPEND; • END • CREATE or ALTER PROCEDURE SP_Dados_Autor2 • (In_nacionalid varchar(30)) • RETURNS (Out_Nome varchar(80), • Out_nascim date, • Out_nacional varchar(30) • ) • AS • BEGIN • FOR Select nome, nascim, nacional • From Autor • Where nacional = :In_nacionalid • INTO :Out_nome, :Out_nascim, :Out_nacional • DO • SUSPEND; • END • SP_Dados_Autor2 • (In_nacionalid ) • :In_nacionalid BD – Stored Procedures

17. Exemplo 02 (final): • Experimente, também: • SELECT * • FROMSP_Dados_Autor2 ( :Nacion ); • SELECT * • FROMSP_Dados_Autor2 ( :”Nacionalidade” ); • SELECT * • FROMSP_Dados_Autor2 ( :”Digite a nacionalidade” ); • SELECT nome as “Nome do autor” • FROMSP_Dados_Autor2 ( :”Digite a nacionalidade” ); BD – Stored Procedures

18. Exercício 01: • Crie um procedimento para calcular o valor médio dos livros de uma determinada editora, lida como parâmetro. • Salve-o com o nome de “avglivros” • Compile-o e efetive (commit) • Chame-o, via SQL, com o parâmetro predefinido (‘Brasoft Editora’, por exemplo) • Chame-o, via SQL, com o parâmetro digitado via diálogo • Exercício 02: • Projete os títulos, datas de lançamento e valores dos livros que têm o seu valor maior que o valor médio daquele digitado via diálogo, baseado no procedimento “avglivros”. BD – Stored Procedures