dbpro - DATABASE, Notas de estudo de Informática

Baixe dbpro - DATABASE e outras Notas de estudo em PDF para Informática, somente na Docsity!

PROGRAMAÇÃO

PARA

BANCO DE DADOS

PL/SQL

Prof. Marcos Alexandruk

SUMÁRIO

1. Conceitos de programação para banco de dados: Introdução ao PL/SQL

2. Declarações

3. Tipos de dados

4. Constantes e variáveis

5. Comandos SQL dentro de um bloco PL/SQL

6. Instruções IF-THEN-ELSE e CASE

7. Instruções LOOP, FOR e WHILE

8. Tratamento de exceções

9. Cursores explícitos e implícitos

10. Procedures

11. Functions

12. Triggers

13. Packages

2. DECLARAÇÕES

Na área DECLARE podemos declarar:

• constantes
• variáveis
• cursores
• estruturas
• tabelas

3. TIPOS DE DADOS

3.1. Os tipos de dados simples que podem ser utilizados em declarações PL/SQL são:

TIPOS DESCRIÇÃO

CHAR Alfanumérico, tamanho fixo, limite: 2000 caracteres CHARACTER Idêntico ao CHAR, mantém compatibilidade com outras versões SQL VARCHAR2 Alfanumérico, tamanho variável, limite: 4000 caracteres

VARCHAR E STRING Idêntico ao VARCHAR2, mantém compatibilidade com outras versões SQL CLOB (Character Long Object) Alfanumérico, tamanho variável, limite 4 Gb

LONG Alfanumérico, limites: 2 GB, apenas um por tabela ROWID Armazena os valores dos ROWIDs das linhas das tabelas

BLOB (Binary Long Object)

Binário, tamanho variável, limite: 4 Gb

BFILE (Binary File)

Armazena uma referência a um arquivo externo (que deverá localizar-se na mesma máquina do banco de dados, não permite referência remota) RAW Hexadecimais, tamanho variável, limite: 2 Kb

LONG ROW Hexadecimais, tamanho variável, limite: 2 Gb

NUMBER

Numérico, limite: 38 dígitos Exemplo: NUMBER (10,2) armazena 10 números (8 inteiros e 2 decimais)

SUBTIPOS:

• DECIMAL
• DEC
• DOUBLEPRECISION
• INTEGER
• INT
• NUMERIC
• REAL
• SMALLINT
• FLOAT
• PLS_INTEGER BINARY_INTEGER Numérico, positivos e negativos, limites: -2147483647 e 2147483647

SUBTIPOS:

• NATURAL (limites: 0 e 2147483647)
• NATURALN (limites: 0 e 2147483647, não aceita valores nulos)
• POSITIVE (limites: 1 e 2147483647)
• POSITIVEN (limites: 0 e 2147483647, não aceita valores nulos)

DATE Data e hora (formato padrão: DD-MMM-YY TIMESTAMP Data e hora (com milésimos de segundo) BOOLEAN Armazena os valores: TRUE, FALSE ou NULL

3.2. Tipos compostos:

• RECORD
• TABLE

3.3. Tipos referenciados:

• REF CURSOR

4. CONSTANTES E VARIÁVEIS

4.1. CONSTANTES

Para declarações de constantes, a palavra CONSTANT deve aparecer antes do tipo de

dado e a seguir utiliza-se o operador := para atribuir-lhe um valor.

Exemplo:

DECLARE

pi CONSTANT NUMBER(9,7) := 3.1415927; ... BEGIN ... END; /

4.2. VARIÁVEIS

As variáveis são inicializadas de maneira similar às constantes: declarando-se o tipo e

atribuindo-lhe um valor. Variáveis não inicializadas explicitamente recebem o valor

NULL. Não é necessário inicializar uma variável com valor NULL. Pode-se aplicar a

restrição NOT NULL a uma variável. Neste caso ela deverá ser inicializada.

Exemplos:

v1 NUMBER(4):= 1; v2 NUMBER(4):= DEFAULT 1; v3 NUMBER(4) NOT NULL:= 1;

Podemos atribuir valores às variáveis de duas maneiras:

• Utilizando o operador de atribuição:

total := quant * valor;

• Utilizando um comando SELECT com a cláusula INTO:

SELECT ra_aluno, nome_aluno INTO ra, aluno FROM aluno;

4.2.1. HERANÇA DE TIPO E TAMANHO

As constantes e variáveis podem herdar o tipo de outras variáveis, de colunas ou até

da linha inteira de uma tabela. Desta forma, diminuem-se as manutenções oriundas

nas alterações realizadas nas colunas de tabelas (ex: tamanho da coluna).

• Herdando o tipo de uma variável previamente declarada:

nome_da_variavel_2 nome_da_variavel_1%Type;

• Herdando o tipo de uma coluna de uma tabela:

nome_da_variavel nome_da_tabela.nome_da_coluna%Type;

• Herdando o tipo de uma linha inteira de uma tabela:

nome_da_variavel nome_da_tabela%Rowtype;

4.2.2. ESCOPO DE VARIÁVEIS

Variáveis definidas dentro de um bloco serão locais para esse bloco e globais para os

sub-blocos. Não serão reconhecidas em outros blocos isolados.

5. Alterar o nome do aluno cujo RA=5 para 'ERNESTO'.

BEGIN

UPDATE ALUNO

SET NOME='ERNESTO'

WHERE RA=5;

END;

6. Excluir da tabela o aluno cujo RA=5.

BEGIN

DELETE FROM ALUNO

WHERE RA=5;

END;

6. INSTRUÇÕES IF-THEN-ELSE E CASE

6.1. IF-THEN-ELSE

Executa um conjunto de ações de acordo com uma ou mais condições.

IF condição_ THEN relação_de_comandos_ [ELSIF condição_ THEN relação_de_comandos_2] [ELSE relação_de_comandos_3] END IF;

Exemplo 1:

DECLARE

V_1 NUMBER(2) := 4;

V_2 VARCHAR2(5);

BEGIN

IF MOD(V_1,2) = 0

THEN V_2 := 'PAR';

ELSE V_2 := 'IMPAR';

END IF;

DBMS_OUTPUT.PUT_LINE ('O número é: ' || V_2); END; /

NOTA: MOD(V1,2) divide o valor de V_1 por 2 e retorna o resto da divisão.

Exemplo 2:

CREATE TABLE ALUNO (

RA NUMBER(9),

NOTA NUMBER(3,1));

INSERT INTO ALUNO VALUES (1,4);

DECLARE

V_RA ALUNO.RA%TYPE := 1;

V_NOTA ALUNO.NOTA%TYPE;

V_CONCEITO VARCHAR2(12);

BEGIN

SELECT NOTA

INTO V_NOTA

FROM ALUNO

WHERE RA = V_RA;

IF V_NOTA <= 5

THEN V_CONCEITO := 'REGULAR';

ELSIF V_NOTA < 7

THEN V_CONCEITO := 'BOM';

ELSE V_CONCEITO := 'EXCELENTE';

END IF;

DBMS_OUTPUT.PUT_LINE ('Conceito: ' || V_CONCEITO); END; /

6.2. CASE

Retorna determinado resultado de acordo com o valor da variável de comparação.

[variável :=] CASE WHEN expressão_1 THEN declaração_ WHEN expressão_2 THEN declaração_ ... ELSE declaração_n END;

EXERCÍCIOS 01:

1. Criar uma tabela conforme segue:

CREATE TABLE ALUNO (

RA NUMBER(9),

DISCIPLINA VARCHAR2(30),

MEDIA NUMBER(3,1),

CARGA_HORA NUMBER(2),

FALTAS NUMBER(2),

RESULTADO VARCHAR2(10));

Inserir uma linha deixando a coluna RESULTADO em branco.

INSERT INTO ALUNO VALUES (1,'DISC 1',7.5,80,20,'');

Criar um bloco PL/SQL para preencher a coluna resultado conforme o seguinte:

Se o aluno obteve média igual ou maior que 7.0 e suas faltas não ultrapassarem 25%

da carga horária da disciplina o resultado será: APROVADO.

Se o aluno obteve média inferior a 7.0 e suas faltas não ultrapassarem 25% da carga

horária da disciplina o resultado será: EXAME.

Para demais casos o resultado será: REPROVADO.

2. Criar uma tabela, conforme segue:

CREATE TABLE PRODUTO (

CODIGO NUMBER(2),

DESCRICAO VARCHAR2(20));

Inserir sete produtos diferentes na tabela acima.

Criar um bloco PL/SQL para apresentar um produto diferente para cada dia da

semana.

Nota: A mensagem acima deverá ser exibida dinamicamente, conforme a data do

sistema (SYSDATE).

Apresentar a seguinte mensagem:

Hoje é TERÇA-FEIRA e o produto em oferta é PRODUTO 3.

7. INSTRUÇÕES LOOP, FOR E WHILE

FOR

Repete um bloco de comando n vezes, ou seja, até que a variável contadora atinja o

seu valor final.

A variável contadora não deve ser declarada na seção DECLARE e deixará de existir

após a execução do comando END LOOP.

FOR v_contador IN valor_inicial..valor_final LOOP bloco_de_comandos END LOOP;

Exemplo 1:

DECLARE

V_AUX NUMBER(2) := 0;

BEGIN

FOR V_CONTADOR IN 1..

LOOP

V_AUX := V_AUX +1;

DBMS_OUTPUT.PUT_LINE (V_AUX);

END LOOP;

END;

Exemplo 2:

DECLARE

V_RA_INICIAL ALUNO.RA%TYPE := 1;

V_RA_FINAL V_RA_INICIAL%TYPE;

V_AUX V_RA_INICIAL%TYPE := 0;

BEGIN

SELECT COUNT(RA)

INTO V_RA_FINAL

FROM ALUNO;

FOR V_CONTADOR IN V_RA_INICIAL..V_RA_FINAL

LOOP

V_AUX := V_AUX +1;

DBMS_OUTPUT.PUT_LINE ('Total de alunos: ' || V_AUX); END LOOP; END; /

WHILE

Repete um bloco de comandos enquanto a condição que segue o comando WHILE for

verdadeira.

Exemplo 1:

DECLARE

V_AUX NUMBER(2) := 0;

BEGIN

WHILE V_AUX < 10

LOOP

V_AUX := V_AUX +1;

DBMS_OUTPUT.PUT_LINE (V_AUX);

END LOOP;

END;

Exemplo:

DECLARE

V_AUX NUMBER(2) := 0;

BEGIN

LOOP

V_AUX := V_AUX +1;

DBMS_OUTPUT.PUT_LINE (V_AUX);

IF V_AUX = 10

THEN EXIT;

END IF;

END LOOP;

END;

EXERCÍCIO

Crie uma tabela chamada CIRCULO com as seguintes colunas:

RAIO NUMBER(2),

AREA NUMBER(8,2)

CREATE TABLE CIRCULO (

RAIO NUMBER(2),

AREA NUMBER(8,2));

Desenvolva um programa em PL/SQL para inserir os raios com valores 1 a 10 e as

respectivas áreas na tabela acima.

SOLUÇÃO 1: WHILE

DECLARE

PI CONSTANT NUMBER(9,7) := 3.1415927;

RAIO NUMBER(2);

AREA NUMBER(8,2);

BEGIN

RAIO := 1;

WHILE RAIO <=

LOOP

AREA := PI*POWER(RAIO,2);

INSERT INTO CIRCULO VALUES (RAIO,AREA);

RAIO := RAIO+1;

END LOOP;

END;

SOLUÇÃO 2: FOR

DECLARE

PI CONSTANT NUMBER(9,7) := 3.1415927;

RAIO NUMBER(2) := 1;

AREA NUMBER(8,2);

BEGIN

FOR CONTADOR IN 1..

LOOP

AREA := PI*POWER(RAIO,2);

INSERT INTO CIRCULO VALUES (RAIO,AREA);

RAIO := RAIO +1;

END LOOP;

END;

LABELS

Utilizados para nomear blocos ou sub-blocos.

Devem localizar-se antes do início do bloco e preceder pelo menos um comando.

Se não houver necessidade de nenhum comando após o label, deve-se utilizar o

comando NULL.

<<NOME_DO_LABEL>>

DECLARE

BEGIN

RELAÇÃO_DE_COMANDOS

<<NOME_DO_LABEL>>

RELAÇÃO DE COMANDOS

END;

Um label também pode ser aplicado a um comando de repetição LOOP.

<>

LOOP

LOOP

-- SAIR DOS DOIS LOOPS

EXIT PRINCIPAL WHEN ...

END LOOP;

END LOOP PRINCIPAL;

GOTO

Utilizado para desviar um fluxo de um bloco PL/SQL para determinado label.

Não pode ser utilizado para:

• Desviar o fluxo para dentro de um IF;
• Desviar o fluxo de um IF para outro;
• Desviar o fluxo para dentro de um sub-bloco;
• Desviar o fluxo para um bloco externo ao bloco corrente;
• Desviar o fluxo de uma EXCEPTION para o bloco corrente e vice-versa.

GOTO nome_do_label

Exemplo 1:

<>

DECLARE

V_NOME ALUNO.NOME%TYPE;

BEGIN

SELECT COUNT(RA)

INTO V_CONTA

FROM ALUNO;

IF V_CONTA = 10

GOTO FIM;

ELSE INSERT INTO ALUNO VALUES (20,'SILVA');

END IF;

<>

DBMS_OUTPUT.PUT_LINE('Fim do programa'); END; /

8. TRATAMENTO DE EXCEÇÕES

Exceções são erros ou imprevistos que podem ocorrer durante a execução de um

bloco PL/SQL.

Nesses casos, o gerenciador de banco de dados aborta a execução e procura uma

área de exceções.

As exceções podem ser:

• Predefinidas
• Definidas pelo usuário

PREDEFINIDAS

Disparadas automaticamente quando, no bloco PL/SQL, uma regra Oracle for violada.

Podem ser identificadas por um nome e um número.

EXCEPTION

WHEN nome_da_exceção THEN relação_de_comandos; WHEN nome_da_exceção THEN relação_de_comandos;

ERRO NOME DESCRIÇÃO

ORA-00001 DUP_VAL_ON_INDEX

Tentativa de armazenar valor duplicado em uma coluna que possui chave primária ou única.

ORA-01012 NOT_LOGGED_ON Tentativa acessar o banco de dados sem estar conectado a ele.

ORA-01403 NO_DATA_FOUND

Ocorre quando um comando SELECT ... INTO não retorna nenhuma linha.

ORA-01422 TOO_MANY_ROWS

Ocorre quando um comando SELECT ... INTO retorna mais de uma linha.

ORA-01476 ZERO_DIVIDE Tentativa de dividir qualquer número por zero.

Exemplo:

DECLARE

V_RA ALUNO.RA%TYPE;

V_NOME ALUNO.NOME%TYPE;

BEGIN

SELECT RA, NOME

INTO V_RA, V_NOME

FROM ALUNO

WHERE RA=30;

DBMS_OUTPUT.PUT_LINE(V_RA ||' - '|| V_NOME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('Não há nenhum aluno com este RA'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('Há mais de um aluno com este(s) RA(s)'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Erro desconhecido'); END;

EXERCÍCIO

Elabore um programa em PL/SQL que faça o seguinte tratamento de exceção:

• Informe tentativa de inserir valor duplicado numa coluna que é chave primária.

DECLARE

BEGIN

INSERT INTO ALUNO VALUES (1,'ANTONIO');

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE ('Já existe um aluno com este RA'); END; /

DEFINIDAS PELO USUÁRIO

Além dos erros tratados automaticamente pelo Oracle, regras de negócio específicas

podem ser tratadas.

As exceções definidas pelo usuário devem ser declaradas e chamadas explicitamente

pelo comando RAISE.

DECLARE

nome_da_exceção EXCEPTION; BEGIN ... IF ... THEN RAISE nome_da_exceção; END IF; ... EXCEPTION WHEN nome_da_exceção THEN relação_de_comandos END; /

Exemplo 1:

DECLARE

V_RA ALUNO.RA%TYPE;

V_NOTA ALUNO.NOTA%TYPE;

V_CONTA NUMBER(2);

CONTA_ALUNO EXCEPTION;

BEGIN

SELECT COUNT(RA)

INTO V_CONTA

FROM ALUNO;

IF V_CONTA = 10 THEN

RAISE CONTA_ALUNO;

ELSE INSERT INTO ALUNO VALUES (20,'SILVA');

END IF;

EXCEPTION

WHEN CONTA_ALUNO THEN

DBMS_OUTPUT.PUT_LINE('Não foi possível incluir: turma cheia'); END; /

Exemplo 2:

DECLARE

V_RA ALUNO.RA%TYPE := &RA;

V_NOME ALUNO.NOMEA%TYPE := '&NOME';

BEGIN

INSERT INTO ALUNO VALUES (V_RA,V_NOME);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('Este RA já foi utilizado'); END; /

Exemplo:

DECLARE

CURSOR c_cliente IS SELECT codigo, nome FROM cliente; v_cliente c_cliente%rowtype; BEGIN OPEN c_cliente; LOOP FETCH c_cliente INTO v_cliente; EXIT when c_cliente%notfound; DBMS_OUTPUT.PUT_LINE('Cliente: '||v_cliente.nome); END LOOP; CLOSE c_cliente; END;

9.1.2. UTILIZANDO: FOR

O comando FOR ... LOOP, quando aplicado a um cursor, executa automaticamente as

seguintes ações:

• Cria a variável do tipo registro que receberá os dados;
• Abre (OPEN) o cursor;
• Copia as linhas uma a uma (FETCH), a cada interação do comando;
• Controla o final do cursor;
• Fecha (CLOSE) o cursor.

NOTA: Caso seja necessário sair do loop do comando FOR durante sua execução, o

cursor deverá ser fechado explicitamente com o comando CLOSE.

Exemplo:

DECLARE

CURSOR c_cliente IS SELECT codigo, nome FROM cliente; BEGIN FOR v_cliente IN c_cliente LOOP DBMS_OUTPUT.PUT_LINE('Cliente: '|| v_cliente.nome); END LOOP; END;

NOTA:

As variáveis devem ser visíveis no ponto da declaração do cursor:

DECLARE

V_RA ALUNO.RA%TYPE;

C_ALUNO IS

SELECT * FROM ALUNO

WHERE RA = V_RA;

CORRETO

DECLARE

C_ALUNO IS

SELECT * FROM ALUNO

WHERE RA = V_RA;

V_RA ALUNO.RA%TYPE;

ERRADO: V_RA não foi declarado antes de ser referenciado.

EXERCÍCIOS:

1. Criar a tabela PRODUTO:

CREATE TABLE PRODUTO (

CODIGO NUMBER(4),

VALOR NUMBER(7,2));

Inserir os valores:

INSERT INTO PRODUTO VALUES (1000,300);

INSERT INTO PRODUTO VALUES (1001,500);

INSERT INTO PRODUTO VALUES (2000,300);

INSERT INTO PRODUTO VALUES (2001,500);

Criar um bloco PL/SQL para atualizar os preços conforme segue:

• Produtos com CODIGO inferior a 2000: Acrescentar 10% ao VALOR atual.
• Produtos com CODIGO igual ou superior a 2000: Acrescentar 20% ao VALOR atual.

2. Criar a tabela ALUNO:

Observação: Similar ao exercício 1 da página 10. Porém, utiliza cursor.

CREATE TABLE ALUNO (

RA NUMBER(9),

DISCIPLINA VARCHAR2(30),

MEDIA NUMBER(3,1),

CARGA_HORA NUMBER(2),

FALTAS NUMBER(2),

RESULTADO VARCHAR2(10));

Inserir uma linha deixando a coluna RESULTADO em branco.

INSERT INTO ALUNO VALUES (1,'DISC 1',7.5,80,20,'');

INSERT INTO ALUNO VALUES (2,'DISC 1',5.5,80,20,'');

INSERT INTO ALUNO VALUES (3,'DISC 1',7.5,80,40,'');

Criar um bloco PL/SQL para preencher a coluna resultado conforme o seguinte:

• Se o aluno obteve média igual ou maior que 7.0 e suas faltas não ultrapassarem

25% da carga horária da disciplina o resultado será: APROVADO.

• Se o aluno obteve média inferior a 7.0 e suas faltas não ultrapassarem 25% da

carga horária da disciplina o resultado será: EXAME.

• Para demais casos o resultado será: REPROVADO.