Brayan Monteiro

Posted on Mar 6

PL/SQL na prática!

#plsql #oracle #sql

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;