PL/SQL

O que é PL/SQL

Definição: PL/SQL (Procedural Language/SQL)

Linguagem procedural da Oracle, construída sobre o SQL. Diferente do SQL — que é declarativo (você descreve o que quer, o banco decide como buscar) — o PL/SQL permite escrever lógica de programação de verdade dentro do banco: variáveis, estruturas condicionais e de repetição, tratamento de erros, funções e procedimentos. Os programas são organizados em blocos (ver adiante), que podem ser executados diretamente ou salvos no banco como procedures, functions, packages ou triggers para serem reaproveitados depois.

Definição: Por que aprender PL/SQL

Mesmo usando só o banco de dados (sem uma ferramenta específica de desenvolvimento), é comum que processos internos do servidor Oracle sejam escritos em PL/SQL — conhecer a linguagem ajuda a entender e manter esses processos. Além disso, como os programas em PL/SQL ficam armazenados no próprio banco, uma vez escritos e compilados, eles não precisam ser reenviados a cada execução — só chamados pelo nome. Isso reduz o tráfego entre aplicação e banco (ver comparação a seguir) e melhora desempenho.

SQL, SQL*Plus e PL/SQL: qual é a diferença

É comum confundir esses três termos, já que aparecem sempre juntos.

Termo	O que é
SQL	A linguagem de consulta em si (SELECT, INSERT, UPDATE, DELETE). Declarativa, sempre executada no servidor do banco. Não é propriedade da Oracle — é um padrão usado por qualquer banco relacional (ver SQL).
PL/SQL	A linguagem procedural de programação da Oracle, construída sobre o SQL. Os programas são escritos em blocos, que podem ser executados tanto no cliente (Oracle Forms, Reports) quanto diretamente no servidor do banco.
SQL*Plus	A ferramenta de linha de comando que serve de interface entre quem programa e o banco. Ela recebe o comando SQL ou bloco PL/SQL digitado, envia para o motor correto (executor de SQL ou compilador/executor de PL/SQL) e exibe o resultado na tela.

flowchart LR
    subgraph Desktop
        SP["SQL*Plus"]
    end
    subgraph Servidor["Servidor Banco de Dados Oracle"]
        PLSQL["Compilador e Executor PL/SQL"]
        SQLEXEC["Executor de Declarações SQL"]
        DB[("Banco de Dados Oracle")]
    end
    SP -->|"comandos SQL<br/>ou blocos PL/SQL"| PLSQL
    PLSQL -->|"consultas SQL"| SQLEXEC
    SQLEXEC --> DB

Definição: Por que blocos PL/SQL reduzem tráfego de rede

Quando uma aplicação envia comandos SQL soltos, cada um viaja separadamente até o servidor — ela envia, espera a resposta, envia o próximo. Quando ela envia um bloco PL/SQL, todos os comandos SQL e a lógica de controle dentro dele são enviados de uma vez só ao servidor, executados lá dentro, e só o resultado final volta pela rede. Menos idas e vindas entre aplicação e banco tende a significar melhor desempenho, principalmente quando a comunicação depende de uma rede cliente/servidor mais lenta.

Programação em bloco

Definição: Bloco PL/SQL

A unidade fundamental de um programa PL/SQL. Um bloco é delimitado pelas palavras begin e end, e pode conter outros blocos dentro dele (chamados sub-blocos). Um bloco completo é formado por três áreas:

• declare (opcional) — onde variáveis, constantes, cursores e exceções definidas pelo usuário são declaradas.
• begin...end (obrigatória) — onde ficam os comandos que o programa de fato executa.
• exception (opcional, mas fortemente recomendada) — onde erros que aconteçam durante a execução são tratados.

declare
    -- área de declaração de variáveis, cursores, exceções
begin
    -- área de execução dos comandos
exception
    when others then
        -- área de tratamento de erros
end;
/

O comando / (barra) é o que instrui o SQL*Plus a executar o bloco montado.

Definição: Bloco anônimo

Um bloco PL/SQL escrito sem cabeçalho (sem nome) é chamado de bloco anônimo — ele não é gravado no banco, e se a ferramenta usada para escrevê-lo for fechada sem salvar o código em algum arquivo, ele se perde. Blocos nomeados, que ficam armazenados no banco para reuso, são vistos a partir do Capítulo 16 (Programas armazenados).

Um exemplo simples, somando dois números e tratando um possível erro:

declare
    soma number;
begin
    soma := 45 + 55;
    dbms_output.put_line('Soma: ' || soma);
exception
    when others then
        raise_application_error(-20001, 'Erro ao somar valores!');
end;
/

dbms_output.put_line (detalhado na próxima seção) escreve uma mensagem na tela; raise_application_error dispara um erro customizado, usado aqui para avisar caso algo dê errado na soma.

Definição: Erro de sintaxe x erro de execução

O SQL*Plus valida a sintaxe do bloco antes mesmo de tentar executá-lo — por exemplo, esquecer o := numa atribuição gera um erro imediato, apontando linha e coluna, antes de qualquer linha do bloco rodar. Já um erro de execução (ex.: tentar somar um número com uma string) só aparece quando o bloco de fato roda — nesse caso, o controle é transferido para a área exception, e é lá que o problema deve ser tratado.

Uma metodologia para começar a escrever um bloco PL/SQL

Encarar uma folha em branco é a maior dificuldade ao aprender qualquer linguagem nova. Um roteiro prático ajuda a estruturar o raciocínio ao montar um programa PL/SQL a partir de um enunciado (ex.: "escreva um programa que imprima o nome dos funcionários de um determinado gerente e de uma determinada localização"):

1. Identifique as fontes de dados — quais tabelas o programa vai precisar. Em caso de dúvida, monte e teste os SELECTs necessários fora do bloco primeiro, como consultas SQL soltas, até ter certeza de que trazem os dados certos.
2. Identifique o tipo de objeto pedido — o enunciado menciona um bloco anônimo? Uma procedure? Uma function? Uma package? Um trigger? Isso define o cabeçalho do programa (ou a ausência dele, no caso de um bloco anônimo).
3. Monte o esqueleto mínimo do bloco — declare / begin-end / exception, mesmo vazio, antes de preencher a lógica.
4. Preencha a área de declaração — variáveis, cursores e tipos que o programa vai usar, com base nos SELECTs já validados no passo 1.
5. Preencha o corpo do bloco — os comandos, laços e condições que implementam a lógica pedida, usando o que foi declarado.
6. Trate os erros — pelo menos um when others deve sempre existir, para que o programa nunca "quebre" silenciosamente. Usar sqlerrm (mensagem do erro) e sqlcode (código do erro) na área de tratamento ajuda a diagnosticar o problema.

Definição: sqlerrm e sqlcode

Duas funções disponíveis dentro da área exception de um bloco PL/SQL: sqlerrm retorna a mensagem de erro gerada pelo banco; sqlcode retorna o código numérico do erro. Usadas juntas (ex.: dbms_output.put_line('Erro: ' || sqlcode || ' - ' || sqlerrm)) ajudam a diagnosticar exatamente o que deu errado, em vez de só saber que algo falhou.

Aplicando o roteiro ao enunciado de exemplo — imprimir os funcionários de um gerente e localização específicos, usando um cursor (estrutura para percorrer o resultado de um SELECT linha a linha, detalhada mais adiante) para percorrer o resultado:

declare
    cursor c1(p_gerente number, p_localizacao varchar2) is
        select f.nome, f.cargo
        from funcionario f, departamento d
        where f.id_departamento = d.id
        and d.localizacao = p_localizacao
        and f.id_gerente = p_gerente;
    r1 c1%rowtype;
begin
    open c1(p_gerente => 7698, p_localizacao => 'CHICAGO');
    loop
        fetch c1 into r1;
        exit when c1%notfound;
        dbms_output.put_line('Nome: ' || r1.nome || ' Cargo: ' || r1.cargo);
    end loop;
    close c1;
exception
    when others then
        dbms_output.put_line('Erro: ' || sqlerrm);
end;
/

Definição: Ative a saída do dbms_output no SQL*Plus

Para que as mensagens enviadas por dbms_output.put_line (e os demais métodos do pacote, ver a seguir) apareçam de fato na tela, é preciso executar antes, na sessão do SQL*Plus: set serveroutput on.

O pacote dbms_output

Definição: dbms_output

Pacote nativo do Oracle com funções e procedimentos para gerar mensagens a partir de blocos anônimos, procedures, packages ou triggers. As mensagens não são escritas na tela diretamente — elas ficam guardadas numa área de buffer em memória durante a execução da sessão, e só são exibidas quando a ferramenta (SQL*Plus) as lê do buffer, ao final do programa (com set serveroutput on habilitado).

Procedure	O que faz
enable(tamanho)	Habilita a chamada das demais rotinas do pacote, definindo o tamanho do buffer em bytes (entre 2.000 e 1.000.000).
disable	Desabilita as demais rotinas e limpa o buffer — útil para suprimir mensagens de depuração já usadas.
put(texto)	Inclui uma informação no buffer, sem adicionar quebra de linha ao final.
put_line(texto)	Inclui uma informação no buffer com quebra de linha ao final — o uso mais comum do pacote.
new_line	Adiciona uma quebra de linha ao buffer (usado junto de put, já que put sozinho não quebra linha).
get_line(linha, status)	Lê uma única linha do buffer. status indica se havia algo a ler: 0 = linha recuperada; 1 = buffer vazio, nada retornado.
get_lines(tabela, quantidade)	Lê várias linhas do buffer de uma vez, devolvendo-as numa variável do tipo array (dbms_output.chararr).

begin
    dbms_output.put('T');
    dbms_output.put('E');
    dbms_output.put('S');
    dbms_output.put('T');
    dbms_output.new_line;
end;
/
-- imprime: TESTE (numa única linha, graças ao new_line manual)

begin
    dbms_output.put_line('Primeira linha');
    dbms_output.put_line('Segunda linha');
end;
/
-- imprime cada put_line já na sua própria linha

Definição: As duas exceções do dbms_output

• ORU-10027 (overflow de buffer) — o buffer ficou pequeno demais para as mensagens enviadas. Solução: aumentar o tamanho definido em enable, ou gravar menos dados.
• ORU-10028 (overflow de comprimento de linha) — uma chamada a put ou put_line ultrapassou o limite de 255 caracteres por linha. Solução: quebrar o texto em chamadas menores.

Variáveis bind e de substituição

O SQL*Plus permite usar dois tipos de variável de usuário — úteis tanto em comandos SQL soltos quanto dentro de blocos PL/SQL — que se comportam de forma bem diferente.

Definição: Variável bind

Declarada com variable nome tipo diretamente no prompt do SQL*Plus (sem precisar de uma área declare). Referenciada com dois-pontos antes do nome (:nome) tanto em comandos SQL quanto em blocos PL/SQL. Seu valor é atribuído com exec :nome := valor e visualizado com print nome. Uma variável bind vive enquanto a sessão do SQL*Plus estiver ativa, e não é visível para outras sessões.

variable mensagem varchar2(200)

begin
    :mensagem := 'Curso PLSQL';
end;
/

select :mensagem from dual;
-- retorna: Curso PLSQL (o valor persiste entre execuções, na mesma sessão)

Definição: Variável de substituição

Definida com define nome = valor, sem precisar declarar um tipo — é sempre tratada como texto, e o SQL*Plus faz a substituição do seu conteúdo antes de interpretar o comando (por isso pode inclusive substituir palavras reservadas inteiras, como uma cláusula where completa, não só um valor). Referenciada com &nome (uma única vez, some depois de usada) ou &&nome (permanece definida para reutilização). Se usada sem ter sido definida antes, o SQL*Plus pergunta o valor interativamente no momento da execução. undefine nome remove a definição.

select ename from emp where empno = &wempno;
-- se &wempno não foi definida, o SQL*Plus pergunta: "Informe o valor para wempno:"

define gfrom = 'from emp'
define gwhere = 'where empno = 7369'
select ename &gfrom &gwhere;
-- a variável de substituição pode conter até uma cláusula SQL inteira

	Variável bind	Variável de substituição
Declaração	variable nome tipo	Não precisa — define já atribui um valor
Tipo	Tipado (number, varchar2, ...)	Sempre tratada como texto
Referência	:nome	&nome (uma vez) ou &&nome (persiste)
Quando é resolvida	Em tempo de execução, pelo motor SQL/PL-SQL	Antes da execução — é uma substituição textual
Uso na cláusula from	Não permitido	Permitido (pode substituir qualquer parte do comando)

Definição: accept — variável de substituição com prompt customizado

Alternativa ao define para pedir um valor ao usuário de forma mais controlada, muito usada em arquivos de script: accept nome number for 999 default 20 prompt "Informe o valor: " define o tipo (number), uma máscara de formato (for 999, até 3 dígitos), um valor padrão (default 20) e a mensagem exibida na hora de pedir o valor (prompt).

Scripts salvos em arquivo (save arquivo.sql, executado depois com @arquivo.sql) também podem receber valores como parâmetros posicionais, referenciados dentro do arquivo como &1, &2 etc. — o valor é passado na própria chamada:

-- dentro de S_EMP.sql:
select ename from emp where deptno = &1;

@S_EMP.sql 10   -- primeira execução: deptno = 10
@S_EMP.sql 20   -- segunda execução: deptno = 20

Aspectos iniciais da programação PL/SQL

Identificadores e escopo

Definição: Regras para identificadores em PL/SQL

Nomes de variáveis, constantes ou qualquer objeto em PL/SQL seguem regras fixas: no máximo 30 caracteres; não podem ser uma palavra reservada da linguagem (begin, if, loop, end, ...); o primeiro caractere deve obrigatoriamente ser uma letra (os demais podem incluir números e alguns caracteres especiais).

Definição: Escopo de um identificador

O escopo de uma variável, constante ou cursor é limitado ao bloco onde foi declarado — incluindo os sub-blocos dentro dele. Um identificador declarado num bloco mais interno não pode ser acessado por um bloco mais externo. Se o mesmo nome for declarado tanto no bloco externo quanto num bloco interno, dentro do bloco interno prevalece a declaração local — a declaração externa fica inacessível enquanto durar esse conflito. Por boas práticas, evite reutilizar o mesmo nome de identificador em blocos aninhados.

create or replace procedure folha_pagamento(pqt_dias number) is
    wvl_bruto   number;
    wvl_ir      number;
    wvl_liquido number;
begin
    wvl_bruto := pqt_dias * 25;
    declare
        wtx_ir number;  -- escopo limitado a este sub-bloco
    begin
        if wvl_bruto > 5400 then
            wtx_ir := 27;
        else
            wtx_ir := 8;
        end if;
        wvl_ir := (wvl_bruto * wtx_ir) / 100;
        wvl_liquido := wvl_bruto - wvl_ir;
    end;
    dbms_output.put_line('Valor líquido: ' || wvl_liquido);
exception
    when others then
        dbms_output.put_line('Erro ao calcular pagamento: ' || sqlerrm);
end folha_pagamento;
/

Tentar acessar wtx_ir fora do sub-bloco onde foi declarada geraria um erro — seu escopo termina no end do bloco interno.

Transações

Definição: Transação

Unidade lógica de trabalho composta por um ou mais comandos DML (insert, update, delete) — os comandos DCL (Data Control Language) commit e rollback controlam se essas alterações são de fato efetivadas no banco. Uma transação existe justamente para garantir que um conjunto de alterações relacionadas (ex.: os vários passos de uma transferência bancária entre contas) aconteça por inteiro ou não aconteça — evitando que o banco fique num estado inconsistente pela metade.

Definição: commit x rollback x savepoint

• commit — torna permanentes todas as alterações feitas no banco durante a sessão desde o último commit.
• rollback — desfaz todas as alterações feitas desde o último commit, restaurando os dados ao estado anterior.
• savepoint nome — cria um ponto de salvamento nomeado dentro de uma transação; rollback to nome desfaz só as alterações feitas depois daquele ponto específico, sem descartar a transação inteira. Pouco usado em código PL/SQL grande, por poder desestruturar a lógica do programa.

begin
    insert into dept values (41, 'GENERAL LEDGER', '');
    savepoint ponto_um;
    insert into dept values (42, 'PURCHASING', '');
    savepoint ponto_dois;
    insert into dept values (43, 'RECEIVABLES', '');
    rollback to ponto_dois;  -- desfaz só o insert do dept 43
    commit;
end;
/

Definição: set transaction

Comando opcional que abre explicitamente uma transação com regras específicas (ex.: set transaction read write) — deve ser sempre o primeiro comando da transação, e enquanto ela estiver aberta só consultas são permitidas até que ela seja encerrada. Um commit, rollback, ou qualquer comando DDL (que possui commit implícito) encerra o efeito do set transaction.

Em PL/SQL, transações seguem os mesmos princípios — inclusive o uso de savepoint — mas com um cuidado a mais: como um bloco PL/SQL agrupa vários comandos numa só ida ao banco, é fácil perder de vista em que ponto exato uma transação foi aberta ou fechada. Manter sempre a consistência e integridade dos dados manipulados deve guiar quando confirmar (commit) ou desfazer (rollback) as alterações.

Variáveis e constantes

Variáveis e constantes em PL/SQL seguem as mesmas regras de identificadores já vistas, com um detalhe importante: constantes exigem um valor inicial obrigatório (constant), enquanto variáveis podem ou não ter um valor padrão (default).

declare
    dt_entrada  date            default sysdate;
    dt_saida    date;
    fornecedor  tipo_pessoa;               -- tipo definido pelo desenvolvedor
    qt_max      number(5)       default 1000;
    qt_min      constant number(50) default 100;
    nm_pessoa   char(60);
    vl_salario  number(11,2);
    in_nao      constant boolean default false;
    qtd         number(10)      := 0;
    vl_perc     constant number(4,2) := 55.00;
    cd_cargo    employee.job%type;         -- mesmo tipo da coluna job de employee
    reg_depto   department%rowtype;        -- estrutura inteira da linha de department
end;

Definição: %TYPE e %ROWTYPE

Duas formas de declarar uma variável referenciando o tipo de outra coisa já existente no banco, em vez de repetir o tipo manualmente (evitando quebras se a coluna original mudar de tipo depois). coluna%type faz a variável assumir o mesmo tipo de dado de uma coluna específica de uma tabela. tabela%rowtype cria uma variável do tipo registro (uma estrutura heterogênea, detalhada mais adiante), com um campo para cada coluna da tabela, refletindo a estrutura inteira da linha.

O escopo de variáveis e constantes segue a mesma regra vista para identificadores em geral: limitado ao bloco (e sub-blocos) onde foram declaradas.

Tipos de dados em PL/SQL

Tipo	Uso
VARCHAR2(tamanho)	Texto de tamanho variável — só ocupa o espaço realmente usado. Tipo recomendado para texto (VARCHAR/STRING existem só por compatibilidade e não devem ser usados).
CHAR(tamanho)	Texto de tamanho fixo — sempre ocupa o tamanho máximo declarado, completando com espaços.
NUMBER(p,s)	Numérico com sinal e ponto decimal — p é a precisão (total de dígitos), s é a escala (casas decimais). INTEGER, DECIMAL, FLOAT e outros são subtipos equivalentes a variações de NUMBER.
DATE	Data com hora, minuto e segundo — mesmo que a aplicação só use a parte da data, a hora sempre existe internamente (meia-noite, se não especificada).
BOOLEAN	TRUE ou FALSE — só existe em PL/SQL, não pode ser usado como tipo de uma coluna de tabela.
LONG / LONG RAW	Texto/binário grande (até 2 GB) — apenas uma coluna desse tipo é permitida por tabela, com fortes restrições de uso (não indexável, não pode aparecer em WHERE/GROUP BY/ORDER BY). Considerado obsoleto, substituído pelos tipos LOB.
RAW	Dado binário de tamanho fixo (ex.: dados criptografados, imagens pequenas).
CLOB / NCLOB	Texto muito grande (até (4 GB − 1) × tamanho do bloco de dados) — substituem LONG, permitindo múltiplas colunas desse tipo por tabela. NCLOB usa o conjunto de caracteres nacional do banco.
BLOB	Dado binário não estruturado muito grande (som, imagem, vídeo) — mesma capacidade dos tipos LOB de texto.
BFILE	Referência a um arquivo binário armazenado fora do banco, no sistema de arquivos do servidor (até 4 GB).
ROWID	Tipo especial que guarda o endereço físico de uma linha numa tabela.

Definição: Por que LONG está obsoleto

Os tipos LOB (CLOB, NCLOB, BLOB) surgiram como substitutos de LONG e LONG RAW justamente porque estes só permitiam uma coluna desse tipo por tabela e tinham restrições pesadas de uso (não podiam aparecer em cláusulas WHERE, GROUP BY, ORDER BY, nem ser indexados). Os tipos LOB removem essa limitação de coluna única e são a escolha recomendada hoje para armazenar conteúdo grande.

Exceções

Definição: Exceção (PL/SQL)

Mecanismo de tratamento de erros do PL/SQL. Existem dois tipos: exceções predefinidas, que o Oracle dispara automaticamente quando ocorre um erro conhecido (ex.: divisão por zero), e exceções definidas pelo usuário, que só existem porque foram declaradas e disparadas explicitamente pelo programa — o Oracle não as conhece. Se uma exceção não for tratada por nenhum bloco, o Oracle trata o erro por conta própria e aborta o programa.

Exceções predefinidas

Exceção	Quando ocorre
no_data_found	Um select into não retorna nenhum registro. Não ocorre com funções de agrupamento (sum, avg, ...), que retornam nulo, nem em fetch de cursor.
too_many_rows	Um select into retorna mais de uma linha.
invalid_cursor	Tentativa de usar um cursor que não está aberto.
cursor_already_open	Tentativa de abrir um cursor que já está aberto.
invalid_number	Conversão de tipo impossível num comando SQL dentro do bloco.
value_error	Erro de conversão/tamanho de dado num comando PL/SQL (a contraparte de invalid_number fora do SQL).
dup_val_on_index	Tentativa de inserir um valor duplicado numa coluna com chave única/primária.
login_denied	Tentativa de conectar ao banco com usuário/senha inválidos.
not_logged_on	Tentativa de usar algum recurso do banco sem estar conectado.
program_error	Erro interno do Oracle.
rowtype_mismatch	Um fetch retorna uma linha incompatível com o tipo registro da variável de destino.
timeout_on_resource	Tempo esgotado esperando por um recurso do banco.
zero_divide	Tentativa de dividir um número por zero.
others	Qualquer outro erro não coberto pelas exceções específicas acima.

Definição: sqlcode e sqlerrm

Duas variáveis disponíveis dentro de qualquer bloco exception, especialmente úteis para diagnosticar o que caiu em others: sqlcode retorna o código numérico do erro gerado pelo Oracle; sqlerrm retorna a descrição textual desse erro. Usadas juntas (sqlerrm || ' - Código: (' || sqlcode || ').'), ajudam a identificar a causa de um erro que não foi tratado especificamente.

declare
    wempno number;
begin
    select empno into wempno from emp where deptno = 30;
exception
    when no_data_found then
        dbms_output.put_line('Empregado não encontrado.');
    when too_many_rows then
        dbms_output.put_line('O departamento informado retornou mais de um registro.');
    when others then
        dbms_output.put_line('Erro: ' || sqlerrm || ' - Código: (' || sqlcode || ').');
end;
/

Definição: Ordem e escopo das cláusulas exception

Exceções específicas (when no_data_found then ...) devem sempre vir antes de when others — do contrário, qualquer erro cairia na cláusula others antes de chegar às específicas, tornando-as inúteis. Uma área exception é válida apenas dentro do bloco onde está declarada: se um erro não for tratado no bloco mais interno onde ocorreu, o controle sobe para a área exception do bloco mais externo mais próximo — seguindo a hierarquia de blocos aninhados. Se o erro acontecer dentro de um loop sem nenhuma área de tratamento disponível, o loop é interrompido e o controle sobe do mesmo jeito.

Definição: raise_application_error

Procedure nativa que interrompe a execução do bloco atual e força o desvio para a área exception mais próxima, permitindo lançar um erro customizado de propósito — por exemplo, quando uma regra de negócio é violada, mesmo sem nenhum erro técnico do banco ter ocorrido. Recebe dois parâmetros: um código de erro (deve estar na faixa reservada de -20000 a -20999, exclusiva para erros customizados) e uma mensagem descritiva.

begin
    raise_application_error(-20000, 'Valores não encontrados para o departamento 99.');
end;
/

Exceções definidas pelo usuário

Definição: Exceção definida pelo usuário

Diferente das predefinidas, o Oracle não sabe quando uma exceção definida pelo usuário deve ocorrer — cabe ao próprio programa declará-la (na área declare, com o tipo exception) e dispará-la explicitamente com o comando raise quando a condição desejada for verdadeira, geralmente dentro de um if.

declare
    wsal          number;
    werro_salario exception;
begin
    select nvl(avg(sal), 0) into wsal from emp where deptno = 99;
    if wsal = 0 then
        raise werro_salario;
    end if;
exception
    when werro_salario then
        dbms_output.put_line('O salário necessita ser maior que zero.');
    when no_data_found then
        dbms_output.put_line('Empregado não encontrado.');
    when others then
        dbms_output.put_line('Erro: ' || sqlerrm || ' - Código: (' || sqlcode || ').');
end;
/

A partir do momento em que o raise aciona a exceção, as regras de propagação e tratamento são exatamente as mesmas já vistas para exceções predefinidas — inclusive podendo ser tratadas dentro de functions, procedures e packages, sem exigir intervenção do usuário final.

Estruturas de condição: if

Definição: if (PL/SQL)

Estrutura de condição usada para alterar o fluxo do programa dependendo se uma condição é verdadeira ou falsa. Tem três variações, cada uma cobrindo um nível diferente de complexidade: if-end if (executa algo só se a condição for verdadeira), if-else-end if (um caminho para verdadeiro, outro para falso) e if-elsif(-else)-end if (permite testar várias condições em sequência).

-- if-end if: executa só se a condição for verdadeira
if <condicao> then
    <instruções>
end if;

-- if-else-end if: um caminho para cada resultado
if <condicao> then
    <instruções>
else
    <instruções>
end if;

-- if-elsif(-else)-end if: várias condições em sequência
if <condicao_1> then
    <instruções>
elsif <condicao_2> then
    <instruções>
else
    <instruções>
end if;

declare
    x   number := 10;
    res number;
begin
    res := mod(x, 5);
    if res = 0 then
        dbms_output.put_line('O resto da divisão é zero!');
    elsif res > 0 then
        dbms_output.put_line('O resto da divisão não é zero!');
    else
        dbms_output.put_line('O resto da divisão é menor que zero!');
    end if;
end;
/

Definição: if aninhado (nested if)

Uma declaração if pode conter outra if dentro do seu escopo, permitindo filtrar uma condição em várias camadas sucessivas. É uma ferramenta poderosa, mas usada com moderação — muitos níveis de aninhamento dificultam a leitura e a depuração do programa. O recomendado é não ultrapassar 4 níveis de if aninhados.

Formatando as declarações if

Um código com if bem formatado é mais fácil de ler e depurar depois. Boas práticas:

• Recuar (indentar) a próxima declaração para dentro a cada novo nível de if.
• Deixar comentários depois da declaração if, nunca na mesma linha.
• Recuar o corpo de instruções dentro de cada bloco if, a partir da própria declaração.
• Se uma condição for grande demais e precisar quebrar linha, recuar a continuação.
• Sempre alinhar else/elsif na mesma coluna do if a que correspondem, e o end if também.

Evitando erros comuns no uso de if

• Verificar se toda declaração if tem seu end if correspondente — e se elsif não foi digitado por engano como elseif (um erro comum de sintaxe).
• Evitar ifs aninhados complexos demais — se a lógica ficar difícil de acompanhar, vale avaliar se uma função separada resolveria melhor a mesma tarefa.
• Verificar se não foi colocado um espaço ou traço no lugar errado em end if (a sintaxe correta é sempre end if, com um espaço, nunca endif).
• Não esquecer a pontuação: ; depois de end if e depois de cada declaração — exceto logo após a palavra-chave then, que não leva ;.

Comandos de repetição

PL/SQL oferece três estruturas de repetição — for loop, while loop e loop — cada uma adequada a uma situação diferente.

for loop

Definição: for loop

Repete um bloco de código um número fixo de vezes, definido por um intervalo (início..fim). A variável de controle não precisa ser declarada — o próprio for a declara e a incrementa automaticamente a cada volta, e seu escopo se limita ao loop.

begin
    for i in 1..10 loop
        dbms_output.put_line('5 X ' || i || ' = ' || (5*i));
    end loop;
end;
/

O intervalo aceita a palavra-chave reverse, para contar de trás para frente, e pode usar variáveis em vez de números fixos (ex.: for x in inter1..inter2 loop) — e também pode ser aninhado, um for dentro do outro, o externo controlando quantas vezes o interno roda por completo.

begin
    for i in reverse 1..10 loop
        dbms_output.put_line('5 X ' || i || ' = ' || (5*i));
    end loop;
end;
/

Definição: Cuidados ao usar for loop

• Não definir o intervalo do mais alto para o mais baixo sem usar reverse.
• Não deixar que as variáveis do intervalo (início/fim) acabem em null.
• Não esquecer o ; depois de end loop.
• Ao aninhar loops, conferir se a lógica de cada nível está correta.

while loop

Definição: while loop

Repete um bloco enquanto uma condição permanecer verdadeira, avaliada antes de cada execução — diferente do for loop (repetições fixas) ou do loop simples (roda pelo menos uma vez), o while pode nunca executar o bloco, se a condição já começar falsa.

declare
    x          number default 0;
    label_vert varchar2(240) default '&label';
    tam_label  number default 0;
begin
    tam_label := length(label_vert);
    while (x < tam_label) loop
        x := x + 1;
        dbms_output.put_line(substr(label_vert, x, 1));
    end loop;
end;
/

loop

Definição: loop (simples)

O comando de repetição mais simples do PL/SQL — não tem intervalo nem condição embutida, por isso seu funcionamento é, em tese, infinito. Sempre precisa ser combinado com um exit (ou exit when) para determinar quando a repetição deve parar — sem isso, o programa entra num loop eterno.

Definição: exit x exit when

exit, geralmente dentro de um if, encerra o loop imediatamente quando alcançado. exit when <condição> faz a mesma coisa de forma mais direta, sem precisar de um if auxiliar — é a forma recomendada, por ficar mais fácil de acompanhar e exigir menos código.

declare
    x          number default 0;
    label_vert varchar2(240) default '&label';
    tam_label  number default 0;
begin
    tam_label := length(label_vert);
    loop
        x := x + 1;
        dbms_output.put_line(substr(label_vert, x, 1));
        exit when x = tam_label;
    end loop;
end;
/

Definição: PL/SQL não tem repeat until

Diferente de outras linguagens, o PL/SQL não possui um comando repeat until (repita até). O loop combinado com exit when cobre essa mesma necessidade.

Qual loop usar

Situação	Loop recomendado
Sabe exatamente quantas vezes o bloco deve rodar	for loop
Não há certeza de quantas vezes vai rodar, e a condição pode já começar falsa (o bloco pode nunca executar)	while loop
Precisa de um "repita até" — roda pelo menos uma vez, para com exit/exit when	loop

Definição: Orientações gerais sobre loops

• Sempre garanta que a condição de exit/exit when será atendida ao menos uma vez — caso contrário, o loop é infinito.
• Prefira exit when a um exit dentro de um if — é mais direto e legível.
• Use nomes descritivos (rótulos) para loops aninhados, facilitando a leitura.
• Evite usar return para sair de um loop dentro de uma função — é uma forma incorreta de encerrar um loop, mesmo funcionando tecnicamente.
• Ao criar variáveis de limite superior/inferior num for, use variáveis (não valores fixos) se esses limites puderem mudar no futuro.

Cursores

Definição: Cursor

Estrutura do PL/SQL que permite percorrer, linha a linha, o resultado de um select — funciona como uma área de memória que guarda o conjunto de linhas retornado, junto com um ponteiro que indica a linha atual. Existem dois tipos: explícito, declarado e controlado manualmente pelo programador, e implícito, criado, aberto e fechado automaticamente pelo próprio Oracle.

Cursor explícito

Definição: Ciclo de vida de um cursor explícito

Um cursor explícito passa por quatro etapas, sempre nessa ordem: declarar (cursor nome is select ..., na área declare, podendo receber parâmetros, assim como uma variável), abrir (open nome, que executa o select e posiciona o cursor antes da primeira linha), buscar/recuperar (fetch nome into variável, que traz uma linha por vez — repetido dentro de um loop até não haver mais linhas) e fechar (close nome, liberando os recursos). A variável de destino do fetch costuma ser declarada com nome_do_cursor%rowtype, para casar automaticamente com as colunas do select do cursor.

declare
    cursor c1 is
        select ename, job
        from emp
        where deptno = 30;
    r1 c1%rowtype;
begin
    open c1;
    loop
        fetch c1 into r1;
        exit when c1%notfound;
        dbms_output.put_line('Nome: ' || r1.ename || ' Cargo: ' || r1.job);
    end loop;
    close c1;
end;
/

Definição: Parâmetros de um cursor

Assim como uma procedure, um cursor pode receber parâmetros — informados entre parênteses na declaração (cursor c1(p_deptno number, p_job varchar2) is ...) e passados na hora de abri-lo (open c1(30, 'CLERK')). Os valores podem ser passados posicionalmente (na mesma ordem dos parâmetros) ou nomeados (open c1(p_deptno => 30, p_job => 'CLERK')) — a passagem nomeada é mais segura quando há muitos parâmetros, por não depender da ordem exata.

Cursor for loop

Definição: Cursor for loop

Uma forma simplificada de trabalhar com cursores, que dispensa open, fetch, exit when %notfound e close — o próprio for cuida de todo o ciclo de vida automaticamente, criando a variável de linha (do tipo %rowtype do cursor) sem precisar declará-la. É a abordagem recomendada quando o programa precisa ler todas as linhas do cursor; quando o controle precisa ser manual (ex.: parar antes do fim), o cursor tradicional com loop/fetch continua sendo necessário.

declare
    cursor c1(pmgr number, pdname varchar2) is
        select ename, job, dname
        from emp, dept
        where emp.deptno = dept.deptno
        and dept.loc = pdname
        and emp.mgr = pmgr;
begin
    for r1 in c1(pmgr => 7698, pdname => 'CHICAGO') loop
        dbms_output.put_line('Nome: ' || r1.ename || ' Cargo: ' || r1.job);
    end loop;
end;
/

O select do cursor também pode ser definido diretamente dentro do próprio for, sem precisar de uma área declare separada — útil para cursores usados uma única vez, mas menos reaproveitável se o mesmo select precisar ser chamado em vários lugares (qualquer alteração exigiria editar cada ocorrência, em vez de um único lugar):

begin
    for r1 in (select empno, ename from emp where job = 'MANAGER') loop
        dbms_output.put_line('Gerente: ' || r1.empno || ' - ' || r1.ename);
    end loop;
end;
/

Cursor implícito

Definição: Cursor implícito

Criado automaticamente pelo Oracle sempre que um comando insert, update, delete ou select into é executado dentro de um bloco PL/SQL, sem que o desenvolvedor precise declará-lo. Para insert/update/delete, o Oracle cria o cursor, executa o comando e o fecha. Para select into, o processo é mais custoso: o Oracle cria o cursor, faz um primeiro fetch (para trazer a linha), faz um segundo fetch só para confirmar que não há mais de uma linha (o que dispara too_many_rows, se houver), e então fecha o cursor.

Definição: Por que cursor explícito pode ter melhor performance

Um cursor explícito usado só para uma linha (com fetch único) evita o segundo fetch de verificação que o select into sempre faz — para selects complexos, com muitas tabelas e grandes volumes de dados, evitar esse passo extra pode gerar ganhos de performance consideráveis.

Atributos de cursor

Definição: Atributos de cursor

Informações que o Oracle disponibiliza sobre o estado de um cursor, acessadas com nome_do_cursor%atributo. Cursores implícitos usam sempre o nome fixo sql (sql%found, por exemplo) — como só existe um cursor implícito "atual" por vez, seus atributos refletem sempre o último comando SQL executado no bloco.

Atributo	Indica
%found	true se o último fetch (ou insert/update/delete) afetou/retornou alguma linha.
%notfound	O oposto de %found — true se nenhuma linha foi retornada/afetada. Usado tipicamente em exit when cursor%notfound.
%rowcount	Quantidade de linhas já lidas (cursor explícito) ou afetadas por um insert/update/delete/select into (cursor implícito).
%isopen	true se o cursor está aberto. Só existe para cursores explícitos — um cursor implícito é aberto e fechado pelo Oracle antes que o programa tenha chance de checar.

Cursores encadeados

Definição: Cursores encadeados

Um cursor pode ser aberto e percorrido dentro do loop de outro cursor — tipicamente usado quando o resultado de um depende de uma coluna retornada pelo outro (ex.: para cada gerente encontrado no cursor externo, buscar seus subordinados no cursor interno, passando o empno do gerente como parâmetro).

declare
    cursor c1 is
        select empno, ename from emp where job = 'MANAGER';
    cursor c2(pmgr number) is
        select empno, ename, dname
        from emp, dept
        where emp.deptno = dept.deptno
        and mgr = pmgr;
begin
    for r1 in c1 loop
        dbms_output.put_line('Gerente: ' || r1.empno || ' - ' || r1.ename);
        for r2 in c2(r1.empno) loop
            dbms_output.put_line('  Subordinado: ' || r2.empno || ' - ' || r2.ename);
        end loop;
    end loop;
end;
/

Cursor com FOR UPDATE

Definição: for update

Cláusula acrescentada ao select de um cursor para garantir exclusividade sobre as linhas retornadas enquanto o cursor estiver aberto — nenhuma outra sessão consegue alterar essas linhas até um commit ou rollback liberá-las. Pode travar a tabela inteira (for update) ou só colunas específicas (for update of coluna) — quando o select envolve várias tabelas, é preciso informar de qual tabela vêm as colunas travadas.

Definição: nowait

Por padrão, se as linhas já estiverem travadas por outra sessão, o cursor for update espera o recurso ser liberado. A diretiva for update ... nowait evita essa espera: se o recurso já estiver ocupado, uma exceção é disparada imediatamente, em vez de bloquear o programa.

Definição: where current of

Cláusula usada num update ou delete dentro do loop de um cursor for update, para alterar/excluir exatamente a linha em que o cursor está posicionado no momento — sem precisar reescrever a condição where do cursor. O Oracle usa o rowid da linha atual, o que torna o acesso mais direto e rápido do que refazer a busca por chave. Só funciona quando o select do cursor referencia colunas de uma única tabela (quando o for update trava várias tabelas de uma vez, current of não sabe a qual delas se referir).

declare
    cursor c1(pdeptno number) is
        select *
        from emp
        where deptno = pdeptno
        for update of sal nowait;
    r1 c1%rowtype;
    wreg_atualizados number default 0;
begin
    open c1(pdeptno => 10);
    loop
        fetch c1 into r1;
        exit when c1%notfound;
        update emp set sal = sal + 100.00
        where current of c1;
        wreg_atualizados := wreg_atualizados + sql%rowcount;
    end loop;
    dbms_output.put_line(wreg_atualizados || ' registros atualizados!');
end;
/

Funções de caracteres, cálculos e operadores aritméticos

Definição: Funções built-in do Oracle

Além da lógica escrita pelo desenvolvedor, o Oracle disponibiliza um conjunto de funções nativas para manipular texto e números diretamente dentro de comandos SQL (podem ser usadas em qualquer cláusula, exceto from) — evitando que o programa precise implementar essas manipulações manualmente. Não são um recurso exclusivo do PL/SQL: as mesmas funções funcionam em qualquer select executado direto no SQL*Plus.

Funções de caracteres

Função	O que faz
initcap(texto)	Deixa maiúscula a primeira letra de cada palavra.
lower(texto)	Converte todos os caracteres para minúsculo.
upper(texto)	Converte todos os caracteres para maiúsculo.
substr(texto, início, tamanho)	Extrai um trecho de uma string a partir de uma posição.
to_char(valor, máscara)	Converte um número (ou data) para string, opcionalmente aplicando uma máscara de formatação (ex.: to_char(salario, 'fm999g990d00')).
instr(texto, busca)	Retorna a posição da primeira ocorrência de um trecho dentro do texto.
length(texto)	Retorna o tamanho (em bytes) do texto.
rpad(texto, tamanho, preenchimento)	Alinha à esquerda, completando à direita até o tamanho informado.
lpad(texto, tamanho, preenchimento)	Alinha à direita, completando à esquerda até o tamanho informado.

declare
    wnome varchar2(100) default 'analista de sistemas';
begin
    dbms_output.put_line(initcap(wnome));  -- Analista De Sistemas
end;
/

Funções de cálculo

Função	O que faz
round(valor, casas)	Arredonda um valor com o número de casas decimais informado.
trunc(valor, casas)	Trunca (corta, sem arredondar) um valor com casas decimais.
mod(valor, divisor)	Retorna o resto da divisão entre dois valores.
sqrt(valor)	Retorna a raiz quadrada.
power(base, expoente)	Retorna um valor elevado a outro.
abs(valor)	Retorna o valor absoluto (sem sinal).
ceil(valor)	Retorna o menor inteiro maior ou igual ao valor (arredonda para cima).
floor(valor)	Retorna o maior inteiro menor ou igual ao valor (arredonda para baixo).
sign(valor)	Retorna 1 se o valor é maior que zero, -1 se é menor, 0 se é igual.

Operadores aritméticos

Os operadores + (soma), - (subtração), * (multiplicação) e / (divisão) podem ser usados diretamente em qualquer comando SQL, inclusive combinando colunas de uma tabela com valores literais — como em trunc((sysdate - hiredate) / 365), que calcula quantos anos completos se passaram desde uma data.

Funções de data

Definição: Funções de data (Oracle)

Funções nativas para manipular valores do tipo date — aplicar formatações, extrair partes específicas (dia, mês, ano) ou calcular diferenças entre datas.

Função	O que faz
sysdate	Retorna a data e hora corrente do servidor do banco de dados.
current_date	Retorna a data corrente ajustada ao fuso horário da sessão do usuário — pode diferir de sysdate se a sessão foi aberta num fuso diferente do servidor.
sessiontimezone	Mostra o fuso horário da sessão atual (calculado em relação ao meridiano de Greenwich).
add_months(data, n)	Soma (ou subtrai, com n negativo) n meses a uma data.
months_between(data1, data2)	Retorna quantos meses existem entre duas datas.
next_day(data, dia_semana)	Retorna a próxima ocorrência do dia da semana informado (ex.: 'FRIDAY'), a partir de uma data.
last_day(data)	Retorna o último dia do mês de uma data.
round(data, 'year') / trunc(data, 'year')	Arredonda/trunca uma data para uma unidade (ano, mês, ...) — mesmas funções usadas para números, aplicadas a datas com o parâmetro de formato (fmt).

declare
    wtermino_exp    date;
    wmeses_trabalho varchar2(20);
    cursor c1 is
        select ename, dname, hiredate
        from emp e, dept d
        where e.deptno = d.deptno
        and add_months(hiredate, 350) >= sysdate;
begin
    for r1 in c1 loop
        wtermino_exp    := add_months(r1.hiredate, 3);
        wmeses_trabalho := to_char(months_between(sysdate, r1.hiredate), '990D0');
        dbms_output.put_line('Nome: ' || r1.ename ||
                              ' Término Exp.: ' || wtermino_exp ||
                              ' Meses Trab.: ' || wmeses_trabalho);
    end loop;
end;
/

Funções de conversão

Definição: to_date, to_number e to_char

As três funções de conversão de tipo mais usadas no dia a dia. to_date converte uma string para date; to_number converte uma string para número; to_char converte um número ou uma data para string — a única das três voltada para exibição/formatação. Todas recebem o valor a converter, uma máscara de formato (obrigatória sempre que o valor não estiver no formato padrão da sessão) e, opcionalmente, um parâmetro de idioma/localidade.

Definição: to_date/to_number convertem, não formatam a exibição

Um erro comum é achar que o formato passado para to_date/to_number também controla como o valor aparece na tela depois — não controla. Essas duas funções usam o formato só para entender a string de entrada; o valor resultante é exibido no formato padrão da sessão (nls_date_format, no caso de datas). Para controlar a exibição, use to_char.

declare
    wdata date;
begin
    wdata := to_date('010182', 'ddmmrr');  -- entende a string de entrada
    dbms_output.put_line('Data: ' || wdata);  -- exibida no formato da sessão
end;
/

Elementos de formato de data mais usados:

Elemento	Representa
YYYY / YY	Ano com 4 ou 2 dígitos.
RR / RRRR	Alternativa "segura para o século" a YY/YYYY: interpreta o século com base numa regra de proximidade (ex.: '49' vira 2049, '50' vira 1950), evitando o clássico "bug do ano 2000" de truncar o ano em 2 dígitos.
MM	Mês, numérico (01-12).
MONTH / MON	Nome do mês por extenso / abreviado em 3 letras.
DD	Dia do mês (1-31).
DDD	Dia do ano (1-366).
DAY	Nome do dia da semana por extenso.
HH / HH12 / HH24	Hora (1-12 ou 0-23).
MI / SS	Minutos / segundos.
FM	Remove espaços em branco que sobrariam por ausência de caracteres no formato.

Definição: Limitações do to_date

A string a converter não pode ter mais de 220 caracteres; a máscara não pode repetir o mesmo elemento duas vezes (ex.: 'DD-MM-MM'); e não pode misturar HH24 (0-23) com AM/PM (indicadores de meridiano, que só fazem sentido com HH/HH12).

Definição: Separador decimal e de milhar em to_number

Por padrão, o Oracle usa ponto como separador decimal e vírgula como separador de milhar nos cálculos internos — mas a sessão pode ter outra convenção configurada (nls_numeric_characters). Se a string passada para to_number usa separadores diferentes dos configurados na sessão, o Oracle gera erro de conversão, mesmo que a máscara pareça compatível à primeira vista. É possível alterar a sessão inteira (alter session set nls_numeric_characters = '.,') ou informar os separadores só para aquela chamada, via o terceiro parâmetro opcional (to_number('4.569.900,87', '9G999G999D00', 'nls_numeric_characters=''.,''')).

Definição: Outros parâmetros de sessão relacionados

alter session set nls_language = '...' muda o idioma geral da sessão; nls_date_language muda especificamente o idioma usado em nomes de mês/dia; nls_date_format muda o formato padrão usado para exibir datas quando nenhuma máscara é informada.

to_char: formatando números para exibição

Elemento	O que faz
9	Cada 9 representa um dígito — zeros à esquerda viram espaço em branco.
0	Como 9, mas exibe zeros à esquerda/direita em vez de espaço.
$	Prefixa o símbolo de moeda.
S	Exibe sinal (+/-) explícito, conforme o valor.
D	Posição do ponto/vírgula decimal (usa o separador da sessão).
G	Posição do separador de grupo/milhar (usa o separador da sessão).
L	Símbolo de moeda local.
, / .	Posição literal de vírgula/ponto, independente do separador decimal/de grupo configurado na sessão.
FM	Remove espaços em branco extras do resultado.

declare
    wsal_formatado varchar2(50);
begin
    for r1 in (select sal from emp) loop
        wsal_formatado := 'R$ ' || to_char(r1.sal, 'fm999g999d00');
        dbms_output.put_line('Salário Formatado: ' || wsal_formatado);
    end loop;
end;
/
-- Salário Formatado: R$ 800.00

to_char também formata datas, usando os mesmos elementos vistos na tabela de to_date — útil para montar textos por extenso:

declare
    wdata_extenso varchar2(100);
begin
    wdata_extenso := initcap(to_char(sysdate, 'fmmonth')) || ' de ' ||
                     to_char(sysdate, 'dd') || ', ' || to_char(sysdate, 'yyyy');
    dbms_output.put_line(wdata_extenso);
end;
/

Funções condicionais

Definição: nvl e nullif

nvl(valor, substituto) retorna substituto se valor for null; caso contrário, retorna o próprio valor. nullif(valor1, valor2) compara os dois parâmetros: se forem iguais, retorna null; caso contrário, retorna valor1.

Definição: Por que NULL em cálculos e comparações passa despercebido

O Oracle trata qualquer operação envolvendo null (aritmética ou de comparação em where) como resultando em null/falso — sem gerar erro, apenas ignorando silenciosamente a linha ou o valor afetado. Isso torna bugs com null difíceis de perceber: um sum(coluna) com valores nulos simplesmente os ignora no total, sem avisar que algo foi pulado. nvl existe justamente para tornar essa substituição explícita, ao invés de deixá-la acontecer silenciosamente.

declare
    wsal_comm1 number;
    wsal_comm2 number;
begin
    select sum(sal + comm) into wsal_comm1 from emp;          -- ignora linhas com comm nulo
    select sum(sal + nvl(comm, 0)) into wsal_comm2 from emp;  -- trata nulo como zero
    dbms_output.put_line('Sal. Comm1: ' || wsal_comm1 || ' - Sal. Comm2: ' || wsal_comm2);
end;
/

Definição: greatest e least

greatest(lista_de_valores) retorna o maior valor de uma lista passada como parâmetro; least(lista_de_valores) retorna o menor. Os demais valores são convertidos para o tipo de dado do primeiro antes da comparação.

decode e case

Definição: decode

Função exclusiva do Oracle que funciona como um if-else/switch dentro de um select: compara um valor a uma lista de pares (comparação, resultado) e retorna o resultado correspondente à primeira comparação que bater — com um último parâmetro opcional como valor padrão, caso nenhuma bata.

select ename, job, mgr,
    decode(mgr, 7902, 'MENSALISTA',
                7839, 'COMISSIONADO',
                7566, 'MENSAL/HORISTA',
                'OUTROS') tipo
from emp;

Definição: case

Estrutura equivalente ao decode, mas seguindo o padrão ANSI SQL — funciona em qualquer banco de dados relacional, não só Oracle (embora o Oracle também a suporte, em versões mais recentes).

select ename, job, mgr,
    case
        when mgr = 7902 then 'MENSALISTA'
        when mgr = 7839 then 'COMISSIONADO'
        when mgr = 7566 then 'MENSAL/HORISTA'
        else 'OUTROS'
    end tipo
from emp;

Definição: Quando usar decode x case

Ambos produzem o mesmo resultado — a escolha depende da abrangência do projeto: para aplicações específicas de Oracle, decode é uma opção válida (e, para quem já o conhece, pode deixar o código mais direto); para aplicações que precisam funcionar em vários bancos de dados diferentes, case é a escolha correta, por seguir o padrão ANSI.

Programas armazenados

Definição: Programa armazenado

Um bloco PL/SQL nomeado e gravado no banco de dados (diferente de um bloco anônimo, que existe só durante sua própria execução). Pode ser do tipo procedure, function ou package (pacotes são detalhados na próxima seção). Benefícios de armazenar um programa no banco: reaproveitamento (o mesmo código fica disponível para qualquer parte do sistema que precise dele — ex.: uma função de validação de CPF usada por vários módulos), rapidez (já compilado, não precisa ser reenviado/recompilado a cada execução), controle de alterações (manter o programa num único lugar, facilitando atualizá-lo), controle de acesso (concessões de permissão limitam quem pode executá-lo) e modularização (programas relacionados podem ser agrupados em packages).

procedure x function

Definição: procedure x function

A diferença central: uma function sempre retorna um valor (via return), e seu cabeçalho declara o tipo desse retorno; uma procedure não é obrigada a retornar nada (embora possa "retornar" valores indiretamente através de parâmetros out, ver adiante). Funções podem ser chamadas de dentro de comandos SQL (select, where); procedures não podem — só podem ser executadas via execute (no SQL*Plus) ou de dentro de um bloco PL/SQL. Funções também têm uma restrição: seu corpo não pode conter comandos DML, DDL ou DCL quando chamadas a partir de um comando SQL — apenas selects.

create or replace procedure calc (x1 in number, x2 in number,
                                   op in varchar2, res out varchar2) is
begin
    if x1 + x2 = 0 then
        res := '0';
    elsif op = '*' then
        res := to_char(x1 * x2);
    elsif op = '/' then
        if x2 = 0 then
            res := 'Erro de divisão por zero!';
        else
            res := to_char(x1 / x2);
        end if;
    elsif op = '+' then
        res := to_char(x1 + x2);
    else
        res := 'Operador inválido!';
    end if;
end;
/

create or replace function valida_cpf(cpf in char) return varchar2 is
    m_total  number default 0;
    m_digito number default 0;
begin
    for i in 1..9 loop
        m_total := m_total + substr(cpf, i, 1) * (11 - i);
    end loop;
    m_digito := 11 - mod(m_total, 11);
    if m_digito > 9 then
        m_digito := 0;
    end if;
    if m_digito != substr(cpf, 10, 1) then
        return 'I';  -- inválido
    end if;
    return 'V';  -- válido
end valida_cpf;
/

Definição: Programas armazenados podem ser criados localmente também

procedure/function também podem ser declaradas dentro da área declare de um bloco anônimo (ou de outro programa armazenado) — nesse caso, não usam create, ficam visíveis só dentro daquele bloco, e não são gravadas no banco.

Passando parâmetros

Definição: Modos de parâmetro — in, out, in out

• in (padrão quando nenhum modo é informado) — parâmetro de entrada: seu valor pode ser lido e atribuído a outras variáveis, mas não pode ser reatribuído dentro do programa.
• out — parâmetro de saída: pode ser atribuído dentro do programa (é assim que o valor "sai" de volta para quem chamou), mas não pode ser lido/usado do lado direito de uma atribuição.
• in out — combina os dois: pode ser lido e reatribuído livremente.

procedure exemplo(param1 in number, param2 out number, param3 in out number) is
    x number;
    y number;
    z number;
begin
    x := param1;  -- uso correto
    param1 := x;  -- uso incorreto

    y := param2;  -- uso incorreto
    param2 := y;  -- uso correto

    z := param3;  -- uso correto
    param3 := z;  -- uso correto
end;
/

Definição: Chamando um programa com parâmetros

Assim como cursores (ver Parâmetros de um cursor), a passagem pode ser posicional (respeitando a ordem definida no cabeçalho) ou nomeada (calc(x1 => 10, x2 => 5, op => '*', res => wres)) — nomeada dispensa respeitar a ordem original, o que reduz o risco de erro quando há muitos parâmetros.

Uma function só pode devolver um único valor através do seu return — mas, assim como uma procedure, também pode receber parâmetros out para devolver informações adicionais numa única chamada.

Gerenciando programas armazenados

Definição: create or replace

Recriar um programa armazenado do zero (drop + create) perde grants e sinônimos associados a ele. create or replace atualiza a definição preservando esses vínculos — e continua funcionando mesmo que o novo código tenha erros de sintaxe (o objeto simplesmente fica marcado como inválido, em vez de a operação inteira falhar).

Comando/view	Uso
grant execute on objeto to usuário/public	Concede permissão de execução.
create synonym apelido for objeto	Cria um alias para facilitar o acesso — não concede permissão por si só.
alter procedure/function nome compile	Força a recompilação do objeto.
user_objects / all_objects / dba_objects	Consultam metadados (status, data de criação) dos objetos.
desc nome	Mostra o cabeçalho de uma procedure/function: parâmetros, tipos e modos (in/out).
user_source / all_source / dba_source	Consultam o código-fonte armazenado dos objetos.
show error	Mostra os erros de compilação do último objeto criado/alterado.
user_errors / all_errors / dba_errors	Consultam erros de compilação de forma persistente (não só do último comando).

Dependência de objetos

Definição: Dependência direta x indireta

Quando um programa A chama um programa B, A tem uma dependência direta de B — B precisa estar válido para que A também esteja. Se B, por sua vez, chama C, A tem uma dependência indireta de C (via B). O Oracle consegue, na maioria dos casos, identificar essas cadeias e restabelecer automaticamente a validação de quem depende de um objeto corrigido — mas isso não é garantido em todos os casos, por isso vale sempre conferir o status dos objetos após alterações.

flowchart LR
    PROC1 -->|direta| PROC2
    PROC2 -->|direta| PROC3
    PROC3 -->|direta| PROC4
    PROC1 -.->|indireta| PROC3
    PROC1 -.->|indireta| PROC4
    PROC2 -.->|indireta| PROC4

Definição: Ordem de criação e invalidação em cascata

Criar objetos fora da ordem de dependência (ex.: criar proc1, que chama proc2, antes de proc2 existir) faz o Oracle criá-los mesmo assim, mas marcados como inválidos — o ideal é criar sempre na ordem inversa da dependência (do mais dependido para o que mais depende: proc4, proc3, proc2, proc1). Invalidar um objeto não afeta quem ele depende (invalidar proc1 não invalida proc2), mas invalida em cascata quem depende dele (invalidar proc4 invalida proc3, proc2 e proc1, transitivamente).

Definição: Corrigir dependências exige ordem inversa

Depois de um objeto "raiz" ficar inválido e invalidar toda a cadeia acima dele, a correção também precisa respeitar a ordem: recriar proc1 (o topo da cadeia) antes de proc2 estar válido falha com PLS-00905: object ... é inválido — é preciso corrigir de baixo para cima (proc4 primeiro, subindo até proc1).

Definição: Recompilação automática em tempo de execução

Mesmo com um objeto marcado como inválido no banco, o sistema pode continuar funcionando sem erro perceptível: ao executar um objeto inválido, se todas as suas dependências diretas já estiverem válidas, o Oracle tenta recompilá-lo automaticamente na hora — e, tendo sucesso, o objeto (e toda a cadeia acima dele) passa a VALID sem exigir um compile manual.

Packages

Definição: Package

Programa armazenado cujo diferencial é funcionar como um repositório — agrupa vários procedures/functions relacionados (ex.: todos os programas de um módulo de RH, Financeiro ou Comercial) num único objeto, além de poder conter variáveis, cursores, exceções e types compartilhados entre eles. Um package é composto por até duas partes: a specification (obrigatória) e o body (opcional só quando a specification não referencia nenhum objeto que exigiria um corpo — procedures/functions declaradas na specification exigem um body correspondente).

Definição: specification x body

A specification é a área pública do package: só o que está declarado nela pode ser acessado de fora do package (por outros programas/usuários com permissão) — funciona como um "cardápio" do que o package oferece. Pode conter cabeçalhos de procedures/functions, declaração de variáveis/constantes, cursores, exceções e types. O body é onde fica o código de fato: implementa os cabeçalhos declarados na specification e pode conter objetos adicionais (variáveis, cursores, procedures/functions auxiliares) que ficam com escopo privado — inacessíveis de fora, mesmo que outro objeto do próprio body os utilize internamente.

flowchart TB
    subgraph SPEC["package specification (público)"]
        F1["function soma(...)"]
        F2["function subtrai(...)"]
    end
    subgraph BODY["package body (implementação)"]
        F1B["function soma(...) is ... end;"]
        F2B["function subtrai(...) is ... end;"]
        PRIV["procedure imprime_msg(...)<br/>(privada, só o body usa)"]
    end
    SPEC -.->|implementado por| BODY
    F1B --> PRIV
    F2B --> PRIV

create package calculo as
    function soma(x1 number, x2 number) return number;
    function subtrai(x1 number, x2 number) return number;
    function multiplica(x1 number, x2 number) return number;
    function divide(x1 number, x2 number) return number;
end calculo;
/

create package body calculo as
    res number;  -- variável privada, só visível dentro do body

    procedure imprime_msg(msg varchar2) is  -- privada, não está na specification
    begin
        dbms_output.put_line(msg);
    end;

    function soma(x1 number, x2 number) return number is
    begin
        res := x1 + x2;
        return res;
    end;

    function subtrai(x1 number, x2 number) return number is
    begin
        res := x1 - x2;
        if res = 0 then
            imprime_msg('Resultado igual a zero: ' || res);
        end if;
        return res;
    end;

    function multiplica(x1 number, x2 number) return number is
    begin
        return x1 * x2;
    end;

    function divide(x1 number, x2 number) return number is
    begin
        if x2 = 0 then
            imprime_msg('Erro de divisão por zero!');
            return null;
        end if;
        return x1 / x2;
    end;
end calculo;
/

Chamar um objeto público de um package usa a notação package.objeto(...):

declare
    res number;
begin
    res := calculo.soma(450, 550);
    dbms_output.put_line('450 + 550 = ' || res);
end;
/

Tentar acessar calculo.imprime_msg(...) (privada) de fora do package gera erro — ORA-06550: PLS-00302: o componente 'IMPRIME_MSG' deve ser declarado — porque ela só está declarada no body, nunca na specification.

Definição: Package specification sem body — área de sessão

Uma specification sem body (só com variáveis/cursores, sem cabeçalhos de procedures/functions) é útil como área de armazenamento compartilhada entre todos os programas de uma mesma sessão — os valores ficam disponíveis enquanto a sessão estiver aberta, sem precisar de um body para existir.

Definição: Escopo e inicialização de um package

Os objetos de um package têm escopo de sessão do usuário que o executou — não global entre sessões diferentes. A área begin...end de um body (se existir) e a inicialização de variáveis da specification só são executadas uma única vez por sessão, na primeira referência ao package — não a cada chamada.

Definição: Gerenciamento de packages

Os mesmos comandos e views usados para procedures/functions (ver Gerenciando programas armazenados) se aplicam a packages: alter package nome compile, desc nome (lista as procedures/functions públicas), user_objects/all_objects (status — um package aparece como dois objetos, tipo package e package body), user_source (código- fonte) e show error/user_errors (erros de compilação).

Transações autônomas

Definição: Transação autônoma

Recurso que isola a transação de um programa (procedure, function — dentro ou fora de packages — ou bloco PL/SQL anônimo, mas não um sub-bloco nem um trigger sozinho) da transação que a chamou. Ao ser executado, o Oracle abre uma nova sessão de transação só para aquele objeto — a partir desse momento, existem pelo menos duas transações em aberto ao mesmo tempo: a original e a autônoma. Declarada com pragma autonomous_transaction; logo na área declare.

create procedure lista_dept is
    pragma autonomous_transaction;
begin
    for i in (select * from dept order by deptno) loop
        dbms_output.put_line(i.deptno || ' - ' || i.dname);
    end loop;
    commit;
end;
/

Definição: Isolamento de dados entre transação autônoma e a original

Dentro de uma transação autônoma, só são visíveis os dados já efetivados (commitados) no banco — nunca os dados pendentes (não commitados) da transação original que a chamou, mesmo que ambas estejam rodando na mesma sessão de usuário. Isso significa que um insert feito na transação original, mas ainda não commitado, não aparece para um select executado dentro da transação autônoma chamada em seguida.

Definição: Transação autônoma precisa ser encerrada explicitamente

Assim como qualquer transação, uma transação autônoma precisa ser encerrada com commit ou rollback antes do fim do programa que a declarou — caso contrário, ela fica pendente, o que gera erro.

O exemplo a seguir mostra o comportamento na prática: uma procedure insere um país sem commit, chama uma segunda procedure (declarada com transação autônoma) que insere outro país e já efetiva essa inserção sozinha, e por fim desfaz (rollback) a inserção original — mas não a que já foi efetivada de forma independente.

create procedure insere_pais_portugal is
    pragma autonomous_transaction;
begin
    insert into countries (country_id, country_name, region_id)
    values ('PT', 'Portugal', 1);
    commit;  -- efetiva só o que pertence a esta transação autônoma
end;
/

create procedure insere_pais_espanha is
begin
    insert into countries (country_id, country_name, region_id)
    values ('ES', 'Espanha', 1);
    -- neste ponto, a listagem já mostraria a Espanha (mesma transação)
    insere_pais_portugal;
    -- neste ponto, a listagem NÃO mostraria a Espanha (transação autônoma isolada)
    rollback;  -- desfaz a Espanha; Portugal já foi commitado à parte
    -- a listagem agora mostraria Portugal, mas não a Espanha
end;
/

Ao final, a tabela countries contém Portugal (commitado de forma independente pela transação autônoma), mas não Espanha (desfeita pelo rollback da transação original).

Definição: Por que transações autônomas importam para triggers

Certos cenários de uso de triggers (próximo tópico) só funcionam corretamente com transações autônomas — por exemplo, registrar um log de auditoria que precisa persistir mesmo que a operação original que disparou o trigger seja desfeita (rollback) depois.

Triggers

Definição: Trigger

Bloco PL/SQL armazenado no banco, disparado automaticamente por uma ação — nunca chamado diretamente. Existem dois grandes tipos: trigger de banco de dados, associado a uma tabela (ou view) e disparado por insert/update/ delete; e trigger de sistema, disparado por eventos de nível de sistema (login, criação de objetos, erros, etc.) — mais usado por DBAs.

Trigger de tabela x trigger de linha

Definição: Trigger de tabela (ou de comando)

Dispara uma única vez por comando, independente de quantas linhas o comando afetou — não existe a cláusula for each row na sua definição. Útil quando a lógica do trigger não depende dos valores específicos de cada linha alterada (ex.: recalcular um total agregado depois de qualquer mudança na tabela).

create or replace trigger tig_audit_emp
    after insert or delete or update of sal, comm on emp
declare
    wnr_registros       number default 0;
    wvl_total_salario   number default 0;
    wvl_total_comissao  number default 0;
    wnr_registros_audit number default 0;
begin
    select count(*) into wnr_registros from emp;
    select sum(sal) into wvl_total_salario from emp;
    select sum(comm) into wvl_total_comissao from emp;
    select count(*) into wnr_registros_audit from tab_audit_emp;
    if wnr_registros_audit = 0 then
        insert into tab_audit_emp (nr_registros, vl_total_salario, vl_total_comissao)
        values (wnr_registros, wvl_total_salario, wvl_total_comissao);
    else
        update tab_audit_emp
        set nr_registros = wnr_registros, vl_total_salario = wvl_total_salario,
            vl_total_comissao = wvl_total_comissao;
    end if;
end;
/

before delete or insert or update of sal on emp — a lista de eventos que dispara o trigger é informada logo após create trigger nome, junto do momento (before ou after) e, opcionalmente, restrita a colunas específicas (of sal, só dispara se sal for alterada num update; sem essa cláusula, qualquer coluna alterada dispara).

Definição: Trigger de linha

Dispara uma vez para cada linha afetada pelo comando — usa a cláusula for each row. Dentro dele, é possível acessar (e, dependendo do momento, alterar) os valores da linha antes (:old) e depois (:new) da alteração, através de referências criadas automaticamente. insert só tem :new (não existe linha anterior); delete só tem :old (não existe linha nova); update tem os dois.

Definição: :old e :new só podem ser alterados no before

Quando o trigger dispara no before (antes da efetivação no banco), o valor de :new pode ser alterado dentro do trigger — útil para normalizar ou calcular um valor antes de ele ser gravado. No after, os dados já foram efetivados, e :old/:new só podem ser lidos, nunca alterados. :old nunca pode ser alterado, em nenhum momento.

create or replace trigger tig_hist_cargo_emp
    after update of job on emp
    referencing old as v new as n
    for each row
begin
    insert into tab_hist_cargo_emp (empno, job_anterior, job_atual,
                                     dt_alteracao_cargo, ds_historico)
    values (:n.empno, :v.job, :n.job, sysdate,
            'O empregado ' || :n.ename || ' passou para o cargo ' || :n.job || '.');
end;
/

referencing old as v new as n cria apelidos (v/n) para :old/:new — opcional, mas útil para deixar o código mais curto.

Definição: Cláusula when

Restringe quando o corpo do trigger de linha deve de fato executar, com base nos valores de :old/:new — sem precisar de um if dentro do corpo. Pode ser usada tanto em triggers de linha quanto de tabela.

create or replace trigger tig_pc_com_sal_emp
    before insert or update of sal, comm on emp
    referencing old as v new as n
    for each row
    when (n.job = 'SALESMAN')  -- só dispara para vendedores
begin
    :n.pc_com_sal := nvl(:n.comm, 0) * 100 / :n.sal;
end;
/

Definição: Predicados condicionais inserting, updating, deleting

Dentro de um trigger que dispara para mais de um tipo de evento (ex.: after insert or delete or update), os predicados inserting, updating e deleting (booleanos) identificam qual ação, especificamente, disparou aquela execução — útil para ramificar a lógica dentro de um único trigger, em vez de criar um trigger separado para cada evento.

if inserting then
    wacao := 'inserido';
elsif updating then
    wacao := 'atualizado';
elsif deleting then
    wacao := 'excluído';
end if;

Restrições e gerenciamento de triggers

Definição: O que não é permitido dentro de um trigger

Comandos DDL não são permitidos dentro de um trigger. Comandos de controle de transação (commit, rollback, savepoint) também não são permitidos — a única exceção é dentro de um trigger declarado com pragma autonomous_transaction, já que aí ele passa a controlar sua própria transação independente.

Definição: Um trigger pode ser criado inválido

Assim como procedures/functions, o Oracle valida um trigger na criação, mas permite criá-lo mesmo com erros — ficando marcado como inválido, em vez de a criação falhar. Precisa ser corrigido e recriado (create or replace) para voltar a disparar.

Comando	Efeito
alter trigger nome compile	Recompila o trigger.
alter trigger nome disable / enable	Desativa/reativa um trigger específico (permanece criado, só não dispara).
alter table tabela disable/enable all triggers	Desativa/reativa todos os triggers de uma tabela de uma vez.
drop trigger nome	Remove definitivamente.
grant create trigger to usuário	Permite criar triggers no próprio esquema.
grant create any trigger to usuário	Permite criar triggers em qualquer esquema (exceto sys) — usar com cautela.
user_triggers / all_triggers / dba_triggers	Consultam metadados e o código-fonte (trigger_body) dos triggers.

Definição: Recursividade e restrições de definição

Um trigger (de linha ou de tabela) pode agir de forma recursiva — o Oracle garante até 50 níveis de recursividade para esse cenário. Um mesmo trigger não pode contemplar before e after ao mesmo tempo — são sempre dois triggers distintos. Ao excluir uma tabela, todos os triggers associados a ela são excluídos junto.

Ordem de disparo entre triggers de tabela e de linha

Definição: Sequência de disparo

Uma tabela pode ter vários triggers associados. Quando existe um trigger de tabela e um de linha para o mesmo evento, a ordem de disparo segue um padrão fixo: o trigger de tabela before dispara uma vez, antes de qualquer linha ser processada; depois, para cada linha afetada, disparam o trigger de linha before e, em seguida, o de linha after; por fim, depois de todas as linhas processadas, dispara o trigger de tabela after, uma única vez.

flowchart TD
    TB["Trigger de TABELA — before<br/>(dispara 1x)"] --> L1
    subgraph "Para cada linha afetada"
        L1["Trigger de LINHA — before"] --> L2["Trigger de LINHA — after"]
    end
    L2 --> TA["Trigger de TABELA — after<br/>(dispara 1x)"]

Definição: Ordem entre triggers equivalentes não é garantida

Se existirem dois triggers do mesmo tipo e disparados no mesmo momento (ex.: dois triggers de linha before) para o mesmo evento, o Oracle não garante qual dispara primeiro. Se houver dependência entre eles, o recomendado é unificá-los num único trigger.

Mutante table (mutating table)

Definição: Erro de mutante table (ORA-04091)

Limitação dos triggers de linha: dentro deles, não é possível fazer um select (ou qualquer DML) na mesma tabela que disparou o trigger, quando o momento é after — porque, enquanto o trigger está rodando, os dados dessa tabela ainda estão em alteração (a transação não foi confirmada), então uma consulta a ela retornaria um estado inconsistente. O erro não ocorre: quando o trigger é de linha e o momento é before; ou em triggers de tabela, independente do momento (pois estes só disparam depois que todas as linhas já foram processadas).

Definição: Como contornar o mutante table

Duas saídas possíveis: trocar o momento do trigger de after para before (quando a lógica permitir); ou declarar o trigger com pragma autonomous_transaction, abrindo uma transação independente para a consulta — mas atenção: dentro dessa transação autônoma, só são visíveis os dados já confirmados, então uma contagem feita ali pode não refletir a própria alteração que disparou o trigger (que ainda está pendente na transação original).

Trigger de sistema

Definição: Trigger de sistema

Disparado por eventos de nível de sistema, não de uma tabela específica — pode ser associado ao escopo on database (dispara para qualquer usuário) ou on schema (dispara só para o usuário/esquema especificado).

Alguns dos eventos mais comuns:

Evento	Dispara quando
startup / shutdown	O banco de dados é aberto / inicia o fechamento.
servererror	Ocorre um erro no banco (com algumas exceções, como ORA-1034).
after logon / before logoff	Uma conexão é completada / o usuário se desconecta.
before/after create, alter, drop	Um objeto é criado/alterado/removido.
before/after ddl	Um comando DDL é executado.
before grant / before revoke	Uma concessão/revogação de permissão é executada.

create or replace trigger tgr_hist_conexao_usuario
    after logon on database
begin
    insert into hist_usuario
    values (ora_login_user, sysdate,
            'Conexão ao banco de dados ' || ora_database_name);
end;
/

Definição: Funções de atributo de evento

Disponíveis dentro de um trigger de sistema para obter informações sobre o evento que o disparou: ora_login_user (usuário que conectou), ora_database_name (nome do banco), ora_dict_obj_name/ora_dict_obj_type (nome/tipo do objeto afetado por um evento DDL), ora_sysevent (nome do evento que disparou o trigger) e ora_client_ip_address (IP do cliente, em conexões TCP/IP), entre outras.

Trigger de view (instead of)

Definição: instead of

Cláusula usada para criar um trigger de linha sobre uma view, no lugar de before/after. Necessária sempre que a view for composta por mais de uma tabela — nesse caso, o Oracle não sabe automaticamente como aplicar um insert/ update/delete na view às tabelas de origem, então o trigger instead of assume o controle e decide manualmente o que fazer em cada tabela.

create or replace trigger trg_emp_dept_v
    instead of insert or update or delete
    on emp_dept_v
    referencing new as new old as old
begin
    if inserting then
        -- decide manualmente em qual(is) tabela(s) de origem inserir,
        -- usando :new para os valores informados na view
        insert into dept (deptno, dname, loc)
        values (:new.deptno, :new.dname, null);
        insert into emp (empno, ename, job, sal, mgr, deptno)
        values (:new.empno, :new.ename, :new.job, :new.sal, :new.mgr, :new.deptno);
    elsif updating then
        -- lógica equivalente usando :new para atualizar as tabelas de origem
        null;
    elsif deleting then
        -- lógica equivalente usando :old, tipicamente validando integridade
        -- referencial antes de excluir (ex.: não excluir um depto que ainda
        -- tem funcionários vinculados)
        null;
    end if;
end;
/

Sem o trigger instead of, um insert into emp_dept_v (...) falharia — o Oracle não tem como saber, sozinho, quais colunas pertencem a emp e quais pertencem a dept.

PL/SQL Tables — estruturas homogêneas

Definição: PL/SQL Table

O nome que o PL/SQL dá a um vetor (array) — uma estrutura que guarda vários valores do mesmo tipo, mantida em memória (não fisicamente em disco, como uma tabela do banco), o que garante acesso mais rápido aos dados. Antes de usá-la, é preciso definir um type (na área declare de um bloco, procedure, function ou package) descrevendo a estrutura, e só então declarar uma variável baseada nesse type — o type em si não ocupa memória, só a variável declarada a partir dele.

declare
    type deptnotab is table of number index by binary_integer;
    type dnametab  is table of dept.dname%type index by binary_integer;
    wdeptnotab deptnotab;
    wdnametab  dnametab;
    idx binary_integer default 0;
begin
    for r1 in (select deptno, dname from dept) loop
        idx := idx + 1;
        wdeptnotab(idx) := r1.deptno;
        wdnametab(idx)  := r1.dname;
    end loop;
end;
/

table of tipo define que o type é uma PL/SQL Table de um determinado tipo de dado (um tipo escalar, como number, ou referenciado com %type/%rowtype); index by binary_integer define a forma de indexação usada para acessar os dados na memória. A posição usada entre parênteses (wdeptnotab(idx)) funciona como o índice do vetor.

Definição: PL/SQL Table a partir de %rowtype

Uma PL/SQL Table também pode ser definida a partir de tabela%rowtype ou cursor%rowtype — nesse caso, cada posição do vetor guarda uma linha inteira (todas as colunas), não um único valor escalar.

declare
    type deptab is table of dept%rowtype index by binary_integer;
    wdeptab deptab;
    idx binary_integer default 0;
begin
    for r1 in (select * from dept) loop
        idx := idx + 1;
        wdeptab(idx) := r1;  -- guarda a linha inteira nesta posição
    end loop;

    for i in 1..wdeptab.last loop
        dbms_output.put_line('Departamento: ' || wdeptab(i).deptno ||
                              ' - ' || wdeptab(i).dname);
    end loop;
end;
/

Definição: Métodos de navegação de uma PL/SQL Table

Método	Retorna
nome.first	A posição do primeiro elemento preenchido.
nome.last	A posição do último elemento preenchido.
nome.count	A quantidade de elementos preenchidos na tabela.
nome.next(posição)	A posição do próximo elemento preenchido, a partir da posição informada — usado para navegar com while, sem depender de um índice sequencial fixo.

declare
    n binary_integer;
begin
    n := wdeptab.first;
    while n <= wdeptab.last loop
        dbms_output.put_line('Departamento: ' || wdeptab(n).dname);
        n := wdeptab.next(n);
    end loop;
end;
/

PL/SQL Records (estruturas heterogêneas)

Definição: PL/SQL Record

Diferente de uma PL/SQL Table (estrutura homogênea, todos os elementos do mesmo tipo), um Record é uma estrutura heterogênea — permite agrupar campos de tipos de dado diferentes sob um único type, sem precisar criar uma tabela ou cursor associado. Funciona como uma linha de dados customizada, definida à mão.

declare
    type deprec is record (
        deptno number(2, 8),
        dname  varchar2(14),
        loc    varchar2(13)
    );
    wdeprec deprec;
begin
    wdeprec.deptno := 50;
    wdeprec.dname  := 'TI';
    wdeprec.loc    := 'BRASIL';
    dbms_output.put_line(wdeprec.deptno || ' - ' || wdeprec.dname ||
                          ' - ' || wdeprec.loc);
end;
/

Definição: Record guarda uma única linha — combine com Table para várias

Por si só, uma variável do tipo Record guarda apenas um registro por vez (diferente de uma PL/SQL Table, que guarda vários elementos indexados). Para guardar várias linhas, cada uma com a estrutura heterogênea de um Record, combine os dois recursos: declare uma PL/SQL Table cujo tipo de elemento é o próprio Record (table of deprec index by binary_integer) — cada posição da tabela passa a guardar um Record completo, com todos os seus campos.

declare
    type deprec is record (deptno number(2,8), dname varchar2(14), loc varchar2(13));
    type deptab is table of deprec index by binary_integer;
    wdeptab deptab;
    idx binary_integer default 0;
begin
    for r1 in (select * from dept) loop
        idx := idx + 1;
        wdeptab(idx) := r1;  -- cada posição guarda um Record inteiro
    end loop;

    for i in 1..wdeptab.last loop
        dbms_output.put_line('Departamento: ' || wdeptab(i).deptno ||
                              ' - ' || wdeptab(i).dname ||
                              ' - Local: ' || wdeptab(i).loc);
    end loop;
end;
/

Pacote utl_file

Definição: utl_file

Pacote nativo do Oracle para ler e gravar arquivos de texto no sistema operacional do servidor, diretamente a partir de blocos PL/SQL — usado com frequência para exportar dados para sistemas satélites, gerar relatórios em arquivo, ou importar dados de arquivos externos para dentro do banco.

Definição: Onde o utl_file pode ler/gravar

Duas formas de autorizar os diretórios do servidor que o utl_file pode acessar: o parâmetro de banco utl_file_dir (forma antiga — exige reiniciar o banco para alterar) e o objeto directory (forma atual, recomendada — não exige reinício). Um directory é criado apontando para um caminho físico do servidor, e depois tem acesso concedido a usuários específicos.

create directory dir_principal as 'C:\tmp\arquivos';
grant read, write on directory dir_principal to tsql;
-- conectado como usuário sys:
grant execute on utl_file to tsql;

Independente da forma escolhida, as permissões do sistema operacional sobre o diretório também precisam permitir o acesso — configurar só do lado do Oracle não basta.

Definição: Principais rotinas do utl_file

Rotina	O que faz
fopen(diretório, arquivo, modo)	Abre um arquivo, retornando um handle do tipo utl_file.file_type. modo é R (leitura), W (escrita — sobrescreve) ou A (append — adiciona ao final). Uma segunda assinatura aceita também max_linesize.
is_open(arquivo)	Verifica se um arquivo está aberto.
get_line(arquivo, buffer)	Lê uma linha do arquivo para dentro de buffer.
put(arquivo, buffer)	Grava uma string, sem quebra de linha ao final.
put_line(arquivo, buffer)	Grava uma string, com quebra de linha ao final.
putf(arquivo, formato, arg1..arg5)	Grava com substituição de argumentos no texto — inspirado no printf do C.
new_line(arquivo, linhas)	Grava uma (ou mais) quebra de linha.
fflush(arquivo)	Força a gravação imediata do buffer em disco.
fclose(arquivo) / fclose_all	Fecha um arquivo específico / todos os arquivos abertos.

Definição: Exceções do utl_file

As mais comuns: invalid_path (diretório inválido — conferir o directory ou utl_file_dir), invalid_mode (modo de abertura inválido), invalid_filehandle (uso de um handle que não representa um arquivo aberto), invalid_operation (ex.: tentar gravar num arquivo aberto só para leitura), write_error/ read_error (falha do sistema operacional ao gravar/ler) e internal_error (erro interno do próprio pacote). Além dessas, get_line também pode disparar a exceção predefinida no_data_found ao alcançar o final do arquivo.

Exemplo completo, exportando dados de duas tabelas para um arquivo separado por ponto e vírgula:

declare
    cursor c1 is
        select a.deptno, dname, empno, ename
        from dept a, emp b
        where a.deptno = b.deptno
        order by a.deptno;
    r1 c1%rowtype;
    meu_arquivo utl_file.file_type;
begin
    meu_arquivo := utl_file.fopen('DIR_PRINCIPAL', 'empregados.txt', 'w');
    open c1;
    loop
        fetch c1 into r1;
        exit when c1%notfound;
        utl_file.put_line(meu_arquivo, r1.deptno || ';' || r1.dname || ';' ||
                                        r1.empno || ';' || r1.ename);
    end loop;
    close c1;
    utl_file.fclose(meu_arquivo);
exception
    when utl_file.invalid_path then
        utl_file.fclose(meu_arquivo);
        dbms_output.put_line('Caminho ou nome do arquivo inválido');
    when utl_file.invalid_mode then
        utl_file.fclose(meu_arquivo);
        dbms_output.put_line('Modo de abertura inválido');
end;
/

E a leitura de volta, no mesmo formato:

declare
    meu_arquivo utl_file.file_type;
    linha varchar2(32000);
    wdeptno emp.deptno%type;
    wdname  dept.dname%type;
    wempno  emp.empno%type;
    wename  emp.ename%type;
begin
    meu_arquivo := utl_file.fopen('DIR_PRINCIPAL', 'empregados.txt', 'r');
    loop
        utl_file.get_line(meu_arquivo, linha);
        wdeptno := rtrim(substr(linha, 1, instr(linha, ';', 1, 1) - 1));
        -- (demais campos extraídos de forma semelhante, com substr/instr
        -- localizando cada ';' na linha)
        dbms_output.put_line('Depto: ' || wdeptno);
    end loop;
    utl_file.fclose(meu_arquivo);
exception
    when no_data_found then
        utl_file.fclose(meu_arquivo);
        dbms_output.put_line('Final do arquivo.');
end;
/

Definição: get_line não retorna null no fim do arquivo

Ao alcançar o final do arquivo, get_line dispara a exceção no_data_found — não retorna null/vazio. Por isso, o controle de fim de leitura deve ser feito tratando essa exceção (como no exemplo acima), e não checando se a variável ficou nula.

Definição: Separador delimitado x posição fixa

Um arquivo pode ser gerado separando os valores por um caractere delimitador (como ;, lido depois com substr/instr) ou usando posições fixas por coluna — gerado com rpad(valor, tamanho) para cada campo, garantindo que cada um sempre ocupe a mesma largura, e lido de volta com substr nas posições exatas conhecidas de antemão. A escolha não muda o uso do utl_file em si, só o formato de layout do arquivo.

SQL Dinâmico

Definição: SQL Dinâmico

Recurso que permite montar e executar um comando SQL (ou um bloco PL/SQL inteiro) a partir de uma string, em vez de um comando fixo escrito no código — a estrutura do comando (nome de tabela, colunas, cláusulas) pode ser definida em tempo de execução, não em tempo de compilação. Útil quando o comando precisa variar de forma que os parâmetros sozinhos não resolvem (ex.: o nome da tabela muda, não só o valor filtrado).

execute immediate

Definição: execute immediate

Comando usado para executar uma string contendo um comando SQL (insert, update, delete, create, alter, ou mesmo um select simples) ou um bloco PL/SQL. Parâmetros são passados com a cláusula using (na ordem em que aparecem como bind variables, com : no texto do comando) — por padrão como in, mas também aceita out/in out ao chamar procedures/functions dinamicamente.

declare
    wcomando varchar2(4000);
begin
    wcomando := 'insert into dept values (:deptno, :dname, :loc)';
    execute immediate wcomando using 99, 'RH', 'BRASIL';
end;
/

-- DDL também pode ser executado dinamicamente
execute immediate 'create table func (cd_func number, nm_func varchar2(50))';
execute immediate 'alter table func modify cd_func number not null';

Definição: returning into

Permite capturar, numa mesma chamada de execute immediate, valores resultantes de um insert/update/delete (ex.: uma coluna gerada automaticamente, ou o valor já atualizado) — sem precisar de um select separado depois.

declare
    watualiza_dept varchar2(2000);
    wdname dept.dname%type;
    wloc_re dept.loc%type;
begin
    watualiza_dept := 'update dept set loc = :1 where deptno = :2 ' ||
                       'returning dname, loc into :3, :4';
    execute immediate watualiza_dept using 'CHILE', 99
        returning into wdname, wloc_re;
end;
/

Definição: Chamando procedures/functions dinamicamente

Um bloco PL/SQL inteiro (não só um comando SQL solto) também pode ser montado como string e executado via execute immediate — incluindo a chamada a uma procedure existente, passando parâmetros in/out através da cláusula using.

declare
    winsere_dept varchar2(2000);
    wstatus varchar2(4000);
begin
    winsere_dept := 'begin cria_dept(:a, :b, :c, :d); end;';
    execute immediate winsere_dept
        using in 88, in 'RH', in 'ARGENTINA', in out wstatus;
    if wstatus = 'OK' then
        dbms_output.put_line('Departamento inserido com sucesso.');
    end if;
end;
/

ref cursor

Definição: ref cursor

Tipo de variável que referencia a estrutura de um comando select de forma dinâmica — diferente de um cursor comum, não está ligada a um único comando SQL fixo; a mesma variável ref cursor pode ser reaproveitada com comandos select diferentes ao longo do programa. Sua principal vantagem é apontar só para a estrutura/resultado do select, sem armazenar os dados em si — útil, por exemplo, para repassar um resultado de consulta entre programas ou sistemas diferentes.

declare
    type empcurtyp is ref cursor;
    emp_cv  empcurtyp;
    my_ename varchar2(15);
    my_sal   number default 1000;
begin
    open emp_cv for 'select ename, sal from emp where sal > :s' using my_sal;
    loop
        fetch emp_cv into my_ename, my_sal;
        exit when emp_cv%notfound;
        dbms_output.put_line('Empregado: ' || my_ename || ' Salário: ' || my_sal);
    end loop;
    close emp_cv;
end;
/

O comando por trás de um ref cursor também pode ser montado dinamicamente, concatenando um nome de tabela recebido por parâmetro — útil para criar uma procedure genérica, reaproveitável para várias tabelas:

create procedure print_table(tab_name varchar2) is
    type refcurtyp is ref cursor;
    cv refcurtyp;
    wdname dept.dname%type;
    wloc   dept.loc%type;
begin
    open cv for 'select dname, loc from ' || tab_name;
    loop
        fetch cv into wdname, wloc;
        exit when cv%notfound;
        dbms_output.put_line('Departamento: ' || wdname || ' Localização: ' || wloc);
    end loop;
    close cv;
end;
/

bulk collect

Definição: bulk collect

Cláusula que carrega todo o resultado de um select (dinâmico ou não) direto numa variável do tipo PL/SQL Table, sem precisar de um loop de fetch manual. Pode ser usada com select into, fetch into, returning into e execute immediate ... into. Funciona só com variáveis do tipo Table — não é permitido usá-la com uma variável do tipo Record diretamente.

declare
    type empcurtyp is ref cursor;
    type numlist   is table of number;
    type namelist  is table of varchar2(15);
    emp_cv empcurtyp;
    empnos numlist;
    enames namelist;
    sals   numlist;
begin
    open emp_cv for 'select empno, ename from emp';
    fetch emp_cv bulk collect into empnos, enames;
    close emp_cv;

    for r in 1..empnos.count loop
        dbms_output.put_line('Cód.: ' || empnos(r) || ' - Empregado: ' || enames(r));
    end loop;

    execute immediate 'select sal from emp' bulk collect into sals;
    for r in 1..sals.count loop
        dbms_output.put_line('Salário: ' || sals(r));
    end loop;
end;
/

Como último exemplo, uma function que retorna a quantidade de registros de uma tabela informada por parâmetro, calculada com SQL dinâmico:

create function row_count(tab_name varchar2) return integer is
    rows integer;
begin
    execute immediate 'select count(*) from ' || tab_name into rows;
    return rows;
end;
/