Sumário

1. Estrutura Básica de um Bloco PL/SQL

2. Variáveis e Constantes

3. Estruturas de Controle

4. Cursores

5. Procedures

6. Functions

7. Triggers

8. Boas Práticas em PL/SQL

9. Conclusão
Autor: @itsrafaelleal

1. Estrutura Básica de um Bloco PL/SQL

O PL/SQL (Procedural Language/Structured Query Language) é uma extensão

procedural da linguagem SQL desenvolvida pela Oracle. Ele permite combinar a
manipulação de dados do SQL com recursos de programação procedural, como
variáveis, estruturas de controle e tratamento de exceções. Um bloco PL/SQL é a
unidade fundamental de código e pode ser anônimo (executado uma única vez) ou
nomeado (como procedures, functions e packages).

Seções de um Bloco PL/SQL

Um bloco PL/SQL é composto por três seções principais, embora nem todas sejam
obrigatórias:

DECLARE (Opcional): Esta seção é utilizada para declarar variáveis, constantes,

cursores e exceções definidas pelo usuário. É onde você prepara os elementos
que serão utilizados na lógica do seu programa.

BEGIN (Obrigatória): Contém a lógica executável do bloco. É aqui que as

instruções SQL e PL/SQL são processadas. Todas as operações de manipulação
de dados, chamadas de procedures/functions e estruturas de controle são
escritas nesta seção.

EXCEPTION (Opcional): Esta seção é dedicada ao tratamento de erros. Se

ocorrer uma exceção (erro) durante a execução da seção BEGIN , o controle é
transferido para a seção EXCEPTION , onde você pode definir ações para lidar
com o erro de forma controlada, evitando que o programa aborte
inesperadamente.

Estrutura Geral

A sintaxe geral de um bloco PL/SQL é a seguinte:

 [DECLARE
-- Declaração de variáveis, constantes, cursores, etc.
]
BEGIN
-- Lógica executável (instruções SQL e PL/SQL)
-- ...
[EXCEPTION
-- Tratamento de exceções
-- WHEN excecao_especifica THEN
-- -- Ações para a exceção
-- WHEN OTHERS THEN
-- -- Ações para qualquer outra exceção
]
END;
/ -- Opcional, para executar o bloco no SQL*Plus ou SQL Developer

Exemplo Prático: Bloco PL/SQL Completo

Este exemplo demonstra um bloco PL/SQL anônimo que calcula o valor total de um
pedido, aplica um desconto e exibe o resultado. Ele inclui as seções DECLARE , BEGIN e
EXCEPTION .
 DECLARE
-- Declaração de variáveis para armazenar dados do pedido
v_id_pedido NUMBER := 101; -- ID do pedido
v_valor_unitario NUMBER := 50.00; -- Valor unitário do produto
v_quantidade NUMBER := 3; -- Quantidade de produtos
v_desconto_percentual NUMBER := 0.10; -- 10% de desconto
v_valor_total NUMBER; -- Variável para armazenar o valor total
calculado

BEGIN
-- Calcula o valor total antes do desconto
v_valor_total := v_valor_unitario * v_quantidade;

-- Aplica o desconto
v_valor_total := v_valor_total * (1 - v_desconto_percentual);

-- Exibe o valor total (saída para o console)

DBMS_OUTPUT.PUT_LINE(\'O valor total do pedido \' || v_id_pedido || \'
após o desconto é: \' || v_valor_total);

EXCEPTION
-- Tratamento de exceções
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(\'Erro: Tentativa de divisão por zero.\');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(\'Erro: Problema de conversão de dados ou valor
inválido.\');
WHEN OTHERS THEN
-- Captura qualquer outra exceção não tratada especificamente
DBMS_OUTPUT.PUT_LINE(\'Ocorreu um erro inesperado: \' || SQLERRM);
END;
/

Explicação do Exemplo:

Na seção DECLARE , definimos variáveis para o ID do pedido, valor unitário,

quantidade, percentual de desconto e o valor total. Note a inicialização de
algumas variáveis.

Na seção BEGIN , realizamos os cálculos para determinar o valor total do pedido

após o desconto. A função DBMS_OUTPUT.PUT_LINE é usada para exibir
mensagens no console (necessário habilitar SET SERVEROUTPUT ON no
SQL*Plus/SQL Developer).
 Na seção EXCEPTION , incluímos blocos WHEN para tratar exceções comuns como
ZERO_DIVIDE (divisão por zero) e VALUE_ERROR (erro de valor). O WHEN OTHERS é
um catch-all para qualquer outra exceção, exibindo a mensagem de erro padrão
do Oracle ( SQLERRM ).

2. Variáveis e Constantes

Em PL/SQL, variáveis e constantes são elementos fundamentais para armazenar

dados temporariamente durante a execução de um bloco de código. Elas permitem
que você manipule informações, realize cálculos e tome decisões de forma dinâmica.

Como Declarar Variáveis

A declaração de variáveis é feita na seção DECLARE de um bloco PL/SQL. É necessário

especificar o nome da variável, seu tipo de dado e, opcionalmente, um valor inicial.

Sintaxe:

nome_variavel TIPO_DE_DADO [:= valor_inicial];

nome_variavel : Um identificador único para a variável. Recomenda-se seguir

padrões de nomenclatura, como prefixar com v_ .

TIPO_DE_DADO : O tipo de dado que a variável irá armazenar (ex: NUMBER ,

VARCHAR2 , DATE , BOOLEAN ). PL/SQL suporta os tipos de dados SQL e alguns
tipos específicos de PL/SQL.

:= valor_inicial (Opcional): Atribui um valor inicial à variável no momento da

declaração. Se não for inicializada, a variável terá o valor NULL por padrão.

Exemplos de Declaração:
 DECLARE
v_nome_cliente VARCHAR2(100);
v_idade_cliente NUMBER(3) := 30;
v_data_cadastro DATE DEFAULT SYSDATE;
v_ativo BOOLEAN := TRUE;
v_salario NUMBER(10, 2);
v_descricao_produto [Link]%TYPE; -- Declaração baseada em
coluna de tabela
v_registro_cliente clientes%ROWTYPE; -- Declaração baseada em linha
de tabela
BEGIN
NULL; -- Bloco vazio para demonstração
END;
/

Atribuição de Valores

A atribuição de valores a variáveis é realizada utilizando o operador := (dois pontos

seguido de igual). Isso pode ser feito tanto na declaração quanto na seção BEGIN do
bloco.

Sintaxe:

nome_variavel := expressao_ou_valor;

Exemplo:

DECLARE
v_contador NUMBER := 0;
BEGIN
v_contador := v_contador + 1; -- Incrementa o valor da variável
DBMS_OUTPUT.PUT_LINE(\'Contador: \' || v_contador);

SELECT COUNT(*) INTO v_contador FROM dual; -- Atribui o resultado de uma

consulta SQL
DBMS_OUTPUT.PUT_LINE(\'Contador após SELECT: \' || v_contador);
END;
/
Uso de Constantes

Constantes são identificadores que armazenam um valor fixo que não pode ser
alterado durante a execução do bloco. Elas são declaradas na seção DECLARE e devem
ser inicializadas no momento da declaração, utilizando a palavra-chave CONSTANT .

Sintaxe:

nome_constante CONSTANT TIPO_DE_DADO [:= valor];

nome_constante : Um identificador único para a constante. Recomenda-se

prefixar com c_ .

TIPO_DE_DADO : O tipo de dado da constante.

:= valor : O valor fixo que a constante irá armazenar. É obrigatório.

Exemplo de Declaração de Constante:

DECLARE
c_PI CONSTANT NUMBER := 3.14159;
c_TAXA_JUROS CONSTANT NUMBER := 0.05;
c_NOME_EMPRESA CONSTANT VARCHAR2(50) := \'Minha Loja Online\';
BEGIN
NULL; -- Bloco vazio para demonstração
END;
/

Exemplo Prático: Utilizando Variáveis e Constantes

Este exemplo simula o cálculo do preço final de um produto em um e-commerce,

aplicando um imposto e um desconto promocional, ambos definidos como
constantes. O preço base é uma variável.
 DECLARE
-- Constantes para imposto e desconto
c_TAXA_IMPOSTO CONSTANT NUMBER := 0.18; -- 18% de imposto
c_DESCONTO_PROMO CONSTANT NUMBER := 0.05; -- 5% de desconto promocional

-- Variáveis para o produto

v_nome_produto VARCHAR2(100) := \'Smartphone X\';
v_preco_base NUMBER(10, 2) := 1200.00;
v_preco_com_imposto NUMBER(10, 2);
v_preco_final NUMBER(10, 2);
BEGIN
-- 1. Calcular preço com imposto
v_preco_com_imposto := v_preco_base * (1 + c_TAXA_IMPOSTO);

-- 2. Aplicar desconto promocional

v_preco_final := v_preco_com_imposto * (1 - c_DESCONTO_PROMO);

-- 3. Exibir os resultados
DBMS_OUTPUT.PUT_LINE(\'Produto: \' || v_nome_produto);
DBMS_OUTPUT.PUT_LINE(\'Preço Base: R$ \' || TO_CHAR(v_preco_base,
\'FM999G999D00\'));
DBMS_OUTPUT.PUT_LINE(\'Taxa de Imposto: \' || TO_CHAR(c_TAXA_IMPOSTO *
100, \'FM99D00\') || \'%\');
DBMS_OUTPUT.PUT_LINE(\'Preço com Imposto: R$ \' ||
TO_CHAR(v_preco_com_imposto, \'FM999G999D00\'));
DBMS_OUTPUT.PUT_LINE(\'Desconto Promocional: \' ||
TO_CHAR(c_DESCONTO_PROMO * 100, \'FM99D00\') || \'%\');
DBMS_OUTPUT.PUT_LINE(\'Preço Final: R$ \' || TO_CHAR(v_preco_final,
\'FM999G999D00\'));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(\'Ocorreu um erro no cálculo: \' || SQLERRM);
END;
/

Explicação do Exemplo:

Duas constantes, c_TAXA_IMPOSTO e c_DESCONTO_PROMO , são declaradas para

valores que não mudarão. Isso melhora a legibilidade e facilita a manutenção do
código.
 Variáveis como v_nome_produto , v_preco_base , v_preco_com_imposto e
v_preco_final são declaradas para armazenar os dados e resultados
intermediários.

Os cálculos são realizados na seção BEGIN , utilizando as variáveis e constantes.

A função TO_CHAR é usada para formatar a saída dos valores monetários e

percentuais, tornando-a mais amigável. FM remove espaços em branco à
esquerda, G para separador de milhar e D para separador decimal.

Um bloco EXCEPTION genérico ( WHEN OTHERS ) é incluído para capturar e

reportar quaisquer erros inesperados durante o processamento.

3. Estruturas de Controle

As estruturas de controle em PL/SQL permitem que você defina o fluxo de execução do

seu código, tomando decisões e repetindo blocos de instruções com base em
condições. Elas são essenciais para criar programas complexos e dinâmicos.

IF / ELSIF / ELSE

A estrutura IF é usada para executar um bloco de código condicionalmente. Ela

avalia uma condição booleana e executa as instruções correspondentes se a condição
for verdadeira.

Sintaxe:

IF condicao_1 THEN
-- Instruções se condicao_1 for verdadeira
ELSIF condicao_2 THEN -- Opcional, pode haver múltiplos ELSIF
-- Instruções se condicao_2 for verdadeira
ELSIF condicao_n THEN
-- Instruções se condicao_n for verdadeira
ELSE -- Opcional
-- Instruções se nenhuma das condições anteriores for verdadeira
END IF;
CASE

A estrutura CASE oferece uma alternativa mais limpa e legível para múltiplas
condições ELSIF , especialmente quando você está testando o mesmo valor contra
diferentes possibilidades. Existem duas formas de CASE : a expressão CASE (que
retorna um valor) e a instrução CASE (que executa um bloco de código).

Sintaxe (Instrução CASE):

CASE expressao_ou_variavel
WHEN valor_1 THEN
-- Instruções se expressao_ou_variavel = valor_1
WHEN valor_2 THEN
-- Instruções se expressao_ou_variavel = valor_2
-- ...
ELSE -- Opcional
-- Instruções se nenhum dos valores corresponder
END CASE;

-- Ou a forma pesquisada (sem expressao_ou_variavel inicial)

CASE
WHEN condicao_1 THEN
-- Instruções se condicao_1 for verdadeira
WHEN condicao_2 THEN
-- Instruções se condicao_2 for verdadeira
-- ...
ELSE -- Opcional
-- Instruções se nenhuma das condições for verdadeira
END CASE;

LOOP, WHILE e FOR

As estruturas de repetição (loops) permitem executar um bloco de código várias vezes.

PL/SQL oferece LOOP (loop básico), WHILE (loop condicional) e FOR (loop com
contador).

LOOP (Loop Básico)

O LOOP básico executa um bloco de código repetidamente até que uma condição
EXIT seja satisfeita. Se não houver uma condição EXIT , o loop será infinito.
Sintaxe:

LOOP
-- Instruções a serem repetidas
EXIT WHEN condicao_de_saida; -- Condição para sair do loop
END LOOP;

WHILE LOOP (Loop Condicional)

O WHILE LOOP executa um bloco de código enquanto uma condição específica for
verdadeira. A condição é avaliada no início de cada iteração.

Sintaxe:

WHILE condicao_permanencia LOOP

-- Instruções a serem repetidas enquanto a condição for verdadeira
END LOOP;

FOR LOOP (Loop com Contador)

O FOR LOOP é ideal para quando você sabe o número de iterações ou precisa iterar
sobre uma faixa de valores. Ele gerencia automaticamente um contador.

Sintaxe:

FOR contador IN [REVERSE] limite_inferior .. limite_superior LOOP

-- Instruções a serem repetidas
END LOOP;

REVERSE (Opcional): Faz com que o loop itere em ordem decrescente.

Exemplo Prático: Usando Estruturas de Decisão e Repetição

Este exemplo simula o processamento de pedidos em um e-commerce, aplicando

diferentes descontos com base no valor total do pedido ( IF/ELSIF/ELSE ),
classificando o status do pedido ( CASE ) e processando uma lista de itens ( FOR LOOP ).
DECLARE
v_id_pedido NUMBER := 205;
v_valor_total NUMBER := 750.00;
v_status_pedido VARCHAR2(20);
v_desconto_aplicado NUMBER(5,2);
TYPE t_itens_pedido IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_itens t_itens_pedido;
BEGIN
-- 1. Aplicar desconto com base no valor total do pedido (IF/ELSIF/ELSE)
IF v_valor_total >= 1000 THEN
v_desconto_aplicado := 0.15; -- 15% de desconto para pedidos grandes
ELSIF v_valor_total >= 500 THEN
v_desconto_aplicado := 0.10; -- 10% de desconto para pedidos médios
ELSE
v_desconto_aplicado := 0.05; -- 5% de desconto para pedidos pequenos
END IF;

v_valor_total := v_valor_total * (1 - v_desconto_aplicado);

DBMS_OUTPUT.PUT_LINE(
\'Pedido ID: \' || v_id_pedido ||
\', Valor com Desconto (\' || TO_CHAR(v_desconto_aplicado * 100) ||
\'%): R$ \' || TO_CHAR(v_valor_total, \'FM999G999D00\')
);

-- 2. Classificar o status do pedido (CASE Statement)

-- Supondo que v_status_pedido viria de uma consulta ou entrada
v_status_pedido := \'PROCESSANDO\'; -- Exemplo de status

CASE v_status_pedido
WHEN \'PENDENTE\' THEN
DBMS_OUTPUT.PUT_LINE(\'Ação: Aguardando pagamento.\');
WHEN \'PROCESSANDO\' THEN
DBMS_OUTPUT.PUT_LINE(\'Ação: Preparando envio.\');
WHEN \'ENVIADO\' THEN
DBMS_OUTPUT.PUT_LINE(\'Ação: Pedido em trânsito.\');
WHEN \'ENTREGUE\' THEN
DBMS_OUTPUT.PUT_LINE(\'Ação: Pedido concluído.\');
ELSE
DBMS_OUTPUT.PUT_LINE(\'Ação: Status desconhecido.\');
END CASE;

-- 3. Processar itens do pedido (FOR LOOP)

v_itens(1) := \'Laptop Gamer\';
v_itens(2) := \'Mouse Sem Fio\';
v_itens(3) := \'Teclado Mecânico\';
 DBMS_OUTPUT.PUT_LINE(\'Itens do Pedido:\');
FOR i IN v_itens.FIRST .. v_itens.LAST LOOP
DBMS_OUTPUT.PUT_LINE(\'-\' || v_itens(i));
END LOOP;

-- Exemplo de WHILE LOOP (simulando um contador de tentativas)

DECLARE
v_tentativas NUMBER := 0;
v_max_tentativas CONSTANT NUMBER := 3;
BEGIN
DBMS_OUTPUT.PUT_LINE(\'--- Simulação de Tentativas ---\');
WHILE v_tentativas < v_max_tentativas LOOP
v_tentativas := v_tentativas + 1;
DBMS_OUTPUT.PUT_LINE(\'Tentativa número: \' || v_tentativas);
-- Simular alguma operação que pode falhar
IF v_tentativas = v_max_tentativas THEN
DBMS_OUTPUT.PUT_LINE(\'Falha após \' || v_max_tentativas || \'
tentativas.\');
END IF;
END LOOP;
END;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(\'Ocorreu um erro: \' || SQLERRM);
END;
/

Explicação do Exemplo:

A estrutura IF/ELSIF/ELSE é utilizada para aplicar um desconto variável ao

v_valor_total do pedido, dependendo do seu montante.

A instrução CASE é empregada para exibir uma mensagem de ação com base no
v_status_pedido , demonstrando como lidar com múltiplas condições de forma
organizada.

Um FOR LOOP itera sobre uma coleção ( v_itens ) para listar os produtos de um
pedido. A coleção t_itens_pedido é um tipo TABLE OF que simula um array.

Um WHILE LOOP aninhado demonstra um cenário de repetição com base em

uma condição ( v_tentativas < v_max_tentativas ), útil para retentativas ou
processamento contínuo até uma condição ser satisfeita.
4. Cursores

Em PL/SQL, um cursor é uma área de memória privada no servidor Oracle que

armazena informações sobre uma instrução SQL (SELECT, INSERT, UPDATE, DELETE) e
o conjunto de linhas que ela afeta. Ele é essencial para processar múltiplas linhas
retornadas por uma consulta SELECT uma por uma.

O que são Cursores

Quando uma instrução SQL é executada, o Oracle aloca uma área de contexto na
memória para processar essa instrução. Para instruções SELECT que retornam
múltiplas linhas, o cursor permite que o programa PL/SQL itere sobre essas linhas
individualmente. Existem dois tipos principais de cursores:

Cursor Implícito: Criado e gerenciado automaticamente pelo Oracle para todas

as instruções SQL que não são SELECT que retornam múltiplas linhas (como
INSERT , UPDATE , DELETE , e SELECT INTO que retorna uma única linha). O
PL/SQL fornece atributos de cursor implícito ( SQL%ROWCOUNT , SQL%FOUND ,
SQL%NOTFOUND , SQL%ISOPEN ) para verificar o resultado da operação.

Cursor Explícito: Declarado e gerenciado manualmente pelo programador para

consultas SELECT que se espera que retornem múltiplas linhas. Ele oferece
maior controle sobre o processamento das linhas.

Cursor Implícito

O Oracle gerencia cursores implícitos para todas as instruções DML e para instruções
SELECT INTO que retornam uma única linha. Você pode usar os atributos SQL% para
verificar o status da operação.

Atributos de Cursor Implícito:

SQL%ROWCOUNT : Retorna o número de linhas afetadas pela última instrução SQL.

SQL%FOUND : Retorna TRUE se a última instrução SQL afetou uma ou mais linhas.

SQL%NOTFOUND : Retorna TRUE se a última instrução SQL não afetou nenhuma

linha.
 SQL%ISOPEN : Sempre retorna FALSE para cursores implícitos, pois o Oracle os
abre e fecha automaticamente.

Exemplo de Uso de Atributos Implícitos:

DECLARE
v_id_cliente NUMBER := 10;
BEGIN
UPDATE clientes
SET email = \'[Link]@[Link]\'
WHERE id_cliente = v_id_cliente;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(\'Cliente \' || v_id_cliente || \' atualizado com
sucesso. Linhas afetadas: \' || SQL%ROWCOUNT);
ELSE
DBMS_OUTPUT.PUT_LINE(\'Cliente \' || v_id_cliente || \' não encontrado
para atualização.\');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(\'Erro ao atualizar cliente: \' || SQLERRM);
END;
/

Cursor Explícito

Para processar múltiplas linhas retornadas por uma consulta SELECT , você deve
declarar e gerenciar um cursor explícito. O ciclo de vida de um cursor explícito envolve
quatro etapas:

1. DECLARAR: Definir o cursor na seção DECLARE com uma instrução SELECT .

2. ABRIR (OPEN): Executar a instrução SELECT associada ao cursor e popular a

área de contexto com as linhas resultantes.

3. LER (FETCH): Recuperar uma linha por vez do cursor para variáveis PL/SQL.

4. FECHAR (CLOSE): Liberar os recursos associados ao cursor.

Sintaxe Geral do Ciclo de Vida:

 DECLARE
CURSOR nome_cursor IS
SELECT coluna1, coluna2 FROM tabela WHERE condicao;
variavel1 tipo_coluna1;
variavel2 tipo_coluna2;
BEGIN
OPEN nome_cursor;
LOOP
FETCH nome_cursor INTO variavel1, variavel2;
EXIT WHEN nome_cursor%NOTFOUND;
-- Processar a linha recuperada
END LOOP;
CLOSE nome_cursor;
END;
/

Atributos de Cursor Explícito:

Assim como os implícitos, os cursores explícitos também possuem atributos:

%ROWCOUNT : Número de linhas lidas até o momento pelo cursor.

%FOUND : TRUE se a última operação FETCH retornou uma linha.

%NOTFOUND : TRUE se a última operação FETCH não retornou uma linha.

%ISOPEN : TRUE se o cursor está aberto.

Exemplo Prático Completo com Cursor Explícito

Este exemplo demonstra como usar um cursor explícito para listar todos os produtos
de uma determinada categoria em um e-commerce e exibir suas informações.
DECLARE
-- 1. Declarar o cursor para selecionar produtos de uma categoria
CURSOR c_produtos_categoria (p_categoria_id NUMBER) IS
SELECT id_produto, nome_produto, preco
FROM produtos
WHERE id_categoria = p_categoria_id
ORDER BY nome_produto;

-- Variáveis para armazenar os dados recuperados do cursor

v_id_produto produtos.id_produto%TYPE;
v_nome_produto produtos.nome_produto%TYPE;
v_preco [Link]%TYPE;

-- Variável para a categoria que queremos buscar

v_categoria_buscada NUMBER := 1; -- Ex: Categoria \'Eletrônicos\'

BEGIN
DBMS_OUTPUT.PUT_LINE(
\'--- Listando Produtos da Categoria ID: \' || v_categoria_buscada || \'
---\'
);

-- 2. Abrir o cursor, passando o parâmetro da categoria

OPEN c_produtos_categoria(v_categoria_buscada);

LOOP
-- 3. Ler uma linha do cursor para as variáveis
FETCH c_produtos_categoria INTO v_id_produto, v_nome_produto, v_preco;

-- Sair do loop quando não houver mais linhas

EXIT WHEN c_produtos_categoria%NOTFOUND;

-- Processar a linha: exibir as informações do produto

DBMS_OUTPUT.PUT_LINE(
\'ID: \' || v_id_produto ||
\', Nome: \' || v_nome_produto ||
\', Preço: R$ \' || TO_CHAR(v_preco, \'FM999G999D00\')
);
END LOOP;

-- 4. Fechar o cursor
CLOSE c_produtos_categoria;

-- Verificar se alguma linha foi encontrada

IF c_produtos_categoria%ROWCOUNT = 0 THEN
 DBMS_OUTPUT.PUT_LINE(\'Nenhum produto encontrado para a categoria \' ||
v_categoria_buscada || \'.\');
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(\'Ocorreu um erro ao processar os produtos: \' ||
SQLERRM);
IF c_produtos_categoria%ISOPEN THEN
CLOSE c_produtos_categoria; -- Garante que o cursor seja fechado em
caso de erro
END IF;
END;
/

Explicação do Exemplo:

O cursor c_produtos_categoria é declarado com um parâmetro

p_categoria_id , permitindo que a consulta seja dinâmica.

As variáveis v_id_produto , v_nome_produto e v_preco são declaradas com

%TYPE para herdar o tipo de dado das colunas correspondentes da tabela
produtos , garantindo compatibilidade e facilitando a manutenção.

O cursor é OPEN com o v_categoria_buscada como parâmetro.

Um LOOP é usado para iterar sobre as linhas. A cada iteração, FETCH recupera
uma linha para as variáveis. EXIT WHEN c_produtos_categoria%NOTFOUND
garante que o loop termine quando todas as linhas forem processadas.

As informações de cada produto são exibidas usando DBMS_OUTPUT.PUT_LINE .

Após o loop, o cursor é CLOSE para liberar os recursos.

O atributo c_produtos_categoria%ROWCOUNT é usado para verificar se alguma

linha foi encontrada. O bloco EXCEPTION garante que o cursor seja fechado
mesmo em caso de erro, prevenindo vazamento de recursos.

5. Procedures

Em PL/SQL, uma procedure é um bloco de código nomeado e armazenado no banco

de dados que executa uma ação específica. Diferente de uma função, uma procedure
não precisa retornar um valor, embora possa retornar múltiplos valores através de
parâmetros de saída. Procedures são ideais para encapsular lógica de negócios
complexa, realizar operações DML (INSERT, UPDATE, DELETE) e automatizar tarefas.

Conceito de Procedure

Procedures são subprogramas que podem ser chamados por outros blocos PL/SQL,
aplicações externas ou até mesmo por outras procedures e functions. Elas promovem
a modularidade, reusabilidade e manutenção do código, permitindo que você divida
grandes problemas em partes menores e mais gerenciáveis.

Sintaxe Completa Explicada ( CREATE OR REPLACE PROCEDURE... )

A sintaxe básica para criar ou substituir uma procedure é a seguinte:

CREATE [OR REPLACE] PROCEDURE nome_procedure

[(parametro1 [IN | OUT | IN OUT] tipo_dado_parametro1,
parametro2 [IN | OUT | IN OUT] tipo_dado_parametro2,
...)]
[AUTHID CURRENT_USER | DEFINER] -- Opcional: define o contexto de
privilégios
IS | AS
-- Declaração de variáveis locais, constantes, cursores, etc.
BEGIN
-- Lógica executável da procedure
-- Instruções SQL e PL/SQL
EXCEPTION
-- Tratamento de exceções
END nome_procedure;
/

CREATE OR REPLACE PROCEDURE : Cria uma nova procedure ou substitui uma

existente com o mesmo nome. OR REPLACE é útil durante o desenvolvimento
para evitar a necessidade de dropar a procedure manualmente.

nome_procedure : O nome único da procedure.

parametroX : Nome do parâmetro. Pode ser seguido por IN , OUT ou IN OUT

para definir seu modo.

tipo_dado_parametroX : O tipo de dado do parâmetro (ex: NUMBER , VARCHAR2 ,

DATE ).
 AUTHID CURRENT_USER | DEFINER : Define se a procedure será executada com os
privilégios do usuário que a invoca ( CURRENT_USER ) ou do usuário que a criou
( DEFINER ). DEFINER é o padrão.

IS | AS : Palavras-chave que marcam o início da seção de declaração do corpo

da procedure.

DECLARE , BEGIN , EXCEPTION , END : Seções padrão de um bloco PL/SQL, como

visto anteriormente.

Parâmetros IN, OUT e IN OUT

Os parâmetros permitem que você passe dados para a procedure e receba dados dela.
Existem três modos de parâmetro:

IN (Padrão): O parâmetro é usado para passar um valor de entrada para a

procedure. O valor do parâmetro IN não pode ser alterado dentro da procedure.
Se nenhum modo for especificado, IN é o padrão.

OUT: O parâmetro é usado para retornar um valor da procedure para o programa

chamador. O valor do parâmetro OUT é inicializado como NULL dentro da
procedure e qualquer valor atribuído a ele será retornado ao chamador. Não
pode ser usado em expressões ou condições dentro da procedure.

IN OUT: O parâmetro é usado para passar um valor de entrada para a procedure

e também para retornar um valor modificado para o programa chamador. O valor
inicial do parâmetro IN OUT é o valor passado pelo chamador, e ele pode ser
lido e modificado dentro da procedure.

Exemplo Prático de Criação e Execução de uma Procedure

Este exemplo cria uma procedure que atualiza o status de um pedido em um sistema
de e-commerce e retorna uma mensagem de sucesso ou erro. Ela demonstra o uso de
parâmetros IN e OUT .
-- Supondo a existência de uma tabela PEDIDOS com colunas ID_PEDIDO, STATUS,
DATA_ATUALIZACAO
-- CREATE TABLE PEDIDOS (
-- ID_PEDIDO NUMBER PRIMARY KEY,
-- STATUS VARCHAR2(50),
-- DATA_ATUALIZACAO DATE
-- );
-- INSERT INTO PEDIDOS VALUES (101, \'PENDENTE\', SYSDATE);
-- COMMIT;

CREATE OR REPLACE PROCEDURE prc_atualizar_status_pedido

(p_id_pedido IN NUMBER,
p_novo_status IN VARCHAR2,
p_mensagem_saida OUT VARCHAR2)
IS
v_count_pedidos NUMBER;
BEGIN
-- Verifica se o pedido existe
SELECT COUNT(*)
INTO v_count_pedidos
FROM pedidos
WHERE id_pedido = p_id_pedido;

IF v_count_pedidos = 0 THEN
p_mensagem_saida := \'Erro: Pedido \' || p_id_pedido || \' não
encontrado.\';
ELSE
-- Atualiza o status do pedido
UPDATE pedidos
SET status = p_novo_status,
data_atualizacao = SYSDATE
WHERE id_pedido = p_id_pedido;

-- Verifica se a atualização foi bem-sucedida

IF SQL%ROWCOUNT > 0 THEN
COMMIT; -- Confirma a transação
p_mensagem_saida := \'Sucesso: Status do pedido \' || p_id_pedido ||
\' atualizado para \' || p_novo_status || \'.\';
ELSE
ROLLBACK; -- Desfaz a transação em caso de falha
p_mensagem_saida := \'Erro: Falha ao atualizar o status do pedido \'
|| p_id_pedido || \'.\';
END IF;
END IF;
 EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Garante o rollback em caso de qualquer erro inesperado
p_mensagem_saida := \'Erro inesperado ao atualizar pedido: \' ||
SQLERRM;
END prc_atualizar_status_pedido;
/

-- Exemplo de Execução da Procedure

DECLARE
v_feedback VARCHAR2(200);
BEGIN
-- Chamada 1: Atualiza um pedido existente
prc_atualizar_status_pedido(p_id_pedido => 101, p_novo_status =>
\'PROCESSANDO\', p_mensagem_saida => v_feedback);
DBMS_OUTPUT.PUT_LINE(v_feedback);

-- Chamada 2: Tenta atualizar um pedido que não existe

prc_atualizar_status_pedido(p_id_pedido => 999, p_novo_status =>
\'CANCELADO\', p_mensagem_saida => v_feedback);
DBMS_OUTPUT.PUT_LINE(v_feedback);

-- Chamada 3: Exemplo com parâmetro IN OUT (se a procedure tivesse um)

-- Supondo uma procedure: prc_processar_valor(p_valor IN OUT NUMBER)
-- DECLARE
-- v_meu_valor NUMBER := 100;
-- BEGIN
-- prc_processar_valor(v_meu_valor);
-- DBMS_OUTPUT.PUT_LINE(\'Valor processado: \' || v_meu_valor);
-- END;
-- /
END;
/

Explicação do Exemplo:

A procedure prc_atualizar_status_pedido recebe p_id_pedido e

p_novo_status como parâmetros IN (entrada) e p_mensagem_saida como
parâmetro OUT (saída).

Dentro da procedure, primeiro verifica-se a existência do pedido. Se o pedido

não for encontrado, uma mensagem de erro é atribuída a p_mensagem_saida .

Se o pedido existe, a instrução UPDATE é executada para modificar o status e a

data de atualização. SQL%ROWCOUNT é usado para verificar se a atualização afetou
 alguma linha.

COMMIT é usado para persistir as alterações no banco de dados em caso de

sucesso, e ROLLBACK para desfazer as alterações em caso de falha ou exceção.

A seção EXCEPTION captura erros inesperados e atribui uma mensagem de erro a

p_mensagem_saida .

No bloco de execução, a procedure é chamada duas vezes com diferentes

p_id_pedido para demonstrar os cenários de sucesso e falha. O valor retornado
no parâmetro OUT ( v_feedback ) é então exibido.

6. Functions

Em PL/SQL, uma function (função) é um tipo de subprograma nomeado e

armazenado no banco de dados, semelhante a uma procedure, mas com uma
diferença fundamental: uma function sempre retorna um único valor. Functions são
ideais para realizar cálculos, manipular dados e retornar um resultado que pode ser
usado em expressões SQL ou em outros blocos PL/SQL.

Conceito de Function

Functions são blocos de código reutilizáveis que encapsulam uma lógica específica e,
ao final de sua execução, produzem um resultado. Elas são frequentemente utilizadas
para computar valores, formatar dados ou realizar validações que precisam ser
integradas diretamente em consultas SQL ou em outras operações que esperam um
valor de retorno.

Diferença entre Function e Procedure

A principal distinção entre functions e procedures reside em seu propósito e

comportamento:
 Característica Function Procedure

Não retorna valor

Retorno de
Sempre retorna um único valor. diretamente (pode usar OUT
Valor
parâmetros).

Pode ser usada diretamente em instruções Não pode ser usada

Uso em SQL SQL ( SELECT , WHERE , HAVING , ORDER diretamente em instruções
BY ). SQL.

Propósito Executar uma ação ou

Calcular e retornar um valor.
Principal conjunto de ações.

Pode ter parâmetros IN ,

Parâmetros Pode ter parâmetros IN .
OUT , IN OUT .

Geralmente não realiza COMMIT ou

Pode realizar COMMIT ou
Transações ROLLBACK (deve ser transacionalmente
ROLLBACK .
transparente).

Sintaxe Completa Explicada

A sintaxe para criar ou substituir uma function é similar à de uma procedure, mas
inclui a cláusula RETURN para especificar o tipo de dado do valor que será retornado.
 CREATE [OR REPLACE] FUNCTION nome_function
[(parametro1 [IN] tipo_dado_parametro1,
parametro2 [IN] tipo_dado_parametro2,
...)]
RETURN tipo_de_retorno -- Obrigatório: tipo de dado do valor retornado
[AUTHID CURRENT_USER | DEFINER] -- Opcional: define o contexto de
privilégios
IS | AS
-- Declaração de variáveis locais, constantes, cursores, etc.
BEGIN
-- Lógica executável da function
-- Instruções SQL e PL/SQL
-- ...
RETURN valor_a_retornar; -- Obrigatório: retorna o valor
EXCEPTION
-- Tratamento de exceções
END nome_function;
/

RETURN tipo_de_retorno : Especifica o tipo de dado do valor que a função irá

retornar. Este é um elemento chave que diferencia uma função de uma
procedure.

RETURN valor_a_retornar : Dentro do bloco BEGIN , a instrução RETURN é

usada para sair da função e passar o valor especificado de volta ao chamador.

Uso de RETURN

A instrução RETURN é fundamental em funções PL/SQL. Ela não apenas especifica o

valor que a função irá produzir, mas também encerra a execução da função. Uma
função pode ter múltiplas instruções RETURN , mas apenas uma será executada por
chamada.

Exemplo:
 CREATE OR REPLACE FUNCTION fn_calcular_bonus
(p_salario IN NUMBER,
p_anos_servico IN NUMBER)
RETURN NUMBER
IS
v_bonus NUMBER;
BEGIN
IF p_anos_servico >= 5 THEN
v_bonus := p_salario * 0.10; -- 10% de bônus
ELSE
v_bonus := p_salario * 0.05; -- 5% de bônus
END IF;
RETURN v_bonus;
END fn_calcular_bonus;
/

Exemplo Prático de Function Sendo Utilizada em uma Consulta SQL

Este exemplo cria uma função que calcula o preço final de um produto em um e-
commerce, aplicando um desconto baseado em uma categoria específica. A função
será então utilizada diretamente em uma consulta SELECT .
-- Supondo a existência de uma tabela PRODUTOS com colunas ID_PRODUTO,
NOME_PRODUTO, PRECO, ID_CATEGORIA
-- CREATE TABLE PRODUTOS (
-- ID_PRODUTO NUMBER PRIMARY KEY,
-- NOME_PRODUTO VARCHAR2(100),
-- PRECO NUMBER(10, 2),
-- ID_CATEGORIA NUMBER
-- );
-- INSERT INTO PRODUTOS VALUES (1, \'Laptop Ultra\', 2500.00, 10);
-- INSERT INTO PRODUTOS VALUES (2, \'Fone Bluetooth\', 300.00, 20);
-- INSERT INTO PRODUTOS VALUES (3, \'Monitor 4K\', 1500.00, 10);
-- INSERT INTO PRODUTOS VALUES (4, \'Mouse Gamer\', 120.00, 20);
-- COMMIT;

CREATE OR REPLACE FUNCTION fn_calcular_preco_final

(p_preco_base IN NUMBER,
p_id_categoria IN NUMBER)
RETURN NUMBER
IS
v_desconto NUMBER := 0;
v_preco_final NUMBER;
BEGIN
-- Aplica um desconto maior para produtos da categoria \'Eletrônicos\' (ID
10)
IF p_id_categoria = 10 THEN
v_desconto := 0.15; -- 15% de desconto
ELSIF p_id_categoria = 20 THEN
v_desconto := 0.05; -- 5% de desconto para \'Acessórios\'
END IF;

v_preco_final := p_preco_base * (1 - v_desconto);

RETURN v_preco_final;
EXCEPTION
WHEN OTHERS THEN
-- Em caso de erro, retorna o preço base sem desconto
RETURN p_preco_base;
END fn_calcular_preco_final;
/

-- Exemplo de Uso da Function em uma Consulta SQL

SELECT
p.id_produto,
p.nome_produto,
[Link] AS preco_original,
 fn_calcular_preco_final([Link], p.id_categoria) AS preco_com_desconto
FROM
produtos p
WHERE
p.id_categoria IN (10, 20);

-- Exemplo de Uso da Function em um Bloco PL/SQL

DECLARE
v_preco_produto NUMBER := 500.00;
v_categoria_produto NUMBER := 10;
v_preco_final_calculado NUMBER;
BEGIN
v_preco_final_calculado := fn_calcular_preco_final(v_preco_produto,
v_categoria_produto);
DBMS_OUTPUT.PUT_LINE(
\'Preço original: R$ \' || TO_CHAR(v_preco_produto, \'FM999G999D00\') ||
\', Categoria: \' || v_categoria_produto ||
\', Preço Final: R$ \' || TO_CHAR(v_preco_final_calculado,
\'FM999G999D00\')
);
END;
/

Explicação do Exemplo:

A função fn_calcular_preco_final recebe o preço base e o ID da categoria de

um produto como parâmetros IN e retorna um NUMBER (o preço final).

Dentro da função, um desconto é aplicado com base no p_id_categoria

usando uma estrutura IF/ELSIF .

A instrução RETURN v_preco_final; envia o valor calculado de volta ao

chamador.

No exemplo de uso em SQL, a função é chamada diretamente na cláusula

SELECT para calcular o preco_com_desconto para cada produto, demonstrando
como funções podem ser integradas perfeitamente em consultas.

Um segundo exemplo mostra como a função pode ser chamada dentro de um

bloco PL/SQL, atribuindo seu resultado a uma variável.
7. Triggers

Em PL/SQL, uma trigger (gatilho) é um bloco de código PL/SQL ou uma instrução SQL
que é executado automaticamente em resposta a um evento específico no banco de
dados. Esses eventos podem ser operações DML (INSERT, UPDATE, DELETE) em
tabelas, operações DDL (CREATE, ALTER, DROP) em objetos do esquema, ou eventos
de banco de dados (LOGON, LOGOFF, STARTUP, SHUTDOWN).

O que são Triggers

Triggers são mecanismos poderosos para impor regras de negócio, manter a

integridade referencial complexa, auditar alterações de dados, replicar dados e
automatizar tarefas. Elas são armazenadas no banco de dados e são acionadas de
forma transparente para o usuário ou aplicação que causou o evento.

Tipos de Triggers

As triggers podem ser classificadas de diversas maneiras:

Timing (Momento de Disparo):

BEFORE : A trigger é disparada antes que a instrução SQL que a acionou seja
executada. Útil para validações, modificação de dados antes da operação,
ou para gerar valores (ex: IDs sequenciais).

AFTER : A trigger é disparada depois que a instrução SQL que a acionou é

executada. Útil para auditoria, manutenção de logs, ou para realizar ações
em outras tabelas com base nos dados já modificados.

Evento (Tipo de Operação):

INSERT : Disparada quando novas linhas são inseridas na tabela.

UPDATE : Disparada quando linhas existentes são modificadas na tabela.

Pode ser especificado para colunas específicas (ex: ON UPDATE OF
coluna1, coluna2 ).

DELETE : Disparada quando linhas são removidas da tabela.

Nível de Granularidade:
 FOR EACH ROW (Nível de Linha): A trigger é disparada uma vez para cada
linha afetada pela instrução SQL. É o tipo mais comum para validações e
auditorias detalhadas. Dentro de triggers de nível de linha, você pode usar
as pseudocolunas :OLD e :NEW para acessar os valores das colunas antes e
depois da operação, respectivamente.

FOR EACH STATEMENT (Nível de Instrução): A trigger é disparada uma única

vez para a instrução SQL, independentemente do número de linhas
afetadas. Útil para validações gerais ou para registrar o início/fim de uma
operação em massa.

Quando Utilizar Triggers

Triggers são úteis para:

Auditoria: Registrar quem, quando e o que foi alterado em uma tabela.

Integridade de Dados: Impor regras de negócio complexas que não podem ser
expressas por constraints padrão (CHECK, FOREIGN KEY).

Geração de Valores: Gerar automaticamente valores para colunas (ex: carimbos

de data/hora, IDs sequenciais).

Replicação de Dados: Manter tabelas espelho ou sincronizar dados entre

sistemas.

Prevenção de Operações: Impedir certas operações DML ou DDL com base em

condições específicas.

Sintaxe Completa Explicada

A sintaxe para criar uma trigger é a seguinte:

CREATE [OR REPLACE] TRIGGER nome_trigger
{BEFORE | AFTER} {INSERT | UPDATE [OF coluna1, coluna2, ...] | DELETE} --
Timing e Evento
ON nome_tabela
[FOR EACH ROW] -- Nível de Granularidade (opcional, se omitido é FOR EACH
STATEMENT)
[WHEN (condicao)] -- Opcional: condição adicional para o disparo da trigger
DECLARE
-- Declarações locais (variáveis, constantes)
BEGIN
-- Lógica PL/SQL da trigger
-- Para triggers FOR EACH ROW, pode-se usar :[Link] e :[Link]
EXCEPTION
-- Tratamento de exceções
END;
/

CREATE [OR REPLACE] TRIGGER : Cria ou substitui uma trigger.

nome_trigger : Nome único da trigger.

BEFORE | AFTER : Define o momento de disparo.

INSERT | UPDATE | DELETE : Define o evento que aciona a trigger. Para UPDATE ,
pode-se especificar as colunas que, se atualizadas, disparam a trigger.

ON nome_tabela : A tabela na qual a trigger será criada.

FOR EACH ROW : Especifica que a trigger é de nível de linha. Se omitido, é de nível
de instrução.

WHEN (condicao) : Uma condição booleana que deve ser verdadeira para que a
trigger seja disparada. Esta condição é avaliada antes da execução do corpo da
trigger.

:[Link] e :[Link] : Pseudocolunas disponíveis apenas em triggers

FOR EACH ROW . :OLD refere-se ao valor da coluna antes da operação DML, e
:NEW refere-se ao valor após a operação DML (ou o valor que será
inserido/atualizado).
Exemplo Prático de Trigger em uma Tabela

Este exemplo cria uma trigger BEFORE INSERT OR UPDATE FOR EACH ROW na tabela
PRODUTOS de um e-commerce. A trigger garante que o preço de um produto nunca
seja negativo e que a data de última atualização seja sempre registrada
automaticamente.
-- Supondo a existência da tabela PRODUTOS com colunas ID_PRODUTO,
NOME_PRODUTO, PRECO, DATA_CRIACAO, DATA_ULTIMA_ATUALIZACAO
-- CREATE TABLE PRODUTOS (
-- ID_PRODUTO NUMBER PRIMARY KEY,
-- NOME_PRODUTO VARCHAR2(100) NOT NULL,
-- PRECO NUMBER(10, 2) NOT NULL,
-- DATA_CRIACAO DATE DEFAULT SYSDATE,
-- DATA_ULTIMA_ATUALIZACAO DATE
-- );

CREATE OR REPLACE TRIGGER trg_produtos_manutencao

BEFORE INSERT OR UPDATE ON PRODUTOS
FOR EACH ROW
BEGIN
-- 1. Garantir que o preço não seja negativo
IF :[Link] < 0 THEN
:[Link] := 0; -- Define o preço como zero se for negativo
-- Alternativamente, poderia-se levantar uma exceção:
-- RAISE_APPLICATION_ERROR(-20001, \'O preço do produto não pode ser
negativo.\');
END IF;

-- 2. Registrar a data da última atualização/criação

IF INSERTING THEN -- Se for uma operação de INSERT
:NEW.DATA_CRIACAO := SYSDATE;
:NEW.DATA_ULTIMA_ATUALIZACAO := SYSDATE;
ELSIF UPDATING THEN -- Se for uma operação de UPDATE
:NEW.DATA_ULTIMA_ATUALIZACAO := SYSDATE;
END IF;

-- Pode-se também verificar se uma coluna específica foi atualizada

-- IF UPDATING (\'PRECO\') THEN
-- DBMS_OUTPUT.PUT_LINE(\'Preço do produto \' || :NEW.NOME_PRODUTO || \'
foi atualizado.\');
-- END IF;

EXCEPTION
WHEN OTHERS THEN
-- Logar o erro ou levantar uma exceção mais específica
RAISE_APPLICATION_ERROR(-20002, \'Erro na trigger
trg_produtos_manutencao: \' || SQLERRM);
END;
/

-- Testando a Trigger
 -- Inserção de um novo produto (DATA_CRIACAO e DATA_ULTIMA_ATUALIZACAO serão
preenchidas)
INSERT INTO PRODUTOS (ID_PRODUTO, NOME_PRODUTO, PRECO) VALUES (101, \'Fone
Bluetooth Premium\', 150.00);
COMMIT;
SELECT ID_PRODUTO, NOME_PRODUTO, PRECO, DATA_CRIACAO,
DATA_ULTIMA_ATUALIZACAO FROM PRODUTOS WHERE ID_PRODUTO = 101;

-- Inserção com preço negativo (trigger irá corrigir para 0)

INSERT INTO PRODUTOS (ID_PRODUTO, NOME_PRODUTO, PRECO) VALUES (102, \'Cabo
USB-C\', -10.00);
COMMIT;
SELECT ID_PRODUTO, NOME_PRODUTO, PRECO, DATA_CRIACAO,
DATA_ULTIMA_ATUALIZACAO FROM PRODUTOS WHERE ID_PRODUTO = 102;

-- Atualização de um produto (DATA_ULTIMA_ATUALIZACAO será atualizada)

UPDATE PRODUTOS SET PRECO = 160.00 WHERE ID_PRODUTO = 101;
COMMIT;
SELECT ID_PRODUTO, NOME_PRODUTO, PRECO, DATA_CRIACAO,
DATA_ULTIMA_ATUALIZACAO FROM PRODUTOS WHERE ID_PRODUTO = 101;

-- Atualização com preço negativo (trigger irá corrigir para 0)

UPDATE PRODUTOS SET PRECO = -5.00 WHERE ID_PRODUTO = 101;
COMMIT;
SELECT ID_PRODUTO, NOME_PRODUTO, PRECO, DATA_CRIACAO,
DATA_ULTIMA_ATUALIZACAO FROM PRODUTOS WHERE ID_PRODUTO = 101;

-- Limpeza (opcional)
-- DELETE FROM PRODUTOS WHERE ID_PRODUTO IN (101, 102);
-- COMMIT;

Explicação do Exemplo:

A trigger trg_produtos_manutencao é definida para ser disparada BEFORE

INSERT OR UPDATE na tabela PRODUTOS e FOR EACH ROW (para cada linha
afetada).

Dentro do bloco BEGIN , a condição IF :[Link] < 0 THEN :[Link] :=

0; END IF; verifica se o novo preço ( :[Link] ) é negativo. Se for, ele é
ajustado para 0 antes que a operação DML seja efetivada no banco de dados.

As condições INSERTING e UPDATING são pseudocolunas booleanas que

indicam o tipo de operação DML que acionou a trigger. Elas são usadas para
 preencher automaticamente as colunas DATA_CRIACAO e
DATA_ULTIMA_ATUALIZACAO com a data e hora atuais ( SYSDATE ).

A seção EXCEPTION inclui um RAISE_APPLICATION_ERROR para reportar erros da

trigger de forma controlada, com um código de erro personalizado ( -20002 ) e a
mensagem de erro original ( SQLERRM ).

Os testes demonstram como a trigger atua na correção de preços negativos e na

atualização automática das datas de criação e modificação, garantindo a
integridade dos dados e a automação de tarefas.

8. Boas Práticas em PL/SQL

Adotar boas práticas de programação em PL/SQL é crucial para desenvolver código

robusto, legível, de fácil manutenção e com bom desempenho. Ignorar esses
princípios pode levar a sistemas difíceis de depurar, com erros ocultos e caros de
manter.

Padrões de Nomenclatura

Consistência na nomenclatura torna o código mais compreensível e padronizado.

Algumas convenções comuns incluem:

Tabelas: Nomes no plural, maiúsculas, com underscore para separar palavras

(ex: PRODUTOS , PEDIDOS_ITENS ).

Colunas: Nomes descritivos, maiúsculas, com underscore (ex: ID_PRODUTO ,

DATA_CADASTRO ).

Variáveis: Prefixo v_ (ou p_ para parâmetros), minúsculas, com underscore (ex:

v_nome_cliente , p_id_pedido ).

Constantes: Prefixo c_ , maiúsculas, com underscore (ex: C_TAXA_IVA ,

C_MAX_TENTATIVAS ).

Cursores: Prefixo c_ (ex: c_clientes_ativos ).

Procedures: Prefixo prc_ (ex: prc_atualizar_status_pedido ).

Functions: Prefixo fn_ (ex: fn_calcular_desconto ).

Packages: Prefixo pkg_ (ex: pkg_utilitarios ).

 Triggers: Prefixo trg_ (ex: trg_auditoria_pedidos ).

Organização do Código

Um código bem organizado é mais fácil de ler e manter:

Indentação: Use indentação consistente (2 ou 4 espaços) para blocos de código

( BEGIN...END , IF...END IF , LOOP...END LOOP ).

Espaçamento: Use espaços em branco para melhorar a legibilidade, como ao

redor de operadores ( := , = , + ) e após vírgulas.

Comentários: Comente blocos de código complexos, variáveis não óbvias e a

lógica de negócio. Use -- para comentários de linha única e /* ... */ para
blocos de comentários.

Modularização: Divida a lógica complexa em subprogramas (procedures e

functions) menores e focados em uma única tarefa. Utilize packages para
agrupar subprogramas relacionados.

Declarações: Agrupe declarações de variáveis, constantes e cursores por tipo ou

propósito na seção DECLARE .

Tratamento de Exceções

O tratamento de exceções é fundamental para criar aplicações robustas que não

falham inesperadamente. Sempre inclua uma seção EXCEPTION em seus blocos
PL/SQL:

Trate exceções específicas: Capture exceções conhecidas (ex: NO_DATA_FOUND ,

TOO_MANY_ROWS , DUP_VAL_ON_INDEX , ZERO_DIVIDE ) antes de usar um WHEN
OTHERS genérico.

Use WHEN OTHERS com cautela: O WHEN OTHERS deve ser o último handler e
geralmente deve registrar o erro ( SQLCODE , SQLERRM ) e, se apropriado, levantar
uma exceção de aplicação ( RAISE_APPLICATION_ERROR ) ou reverter a transação
( ROLLBACK ).

Evite NULL handlers: Não use WHEN OTHERS THEN NULL; pois isso esconde
erros e dificulta a depuração.
 Log de erros: Implemente um mecanismo para registrar detalhes dos erros
(código, mensagem, stack trace, data/hora, usuário) em uma tabela de log para
análise posterior.

Legibilidade

Um código legível é um código que pode ser rapidamente compreendido por qualquer
desenvolvedor, incluindo você mesmo no futuro:

Nomes descritivos: Use nomes de variáveis, constantes e subprogramas que

indiquem claramente seu propósito.

Evite abreviações excessivas: A menos que sejam amplamente conhecidas no

contexto.

Quebra de linha: Quebre linhas longas para melhorar a leitura, especialmente

em instruções SQL complexas.

Consistência: Mantenha um estilo de codificação consistente em todo o projeto.

Exemplo de Código Aplicando Boas Práticas

Este exemplo demonstra uma procedure que atualiza o estoque de um produto após
uma venda, aplicando as boas práticas discutidas.
-- Supondo a existência da tabela PRODUTOS com colunas ID_PRODUTO,
NOME_PRODUTO, ESTOQUE
-- E uma tabela LOG_ERROS para registrar problemas

CREATE OR REPLACE PROCEDURE prc_atualizar_estoque_venda

(p_id_produto IN produtos.id_produto%TYPE,
p_quantidade_vendida IN NUMBER)
IS
-- Constantes
c_MIN_ESTOQUE CONSTANT NUMBER := 0;

-- Variáveis locais
v_estoque_atual [Link]%TYPE;
v_novo_estoque [Link]%TYPE;
BEGIN
-- 1. Obter o estoque atual do produto
SELECT estoque
INTO v_estoque_atual
FROM produtos
WHERE id_produto = p_id_produto
FOR UPDATE OF estoque; -- Bloqueia a linha para evitar concorrência

-- 2. Calcular o novo estoque

v_novo_estoque := v_estoque_atual - p_quantidade_vendida;

-- 3. Validar se o novo estoque não é negativo (regra de negócio)

IF v_novo_estoque < c_MIN_ESTOQUE THEN
RAISE_APPLICATION_ERROR(-20001, \'Estoque insuficiente para o produto \'
|| p_id_produto || \'.\');
END IF;

-- 4. Atualizar o estoque na tabela

UPDATE produtos
SET estoque = v_novo_estoque
WHERE id_produto = p_id_produto;

-- 5. Confirmar a transação
COMMIT;

DBMS_OUTPUT.PUT_LINE(
\'Estoque do produto \' || p_id_produto ||
\' atualizado de \' || v_estoque_atual ||
\' para \' || v_novo_estoque || \'.\'
);
 EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20002, \'Produto \' || p_id_produto || \' não
encontrado.\');
WHEN DUP_VAL_ON_INDEX THEN -- Exemplo, embora improvável aqui
ROLLBACK;
RAISE_APPLICATION_ERROR(-20003, \'Erro de duplicidade ao atualizar
estoque.\');
WHEN OTHERS THEN
ROLLBACK; -- Reverte todas as operações em caso de erro
-- Registrar o erro em uma tabela de log
INSERT INTO log_erros (data_erro, mensagem_erro, codigo_erro,
procedure_origem)
VALUES (SYSDATE, SQLERRM, SQLCODE, \'prc_atualizar_estoque_venda\');
COMMIT; -- Confirma o log de erro
RAISE_APPLICATION_ERROR(-20004, \'Erro inesperado na atualização de
estoque: \' || SQLERRM);
END prc_atualizar_estoque_venda;
/

-- Testando a Procedure com Boas Práticas

-- Inserir um produto para teste

-- INSERT INTO PRODUTOS (ID_PRODUTO, NOME_PRODUTO, ESTOQUE) VALUES (201,
\'Tablet Pro\', 50);
-- COMMIT;

-- Teste 1: Venda bem-sucedida

-- EXEC prc_atualizar_estoque_venda(p_id_produto => 201,
p_quantidade_vendida => 10);

-- Teste 2: Estoque insuficiente

-- EXEC prc_atualizar_estoque_venda(p_id_produto => 201,
p_quantidade_vendida => 100);

-- Teste 3: Produto não encontrado

-- EXEC prc_atualizar_estoque_venda(p_id_produto => 999,
p_quantidade_vendida => 5);

-- Limpeza (opcional)
-- DELETE FROM PRODUTOS WHERE ID_PRODUTO IN (101, 102);
-- COMMIT;

Explicação do Exemplo:
 Nomenclatura: A procedure segue o padrão prc_ , parâmetros com p_ ,
constantes com c_ e variáveis locais com v_ .

Modularização: A procedure tem um propósito único: atualizar o estoque após

uma venda.

Legibilidade: Indentação consistente, comentários explicativos e nomes

descritivos tornam o código fácil de entender.

Tratamento de Exceções:
NO_DATA_FOUND é tratado especificamente para quando o produto não é
encontrado.

Uma exceção de aplicação ( RAISE_APPLICATION_ERROR ) é levantada se o

estoque ficar negativo, informando o usuário sobre a regra de negócio
violada.

FOR UPDATE OF estoque é usado para bloquear a linha do produto,

prevenindo problemas de concorrência em ambientes multiusuário.

O WHEN OTHERS captura qualquer outro erro inesperado, realiza um

ROLLBACK para desfazer a transação e, crucialmente, registra o erro em
uma tabela LOG_ERROS antes de levantar uma exceção genérica para o
chamador. Isso garante que todos os erros sejam registrados para análise.

9. Conclusão

Chegamos ao final desta apostila, que teve como objetivo fornecer uma base sólida e
abrangente sobre os principais conceitos e funcionalidades do PL/SQL (Procedural
Language/Structured Query Language) da Oracle. Ao longo das seções, exploramos
desde a estrutura fundamental de um bloco PL/SQL até tópicos mais avançados como
cursores, procedures, functions e triggers, sempre acompanhados de exemplos
práticos e explicações detalhadas.

Recapitulando, você aprendeu a:

Compreender a estrutura básica de um bloco PL/SQL, com suas seções

DECLARE , BEGIN e EXCEPTION .

Declarar e utilizar variáveis e constantes para armazenar e manipular dados

temporariamente.
 Controlar o fluxo de execução do programa com estruturas de controle como
IF/ELSIF/ELSE , CASE , LOOP , WHILE e FOR .

Processar conjuntos de resultados de consultas SQL linha a linha utilizando

cursores (implícitos e explícitos).

Criar procedures para encapsular lógica de negócios e executar ações, utilizando

parâmetros IN , OUT e IN OUT .

Desenvolver functions para realizar cálculos e retornar um único valor,

integrando-as em consultas SQL.

Implementar triggers para automatizar ações em resposta a eventos DML,

garantindo a integridade e a auditoria dos dados.

Adotar boas práticas de programação para escrever código PL/SQL legível,

manutenível e eficiente.

Sugestões de Próximos Estudos em PL/SQL e Oracle

O universo do PL/SQL e do Oracle é vasto e oferece muitas outras áreas para

aprofundamento. Para continuar sua jornada de aprendizado, sugerimos os seguintes
tópicos:

Packages: Aprenda a agrupar procedures, functions, variáveis e cursores

relacionados em unidades lógicas, promovendo a modularidade e a reutilização.

Tipos de Dados Avançados: Explore tipos como RECORD , TABLE OF RECORD ,

VARRAY , NESTED TABLE para manipulação de coleções de dados.

SQL Dinâmico: Entenda como construir e executar instruções SQL em tempo de

execução usando EXECUTE IMMEDIATE e DBMS_SQL .

PL/SQL Orientado a Objetos: Descubra como criar tipos de objetos e tabelas de

objetos para modelar dados complexos.

Performance Tuning em PL/SQL: Otimize seu código para garantir a máxima

eficiência, utilizando técnicas como BULK COLLECT , FORALL , e evitando N+1
SELECTs .

Segurança em PL/SQL: Aprofunde-se em tópicos como AUTHID , privilégios e

roles para garantir a segurança de suas aplicações.

Ferramentas de Desenvolvimento Oracle: Explore o uso de ferramentas como

SQL Developer, SQL*Plus, e Oracle APEX para desenvolvimento e gerenciamento.
 Administração de Banco de Dados Oracle (DBA): Entenda os conceitos de
arquitetura, backup e recuperação, gerenciamento de usuários e performance do
banco de dados.

Esperamos que esta apostila sirva como um guia valioso em sua jornada de
aprendizado em PL/SQL. A prática constante e a exploração de novos conceitos são a
chave para se tornar um desenvolvedor PL/SQL proficiente. Boa sorte em seus estudos
e projetos!