Projeto de Banco de Dados - Aula 4

Stored Programs

Execução condicional

É possível controlar o fluxo de execução utilizando IF e CASE,  ambos possuem praticamente a mesma funcionalidade.

 4.1

Loops

Permitem stored programs executar declarações repetidamente. São 3 os tipos de Loop:

- Loops simples utilizando LOOP e END LOOP;

- Loops que continuam enquanto uma condição é verdadeira, usando as cláusulas WHILE e END WHILE;

- Loops que continuam até que a condição seja verdadeira, usando as cláusulas REPEAT e UNTIL.

Nos 3 casos, a execução é concluída com a declaração LEAVE.

4.2

Resultado da execução

4.3

Tratando Erros

Quando um erro ocorre em um stored program o comportamento padrão é terminar o programa a passar o erro pro programa-chamador.

O que fazer quando precisamos responde de uma forma diferente ao erro?

Criamos um Error Handler

Vamos verificar 2 cenários....

Tratando Erros

Cenário 1:

Se pensarmos que um SQL incorporado pode não retornar linha alguma, ou precisássemos recuperar todas as linhas usando com comando SELECT utilizando um cursor (http://en.wikipedia.org/wiki/Cursor_(databases)), um NOT FOUND iria impedir que o stored program terminasse prematuramente.

Cenário 2:

Se pensarmos que um comando SQL pode retornar um erro (violação de restrição, por exemplo), podemos criar um tratador para evitar o término do programa. O tratador irá permitir processar o erro e continuar a execução do programa.

Interagindo com o Banco de Dados

A maioria dos stored programs envolve algum tipo de interação com o Banco de Dados. Existem quatro principais tipos:

- Guardar o resultado de um comando SQL em uma variável;

- Criar um curso que permite o stored program iterar através das linhas retornadas pelo comando SQL;

- Executar um comando SQL e devolver o resultado ao programa-chamador;

- Incorporar comando SQL que não retornam um conjunto de registros.

Interagindo com o Banco de Dados - SELECTing INTO Local Variables

Usamos SELECT INTO quando buscamos informações de uma única linha de dados.

Neste caso incluímos a cláusula INTO dentro do SELECT que diz ao banco de dados onde colocar o dado recebido.

4.4

4.5

Interagindo com o Banco de Dados – Utilizando Cursores

SELECT INTO é bom para resultados de uma única linha.

É quando queremos tratar um retorno com múltiplas linhas?

Resposta... Use cursores

Um cursor permite obtermos uma ou mais linhas de conjunto de registros, normalmente com a intenção de interarmos processando linha-a-linha.

4.6

Interagindo com o Banco de Dados – Incorporando Non_SELECTs

4.7

Interagindo com o Banco de Dados – Chamando Stored Programs de Stored Programs

4.8

Criando e usando Cursores

Para tratar um comando SELECT que retornar mais de uma linha, nós devemos criar e manipular um cursor.

Um cursor é um objeto que fornece programaticamente acesso a um conjunto de resultados retornados pelo SELECT.

Usamos um cursor para iterar através das linhas no conjunto de resultados e executar uma ação para cada linha individualmente.

Definindo um cursor.

Definimos um cursor com a comando DECLARE que tem a seguinte sintaxe:

DECLARE cursor_name CURSOR FOR SELECT_...;

A declaração do cursor deve ocorrer depois da declaração de todas as variáveis.

Declarar antes gera um erro 1337.

Um cursor está sempre associado com um comando SELECT.

Um cursor pode referenciar variáveis dentro da cláusula WHERE ou uma lista de colunas.

CREATE PROCEDURE cursor_demo (in_customer_id INT)

BEGIN

DECLARE v_customer_id INT;

DECLARE v_customer_name VARCHAR(30);

DECLARE c1 CURSOR FOR

SELECT in_customer_id,customer_name

FROM customers

WHERE customer_id=in_customer_id;

Declarando um cursor

Existem 3 declarações para executar operações com cursores:

OPEN – Inicializa o resultset para o cursor;

FETCH – Recebe a próxima linha do cursor e move o ponteiro do cursor para a linha seguinte.

CLOSE – Desativa o cursor e libera a memória associada com aquele cursor.

Devemos fechar um cursor toda vez que terminarmos o FETCH.

...

OPEN cursor1;

cursor_loop:LOOP

FETCH cursor1 INTO l_customer_name,l_contact_surname,l_contact_firstname;

END LOOP cursor_loop;

CLOSE cursor1;

...

...

OPEN cursor1;

cursor_loop:LOOP

FETCH cursor1 INTO l_customer_name,l_contact_surname,l_contact_firstname;

END LOOP cursor_loop;

CLOSE cursor1;

…

E o que acontece se tentarmos pegar um dado além dos que contém no cursor?

ERROR 1329 (02000): No data to FETCH

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

SET l_last_row_fetched=0;

OPEN cursor1;

cursor_loop:LOOP

FETCH cursor1 INTO l_customer_name,l_contact_surname,l_contact_firstname;

IF l_last_row_fetched=1 THEN

LEAVE cursor_loop;

END IF;

/*Do something with the row fetched*/

END LOOP cursor_loop;

CLOSE cursor1;

SET l_last_row_fetched=0;

Tipos de Loop de um cursor

OPEN dept_csr;

dept_loop1:LOOP

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments=1 THEN

LEAVE dept_loop1;

END IF;

SET l_department_count=l_department_count+1;

END LOOP;

CLOSE dept_csr;

SET no_more_departments=0;

----

DECLARE dept_csr CURSOR FOR SELECT department_id,department_name, location FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

REPEAT

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

UNTIL no_more_departments

END REPEAT;

CLOSE dept_csr;

SET no_more_departments=0;

-----

DECLARE dept_csr CURSOR FOR SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

dept_loop:REPEAT

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments THEN

LEAVE dept_loop;

END IF;

SET l_department_count=l_department_count+1;

UNTIL no_more_departments

END REPEAT dept_loop;

CLOSE dept_csr;

SET no_more_departments=0;

----

4.9

Condições de Erro

4.10

4.11

4.12

4.13