Marcelo Henrique dos Santos

(1)

BANCO DE DADOS – MODELAGEM E IMPLEMENTAÇÃO

Mestrado em Educação (em andamento) MBA em Marketing e Vendas (em andamento)

Especialista em games

Bacharel em Sistema de Informação

(2)

AULA 05

Procedures

(3)

Stored Procedures

• É um conjunto de comandos SQL definidos pelo usuário que ficam armazenados num BD como um procedimento/função, para eventuais processamentos.

• São processamentos de tarefas da aplicação que residem no SGBD ao invés de no código da aplicação (cliente).

(4)

Stored Procedures

• Vantagens:

• 1.Desempenho

– Ex.: Seja a consulta

SELECT codigop, nome, COUNT(*) FROM Projeto p, Alocacao a

WHERE p.codproj = a.codigop GROUP BY p.codproj, p.nome

(5)

Stored Procedures

• Se vários usuários realizarem esta consulta o tráfego de rede será alto.

• se criarmos uma stored procedure para executar esta consulta, os usuários necessitarão apenas de um comando para executar a consulta anterior: EXEC nomeProcedimento;

• Outro ponto é a compilação, a consulta anterior seria compilada a cada chamada, enquanto o procedimento contendo a consulta seria compilado uma única vez

(6)

Stored Procedures -

Vantagens

• 2. Facilita o gerenciamento do BD, pois a consulta é escrita em um único lugar, portanto a manutenção desta torna-se mais eficaz e segura.

(7)

Stored Procedures -

Vantagens

• 3. Segurança: podemos usar stored

procedures para limitar o acesso de alguns usuários ao BD. Desta forma, a maneira em que o BD pode ser modificado é estritamente definida.

(8)

Stored Procedures:

SQL/PSM

• SQL/PSM - Persistent Stored Modules: parte do padrão SQL relativo às Stored Procedures • No momento cada SGBD oferece sua própria

linguagem (Oracle PL/SQL, Microsoft

Transact/SQL, etc)

• Em PSM, definimos módulos que são coleções de definições de funções ou procedimentos, declarações de tabelas temporárias, dentre outros.

(9)

Stored Procedures

-SQL/PSM

• Criando Funções e Procedimentos

– CREATE PROCEDURE <NOME> (<parâmetros>) declarações locais

corpo do procedimento

– CREATE FUNCTION <NOME> RETURNS <tipo> declarações locais

corpo da função

• obs.: parâmetros são do tipo modo-nome-tipo (onde modo indica IN, OUT ou INOUT)

(10)

Stored Procedures

-SQL/PSM

Exemplo:

CREATE PROCEDURE MudaEndereco ( IN endAntigo VARCHAR(255),

IN endNovo VARCHAR(255)

)

UPDATE Empregado SET endereco = endNovo

WHERE endereco = endAntigo;

(11)

Stored Procedures

-SQL/PSM

Alguns Comandos:

1) Chamada a um procedimento:

CALL <nome procedure> (<lista argumentos>);

Obs.: CALL é aplicado apenas a Procedures (não a Function) Esta chamada pode ser realizada de vários lugares:

- Programa com SQL embutido

EXEC SQL CALL calcula(:x, 3);

- Como comando em outro procedimento ou função PSM: CALL calcula (10);

(12)

Stored Procedures

-SQL/PSM

2) Comando de Retorno (usado apenas em funções)

RETURN <expressão>; (OBS este comando não encerra a função)

) Declaração de variáveis locais: DECLARE <nome> <tipo>;

(13)

Stored Procedures

-SQL/PSM

3) Comando de atribuição

SET <variável> = <expressão>;

4) Grupo de comandos:

delimitados por BEGIN e END

5) Labels: colocamos labels em comandos precedendo estes pelo nome do label e dois pontos.

(14)

Stored Procedures

-SQL/PSM

6) Comandos condicionais 7) Laços

IF <condição> THEN LOOP

<comandos> <Comandos> ELSEIF <condição> THEN END LOOP;

<comandos> …

ELSE <comandos> END IF;

(15)

Stored Procedures

-SQL/PSM

Exemplo: Função sobre o esquema Filmes que recebe um ano e nome de estúdio e retorna TRUE se aquele estúdio produziu apenas um filme preto e branco naquele ano ou nada produziu. CREATE FUNCTION PretoeBranco( a int, studio char[15])

RETURNS BOOLEAN IF not exists (

select * from Filme where ano = a and nomeStudio = studio)

THEN RETURN TRUE; -- não faz a função retornar agora ELSEIF 1 <=

(select count(*) from Filme where ano = a and nomeStudio = nome and NOT emcores)

THEN RETURN TRUE; ELSE RETURN FALSE;

(16)

Stored Procedures

-SQL/PSM

Exemplo: Procedimento que calcula a média e variância de um estúdio CREATE PROCEDURE MeanVar ( IN s char[15],

OUT mean REAL, OUT variance REAL) DECLARE NotFound FOR SQLSTATE ‘02000’; DECLARE filmeCursor CURSOR FOR

select duracao from Filme where nomeStudio = s; DECLARE novaDuracao INTEGER;

DECLARE contaFilmes INTEGER; BEGIN SET mean = 0.0; SET variance = 0.0; SET contaFilmes = 0; OPEN filmeCursor; filmeLOOP: LOOP

FETCH filmeCursor INTO novaDuracao;

IF NotFound THEN LEAVE filmeCurdor END IF; SET contaFilmes = contaFilmes + 1;

SET mean = mean + novaDuracao;

SET variance = variance + novaDuracao * novaDuracao; END LOOP;

SET mean = mean / contaFilmes;

SET variance = variance/contaFilmes - mean * mean; CLOSE filmeCursor;

(17)

Stored Procedures

-SQL/PSM

For-Loops

usado para fazer iterator num cursor

FOR <nome laço> AS <nome cursor> CURSOR FOR <query>

DO

<comandos> END FOR;

Veja exemplo no próximo slide! - WHILE <condição> DO <comandos> END WHILE; - REPEAT <comandos> UNTIL <condição> END REPEAT;

(18)

Stored Procedures

-SQL/PSM

Exemplo: Mesmo procedimento de média e variância de estúdios, usando FOR-Loops

CREATE PROCEDURE MeanVar ( IN s char[15], OUT mean REAL, OUT variance REAL) DECLARE contaFilmes INTEGER;

BEGIN

SET mean = 0.0; SET variance = 0.0; SET contaFilmes = 0;

FOR filmeLOOP AS filmeCursor CURSOR FOR select duracao from Filme where nomeStudio = s; DO

SET contaFilmes = contaFilmes + 1; SET mean = mean + novaDuracao;

SET variance = variance + novaDuracao * novaDuracao; END FOR;

SET mean = mean / contaFilmes;

SET variance = variance/contaFilmes - mean * mean; END;

(19)

Stored Procedures

-SQL/PSM

Exceções em PSM:

É possível testar o SQLSTATE para verificar a ocorrência de erros e tomar uma decisão, quando erros ocorram.

Isto é feito através do EXCEPTION HANDLER que é

associado a blocos BEGIN END (o handler aparece dentro do bloco) Os componentes do handler são:

1) Lista de exceções a serem tratadas

2) Código a ser executado quando exceção ocorrer

3) Indicação para onde ir depois que o handler concluir

SINTAXE: DECLARE <onde ir> HANDLER FOR <condições> <comando>

As escolhas de <onde ir> são: - CONTINUE

- EXIT (sai do bloco BEGIN .. END) - UNDO

(20)

Stored Procedures

-SQL/PSM

Exemplo de exceções em PSM:

CREATE FUNCTION getSalario (mat integer) RETURNS FLOAT DECLARE NotFound CONDITION FOR SQLSTATE ‘02000’; DECLARE TooMany CONDITION FOR SQLSTATE ‘21000’; BEGIN

DECLARE EXIT HANDLER FOR NotFound, TooMany RETURN NULL;

RETURN ( select salario from Empregado where where matricula = mat); END;

(21)

BIBLIOGRAFIA

• Elmasri & Navathe – Fundamentos de Bancos de Dados

• Carlos Alberto Heuser – Projeto de Banco de Dados

• Korth e Silberchatz – Sistema de Bancos de Dados

PROGRAMAÇÃO ORIENTADA A EVENTOS

(22)

BIBLIOGRAFIA

• Baptista, Cláudio. Banco de Dados II / Capítulo 1: SQL-PSM. Bacharelado em Ciência da

Computação UFCG/DSC.

• HEUSER, C.A. Projeto de Banco de Dados. 6ª Edição. Porto Alegre. Capítulos 2 e 3

• Korth, H. F.; Sudarshan, S; Silberschatz, A. Sistema de Banco de Dados. 5a ed. Editora Campus, 2006. - Capítulo 6

(23)

BIBLIOGRAFIA

• HEUSER, C.A. Projeto de Banco de Dados. 6ª Edição. Porto Alegre. Capítulos 2 e 3

• Korth, H. F.; Sudarshan, S; Silberschatz, A. Sistema de Banco de Dados. 5a ed. Editora Campus, 2006. - Capítulo 6

• Elmasri, R.; Navathe S. B. Sistemas de Banco de Dados. 4 ed. Editora Addison-Wesley. 2005. - Capítulo 3

(24)

BIBLIOGRAFIA

• Elmasri, R.; Navathe S. B. Sistemas de Banco de Dados. 4 ed. Editora Addison-Wesley. 2005. - Capítulo 3