Edvar Bergman Araujo

Banco de Dados II

Programação Procedural emBanco de Dados

O que são Stored Procedures?

• Stored procedures são programas armazenados no banco de dados

• São trechos de códigos que realizam tarefas específicas

• Chamadas como comandos• Podem receber parâmetros e retornar valores• SQL e linguagens específicas• Possibilidade de executar comandos DML como

SELECT, INSERT, UPDATE e DELETE• Suporte a variáveis, comentários, instruções

declarativas, testes condicionais, desvios e laços.

• Cada stored procedure é um módulo independente de código que pode ser executado a partir de:– uma outra procedure;– uma Trigger;– de uma aplicação cliente;– como parte de uma instrução SELECT, retornando

valores como se o procedimento fosse uma subconsulta;

• Eles permitem que se manipule diretamente os dados em banco, sem qualquer intervenção do cliente e sem impor tráfego sobre a rede.

O que são Stored Procedures?

Objetivos de utilização

• Melhorar o desempenho das aplicações• Reduzir o volume de dados trafegados na

rede• Melhorar a manutenção do software• Melhorar a segurança no acesso aos dados

Vantagens

• Stored procedures podem executar operações muito mais complexas que uma simples query.

• Se mais de uma aplicação irá acessar o banco de dados, as stored procedures podem também economizar tempo de manutenção e desenvolvimento já que qualquer aplicação poderá chamá-la.

• Alterar a stored procedure sem ter que alterar ou mesmo recompilar cada aplicação cliente.

• As alterações tornam-se imediatamente disponíveis para todos os aplicativos clientes que o utilizam.

Vantagens

• Segurança - Stored procedures tem uma grande importância na segurança do banco de dados uma vez que elas podem acessar tabelas que o usuário não tem o direito de fazê-lo.

• Clientes magros: como a regra de negócio está no servidor, aplicativos clientes podem ser menores, exigindo menos recursos do sistema (disco, memória e CPU) e consequentemente hardware de menor custo;

Quando utilizar

• Se a operação pode ser feita no servidor, sem a necessidade de obter informações do usuário enquanto a operação estiver sendo processada.

• Se a operação requer o processamento de um grande número de linhas, que implicaria em custos em termos de tráfego na rede para enviá-las através da rede para a aplicação cliente.

Quando utilizar

• Se a operação é uma que deve ser feita periodicamente ou frequentemente.– tais como um fechamento de fim de mês ou uma

operação de arquivamento.

• Se a operação é efetuada por vários módulos ou processos diferentes dentro de sua aplicação ou por aplicações diferentes.

Linguagens

• Específicas de cada SGBD– Oracle: PL/SQL– PostgreSQL: PL/PgSQL– SQL Server: Transact-SQL– DB2: SQL PL– MySQL: PL/MySQL

Desvantagens

• Dependência do SGBD• Falta de Padrão

Estrutura de um Bloco PL/SQL

DECLARE (opcional)– Variáveis, cursores, exceções definidas pelo

usuário

BEGIN (obrigatório)– Comandos SQL– Comandos PL/SQL

EXCEPTION (opcional)– Ações para executar quando ocorrer um erro

END; (obrigatório)

Tipos de Blocos

[DECLARE]

BEGIN-- comandos

[EXCEPTION]

END;

PROCEDURE nameISBEGIN

-- comandos

[EXCEPTION]

END;

FUNCTION nameRETURN datatypeISBEGIN

-- comandosRETURN value;

[EXCEPTION]

END;

Anônimo Procedure Função

Variáveis

• Declaradas e inicializadas na seção de Declaração

• Podem ter o conteúdo alterado no corpo da Procedure

• Linguagem Tipada: quando cria-se uma variável, define-se o seu tipo especificando o formato de armazenamento, restrições e faixa de valores válidos.

• Tipos de dados escalares (os mesmos usados para definir as colunas das tabelas do Oracle, com suporte a boolean)

• Tipos de dados compostos (registros)

Declaração de Variáveis

Sintaxe:identificador [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expressão];Exemplos:

vDataNascDATE;vCodDepto NUMBER(2) NOT NULL := 10;vCidade VARCHAR2(30) := 'Novo Hamburgo';cComissao CONSTANT NUMBER := 1400;

Atribuindo Valores para Variáveis

Sintaxe:identificador := expressão ;

Exemplos:vCodDepto := 50;vCidade := 'Porto Alegre';vDataNasc := TO_DATE('01/05/2013', 'DD/MM/YYYY');

Atributo %TYPE

• Utilizado para declarar uma variável de acordo com:– uma coluna do banco de dados– outra variável previamente declarada

• A variável criada terá o mesmo tipo e precisão da coluna da tabela ou variável base.

Exemplo:vNroConta conta.NRO_CONTA%TYPE;

Comentando o Código

• Comente o código para documentar cada fase e auxiliar na depuração. O comentário pode ser:– uma única linha, usando dois hifens (--)– várias linhas, usando os delimitadores /* e */

• Exemplos: -- Incrementar a variável vContadorvContador := vContador + 1; -- pode ser utilizado no final

/*Bloco de comentário.Inicia por /* e termina por */

*/

Estrutura de uma Procedure - Parâmetros

CREATE PROCEDURE Nome_da_Procedure( nome_parametro1 tipo_parametro1, nome_parametro2 tipo_parametro2,

...nome_parametroN tipo_parametroN)

IS<declaração de variáveis e cursores>

BEGIN

<comandos da procedure>

END;

Estrutura de uma FUNCTION

CREATE FUNCTION Nome_da_Funcao(nome_parametro tipo_parametro)

RETURN <tipo de dados que a função retorna>

IS<declaração de variáveis locais>

BEGIN<comandos da função>

RETURN value;

END;

Comandos SELECT em PL/SQL

• Utilizado para recuperar dados do banco de dados

Sintaxe:SELECT lista_de_colunas INTO {variavel1[, variavel2, ...] | record_nameFROM tabelaWHERE condição;

Comandos SELECT em PL/SQL - Exemplo

DECLARE vSaldo NUMBER(15,2); vLimite conta.LIMITE%TYPE;BEGIN SELECT saldo, limite INTO vSaldo, vLimite FROM conta WHERE nro_conta = 30001; ...END;

Select into serve somente qdo retornar f1 linha

Quando for necessário trabalhar com mais de 1 linha, é necessário o uso de Cursores

Comandos SELECT em PL/SQL

• Cuidado:– O comando SELECT deve retornar somente uma

linha.– Mais de uma linha ou nenhuma linha causa um

erro.– Exceções geradas:

• NO_DATA_FOUND (nenhuma linha)• TOO_MANY_ROWS (mais de uma linha)

COMO EVITAR?BEGIN ... BEGIN SELECT saldo, limite INTO vSaldo, vLimite FROM conta WHERE nro_conta = 30001; EXCEPTION WHEN NO_DATA_FOUND THEN vSaldo := 0; vLimite := 0; END; ...END;

Manipulando Dados em PL/SQL

• É possível executar comandos DML (INSERT, UPDATE e DELETE) da mesma forma como seriam utilizados fora de um bloco PL/SQL

Comando IF

Sintaxe IF simples:

IF condição THEN comandos;END IF;

Sintaxe IF com ELSE:

IF condição THEN comandos;ELSE comandos;END IF;

Controle de Repetições

• O PL/SQL possui algumas estruturas para controles de repetição:– O LOOP básico fornece ações repetitivas sem

condições globais;– O FOR loop fornece controle de repetições de

ações baseado em um contador– O WHILE loop fornece controle de repetições de

ações baseado em uma condição– O comando EXIT encerra um loop– O comando CONTINUE interrompe a iteração atual

e vai para a próxima iteração do laço.

LOOP básico

Sintaxe:

LOOP --delimitador comandos; --comandos ... --comandos EXIT [WHEN condição]; --saída do loopEND LOOP; --delimitador de fim

FOR LOOP

Sintaxe:

FOR contador IN vlr_inicial..valor_final LOOP comando 1; comando 2; ... comando N;END LOOP;

Obs.: pode-se utilizar a cláusula REVERSE, após o IN, para que a variável de controle varie de um valor maior para um menor.

WHILE LOOP

Sintaxe:

WHILE condicao LOOP comando 1; comando 2; ... comando N;END LOOP;

Obs.: A condição é avaliada no início de cada repetição. O loop se encerra quando a condição for FALSE.

Exemplo

Desenvolver uma função que retorne o número de cartões de crédito de uma conta corrente.

Obs.: exemplo simples, apenas com fins didáticos, para demonstrar o funcionamento.

CREATE FUNCTION funcObtemNumeroCartoes (pNroConta conta.NRO_CONTA%TYPE)RETURN NUMBER IS vCont NUMBER;BEGIN SELECT COUNT(*) INTO vCont FROM CARTAO WHERE NRO_CONTA = pNroConta;

RETURN vCont;END;

Como testar?

SELECT funcObtemNumeroCartoes(30002) AS NRO_CARTOESFROM DUAL

OU

SELECT C.*, funcObtemNumeroCartoes(C.NRO_CONTA) AS NRO_CARTOESFROM CONTA C

Exercícios

1. Desenvolver uma função que retorne o valor total aplicado atualizado de uma conta corrente.

testar: SELECT C.NRO_CONTA, funcValorTotalAplicado(C.NRO_CONTA) AS VLR_APLICADO FROM CONTA C;

2. Desenvolver uma função que retorne a média de gastos com cartão de crédito por um determinado correntista em um período.

testar: SELECT C.nro_conta, funcValorMediaCartoes2(C.NRO_CONTA, to_date('01/01/2015','dd/mm/yyyy'),to_date('31/03/2015','dd/mm/yyyy')) AS vlrmediacartoes FROM CONTA C;

Atributo %ROWTYPE

• Utilizado para declarar uma variável• A variável poderá armazenar um

conjunto de colunas• O número, tipo e nome das colunas é

obtido a partir da tabela ou view de origem

Exemplo:vConta conta%ROWTYPE;

Cursores

• Oracle utiliza áreas de memória para executar comandos SQL e para armazenar informações

• Cursores PL/SQL são usados para “nomear” uma destas áreas e acessar as informações armazenadas

Cursores

Result Set

Registro AtualCursor

7369 Jonas DBA

7566 Marta Gerente

7788 Cristina Analista

7876 Carolina Testador

7902 Marcos Programador

Cursores

• Cria um cursor

DECLAREDECLARE

• Executa o SQL e obtém o Resul Set

OPENOPEN

• Carrega a linha atual para vairáveis

FETCHFETCH

• Verifica se ainda existem linhas, executando um novo FETCH

EMPTY?

Não

• Libera a área de memória

CLOSECLOSESim

Cursores

Sintaxe:CURSOR nome_do_cursor IS comando_select;

Exemplo:CURSOR cContas30 IS SELECT NRO_CONTA, NOME, SALDO, LIMITE FROM CONTA WHERE COD_AGENCIA = 30;

vContas30 cContas30%ROWTYPE;

Cursores

BEGIN ... OPEN cContas30; LOOP FETCH cContas30 INTO vContas30; EXIT WHEN cContas30%NOTFOUND; ... -- Processa os dados recuperados ... END LOOP; CLOSE cContas30; ...END;

Cursores

• Existem 4 atributos para obter informações de status de um cursor:

1. %ISOPEN – retorna TRUE se o cursor estiver aberto

2. %NOTFOUND – retorna TRUE se o FETCH mais recente não retornou uma linha

3. %FOUND – retorna TRUE se o FETCH mais recente retornou uma linha

4. %ROWCOUNT – retorna o número total de linhas recuperadas até o momento

Cursores – FOR loop

• O Oracle possui um tipo especial de laço FOR para ser utilizado com cursores.

• A principal vantagem é a facilidade de utilização, pois:O cursor é aberto automaticamenteOs registros são recuperados um a um, a

cada repetição do laçoO cursor é fechado automaticamenteNão é necessário declarar a variável

Cursores – FOR loop

Exemplo:...FOR vCon30 IN cContas30 LOOP -- -- OPEN e FETCH implícitos -- os dados são carregados na variável vCon30 ...END LOOP; -- CLOSE implícito é feito...

Exercício

3. Desenvolver uma stored procedure para calcular as faturas de todos os cartões com vencimento em um determinado dia (obtido a partir da data recebida como parâmetro).

– Calcular o valor total e gravar um registro na tabela FATURA

– Atualizar a tabela de movimentos com o código da fatura que gerou o desconto

– Sugere-se testar usando a data: 05/03/2015

Dica: para obter o dia de uma data, usar:extract(day from DATA_QUALQUER)Considerando a data acima, o retorno será 5.

Pegar o dia de vencto da tabela CARTAO (col DIA_VENCTO). Ler a tabela MOVTO_CARTAO, gerar fatura p\ todos os cartões com vencto no dia e cujo campo COD_FATURA esteja null. O código da fatura é a ultima fatura + 1

Como testar?

Em uma janela do SQL Developer

BEGIN prcGeraFatura(to_date('05/03/2015', 'dd/mm/yyyy'));END;

DEBUG no SQL Developer

• O SQL Developer possui recurso para depurar Stored Procedures.

• Demonstração prática de DEBUG no SQL Developer

Trabalho

Desenvolver o trabalho conforme especificação disponível no documento “Trabalho de Stored Procedures”.