Resumo sobre Oracle

Prévia do material em texto

Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
1 
 
 
 
 
 
 
 
 
 
 
 
 
Material de Apoio 
 
 
 
Oracle 
 
 
 
 
 
 
 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
2 
ÍNDICE 
 
 
 
1. Introdução............................................................................................................................... 3 
2. Objetivo.................................................................................................................................. 3 
3. Tabelas e colunas (Tables and columns)................................................................................ 3 
4. Sequences (sequências) .......................................................................................................... 6 
5. Tabela DUAL......................................................................................................................... 8 
6. Funções e Operadores de linhas simples................................................................................ 8 
7. SQL e PL/SQL ..................................................................................................................... 11 
8. Estrutura de um Bloco PL/SQL ........................................................................................... 15 
9. Views.................................................................................................................................... 17 
10. Index (índice) ..................................................................................................................... 18 
11. Procedures, Functions e Triggers ....................................................................................... 19 
12. Tratamento de Erros (Exceções) ........................................................................................ 29 
13. Cursor ................................................................................................................................. 34 
 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
3 
1. Introdução 
 
O Oracle é um sistema gerenciador de banco de dados (SGBD) relacional utilizado 
por muitas empresas hoje em dia. Sua aplicação vem aumentando consideravelmente por 
oferecer confiabilidade (segurança/suporte) e alto desempenho com grandes volumes de 
dados e transações. 
 
2. Objetivo 
 
 Esta apostila tem como objetivo oferecer aos alunos informações para a 
compreensão de funcionalidades básicas do Oracle. 
 
3. Tabelas e colunas (Tables and columns) 
 
Tabelas são mecanismos de armazenamento para dados de um banco de dados 
Oracle. Sua função é armazenar uma série de colunas fixas. As colunas de uma tabela 
descrevem os atributos da entidade que esta sendo estipulado pela tabela. Cada coluna tem 
um nome e características específicas. 
 As características de uma coluna são divididas em duas partes: seu tipo de dado e 
seu tamanho. 
 
CREATE TABLE [schema].tabela 
 (coluna tipo_de_dado 
 [DEFAULT expressão]) 
 
o schema é o mesmo que o nome do 
dono. Na parte prática veremos com 
mais atenção a criação de tabelas. 
 
ALTER TABLE tabela 
 ADD (coluna tipo_de_dado 
 [DEFAULT expressão]) 
acrescenta colunas em uma tabela 
ALTER TABLE tabela 
 MODIFY (coluna tipo_de_dado 
 [DEFAULT expressão]) 
altera colunas em uma tabela 
DROP TABLE tabela apaga a tabela (Não é possível fazer 
rollback desta classe de comandos - 
DDL). 
TRUNCATE TABLE tabela remove todas as linhas de uma tabela 
preservando a sua estrutura. 
RENAME tabela TO novonome renomeia uma tabela. 
COMMENT ON TABLE tabela IS 
‘comentário’ 
adiciona comentário em tabela 
(dicionário de dados: 
USER_TAB_COMMENTS). 
 
 Exemplos. 
 
 Criar uma tabela: 
 
 CREATE TABLE CLIENTE 
 (CLI_COD NUMBER(6) NOT NULL PRIMARY KEY, 
 CLI_NOME VARCHAR2(70) NOT NULL, 
 CLI_SEXO CHAR(1) CHECK (CLI_SEXO IN ('M', 'F')), 
 CLI_DTNASCTO DATE NOT NULL, 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
4 
 CID_COD NUMBER(3), 
 CONSTRAINT FK_CLI_CIDADE FOREIGN KEY (CID_COD) 
REFERENCES CIDADE(CID_COD)); 
 
 DROP TABLE CLIENTE; 
 
 Alterar uma tabela: 
 
 ALTER TABLE CLIENTE ADD (CLI_CPF VARCHAR2(11)); 
 ALTER TABLE CLIENTE RENAME 
COLUMN CLI_DTNASCTO TO CLI_DTNASC; 
 ALTER TABLE CLIENTE DROP COLUMN CLI_CPF; 
 
 Apagar uma tabela: 
 
 DROP TABLE CLIENTE; 
 
Os tipos de dados (Data Types) básicos de dados no Oracle são: 
 
size = tamanho 
Tipo Descrição 
CHAR(size) Aceita dados alfanuméricos. Tamanho (size) fixo, podendo ser 
utilizado de 1 a 2000 caracteres. 
VARCHAR2(size) Aceita dados alfanuméricos. Tamanho variável pelo estipulado em 
(size), podendo variar de 1 a 4000 caracteres. 
VARCHAR(size) Mesmo que o tipo Varchar2 (não é recomendado). Existe para 
manter compatibilidade com versões anteriores. 
DATE Valor de data e hora abrangendo 01/01/4712 a.C. até 31/12/4712 
d.C. 
LONG Similar ao Varchar2, porém o tamanho máximo é de 2 GB. A 
documentação da Oracle não recomenda seu uso. Existe para 
manter compatibilidade com versões anteriores. Sugere como 
alternativa a utilização de CLOB. 
NUMBER(I,D) Valor numérico, onde: I = quantidade total de dígitos 
(inteiros+decimais) e D = quantidade de dígitos decimais 
RAW Usado para dados binários ou dados não interpretados pelo Oracle, 
como uma seqüência de caracteres gráficos, arquivos executáveis, 
arquivos PDF, etc. Tem tamanho máximo de 2000 bytes 
LONG RAW Similar ao Raw, mas com tamanho de até 2 GB 
BLOB Binary Large Object – suporta dados binários com tamanho até 4 GB 
CLOB Large Object – suporta dados caracteres com tamanho até 4 GB 
BFILE Utilizado como apontador para um arquivo binário em um servidor de 
arquivos – tamanho até 4 GB 
 
 Existem diversos outros tipos aceitos pelo Oracle, mas que não foram abordados 
aqui por não fazerem parte do escopo da disciplina. 
 
As tabelas do usuário SYS são chamadas de tabelas de dicionário de dados. Elas 
provêem um catálogo do sistema que o banco de dados usa para sua própria administração. 
 Tabelas se relacionam umas com as outras através das colunas que as mesmas têm 
em comum. O banco de dados pode reforçar esta relação via integridade referencial. O 
Oracle denomina esta ligação de constraint (constrangimento). 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
5 
3.1. Table Constraint (restrição de tabelas) 
 
O Oracle usa constaints para prevenir entrada de dados inválidos em tabelas. 
Podemos usar constraints para fazer as seguintes operações: 
• reforçar regras no nível de tabela sempre que uma linha é inserida, atualizada ou 
apagada. 
• prevenir a deleção de uma tabela se existem dependentes em outras tabelas. 
• prover regras para as ferramentas do Oracle, como o SQL Developer. 
 
Podem ser: 
NOT NULL impede que um valor nulo seja inserido nesta coluna. 
UNIQUE KEY especifica uma coluna ou uma série de colunas que não podem ter 
valores repetidos ao longo da tabela. 
PRIMARY KEY identificador único de cada linha em uma tabela. 
FOREIGN KEY estabelece e reforça uma chave estrangeira entre uma coluna e a 
tabela relacionada. 
CHECK estabelece uma condição que deve ser verdadeira. 
 
 
Exemplos 
 
CREATE TABLE EMPREGADOS( 
 EMP_COD NUMBER(6) PRIMARY KEY, 
 DEP_COD NUMBER(2) NOT NULL, 
 EMP_NOME VARCHAR2(60), 
 EMP_SALARIO NUMBER(10,2), 
CONSTRAINT CONST_EMPREGADO UNIQUE(NOME)); 
 
Adicionando uma constraint 
 
ALTER TABLE EMPREGADOS 
ADD CONSTRAINT CONST_DEPARTAMENTO_FK 
 FOREIGN KEY (DEP_COD) REFERENCES DEPARTAMENTOS(DEP_COD); 
 
Deletando uma constraint 
 
ALTER TABLE EMPREGADOS 
DROP CONSTRAINT CONST_DEPARTAMENTO_FK; 
 
Desabilitando uma constraint 
 
ALTER TABLE EMPREGADOS 
DISABLE CONSTRAINT CONST_DEPARTAMENTO_FK; 
 
Habilitando uma constraint 
 
ALTER TABLE EMPREGADOS 
ENABLE CONSTRAINTCONST_DEPARTAMENTO_FK; 
 
OBS: As constraints são armazenadas na tabela USER_CONSTRAINTS do dicionário de 
dados. 
 
Outros exemplos: 
 
“nome da constraint” 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
6 
CREATE TABLE EMPREGADOS 
(CODIGO NUMBER(10) PRIMARY KEY, 
 NOME CHAR(40) NOT NULL, 
 DEP_COD NUMBER(2) DEFAULT 10, 
 SALARIO NUMBER(7,2) CHECK (SALARIO < 180), 
 NASC DATE, 
 RG CHAR(9) UNIQUE, 
 FOREIGN KEY (DEP_COD) REFERENCES DEPARTAMENTOS(DEP_COD) 
 ) TABLEPACE USERS; 
 
Note que cada coluna tem o seu tamanho e o tipo de dados associado, bem como 
algumas colunas possuem as seguintes constraints 
 
PRIMARY KEY 
NOT NULL, 
DEFAULT 10 
CHECK (SALÁRIO < 180) 
UNIQUE 
 
A constraint FOREIGN KEY nos obriga a ter uma tabela DEPTO, cuja coluna 
DEP_COD tenha o valor que estamos inserindo nesta linha neste momento. 
 
 Outros exemplos: 
 
 ALTER TABLE CLIENTE DROP CONSTRAINT FK_CLI_CIDADE; 
ALTER TABLE CLIENTE ADD 
CONSTRAINT FK_CLI_CIDADE FOREIGN KEY 
(CID_COD) REFERENCES CIDADE; 
ALTER TABLE CLIENTE ADD CONSTRAINT NN_CLI_CPF CLI_CPF 
VARCHAR2(11) NOT NULL; 
 ALTER TABLE CLIENTE ADD CONSTRANIT PK_CLIENTE 
PRIMARY KEY (CLI_COD); 
 
 
4. Sequences (sequências) 
 
Uma seqüência ou sequence é usada para gerar uma lista de números seqüências 
para as colunas numéricas das tabelas de um banco de dados, recomendada 
principalmente para chaves primárias. As seqüências simplificam a programação de 
aplicações pois geram automaticamente e sem degradação de performance, valores 
seqüências para linhas de uma ou mais tabelas, portanto não acessam o disco, não ocorrem 
locks implícitos ou explícitos em suas estruturas e, assim a serialização é reduzida, pois dois 
ou mais comandos podem utilizá-las ao mesmo tempo. 
Os números seqüências gerados pelo Oracle são inteiros de 38 dígitos e são 
definidos em um banco de dados. A definição de uma seqüência mostra diversas 
informações como o nome da seqüência, se a geração dos números é ascendente ou 
descendente, o intervalo entre os números gerados, e outras informações. As definições de 
todas as seqüências de um banco são armazenadas em uma tabela do dicionário de dados. 
Os números seqüências são usados pelos comandos SQL para gerar um novo 
número seqüencial ou usar o número atual. Os números seqüências são gerados 
independentemente das tabelas mas podemos usar um gerador de números seqüências 
para mais de uma tabela. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
7 
Resumindo: 
• automaticamente gera números únicos; 
• é um objeto compartilhável; 
• é tipicamente usada para criar um valor para uma chave primaria; 
• substitui código em aplicações 
• aumenta a velocidade para acessar valores seqüenciais armazenados na 
memória (“cacheados”). 
 
Sintaxe: 
 
CREATE SEQUENCE sequence 
 [INCREMENT BY n] 
 [START WITH n] 
 [(MAXVALUE n|NOMAXVALUE)] 
 [(MINVALUE n|NOMINVALUE)] 
 [(CYCLE|NOCYCLE)] 
 [(CACHE n|NOCACHE)]; 
 
INCREMENT BY especifica o intervalo entre os números da sequence - padrão 1 
START WITH n especifica o primeiro valor da sequence - padrão 1 
MAXVALUE n especifica o valor máximo para a sequence atingir 
NOMAXVALUE sem valor máximo 
MINVALUE n especifica o valor mínimo da sequence 
NOMINVALUE sem valor mínimo 
CYCLE começa a gerar do começo quando o limite for alcançado 
NOCYCLE não começa a gerar do começo quando o limite for alcançado 
CACHE n especifica quantos valores o Oracle Server irá pré-alocar na memória 
para disponibilidade imediata - padrão 20 valores. 
NOCACHE não pré-aloca nada, fazendo consultas ao dicionário de dados a cada 
incremento da sequence. 
 
Usando uma sequence: 
 
CURRVAL Retorna o valor corrente de uma sequence. Considera somente o 
valor da sessão/conexão ativa. Somente acessível após o primeiro 
NEXTVAL da sessão/conexão. 
NEXTVAL Retorna o próximo valor de uma sequence e faz o seu incremento. 
Considera a instância do banco para que o incremento nunca se 
repita para diferentes ssões/conexões. 
 
Exemplo - gravando dados em uma tabela com chave primária: 
 
CREATE SEQUENCE SEQ_CLIENTE 
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE 
NOMINVALUE 
NOCYCLE 
NOCACHE; 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
8 
Situação 1: 
 
INSERT INTO CLIENTE (CLI_COD,CLI_NOME) 
VALUES (SEQ_CLIENTE.NEXTVAL,'TESTE'); 
 
INSERT INTO CLIENTE_FONE (CLI_COD, FONE_COD, FONE_NUMERO) 
VALUES (SEQ_CLIENTE.CURRVAL, 1, '(18) 3333-4567'); 
 
INSERT INTO CLIENTE_FONE (CLI_COD, FONE_COD, FONE_NUMERO) 
VALUES (SEQ_CLIENTE.CURRVAL, 2, '(18) 4444-5566'); 
 
Situação 2: 
 
CREATE OR REPLACE PROCEDURE INSERE_CLIENTE 
 (P_CLI_COD OUT CLIENTE.CLI_COD%TYPE, 
 P_CLI_NOME IN CLIENTE.CLI_NOME%TYPE) 
IS 
BEGIN 
 SELECT SEQ_CLIENTE.NEXTVAL INTO P_CLI_COD FROM DUAL; 
 INSERT INTO CLIENTE(CLI_COD,CLI_NOME) 
 VALUES(P_CLI_COD,P_CLI_NOME); 
END; 
 
5. Tabela DUAL 
 
Tabela do usuário SYS com acesso público para ser utilizado em instruções “select” 
que necessitam retornar uma única linha, sem necessidade de acesso a atributos de outras 
tabelas. 
 Exemplo: 
 
 -- retorna a data do sistema 
 SELECT SYSDATE FROM DUAL; 
 
-- realiza a operação matemática 
SELECT (145.6*85.1/4) AS CONTA FROM DUAL; 
 
-- exibir o usuário conectado no banco 
SELECT USER FROM DUAL; 
 
6. Funções e Operadores de linhas simples 
 
 No Oracle, como em todos os outros SGBD’s, há uma série de funções pré-definidas 
para serem utilizadas no intuito de transformar dados a serem recuperados ou manipulados 
com comandos SQL. Essas funções podem: 
 
• manipular itens de dados; 
• aceitar argumentos e retornar um valor; 
• agir sobre cada linha retornada; 
• retornar um resultado por linha; 
• modificar o tipo de dado 
• podem ser aninhados 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
9 
 
Algumas das funções mais utilizadas: 
 
Funções Gerais 
LOWER converte uma string em minúscula 
UPPER converte uma string em maiúscula 
INITCAP Retorna a string com o primeiro caractere de cada 
palavra em maiúsculo e os caracteres restantes 
em minúsculo 
CONCAT Concatenação de strings - equivalente a || 
SUBSTR(coluna/expressão,m[,n]) retorna caracteres especificados a partir da 
posição m até a posição n da coluna/expressão. 
LENGTH(coluna/expressão) retorna o número de caracteres (tamanho) da 
coluna/expressão 
INSTR 
(str1,str2,p_ini,ocorrencia,’string’) 
retorna a posição em que a string2 está contida 
de string1. 
p_ini: posição de string1 a partir da qual será 
iniciada a busca – opcional – default 1 
ocorrência: indica o número da ocorrência – se 
retorna na 1a. ocorrência encontrada, na 2a., e 
assim por diante – opcional – default 1. 
LPAD(string1,n,string2) 
 
Retorna string1 preenchida à esquerda até o 
comprimento de n com os caracteres de string2. 
RPAD(string1,n,string2) 
 
Retorna string1 preenchida à direita até o 
comprimento de n com os caracteres de string2. 
ROUND(coluna/expressão,n) arredonda a coluna/expressão para n casas 
decimais. 
TRUNC(coluna/expressão,n) trunca a coluna/expressão em n casas decimais e 
se n for omitido, pega somente a parte inteira da 
expressão 
NVL(coluna/expressão,n) converte um valor nulo na expressão n 
Ex: NVL(salário,’não estipulado’) 
DECODE(coluna/expressão, 
pesquisa1, resultado1[, pesquisa2, 
resultado2,...,][default]) 
decifra a coluna/expressão e compara com 
pesquisaN, devolvendo como resultado o 
resultadoN, se nenhum for encontrado segue o 
default 
MOD(m,n) retorna o resto da divisão 
Funções de data e hora 
SYSDATE retorna a data e a hora do sistema 
data + numero resulta em data + numero dias (data) 
date - numero resulta em data - numero (data) 
date - date número de dias (inteiro) 
date + numero/24 adiciona um número de horas a uma data (data) 
MONTHS_BETWEEN(data1,data2) Retorna o número de meses entre data1 e data2 
ADD_MONTHS(d,x) Retorna a datad mais x meses 
NEXT_DAY(data,dia_semana) A partir da data informada, retorna a data do 
próximo dia da semana especificado. 
Onde dia_semana; 
1–domingo, 2-segunda,.....,6-sábado 
LAST_DAY(data) Retorna a data do último dia do mês 
ROUND(data,formato) arredonda a data para o formato especificado 
(opcional) ou para o menor valor possível. 
TRUNC trunca a data, para o formato especificado 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
10 
(opcional) ou para o menor valor possível. 
Funções de conversão 
TO_CHAR(x,formato) converte x em um tipo de caractere, formato 
opcional 
TO_DATE(y,formato) converte y para data 
TO_NUMBER(x) converte x para tipo numérico 
 
 O tratamento e manipulação do tipo DATE no Oracle exige alguns cuidados 
especiais, pois este tipo armazena sempre data e hora simultaneamente num mesmo 
atributo ou variável. Para isso, é indispensável a utilização das funções TO_CHAR e 
TO_DATE, em ambas pode ser especificado uma máscara de formatação de data e hora. 
Na tabela abaixo são apresentados os mais comuns: 
 
DD Dia: 01, 02…31 
MM Mês: 01...12 
YY Ano de 2 dígitos: 07,08 
YYYY Ano de 4 dígitos: 2007, 2008 
HH24 Hora no padrão 24 hs: de 00 a 23 
MI Minutos: 00 a 59 
SS Segundos: 00 a 59 
d Dia da semana (1-domingo a 6-sábado) 
 
 Existem vários outros formatos que podem ser verificados nos arquivos de referência 
no diretório público. 
 Algumas situações serão apresentadas para melhor entendimento. 
 
SELECT NOT_NUM, 
 TO_CHAR(NOT_DATA,'DD/MM/YYYY HH24:MI:SS') AS DATA 
FROM NOTA 
WHERE NOT_DATA BETWEEN 
 TO_DATE('01/01/2000 00:00:00','DD/MM/YYYY HH24:MI:SS') AND 
 TO_DATE('091231235959', 'YYMMDDHH24MISS'); 
 
Neste caso note que para exibir o atributo NOT_DATA (definido como “date” na 
tabela) foi utilizada a função TO_CHAR para formatar o modo de exibição. Caso não tivesse 
sido usada a função, seria exibida a data no formato padrão do “client” do Oracle. 
Outro detalhe está presente na cláusula WHERE quando há a comparação do 
atributo NOT_DATA. Tem-se que comparar DATE com DATE, por isso transformar a string 
entre aspas em DATE com a função TO_DATE. 
 
-- SOMAR 3 HORAS EM UMA DATA 
SELECT TO_CHAR(SYSDATE+(1/24)*3,’DD/MM/YYYY HH24:MI’) AS DATA 
FROM DUAL; 
 
-- QUANTOS DIAS HÁ ENTRE 2 DATAS 
SELECT TO_DATE('01/01/08','DD/MM/YY')-TO_DATE('01/05/05','DD/MM/YY') 
FROM DUAL; 
 
-- INSERT EM UMA TABELA COM ATRIBUTO DATE 
INSERT INTO NOTA(NOT_NUM, CLI_COD, NOT_DATA, NOT_TOTAL) 
VALUE(150,1,TO_DATE('01/01/08 HH24:MI','DD/MM/YY 10:30'),5230); 
 
 Mais referências podem ser encontradas no diretório public. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
11 
7. SQL e PL/SQL 
 
Structured Query Language (Linguagem estruturada de consulta) a SQL permite que 
você se comunique com o banco de dados com as seguintes vantagens: 
1.Eficiência; 
2.Facilidade de aprendizado e uso; 
3.Funcionalidade total - permite definir, manipular e recuperar dados em tabelas. 
 
 PL/SQL - Procedural Language que estende os recursos da SQL através do uso de 
lógica de aplicação. 
 
7.1. Declarações SQL 
 
Select Recuperação de dados (DRS) 
Insert 
Update 
Delete 
Linguagem de manipulação de dados 
(DML) 
Create 
Alter 
Drop 
Rename 
Truncate 
Linguagem de definição de dados (DDL) 
Commit 
Rollback 
Savepoint 
Controles de Transações 
Grant 
Revoke 
Linguagem de controle de dados (DCL) 
 
7.2. Benefícios da PL/SQL 
 
 Com a PL/SQL podemos realizar toda a camada de aplicação lógica internamente no 
banco de dados, ficando a cargo da estação somente a camada de interface com o usuário. 
1. Aumento de performance através da redução de tráfico de rede; 
2. Agrupamento de lógicas relacionadas em blocos; 
3. Aninhar blocos menores em maiores para construir programas mais poderosos; 
4. Quebrar um problema complexo em blocos menores para facilitar a 
administração; 
5. Colocar código reusável em bibliotecas; 
6. Alta portabilidade (pode migrar para qualquer host); 
7. Posso declarar identificadores; 
8. Estrutura de controle procedural (pascal, c); 
9. Pode manipular erros; 
 
Qualquer rotina de um programa PL/SQL é baseada em blocos. Um programa 
PL/SQL pode ser composto por um ou mais blocos, os quais podem ocorrer 
seqüencialmente (um depois do outro) ou aninhados (um dentro do outro). 
 Serão apresentados mais detalhes mais à frente. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
12 
7.3. Sintaxe 
7.3.1. Select 
 
SELECT * FROM tabela; seleciona todos os elementos da tabela 
SELECT 
coluna1,coluna2,...,colunan 
FROM tabela; 
seleciona as colunas mencionadas da 
tabela 
SELECT (coluna1+300)+100, 
coluna2* 2 FROM tabela; 
seleciona as colunas mencionadas com 
operadores matemáticos que têm a 
seguinte precedência: * / + - para mudar a 
preferência tenho que usar parênteses. 
SELECT coluna1*1.08 AS “Novo 
salário” FROM tabela; 
posso dar um apelido as colunas 
associando um alias ao mesmo. As aspas 
duplas preservam a formatação interna 
dos caracteres. 
SELECT nome||’ ‘||sobrenome 
FROM tabela; 
|| é o operador de concatenação. 
SELECT DISTINCT salário FROM 
tabela; 
a clausula distinct elimina as linhas 
duplicadas na consulta. 
 
7.3.2. Restringindo e ordenando dados 
 
SELECT * 
FROM tabela 
WHERE nome= ‘JOÃO’; 
seleciona todos os elementos da tabela 
cujo nome seja exatamente ‘JOÃO’. 
Operadores de comparação = (igual) 
> (maior) 
>= (maior ou igual) 
< (menor) 
<= (menor ou igual) 
<> (diferente) 
 
SELECT nome,salário 
FROM tabela 
WHERE cargo<>‘diretor’ and 
salário > 10000; 
posso colocar tantas cláusulas restritivas 
quantas eu quiser adicionando AND, OR 
ou NOT - operadores lógicos.Têm a 
seguinte precedência: 
NOT 
AND 
OR 
Outros operadores de comparação BETWEEN ... AND... - entre dois valores 
(muito usado para datas) 
IN(lista) - coloco todos os elementos que 
quero comparar 
LIKE – como associado ao % - que 
substitui qualquer expressão - e ao _ 
(underline) - que substitui qualquer 
caractere - faz buscas por expressões 
parciais. 
IS NULL - compara com valores nulos dos 
campos. 
SELECT nome||’ ‘||sobrenome a cláusula order by ordena a consulta, 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
13 
FROM tabela ORDER BY nome 
[DESC][ASC]; 
trazendo os dados de forma ordenada em 
forma descendente DESC - ou 
ascendente ASC (padrão). Podem ser 
usadas múltiplas colunas na ordenação 
 
7.4. Mostrando dados de várias tabelas 
 
SELECT 
tabela1.coluna1,tabela2.coluna2 
FROM tabela1,tabela2 
WHERE 
tabela1.chave1 = tabela2.chave2 
Quando dados de mais de uma tabela no 
banco de dados são requeridos, podem 
ser juntados com a cláusula WHERE ao 
lado. Geralmente isto se dá com chaves 
primárias e chaves estrangeiras. 
SELECT 
tabela1.coluna1,tabela2.coluna2 
FROM tabela1,tabela2 
A SQL ao lado devolve um produto 
cartesiano das duas tabelas (mostra para 
cada registro da 1a todos os registros da 2a 
) 
Equijoin Envolve duas colunas iguais/correlatas em 
ambas as tabelas - geralmente chave 
primária e chave estrangeira 
Non-equijoin Duas colunas diferentes compõem a 
consulta, como mostrar os salários da 
tabela A que estão entre dois valores da 
tabela B. 
Outer join 
SELECT tabela1.coluna1, 
tabela2.coluna2 
FROM tabela1 , tabela2 
 WHERE tabela1.codigo(+) = 
tabela2.codigo; 
Retorna registros que não 
necessariamente encontraram 
correspondência. No Exemplo ao lado 
pegamos os dados da tabela1 que não 
têm correspondente na tabela2 e os 
exibimos mesmo assim com valor nulo. 
 
Self join 
SELECT unique e1.cargo, e1.nome 
 FROM empregados e1, 
empregados e2 
WHERE e1.chefe = e2.emp_codigo; 
Podemos ter uma tabela relacionada com 
ela mesmo e extrair os dados baseados 
nesta informação. 
EMPREGADOS 
EMP_COD, NOME, CHEFE 
 
7.5. Agregando dados usando funções de agrupamento 
 
 As funções de grupo buscam resumos de operações, tais como totalda folha, saldo, 
média, etc... 
AVG Média (ignora valores nulos) 
COUNT Conta o número de registros que 
satisfazem o critério especificado. 
MAX pega o maior valor (ignora valores nulos) 
MIN pega o menor valor (ignora valores nulos) 
STDDEV desvio padrão (ignora valores nulos) 
SUM soma os valores (ignora valores nulos) 
VARIANCE calcula a variância (ignora valores nulos). 
GROUP BY 
 
agrupa os valores de uma consulta por 
esta condição. Todos os itens da consulta 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
14 
SELECT 
departamento,AVG(salário) 
FROM empregados 
GROUP BY departamento 
têm que estar agrupados ou usando 
funções de agrupamento. 
HAVING 
 
SELECT 
departamento,AVG(salário) 
FROM empregados 
GROUP BY departamento 
HAVING AVG(salário) > 1000 
ORDER BY departamento 
 
Esta cláusula é usada para especificar 
quais grupos serão mostrados. 
 
7.6. Subqueries 
 
Podemos nos deparar com a seguinte ordem: Mostre todos os salários que sejam 
maiores que o do empregado ‘Jose’. 
 A SQL que recupera os dados do banco de dados tem que passar como condição de 
busca o salário do ‘Jose’ para a SQL que recupera os dados dos outros funcionários. 
 
SELECT departamento,nome,salario 
FROM empregados 
WHERE salario > (SELECT salario 
 FROM empregados 
 WHERE nome = ‘Jose’); 
 
Para usar a subqueries temos algumas regras básicas: 
 
• Encapsule as subqueries entre parênteses; 
• coloque as subqueries do lado direito da comparação; 
• não coloque ORDER BY em uma subquerie (Oracle 8 ou inferior); 
• use operadores de linha única (=,>,<) com consultas de linha única; 
• use operadores de múltiplas linhas (in, exists) com subqueries de múltiplas 
linhas. 
 
7.7 Controlando Transações 
 
 O servidor Oracle assegura a consistência dos dados baseado em transações. 
Transações nos proporcionam mais flexibilidade e controle quando estamos mudando 
dados. 
 As chaves de transações são os comandos: 
 
COMMIT finaliza a transação corrente, fazendo 
todas as atualizações pendentes e 
transformando-as em permanentes. 
ROLLBACK finaliza a transação corrente descartando 
todas as mudanças realizadas. 
SAVEPOINT marca um ponto de inicio para uma dada 
transação, nomeando-a: 
SAVEPOINT a; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
15 
8. Estrutura de um Bloco PL/SQL 
 
 PL/SQL é uma linguagem estruturada em blocos significando que programas podem 
ser divididos em blocos lógicos. Constitui-se de três sessões: 
 
Declarativa contém todas as variáveis, constantes, 
exceções definidas pelo usuário que 
estão referenciadas nas sessões de 
execução 
Opcional 
Executável contém as declarações SQL para 
manipulação de dados no banco de 
dados e instruções PL/SQL para 
manipulação de dados no bloco. 
Obrigatória 
Exception Handling 
(manipulação de exceções) 
Especifica a ação a tomar quando erros 
e condições anormais ocorrem na 
sessão executável. 
Opcional 
 
 
Tipos de blocos 
 
Anonymous Procedure Function 
[DECLARE] 
 
BEGIN 
 -- comandos 
[EXCEPTION] 
 
END; 
PROCEDURE nome IS 
 
BEGIN 
 -- comandos 
[EXCEPTION] 
 
END; 
FUNCTION nome 
RETURN tipo_de_dado 
IS 
 
BEGIN 
 -- comandos 
 RETURN valor; 
[EXCEPTION] 
 
END; 
 
8.1. Estruturas de Controle 
 
 Podemos controlar o fluxo de um bloco PL/SQL com algumas estruturas de controles 
que existem na maioria das linguagens que conhecemos. 
 
Nota: Toda instrução PL/SQL é finalizada com ponto-e-vírgula (;). 
 
Atribuição ( := ) 
 
( := ) - dois pontos igual 
 variável := expressão; 
 vnome := ‘jose’; 
IF THEN ELSE 
 
IF condição THEN 
sequencia_de_intruções; 
ELSIF condição THEN 
sequencia_de_intruções; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
16 
ELSE 
sequencia_de_intruções; 
END IF; 
 
 A única observação é que a sintaxe precisa ser exata e não temos ferramentas de 
interação para analisar onde está o erro precisamente, ainda mais se o mesmo ocorrer 
dentro desta estrutura. 
 
LOOP 
 
 PL/SQL nos fornece um número de facilidades para estrutura de loop para repetir 
uma seqüência de três maneiras básicas: Loop Básico, Loop FOR e Loop WHILE. 
 
LooP Básico: 
 
DECLARE 
 VAR_COD EMPREGADO.COD_EMPREGADO%TYPE := 100; 
 VAR_CONTADOR NUMBER(2) :=1; 
BEGIN 
 LOOP 
 INSERT INTO ITEM(COD_EMPREGADO,ORDEM) 
 VALUES(VAR_COD,VAR_CONTADOR); 
 VAR_CONTADOR := VAR_CONTADOR+1; 
 EXIT WHEN VAR_CONTADOR > 10; 
 END LOOP; 
END; 
 
Loop FOR: 
 
DECLARE 
 VAR_COD EMPREGADO.COD_EMPREGADO%TYPE := 100; 
 VAR_CONTADOR NUMBER(2); 
BEGIN 
 FOR VAR_CONTADOR IN 1..10 LOOP 
 INSERT INTO ITEM(COD_EMPREGADO,ORDEM) 
 VALUES(VAR_COD,VAR_CONTADOR); 
 END LOOP; 
END; 
 
Loop WHILE: 
 
DECLARE 
VAR_COD EMPREGADO.COD_EMPREGADO%TYPE := 100; 
VAR_CONTADOR NUMBER(2) :=1; 
BEGIN 
WHILE VAR_CONTADOR < 10 LOOP 
 INSERT INTO ITEM(COD_EMPREGADO,ORDEM) 
 VALUES(VAR_COD,VAR_CONTADOR); 
 VAR_CONTADOR := VAR_CONTADOR+1; 
END LOOP; 
END; 
 
 Posso aninhar os loops à vontade e há ainda um diferencial importante que é o fato 
de podermos nomear um loop e sair dele com “EXIT nome“. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
17 
CASE 
 
 Sintaxe 1: 
 
 CASE VAR_TESTE 
 WHEN VALOR1 THEN ...INSTRUÇÕES...; 
 WHEN VALOR2 THEN ...INSTRUÇÕES...; 
 ELSE ...INSTRUÇÕES...; 
 END CASE; 
 
 Sintaxe 2: 
 
 CASE 
 WHEN VAR_TESTE_1 = VALOR1 THEN 
...INSTRUÇÕES...; 
 WHEN VAR_TESTE_1 < VALOR2 THEN 
...INSTRUÇÕES...; 
 ELSE 
...INSTRUÇÕES...; 
 END CASE; 
 
 
 Sintaxe 3 (case com “select”): 
 
SELECT CASE 
 WHEN CLI_COD='01' THEN 
 'CODIGO 1' 
 WHEN CLI_COD = '02' THEN 
 'CODIGO 2' 
 ELSE 
 'OUTRO CODIGO' 
 END AS LOCAL 
 FROM CLIENTE; 
 
 
9. Views 
 
 Devemos usar uma view para: 
 
• restringir o acesso a dados; 
• fazer queries complexas ficarem simples; 
• permitir a independência de dados; 
• para apresentar diferentes visões do mesmo dado. 
 
Criando uma view: 
 
CREATE [OR REPLACE] [FORCE] [NOFORCE] VIEW visão 
AS subquerie 
 [WITH CHECK OPTION [CONSTRAINT constriant]] 
[WITH READ ONLY] 
 
OR REPLACE se já houver substitua; 
FORCE cria independente da existência das tabelas; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
18 
 
NO FORCE cria se a tabela existir (default); 
WITH CHECK OPTION especifica que somente linhas acessíveis à view 
podem ser atualizadas ou inseridas; 
WITH READ ONLY assegura que nenhuma operação DML pode ser 
feita sobre esta view. 
 
Exemplo: 
 
CREATE OR REPLACE VIEW V_CLIENTES (CODIGO, NOME, TOTAL) 
 AS SELECT C.CLI_COD, C.CLI_NOME, SUM(N.NOT_TOTAL) AS TOTAL 
 FROM CLIENTE C, NOTA N 
 WHERE C.CLI_COD = N.CLI_COD 
 GROUP BY C.CLI_COD, C.CLI_NOME; 
 
10. Index (índice) 
 
 Este tópico somente aborda sintaxe e algumas particularidades para a criação de 
índices que, sucintamente, são estruturas auxiliares que contém a chave do índice 
(composta de um ou mais atributos da tabela) de forma ordenada e o endereço do registro 
correspondente na tabela e é utilizado para otimizar consultas ao banco de dados. 
 Exemplo: se utilizamos muito buscas por data da nota fiscal, então aconselha-se a 
termos um índice pela data da nota fiscal referente à tabela de nota fiscal; ou então para 
consultas por nome do cliente. 
 
 SELECT * 
 FROM NOTA N 
 WHERE N.NOT_DATA BETWEEN (SYSDATE-30) AND SYSDATE; 
 
 Chave do índice: NOT_DATA 
 
 SELECT * 
 FROM CLIENTE C 
 WHERE CL.CLI_NOME LIKE ‘MARIA%’; 
 
 Chave do índice: CLI_NOME 
 
 Enfim, é preciso um aprofundamento maior no assunto para que questões 
fundamentais sejam assimiladas e tenha-se a compreensão exata da necessidade dos 
índices. 
 
Sintaxe: 
 
CREATEINDEX NOME_INDICE 
ON TABELA (COLUNA_1,COLUNA_2,...,COLUNA_N) TABLESPACE X; 
 
Cria o índice da tabela nas colunas, sendo que a ordem é a que foi digitada. 
 
Guia para criar índices: 
• se a coluna é usada freqüentemente em clausulas WHERE; 
• se a coluna contém uma grande faixa de valores; 
• se a coluna contém uma grande quantidade de valores nulos; 
• se duas ou mais colunas são usadas freqüentemente juntas em clausulas 
WHERE; 
Chave do índice 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
19 
• se a tabela for grande e a maioria das consultas recuperar de 2 a 4% das linhas. 
 
Guia para não criar um índice: 
• se a tabela for pequena; 
• se a coluna não for freqüentemente usada como condição em consultas; 
• se a maioria das consultas recuperar mais de 4% das linhas; 
• se a tabela for atualizada constantemente ( o banco vai sofrer para fazer a 
manutenção). 
 
Removendo índices: 
 
DROP INDEX índice; 
 
11. Procedures, Functions e Triggers 
 
Procedures e functions são coleções de comandos SQL e PL/SQL. 
São objetos do banco de dados que nos permitem armazenar blocos de instruções 
para executar determinadas tarefas. 
 
Atenção: não confundir objeto de banco de dados (procedure, 
tables,indexes,sequences, etc...) com objeto de Orientação a Objetos. O Oracle também 
suporta a criação de classes e objetos e todos os recursos da Orientação a Objetos; mas 
para isto são necessários comandos e instruções específicos. 
 
Características 
 
1. Armazenados de forma compilada no banco de dados (todo o texto ou fonte das 
procedures e functions) 
 
2. Podem chamar outras procedures e functions ou serem chamadas 
 
3. Podem ser chamadas por usuários em ambiente Cliente/Servidor (Delphi, VB) ou 
Web (asp, php) 
 
 Procedures e functions são semelhantes, a única diferença é que as functions podem 
retornar valores por elas próprias. Já nas procedures a única forma de termos retorno de 
valor é definindo uma variável de saída, o que pode inviabilizar sua utilização em alguns 
casos. 
 
Utilização 
 
 1. Definir uma central de funções de negócios 
 exemplo: criar pedidos / eliminar cliente 
 
 2. Armazenar atividades em lotes (Job Batch) no banco de dados 
 exemplo: fechamento mensal 
 
 3. Encapsular uma transação 
 exemplo: reprocessar custo médio ou estoque de produtos 
 
 4. Canalizar as alterações de uma tabela em um único local. 
exemplo: todas as alterações de salário e departamento do 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
20 
empregado é feita por uma única transação. 
 
 5. Criar códigos genéricos (comuns ou compartilhados) 
 exemplo: manter as aplicações em um único banco de dados 
 
6. Implementar restrições de segurança para dados protegidos diretamente pelo 
próprio gerenciador. 
exemplo: somente determinados usuários terão direito de executar um 
determinado procedimento/função. 
 
Comandos válidos 
 
 1. SQL DML ou PL/SQL 
 
2. Chamada de outros procedimentos e funções armazenados no banco (remotos ou 
não) 
 
Comandos inválidos 
 
1. DDL (exceto em SQL dinânico) 
 
2. SQL Dinâmico (em versões anteriores à 8 e sem a utilização de execute 
immediate) 
 
3. COMMIT, ROLLBACK e SAVEPOINT para procedimentos e funções que serão 
chamados por uma Database Trigger e por procedures remotas 
 
11.1. Erros de Compilação 
 
 A compilação de PL/SQL é realizada pelo mecanismo Oracle PL/SQL. Erros de 
sintaxe ocorridos durante a compilação são armazenados no banco de dados. 
 
 Para ver os erros: 
 
 1. Use no SQL * Plus, o comando SHOW ERRORS; 
 
 2. Selecione os erros através das visões (views): 
 . USER_ERRORS 
 . ALL_ERRORS 
 . DBA_ERRORS 
 
 Para ter acesso às visões ALL_ERRORS e DBA_ERRORS é necessário que os 
usuários tenham permissão para tanto. Já a visão USER_ERRORS é acessível aos 
usuários que tem permissão de acesso de visão ao catálogo (dicionário de dados). 
 
 O SHOW ERRORS exibirá: 
 . NAME nome do objeto 
 . TYPE tipo do objeto (procedure,function,package,etc) 
 . LINE número da linha em que ocorreu o erro 
 . POSITION coluna da linha em que ocorreu o erro 
 . TEXT descrição do erro 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
21 
11.2. Armazenamento no banco de dados 
 
 O Oracle armazena em seu dicionário de dados o código fonte e o compilado de 
cada procedure, function, trigger ou package: 
 
 1. Compila os comandos PL/SQL do código fonte 
 
 2. Armazena os dados do procedimento: 
a) Nome do objeto 
b) Código fonte 
c) Caminho de acesso (parse tree) 
d) Código compilado 
e) Informações sobre dependência (referência cruzada) 
 
Nota: comandos SQL em procedimentos não são armazenados de forma compilada 
(parsed). 
 
 Visões do dicionário de dados sobre procedimentos, funções, etc: 
 . USER_SOURCE 
 . ALL_SOURCE 
 . DBA_SOURCE 
 
 Colunas relevantes: 
 . OWNER (não tem em USER_SOURCE) 
 . NAME 
 . TYPE 
 . LINE 
 . TEXT 
 
 Exemplo: 
 
SELECT TEXT 
FROM USER_SOURCE 
WHERE NAME = 'NOME_DO_OBJETO'; 
 
11.3. Descrição (describe) 
 
 Podemos visualizar as informações de estrutura de procedures, functions e packages 
através do comando DESCRIBE. 
 Sintaxe: 
 
DESCRIBE NOME_DO_OBJETO; 
ou 
DESC NOME_DO_OBJETO; 
 
11.4. Dependências 
 
Quando criamos procedures, functions ou triggers que em seu bloco de instruções é 
feito uso ou chamada de outras procedures ou functions, estes objetos que chamaram estes 
outros ficam dependentes deles. Ou seja, se numa procedure A chamo uma function B, 
temos que a procedure A é dependente a function B, de tal forma que se forem feitas 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
22 
modificações na function B e estas modificações resultarem em erro que a invalidem, então 
a procedure A também é invalidada. Para que a procedure A seja revalidada, tem-se que 
revalidar primeiro a function B e depois a procedure A. Isto é possível através da 
recompilação destes objetos. 
 Um procedimento é dependente de: 
 
1. Objetos de banco de dados que tem referência (dependência direta), como 
procedures, functions, tables, views, synonyms e sequences 
 
2. Objetos que outros objetos dependem dele (dependência indireta). Exemplo: uma 
procedure utiliza uma view de uma table, assim esta procedure tem dependência 
indireta desta table. 
 
O Oracle verifica as dependências automaticamente. Quando um objeto do 
banco de dados é modificado e o mesmo possui objetos dependentes, estes são marcados 
para serem recompilados na coluna STATUS da tabela USER_OBJECTS. 
Objeto dependente deve ser recompilado se o objeto por ele referenciado 
for alterado. A recompilação dos dependentes é feita automaticamente em rempo de 
execução. 
 Podemos obter informações das dependências através das views: 
 . USER_DEPENDENCIES 
 . ALL_DEPENDENCIES 
 . DBA_DEPENDENCIES 
 
11.5. Recompilação 
 
 Procedures e functions podem ser recompilados manualmente pelo desenvolvedor 
ou automaticamente pelo RDBMS. 
 
 Recompilação manual, sintaxe: 
 
ALTER PROCEDURE [FUNCTION] 
[SCHEMA] NOME_DO_OBJETO COMPILE; 
 
11.6. Procedures 
 
Sintaxe de criação 
 
CREATE [OR REPLACE] PROCEDURE [SCHEMA.] NOME_DA_PROCEDURE 
 ( ARGUMENTO_1 [IN/OUT/IN OUT] TIPO_DO_DADO, 
 ARQUMENTO_2 [IN/OUT/IN OUT] TIPO_DO_DADO, 
 ... ) 
IS [AS] 
 ...DECLARAÇÃO DE VARIÁVEIS... 
 VARIAVEL_1 TIPO_DO_DADO; 
BEGIN 
 INSTRUÇÕES PL/SQL... 
 ... 
END; 
/ 
SHOW ERRORS; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
23 
 
Opção Descrição 
OR REPLACE Depois de criada uma procedure, se esta opção não for especificada 
numa nova compilação, dará uma mensagem de erro mostrando que a 
esta procedure já existe. O comando REPLACE troca a procedure 
especificada e suas instruções internas pela que está sendo 
compilada. 
SCHEMA Ou usuário dono do objeto procedure, se nada for epecificado será 
criado com ousuário corrente como dono. 
IN Identifica se o parâmetro é de entrada – é a especificação default se 
nada for informado. Os parâmetros IN não podem ser seu conteúdo 
alterado dentro da procedure. 
OUT Identifica se o parâmetro é de saída. Os parâmetros de saída podem 
ser alterados dentro da procedure, não sendo possível receber 
passagem de valores externos. 
IN OUT Identifica que o parâmetro é de entrada e de saída. Isto significa que 
além de uma variável com esta especificação poder receber valores 
externos por parâmetro, esta também pode ser modificada dentro da 
procedure e ter seu valor recuperado no meio externo 
(aplicação/programa). 
SHOW ERRORS Exibe os erros da procedure, se houver. 
 
Exemplo 1: 
 
 Criar uma procedure que receba como parâmetro de entrada o código de um 
funcionário e retorne em uma parâmetro/variável de saída o valor total dos vales retirados 
por este funcionário. 
 
CREATE OR REPLACE PROCEDURE CALCULA_VALES 
 (P_FUN_COD IN FUNCIONARIO.FUN_COD%TYPE, 
 P_TOTAL OUT NUMBER) 
IS 
BEGIN 
 SELECT SUM(VALOR) INTO P_TOTAL 
 FROM VALES_FUNC 
 WHERE FUN_COD = P_FUN_COD; 
END; 
/ 
SHOW ERRORS; 
Testando a procedure no SQL*PLUS: 
 
-- declarando uma variável para receber o parâmetro de saída 
SQL> VARIABLE V_TOTAL NUMBER; 
 
-- executando a procedure 
SQL> EXECUTE CALCULA_VALES(387,:V_TOTAL); 
 
-- exibindo o conteúdo da variável que armazenou o retorno da ---- 
-- procedure 
SQL> PRINT V_TOTAL; 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
24 
Sintaxe para apagar uma procedure: 
 
 SQL> DROP PROCEDURE NOME_DA_PROCEDURE; 
 
11.7. Functions 
 
Sintaxe de criação: 
 
CREATE [OR REPLACE] FUNCTION [SCHEMA.] NOME_DA_FUNCTION 
 ( ARGUMENTO_1 [IN/OUT/IN OUT] TIPO_DO_DADO, 
 ARQUMENTO_2 [IN/OUT/IN OUT] TIPO_DO_DADO, 
 ... ) RETURN TIPO_DE_DADO 
IS [AS] 
 ...DECLARAÇÃO DE VARIÁVEIS... 
 VARIAVEL_1 TIPO_DO_DADO; 
BEGIN 
 INSTRUÇÕES PL/SQL... 
 ... 
 RETURN(...); 
END; 
/ 
SHOW ERRORS; 
Opção Descrição 
OR REPLACE Depois de criada uma function, se esta opção não for especificada 
numa nova compilação, dará uma mensagem de erro mostrando 
que a esta function já existe. O comando REPLACE troca a function 
especificada e suas instruções internas pela que está sendo 
compilada. 
SCHEMA Ou usuário dono do objeto function, se nada for epecificado será 
criado com o usuário corrente como dono. 
IN Identifica se o parâmetro é de entrada – é a especificação default se 
nada for informado. Os parâmetros IN não podem ser seu conteúdo 
alterado dentro da function. 
OUT Identifica se o parâmetro é de saída. Os parâmetros de saída 
podem ser alterados dentro da function, não sendo possível receber 
passagem de valores externos. 
IN OUT Identifica que o parâmetro é de entrada e de saída. Isto significa 
que além de uma variável com esta especificação poder receber 
valores externos por parâmetro, esta também pode ser modificada 
dentro da function e ter seu valor recuperado no meio externo 
(aplicação/programa). 
SHOW 
ERRORS 
Exibe os erros da procedure, se houver. 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
25 
Exemplo 1: 
 
 Criar uma function que receba como parâmetro de entrada o número de uma nota 
fiscal e retorne o valor total dos produtos desta nota. 
 
CREATE OR REPLACE FUNCTION RET_TOTAL_PROD 
 (P_NF_NUM IN NOTA.NF_NUM%TYPE) RETURN NUMBER 
IS 
 V_TOTAL NUMBER; 
BEGIN 
 SELECT SUM(VALOR_UNIT*QUANT) INTO V_TOTAL 
 FROM ITENS_NOTA 
 WHERE NF_NUM = P_NF_NUM; 
 
 RETURN(V_TOTAL); 
END; 
/ 
SHOW ERRORS; 
Testando a function no SQL*PLUS: 
 
SQL> SELECT RET_TOTAL_PROD(5421) FROM DUAL; 
 
 
Sintaxe para apagar uma function: 
 
SQL> DROP FUNCTION NOME_DA_FUNCTION; 
 
 
 
11.8. Triggers 
 
 Um Database Trigger é um bloco PS/SQL associado a uma tabela específica. Ele é 
executado implicitamente quando é disparado um comando que altera o conteúdo de uma 
tabela. 
 Um Trigger é composto por quatro partes: 
 
Trigger type BEFORE, AFTER – statment (comando) ou row (linha) 
Triggering event INSERT, UPDATE ou DELETE 
Trigger restriction WHEN cláusula (opcional) 
Trigger action Bloco PL/SQL 
 
 Cada tabela (table) pode ter um Trigger de cada tipo, totalizando então 12 Triggers 
por tabela: 
 . INSERT – UPDATE - DELETE 
 . BEFORE – AFTER 
 . FOR STATMENT – FOR EACH ROW (mais utilizado - recomendado) 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
26 
Sintaxe: 
 
CREATE [OR REPLACE] TRIGGER [SCHEMA.] NOME_DO_TRIGGER 
[BEFORE/AFTER][INSERT <OR> UPDATE <OF COLUMNS> <OR> DELETE] 
ON NOME_DA_TABELA 
REFERENCING [OLD AS NOME_OLD][NEW AS NOME_NEW] 
FOR [EACH ROW / STATEMENT] 
[WHEN CONDIÇÃO] 
DECLARE 
 VARIAVEL_1 TIPO_DO_DADOS; 
 ...DECLARAÇÃO DE VARIÁVEIS... 
BEGIN 
 ... 
 INSTRUÇÕES PL/SQL 
 ... 
END; 
/ 
SHOW ERRORS; 
 
Opção Descrição 
OR REPLACE Depois de criada uma trigger, se esta opção não for especificada 
numa nova compilação, dará uma mensagem de erro mostrando 
que a esta trigger já existe. O comando REPLACE troca a trigger 
especificada e suas instruções internas pela que está sendo 
compilada. 
SCHEMA Ou usuário dono do objeto trigger, se nada for epecificado será 
criado com o usuário corrente como dono. 
BEFORE/AFTER Especifica se o trigger vai disparado antes ou depois do evento 
INSERT/UPDATE/D
ELETE 
Indica em quais eventos o trigger será disparado. No caso de 
update ainda há a opção de especificar qual a coluna que deverá 
haver mudança para disparar o trigger. 
ON NOME_TABELA Indica sobre qual tabela o trigger estará vinculado 
REFERENCING Geralmente é omitido, utilizado para nomear as referências OLD e 
NEW disponíveis no trigger 
FOR EACH ROW Indica se o trigger será disparado após a alteração de cada linha 
da tabela 
FOR EACH 
STATEMENT 
Indica se o trigger será disparado após o término do comando de 
alteração da tabela (pouco utilizado) 
WHEN CONDIÇÃO Pode-se nesta opção especificar uma condição para o disparo do 
trigger 
SHOW ERRORS Exibe os erros da procedure, se houver. 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
27 
Exemplo 1: 
 
CREATE OR REPLACE TRIGGER TG_FUNC_SAL 
BEFORE INSERT OR UPDATE OF FUN_SAL 
ON FUNCIONARIOS 
FOR EACH ROW 
DECLARE 
 V_MINSAL NUMBER; 
 V_MAXSAL NUMBER; 
BEGIN 
 SELECT MINSAL, MAXSAL INTO V_MINSAL, V_MAXSAL 
 FROM FUNC_SALARIOS 
 WHERE DEPTO = :NEW.DEPTO; 
 
 IF :NEW.FUN_SAL < V_MINSAL OR :NEW.FUN_SAL > V_MAXSAL THEN 
 RAISE_APPLICATION_ERROR(-20500,’Salário fora da 
faixa’); 
 END IF; 
END; 
 
Um Database Trigger e uma Procedure têm muitas coisas em comum. 
 
Similaridades: 
1. Feitos através de comandos SQL e PL/SQL 
2. Usam área SQL compartilhadas 
3. O SGBD verifica automaticamente as dependências 
 
Diferenças: 
1. Um trigger está associado a uma tabela 
2. Um trigger é chamado implicitamente, a procedure não 
3. COMMIT, ROOLBACK e SAVEPOINT não são permitidos em triggers e em 
procedures chamadas por eles. 
4. Não há GRANT (privilégio) para executar um trigger 
 
A execução de um trigger segue a seguinte sequência: 
 
1. INSERT, UPDATE, DELETE são passados para o SGBD 
2. Executa o Trigger Before em nível de comando (statement) 
3. Para cada linha afetada com comando: 
3.1. Executa o trigger Before em nível de linha (row) 
3.2. Altera a linha, verifica a integridade referencial 
3.3. Executa o trigger After em nível de linha (row) 
4. Completa a verificação da integridade referencial 
5. Executa o trigger After em nível de comando (statement) 
6. Retorna para a aplicação 
 
 
 
Expressões 
 
Para referenciar os valores novos e velhos das colunas no trigger, usa-se os prefixos 
OLD e NEW. 
 
Exemplo: IF :NEW.FUN_SAL < :OLD.FUN_SAL THEN ... 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
28 
Nota: 
 
1. NEW e OLD são variáveis válidas somente para triggers 
2. NEW e OLD - ambas disponíveisno comando UPDATE 
3. OLD vale NULL no comando INSERT 
4. NEW vale NULL no comando DELETE 
5. Pode-se mudar NEW para NOVO (ou qualquer outro nome) e OLD para VELHO 
(ou qualquer outro nome) utilizando a opção REFERENCING 
 
Predicados Condicionais 
 
 Para identificar qual o tipo de operação (insert,update,delete) que disparou a trigger, 
utilizamos: 
 
1. IF INSERTING... 
2. IF UPDATING... 
3. IF DELETING… 
 
 
11.9. Atenção 
 
 Um erro muito comum em stored procedures e triggers é a ocorrência, no bloco 
executável, de instruções “select” sem a cláusula “into”. Todo “select” nestes casos tem que 
possuir a cláusula “into”, atribuindo os valores das colunas de uma tabela para variáveis; e 
este “select...into” pode retornar somente 1 linha. Se retornar 0 linha ou mais de 1 linha, 
ocorre um erro. 
 
Exemplo: 
 
CREATE OR REPLACE FUNCTION RET_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE) RETURN VARCHAR2 
IS 
 V_CID_NOME CIDADE.CID_NOME%TYPE; 
BEGIN 
 SELECT CID_NOME INTO V_CID_NOME 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 RETURN(V_CID_NOME); 
END; 
 
 No exemplo exposto, a restrição na chave primária não permite o erro de múltiplas 
linhas retornadas para dentro de uma variável, fica somente sujeito ao erro de nenhum dado 
encontrado se o código passado como parâmetro não existir. 
 É apresentado a seguir recursos para o tratamento destes erros. 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
29 
 
12. Tratamento de Erros (Exceções) 
 
 O tratamento de erros em PL/SQL ocorre em tempo de execução. Os erros em 
tempo de compilação são detectados pelo mecanismo da PL/SQL e informados ao usuário. 
Erros de sintaxes ou referências a objetos inexistentes são exemplos de exceções de 
compilação. 
 
DECLARE 
 A NUMBER:=1; 
 B NUMBER:=0; 
 X NUMBER; 
BEGIN 
 X := A/B; 
 
EXCEPTION 
 WHEN OTHERS THEN -- qualquer erro 
 ... tratamento/instruções 
 
END; 
 
Neste exemplo o erro será: ORA-01476: o divisor é igual a zero 
 
 
12.1 Exceções definidas pelo usuário 
 
 Os desenvolvedores de rotinas PL/SQL têm a possibilidade de criar erros 
personalizados que não se referenciam aos erros próprios do Oracle. 
 Exemplo: 
 
CREATE OR REPLACE PROCEDURE ALTERA_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE, 
 P_CID_NOME CIDADE.CID_NOME%TYPE, 
 P_CID_UF CIDADE.CID_UF%TYPE) 
IS 
 V_CONT NUMBER; 
 V_MEU_ERRO EXCEPTION; 
BEGIN 
 SELECT COUNT(*) INTO V_CONT 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 IF VCONT=0 THEN 
 RAISE V_MEU_ERRO; 
 ELSE 
 UPDATE CIDADE 
 SET CID_NOME = P_CID_NOME, 
 CID_UF = P_CID_UF 
 WHERE CID_COD = P_CID_COD; 
 END IF; 
EXCEPTION 
 WHEN V_MEU_ERRO THEN 
 RAISE_APPLICATION_ERROR(-20001,'CIDADE NÃO ENCONTRADA...'); 
END; 
 
Bloco de 
tratamento de 
erros 
Declara uma variável do tipo exception 
RAISE – desvia a execução para o 
bloco de tratamento de erros, para a 
variável correspondente. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
30 
 
 
 
 
12.2 Exceções predefinidas 
 
 O Oracle possui várias exceções predefinidas que correspondem aos erros mais 
comuns. Na tabela abaixo são apresentados alguns deles. 
 
Erro Oracle Exceções Descrição 
ORA-0001 DUP_VAL_ON_INDEX Violação de restrição exclusiva 
ORA-0051 TIMEOUT_ON_RESOURCE Tempo de espera esgotado 
ORA-1403 NO_DATE_FOUND Não foram encontrados dados 
ORA-1001 INVALID_CURSOR Operação de cursor ilegal 
ORA-1722 INVALID_NUMBER Número inválido 
ORA-1017 LOGIN_DENIED Nome do usuário/senha inválido 
ORA-6501 PROGRAM_ERROR Erro interno do PL/SQL 
ORA-1476 ZERO_DIVIDE Divisão por zero 
ORA-6511 CURSOR_ALREADY_OPEN Tentativa de abertura de um cursor que já está aberto 
ORA-1422 TOO_MANY_ROWS Uma instrução SELECT..INTO 
corresponde mais que uma linha 
 
RAISE_APPLICATION_ERROR (número_erro,mensagem) 
 
 Função pré-definida para o programador criar suas próprias mensagens de 
erros. Esta função aborta a execução do bloco/rotina correspondente. 
 
Número_erro: valor entre –20000 e –20999 
Mensagem: texto de até 512 caracteres. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
31 
 
Exemplo: 
 
CREATE OR REPLACE FUNCTION RET_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE) RETURN VARCHAR2 
IS 
 V_CID_NOME CIDADE.CID_NOME%TYPE; 
BEGIN 
 BEGIN 
 SELECT CID_NOME INTO V_CID_NOME 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 V_NOME:= '**NÃO ENCONTRADO**'; 
 WHEN TOO_MANY_ROWS THEN 
 V_NOME:= '**MUITAS LINHAS ENCONTRADAS**'; 
 WHEN OTHERS THEN 
 V_NOME:= '**ALGUM ERRO OCORREU**'; 
 END; 
 RETURN(V_CID_NOME); 
END; 
 
 
 
12.3 When Others 
 
 Esta cláusula é executada em todas as exceções levantadas nas rotinas PL/SQL e 
deve ser sempre a última cláusula (handler) do bloco. 
 Exemplo: 
 
CREATE OR REPLACE FUNCTION RET_SALARIO 
 (P_FUN_CODIGO FUNCIONARIO.FUN_CODIGO%TYPE) RETURN NUMBER 
IS 
 V_FUN_SAL FUNCIONARIO.FUN_SAL%TYPE; 
BEGIN 
 
 SELECT FUN_SAL INTO V_FUN_SAL 
 FROM FUNCIONARIO 
 WHERE FUN_CODIGO = P_FUN_CODIGO; 
 
 RETURN(V_FUN_SAL); 
 
EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 RAISE_APPLICATION_ERROR(-20001,’CODIGO DE FUNCIONARIO NÃO 
 ENCONTRADO’); 
 WHEN OTHERS THEN 
 RAISE_APPLICATION_ERROR(-20002,’ALGUM OUTRO ERRO OCORREU...’); 
END; 
 
 Pode-se construir blocos dentro de outros blocos e assim por diante, para que se 
tenha o funcionamento esperado. 
 
 
 
Se neste “select” não encontrar 
nenhuma linha, então dá um erro 
de NO_DATA_FOUND, se 
houver qualquer outro erro, é 
capturado pelo WHEN OTHERS. 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
32 
Exemplo: 
 
 BEGIN 
 BEGIN 
 EXCEPTION 
 WHEN . . . THEN 
 INSERT...; 
 IF . . . THEN. . . END IF; 
 WHEN . . . THEN 
 BEGIN 
 EXCEPTION 
 END; 
 END; 
 EXCEPTION 
 END; 
 
 
12.4 SqlCode e SqlErrm 
 
 São funções predifinidas que retornam o código do erro atual e o texto da mensagem 
do erro atual, consecutivamente. 
 São muito utilizados no tratamento de exceções “WHEN OTHERS” para informar o 
erro ocorrido. 
 Exemplo: 
 
CREATE OR REPLACE PROCEDURE INCLUI_CLIENTE 
(P_CLI_COD CLIENTE.CLI_COD%TYPE, 
 P_CLI_NOME CLIENTE.CLI_NOME%TYPE, 
 P_MSG OUT VARCHAR2) 
IS 
BEGIN 
 BEGIN 
 P_MSG := ‘OK’; 
 INSERT INTO CLIENTE (CLI_COD, CLI_NOME) 
 VALUES(P_CLI_COD, P_CLI_NOME); 
 EXCEPTION 
 WHEN OTHERS THEN 
 P_MSG := ‘ERRO..CODIGO: ’||TO_CHAR(SQLCODE)||’ MENSAGEM:’ 
 ||SQLERRM; 
 END; 
END; 
 
 
12.5 Exception_Init 
 
 Existe a possibilidade de associar um erro do Oracle a uma exceção nomeada 
(nome), ou seja, podemos associar um código de erro do Oracle a um nome. 
 
Sintaxe: 
 
 PRAGMA_EXCEPTION_INIT(nome_do_erro,número_do_erro_oracle) 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
33 
Exemplo: 
 
CREATE OR REPLACE PROCEDURE EXCLUI_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE, 
 P_ERRO OUT VARCHAR2, 
 P_MSG OUT VARCHAR2) 
IS 
 
 V_CONT NUMBER; 
 V_ERRO1 EXCEPTION; 
 V_ERRO2 EXCEPTION; 
 
 -- EXCEPTION_INIT pragma 
 
 PRAGMA EXCEPTION_INIT(V_ERRO2,-2292); 
-- integrity constraint violated - child record found 
 
BEGIN 
 
 BEGIN 
 
 P_ERRO := 'N'; 
 P_MSG := 'OK'; 
 
 SELECT COUNT(*) INTO V_CONT 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 
 IF V_CONT = 0 THEN 
 RAISE V_ERRO1; 
 END IF; 
 
 DELETE 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 
 COMMIT; 
 
 EXCEPTION 
 WHEN V_ERRO1 THEN 
 P_ERRO := 'S'; 
 P_MSG := 'CÓDIGO NÃO ENCONTRADO....'; 
 WHEN V_ERRO2THEN 
 P_ERRO := 'S'; 
 P_MSG := 'NÃO FOI POSSÍVEL APAGAR. HÁ REGISTRO 
RELACIONADOS COM ESTE CODIGO...'; 
 END; 
 
END; 
 
 
 
Se durante o delete ocorrer o erro 
-2292, a exceção é capturada em 
V_ERRO2 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
34 
13. Cursor 
 
Cursor é um artifício utilizado em PL/SQL para ter acesso a um conjunto de linhas 
resultantes de uma operação de banco de dados. 
O Oracle sempre executa uma declaração SQL associando um cursor individual, ou 
nome, para ele; o cursor controla todas as fases do processo. 
 Realiza intimamente todas as questões do banco de dados sem considerar o 
processamento da declaração SQL, o Oracle adquire antes a necessidade do cursor 
implícito e manuseia-os automaticamente. 
 No entanto, para processar consultas explicitamente em ordem, declara cursores 
explicitamente e controla-os manualmente no bloco PL/SQL. 
 A seleção de linhas que satisfaz o critério de procura de uma consulta consiste de 
zero, uma ou mais linhas. 
 Quando uma consulta retornar mais de uma linha, declare e utilize um cursor 
explícito. 
 
13.1 Cursor Explícito 
 
 Cursores explícitos podem processar mais de uma linha retornada pela consulta, 
linha a linha. São criados e utilizados pelos programadores, desenvolvedores e DBA’s, 
geralmente em stored procedures e triggers, para realizar operações que necessitem de 
uma estrutura que assuma o resultado de uma instrução “select”. 
 
 Para utilizar um cursor explícito deve-se: 
 
a. declarar o cursor (declare) 
 
DECLARE 
 CURSOR NOME_DO_CURSOR IS 
 DECLARAÇÃO_SELECT; 
 
Onde: NOME_DO_CURSOR é uma declaração PS/SQL 
DECLARAÇÃO_SELECT é uma declaração “SELECT” sem a cláusula 
“INTO” 
 
Nota: Não inclua a cláusula “INTO” dentro da declaração do cursor, pois ele 
aparece depois dentro da declaração “FETCH”, implicitamente. 
Se a declaração “SELECT” utiliza qualquer variável, é correto declará-
la(s) antes da declaração do cursor. 
 
b. abrir o cursor (open) 
 
OPEN NOME_DO_CURSOR; 
 
Onde: NOME_DO_CURSOR é o nome de um cursor declarado previamente. 
 
c. buscar dados do cursor (fetch) 
 
FETCH NOME_DO_CURSOR INTO VARIAVEL1, VARIAVEL2,... 
 
Onde: NOME_DO_CURSOR é o nome do cursor declarado previamente. 
 VARIAVEL é uma variável de saída para armazenar resultados. 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
35 
Nota: Inclua o mesmo número de variáveis dentro da cláusula “INTO” da 
declaração “FETCH” como colunas de saída na declaração “SELECT”, 
assim como a compatibilidade dos seus tipos. 
 As variáveis correspondem ao posicionamento das colunas. 
 
d. fechar o cursor (close) 
 
CLOSE NOME_DO_CURSOR; 
 
 Onde: NOME_DO_CURSOR é o cursor previamente declarado. 
 
Nota: Não tente buscar dados de um cursor uma vez que ele esteja fechado. 
 
 Para buscar linha a linha de um cursor, é necessário determinar quando entrar e sair 
de um “LOOP” pela inspeção de um atributo do cursor. 
 
 
Atributos de Cursor Descrição 
%ISOPEN atributo booleano que avalia se o cursor está aberto 
%NOTFOUND Atributo booleano que avalia se o mais recente “fetch” não 
retornou nenhuma linha 
%FOUND Atributo booleano que avalia se o mais recente “fetch” retornou 
alguma linha 
%ROWCOUNT Atributo numérico que retorna o número total de linhas que foram 
processadas pelo “fetch” e não o total que será retornado pelo 
cursor explícito. 
Nota: 
• não use atributos de cursor diretamente dentro de uma declaração SQL 
• busque linhas somente quando o cursor estiver aberto. 
 
Exemplo 1: 
 
DECLARE 
 CURSOR C_ALUNOS IS 
 SELECT ALU_COD, DT_TRANCOU 
 FROM ALUNOS 
 WHERE DT_TRANCOU < TO_DATE('01/01/2000', 'DD/MM/YYYY'); 
 
V_ALU_COD ALUNOS.ALU_COD%TYPE; 
V_DT_TRANCOU ALUNOS.DT_TRANCOU%TYPE; 
BEGIN 
OPEN C_ALUNOS; 
 
LOOP 
 FETCH C_ALUNOS INTO V_ALU_COD, V_DT_TRANCOU; 
 
 EXIT WHEN C_ALUNOS%NOTFOUND OR C_ALUNOS%ROWCOUNT=5; 
 
 INSERT INTO TRANCADOS (ALU_COD, DT_TRANCOU) 
 VALUES (V_ALU_COD, V_DT_TRANCOU); 
END LOOP; 
 
CLOSE C_ALUNOS; 
COMMIT; 
END; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
36 
 
 
Existe um caminho mais curto para buscar linhas de um cursor utilizando a estrutura “FOR 
LOOP”: 
 
FOR NOME_DO_REGISTRO IN NOME_DO_CURSOR LOOP 
 DECLARAÇÃO 1; 
DECLARAÇÃO 2; 
... 
END LOOP; 
 
Onde: NOME_DO_REGISTRO é o nome de um registro declarado implicitamente. 
 NOME_CO_CURSOR é o nome do cursor previamente declarado. 
 
Exemplo 2: 
 
DECLARE 
 V_NUM_PEDIDO PEDIDO_ITEM.NUM_PEDIDO%TYPE; 
 
 CURSOR C_ITEM IS 
 SELECT PROD_COD, SUM(QUANT*VALOR) AS TOTAL 
 FROM PEDIDO_ITEM 
 WHERE NUM_PEDIDO = V_NUM_PEDIDO 
 GROUP BY PROD_COD; 
BEGIN 
 V_NUM_PEDIDO := 10; 
 FOR VREG IN C_ITEM LOOP -- open, fetch e declaração de VREG 
 -- implícito 
 
 INSERT INTO ACUMULADO (PROD_COD, TOTAL) 
 VALUES (VREG.PROD_COD, VREG.TOTAL); 
 
 END LOOP; /*close implícito*/ 
 
 COMMIT; 
END; 
 
 
Neste exemplo poderia ter sido declarada a variável VREG como 
VREG.C_ITEM%ROWTYPE, passando assim a assumir a estrutura de registro (linha) do 
cursor. 
 
Outro recurso interessante para ser usado em um cursores é a cláusula 
“FOR UPDATE” e “CURRENT OF”. 
 
Exemplo 3: 
 
CREATE OR REPLACE PROCEDURE ATRIBUI_DESCONTOS 
(P_CUR_COD CURSOS.CUR_COD%TYPE) 
IS 
 CURSOR C_ALUNOS(P_CUR_COD) IS 
 SELECT ALU_CPF, ALU_DESCTO 
 FROM ALUNOS WHERE CUR_COD = P_CUR_COD 
FOR UPDATE OF ALU_DESCTO; 
 V_CONT NUMBER; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
37 
BEGIN 
 FOR VREG IN C_ALUNOS LOOP 
 IF V_REG.ALU_DESCTO = 0 THEN 
 SELECT COUNT(*) INTO V_CONT 
 FROM FUNCIONARIOS 
 WHERE FUN_CPF = V_REG.ALU_CPF; 
 IF V_CONT <> 0 THEN 
 UPDATE ALUNOS 
 SET ALU_DESCTO = 50 
 WHERE CURRENT OF C_ALUNOS; 
 END IF; 
 END IF; 
 END LOOP; 
 COMMIT; 
END; 
Nota: é importante destacar que a cláusula “FOR UPDATE” faz com que os registros 
(linhas) da tabela que o cursor envolve sejam “locados/bloqueados” quando o cursor é 
aberto; e permanecem assim até que o mesmo seja fechado e após um “commit” ou 
“rollback” se foram feitas modificações nestas linhas da tabela. 
 
Exemplo 4: 
 
No SQL/PLUS: 
 
SET SERVEROUTPUT ON; 
 
DECLARE 
 CURSOR C_CLIENTE IS 
 SELECT C.CLI_NOME, D.CID_NOME 
 FROM CLIENTE C, CIDADE D 
 WHERE C.CID_COD = D.CID_COD; 
 
 VNOME CLIENTE.CLI_NOME%TYPE; 
 VCIDADE CIDADE.CID_NOME%TYPE; 
 
BEGIN 
 OPEN C_CLIENTE; 
 LOOP 
 FETCH C_CLIENTE INTO VNOME, VCIDADE; 
 EXIT WHEN C_CLIENTE%NOTFOUND; 
 DBMS_OUTPUT.PUT_LINE(VNOME||' - '||VCIDADE); 
 END LOOP; 
 CLOSE C_CLIENTE; 
END; 
 
Exemplo 5 (outra forma de loop no cursor): 
 
CREATE OR REPLACE PROCEDURE DESATIVA_CLIENTES 
IS 
BEGIN 
 FOR VCLI IN (SELECT CLI_COD 
 FROM CLIENTE WHERE CLI_ATIVO='N') LOOP 
 INSERT INTO CLIENTES_INATIVOS (CLI_COD, DATA) 
 VALUES (VCLI.CLI_COD, SYSDATE); 
 DELETE FROM CLIENTE WHERE CLI_COD = VCLI.CLI_COD; 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
38 
 END LOOP; 
 COMMIT; 
END; 
 
13.2 Cursor Implícito 
 
 O cursor implícito é chamado cursor SQL, porque ele armazena as informações 
referentes ao processamento da última declaração SQL não associada a um cursor 
explícito. Todas as instruções SQL são executadas dentro de uma área de contexto e, 
portanto, têm um cursor (implícito) que aponta para essa área. Diferentemente dos cursores 
explícitos, o cursor SQL não é aberto ou fechado pelo programa. A PL/SQL implicitamente 
abre o cursor SQL, processa a instrução SQL nele e em seguida fecha o cursor. 
 Este cursor é utilizado para processar as instruções INSERT, UPDATE, DELETE E 
SELECT...INTO. Pelo fato de o cursor SQL ser aberto e fechado pelo mecanismo PL/SQL, 
os comandos OPEN, FETCH E CLOSE não são relevantes.Entretanto, os atributos de 
cursor podem ser aplicados no cursor SQL. 
 Embora SQL%NOTFOUND possa ser utilizada com instruções SELECT...INTO, 
não é realmente útil fazer isso, pois uma instrução SELECT...INTO levantará o erro do 
Oracle (ORA-1403: no data found) quando ela não corresponder a nenhuma linha. 
Esse erro faz com que o controle passe imediatamente para a seção de tratamento de 
exceção do bloco, evitando a verificação em SQL%NOTFOUND. 
Veremos nos exemplos a seguir as situações apresentadas. 
 
Atributos de Cursor Descrição 
SQL%ISOPEN atributo booleano – sempre avalia para “FALSE” 
SQL%NOTFOUND Atributo booleano que avalia se a mais recente declaração SQL 
não afetou nenhuma linha 
SQL%FOUND Atributo booleano que avalia se o mais recente declaração SQL 
afetou uma ou mais linhas 
SQL%ROWCOUNT Atributo numérico que retorna o número total de linhas que foram 
processadas pela declaração SQL mais recente. 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
39 
Exemplo 1: 
 
CREATE OR REPLACE PROCEDURE ALTERA_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE, 
 P_CID_NOME CIDADE.CID_NOME%TYPE, 
 P_CID_UF CIDADE.CID_UF%TYPE, 
 P_ERRO OUT VARCHAR2, 
 P_MSG OUT VARCHAR2) 
 
IS 
 V_CID_COD CIDADE.CID_COD%TYPE; 
BEGIN 
 
 P_ERRO := 'N'; 
 P_MSG := 'OK'; 
 
 UPDATE CIDADE 
 SET CID_NOME = P_CID_NOME, 
 CID_UF = P_CID_UF 
 WHERE CID_COD = P_CID_COD; 
 
 IF SQL%NOTFOUND THEN -- CURSOR IMPLICITO 
 P_ERRO := 'S'; 
 P_MSG := 'CÓDIGO NÃO ENCONTRADO...'; 
 ELSE 
 P_MSG := 'CIDADE ALTERADA COM SUCESSO...'; 
 COMMIT; 
 END IF; 
 
END; 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
40 
 
Exemplo 2: 
 
CREATE OR REPLACE PROCEDURE CONSULTA_CIDADE 
 (P_CID_COD CIDADE.CID_COD%TYPE, 
 P_CID_NOME OUT CIDADE.CID_NOME%TYPE, 
 P_CID_UF OUT CIDADE.CID_UF%TYPE) 
IS 
BEGIN 
 
 BEGIN 
 SELECT CID_NOME, CID_UF INTO P_CID_NOME, P_CID_UF 
 FROM CIDADE 
 WHERE CID_COD = P_CID_COD; 
 
 -- se o código da cidade não existir, não chega na verificação 
 -- abaixo, dá erro no select e vai imediatamente para a seção 
 -- de tratamento de erros 
 
 IF SQL%NOTFOUND THEN 
 P_CID_NOME := 'CIDADE NÃO ENCONTRADA...'; 
 P_CID_UF := 'XX'; 
 END IF; 
 
 EXCEPTION -- bloco de tratamento de erros da PL/SQL - Oracle 
 WHEN NO_DATA_FOUND THEN 
 P_CID_NOME := 'CIDADE NÃO ENCONTRADA...'; 
 P_CID_UF := 'XX'; 
 END; 
 
END; 
 
 
13.3 Ref Cursor 
 
 Ref cursor ou cursor de referência é um tipo de variável de cursor que assume a 
estrutura de um “SELECT” como se fosse uma tabela, com múltiplas colunas e múltipas 
linhas; sua principal vantagem é poder ser um parâmetro de saída de uma procedure ou 
uma function. Este é um recurso muito interessante para ser utilizado em consultas (select). 
Complementando, uma variável de cursor é um tipo de referência. Ela pode referir-se a 
posições de memória diferentes à medida que o programa é executado. 
 Os exemplos até aqui abordaram cursores estáticos que sempre estão associados a 
somente uma instrução “select”. Variáveis de cursor (ref cursor) podem conter diferentes 
valores em tempo de execução. 
 
 TYPE nome_do_tipo IS REF CURSOR [RETURN tipo_de_retorno] 
 
Onde: 
nome_do_tipo é o nome do novo tipo de referência 
tipo_de_retorno é um tipo de registro que indica os tipos da lista “select” que 
por fim retornarão pela variável de cursor. 
 
 
 
 
 
Este bloco 
fica sem 
utilidade 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
41 
Exemplo 1 – declarando como um tipo específico (estático): 
 
DECLARE 
 -- utilizando %rowtype 
 TYPE TP_CIDADE IS REF CURSOR 
 RETURN CIDADE%ROWTYPE; 
 
 -- definindo um novo tipo de registro 
 TYPE TP_REGISTRO IS RECORD 
 (cli_nome cliente.cli_nome%type, 
 cid_nome cidade.Cid_nome%type); 
 
V_VAR1 TP_REGISTRO; 
 
-- criando uma variável de cursor que utiliza o tipo registro 
-- que foi criado 
TYPE TP_MEU_REFCURSOR IS REF CURSOR RETURN TP_REGISTRO; 
 
 V_MEU_CURSOR TP_MEU_REFCURSOR; 
 
 
Exemplo 2 – Fazendo um LOOP em um Ref Cursor: 
 
CREATE OR REPLACE PROCEDURE TESTE_CLIENTES 
IS 
 TYPE T_MEU_TIPO IS REF CURSOR; 
 C_CLIENTE T_MEU_TIPO; 
 VCLI_COD NUMBER(6); 
BEGIN 
 OPEN C_CLIENTE FOR SELECT CLI_COD FROM CLIENTE; 
 LOOP 
 FETCH C_CLIENTE INTO VCLI_COD; 
 EXIT WHEN C_CLIENTE%NOTFOUND; 
 INSERT INTO CLIENTE_TESTE (CLI_COD) VALUES (VCLI_COD); 
 END LOOP; 
 CLOSE C_CLIENTE; 
 COMMIT; 
END; 
 
 Para utilizarmos o Ref Cursor para declarar uma variável a ser utilizada como 
parâmetro de saída em uma procedure ou function, a principal funcionalidade no escopo em 
questão, tem-se que criar uma package criando e definindo um nome para um tipo “ref 
cursor”: 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
42 
Exemplo 3: 
 
Passo 1 – É necessária a criação do tipo “ref cursor”. Como trata-se de um cursor variável 
que pode representar diferentes estruturas e resultados de instruções “select”, basta criar 
um único tipo para ser utilizado em todo o banco de dados. Não há a necessidade de criar 
um tipo para cada variável ou parâmetro de stored procedure que tiver que ser declarada. 
 
CREATE OR REPLACE PACKAGE PK_REF_CURSOR AS 
 TYPE TP_REF_CURSOR IS REF CURSOR; 
END PK_REF_CURSOR; 
/ 
 
Passo 2 – Cria-se uma procedure ou function que tenha como parâmetro de saída uma 
variável do tipo ref cursor definido na package: 
 
CREATE OR REPLACE PROCEDURE RETORNA_PRODUTOS 
 (P_PRO_DESC IN PRODUTO.PRO_DESC%TYPE, 
 P_SAIDA OUT PK_REF_CURSOR.TP_REF_CURSOR) AS 
BEGIN 
 OPEN P_SAIDA FOR 
 SELECT PRO_COD, PRO_DESC, PRO_PRVENDA 
 FROM PRODUTO 
 WHERE PRO_DESC LIKE '%'||P_PRO_DESC||'%' 
 ORDER BY PRO_DESC; 
END; 
Para testar no Sql/Plus: 
-- cria uma variável PL/SQL dentro do SQL/PLUS para receber o -
-- resultado do parâmetro de saída 
VARIABLE RETORNO REFCURSOR; 
EXECUTE RETORNA_PRODUTOS(‘A’,:RETORNO); 
PRINT RETORNO; 
 
 
 
 
Universidade do Oeste Paulista 
Faculdade de Informática de Presidente Prudente 
 
43 
Referências Bibliográficas 
 
URMAN, Scott; tradução: Edson Furmankiewicz. Oracle 9i Programação PL/Sql. Rio 
de Janeiro: Elsevier, 2002.