PL/SQL na prática!

Seção 1 – PL/SQL Fundamentos - Bloco Anônimo Um bloco anônimo é um trecho de código PL/SQL executado uma única vez, sem ser armazenado no banco de dados. Estrutura Básica: DECLARE -- Declarações de variáveis e constantes BEGIN -- Lógica principal EXCEPTION -- Tratamento de exceções END; Exemplo: DECLARE v_nome VARCHAR2(50) := 'João'; BEGIN DBMS_OUTPUT.PUT_LINE('Olá, ' || v_nome); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLERRM); END; Seção 2 – Declaração de Identificadores Variáveis DECLARE v_id NUMBER := 10; -- Tipo NUMBER v_data DATE := SYSDATE; -- Tipo DATE v_texto VARCHAR2(100); -- Variável não inicializada BEGIN v_texto := 'Texto dinâmico'; END; Constantes DECLARE c_pi CONSTANT NUMBER := 3.1415; BEGIN DBMS_OUTPUT.PUT_LINE('Valor de PI: ' || c_pi); END; Tipos de Dados NUMBER, VARCHAR2, DATE, BOOLEAN, etc. Variável Bind Variáveis declaradas fora do bloco PL/SQL (em SQLPlus ou SQL Developer): VARIABLE g_id NUMBER BEGIN :g_id := 100; END; / PRINT g_id; Seção 3 – Sintaxe e Diretrizes Exemplo com Função: DECLARE v_total NUMBER; BEGIN SELECT COUNT() INTO v_total FROM employees; DBMS_OUTPUT.PUT_LINE('Total de empregados: ' || v_total); END; Blocos Aninhados: DECLARE v_global NUMBER := 1; BEGIN DECLARE v_local NUMBER := 2; BEGIN DBMS_OUTPUT.PUT_LINE(v_global + v_local); -- Resultado: 3 END; END; Seção 4 – Comandos SQL no PL/SQL SELECT INTO DECLARE v_nome employees.first_name%TYPE; BEGIN SELECT first_name INTO v_nome FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_nome); END; INSERT BEGIN INSERT INTO employees (employee_id, first_name, last_name) VALUES (1000, 'Maria', 'Silva'); COMMIT; END; Cursor Implícito BEGIN UPDATE employees SET salary = salary * 1.1; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' linhas atualizadas.'); END; Seção 5 – Estruturas de Controle IF-THEN-ELSE DECLARE v_nota NUMBER := 75; BEGIN IF v_nota >= 70 THEN DBMS_OUTPUT.PUT_LINE('Aprovado'); ELSE DBMS_OUTPUT.PUT_LINE('Reprovado'); END IF; END; CASE DECLARE v_dia VARCHAR2(10) := 'Segunda'; BEGIN CASE v_dia WHEN 'Segunda' THEN DBMS_OUTPUT.PUT_LINE('Dia útil'); ELSE DBMS_OUTPUT.PUT_LINE('Fim de semana'); END CASE; END; LOOP Básico DECLARE i NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(i); i := i + 1; EXIT WHEN i > 5; END LOOP; END; Seção 6 – Tipos Compostos (Records) DECLARE TYPE t_empregado IS RECORD ( id employees.employee_id%TYPE, nome employees.first_name%TYPE ); v_emp t_empregado; BEGIN SELECT employee_id, first_name INTO v_emp FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_emp.nome); END; %ROWTYPE DECLARE v_emp employees%ROWTYPE; BEGIN SELECT * INTO v_emp FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_emp.first_name); END; Seção 7 – Collections Associative Array DECLARE TYPE t_nomes IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; v_lista t_nomes; BEGIN v_lista(1) := 'Maria'; v_lista(2) := 'João'; DBMS_OUTPUT.PUT_LINE(v_lista(1)); END; Bulk Collect DECLARE TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE; v_ids t_emp_ids; BEGIN SELECT employee_id BULK COLLECT INTO v_ids FROM employees WHERE department_id = 50; DBMS_OUTPUT.PUT_LINE(v_ids.COUNT || ' registros.'); END; Seção 8 – Cursor Explícito Cursor com Parâmetros DECLARE CURSOR c_emp (p_dept_id NUMBER) IS SELECT * FROM employees WHERE department_id = p_dept_id; v_emp employees%ROWTYPE; BEGIN OPEN c_emp(50); LOOP FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.first_name); END LOOP; CLOSE c_emp; END; Seção 9 – Tratamento de Exceções DECLARE v_div NUMBER; BEGIN v_div := 10 / 0; -- Divisão por zero EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Erro: Divisão por zero.'); END; Seção 10 – Procedures Criando uma Procedure CREATE OR REPLACE PROCEDURE aumentar_salario ( p_emp_id IN NUMBER, p_percentual IN NUMBER ) AS BEGIN UPDATE employees SET salary = salary * (1 + p_percentual/100) WHERE employee_id = p_emp_id; COMMIT; END aumentar_salario; Chamando a Procedure: BEGIN aumentar_salario(100, 10); -- Aumenta 10% para o empregado 100 END; Seção 11 – Funções Criando uma Função CREATE OR REPLACE FUNCTION calcular_idade ( p_data_nasc DATE ) RETURN NUMBER AS v_idade NUMBER; BEGIN v_idade := TRUNC(MONTHS_BETWEEN(SYSDATE, p_data_nasc)

Mar 6, 2025 - 23:57

Seção 1 – PL/SQL Fundamentos - Bloco Anônimo

Um bloco anônimo é um trecho de código PL/SQL executado uma única vez, sem ser armazenado no banco de dados.

Estrutura Básica:

DECLARE
  -- Declarações de variáveis e constantes
BEGIN
  -- Lógica principal
EXCEPTION
  -- Tratamento de exceções
END;

Exemplo:

DECLARE
  v_nome VARCHAR2(50) := 'João';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Olá, ' || v_nome);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLERRM);
END;

Seção 2 – Declaração de Identificadores

Variáveis

DECLARE
  v_id NUMBER := 10;          -- Tipo NUMBER
  v_data DATE := SYSDATE;     -- Tipo DATE
  v_texto VARCHAR2(100);      -- Variável não inicializada
BEGIN
  v_texto := 'Texto dinâmico';
END;

Constantes

DECLARE
  c_pi CONSTANT NUMBER := 3.1415;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Valor de PI: ' || c_pi);
END;

Tipos de Dados

NUMBER, VARCHAR2, DATE, BOOLEAN, etc.

Variável Bind

Variáveis declaradas fora do bloco PL/SQL (em SQL*Plus ou SQL Developer):

VARIABLE g_id NUMBER
BEGIN
  :g_id := 100;
END;
/
PRINT g_id;

Seção 3 – Sintaxe e Diretrizes

Exemplo com Função:

DECLARE
  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM employees;
  DBMS_OUTPUT.PUT_LINE('Total de empregados: ' || v_total);
END;

Blocos Aninhados:

DECLARE
  v_global NUMBER := 1;
BEGIN
  DECLARE
    v_local NUMBER := 2;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_global + v_local); -- Resultado: 3
  END;
END;

Seção 4 – Comandos SQL no PL/SQL

SELECT INTO

DECLARE
  v_nome employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_nome 
  FROM employees 
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_nome);
END;

INSERT

BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1000, 'Maria', 'Silva');
  COMMIT;
END;

Cursor Implícito

BEGIN
  UPDATE employees SET salary = salary * 1.1;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' linhas atualizadas.');
END;

Seção 5 – Estruturas de Controle

IF-THEN-ELSE

DECLARE
  v_nota NUMBER := 75;
BEGIN
  IF v_nota >= 70 THEN
    DBMS_OUTPUT.PUT_LINE('Aprovado');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Reprovado');
  END IF;
END;

CASE

DECLARE
  v_dia VARCHAR2(10) := 'Segunda';
BEGIN
  CASE v_dia
    WHEN 'Segunda' THEN DBMS_OUTPUT.PUT_LINE('Dia útil');
    ELSE DBMS_OUTPUT.PUT_LINE('Fim de semana');
  END CASE;
END;

LOOP Básico

DECLARE
  i NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    i := i + 1;
    EXIT WHEN i > 5;
  END LOOP;
END;

Seção 6 – Tipos Compostos (Records)

DECLARE
  TYPE t_empregado IS RECORD (
    id employees.employee_id%TYPE,
    nome employees.first_name%TYPE
  );
  v_emp t_empregado;
BEGIN
  SELECT employee_id, first_name INTO v_emp 
  FROM employees 
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.nome);
END;

%ROWTYPE

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp 
  FROM employees 
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
END;

Seção 7 – Collections

Associative Array

DECLARE
  TYPE t_nomes IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_lista t_nomes;
BEGIN
  v_lista(1) := 'Maria';
  v_lista(2) := 'João';
  DBMS_OUTPUT.PUT_LINE(v_lista(1));
END;

Bulk Collect

DECLARE
  TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_emp_ids;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_ids 
  FROM employees 
  WHERE department_id = 50;
  DBMS_OUTPUT.PUT_LINE(v_ids.COUNT || ' registros.');
END;

Seção 8 – Cursor Explícito

Cursor com Parâmetros

DECLARE
  CURSOR c_emp (p_dept_id NUMBER) IS
    SELECT * FROM employees WHERE department_id = p_dept_id;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN c_emp(50);
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
  END LOOP;
  CLOSE c_emp;
END;

Seção 9 – Tratamento de Exceções

DECLARE
  v_div NUMBER;
BEGIN
  v_div := 10 / 0; -- Divisão por zero
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Erro: Divisão por zero.');
END;

Seção 10 – Procedures

Criando uma Procedure

CREATE OR REPLACE PROCEDURE aumentar_salario (
  p_emp_id IN NUMBER,
  p_percentual IN NUMBER
) AS
BEGIN
  UPDATE employees 
  SET salary = salary * (1 + p_percentual/100)
  WHERE employee_id = p_emp_id;
  COMMIT;
END aumentar_salario;

Chamando a Procedure:

BEGIN
  aumentar_salario(100, 10); -- Aumenta 10% para o empregado 100
END;

Seção 11 – Funções

Criando uma Função

CREATE OR REPLACE FUNCTION calcular_idade (
  p_data_nasc DATE
) RETURN NUMBER AS
  v_idade NUMBER;
BEGIN
  v_idade := TRUNC(MONTHS_BETWEEN(SYSDATE, p_data_nasc) / 12);
  RETURN v_idade;
END calcular_idade;

Usando a Função em SQL:

SELECT first_name, calcular_idade(birth_date) AS idade 
FROM employees;

Seção 12 – Packages

Package Specification

CREATE OR REPLACE PACKAGE pkg_emp AS
  PROCEDURE aumentar_salario(p_emp_id NUMBER, p_percentual NUMBER);
  FUNCTION calcular_idade(p_data_nasc DATE) RETURN NUMBER;
END pkg_emp;

Package Body

CREATE OR REPLACE PACKAGE BODY pkg_emp AS
  PROCEDURE aumentar_salario(p_emp_id NUMBER, p_percentual NUMBER) IS
  BEGIN
    UPDATE employees 
    SET salary = salary * (1 + p_percentual/100)
    WHERE employee_id = p_emp_id;
    COMMIT;
  END;

  FUNCTION calcular_idade(p_data_nasc DATE) RETURN NUMBER IS
    v_idade NUMBER;
  BEGIN
    v_idade := TRUNC(MONTHS_BETWEEN(SYSDATE, p_data_nasc) / 12);
    RETURN v_idade;
  END;
END pkg_emp;

Seção 13 – Triggers DML

Trigger a Nível de Linha

CREATE OR REPLACE TRIGGER trg_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary);
END;

Seção 14 – Bulk Collect com LIMIT

DECLARE
  TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_emp_ids;
  CURSOR c_emp IS SELECT employee_id FROM employees;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_ids LIMIT 100;
    EXIT WHEN v_ids.COUNT = 0;
    -- Processar 100 registros por vez
  END LOOP;
  CLOSE c_emp;
END;

Seção 15 – SQL Dinâmico

EXECUTE IMMEDIATE

DECLARE
  v_sql VARCHAR2(200);
  v_emp_id NUMBER := 100;
  v_salary NUMBER;
BEGIN
  v_sql := 'SELECT salary FROM employees WHERE employee_id = :id';
  EXECUTE IMMEDIATE v_sql INTO v_salary USING v_emp_id;
  DBMS_OUTPUT.PUT_LINE(v_salary);
END;

Seção 16 – LOBs (BLOB)

DECLARE
  v_blob BLOB;
  v_bfile BFILE := BFILENAME('DIR_IMAGENS', 'foto.jpg');
BEGIN
  INSERT INTO imagens (id, imagem) 
  VALUES (1, EMPTY_BLOB())
  RETURNING imagem INTO v_blob;

  DBMS_LOB.OPEN(v_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
  DBMS_LOB.CLOSE(v_bfile);
  COMMIT;
END;