Linguagens hospedeiras
Aplicações




interrogar a BD
tomar decisões
dialogar com o utilizador
etc.
linguagem de programação convencional
C, Cobol, Ada, Java
linguagem de manipulação de dados
optimizada para acesso rápido a grandes BD
O SGBD fornece um conjunto de procedimentos
que implementam as principais funções da LMD
PL/SQL - 1
Acesso à BD
ligação directa (API)
ODBC
JDBC
dados locais
BD
aplicação
chamadas
LMD
PL/SQL - 2
Comunicação



os programas de aplicação não escrevem
directamente na BD
usam operações do nível conceptual - a bem da
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 um tuplo de cada vez
o seguinte é obtido com uma instrução de fetch
os dados na área local são variáveis normais do programa
PL/SQL - 3
PL/SQL - Ambiente
Motor PL/SQL
PL/SQL
Block
PL/SQL
PL/SQL
Block
SQL
Execução da
expressão
procedimental
Execução da expressão SQL
Servidor Oracle
PL/SQL - 4
PL/SQL - Benefícios



Pode ser usado para agrupar várias perguntas 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 - 5
PL/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 da BD (%TYPE, %ROWTYPE)
PL/SQL - 6
PL/SQL - Benefícios
Declare
ooo
Begin
ooo
Exception
ooo
End;





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 excepções.
PL/SQL - 7
Estruturas de controlo

Instruções condicionais:




IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-END IF
Ciclos



LOOP
FOR
WHILE
PL/SQL - 8
Estruturas de controlo
LOOP
statement1;
…
EXIT [WHEN condition];
END LOOP;
condition - é uma variável booleana ou uma expressão
que evolui para (TRUE, FALSE ou NULL)
Exercício: bloco que insira 10 linhas numa tabela de
linhas de encomenda, para a encomenda 0017
PL/SQL - 9
Exemplo de ciclo
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 - 10
Estruturas de controlo
FOR counter IN [REVERSE]
lower_bound . . uper_bound LOOP
statement1;
statement2;
...
END LOOP;
WHILE condition LOOP
statement1;
statement2;
...
END LOOP;
PL/SQL - 11
Frases SQL em PL/SQL






Extrair uma única linha de dados da BD usando
SELECT;
Alterar linhas da BD usando comandos DML;
Controlar transacções com COMMIT,
ROLLBACK ou SAVEPOINTS;
Determinar a saída gerada por DML com cursores;
PL/SQL não suporta DDL (create/alter/drop table);
PL/SQL não suporta DCL (Grant,…);
PL/SQL - 12
Estrutura
DECLARE - Optativo
Variáveis, cursores, excepções definidas pelo utilizador
BEGIN - Obrigatório
Declare
ooo
Expressões PL/SQL
Begin
EXCEPTION - Optativo
ooo
Acções a executar quando ocorrem erros Exception
ooo
END - Obrigatório
End;
Expressões SQL
PL/SQL - 13
Tipos de Blocos
Anónimo
[DECLARE]
BEGIN
--statements
[EXCEPTION]
END;
Procedimento
PROCEDURE name
IS
Função
[EXCEPTION]
FUNCTION name
RETURN datatype
IS
BEGIN
-- statements
RETURN value;
[EXCEPTION]
END;
END;
BEGIN
--statements
•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 - 14
Procedimentos
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
3
4
5
6
7
8
9
(v_id in emp.empno%TYPE)
IS
BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE empno = v_id;
END raise_salary;
/
Executar:
SQL> EXECUTE raise_salary (7369)
PL/SQL> raise_salary(7369)
PL/SQL - 15
Funçõ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 - 16
Gatilhos



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
activou o gatilho.
PL/SQL - 17
Gatilhos

Que frase DML activa gatilhos?


Tipo de gatilhos: (quantas vezes deve o gatilho
executar ao acontecer o evento?)



INSERT; UPDATE; DELETE.
Statement - executa uma vez;
Row - executa uma vez para cada linha afectada pelo gatilho.
Corpo do gatilho

Bloco PL/SQL ou chamada a procedimento.
PL/SQL - 18
Gatilhos
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 - 19
Gatilhos
Exemplo
SQL>
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
IF NOT (:NEW.JOB IN (‘MANAGER’,’PRESIDENT’))
and :NEW.SAL > 5000
THEN
RAISE_APPLICATION_ERROR
(-20202,’Employee cannot earn this amount’);
END IF;
END;
•UPDATE EMP SET SAL=6500 WHERE ENAME=‘MILLER’;
PL/SQL - 20
Tipos 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 - 21
Variáveis ligadas
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 - 22
Exemplo 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 - 23
Declaraçã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 - 24
Tipos escalares

Varchar2(max_comp)
Number[(precisão,casas decimais)]
Date
ATENÇÃO: As variáveis
Char [(max_comp)] não devem ter nomes
iguais a nomes das
Long
colunas da BD.
Long Raw

Boolean








true, false, null
Binary_integer
Pls_integer
Ambiguidade: não se
sabe se se escreve na BD
ou na variável!
PL/SQL - 25
Exercício sobre variáveis

Quais as declarações erradas?
DECLARE
v_id
v_x, v_y, v_z
v_aniversario
v_em_stock
number(4);
varchar2(10);
date not null;
boolean := 1;
PL/SQL - 26
Alcance 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 - 27
PL/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 - 28
PL/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 - 29
Registos

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 - 30
Registos 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 - 31
Tabelas

Coluna de Binary integer funciona como índice; segunda
coluna (de conteúdo) pode ser simples ou composta
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(3); -- delete element 3
/* PL/SQL keeps a placeholder for element 3. So, the
next statement appends element 4, not element 3. */
courses.EXTEND; -- append one null element
/* Now element 4 exists, so the next statement does
not raise SUBSCRIPT_BEYOND_COUNT. */
courses(4) := 'Engl 2005';
PL/SQL - 32
Tabelas
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30, 40);
BEGIN
depts.DELETE(3); -- apaga terceiro elemento
FORALL i IN depts.FIRST..depts.LAST – ligação em massa
DELETE FROM emp WHERE deptno = depts(i); -- causa erro
END;

Métodos para tabelas


Exists
Count
First
Last
Prior
Next
Extend
Trim
Delete
PL/SQL - 33
PL/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 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
Controlo de cursores explícitos
no
Declare
Open
cria uma
identifica
área no SQL conjunto
activo
Fetch
Empty?
Close
yes
carrega linha
testa fim de
corrente
dados
liberta conjunto
activo
PL/SQL - 36
Cursores - 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 num 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 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í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 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-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
PL/SQL - 42