10 programacao com banco de dados pl sql

Prévia do material em texto

Projeto de Banco de Dados
Turma A-1
Projeto de Banco de Dados
Turma A-1
Professor Rodrigo Salvador Monteiro
salvador@ic.uff.br
Professor Rodrigo Salvador Monteiro
salvador@ic.uff.br
Conteúdo
PL/SQL
PL/SQL 
- 2
Aplicações
� Queries no BD
� tomar decisões
� dialogar com o utilizador
� etc.
Linguagens hospedeirasLinguagens hospedeiras
linguagem de programação convencional
C, Cobol, Ada, Java
linguagem de manipulação de dados
otimizada para acesso rápido a grandes BD
O SGBD fornece um conjunto de procedimentos que 
implementam as principais funções da LMD
PL/SQL 
- 3
Acesso à BDAcesso à BD
Chamadas
LMD
BDdados locais
aplicação
ligação direta (API)
ODBC
JDBC
PL/SQL 
- 4
ComunicaçãoComunicação
• os programas de aplicação não escrevem diretamente no 
BD
• usam operações do nível conceitual - independência dos 
dados
• se uma chamada da LMD produz uma relação
� esta pode ser maior que a área de dados local
� as linguagens convencionais são record-oriented
• logo
� é transferido para a área local uma tupla de cada vez
� a seguinte é obtida com uma instrução de fetch
� os dados na área local são variáveis normais do programa
PL/SQL 
- 5
PL/SQL - AmbientePL/SQL - Ambiente
PL/SQL
Block
PL/SQL
Block
Execução da 
expressão
procedimental
Execução da expressão SQL
Servidor Oracle
PL/SQL
SQL
Motor PL/SQL
PL/SQL 
- 6
PL/SQL - BenefíciosPL/SQL - Benefícios
• Pode ser usado para agrupar várias queries SQL num 
só bloco e enviá-lo de uma só vez para o servidor.
• PL/SQL pode cooperar com as ferramentas de 
desenvolvimento de aplicações Oracle tais como 
Developer Forms e Reports adicionando poder de 
processamento procedimental a estas ferramentas.
• Podemos tirar vantagens das capacidades 
procedimentais do PL/SQL que não estão presentes 
no SQL.
PL/SQL 
- 7
PL/SQL - BenefíciosPL/SQL - Benefícios
• Os programas podem ser transportados para qualquer 
ambiente (SO ou plataforma) que suporte o servidor 
Oracle e PL/SQL, ou seja, os programas PL/SQL 
podem correr em qualquer sítio onde o servidor Oracle 
corra.
• Pode-se escrever programas e criar bibliotecas que 
podem ser reutilizadas em diferentes ambientes.
• Pode-se declarar variáveis dinamicamente baseadas 
em estruturas de dados ou tabelas e colunas do BD 
(%TYPE, %ROWTYPE)
PL/SQL 
- 8
PL/SQL - BenefíciosPL/SQL - Benefícios
• Desenvolvimento modular de 
programas.
• Permite executar instruções 
condicionais.
• Permite executar instruções num ciclo.
• Permite processar várias linhas 
devolvidas por uma pergunta através 
de cursores.
• Os erros podem ser processados com 
rotinas de tratamento de exceções.
Declare
Begin
Exception
End;
o o o 
o o o 
o o o 
PL/SQL 
- 9
Estruturas de ControleEstruturas de Controle
• Instruções condicionais:
� IF-THEN-END IF
� IF-THEN-ELSE-END IF
� IF-THEN-ELSIF-END IF
• Ciclos
� LOOP
� FOR
� WHILE
PL/SQL 
- 10
Estruturas de ControleEstruturas de Controle
LOOP
statement1;
…
EXIT [WHEN condition];
END LOOP;
condition - é uma variável booleana ou uma expressão que evolui 
para (TRUE, FALSE ou NULL)
PL/SQL 
- 11
ExemploExemplo
DECLARE
v_num_enc itens.no_enc%TYPE := 134;
Contador number(2) := 1;
BEGIN
LOOP
INSERT INTO itens( no_enc, linha )
values(v_num_enc, contador );
Contador:= contador+1;
Exit when contador >10;
End loop;
END;
PL/SQL 
- 12
Estruturas de ControleEstruturas de Controle
FOR counter IN [REVERSE]
lower_bound . . uper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
WHILE condition LOOP
statement1;
statement2;
. . . 
END LOOP;
PL/SQL 
- 13
SQL em PL/SQL, o que é permitido?SQL em PL/SQL, o que é permitido?
• Extrair tuplas do BD usando SELECT;
• Alterar tuplas do BD usando comandos DML;
• Controlar transacções com COMMIT, ROLLBACK 
ou SAVEPOINTS;
• PL/SQL não suporta DDL (create/alter/drop
table);
• PL/SQL não suporta DCL (Grant,…);
PL/SQL 
- 14
EstruturaEstrutura
DECLARE - Optativo
Variáveis, cursores, excepções definidas pelo 
utilizador
BEGIN - Obrigatório
Expressões SQL 
Expressões PL/SQL
EXCEPTION - Optativo
Acções a executar quando ocorrem erros
END - Obrigatório
Declare
Begin
Exception
End;
o o o 
o o o 
o o o 
PL/SQL 
- 15
Tipos de BlocosTipos de Blocos
Anônimo Procedimento Função
[DECLARE]
BEGIN
--statements
[EXCEPTION]
END;
PROCEDURE name
IS
BEGIN
--statements
[EXCEPTION]
END;
FUNCTION name
RETURN datatype
IS
BEGIN
-- statements
RETURN value;
[EXCEPTION]
END;
•Um bloco dentro de outro funciona como uma instrução 
(composta)
•Nível de encaixe arbitrário
•Funções SQL disponíveis em PL/SQL excepto as de 
agregação
PL/SQL 
- 16
ProcedimentosProcedimentos
Sintaxe:
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
…)
IS | AS 
PL/SQL Block
Exemplo: SQL> CREATE OR REPLACE PROCEDURE raise_salary
2 (v_id in emp.empno%TYPE)
3 IS
4 BEGIN
5 UPDATE emp
6 SET sal = sal * 1.1
7 WHERE empno = v_id;
8 END raise_salary;
9 /
Executar: 
SQL> EXECUTE raise_salary (7369) PL/SQL> raise_salary(7369)
PL/SQL 
- 17
FunçõesFunções
Sintaxe: CREATE [OR REPLACE] FUNCTION function_name
(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
…)
RETURN datatype
IS | AS
PL/SQL Block
Exemplo: SQL> CREATE OR REPLACE FUNCTION get_sal
2 (v_id IN emp.empno%TYPE)
3 RETURN NUMBER
4 IS v_salary emp.sal%TYPE :=0;
5 BEGIN
6 SELECT sal INTO v_salary
7 FROM emp WHERE empno = v_id;
8 RETURN (v_salary);
9 END get_sal;
10 /
PL/SQL 
- 18
GatilhosGatilhos
• Um gatilho é um bloco PL/SQL que executa 
implicitamente sempre que um evento particular 
acontece.
• Um gatilho pode ser da BD ou da aplicação.
• Quando executa?
� BEFORE - Antes de executar uma frase DML sobre uma 
tabela.
� AFTER - Depois da frase DML
� INSTEAD OF - Executa o corpo do gatilho em vez da frase 
que ativou o gatilho.
PL/SQL 
- 19
GatilhosGatilhos
• Que frase DML ativa gatilhos?
� INSERT; UPDATE; DELETE.
• Tipo de gatilhos: (quantas vezes deve o gatilho 
executar ao acontecer o evento?)
� Statement - executa uma vez;
� Row - executa uma vez para cada linha afetada pelo 
gatilho.
• Corpo do gatilho
� Bloco PL/SQL ou chamada a procedimento.
PL/SQL 
- 20
GatilhosGatilhos
Sintaxe
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
trigger_body
Ou (gatilho de linha)
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old / NEW AS new]
FOR EACH ROW
[WHEN condition]
trigger_body
PL/SQL 
- 21
GatilhosGatilhos
Exemplo
SQL> CREATE OR REPLACE TRIGGER 
derive_commission_pct
2 BEFORE INSERT OR UPDATE OF sal ON emp
3 FOR EACH ROW
4 BEGIN
5 IF NOT (:NEW.JOB IN (‘MANAGER’,’PRESIDENT’))
6 and :NEW.SAL > 5000
7 THEN
8 RAISE_APPLICATION_ERROR
9 (-20202,’Employee cannot earn this amount’);
10 END IF;
11 END;
•UPDATE EMP SET SAL=6500 WHERE ENAME=‘MILLER’;
PL/SQL 
- 22
Tipos de variáveisTipos de variáveis
• PL/SQL variáveis:
� Escalar (valor único);
� Composta (records);
� Referência (apontador);
� LOB (especificam a localização de objectos grandes. Ex: 
imagens)
• Variáveis não PL/SQL: “bind or host variables”
� Usadas para passar valores obtidos durante a execução 
de PL/SQL para o ambiente SQL*Plus
PL/SQL 
- 23
Variáveis ligadasVariáveisligadas
Variáveis definidas no PL/SQL não são visíveis pelo 
SQL*Plus
Bind variables -> Variáveis ligadas são variáveis que são 
criadas no SQL*Plus e podem ser referidas no PL/SQL.
•Como criar 
Ex: VARIABLE ret_val NUMBER;
•Para referir vars ligadas no PL/SQL usa-se (:) 
imediatamente antes da variável
Ex: :ret_val :=1;
•Para mudar o valor desta variável no SQL*Plus é
necessário escrever um bloco PL/SQL.
Ex: SQL> begin
2 :ret_val:=4;
3 end;
4 /
PL/SQL 
- 24
Exemplo em SQL/PlusExemplo em SQL/Plus
SQL> VARIABLE g_sal_mensal NUMBER
SQL> ACCEPT p_sal_anual PROMPT ‘Salário anual: ‘
SQL> DECLARE
SQL> v_sal NUMBER(9,2) := &p_sal_anual;
SQL> BEGIN
SQL> :g_sal_mensal := v_sal / 12;
SQL> END;
SQL> /
SQL> PRINT g_sal_mensal
SQL> /* ou: SET SERVEROUTPUT ON
SQL> DBMS_OUTPUT.PUT_LINE(‘Salário mensal de ‘ || TO_CHAR(v_sal));
SQL> */
PL/SQL 
- 25
Declaração e atribuiçãoDeclaração e atribuição
Sintaxe:
identifier [CONSTANT] datatype [NOT NULL] 
[:= | DEFAULT expr];
Exemplos:
Declare
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL :=10;
c_comm CONSTANT NUMBER :=1400;
v_ename emp.ename%TYPE;
Atribuição:
v_hiredate := ‘31-DEC-98’; Variáveis %TYPE herdam o tipo da coluna base e 
suportam mudanças nessa 
definição
PL/SQL 
- 26
Tipos escalaresTipos escalares
• Varchar2(max_comp)
• Number[(precisão,casas decimais)]
• Date
• Char [(max_comp)]
• Long
• Long Raw
• Boolean
� true, false, null
• Binary_integer
• Pls_integer
ATENÇÃO: As variáveis não 
devem ter nomes iguais a 
nomes das colunas do BD. 
Ambiguidade: não se sabe se 
se escreve na BD ou na 
variável!
PL/SQL 
- 27
Exercício sobre variáveisExercício sobre variáveis
• Quais as declarações erradas?
DECLARE
v_id number(4);
v_x, v_y, v_z varchar2(10);
v_aniversario date not null;
v_em_stock boolean := 1;
PL/SQL 
- 28
Alcance das variáveisAlcance das variáveis
• Qual o valor de cada variável em ambos os blocos?
DECLARE
V_sal number(7,2) := 60000;
V_com number(7,2) := v_sal*0.2;
V_mensagem varchar2(255) := ‘ de qualidade.’;
Begin ...
Declare
V_sal number(7,2) := 50000;
V_com number(7,2) := 0;
V_total number(7,2) := v_sal+v_com;
Begin ...
V_mensagem := ‘Operario’ || v_mensagem;
End;
V_mensagem := ‘Secretario ’ || v_mensagem;
END;
PL/SQL 
- 29
PL/SQL - SELECT em PL/SQLPL/SQL - SELECT em PL/SQL
SINTAXE: SELECT select_list
INTO {variable_name[, 
variable_name]… | record_name}
FROM table
WHERE condition;
Exemplo: DECLARE
v_sum_sal emp.sal%TYPE;
v_deptno NUMBER NOT NULL :=10;
BEGIN
SELECT SUM(sal) --group function
INTO v_num_sal
FROM emp
WHERE dptno=v_deptno;
END;
• erro se pergunta 
devolver 0 
(NO_DATA_FOUND) ou 
mais do que 1 linha 
(TOO_MANY_ROWS)
• cláusula INTO crucial
PL/SQL 
- 30
PL/SQL - INSERT/UPDATE em PL/SQLPL/SQL - INSERT/UPDATE em PL/SQL
BEGIN
INSERT INTO emp(empno, ename, job, deptno)
VALUES empno_sequence.nextval,’Harding’,’Clerk’, 10);
END;
DECLARE
v_sal_increase emp.sal%TYPE := 2000;
BEGIN
UPDATE emp
SET sal:= sal + v_sal_increase
WHERE job = ‘ANALYST’;
END;
PL/SQL 
- 31
RegistosRegistos
• Definir um tipo RECORD para agrupar dados heterogéneos
� TYPE nome_tipo IS RECORD (campo[, campo]...);
� nome_campo tipo [[NOT NULL] {:= | DEFAULT} expressão]
• Pode-se herdar a definição de um registo
DECLARE
registo_emp emp%ROWTYPE
BEGIN
SELECT * INTO registo_emp FROM emp WHERE...
� Tornando desnecessário conhecer em detalhe a tabela base
PL/SQL 
- 32
Registos encaixadosRegistos encaixados
• DECLARE
TYPE TimeRec IS RECORD (mins SMALLINT, hrs SMALLINT);
TYPE MeetingRec IS RECORD (
day DATE,
time_of TimeRec, -- nested record
room_no INTEGER(4));
TYPE PartyRec IS RECORD (
day DATE,
time_of TimeRec, -- nested record
place VARCHAR2(25));
seminar MeetingRec;
party PartyRec;
BEGIN ...
party.time_of := seminar.time_of;
END;
PL/SQL 
- 33
PL/SQL - CursoresPL/SQL - Cursores
• Um cursor é uma área privada do SQL.
• Há dois tipos de cursores:
� Implícitos (chamados SQL)
� Explícitos
• O servidor Oracle usa cursores implícitos para 
analisar e executar código SQL.
• Os cursores explícitos são explicitamente 
declarados pelo programador.
PL/SQL 
- 34
Atributos do cursor SQLAtributos do cursor SQL
• Através dos atributos do cursor implícito (chamado 
SQL) podemos testar a saída produzida por uma 
pergunta SQL:
� SQL%ROWCOUNT - nº de linhas afectadas pela mais 
recente frase SQL;
� SQL%FOUND - TRUE se a mais recente frase SQL 
afecta uma ou mais linhas;
� SQL%NOTFOUND - TRUE se a mais recente frase SQL 
não afecta nenhuma linha;
� SQL%ISOPEN - Sempre FALSE porque o PL/SQL fecha 
sempre os cursores implícitos depois de os executar;
PL/SQL 
- 35
Controle de cursores explícitosControle de cursores explícitos
cria uma área 
no SQL
Declare Open Fetch CloseEmpty?
yes
no
identifica 
conjunto ativo testa fim de dados
carrega linha 
corrente liberta conjunto 
ativo
PL/SQL 
- 36
Cursores - declaraçãoCursores - declaração
CURSOR cursor_name [(parameter[, parameter]...)]
[RETURN return_type] IS select_statement;
� cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]
DECLARE
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000; 
CURSOR c2 RETURN dept%ROWTYPE IS 
SELECT * FROM dept WHERE deptno = 10;
• sem cláusula INTO
PL/SQL 
- 37
Cursor em um PackageCursor em um Package
CREATE PACKAGE BODY emp_stuff AS
CURSOR c1 RETURN emp%ROWTYPE IS
SELECT * FROM emp WHERE deptno = 20; --
new WHERE clause
...
END emp_stuff;
PL/SQL 
- 38
Processamento das linhasProcessamento das linhas
DECLARE
emp_rec emp%ROWTYPE; 
-- ou emp_rec emp_stuff.c1%ROWTYPE
...
BEGIN
...
OPEN emp_stuff.c1;
LOOP
FETCH emp_stuff.c1 INTO emp_rec;
EXIT WHEN emp_suff.c1%NOTFOUND;
...
END LOOP;
CLOSE emp_stuff.c1;
END;
PL/SQL 
- 39
Atributos dos cursores explícitosAtributos dos cursores explícitos
• Através dos atributos dos cursores explícitos podemos 
controlar o processamento do resultado:
� SQL%ROWCOUNT - nº de linhas devolvidas até ao 
momento;
� SQL%FOUND - TRUE se o fetch mais recente devolveu 
linha;
� SQL%NOTFOUND - TRUE se o fetch mais recente não 
devolveu linha;
� SQL%ISOPEN – TRUE se o cursor estiver aberto
• Convém fechar o cursor; máximo de cursores abertos 
(50) no parâmetro da BD OPEN_CURSORS
PL/SQL 
- 40
Cursor de ciclo FORCursor de ciclo FOR
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE 
exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calcula e armazena os resultados */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, 
NULL);
END LOOP;
COMMIT;
END;
PL/SQL 
- 41
Cursor de sub-perguntaCursor de sub-pergunta
DECLARE
bonus REAL;
BEGIN
FOR emp_rec IN (SELECT empno, sal, comm
FROM emp) LOOP
bonus := (emp_rec.sal * 0.05) + 
(emp_rec.comm * 0.25);
INSERT INTO bonuses VALUES 
(emp_rec.empno, bonus);
END LOOP;
COMMIT;
END;
Neste caso não se pode usar os atributos de cursor porque 
o cursor não tem nome