Curso PL/SQL

Prévia do material em texto

Programação em PL/SQL 
 
Estrutura do Curso 
1. Introdução a PL/SQL 
2. Conceitos Básicos de PL/SQL 
3. Variáveis e Dados de Programa 
4. Controle Condicional e Seqüencial 
5. Loops 
6. Registros em PL/SQL 
7. Tabelas PL/SQL 
8. SQL em PL/SQL 
9. Funções de SQL integradas 
10. Cursores 
11. Procedimentos e Funções 
12. Packages 
13. Triggers 
14. Tratamento de Erros 
15. Testes e Depuração 
16. PL/SQL Dinâmico 
17. Entrada e Saída em Arquivos 
18. 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. 
 
 
 
 
Ambientes de Execuçã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 
Conceitos Básicos da Linguagem PL/SQL 
 
Character Set do 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ímbolos Simples e Compostos 
 
 
 
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 
 
Identificadores 
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. 
 
Literais 
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 
 
Delimitadores 
Delimitador Ponto e vírgula (;) 
Indica o final de um comando 
IF salario < min_salario 
THEN 
salario := salario + salario * .25; 
END IF; 
 
Comentários 
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 * / 
... 
 
Estrutura de um Bloco 
 
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ções de um Bloco 
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ções de um Bloco 
 
 
 
 
 
 
 
 
Variáveis e Dados de Programa 
 
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 
 
Tipos de Dados 
 
 
 
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) 
Tipos de Dados Numéricos 
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á. 
 
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. 
 
 
 
 
 
Caracteres 
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. 
 
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 
 
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 
 
Row id 
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 
 
Booleano (BOOLEAN) 
Tipo de dados lógico (não existe correspondente no servidor Oracle). Pode assumir os 
valores TRUE, FALSE ou NULL. 
 
Data-hora (DATE) 
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. 
 
Conversão entre Tipos 
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 
Valores Nulos em 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 
 
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 
Declaração de Variáveis 
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] 
 
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 
Controle Condicional e Sequencial 
 
Comandos IF... 
Permitem que seja construída uma lógica condicional nas aplicações. 
 
 
 
Combinação IF-THEN 
Exemplos 
IF :empresa.total > media 
THEN 
aplicar_desconto(:empresa.empresa_id); 
END IF; 
IF relatorio_pedido 
THEN 
imprime_relatorio(relatorio_id); 
END IF; 
 
Exemplo 
IF :cliente.total_pedido > max _permitido 
THEN 
pedido_excedente := TRUE; 
ELSE 
pedido_excedente := FALSE; 
END IF; 
 
Exemplo 
IF salario < 10000 
THEN 
bonus:= 2000; 
ELSIF salario< 20000 
THEN 
bonus:= 1500; 
ELSIF salario < 40000 
THEN 
bonus:= 1000; 
ELSE 
bonus:= 500; 
END IF; 
 
Comando GOTO 
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. 
 
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. 
 
Comando NULL 
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*Plus 
 
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. 
Variáveis de Substituiçã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 Associaçã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) 
 
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. 
 
EXECUTE 
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; 
Executar Arquivos de Instruções 
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 
 
SHOW 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 
 
Exercício 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 
 
Exercício 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 
 
Conceitos 
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. 
Loop Simples 
LOOP 
<comandos> 
END LOOP; 
O teste para terminação é feito dentro do loop 
EXIT 
EXIT WHEN <condição> 
 
 
 
LOOP 
<comandos> 
END LOOP; 
O teste para terminação é feito dentro do loop 
EXIT 
EXIT WHEN <condição> 
 
 
 
 
Quando usar 
• não existe a certeza de quantas vezes o loop será executado 
• o loop deve executar pelo menos uma vez 
 
Loop 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. 
 
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 
 
 
 
Loop WHILE 
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. 
 
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 
 
 
 
Registros em PL/SQL 
Conceitos 
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. 
Tipos de Registro 
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 
Declaraç ão de um Registro 
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; 
 
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) ); 
 
Tabelas PL/SQL 
Definiçã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, ) 
Definindo uma Tabela 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. 
Exemplos 
 
 
Referenciando Tabelas PL/SQL 
 
 
 
Usando Tabelas 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; 
 
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; 
 
Atributos de Tabela: COUNT 
Retorna o número de elementos na tabela. 
... 
IF ename_tab.COUNT = 50 THEN 
... 
END; 
Atributos de Tabela: DELETE 
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 
 
Atributos de Tabela: 
EXISTS 
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; 
... 
 
Atributos de Tabela: FIRST e LAST 
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 ea última, o mais elevado. 
 
Atribut os de Tabela: NEXT e PRIOR 
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 em PL/SQL 
Instruções de 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) 
 
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 
DML: Select 
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 
DML: Insert 
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; 
DML: Update 
Altera o conteúdo de uma ou mais linhas de uma tabela 
UPDATE emp 
SET sal = sal * 1.2 
WHERE empno = 1; 
DML: Delete 
Elimina uma ou mais linhas de uma tabela 
DELETE FROM emp 
WHERE empno = 1; 
Cláusula WHERE 
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. 
Referências de tabelas 
As operações de DML referenciam uma tabela, de uma forma geral, no formato: 
[owner.] tabela[@dblink] onde: 
owner - usuário onde a tabela foi criada 
dblink - denominação de uma conexão a um banco de dados remoto 
Sinônimos 
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; 
Pseudo-colunas 
Funções adicionais que só podem ser chamadas a partir de instruções SQL: 
• CURRVAL e NEXTVAL 
• LEVEL 
• ROWID 
• ROWNUM 
Pseudo-colunas: CURRVAL e NEXTVAL 
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; 
 
Pseudo-colunas: LEVEL 
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. 
 
Pseudo-colunas: ROWID 
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; 
 
Pseudo-colunas: ROWNUM 
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) 
 
Privilégios de Acesso 
 
Privilégios de Objeto 
Para efetuar operações num objeto 
Privilégios de Sistema 
Para efetuar operações numa classe de objetos GRANT 
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] 
REVOKE 
Para revogar privilégios de acesso 
Objeto: 
REVOKE privilégio ON objeto FROM usuário 
[CASCADE CONSTRAINTS] 
Sistema: 
REVOKE privilégio FROM usuário 
Perfis de Grupo 
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 
 
Controle de Transações 
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 
 
Funções de SQL integradas 
Funções para Caracteres 
 
 
 
 
 
Funções para Caracteres 
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 
 
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 
 
Funções para Datas 
 
 
 
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 
Funções Numéricas 
 
 
 
 
 
Outras Funções 
 
 
 
Exemplos 
FUNCTION SQLCODE RETURN INTEGER 
FUNCTION SQLERRM RETURN VARCHAR2 
EXCEPTION 
WHEN OTHERS THEN 
MESSAGE( Error | | TO_CHAR(SQLCODE) | | : | | SQLERRM); 
 
 
Funções de Conversão 
 
 
 
Função TO_CHAR 
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 
Função TO_DATE 
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 
Função TO_NUMBER 
FUNCTION TO_NUMBER(string_in IN 
CHAR|VARCHAR2} 
[ , formato IN VARCHAR2 
[ , nls_language VARCHAR2] ] ) 
RETURN NUMBER 
TO_NUMBER( 123.23 ) => 123.23 
TO_NUMBER( abcdef ) => ERRO 
 
Funções de Grupo 
Somente para SELECT com GROUP BY 
 
 
 
Cursores 
Conceitos 
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; 
 
Operações em Cursores 
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. 
Tipos de Cursores 
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. 
 
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. 
Explícitos 
comando SELECT explicitamente definido na seção de declaração, sendo um nome 
associado a ele usados quando desejamosrecuperar mais de uma linha de resultado 
não existem para comandos de UPDATE, INSERT e DELETE 
Declaração 
CURSOR nome_cursor [ ( [ parâmetro [ , 
parâmetro ...] ) ] 
[ RETURN especificação_retorno ] 
IS comando_SELECT; 
Variáveis em um Cursor 
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); 
Precedência de um Identificador 
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 claúsula RETURN 
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 
Exemplo de Uso da claúsula RETURN 
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; 
Porque colocar cursor numa Package? 
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. 
 
Abrindo Cursores 
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. Recuperando Dados de Cursores 
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; 
 
Fechando Cursores 
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. 
 
Atributos de Cursores 
 
 
 
%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; 
... 
 
%NOTFOUND 
Oposto ao %FOUND 
Muito utilizado para terminação de loops 
EXIT WHEN empresa_cur%NOTFOUND; 
 
%ROWCOUNT 
DECLARE 
CURSOR emp_cur IS 
SELECT nome, salário 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; 
 
%ISOPEN 
IF NOT emp_cur %ISOPEN THEN 
OPEN emp_cur; 
END IF; 
 
Atributos de Cursores Implícitos 
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 
 
Parâmetros em Cursores 
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) 
SELECT ... FOR UPDATE 
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 
WHERE CURRENT 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; 
 
Variáveis Cursores 
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. 
Porque usar variáveis cursor? 
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. 
 
 
Exemplo 
DECLARE 
TYPE empresa_curtipo IS REF CURSOR 
RETURN empresa%ROWTYPE; 
empresa_curvar empresa_curtipo; 
BEGIN 
OPEN empresa_curvar FOR SELECT * 
FROM empresa; 
... 
Definindo uma Variável Cursor 
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 uma Stored Procedure 
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; 
 
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; 
Loop Simples para Cursores 
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 
 
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; 
Loop FOR para Cursores 
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 
 
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; 
 
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. 
 
Exercício II .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 
 
Exercício II .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 < 900 - isento 
salario > 900 e <= 1800 - 15% - R$135 
salario > 1800 - 25% - R$315 
 
Exercício II .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 maiorque o salário. 
Subprogramas: 
 
Procedimentos e Funções 
Conceitos 
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 
 
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 
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. 
 
Estrutura do Bloco PL/SQL (Revisão) 
 
 
 
 
Estrutura de Blocos PL/SQL 
 
 
 
 
Procedimentos 
Estrutura 
PROCEDURE <nome> [ (parâmetro [ ,parâmetro ...] ) ] IS 
[comandos de declaração] 
BEGIN 
<comandos> 
[ EXCEPTION <comandos para manuseio de exceptions> ] 
END [nome] ; 
 
Chamada 
um procedimento é chamado da mesma maneira que um comando PL/SQL 
aplicar_desconto(nova_empr_id, 15.00); 
 
 
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; 
Exemplo de Procedure 
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; 
 
Funções 
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] 
 
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 ... 
 
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; 
 
Declaração RETURN 
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. 
Exemplo de Funçã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; 
Parâmetros 
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 
Modo de Passagem de Parâmetros 
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 
Passagem de Parâmetros 
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; 
 
Como o PL/SQL faz a associação dos parâmetros? 
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); 
Stored Procedures/Functions 
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 
 
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; 
 
Exemplo: 
Módulo Local 
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 
 
DECLARE 
rent REAL; 
PROCEDURE raise_rent (increase IN OUT REAL) IS 
BEGIN 
rent := rent + increase; 
... 
END raise_rent; 
... 
BEGIN 
... 
raise_rent(rent); indeterminate 
 
Dependências dos Subprogramas 
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 
 
Privilégios de Acesso 
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. 
 
Packages 
Conceitos 
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 
 
Vantagens de utilizarmos packages: 
• information hiding 
• projeto orientado a objetos 
• projeto top-down 
• persistência de objetos 
• melhor performance 
 
Interface 
 
 
 
Estrutura de uma Package 
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] ; 
 
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] ; 
Exemplo Package - Especificaçã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; 
 
Exemplo Package - Body 
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 WHEREempno = emp_id; 
END fire_employee; 
END emp_actions; 
 
Packages e Escopo 
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 
Over loading de Módulos 
Dois ou mais módulos podem ter o mesmo nome com uma lista diferente de parâmetros. 
 
 
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. 
Exemplo: 
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; 
 
Inicialização de Packages 
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; 
 
Dependências de Packages 
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 
Utilização de Stored Functions em instruções 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) 
 
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 
RESTRICT_REFERENCES 
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]); 
Algumas Packages Pré-Definidas 
 
 
 
Exercício III.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. 
 
Exercício III.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). 
Exercício III.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). 
 
Exercício III.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 
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. 
 
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 
Sintaxe 
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 
 
Triggering Statement 
É a especificação da ação que levará ao acionamento do trigger, podendo ser: 
• INSERT 
• UPDATE 
• DELETE 
 
Trigger Restriction 
É 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 Action 
É 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. 
 
Acionamento de Triggers 
Quando definimos um trigger, podemos especificar quando ele será acionado: 
• before row 
• before statement 
• after row 
• after statement 
 
Triggers (Exemplo) 
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; 
 
Limite 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 AFTER UPDATE row 
BEFORE DELETE row AFTER DELETE row 
BEFORE INSERT statement AFTER INSERT statement 
BEFORE INSERT row AFTER INSERT row 
BEFORE UPDATE statement AFTER UPDATE statement 
BEFORE DELETE statement AFTER DELETE statement. 
 
Triggers instead-of 
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 
Pseudo-regist ros 
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áusula WHEN 
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 
 
Predicados de Trigger 
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 
 
Tratamento de Erros 
 
Conceitos 
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 
 
Exception Handlers 
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). 
Desvio do Fluxo 
PROCEDURE fluxo 
IS 
novo_valor VARCHAR2(5); 
BEGIN 
 
 
END; 
 
Tipos de Exceptions 
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 
 
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. 
 
Exceptions do sistemacom um nome 
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; 
 
Exceptions Pré-Definidas 
Exception Name Oracle Error SQLCODE Value 
CURSOR_ALREADY_OPEN ORA 06511 6511 
DUP_VAL_ON_INDEX ORA 00001 1 
INVALID_CURSOR ORA 01001 1001 
INVALID_NUMBER ORA 01722 1722 
LOGIN_DENIED ORA 01017 1017 
NO_DATA_FOUND ORA 01403 +100 
NOT_LOGGED_ON ORA 01012 1012 
PROGRAM_ERROR ORA 06501 6501 
ROWTYPE_MISMATCH ORA 06504 6504 
STORAGE_ERROR ORA 06500 6500 
TIMEOUT_ON_RESOURCE ORA 00051 51 
TOO_MANY_ROWS ORA 01422 1422 
VALUE_ERROR ORA 06502 6502 
ZERO_DIVIDE ORA 01476 1476 
 
Definidas pelo programador com um nome 
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); 
Exemplo 
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; 
 
EXCEPTION_INIT pragma 
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. 
 
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; 
Usando raise_application_error 
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. 
 
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. 
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 Exception 
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; 
 
Usando SQLCODE e SQLERRM 
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. 
Exemplo 
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; 
 
Exercício IV.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). 
 
Exercício IV.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 
 
Exercício IV.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). 
 
Testes e Depuração 
 
Diretrizes de Depuração 
• Encontrar o local onde ocorre o erro 
• Definir exatamente o problema 
• Reduzir o programa a um simples teste 
• Estabelecer um ambiente de teste 
 
DBMS_OUTPUT 
Pacote para mostrar mensagens: 
 
 
Exemplo de DBMS_OUTPUT 
BEGIN 
DBMS_OUTPUT.ENABLE(20000); 
DBMS_OUTPUT.PUT_LINE( Início ); 
... 
DBMS_OUTPUT.PUT_LINE( Fim ); 
END; 
Procedure Builder 
Ambiente de desenvolvimento de PL/SQL. Serve como depurador de código PL/SQL. Não 
pode depurar stored procedures, somente chamar PL/SQL Interpreter 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 
Facilidades 
• 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 Dinâmico 
DBMS_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 
DBMS_SQL.OPEN_CURSOR. 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; 
DBMS_SQL.PARSE 
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. 
DBMS_SQL.BIND_VARIABLE 
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 DBMS_SQL.DEFINE_COLUMN. 
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 
DBMS_SQL.EXECUTE 
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. 
DBMS_SQL.FETCH_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. 
DBMS_SQL.EXECUTE_AND_FETCH 
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. 
DBMS_SQL.VARIABLE_VALUE 
Determina o valor deuma 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); 
DBMS_SQL.COLUMN_VALUE 
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); 
DBMS_SQL.CLOSE_CURSOR 
Fecha o cursor. Libera os recursos utilizados pelo cursor. 
Sintaxe 
PROCEDURE CLOSE_CURSOR 
(cursor_id IN OUT INTEGER) 
 
Executar instruções DML (exceto consultas). 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) 
 
Executar instruções 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. 
 
EXECUTE. Executar consultas. 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) 
 
Executar 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) 
 
Novas facilidades 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 
 
Sugestões e Técnicas 
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 
Entrada e Saída em Arquivos PL/SQL I /O em Arquivos 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. 
 
Procedures da Package UTL_FILE 
 
Function/Procedure Description 
FOPEN Abre um arquivo para entrada e saída. Cria um arquivo de saída se ele não existir 
IS_OPEN Determina se um arquivo está aberto 
FCLOSE Fecha um arquivo 
FCLOSE_ALL Fecha todos os arquivos abertos 
GET_LINE Lê uma linha texto de um arquivo aberto. 
PUT Escreve uma linha no arquivo. Não adiciona um caracter de terminação (EOL) 
PUT_LINE Escreve uma linha no arquivo. Adiciona um caracter de terminação 
PUTF Procedure com formatação. 
NEW_LINE Escreve uma ou mais linhas de terminação no arquivo. 
FFLUSH Escreve fisicamente todas as linhas pendentes para o arquivo de saída. 
 
Seguranç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. 
 
Declaração de Tipos 
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(...); 
... 
 
Exceptions 
Exception Name Descrição 
INVALID_PATH Localização ou nome do arquivo inválido 
INVALID_MODE Modo de abertura do arquivo inválido. 
INVALID_FILEHANDLE Arquivo Inválido. 
INVALID_OPERATION Arquivo não pode ser aberto. 
READ_ERROR Um erro de sistema operacional ocorreu durante leitura. 
WRITE_ERROR Um erro de sistema operacional ocorreu durante a escrita.. 
INTERNAL_ERROR 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; 
 
Parâmetros Descrição 
location Diretório 
filename Nome do Arquivo 
open_mode 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) 
Retorno da Função FOPEN 
FOPEN retorna um file handle que deve ser passado para todas as procedures 
chamadas posteriormente. 
 
IS_OPEN 
Sintaxe: 
FUNCTION IS_OPEN(file_handle IN 
FILE_TYPE) 
RETURN BOOLEAN; 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
 
FCLOSE 
Sintaxe: 
PROCEDURE FCLOSE (file_handle IN OUT 
FILE_TYPE); 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
 
FCLOSE_ALL 
Sintaxe: 
PROCEDURE FCLOSE_ALL; 
 
GET_LINE 
Sintaxe: 
PROCEDURE GET_LINE 
(file_handle IN FILE_TYPE, 
buffer OUT VARCHAR2); 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
buffer Um buffer para receber a linha lida do arquivo. 
 
PUT 
Sintaxe: 
PROCEDURE PUT 
(file_handle IN FILE_TYPE, 
buffer IN VARCHAR2); 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
buffer Um buffer que contém a linha a ser escrita. 
 
NEW_LINE 
Sintaxe: 
PROCEDURE NEW_LINE 
(file_handle IN FILE_TYPE, 
lines IN NATURAL := 1); 
Parametro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
lines Número de linhas de terminação a serem 
escritas no arquivo. 
 
PUT_LINE 
Sintaxe: 
PROCEDURE PUT_LINE 
(file_handle IN FILE_TYPE, 
buffer IN VARCHAR2); 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
buffer Um buffer que contem a linha a ser escrita. 
 
FFLUSH 
Sintaxe: 
PROCEDURE FFLUSH 
(file_handle IN FILE_TYPE); 
Parâmetro Descrição 
file_handle Um file handle ativo retornado na função FOPEN 
Exemplo 
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; 
 
Desempenho e Otimização 
Instância Oracle 
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 
 
Otimização de instruções 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 
 
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 
 
Análise de Resultados 
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écnicas 
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