TECNOLOGIA DE BASES DE DADOS Linguagem PL/SQL e Triggers

(1)

T

ECNOLOGIA DE

B

ASES DE

D

ADOS

Linguagem PL/SQL e Triggers

António Manuel Silva Ferreira

U

NIVERSIDADE DE

L

ISBOA

–

F

ACULDADE DE

C

IÊNCIAS

D

EPARTAMENTO DE

I

NFORMÁTICA

(2)

• Linguagem PL/SQL

–

Tipos de dados e declaração de variáveis

–

Execução condicional e ciclos

–

Tratamento de erros baseado em excepções

–

Desenvolvimento de código modular

–

Integração com a linguagem SQL

–

Funções predeﬁnidas

–

Vantagens e desvantagens

• Triggers

–

Conceito e uOlização de triggers

–

Estrutura de um trigger

–

Tipos de triggers

–

Exemplo para autorização especial

(3)

Síntese da Linguagem PL/SQL – 1

• PL/SQL = Procedural Language extensions to SQL

• Grande gama de Opos de dados

–

Predeﬁnidos ou deﬁnidos pelo programador

• MúlOplas alternaOvas para

controlar o ﬂuxo de execução

• Tratamento erros baseado em excepções

–

Excepções predeﬁnidas ou

declaradas pelo programador

• Desenvolvimento de código modular

• Integração com a linguagem SQL

–

Comandos SQL no código PL/SQL

–

Chamada funções PL/SQL em comandos SQL

• Biblioteca de funções predeﬁnidas

data_entrega DATE; vencimento NUMBER;

nome VARCHAR2(80);

IF (c1) THEN ... END IF;

WHILE (c2) LOOP ... END LOOP;

EXCEPTION

WHEN ZERO_DIVIDE THEN ...

FUNCTION dobro(n IN NUMBER) RETURN NUMBER;

UPDATE empregado

SET vencimento :=

dobro(vencimento);

(4)

Síntese da Linguagem PL/SQL – 2

CREATE OR REPLACE PACKAGE BODY funções_simples IS FUNCTION factorial(n IN NUMBER)

RETURN NUMBER IS resultado NUMBER := 1; BEGIN IF (n < 0) THEN RAISE VALUE_ERROR; END IF; WHILE (n > 1) LOOP resultado := resultado * n; n := n -‐ 1; END LOOP; RETURN resultado; EXCEPTION

WHEN VALUE_ERROR THEN RETURN 0;

END factorial;

(5)

Tipos de Dados e Declaração de Variáveis – 1

• Tipos de dados predeﬁnidos

nome VARCHAR2(255) := NULL; letra CHAR := ‘A’;

agora DATE := SYSDATE; PI CONSTANT NUMBER(6,5) := 3.14159;

• Tipos de dados deﬁnidos pelo programador

SUBTYPE tipo_data_contrato IS DATE; TYPE registo_cliente IS RECORD ( número NUMBER(5), nome VARCHAR2(80)); data_contrato tipo_data_contrato; cliente registo_cliente; cliente.número := 1502; cliente.nome := ‘Sofia’;

Declaração de

uma constante

Nota: Zpos de dados deﬁnidos

pelo programador podem ser

incompa[veis com o JDBC!

Algumas conversões são automáZcas:

NUMBER ↔ VARCHAR2 ou CHAR

(6)

• Tipos de dados ancorados em tabelas SQL

ou em atributos de tabelas

TYPE registo_emp1 IS RECORD ( número NUMBER(5),

nome VARCHAR2(80));

TYPE registo_emp2 IS RECORD ( número emp.número%TYPE,

nome emp.nome%TYPE); emp1 registo_emp1;

emp2 registo_emp2; emp3 emp%ROWTYPE;

• %TYPE e %ROWTYPE facilitam a manutenção do código

Tipos de Dados e Declaração de Variáveis – 2

-‐-‐ TABELA DE EXEMPLO. CREATE TABLE emp ( número NUMBER(5), nome VARCHAR2(80) );

Tipo de dados está dependente da

deﬁnição dos atributos da tabela emp

emp%ROWTYPE é um Zpo RECORD

que segue a deﬁnição da tabela emp

(7)

Execução Condicional e Ciclos

• IF … THEN … ELSIF … ELSE … END IF

IF (está_na_hora) THEN soa_alarme; END IF; IF (está_na_hora) THEN soa_alarme; ELSE está_atento; END IF; IF (hora > 20) THEN

cumprimento := ‘Boa noite’; ELSIF (hora > 12) THEN

cumprimento := ‘Boa tarde’; ELSE

cumprimento := ‘Bom dia’; END IF;

• LOOP … EXIT WHEN … END LOOP

x := 1; LOOP

EXIT WHEN (x > 2); x := x + 1;

END LOOP;

• WHILE … LOOP … END LOOP

x := 1

WHILE (x <= 2) LOOP x := x + 1;

END LOOP;

• FOR … IN … LOOP … END LOOP

FOR x IN 1 .. 2 LOOP NULL;

END LOOP;

Variável x

visível no

ciclo FOR

(8)

Tratamento de Erros Baseado em Excepções – 1

• Excepções interrompem o ﬂuxo normal de execução

• Execução transferida para bloco tratamento excepções

FUNCTION divisão(dividendo IN NUMBER, divisor IN NUMBER) RETURN NUMBER;

IS

resultado NUMBER := 0; BEGIN

resultado := dividendo / divisor; RETURN resultado;

EXCEPTION

WHEN ZERO_DIVIDE THEN ...

WHEN OTHERS THEN RAISE; END divisão;

Se divisor for zero, é gerada

a excepção ZERO_DIVIDE,

que faz o ﬂuxo de execução

saltar para o bloco de

tratamento de excepções

Bloco de tratamento

de excepções

(9)

• Excepções não tratadas são propagadas para o bloco de

código imediatamente mais abrangente

BEGIN BEGIN

IF (erro_grave) THEN RAISE excepção_a;

ELSIF (erro_muito_grave) THEN RAISE excepção_b;

END IF; EXCEPTION

WHEN excepção_a THEN NULL; END;

...

EXCEPTION

WHEN excepção_b THEN RAISE; END;

Tratamento de Erros Baseado em Excepções – 2

Forma de ignorar ou

anular uma excepção

Se uma excepção não for tratada em

algum bloco de código, então será

propagada para o terminal do uZlizador

e a execução do código termina

excepção_b é tratada no bloco

de código mais abrangente

Forma de propagar

(10)

Tratamento de Erros Baseado em Excepções – 3

• Excepções deﬁnidas pelo programador

PROCEDURE teste IS

BEGIN

IF (erro_grave) THEN

RAISE_APPLICATION_ERROR(-‐20001, ‘Erro grave!’); END IF;

...

END teste;

• Códigos −20999 a −20000 podem ser

usados livremente pelo programador

• Funções SQLCODE e SQLERRM

fornecem o código e a mensagem da

excepção mais recente

• Excepções

predeﬁnidas

CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE OTHERS

Código da excepção

(11)

Desenvolvimento de Código Modular – 1

• Blocos anónimos

–

Sem nome, sem parâmetros, sem Opo de retorno

–

Execução num ponto ﬁxo do código

–

Variáveis locais + comandos + tratamento de excepções

• Procedimentos

–

Bloco anónimo + nome + parâmetros

–

Podem ser invocados explicitamente no código

• Funções

–

Procedimentos + Opo de retorno

• Pacotes

(12)

Desenvolvimento de Código Modular – 2

• Procedimentos e funções

FUNCTION somaf(x IN NUMBER, y IN NUMBER) RETURN NUMBER; IS r NUMBER := 0; BEGIN r := x + y; RETURN r; END somaf;

PROCEDURE somap(x IN NUMBER, y IN NUMBER, r OUT NUMBER) IS

BEGIN

r := x + y; END somap;

-‐-‐ Os dois comandos seguintes são equivalentes. r := somaf(1, 1);

somap(1, 1, r);

No modo IN o valor

do parâmetro só

pode ser consultado

No modo OUT o valor do parâmetro só

pode ser alterado (mas não consultado)

O modo IN OUT permite

a consulta e alteração do

(13)

Desenvolvimento de Código Modular – 3

• Notações posicional e nomeada

DECLARE r NUMBER := 0; BEGIN r := somaf(1, 1); somap(1, 1, r); r := somaf(x => 1, y => 1); r := somaf(y => 1, x => 1); END; id := regista_cliente(nome => ‘Sofia’, telefone => 217217217,

morada => ‘Rua da Avenida, 1’);

Notação nomeada:

argumentos idenZﬁcados

pelo nome do parâmetro

Notação posicional:

posição do argumento

determina o parâmetro

Notação nomeada torna

o código mais extenso,

mas também mais legível

(14)

Desenvolvimento de Código Modular – 4

• Parâmetros com valor por omissão

FUNCTION dias_que_passaram(data1 IN DATE, data2 IN DATE := SYSDATE) RETURN NUMBER

BEGIN

RETURN (data2 -‐ data1); END dias_que_passaram;

DECLARE

dias NUMBER := 0; BEGIN

-‐-‐ O resultado é idêntico nos dois casos.

dias := dias_que_passaram(TO_DATE(‘28.09.99’, ‘DD.MM.YY’), SYSDATE); dias := dias_que_passaram(TO_DATE(‘28.09.99’, ‘DD.MM.YY’));

END;

Por omissão de argumento em

data2 é assumida a data actual

(fornecida pela função SYSDATE)

Comentários em PL/

SQL começam com -‐-‐

(15)

Desenvolvimento de Código Modular – 5

• Pacotes

–

Colecções de funções e procedimentos inter-‐relacionados

–

Reforçam conceitos de encapsulamento e de reuOlização

–

Construídos em duas etapas: especiﬁcação + codiﬁcação

• Especiﬁcação

–

Declarações de constantes e Opos de dados públicos

–

Especiﬁcações de procedimentos e funções públicas

–

Guardada em ﬁcheiro com extensão SPS (ex. ALUNO.SPS)

• Codiﬁcação

–

Declaração de constantes e Opos de dados privados

–

Codiﬁcação procedimentos e funções públicas e privadas

–

Guardada em ﬁcheiro com extensão SPB (ex. ALUNO.SPB)

(16)

Desenvolvimento de Código Modular – 6

• Especificação: ALUNO.SPS

PACKAGE aluno IS

FUNCTION função1(x IN NUMBER) RETURN NUMBER;

FUNCTION função2(p IN NUMBER) RETURN NUMBER;

END teste;

• Codiﬁcação: ALUNO.SPB

PACKAGE BODY aluno IS

PI CONSTANT NUMBER := 3.14159; FUNCTION função1(x IN NUMBER) RETURN NUMBER IS

BEGIN

-‐-‐ Código da função1 END função1;

FUNCTION função2(p IN NUMBER) RETURN NUMBER IS

BEGIN

-‐-‐ Código da função2 END função2;

FUNCTION função_aux(x IN NUMBER) RETURN NUMBER IS

BEGIN

-‐-‐ Código da função_aux END função_aux;

END teste;

função_aux só é visível

dentro do pacote aluno

(17)

Integração com a Linguagem SQL – 1

• Comandos SQL no código PL/SQL

PROCEDURE gere_empregado(operação IN VARCHAR2,

número_emp IN empregado.número%TYPE, nome_emp IN empregado.nome%TYPE) IS

BEGIN

IF (operação = ‘DELETE’) THEN

DELETE FROM empregado WHERE (número = número_emp); ELSIF (operação = ‘INSERT’) THEN

INSERT INTO empregado(número, nome) VALUES (número_emp, nome_emp); END IF;

END gere_empregado;

• Chamada a funções PL/SQL em comandos SQL

SELECT número_encomenda, dias_que_passaram(data_encomenda) FROM encomenda; UPDATE empregado SET vencimento := novo_vencimento(vencimento);

Evitar nomes de

parâmetros iguais

aos de atributos

de tabelas

(18)

Integração com a Linguagem SQL – 2

• Interrogações que retornam uma ocorrência

DECLARE

nome_empregado1 VARCHAR2(80);

nome_empregado2 empregado.nome%TYPE; -‐-‐ Melhor alternativa. BEGIN

-‐-‐ Apenas os nomes dos empregados são guardados em variáveis locais. SELECT nome INTO nome_empregado1 FROM empregado WHERE número = 1001; SELECT nome INTO nome_empregado2 FROM empregado WHERE número = 1002; END;

DECLARE

empregado1 empregado%ROWTYPE; BEGIN

-‐-‐ Todos os dados do empregado são guardados na variável local. SELECT * INTO empregado1 FROM empregado WHERE número = 1001; END;

Estas interrogações geram a

excepção NO_DATA_FOUND

caso a ocorrência não exista

(19)

Integração com a Linguagem SQL – 3

• Interrogações que retornam múlZplas ocorrências

• Interrogações anteriores envolvem uma ocorrência

–

Excepção TOO_MANY_ROWS é gerada quando uma

interrogação devolve mais do que uma ocorrência

– SELECT * INTO emp1 FROM empregado -‐-‐ Lança excepção

• Para ler múltiplas ocorrências é necessário usar cursores

• Cursores permitem percorrer, de forma iteraZva, todas as

ocorrências resultantes de interrogações

3. Leitura de

ocorrências

4. Fecho

do cursor

1. Declaração

do cursor

2. Abertura

do cursor

(20)

Integração com a Linguagem SQL – 4

• Exemplo de uZlização de cursor

DECLARE

CURSOR c_emp IS

SELECT * FROM empregado; emp empregado%ROWTYPE;

BEGIN

OPEN c_emp; LOOP

FETCH c_emp INTO emp;

EXIT WHEN c_emp%NOTFOUND; ...

END LOOP; CLOSE c_emp; END;

emp vai guardar, uma

de cada vez, todas as

ocorrências retornadas

pela interrogação

2. Abertura do cursor

1. Declaração do cursor

4. Fecho do cursor

3. O ciclo percorre todas as

ocorrências associadas ao

resultado da interrogação

(21)

Integração com a Linguagem SQL – 5

• Exemplo anterior pode ser escrito de forma abreviada

DECLARE

CURSOR c_emp IS

SELECT * FROM empregado; BEGIN

FOR emp IN c_emp LOOP ...

END LOOP; END;

• Gestão do cursor é totalmente implícita

• Este Zpo de ciclo FOR só deve ser usado para percorrer

todas as ocorrências do resultado da interrogação

BEGIN

FOR emp IN (SELECT * FROM empregado) LOOP ...

END LOOP; END;

Forma equivalente do ciclo

FOR, ainda mais abreviada

emp, de Zpo empregado

%ROWTYPE, só é visível

(22)

Integração com a Linguagem SQL – 6

• Atributos de cursores

DECLARE

CURSOR c_emp IS

SELECT * FROM empregado; emp empregado%ROWTYPE;

BEGIN

OPEN c_emp; LOOP

FETCH c_emp INTO emp; IF (c_emp%FOUND) THEN ...

END IF; END LOOP; CLOSE c_emp; EXCEPTION

WHEN OTHERS THEN

IF (c_emp%ISOPEN) THEN CLOSE c_emp; END IF; END;

Devolve TRUE se o

úlZmo FETCH devolveu

uma ocorrência

TRUE se o cursor

ainda está aberto

Outros atributos de cursores:

%NOTFOUND, %ROWCOUNT

(23)

Integração com a Linguagem SQL – 7

• Escritas na ocorrência actual de um cursor

FUNCTION altera_departamento(número_emp IN empregado.número%TYPE,

novo_dep IN empregado.departamento%TYPE) RETURN NUMBER

IS

CURSOR c_emp IS SELECT * FROM empregado

WHERE (número = número_emp) FOR UPDATE OF departamento; aux_emp empregado%ROWTYPE;

retorno NUMBER := NULL; BEGIN

OPEN c_emp;

FETCH c_emp INTO aux_emp; IF (c_emp%FOUND) THEN

UPDATE empregado SET departamento = novo_dep WHERE CURRENT OF c_emp; retorno := 0; ELSE retorno := -‐1; END IF; CLOSE c_emp; RETURN retorno; END altera_departamento;

Ocorrências abrangidas pela interrogação (neste

caso, só uma) ﬁcam reservadas para escrita

UPDATE afecta a ocorrência

actual do cursor c_emp

(24)

Integração com a Linguagem SQL – 8

• Transporte de cursores

CREATE OR REPLACE PACKAGE p_empregado IS

TYPE ref_cursor_emp IS REF CURSOR RETURN empregado%ROWTYPE;

FUNCTION lista_empregados_com_inicial(letra CHAR) RETURN ref_cursor_emp; END p_empregado;

CREATE OR REPLACE PACKAGE BODY p_empregado IS

FUNCTION lista_empregados_com_inicial(letra CHAR) RETURN ref_cursor_emp IS lista_emp ref_cursor_emp;

BEGIN

OPEN lista_emp FOR SELECT * FROM empregado WHERE nome LIKE letra || '%'; RETURN lista_emp;

END lista_empregados_com_inicial; END p_empregado;

• Função devolve resultado da interrogação do cursor

• Resultado é lido por quem invoca a função (ex. Java)

(25)

Integração com a Linguagem SQL – 9

• Funções PL/SQL podem ser usadas em comandos SQL

• Regras a observar

–

Parâmetros das funções têm de ter o modo IN

–

Tipos dados dos parâmetros têm de ser básicos

• NUMBER, VARCHAR2, CHAR, …

–

Funções não podem alterar o conteúdo da base de dados

• Exemplo

FUNCTION dias_que_passaram(data1 IN DATE, data2 IN DATE := SYSDATE) RETURN NUMBER

BEGIN

RETURN (data2 -‐ data1); END dias_que_passaram;

(26)

Funções Predeﬁnidas

• Escrita no ecrã

DBMS_OUTPUT.PUT_LINE(‘Mensagem de depuração’);

• Sobre sequências de caracteres

LOWER(‘ABC’) => ‘abc’ LENGTH(‘ABC’) => 3

INSTR(‘ABC’, ‘B’) => 2;

• Sobre datas

LAST_DAY(‘12-‐JAN-‐2001’) => ‘31-‐JAN-‐2001’ NEXT_DAY(‘24-‐SEP-‐2001’, ‘Friday’) => ‘28-‐SEP-‐2001’ SYSDATE => <data_actual>

• Sobre números

MOD(10, 3) => 1 SQRT(4) => 2 TRUNC(1.2345, 2) => 1.23

• Conversões

TO_CHAR(SYSDATE, ‘DD.MM.YYYY HH24:MI:SS’) => ‘02.10.2000 13:00:00’

Para ver a mensagem de

depuração no SQL*Plus,

executar previamente

SET SERVEROUTPUT ON

Existem muitas mais

funções disponíveis

(27)

• Vantagens

–

ProduZvidade

• Associação de Zpos de dados

• Integração com linguagem SQL

–

Desempenho

• Código próximo dos dados

–

Portabilidade

• Oracle disponível em diversas

plataformas computacionais

–

Comportamento transaccional

• Em caso de excepção não

tratada, código executado é

anulado automaZcamente

• Desvantagens

–

Linguagem proprietária

–

Só usada em SGBDs Oracle

–

Desempenho

• Muitos acessos a dados

limitam recursos para

execução de código

–

Não segue conceitos das

linguagens centradas em

objectos

• Não existem classes

• Necessária adaptação de

diagramas UML

(28)

Conceito e USlização de Triggers

• Conceito de trigger

–

Bloco de código implicitamente executado aquando de uma

operação de escrita sobre uma tabela associada

• INSERT, UPDATE, ou DELETE

–

Bloco código pode conter comandos SQL ou código PL/SQL

–

Trigger é executado como uma unidade

• UZlização de triggers

–

Regras de negócio complexas

–

Réplicas síncronas de tabelas

–

Autorizações especiais

–

Registo de eventos

• Escrita em tabelas

• Início e ﬁm de sessões de uOlizadores

Cuidado com a acZvação

recursiva de triggers!

(29)

Estrutura de um Trigger

CREATE TRIGGER verifica_aumento_vencimento BEFORE UPDATE OF vencimento ON empregado FOR EACH ROW

WHEN (new.vencimento > old.vencimento) DECLARE

vencimento_do_chefe NUMBER := NULL; BEGIN

SELECT vencimento INTO vencimento_do_chefe FROM empregado WHERE (categoria = ‘chefe’); IF (:new.vencimento > vencimento_do_chefe) THEN

RAISE_APPLICATION_ERROR(-‐20001, ‘Não pode ganhar mais que o chefe!’) END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-‐20002, ‘A empresa não tem chefe!’); WHEN OTHERS THEN RAISE;

END;

Restrição de

acZvação (opcional)

Especiﬁcação

do trigger

Acção do

trigger (código)

(30)

Tipos de Triggers

CREATE TRIGGER verifica_aumento_vencimento

BEFORE UPDATE OF vencimento ON empregado FOR EACH ROW

WHEN ... ...

• Cada tabela pode ter 12 Opos disOntos de triggers (2 × 3 × 2)

• Se forem criados vários triggers do mesmo Opo não há a

certeza de qual será acOvado primeiro

• Nota: cláusula WHEN é aplicada em triggers de Opo FOR

EACH ROW, e não pode ter interrogações ou código PL/SQL

Qualquer combinação de

INSERT OR UPDATE OR DELETE

Se omiZdo, o trigger é executado

uma só vez por comando SQL

BEFORE ou AFTER

(31)

Trigger para Autorização Especial

CREATE TRIGGER em_horário_trabalho

BEFORE INSERT OR UPDATE OR DELETE ON empregado DECLARE

data_actual DATE := SYSDATE; hora_actual NUMBER(2) := NULL; dia_semana CHAR(3) := NULL; BEGIN

hora_actual := TO_CHAR(data_actual, ‘HH24’); dia_semana := TO_CHAR(data_actual, ‘DY’); IF (hora_actual NOT BETWEEN 8 AND 24) THEN

RAISE_APPLICATION_ERROR(-‐20001, ‘Fora de horas!’); ELSIF (dia_semana IN (‘SAT’, ‘SUN’)) THEN