Pr o g r a m a ç ã o e m PL /SQL
Es t r u t u r a d o Cu r s o
Introdução a PL/ SQL
Conceitos Básicos de PL/ SQL
Variáveis e Dados de Programa
Controle Condicional e Sequencial
Loops
Registros em PL/ SQL
Tabelas PL/ SQL
Es t r u t u r a d o Cu r s o
SQL em PL/ SQL
Funções de SQL integradas
Cursores
Procedimentos e Funções
Packages
Triggers
Tratamento de Erros
Es t r u t u r a d o Cu r s o
Testes e Depuração
PL/ SQL Dinâmico
Entrada e Saída em Arquivos
Desempenho e Otimização
PL /SQL
Procedural Language extensions to SQL.
Usamos esta linguagem no Oracle Server
e em aplicações-cliente (p.e. Oracle
Forms).
Adiciona construções de programação não
existentes na linguagem de banco de
dados padrão.
Permite a combinação de comandos SQL
com construções procedurais.
V e r s õ e s d o PL /SQL
Cliente
Versão 1.1 Developer/2000
Versão 8.0 Developer 6
Servidor
Versão 2.0 - Oracle 7.0
Versão 2.1 - Oracle 7.1
Versão 2.2 - Oracle 7.2
Versão 2.3 - Oracle 7.3
Versão 8.0 - Oracle 8.0
A r q u i t e t u r a d o PL /SQL
Oracle Forms
Gerador
de SQL
Programa
PL/SQL local
PL/SQL V8.0
Runtime Engine
Oracle Server
Stored Program Unit
Processador
de comandos
SQL
PL/SQL V8.0
Runtime Engine
A m b i e n t e s d e Ex e c u ç ã o
Servidor
Oracle 7: PL/ SQL versão 2
Oracle 8: PL/ SQL versão 8
SQL* Plus (modo interativo)
Cliente
Oracle Developer ( Forms, Reports e
Procedure Builder)
utilizam compiladores PL/ SQL próprios
Co n c e i t o s B á s i c o s d a
L i n g u a g e m PL /SQL
Ch a r a c t e r Se t d o PL /SQL
Tipo
Letras
Caracteres
A-Z, a-z
Dígitos
Sím bolos
0-9
~!@ #$%&*()_
-+=|[]{}:;
<>
,.?/
Tab, espaço, Enter
Espaço em Branco
Ch a r a c t e r Se t d o PL /SQL
O PL/ SQL não é uma linguagem sensível
ao contexto.
letras maiúsculas são tratadas da mesma
maneira que minúsculas, a não ser no caso
destas pertencerem a uma cadeia de
caracteres.
If x < > ´ s´ then
If x < > ´ S´ then
Sím b o l o s Si m p l e s e Co m p o s t o s
S ím b o lo
;
D e s c r iç ã o
te rm in a d o r d e c o m a n d o s
%
in d ic a d o r d e a trib u to (p .e .
% IS O P E N ); s ím b o lo w ild c a r d
s ím b o lo w ild c a r d
in d ic a d o r d e v a riá v e l h o s p e d e ira
o p e ra d o r e x p o n e n c ia l
d ife re n te
o p e ra d o r d e c o n c a te n a ç ã o
d e lim ita d o r d e ró tu lo
_
:
**
< > e !=
||
<< e >>
<= e >=
:=
=>
-/* e * /
m e n o r o u ig u a l e m a io r o u ig u a l
o p e ra d o r d e a trib u iç ã o
o p e ra d o r d e a s s o c ia ç ã o p a ra
n o ta ç ã o p o s ic io n a l
c o m e n tá rio e m u m a ú n ic a d e lin h a
in íc io e fim d e c o m e n tá rio
Ch a r a c t e r Se t d o PL /SQL
Caracteres são agrupados, formando
unidades léxicas, que são os menores
componentes individuais da linguagem.
Uma unidade léxica pode formar:
identificadores
literais
delimitadores
comentários
Ident ific adores
Identificador é um nome para um objeto
PL/ SQL.
constante, variável, exception, procedimento,
função, package, registro, tabela PL/ SQL, cursor
e palavra reservada.
Até 30 caracteres.
Tem que começar com uma letra.
Pode incluir $, _ e # .
Não pode conter espaços.
Lit erais
Literal é um valor não representado por
um identificador; é simplesmente um
valor.
Número
415, 21.6 ou NULL
String
Esta é uma frase , 01-03-97 ou NULL
Boolean
TRUE, FALSE ou NULL
De l i m i t a d o r e s
Delimitador Ponto e vírgula (;)
Indica o final de um comando
IF salario < min_salario
THEN
salario := salario + salario * .25;
END IF;
Co m e n t á r i o s
Comentário de uma única linha
IF salario < min_salario(1994) -- retorna min salário do ano
THEN
salario := salario * .25;
END IF;
Comentário de múltiplas linhas
PROCEDURE calcula_receita (empresa IN NUMBER) IS
/ * Programa alterado em 23/ 9/ 94
Analista Responsável: Manoel de Souza * /
...
Es t r u t u r a d e u m B l o c o
A estrutura da linguagem PL/ SQL é
orientada a blocos
Modularização
um bloco é a unidade básica de trabalho da
qual procedimentos e funções são construídos
Escopo
o bloco oferece um escopo ou contexto para
objetos logicamente relacionados
Se ç õ e s d e u m B l o c o
Cabeçalho (Header)
determina o modo como o bloco deve ser chamado
Seção de Declaração
onde declaramos variáveis, cursores e sub-blocos e
seções de exceção
Seção de Execução
parte que contém o código a ser executado
Seção de Exceções (Exceptions)
manipula exceções ao processamento normal
(condições de aviso e erro).
Se ç õ e s d e u m B l o c o
Cabeçalho
Cabeçalho
IS
Seção
Seçãode
deDeclaração
Declaração
BEGIN
Seção
Seçãode
deExecução
Execução
EXCEPTION
Seção
Seçãode
deExceptions
Exceptions
END;
Se ç õ e s d e u m B l o c o
Blocos Anônimos
Não possuem nome
DECLARE
Seção
Seçãode
deDeclaração
Declaração
BEGIN
Seção
Seçãode
deExecução
Execução
EXCEPTION
Seção
Seçãode
deExceptions
Exceptions
END;
Bloc os Aninhados
Um bloco pode conter um ou mais
sub-blocos de código.
As variáveis dos blocos
externos podem ser
referenciadas nos
blocos internos, porém
não o contrário.
PROCEDURE calcula_totais
IS
total_ano NUMBER;
BEGIN
total_ano := 0;
DECLARE
total_mês NUMBER;
BEGIN
total_mês := total_ano / 12;
END;
END;
Variáveis e
Da d o s d e Pr o g r a m a
Variáveis
Atributos de uma variável
nome, tipo e valor
Nome
Pode ter até 30 caracteres
Tem que começar com uma letra
Constante
Tipo especial de variável
Valor é constante
T i p o s d e Da d o s
Número BINARY_INTEGER
Caractere CHAR
DEC
CHARACTER
DECIMAL
LONG
DOUBLE PRECISION
LONG RAW
FLOAT
RAW
INT
ROWID
INTEGER
STRING
NATURAL
VARCHAR
NUMBER
VARCHAR2
POSITIVE
Boleano
BOOLEAN
REAL
Data-hora DATE
SMALLINT
T i p o s d e Da d o s
Existem no banco de dados: Number, Char,
Long, Long Raw, Raw, Rowid, Varchar2, Date.
Binary_Integer: Utilizado para armazenar
inteiros com sinal. Com intervalo de:
-2147483647 .. 2147483647
Subtipos:
Natural (de 0 .. 2147483647)
Positive (de 1 .. 2147483647)
T i p o s d e Da d o s N u m é r i c o s
Utilize NUMBER para armazenar números
(inclusive ponto-flutuante)
Precisão Máxima: 38 dígitos
1.0E-129 até 9.999E125
Declaração de uma variável numérica:
NUMBER ( precisão, escala )
precisão: número total de dígitos
escala: número de dígitos a direita ou
esquerda do ponto decimal em que o
arredondamento ocorrerá.
T i p o s d e Da d o s N u m é r i c o s
Se a escala é positiva, então a escala
determina que o ponto onde o
arredondamento ocorre está a direita do ponto
decimal.
Se a escala é negativa, então a escala
determina que o ponto onde o
arredondamento ocorre está a esquerda do
ponto decimal.
Se a escala é zero, então o arredondamento
ocorre para a parte inteira do número.
Se a escala não é especificada, então o
arredondamento não ocorre.
T i p o s d e Da d o s N u m é r i c o s - Ex e m p l o s
EXEMPLO:
V ALOR
12345.6784
1234567891.23
1567899
53.35
F ORMATO
NUMBER(10,3)
NUMBER(10,3)
NUMBER(10,-6)
NUMBER(10,-6)
R ESULTADO
12345.678
ERROR
2000000
0
Su b t i p o s N u m é r i c o s
Pr é -d e f i n i d o s
Subtipo
DEC (prec, esc)
DECIMAL(prec, esc)
DOUBLE
PRECISION
FLOAT
INT
INTEGER
REAL
SMALLINT
Compatibilidade Correspondente no
Oracle
ANSI
NUMBER(prec, esc)
IBM
ANSI
NUMBER(prec, esc)
NUMBER
ANSI, IBM
ANSI
ANSI, IBM
ANSI
ANSI, IBM
NUMBER
NUMBER(38)
NUMBER(38)
NUMBER
NUMBER(38)
Ca r a c t e r e s
CHAR
Subtipos: Character e string.
especifica que a variável tem um tamanho fixo
pode- se especificar o tamanho máximo (1 até
32767)
se o tamanho não for especificado, o valor padrão
é 1 (um).
espaços em branco são adicionados ao final da
variável, se esta armazenar uma cadeia de
caracteres de tamanho menor que o máximo.
Ca r a c t e r e s
VARCHAR2 e VARCHAR
armazenam cadeias de caractere de tamanho
variável.
pode- se especificar o tamanho máximo (1 até
32767)
VARCHAR2 e VARCHAR são sinônimos
(visando a compatibilidade com bancos de
dados ANSI).
Recomendação da Oracle: utilize VARCHAR2
Ca r a c t e r e s
LONG
armazenam cadeias de caractere de tamanho
variável, de até 32760 caracteres.
recomendação: utilize VARCHAR2.
RAW
armazena dados binários de até 32767 bytes
o PL/ SQL não tenta interpretar os dados
LONG RAW
armazena dados binários de até 32760 bytes
Ro w i d
No database Oracle, ROWID é uma
pseudocoluna, pertencente a toda tabela.
Internamente gerado, ocupando 6 bytes.
ROWID em PL/ SQL é um subtipo do CHAR
com tamanho fixo.
BBBBBBB.RRRR.FFFF
bloco no database file, linha no bloco e
database file
B o o l e a n o (B OOL EA N )
Tipo de dados lógico (não existe
correspondente no servidor Oracle).
Pode assumir os valores TRUE, FALSE ou
NULL.
Da t a -h o r a (DA T E)
Uma variável do tipo DATE armazena tanto
informações sobre data quanto sobre hora.
Valor de tamanho fixo, que ocupa 7 bytes.
DATE armazena as seguintes informações:
século, ano, mês, dia, hora, minuto e
segundo
Datas válidas para uma variável data estão
entre 1 jan de 4712 AC a 31 dez de 4712 DC.
Podem ser feitas operações aritméticas sobre
um valor do tipo DATE.
Co n v e r s ã o e n t r e T i p o s
Sempre que o PL/ SQL efetua uma operação
envolvendo um ou mais valores, ele primeiro
converte os dados para o formato correto
para a operação.
Conversão Explícita
é usada uma função de conversão pré- definida
Conversão Implícita
sempre que necessário, o PL/ SQL tenta converter
os valores para efetuar a operação
V a l o r e s N u l o s e m PL /SQL
NULL nunca é igual a qualquer outra coisa
IF nome = NULL THEN ... -- ERRADO
Quando usados uma função utilizando um
valor nulo, geralmente recebemos como
resultado um outro valor nulo.
nome := NULL;
IF LENGTH(nome) = 0 THEN -- Não funciona
Maneira correta da comparação:
< identificador> IS NULL
< identificador> IS NOT NULL
V a l o r e s N u l o s e m PL /SQL
Resultado de funções com argumento NULL:
Concatenação
Existem duas maneiras de efetuarmos uma
concatenação: função CONCAT ou operador | |
Nos dois casos, o valor NULL é ignorado
Função NVL
nova_desc := NVL(antiga_desc, Não aplicavel );
Função REPLACE
REPLACE( a.b.c.d.e.f. , . , NULL) = > abcdef
De c l a r a ç ã o d e V a r i á v e i s
Antes de fazer qualquer referência a uma
variável, a mesma deve ser declarada.
Sintaxe
< nome_var> < tipo> [ atribuição de valor padrão]
De c l a r a ç ã o d e V a r i á v e i s
Exemplos
data_admissao DATE;
achou BOOLEAN;
total NUMBER(15,2);
paragrafo VARCHAR2(2000);
prox_aumento CONSTANT DATE := 15-APR- 96 ;
limite NUMBER DEFAULT 3;
nom_empr VARCHAR2(50) NOT NULL DEFAULT
PCS R US
Co n t r o l e Co n d i c i o n a l
e Se q u e n c i a l
Co m a n d o s I F...
Permitem que seja construída uma
lógica condicional nas aplicações.
IF <condição> THEN
<comandos> - TRUE
END IF;
IF <condição> THEN
<comandos> - TRUE
ELSE
<comandos> - FALSE
END IF;
IF <condição> ...
ELSIF <condição>
ELSE
<comandos>
END IF
Determina se os comandos entre o
THEN e o END IF serão executados,
de acordo com a condição.
Baseado na condição informada, será
executado o código entre o THEN e o
ELSE (TRUE) ou entre ELSE e o END
IF (FALSE).
seleciona uma ação dentre várias
condições mutuamente exclusivas,
executando os comandos associados à
condição.
Co m b i n a ç ã o I F-T H EN
Exemplos
IF :empresa.total > media
THEN
aplicar_desconto(:empresa.empresa_id);
END IF;
IF relatorio_pedido
THEN
imprime_relatorio( relatorio_id);
END IF;
Co m b i n a ç ã o I F-T H EN -EL SE
Exemplo
IF :cliente.total_pedido >
THEN
pedido_excedente :=
ELSE
pedido_excedente :=
END IF;
max_permitido
TRUE;
FALSE;
Co m b i n a ç ã o I F-EL SI F
Exemplo
IF salario < 10000
THEN
bonus := 2000;
ELSIF salario < 20000
THEN
bonus := 1500;
ELSIF salario < 40000
THEN
bonus := 1000;
ELSE
bonus := 500;
END IF;
Co m a n d o GOT O
Desvio incondicional para um rótulo
definido no programa.
GOTO nome_rotulo;
...
< < nome_rotulo> >
...
Pelo menos um comando deve existir
após a definição de um rótulo.
O rótulo deve ser único no escopo.
Co m a n d o GOT O
Rótulo destino deve estar no mesmo escopo
que o comando GOTO.
IF, BEGIN, Loop, módulo.
Rótulo destino deve estar na mesma parte
do bloco PL/ SQL que o comando GOTO.
ex.: um GOTO na seção executável não pode
desviar para a seção de exceptions.
O código resultante com o uso do GOTO
pode tornar-se complexo e desestruturado,
dificultando a manutenção.
Co m a n d o N U L L
Melhorar a clareza do programa
IF : report .selection = DETAIL
THEN
exec_detail_report ;
ELSE
NULL;
END IF;
Tirar o efeito de uma exception
Projeto top-down dos módulos do sistema.
Utilização conjunta com o GOTO.
SQL * Pl u s
SQL * Pl u s
Permite introduzir interativamente
instruções de SQL e blocos PL/ SQL a
partir de uma linha de comandos que são
enviadas diretamente para a base de
dados
Comandos não são sensíveis a maiúsculas
e minúsculas
V a r i á v e i s d e Su b s t i t u i ç ã o
Identificadas pelo caracter &
Substituição textual da variável antes de
enviar a instrução para o servidor
Exemplo:
select * from emp
where empno= &num_empregado;
Variáveis de Assoc iaç ão
Variáveis de memória, podendo ser
utilizadas em um ou mais blocos PL/ SQL
Tipos válidos:
VARCHAR2
CHAR
NUMBER
Não podem ser restringidas por precisão ou
escala
REFCURSOR (a partir do SQL* Plus 3.2)
Variáveis de Assoc iaç ão
Exemplo:
SQL> VARIABLE v_contador NUMBER
SQL> BEGIN
2
SELECT COUNT(*)
3
INTO :v_contador
4
FROM emp
5
WHERE empno > 1000;
6 END;
7 /
SQL> PRINT v_contador
Após a execução, a variável v_contador
conterá o resultado do select feito no bloco.
EX ECU T E
Uma chamada a stored procedures deve
ser feita através do comando EXECUTE
Exemplo:
EXECUTE minha_procedure(param1,...);
O SQL* Plus enviará o seguinte bloco PL/ SQL
para a base de dados:
BEGIN minha_procedure(param1,...); END;
Ex e c u t a r A r q u i v o s d e I n s t r u ç õ e s
Para executar quaisquer instruções SQL
ou blocos PL/ SQL
Pode-se utilizar start ou @
Exemplos:
start cria_proc.sql
start cria_func
start pacote1.pck
@funcao_teste.fnc
A extensão default é sql
SH OW ERRORS
Mostra erros de compilação armazenados
na view user_errors
Utilizado após uma tentativa de criar
stored procedures e receber a mensagem:
Warning: Procedure created with compilation errors
Ex e r c íc i o I .1
Faça um script que, dado um número inteiro,
retorne o sua raiz quadrada (p/ rodar no
SQL* Plus).
Sugestões:
utilize a função SQRT
utilize o modo de entrada de dados do SQL* Plus
(&variavel)
construa um bloco anônimo
utilize o comando DBMS_OUTPUT.PUT_LINE
Ex e r c íc i o I .2
Faça um script que calcule as raízes de uma
equação de 2o grau
Sugestões:
utilize o módulo de entrada de dados do SQL* Plus
(&variavel)
construa um bloco anônimo
Obs.: eq = ax2 + bx + c = 0
raiz1 = ( - b + sqrt (b2 - 4.a.c) ) / 2.a
raiz2 = ( - b - sqrt (b2 - 4.a.c) ) / 2.a
Loops
Co n c e i t o s
Um loop permite que um mesmo código
seja executado repetidamente.
Loop Simples
Loop FOR (p/ números e cursores)
Loop WHILE
Na maioria dos casos, uma lógica que
requer um loop pode usar qualquer das
três construções existentes.
L o o p Si m p l e s
LOOP
< comandos>
END LOOP;
O teste para terminação é feito dentro do
loop
EXIT
EXIT WHEN < condição>
L o o p Si m p l e s
Exemplos
LOOP
balanco_restante := balanco_conta(conta_id);
IF balanco_restante < 1000 THEN
EXIT;
ELSE
aplicar_balanco(conta_id, balanco_restante);
END IF;
END LOOP;
LOOP
balanco_restante := balanco_conta(conta_id);
EXIT WHEN balanco_restante < 1000;
aplicar_balanco(conta_id, balanco_restante);
END LOOP;
L o o p Si m p l e s
Quando usar
não existe a certeza de quantas vezes o loop será
executado
o loop deve executar pelo menos uma vez
L o o p FOR
FOR numérico
FOR < indice_loop> IN [ REVERSE] < menor> ..< maior>
LOOP
< comandos>
END LOOP;
O loop termina quando o código é executado o
número de vezes correspondente ao intervalo
informado
Após cada execução do bloco, o PL/ SQL verifica
se o valor atual do índice excede a diferença
entre o maior e menor número informado na faixa
L o o p FOR
FOR numérico
Quando usar:
código dentro do loop será executado um número fixo
de vezes, sem ser necessária uma interrupção
Regras
não declare o índice usado no loop
não mude o valor das variáveis usadas para informar
a faixa de valores (a faixa é analisada no início do
loop), muito menos o valor do índice
não use o comando EXIT dentro do loop FOR
L o o p FOR
FOR numérico
Exemplos
FOR contador IN REVERSE 1..10
LOOP
...
END LOOP;
FOR contador IN 1..10
LOOP
...
END LOOP;
FOR ind_calc IN ini_periodo ..
LEAST(fim_periodo,
periodo_atual)
LOOP
...
END LOOP;
L o o p WH I L E
WHILE < condição>
LOOP
< comandos>
END LOOP;
Executa até que a condição seja falsa.
Antes de cada execução do bloco dentro loop,
o PL/ SQL avalia a condição informada.
L o o p WH I L E
Quando usar
não temos certeza de quantas vezes devemos
executar o corpo do loop
desejamos interromper o loop de acordo com
uma condição
não necessariamente temos que executar o loop
pelo menos uma vez
L o o p WH I L E
WHILE total <= 25000 LOOP
...
SELECT sal INTO salary FROM emp WHERE ...
total := total + salary;
END LOOP;
Este exemplo garante a execução ao
menos uma vez:
done := FALSE;
WHILE NOT done LOOP
sequence_of_statements;
done := boolean_expression;
END LOOP;
Re g i s t r o s e m PL /SQL
Co n c e i t o s
Um registro em PL/ SQL é bastante
similar à estrutura de linhas em uma
tabela.
Um registro é uma estrutura de dados
composta.
O registro como um todo não tem um
valor. Cada componente ou campo é
que o possui.
T i p o s d e Re g i s t r o
Baseado em Tabela
cada campo corresponde a uma coluna em
uma tabela, inclusive com o mesmo nome
Baseado em Cursor
cada campo corresponde a uma coluna ou
expressão no comando SELECT de um
cursor
Definido pelo Programador
cada campo é definido explicitamente
(nome e tipo) através do comando TYPE
De c l a r a ç ã o d e u m Re g i s t r o
Baseado em Tabelas
< nome_reg> < nome_tabela> %ROWTYPE;
DECLARE
empresa_reg empresa%ROWTYPE;
Baseado em Cursores
< nome_reg> < nome_cursor> %ROWTYPE;
DECLARE
empresa_reg empresa_cur%ROWTYPE;
De c l a r a ç ã o d e u m Re g i s t r o
Definido pelo Programador
TYPE < nome_tipo> IS RECORD
( < nome_campo1> < tipo_dado1> ,
< nome_campo2> < tipo_dado2> ,
...
< nome_campoN> < tipo_dadoN> )
TYPE cliente_regtipo IS RECORD
(cliente_id NUMBER(5),
cliente_nome cliente.nome%TYPE,
total_vendas NUMBER(15,2) );
T a b e l a s PL /SQL
De f i n i ç ã o
Como um array, uma tabela PL/ SQL é uma
coleção ordenada de elementos de um mesmo
tipo.
Uma tabela PL/ SQL não tem limites de
tamanho, pode ser incrementada
dinamicamente.
O índice de acesso da tabela não precisa ser
um número sequencial. Por exemplo, pode-se
usar uma série como número do empregado
(como 7369, 7499, 7521, 7566, )
De f i n i n d o u m a T a b e l a PL /SQL
TYPE table_type_name IS TABLE OF
datatype [NOT NULL]
[INDEX BY BINARY_INTEGER];
onde table_type_name é um tipo especificado
pelo usuário.
Na versão 8 a cláusula INDEX BY é
opcional.
Ex e m p l o s
DECLARE
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE NOT NULL
INDEX BY BINARY_INTEGER;
DECLARE
TYPE TimeRecTyp IS RECORD (
hour SMALLINT := 0,
minute SMALLINT := 0,
second SMALLINT := 0);
TYPE TimeTabTyp IS TABLE OF TimeRecTyp
INDEX BY BINARY_INTEGER;
Re f e r e n c i a n d o T a b e l a s PL /SQL
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
BEGIN
...
IF emp_tab(1).job = CLERK THEN ...
END;
U s a n d o T a b e l a s PL /SQL
DECLARE
TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabTyp;
BEGIN
/* Select entire row into record stored by first element. */
SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
IF dept_tab(1).dname = ACCOUNTING THEN ...
...
END;
U s a n d o T a b e l a s PL /SQL
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i BINARY_INTEGER := 0;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
i := i + 1;
/* Fetch entire row into record stored by its element. */
FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
process data record
END LOOP;
CLOSE c1;
END;
A t r i b u t o s d e T a b e l a : COU N T
Retorna o número de elementos na
tabela.
...
IF ename_tab.COUNT = 50 THEN
...
END;
A t r i b u t o s d e T a b e l a : DEL ET E
Este atributo tem 3 formas:
tabela.DELETE remove todos os elementos da
tabela;
tabela.DELETE(n) remove o elemento n da
tabela;
tabela.DELETE(m,n) remove o range m .. n.
É uma instrução completa por si só; não é
chamada como parte de uma expressão
A t r i b u t o s d e T a b e l a : EX I ST S
Retorna TRUE se existir uma linha com
índice i na tabela, caso contrário retorna
FALSE.
IF sal_tab.EXISTS(i) THEN
sal_tab(i) := sal_tab(i) + 500;
ELSE
RAISE salary_missing;
END IF;
...
A t r i b u t o s d e T a b e l a : FI RST e L A ST
Retornam o índice da primeira e da última
linha da tabela, respectivamente.
...
FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
...
END LOOP;
A primeira linha é a que tem o índice mais
baixo e a última, o mais elevado.
A t r i b u t o s d e T a b e l a : N EX T e PRI OR
Retornam o índice do elemento seguinte
ou anterior da tabela, respectivamente.
DECLARE
...
i BINARY_INTEGER;
BEGIN
..
i := any_tab.FIRST;
WHILE i IS NOT NULL LOOP
...
process any_tab(i)
i := any_tab.NEXT(i);
END LOOP;
END;
SQL e m PL /SQL
I n s t r u ç õ e s d e SQL
Podem dividir-se em seis categorias:
DML: linguagem de manipulação de dados
DDL: linguagem de definição de dados
Controle de transações
Controle de sessões
Controle do sistema
SQL incorporado (para pré- compiladores)
SQL e m PL /SQL
Num programa PL/ SQL só podem ser
usadas as seguintes instruções:
DML: select , insert , update, delete
Controle de transações: commit , rollback,
savepoint
Existe uma alternativa para o uso de
instruções DDL em PL/ SQL
A package DBMS_SQL, disponível a partir da
versão 2.1 permite a criação de SQL dinâmico
DM L : Se l e c t
Busca informações do banco de dados
para variáveis PL/ SQL
SELECT emp.ename
INTO v_ename
FROM emp
WHERE emp.empno = 7902;
Deve retornar somente uma linha
A cláusula INTO só é usada em blocos
PL/ SQL
DM L : I n s e r t
Insere uma linha na tabela
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm,
deptno)
VALUES
(1, 'ALBERT', 'SALESMAN', 7698, SYSDATE,
1432, 260, 30);
INSERT INTO emp
SELECT * FROM emp;
DM L : U p d a t e
Altera o conteúdo de uma ou mais linhas
de uma tabela
UPDATE emp
SET sal = sal * 1.2
WHERE empno = 1;
DM L : De l e t e
Elimina uma ou mais linhas de uma tabela
DELETE FROM emp
WHERE empno = 1;
Cl á u s u l a WH ERE
Nas instruções SELECT, UPDATE e
DELETE esta cláusula serve para restringir
o conjunto de linhas sobre as quais a
operação será executada.
Constituída por condições, normalmente
de comparação, separadas pelos
operadores AND, OR, NOT
Re f e r ê n c i a s d e t a b e l a s
As operações de DML referenciam uma
tabela, de uma forma geral, no formato:
[ owner.] tabela[ @dblink]
onde:
owner
dblink
- usuário onde a tabela foi criada
- denominação de uma conexão a um
banco de dados remoto
Si n ô n i m o s
Utilizados para facilitar o acesso aos
objetos do banco de dados, evitando o
uso de owner e dblink para identificá-los.
Exemplo:
CREATE PUBLIC SYNONYM empregado FOR
owner1.emp@bd1;
Após isto pode-se usar:
SELECT * FROM empregado;
em vez de ter que fazer:
SELECT * FROM owner1.emp@bd1;
Ps e u d o -c o l u n a s
Funções adicionais que só podem ser
chamadas a partir de instruções SQL:
CURRVAL e NEXTVAL
LEVEL
ROWID
ROWNUM
Ps e u d o -c o l u n a s : CU RRV A L e N EX T V A L
Utilizadas com sequências (objetos Oracle
para gerar números únicos)
sequência.CURRVAL
retorna o valor atual da sequência
sequência.NEXTVAL
retorna o próximo valor da sequência
Exemplo:
SELECT my_seq.NEXTVAL from dual;
Ps e u d o -c o l u n a s : L EV EL
Utilizado dentro de uma instrução SELECT
que implementa uma pesquisa de árvore
hierárquica numa tabela utilizando as
cláusulas START WITH e CONNECT BY.
Esta pseudo-coluna retorna o nível atual
da árvore.
Ps e u d o -c o l u n a s : ROWI D
Retorna o endereço físico de uma linha da
tabela, num valor do tipo ROWID
Um SELECT utilizando-se de ROWID na cláusula
WHERE é o método mais otimizado de se
recuperar uma linha
Exemplo:
v_rowid ROWID;
...
SELECT ROWID INTO v_rowid FROM emp;
...
UPDATE emp set ... WHERE emp.rowid = v_rowid;
Ps e u d o -c o l u n a s : ROWN U M
Retorna o número atual da linha num
SELECT
Utilizado principalmente na cláusula
WHERE para limitar as linhas a serem
consideradas
SELECT * FROM emp WHERE ROWNUM < 3;
O valor ROWNUM é atribuído a uma linha
antes de ser efetuada uma ordenação
(ORDER BY)
Pr i v i l é g i o s d e A c e s s o
Privilégios de Objeto
Para efetuar operações num objeto
Privilégios de Sistema
Para efetuar operações numa classe de
objetos
GRA N T
Para dar privilégios de acesso
Objeto:
GRANT privilégio ON objeto TO usuário
[WITH GRANT OPTION]
Sistema:
GRANT privilégio TO usuário
[WITH ADMIN OPTION]
REV OK E
Para revogar privilégios de acesso
Objeto:
REVOKE privilégio ON objeto FROM usuário
[CASCADE CONSTRAINTS]
Sistema:
REVOKE privilégio FROM usuário
Pe r f i s d e Gr u p o
Os privilégios comuns podem ser agrupados em
ROLES, para facilitar a concessão para vários
usuários que possuem o mesmo perfil
Em vez de dar privilégios para cada usuário:
Cria- se uma role: CREATE ROLE role
Concedem- se os privilégios: GRANT ... TO
role
Atribuem- se os usuários para essa role
GRANT role TO usuário
PUBLIC: perfil genérico para todos os usuários
Co n t r o l e d e T r a n s a ç õ e s
COMMIT
Salva as operações da transação e libera
locks. As operações são visíveis a outras
sessões
ROLLBACK
Desfaz as operações e libera locks
SAVEPOINT
Ponteiro para marcar o ínicio para onde
um ROLLBACK pode ser efetuado
Fu n ç õ e s d e SQL i n t e g r a d a s
Fu n ç õ e s p a r a Ca r a c t e r e s
Nome
ASCII
Descrição
retorna o código ASCII de um caractere
CHR
retorna o caractere associado ao código ASCII
CONCAT
concatena duas strings
INITCAP
transforma a primeira letra de cada palavra em
maiúscula
INSTR
LENGTH
retorna a localização em uma string da
substring especificada
retorna o tamanho da string
LOWER
converte todas as letras para minúsculas
LPAD
adiciona uma string à esquerda
Fu n ç õ e s p a r a Ca r a c t e r e s
Nome
LTRIM
REPLACE
RPAD
RTRIM
Descrição
retira do lado esquerdo da string todos os caracteres recebidos
como parâmetro
substitui uma sequência de caracteres por um conjunto
diferente de caracteres
adiciona os caracteres especificados à direita da string
retira do lado direito da string todos os caracteres recebidos
como parâmetro
SOUNDEX
retorna a representação fonética de uma string
SUBSTR
retorna a porção especificada da string
TRANSLATE traduz caracteres únicos em uma string por um caracter
diferente
UPPER
converte todas as letras para maiúsculas
Fu n ç õ e s p a r a Ca r a c t e r e s
Exemplos
FUNCTION INSTR(string1 IN VARCHAR2,
string2 IN VARCHAR2
[ , pos_ini IN NUMBER := 1
[ , nth_ocorrencia IN NUMBER := 1] ] )
RETURN NUMBER
INSTR( Estou procurando uma palavra , uma ) = >
18
FUNCTION LPAD(string1 IN VARCHAR2,
tamanho_pad IN NUMBER
[ , string_pad IN VARCHAR2] )
RETURN VARCHAR2
LPAD( 55 , 10, 0) = > 0000000055
LPAD( 12345678 , 5, 0 ) = > 12345
Fu n ç õ e s p a r a Ca r a c t e r e s
Exemplos
FUNCTION LTRIM(string1 IN VARCHAR2
[ , trim_string IN VARCHAR2] )
RETURN VARCHAR2
LTRIM(
Eu gosto de pizza ) = > Eu gosto de pizza
FUNCTION SUBSTR(string_in IN VARCHAR2,
pos_ini IN NUMBER
[ , tam_substr_in IN
NUMBER] )
RETURN VARCHAR2
SUBSTR( Eu gosto de pizza , 4, 5) = > gosto
SUBSTR( Eu gosto de pizza , -1) = > a
Fu n ç õ e s p a r a Da t a s
Nome
ADD_MONTHS
Descrição
adiciona o número de meses especificado à data
LAST_DAY
retorna o último dia do mês da data especificada
MONTHS_BETWEEN retorna o número de meses entre duas datas
NEW_TIME
retorna o valor data/hora, com o tempo alterado
de acordo com o time zone especificado
NEXT_DAY
retorna a data do primeiro dia de semana
especificado que é maior que a data
ROUND
SYSDATE
TRUNC
retorna a data arrendondada para o formato
especificado
retorna a hora/data corrente no Oracle Server
trunca a data da sua porção hora, de acordo com
o formato especificado
Fu n ç õ e s p a r a Da t a s
Exemplos
LAST_DAY(data_in IN DATE) RETURN DATE
LAST_DAY(SYSDATE) - SYSDATE = > número de
dias até o final do mês.
NEXT_DAY(data_in IN DATE,
nome_dia IN VARCHAR2)
RETURN DATE
NEXT_DAY( 01-JAN-1997 , MONDAY )
= > 06-JAN-1997
Fu n ç õ e s N u m é r i c a s
Nome
ABS
COS / ACOS / COSH
SIN / ASIN / SINH
TAN / ATAN TANH
CEIL
Descrição
valor absoluto do número
cosseno; cosseno inverso; cosseno hiperbólico
seno; seno inverso; seno hiperbólico
tangente; tangente inversa; tangente hiperbólica
retorna o teto inteiro de um número
EXP(n)
FLOOR
LN(a)
e elevado à n-ésima potência
maior inteiro menor ou igual ao número
logaritmo natural de a.
LOG(a, b)
MOD(a, b)
logaritmo na base a de b.
resto da divisão de a por b.
Fu n ç õ e s N u m é r i c a s
Nome
POWER(a,b)
ROUND(a,b)
Descrição
a elevado à b-ésima potência
retorna a arredondado em b casas decimais
SIGN(a)
retorna 1 se a > 0, 0 se a = 0 e -1 se a < 0
SQRT
raiz quadrada de um número
TRUNC(a, [b])
a truncado até b casas decimais
Ou t r a s Fu n ç õ e s
Nome
DECODE
DUMP
GREATEST
LEAST
NVL
SQLCODE
Descrição
retorna o resultado de uma comparação no estilo if-then-else
retorna uma string contendo um dump da expressão especificada
retorna o maior dos valores especificados
retorna o menor dos valores especificados
substitui o valor NULL por um outro valor
retorna o número do erro do Oracle para a exception mais recente
SQLERRM
retorna a mensagem de erro do Oracle para a exception mais
recente
retorna o User ID da sessão corrente no Oracle
UID
USER
USERENV
VSIZE
retorna o nome do usuário logado
retorna uma string contento infos sobre a sessão corrente no Oracle
retorna o número de bytes da representação interna do valor
especificado
Ou t r a s Fu n ç õ e s
Exemplos
FUNCTION SQLCODE RETURN INTEGER
FUNCTION SQLERRM RETURN VARCHAR2
EXCEPTION
WHEN OTHERS THEN
MESSAGE( Error | | TO_CHAR(SQLCODE)
| | : | | SQLERRM);
Fu n ç õ e s d e Co n v e r s ã o
Nome
Descrição
CHARTOROWID converte uma string para ROWID
CONVERT
converte uma string de uma character set
para outro
HEXTORAW
converte de hexadecimal para o formato
RAW
RAWTOHEX
converte de raw para hexadecimal
ROWIDTOCHAR converte de ROWID para uma string
TO_CHAR
TO_DATE
TO_NUMBER
converte um número ou data para string
converte uma string para data
converte uma string para número
Fu n ç ã o T O_CH A R
FUNCTION TO_CHAR(param IN { DATE/ NUMBER} ,
[ , formato IN VARCHAR2
[ , nls_language IN
VARCHAR2] ] )
RETURN VARCHAR2
TO_CHAR(SYSDATE, Month DD, YYYY )
= > March 10,1997
TO_CHAR(564.70, $999.9 ) = > $564.7
TO_CHAR(564.70, $0000999.9 ) = > $0000564.7
Fu n ç ã o T O_DA T E
FUNCTION TO_DATE(param IN { VARCHAR2| NUMBER}
[ , formato IN VARCHAR2
[ , nls_language IN VARCHAR2 ] ] )
RETURN DATE
TO_DATE( 123198 , MMDDYY ) = > 31-DEC- 1998
TO_DATE( 16/ 7/ 94 , DD/ MM/ YY ) = > 16-JUL- 1994
Fu n ç ã o T O_N U M B ER
FUNCTION TO_NUMBER(string_in IN
CHAR| VARCHAR2}
[ , formato IN VARCHAR2
[ , nls_language VARCHAR2] ] )
RETURN NUMBER
TO_NUMBER( 123.23 ) = >
TO_NUMBER( abcdef ) = >
123.23
ERRO
Fu n ç õ e s d e Gr u p o
Somente para SELECT com GROUP BY
Nome
Descrição
AVG
COUNT
MAX
MIN
STDDEV
retorna a média dos valores da coluna
retorna o número de linhas que a coluna não é nula
retorna o valor máximo da coluna
retorna o valor mínimo da coluna
retorna o desvio padrão da coluna
SUM
VARIANCE
retorna a soma dos valores da coluna
retorna a variância estatística da coluna
Cu r s o r e s
Co n c e i t o s
Um cursor pode ser encarado como um
ponteiro para a tabela virtual no banco de
dados representada pelo comando
SELECT associado.
Ex.:
CURSOR empregado_cur IS
SELECT * FROM empregado;
OPEN empregado_cur;
FETCH empregado_cur INTO empregado_rec;
CLOSE empregado_cur;
Op e r a ç õ e s e m Cu r s o r e s
OPEN
abre o cursor, faz o parse e o bind da
consulta associada, identificando o resultado
o cursor é posicionado antes da primeira linha.
FETCH
busca a linha corrente do cursor e o posiciona na
próxima
CLOSE
fecha o cursor e libera a memória alocada.
T i p o s d e Cu r s o r e s
Cursores Estáticos
sempre referenciam um comando SQL, que é
conhecido em tempo de compilação.
Variáveis Cursores
a variável referencia um objeto cursor e pode
referenciar diferentes comandos SQL em
ocasiões diferentes.
nova feature do PL/ SQL.
T i p o s d e Cu r s o r e s
Implícitos
o PL/ SQL utiliza um cursor implícito sempre que
um comandos SQL é executado diretamente no
código, desde que o código não utilize um cursor
explícito
usados em cada UPDATE, INSERT ou DELETE
são menos eficientes que cursores explícitos
mais suscetíveis a erro
T i p o s d e Cu r s o r e s
Explícitos
comando SELECT explicitamente definido na
seção de declaração, sendo um nome
associado a ele
usados quando desejamos recuperar mais de
uma linha de resultado
não existem para comandos de UPDATE,
INSERT e DELETE
De c l a r a ç ã o
CURSOR nome_cursor [ ( [ parâmetro [ ,
parâmetro ...] ) ]
[ RETURN especificação_retorno ]
IS comando_SELECT;
V a r i á v e i s e m u m Cu r s o r
Nome do Cursor não é uma variável
No PL/ SQL, a lista de itens no SELECT pode
conter colunas, variáveis do PL/ SQL e
variáveis associadas (p.e. Oracle Forms).
DECLARE
bonus NUMBER := 100;
CURSOR empregado_cur IS
SELECT empregado_id, salario + bonus,
: revisao.avaliacao
FROM empregado
WHERE dt _contrat < ADD_MONTHS(SYSDATE, -36);
Pr e c e d ê n c i a d e u m I d e n t i f i c a d o r
Em um cursor, existe precedência do nome
da coluna sobre o nome de uma variável
PL/ SQL.
PROCEDURE melhorar_SQL
IS
salario NUMBER := 1000;
CURSOR dobrar_sal_cur IS
SELECT salario + salario
FROM empregado
WHERE dt _contrat < ADD_MONTHS(SYSDATE,-36);
BEGIN
A c l a ú s u l a RET U RN
Somente pode ser usada para cursores
que estão contidos em um package.
A claúsula RETURN pode ser feita com as
seguintes estruturas:
Um record definido a partir de uma tabela,
usando %ROWTYPE
Um record definido a partir de um record prédefinido pelo progamador
Ex e m p l o d e U s o d a c l a ú s u l a RET U RN
Primeiro a definição da Package
PACKAGE empresa IS
CURSOR empresa_cur (id IN NUMBER)
RETURN empresa%ROWTYPE;
END empresa;
Depois a definição da Package Body
PACKAGE body empresa IS
CURSOR empresa_cur (id IN NUMBER)
RETURN empresa%ROWTYPE IS
SELECT * FROM empresa
WHERE empresa_id = id;
END empresa;
Po r q u e c o l o c a r c u r s o r n u m a Pa c k a g e ?
Uma package é uma coleção de objetos
logicamente relacionados
Agrupando os códigos, torna-se mais fácil
a identificação e uso dos códigos pelo
programador
Cursores em packages são essencialmente
caixas pretas
O programador não precisa saber como
um cursor é recuperado
A b r i n d o Cu r s o r e s
OPEN < nome_cursor> [ (parâmetro [ ,
parâmetro ...] ) ] ;
O comando OPEN não recupera linhas.
Todos os fetches refletirão os dados
exatamente como da ocasião em que o
cursor foi aberto.
Re c u p e r a n d o Da d o s d e Cu r s o r e s
FETCH < nome_cursor> INTO
< registro_ou_lista_variáveis>
Exemplo:
FETCH empresa_cur INTO empresa_reg;
FETCH max_sal_cur INTO max_sal;
FETCH empr_cur INTO empr_nome(1),
dt _contrat ,
:depto.min_salario;
Fe c h a n d o Cu r s o r e s
CLOSE < nome_cursor>
Libera área de memória (SGA).
Libera todo bloqueio (lock) causado pelo
cursor.
Existe um limite máximo de cursores que
podem ser abertos no SGBD Oracle.
Um cursor é automaticamente fechado
quando o seu escopo é abandonado.
A t r i b u t o s d e Cu r s o r e s
Nome
Descrição
%FOUND
retorna TRUE se o registro foi
recuperado com sucesso
%NOTFOUND recupera TRUE se o registro não foi
recuperado com sucesso
%ROWCOUNT retorna o número de registros
recuperados até o momento
%ISOPEN
retorna TRUE se o cursor estiver
aberto
A t r i b u t o s d e Cu r s o r e s
%FOUND
OPEN pedido_cur;
FETCH pedido_cur INTO pedido_id, empresa_id;
IF pedido_cur%FOUND THEN
:pedido.num_pedidos := :pedido.num_pedidos + 1;
END IF;
...
A t r i b u t o s d e Cu r s o r e s
%NOTFOUND
Oposto ao %FOUND
Muito utilizado para terminação de loops
EXIT WHEN empresa_cur%NOTFOUND;
A t r i b u t o s d e Cu r s o r e s
%ROWCOUNT
DECLARE
CURSOR emp_cur IS
SELECT nome, salario
FROM empregado
ORDER BY salario DESC;
emp_reg emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur ;
LOOP
FETCH emp_cur INTO emp_reg;
EXIT WHEN emp_cur%ROWCOUNT > 10 OR
emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( emp_reg.nome | | - | |
emp_reg.salario);
END LOOP;
END;
A t r i b u t o s d e Cu r s o r e s
%ISOPEN
IF NOT emp_cur%ISOPEN THEN
OPEN emp_cur;
END IF;
A t r i b u t o s d e Cu r s o r e s I m p l íc i t o s
Quando o RDBMS abre um cursor ou
executa um insert, update ou delete, ele
torna uma das seguintes variáveis
habilitadas:
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
Pa r â m e t r o s e m Cu r s o r e s
Um parâmetro faz com que um cursor se torne
mais reutilizável.
DECLARE
CURSOR empresa_cur (categoria_in VARCHAR2)
IS
SELECT nome, categoria, contato
FROM empresa
WHERE categoria = UPPER(categoria_in);
Podemos definir um valor padrão para um
parâmetro.
CURSOR emp_cur( emp_in NUMBER := 0)
SEL ECT ... FOR U PDA T E
Quando um comando SELECT ... FOR
UPDATE é executado, o Oracle
automaticamente bloqueia a linha de
maneira exclusiva
Ninguém conseguirá alterar estes registros
antes de um ROLLBACK ou COMMIT
CURSOR emp_cur IS
SELECT nome, salario
FROM empregado
WHERE salario < 100
FOR UPDATE
WH ERE CU RREN T OF
Esta cláusula é utilizada para comandos
DELETE e UPDATE dentro de um cursor.
O registro mais recentemente recuperado é
apagado ou atualizado
Exemplo:
FETCH emp_cur INTO emp_reg;
...
UPDATE empregado SET salario := salario + bonus
WHERE CURRENT_OF emp_cur;
V a r i á v e i s Cu r s o r e s
Disponível a partir das releases 2.2 e 2.3.
Possibilidade de passar como parâmetro o
resultado de consultas para outros
programas.
Variáveis cursores são como ponteiros do
C ou Pascal, na qual um endereço de
memória é assinalado. Declarando uma
variável cursor se cria um ponteiro, não
um objeto.
Po r q u e u s a r v a r i á v e i s c u r s o r ?
Usa-se uma variável cursor para passar o
resultado de uma query entre stored
subprograms e aplicações client.
Por exemplo, uma aplicação Client, Oracle
Forms, e Oracle Server podem ambos se
referenciar à mesma área de trabalho.
V a r i á v e i s Cu r s o r e s
OPEN
OPENcursor
cursor
FETCH
FETCHreg1
reg1
FETCH
FETCHreg2
reg2
FETCH
FETCHreg3
reg3
CLOSE
CLOSEcursor
cursor
V a r i á v e i s Cu r s o r e s
Exemplo
DECLARE
TYPE empresa_curtipo IS REF CURSOR
RETURN empresa%ROWTYPE;
empresa_curvar empresa_curtipo;
BEGIN
OPEN empresa_curvar FOR SELECT *
FROM empresa;
...
De f i n i n d o u m a V a r i á v e l Cu r s o r
TYPE ref_type_name IS REF CURSOR
RETURN return_type;
onde ref _type_name é o nome da variável
especificada para uso subsequente e return_type
deve representar um record ou uma row na tabela.
Exemplo:
DECLARE
TYPE DeptCurTyp IS REF CURSOR
RETURN dept %ROWTYPE;
Em u m a St o r e d Pr o c e d u r e
CREATE PACKAGE emp_data AS
TYPE GenericCurTyp IS REF CURSOR;
TYPE EmpCurTyp IS REF CURSOR
RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv
(emp_cv IN OUT EmpCurTyp,
choice IN NUMBER);
END emp_data;
Em u m a St o r e d Pr o c e d u r e
(c o n t i n u a ç ã o ...)
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv
(emp_cv IN OUT EmpCurTyp,
choice IN NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp
WHERE comm IS NOT
NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp
WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp
WHERE deptno = 20;
END IF;
END open_emp_cv;
END emp_data;
L o o p Si m p l e s p a r a Cu r s o r e s
As instruções devem ser feitas
explicitamente:
Abrir o cursor
Colocar o fetch dentro do loop
Estabelecer a condição para fim do loop
Fechar o cursor
Requer maior atenção do desenvolvedor
Maior possibilidade de ocorrer erro
L o o p Si m p l e s p a r a Cu r s o r e s
Exemplo
DECLARE
CURSOR cur_emp IS ...
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO ...
EXIT WHEN cur_emp%NOTFOUND;
-- processar informações do cursor
END LOOP;
CLOSE cur_emp;
END;
L o o p FOR p a r a Cu r s o r e s
FOR indice_registro IN nome_cursor
LOOP
< comandos>
END LOOP;
O loop termina incondicionalmente quando
todos os registros do cursor forem
recuperados
A cada execução do loop, o PL/ SQL verifica o
atributo %NOTFOUND
L o o p FOR p a r a Cu r s o r e s
Exemplo
DECLARE
CURSOR ocupacao_cur IS
SELECT hosp_id, nm_quarto
FROM ocupacao WHERE dt _ocupacao = SYSDATE;
BEGIN
FOR ocupacao_reg IN ocupacao_cur
LOOP
atualiza_nota( ocupacao_reg.hosp_id,
ocupacao_reg.nm_quarto);
END LOOP;
END;
L o o p FOR p a r a Cu r s o r e s
Quando usar:
quando desejamos recuperar e processar
todos os registros do cursor
não é apropriado à situações em que
condições devem ser avaliadas para
determinar o término da operação de
recuperação
Observação
o índice do loop, neste caso uma variável do
tipo registro, é encarado da mesma forma
que índices numéricos
Ex e r c íc i o I I .1
Liste os 3 departamentos com maior folha
(soma dos salários dos empregados), em
ordem decrescente.
Sugestão:
Utilize as tabelas dept e emp (SCOTT/ TIGER)
utilize o procedimento
DBMS_OUTPUT.PUT_LINE
Ex e r c íc i o I I .2
De acordo com o salário de um
empregado, calcule o imposto de renda
correspondente.
Sugestões
Utilize a tabela emp SCOTT/ TIGER
Não considere possíveis deduções
Obs.:
salario <
salario >
salario >
900 - isento
900 e < = 1800 - 15% - R$135
1800 - 25% - R$315
Ex e r c íc i o I I .3
Calcular o total de salários pagos (salário
+ comissão) para empregados de um
departamento. Determinar também
quantos empregados tem salário maior
que $2000 e quantos tem a comissão
maior que o salário.
Su b p r o g r a m a s :
Pr o c e d i m e n t o s e Fu n ç õ e s
Co n c e i t o s
Modularização
processo de quebrar grandes blocos de
código em pequenos pedaços (módulos)
torna o código:
mais reutilizável
mais fácil de gerenciar
mais legível
mais confiável
Co n c e i t o s
Estruturas para Modularização
(PL/ SQL):
procedimento
bloco que efetua uma ou mais ações, sendo
possível a passagem de informações, tanto
para dentro quanto para fora do procedimento
função
retorna um único valor; podemos passar
informações para a função através de
parâmetros
Co n c e i t o s
bloco anônimo
bloco PL/ SQL que efetua uma ou mais tarefas;
usado para controlar o escopo dos
identificadores e manuseio de exceptions
package
coleção de procedimentos, funções, tipos e
variáveis; não é exatamente um módulo, mas
está relacionado ao assunto
Es t r u t u r a d o B l o c o PL /SQL (Re v i s ã o )
Cabeçalho
Cabeçalho
DECLARE
Seção
Seçãode
deDeclaração
Declaração
BEGIN
Seção
Seçãode
deExecução
Execução
EXCEPTION
Seção
Seçãode
deExceptions
Exceptions
END;
IS
Seção
Seçãode
deDeclaração
Declaração
BEGIN
Seção
Seçãode
deExecução
Execução
EXCEPTION
Seção
Seçãode
deExceptions
Exceptions
END;
Es t r u t u r a d e B l o c o s PL /SQL
PROCEDURE contratar(nome_in IN VARCHAR2)
IS
data_contratacao DATE;
BEGIN
data_contratacao := SYSDATE - 2;
INSERT INTO empregado (nome, data_contratacao)
VALUES (nome_in, data_contratacao);
EXCEPTION
WHEN DUP_VAL_IN_INDEX
THEN
DBMS_OUTPUT.PUT_LINE( Não inseriu );
END;
Es c o p o (Re v i s ã o )
DECLARE
DECLARE
data_contratacao
data_contratacaoDATE;
DATE;
BEGIN
BEGIN
DECLARE
DECLARE
total_vendas
total_vendasNUMBER;
NUMBER;
BEGIN
BEGIN
END;
END;
END;
END;
escopo
escopode
de
data_contratacao
data_contratacao
escopo
escopode
de
total_vendas
total_vendas
Pr o c e d i m e n t o s
Estrutura
PROCEDURE < nome> [ (parâmetro [ ,
parâmetro ...] ) ]
IS
[ comandos de declaração]
BEGIN
< comandos>
[ EXCEPTION
< comandos para manuseio de
exceptions> ]
END [ nome] ;
Pr o c e d i m e n t o s
Chamada
um procedimento é chamado da mesma
maneira que um comando PL/ SQL
aplicar_desconto(nova_empr_id, 15.00);
Pr o c e d i m e n t o s
Cabeçalho
nome do procedimento e lista de parâmetros
PROCEDURE aplicar_desconto(empr_id_in IN
empresa.empr_id%TYPE, desconto_in IN
NUMBER)
Corpo
código necessário para a execução do
procedimento
PROCEDURE nada IS
BEGIN
NULL;
END;
Ex e m p l o d e Pr o c e d u r e
PROCEDURE raise_salary (emp_id INTEGER, increase REAL)
IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + increase
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, No such number );
WHEN salary_missing THEN
INSERT INTO emp_audit VALUES (emp_id, Salary is null );
END raise_salary;
Fu n ç õ e s
Estrutura
FUNCTION nome [ (parâmetro [ , parâmetro
...] ) ]
RETURN tipo_retornado
IS
[ comandos de declaração]
BEGIN
comandos
[ EXCEPTION
comandos para manuseio de exceptions]
END [ nome]
Fu n ç õ e s
Chamada
uma função é chamada como parte de um
comando, sempre que uma expressão pode
ser usada
vendas_95 := total_vendas( Marisol , 1995);
DECLARE
vendas_95 NUMBER DEFAULT
total_vendas( Marisol , 1995);
IF total_vendas( Marisol , 1995)
THEN ...
Fu n ç õ e s
Cabeçalho
nome da função, lista de parâmetros e
tipo do retorno
FUNCTION total_vendas(nome_in IN
empresa.nome%TYPE, ano_in pedido.ano%TYPE)
RETURN NUMBER;
Corpo
código necessário para a execução da função
FUNCTION nada RETURN BOOLEAN IS
BEGIN
RETURN TRUE;
END;
De c l a r a ç ã o RET U RN
Encerra a execução da função e retorna o
valor para o programa que a chamou.
Um subprograma pode conter várias
declarações RETURN.
Em procedures a declaração RETURN não
deve conter uma expressão.
Em funções a declaração RETURN deve
conter uma expressão que é executada no
momento da execução da declaração.
Ex e m p l o d e Fu n ç ã o
FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN
IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal
FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;
Pa r â m e t r o s
Modo de troca de informações entre o
módulo e o bloco PL/ SQL que o chamou.
Quando declaramos um parâmetro, nunca
especificamos restrições quanto ao
tamanho do tipo de dado.
PROCEDURE mostra_empresa(nome IN
VARCHAR2) IS
É permitida a utilização de %TYPE e
%ROWTYPE na declaração de parâmetros
M o d o d e Pa s s a g e m d e Pa r â m e t r o s
IN
somente para leitura
OUT
somente para escrita (o módulo pode atribuir
um valor ao parâmetro, que será passado ao
bloco PL/ SQL que o chamou)
IN OUT
usado para leitura e escrita
Pa s s a g e m d e Pa r â m e t r o s
PROCEDURE combine_formate_nomes (prim_nome IN OUT VARCHAR2,
ult_nome IN OUT VARCHAR2,
nome_comp OUT VARCHAR2,
formato IN VARCHAR2 := ULTIMO PRIMEIRO )
-- O parâmetro formato não precisa obrigatoriamente ser informado
IS
BEGIN
IF formato = ULTIMO PRIMEIRO THEN
nome_comp := ult_nome | | , | | prim_nome
ELSIF formato = PRIMEIRO ULTIMO THEN
nome_comp := prim_nome | | | | ult_nome;
END IF;
END;
Co m o o PL /SQL f a z a a s s o c i a ç ã o d o s
parâm et ros?
Notação Posicional
associa o valor ao parâmetro correspondente
implicitamente através da posição
Notação por Nome Explicitamente
associa um valor a um parâmetro
explicitamente através do seu nome
vendas_97 := total_vendas(nome_in = > Cia. JK ,
ano_in = > 1997);
vendas_97 := total_vendas(ano_in = > 1997,
nome_in = > Cia. JK );
St o r e d Pr o c e d u r e s /Fu n c t i o n s
Uma stored procedure ou stored function
é uma unidade de programa PL/ SQL que:
tem um nome
pode receber e retornar valores
fica armazenada no dicionário de dados
pode ser usada por vários usuários
St o r e d Pr o c e d u r e s /Fu n c t i o n s
Exemplo:
CREATE PROCEDURE recupera_emp_reg
(emp_numero IN emp.empno%TYPE,
emp_reg
OUT emp%ROWTYPE) AS
BEGIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO emp_ret
FROM emp
WHERE empno = emp_numero;
END;
Módulo Loc al
Procedimento ou função definido da seção
de declaração de um bloco PL/ SQL.
Não pode ser chamado por nenhum outro
módulo PL/ SQL definido fora do bloco ao
qual o mesmo pertence.
Vantagens
reduz o tamanho do módulo, eliminando
códigos repetitivos
torna o código mais legível
Ex e m p l o d e M ó d u l o s L o c a i s
DECLARE
rent REAL;
PROCEDURE raise_rent (increase IN OUT REAL) IS
BEGIN
rent := rent + increase;
...
END raise_rent;
...
BEGIN
...
raise_rent(rent);
indeterminate
De p e n d ê n c i a s d o s Su b p r o g r a m a s
Subprogramas são dependentes dos
objetos que referenciam
Caso algum destes objetos forem
alterados por uma operação DDL, o
subprograma fica com status INVALID e
deve ser recompilado
ALTER {PROCEDURE | FUNCTION} nome COMPILE;
Determinação de dependência (no
INIT.ORA)
Timestamp
Assinatura
Pr i v i l é g i o s d e A c e s s o
Para poder executar um subprograma é
necessário ter o privilégio EXECUTE para
o objeto.
Um subprograma é executado utilizando
os privilégios explícitos do seu owner, sem
considerar privilégios de roles.
Pa c k a g e s
Co n c e i t o s
Uma package é um conjunto de objetos
PL/ SQL que são empacotados com uma
sintaxe especial de BEGIN-END.
Podemos colocar em uma package:
cursores
variáveis
nomes de exceptions
comandos TYPE
procedimentos e funções
Co n c e i t o s
Vantagens de utilizarmos packages:
information hiding
projeto orientado a objetos
projeto top- down
persistência de objetos
melhor performance
Int erfac e
Package
DataBase
Especificação
Aplicação
Body
Es t r u t u r a d e u m a Pa c k a g e
Especificação
contém as definições e especificações de
todos os elementos em uma package que
podem ser referenciados fora dela.
PACKAGE nome_package IS
[ declarações de variáveis e tipos ]
[ declarações de cursores ]
[ declarações de módulos ]
END [ nome_package] ;
Es t r u t u r a d e u m a Pa c k a g e
Corpo
contém implementação de módulos, cursores
e outros objetos
PACKAGE BODY nome_package IS
[ declarações de variáveis e tipos ]
[ especificaçao de comandos SELECT de cursores ]
[ especificação do corpo de módulos ]
[ BEGIN
comandos executáveis ]
[ EXCEPTION
exception handlers ]
END [ nome_package] ;
Ex e m p l o Pa c k a g e - Es p e c i f i c a ç ã o
CREATE PACKAGE emp_actions AS
specification
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
Ex e m p l o Pa c k a g e - B o d y
CREATE PACKAGE BODY emp_actions
AS
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER)
IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,
mgr, SYSDATE, sal, comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Pa c k a g e s e Es c o p o
Objetos declarados na Package
Specification têm escopo público,
podendo ser utilizados fora da package
nome_da_package.nome_do_subprograma (...)
nome_da_package.nome_do_tipo
Aqueles objetos definidos somente no
Package Body têm escopo privado,
podendo somente ser utilizados por
outros objetos dentro da package
Ov e r l o a d i n g d e M ó d u l o s
Dois ou mais módulos podem ter o mesmo
nome com uma lista diferente de parâmetros
FUNCTION valor_ok(numero_in IN NUMBER)
RETURN BOOLEAN
IS
FUNCTION valor_ok(data_in IN DATE)
BEGIN
RETURN BOOLEAN
RETURN numero_in > 0;
IS
END;
BEGIN
RETURN date_in <= SYSDATE;
END;
Ov e r l o a d i n g d e M ó d u l o s
Onde Fazer?
na seção de declaração de um bloco PL/ SQL
dentro de um package
Não podemos fazer a sobrecarga de
nomes de programas independentes,
muito menos criar dois módulos
independentes com o mesmo nome e
listas de parâmetros distintas.
Ex e m p l o
DECLARE
TYPE DateTabTyp IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
TYPE RealTabTyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
hiredate_tab DateTabTyp;
comm_tab RealTabTyp;
indx BINARY_INTEGER;
...
BEGIN
indx := 50;
initialize(hiredate_tab, indx);
calls first version
initialize(comm_tab, indx);
calls second version
...
END;
I n i c i a l i za ç ã o d e Pa c k a g e s
Uma package pode conter um conjunto
de instruções a serem executadas
somente quando a mesma é carregada
para a memória
CREATE OR REPLACE PACKAGE BODY nome_pack AS
...
BEGIN
-- Código de inicialização
...
END nome_pack;
De p e n d ê n c i a s d e Pa c k a g e s
O Package Body depende da Package
Specification e dos objetos referenciados
A Package Specification não depende de
nada
Visões para dependências no PL/ SQL 8.0:
user_dependencies
all_dependencies
dba_dependencies
U t i l i za ç ã o d e St o r e d Fu n c t i o n s e m
i n s t r u ç õ e s SQL
Uma função independente ou contida
numa package pode ser chamada numa
instrução SQL, dependendo das restrições
de referência
Restrições de Referência: definem quais
tipos de estruturas de dados que a função
lê ou modifica
WNDS ( Writes No Database State)
RNDS ( Reads No Database State)
WNPS ( Writes No Package State)
RNPS ( Reads No Package State)
U t i l i za ç ã o d e St o r e d Fu n c t i o n s e m
i n s t r u ç õ e s SQL
Somente para Stored Functions
Parâmetros
devem ser somente de entrada
não podem utilizar tipos PL/ SQL ( boolean,
record)
Tipo de retorno da função também tem
que ser um tipo da base de dados
REST RI CT _REFEREN CES
Para funções independentes o PL/ SQL
consegue determinar as restrições de
referência.
Para funções em packages é necessário
discriminar as restrições através da
pragma abaixo. Isto porque os blocos
PL/ SQL que chamam uma função
empacotada dependem apenas da
package specification e não do body.
PRAGMA RESTRICT_REFERENCES (nome_função, WNDS
[,WNPS] [,RNDS] [,RNPS]);
A l g u m a s Pa c k a g e s Pr é -De f i n i d a s
Nome
Descrição
DBMS_DDL
execução de comandos DDL
DBMS_JOB
DBMS_SNAPSHOT
submete e gerencia jobs programados
dentro do database
mostra saídas de programas PL/SQL
no terminal
gerenciamento de snapshots
DBMS_SQL
construção de SQLs dinâmicos
UTL_FILE
permite que programas PL/SQL leiam
e escrevam em arquivos
DBMS_OUTPUT
Ex e r c íc i o I I I .1
Converta o script criado no exercício I.2
(raízes de uma equação do 2o grau) para
um procedimento.
Armazene este procedimento no banco.
Ex e r c íc i o I I I .2
Converta o script do exercício II.1 (três
departamentos com maior folha - soma de
salários) para um procedimento.
Ao invés de mostrar as informações na
tela, insira-as em uma tabela.
Armazene este procedimento no banco
(stored procedure).
Ex e r c íc i o I I I .3
Converta o script do exercício II.2 (cálculo
do imposto renda) para uma função, que
receba como parâmetro a matrícula do
funcionário e retorne o imposto a ser
pago.
Armazene esta função no banco (stored
function).
Ex e r c íc i o I I I .4
Construa uma package contendo as funções
/ procedimentos do departamento pessoal.
Armazene esta package no banco de dados
Oracle.
Salário Líquido = SAL+ COMM - Imposto de
Renda
COMM = Se o JOB= CLERK , comissão =
1.03 * SAL
Gravar em Contra-Cheque (salário normal,
comissão, imposto de renda e salário
Líquido)
Triggers
Triggers
Correspondem a stored procedures, com
a diferença que os triggers são disparados
automaticamente quando houver
operações de insert, update e delete nas
tabelas associadas.
Triggers
As aplicações de triggers incluem:
cálculo automático de colunas
crítica de transações
garantia de regras de segurança complexas
garantia de integridade referencial em bancos
de dados distribuídos
implementação de regras de negócio
complexas
Si n t a x e
CREATE OR REPLACE schema.trigger
[BEFORE|AFTER] DELETE OR INSERT OR UPDATE [OF
column]
ON schema.table
[REFERENCING OLD AS old NEW AS new]
FOR EACH ROW
WHEN (condition)
pl_sql_block
T r i g g e r i n g St a t e m e n t
É a especificação da ação que levará ao
acionamento do trigger, podendo ser:
INSERT
UPDATE
DELETE
T r i g g e r Re s t r i c t i o n
É uma expressão que limita a execução
de um trigger.
Deve resultar em valor TRUE ou FALSE.
O trigger somente será executado para
valores TRUE.
Trigger Ac t ion
É um bloco PL/ SQL que será executado
quando o trigger for acionado.
Tipos de Triggers
ROW TRIGGERS
são executados uma vez para cada linha
da tabela afetada pelo comando SQL.
STATEMENT TRIGGERS
são executados apenas uma vez para cada
comando SQL que afete a tabelas,
independentemente do número de linhas
envolvidas.
Ac ionam ent o de Triggers
Quando definimos um trigger, podemos
especificar quando ele será acionado:
before row
before statement
after row
after statement
T r i g g e r s (Ex e m p l o )
CREATE TRIGGER dummy
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (new.empno > 0)
DECLARE
/ * variáveis, constantes, cursores, etc. * /
BEGIN
/ * bloco PL/ SQL * /
END;
Lim it e de Triggers por Tabela
Um de cada tipo, totalizando até 12
triggers. No entanto no PL/ SQL 2.1 uma
tabela pode ter mais de um trigger de
cada tipo.
BEFORE UPDATE row
BEFORE DELETE row
BEFORE INSERT statement
BEFORE INSERT row
BEFORE UPDATE statement
BEFORE DELETE statement
AFTER UPDATE row
AFTER DELETE row
AFTER INSERT statement
AFTER INSERT row
AFTER UPDATE statement
AFTER DELETE statement.
T r i g g e r s i n s t e a d -o f
Em PL/ SQL 8.0, podem ser definidos
triggers que serão disparados em vez da
instrução DML que os disparou
Somente para visões
A nível de linha
Exemplo:
Para eliminar linhas de uma view complexa
Ps e u d o -r e g i s t r o s
Disponível para triggers a nível de linha
:old
valores originais do registro da tabela
somente para leitura no corpo do trigger
:new
valores do registro que serão inseridos ou
atualizados na base de dados
podem ser atribuídos valores (somente
quando before)
Cl á u s u l a WH EN
Válida para triggers a nível de linha
O corpo do trigger será executado para as
linhas que a condição especificada
resultar em TRUE
Os pseudo-registros :old e :new podem
ser utilizados dentro da condição, mas os
dois pontos devem ser suprimidos
Pr e d i c a d o s d e T r i g g e r
Usados em triggers disparados para
diferentes tipos de instruções de DML
Funções booleanas que identificam a
instrução que disparou o trigger
INSERTING
UPDATING
DELETING
T r a t a m e n t o d e Er r o s
Co n c e i t o s
Uma exception é uma situação que não
deveria ter ocorrido.
Pode ser causada por:
erro gerado pelo sistema (p.e. out of
memory )
erro causado por uma ação do usuário
aviso gerado pela aplicação e direcionado ao
usuário
Ex c e p t i o n H a n d l e r s
Este mecanismo permite separar o código
de processamento de erros do resto dos
comandos.
Oferece um modelo orientado a eventos.
Não importa como uma exception foi
gerada, ela será tratada na mesma seção
(exception section).
De s v i o d o Fl u x o
PROCEDURE fluxo
IS
novo_valor VARCHAR2(5);
BEGIN
novo_valor := valor_antigo || -novo ;
IF novo_valor LIKE open%
THEN
...
END IF;
EXCEPTION
WHEN VALUE_ERROR
THEN ...
END;
T i p o s d e Ex c e p t i o n s
Existem quatro tipos de exceptions:
exceptions do sistema com um nome
geradas devido a um erro no processamento do
SGBD ou do código PL/ SQL
definidas pelo programador com um nome
geradas devido a um erro no código; elas são
declaradas na seção correspondente
T i p o s d e Ex c e p t i o n s
Exceptions do sistema sem um nome
geradas devido a um erro no processamento do
SGBD ou do código PL/ SQL; somente as mais
comuns possuem um nome
Definidas pelo programador sem um nome
procedimento RAISE_APPLICATION_ERROR é
executado
o programador informa um número (-20000 a
20999) e uma mensagem de erro;
utilizada para facilitar a comunicação de erros do
ambiente cliente-servidor.
Ex c e p t i o n s d o s i s t e m a c o m u m n o m e
DECLARE
stmt INTEGER := 1;
designates 1st SELECT statement
BEGIN
SELECT ...
stmt := 2;
designates 2nd SELECT statement
SELECT ...
stmt := 3;
designates 3rd SELECT statement
SELECT ...
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO errors VALUES ( Error in statement || stmt);
...
END;
Ex c e p t i o n s Pr é -De f i n i d a s
Exception Name
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE
Oracle Error
ORA 06511
ORA 00001
ORA 01001
ORA 01722
ORA 01017
ORA 01403
ORA 01012
ORA 06501
ORA 06504
ORA 06500
ORA 00051
ORA 01422
ORA 06502
ORA 01476
SQLCODE Value
6511
1
1001
1722
1017
+100
1012
6501
6504
6500
51
1422
6502
1476
De f i n i d a s p e l o p r o g r a m a d o r c o m u m
nom e
Diferente das exceptions pré-definidas, as
exceptions definidas pelo programador
devem ser declaradas e devem ser
chamadas explicitamente através da
declaração:
RAISE
Definindo a Exception:
DECLARE
past_due EXCEPTION;
acct_num NUMBER(5);
Ex e m p l o
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
...
DECLARE
início do sub block
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
...
IF ... THEN
RAISE past_due;
esta exception não é
executada
END IF;
...
END;
fim do sub-block
EXCEPTION
WHEN past_due THEN
não é usada
...
END;
EX CEPT I ON _I N I T p r a g m a
Pragma é uma instrução especial ao
compilador
A EXCEPTION_INIT pragma indica que o
compilador deve associar um nome a uma
exception que possui um número
correspondente.
EX CEPT I ON _I N I T p r a g m a
Exemplo
ORA-2292 violated integrity constraining - child
record found
DECLARE
ainda_ha_empreg EXCEPTION;
PRAGMA EXCEPTION_INIT (ainda_ha_empreg, -2292);
BEGIN
DELETE FROM empresa;
EXCEPTION
WHEN ainda_ha_empreg THEN
DBMS_OUTPUT.PUT_LINE
( Ainda existem empregados para a empresa );
END;
U s a n d o r a i s e _a p p l i c a t i o n _e r r o r
A package DBMS_STANDARD, que vem
com o Oracle7, tem algumas facilidades
que ajudam a aplicação a interagir com o
Oracle. Por exemplo, a procedure
raise_application_error permite ao
programador definir uma mensagem de
erro ao seu modo.
continua...
U s a n d o r a i s e _a p p l i c a t i o n _e r r o r
raise_application_error(error_number,
message[, {TRUE | FALSE}]);
Onde error_number é um inteiro negativo entre 20000 .. - 20999 e message é uma string de
caracter com até 2048 bytes. Se o terceiro
parâmetro é TRUE, o erro é colocado em uma
pilha de erros. Se o parâmetro é FALSE ( default ),
o erro substitui todos os erros anteriores.
A chamada a raise_application_error somente
pode ser feita de uma stored procedure. Quando
chamada, o subprograma é encerrado e o
número do erro e a mensagem são retornados.
U s a n d o r a i s e _a p p l i c a t i o n _e r r o r
CREATE PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER)
AS
current_salary NUMBER;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
/* Issue user defined error message. */
raise_application_error( 20101, Salary is missing );
ELSE
UPDATE emp SET sal = current_salary + increase
WHERE empno = emp_id;
END IF;
END raise_salary;
Se ç ã o Ex c e p t i o n
EXCEPTION
WHEN exception_name1 THEN
handler
sequence_of_statements1
WHEN exception_name2 THEN
another
handler
sequence_of_statements2
...
WHEN OTHERS THEN
optional handler
sequence_of_statements3
END;
U s a n d o SQL CODE e SQL ERRM
No tratamento de uma exception pode-se
usar as funções SQLCODE e SQLERRM
Para exceptions internas, o número do
SQLCODE é negativo a menos que o erro
seja no_data_found que neste caso é
+ 100.
O SQLERRM retorna a mensagem do
Oracle.
Ex e m p l o
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;
Ex e r c íc i o I V .1
Altere o script do exercício I.1, incluindo a
seção de exception (prevendo o caso em
que o número informado for menor que
zero).
Ex e r c íc i o I V .2
Construa um script que faça uma consulta
simples à base de dados (tabela scott.emp)
tomando como parâmetro o nome do
funcionário e retorne a sua matrícula.
Utilize o comando SELECT INTO
Pense na possibilidade do funcionário não ser encontrado
Pense na possibilidade de existirem dois funcionários com
o mesmo nome
Trate as demais exceptions de uma maneira genérica
Ex e r c íc i o I V .3
Converta o script anterior (IV.2 - retorno
da matrícula do funcionário dado um
nome) para uma procedimento (recebe o
nome, retorna a matrícula e um status
indicando se fez a recuperação com
sucesso, se não encontrou ou se existem
registros duplicados).
Armazene este procedimento no banco
(stored procedure).
T e s t e s e De p u r a ç ã o
Di r e t r i ze s d e De p u r a ç ã o
Encontrar o local onde ocorre o erro
Definir exatamente o problema
Reduzir o programa a um simples teste
Estabelecer um ambiente de teste
DB M S_OU T PU T
Pacote para mostrar mensagens
Procedure Descrição
enable
disable
put_line
get_line
get_lines
Habilita o buffer de mensagens.
No SQL*Plus pode-se usar SET SERVEROUTPUT ON
Desabilita o buffer de mensagens
Imprime uma linha no buffer
Obtém uma linha do buffer (dentro do bloco PL/SQL)
Obtém todas as linhas do buffer (dentro do bloco PL/SQL)
Ex e m p l o d e DB M S_OU T PU T
BEGIN
DBMS_OUTPUT.ENABLE(20000);
DBMS_OUTPUT.PUT_LINE( Início );
...
DBMS_OUTPUT.PUT_LINE( Fim );
END;
Pr o c e d u r e B u i l d e r
Ambiente de desenvolvimento de PL/ SQL
Serve como depurador de código PL/ SQL
Não pode depurar stored procedures,
somente chamar
PL /SQL I n t e r p r e t e r
Visualizador
Mostra o bloco, procedimento ou função que
está sendo executado
Linha de comandos
Permite execução imediata de instruções de
PL/ SQL
Fa c i l i d a d e s
Depurar sem necessidade de alterar o
código
Inserção de breakpoints
Visualização e alteração dos valores das
variáveis locais
Depuração mais simples
Ambiente de desenvolvimento integrado
PL /SQL Di n â m i c o
DB M S_SQL
A instrução SQL pode ser montada
dinamicamente dentro do bloco
Processa três tipos de instruções
instruções de DML e DDL
consultas
blocos de PL/ SQL anônimos
Instruções SELECT não devem possuir a
cláusula INTO
Não incluir ponto e vírgula no final, exceto
blocos PL/ SQL anônimos
DB M S_SQL .OPEN _CU RSOR
Abre o cursor para executar a instrução
SQL atribuindo um ID para o cursor que
será utilizado nas chamadas
subsequentes.
Sintaxe:
FUNCTION OPEN_CURSOR RETURN INTEGER;
DB M S_SQL .PA RSE
Análise da instrução
Verificação da sintaxe e semântica da
instrução
Se for uma consulta, determina o plano de
execução
Sintaxe
PROCEDURE PARSE (cursor_id IN INTEGER,
instrução IN VARCHAR2,
indicador_linguagem IN
INTEGER);
onde indicador_linguagem pode ser V6, V7 ou
NATIVE.
DB M S_SQL .B I N D_V A RI A B L E
Associação de variáveis de entrada de
dados à variáveis reais do bloco PL/ SQL
Sintaxe
PROCEDURE BIND_VARIABLE
(cursor_id IN INTEGER,
:nome_na_instrução IN
VARCHAR2,
valor_para_atribuir IN tipo_dado);
onde tipo_dado pode ser NUMBER,
VARCHAR2 ou DATE.
Há variações para os demais tipos de
dado
DB M S_SQL .DEFI N E_COL U M N
Associação de variáveis de saída de dados
(resultado de uma consulta) à variáveis
reais do bloco PL/ SQL
Sintaxe
PROCEDURE DEFINE_COLUMN
(cursor_id IN INTEGER,
posição_coluna IN INTEGER,
nome_coluna IN tipo_dado);
Uma instrução para cada coluna da
consulta
DB M S_SQL .EX ECU T E
Para não consulta
executa a instrução e retorna o número de
linhas processadas
Para consulta
determina o conjunto de linhas a serem
processadas
Sintaxe
FUNCTION EXECUTE (cursor_id IN INTEGER)
RETURN INTEGER;
O retorno corresponde às linhas processadas
somente se forem instruções INSERT,
UPDATE ou DELETE.
DB M S_SQL .FET CH _ROWS
Somente usado em consultas
Faz o fetch buscando os dados no
servidor
Os dados de retorno são convertidos nos
tipos definidos com DEFINE_COLUMN
Sintaxe
FUNCTION FETCH_ROWS (cursor_id IN INTEGER)
RETURN INTEGER;
O retorno é o número de linhas.
DB M S_SQL .EX ECU T E_A N D_FET CH
Combina as operações de EXECUTE e a
primeira chamada de FETCH_ROWS
Sintaxe
FUNCTION EXECUTE_AND_FETCH
(cursor_id IN INTEGER,
busca_exata IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Se busca_exata for TRUE e a consulta retornar
mais de uma linha, abre a exception
TOO_MANY_ROWS.
O retorno é o número de linhas.
DB M S_SQL .V A RI A B L E_V A L U E
Determina o valor de uma variável de
associação, se for modificada pela
instrução (parâmetros de saída)
Utilizado quando a instrução é um bloco
PL/ SQL
Sintaxe
PROCEDURE VARIABLE_VALUE
(cursor_id IN NUMBER,
:nome_na_instrução IN VARCHAR2,
valor_variavel OUT tipo_dado);
DB M S_SQL .COL U M N _V A L U E
Somente usado em consultas, após
FETCH_ROWS
Devolve realmente os dados
Variáveis devem ser do mesmo tipo
definido em DEFINE_COLUMN
Sintaxe
PROCEDURE COLUMN_VALUE
(cursor_id IN INTEGER,
posição_coluna IN INTEGER,
valor_coluna OUT tipo_dado);
DB M S_SQL .CL OSE_CU RSOR
Fecha o cursor
Libera os recursos utilizados pelo cursor
Sintaxe
PROCEDURE CLOSE_CURSOR
(cursor_id IN OUT INTEGER)
Ex e c u t a r i n s t r u ç õ e s DM L (e x c e t o
c onsult as)
Passos necessários:
Abrir o cursor (OPEN_CURSOR)
Analisar a instrução (PARSE)
Associar quaisquer variáveis de entrada de
dados (BIND_VARIABLE)
Executar a instrução (EXECUTE)
Fechar o cursor (CLOSE_CURSOR)
Ex e c u t a r i n s t r u ç õ e s DDL
Passos necessários:
Abrir o cursor (OPEN_CURSOR)
Analisar a instrução (PARSE)
Fechar o cursor (CLOSE_CURSOR)
Não pode utilizar bind variables em DDL
As instruções são executadas já no
PARSE, não necessitando, assim, de
EXECUTE
Ex e c u t a r c o n s u l t a s
Passos necessários:
Abrir o cursor (OPEN_CURSOR)
Analisar a instrução (PARSE)
Associar quaisquer variáveis de entrada de dados
(BIND_VARIABLE)
Definir variáveis de saída de dados
(DEFINE_COLUMN)
Executar a consulta (EXECUTE)
Extrair as linhas (FETCH_ROWS)
Devolver os resultados às variáveis PL/ SQL
(COLUMN_VALUE)
Fechar o cursor (CLOSE_CURSOR)
Ex e c u t a r PL /SQL
Passos necessários:
Abrir o cursor (OPEN_CURSOR)
Analisar a instrução (PARSE)
Associar quaisquer variáveis de entrada de
dados (BIND_VARIABLE)
Executar a instrução (EXECUTE)
Obter o valor de quaisquer variáveis de saída
de dados (VARIABLE_VALUE)
Fechar o cursor (CLOSE_CURSOR)
N o v a s f a c i l i d a d e s PL /SQL 8 .0
Capacidade de analisar cadeias de
caracteres de SQL de grandes dimensões
Processamento matricial
Associar e definir tipos Oracle8 (objetos,
LOBs)
Procedimento DESCRIBE_COLUMNS
Su g e s t õ e s e T é c n i c a s
Reutilizar cursores
Um cursor aberto pode processar instruções
SQL diferentes
Instruções repetidas não necessitam repetir o
PARSE, apenas o EXECUTE
Permissões
Os perfis de grupo são desativados em
procedimentos empacotados, incluindo o
DBMS_SQL
Operações de DDL e lock
En t r a d a e Sa íd a e m A r q u i v o s
PL /SQL I /O e m A r q u i v o s OS
A release 7.3 do Oracle Server adiciona a
capacidade de se fazer I/ O através da package
UTL_FILE.
Isto é similar àquelas operações padrões em
arquivos de sistemas operacionais (OPEN, GET,
PUT, CLOSE) com algumas limitações.
Por exemplo, pode-se chamar a função FOPEN
para retornar um arquivo aberto, no qual as
chamadas subsequentes aos comandos
GET_LINE ou PUT são executados nos arquivos.
Enquanto não se fechar o arquivo (FCLOSE) as
operações de I/ O não estarão completas.
Pr o c e d u r e s d a Pa c k a g e U T L _FI L E
Function/Procedure
FOPEN
IS_OPEN
FCLOSE
FCLOSE_ALL
GET_LINE
PUT
PUT_LINE
PUTF
NEW_LINE
FFLUSH
Description
Abre um arquivo para entrada e saída
Cria um arquivo de saída se ele não existir
Determina se um arquivo está aberto
Fecha um arquivo
Fecha todos os arquivos abertos
Lê uma linha texto de um arquivo aberto.
Escreve uma linha no arquivo.
Não adiciona um caracter de terminação (EOL)
Escreve uma linha no arquivo.
Adiciona um caracter de terminação
Procedure com formatação.
Escreve uma ou mais linhas de terminação no arquivo.
Escreve fisicamente todas as linhas pendentes para
o arquivo de saída.
Se g u r a n ç a
O diretório de acesso dos arquivos deve
ser especificado em um parâmetro de
inicialização no arquivo INIT.ORA
UTL_FILE_DIR = < directory name>
A especificação do parâmetro:
UTL_FILE_DIR = *
torna sem efeito a segurança
De c l a r a ç ã o d e T i p o s
A especificação para a package UTL_FILE
é declarada no tipo:
TYPE file_type IS RECORD (id
BYNARY_INTEGER)
Exemplo:
v_filehandle UTL_FILE.FILE_TYPE;
...
v_filehandle := UTL_FILE.FOPEN(...);
...
Ex c e p t i o n s
Exception Name
INVALID_PATH
INVALID_MODE
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
WRITE_ERROR
INTERNAL_ERROR
Descrição
Localização ou nome do arquivo inválido
Modo de abertura do arquivo inválido.
Arquivo Inválido.
Arquivo não pode ser aberto.
Um erro de sistema operacional
ocorreu durante leitura.
Um erro de sistema operacional ocorreu
durante a escrita..
Um erro não especificado ocorreu no PL/SQL.
FOPEN
Sintaxe:
FUNCTION FOPEN(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN
VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;
FOPEN
Parâmetros
location
filename
open_mode
Descrição
Diretório
Nome do Arquivo
r ler um texto (GET_LINE)
w escrever um texto
(PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)
a adicionar um texto
(PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)
Re t o r n o d a Fu n ç ã o FOPEN
FOPEN retorna um file handle que deve
ser passado para todas as procedures
chamadas posteriormente.
I S_OPEN
Sintaxe:
FUNCTION IS_OPEN(file_handle IN
FILE_TYPE)
RETURN BOOLEAN;
Parâmetro
file_handle
Descrição
Um file handle ativo retornado na função FOPEN
FCL OSE
Sintaxe:
PROCEDURE FCLOSE (file_handle IN OUT
FILE_TYPE);
Parâmetro
file_handle
Descrição
Um file handle ativo retornado na função FOPEN
FCL OSE_A L L
Sintaxe:
PROCEDURE FCLOSE_ALL;
GET _L I N E
Sintaxe:
PROCEDURE GET_LINE
(file_handle IN FILE_TYPE,
buffer OUT VARCHAR2);
Parâmetro
file_handle
buffer
Descrição
Um file handle ativo retornado na função FOPEN
Um buffer para receber a linha lida do arquivo.
PU T
Sintaxe:
PROCEDURE PUT
(file_handle IN FILE_TYPE,
buffer IN VARCHAR2);
Parâmetro
file_handle
buffer
Descrição
Um file handle ativo retornado na função FOPEN
Um buffer que contém a linha a ser escrita.
N EW_L I N E
Sintaxe:
PROCEDURE NEW_LINE
(file_handle IN FILE_TYPE,
lines IN NATURAL := 1);
Parametro
file_handle
lines
Descrição
Um file handle ativo retornado na função FOPEN
Número de linhas de terminação a serem
escritas no arquivo.
PU T _L I N E
Sintaxe:
PROCEDURE PUT_LINE
(file_handle IN FILE_TYPE,
buffer IN VARCHAR2);
Parâmetro
file_handle
buffer
Descrição
Um file handle ativo retornado na função FOPEN
Um buffer que contem a linha a ser escrita.
FFL U SH
Sintaxe:
PROCEDURE FFLUSH
(file_handle IN FILE_TYPE);
Parâmetro
file_handle
Descrição
Um file handle ativo retornado na função FOPEN
Ex e m p l o
PROCEDURE file_to_table
(loc_in IN VARCHAR2, file_in IN VARCHAR2,
table_in IN names_tabtype)
IS
names_file CONSTANT UTL_FILE.FILE_TYPE :=
UTL_FILE.FOPEN (loc_in, file_in, R );
line_counter INTEGER := 1;
BEGIN
LOOP
UTL_FILE.GET_LINE(names_file,
table_in(line_counter));
line_counter := line_counter + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.FCLOSE(names_file);
END;
De s e m p e n h o e Ot i m i za ç ã o
I n s t â n c i a Or a c l e
SGA: Sistem Global Area
Shared Pool
Armazena instruções SQL recebidas do BD
Triggers
manter o código o mais pequeno possível, através
de chamadas de subprogramas armazenados
antes da versão 7.3 os triggers não eram
armazenados de forma compilada
Ot i m i za ç ã o d e i n s t r u ç õ e s SQL
Explain Plan
Tabela plan_table
Pode ser criada localmente através de
$ORACLE_HOME\ rdbms\ admin\ utlxplan.sql
No SQL* Plus
SET AUTOTRACE ON
Após cada instrução faz automaticamente o
explain plan
Ot i m i za ç ã o d e i n s t r u ç õ e s SQL
Tkprof
ALTER SESSION SET SQL_TRACE= TRUE
gera um arquivo de log de todas as instruções
SQL, no formato ora_nnnnn.trc
Após todos os comandos efetuados, fechar o
arquivo de log, alterando para FALSE
Executar tkprof para formatar o arquivo .trc
A n á l i s e d e Re s u l t a d o s
NESTED LOOP
Operação necessária para executar os joins
de tabelas
TABLE ACCESS (FULL)
Pesquisa integral, buscando todas as linhas
da tabela
TABLE ACCESS (BY ROWID)
Modo mais rápido de buscar uma só linha
INDEX: { UNIQUE | RANGE} SCAN
Téc nic as
Verificar questões de rede
Utilizar PL/ SQL no Client sempre que
possível
Evitar repetições de parse desnecessárias
Interface matricial Oracle
permite grandes quantidades de dados
enviados pela rede como uma unidade