Programação Avançada em Oracle SQL: Funções Analíticas, CTEs, Particionamento, Triggers, Packages e Mais!

Programação Avançada em Oracle SQL: Funções Analíticas, CTEs, Particionamento, Triggers, Packages e Mais!

Introdução

A programação avançada em Oracle SQL envolve o uso de recursos e técnicas mais complexas para lidar com problemas de banco de dados e manipulação de dados de forma eficiente. Neste artigo, exploraremos alguns conceitos e exemplos práticos de programação avançada em Oracle SQL.

Tópicos Abordados

• Funções Analíticas
• Common Table Expressions (CTEs)
• Particionamento de Tabelas
• Materialized Views (Visões Materializadas)
• Triggers (Gatilhos)
• Stored Procedures
• Packages

Funções Analíticas

As funções analíticas são poderosas ferramentas que permitem realizar cálculos agregados em um conjunto de registros, com base em uma condição de agrupamento específica. Elas são usadas com a cláusula OVER e podem simplificar consultas complexas. Exemplo:

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_per_department
FROM
  employees;

Common Table Expressions (CTEs)

CTEs são consultas nomeadas temporárias que nos permitem reutilizar resultados em uma consulta principal. Isso torna o código mais legível e facilita o entendimento das consultas complexas. Exemplo:

WITH top_employees AS (
  SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS emp_rank
  FROM
    employees
)
SELECT
  employee_id,
  first_name,
  last_name,
  salary
FROM
  top_employees
WHERE
  emp_rank <= 10;

Particionamento de Tabelas

O particionamento é uma técnica que divide tabelas grandes em partes menores chamadas partições, com base em um critério específico (por exemplo, valores de data ou intervalos numéricos). Isso melhora o desempenho e facilita a manutenção de tabelas com grandes quantidades de dados.

CREATE TABLE sales (
  sales_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL(NUMTODSINTERVAL(1,'MONTH'))
(
  PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2023', 'DD-MM-YYYY'))
);

Materialized Views (Visões Materializadas)

Materialized Views são estruturas de dados pré-computadas que armazenam os resultados de uma consulta. Elas são usadas para melhorar o desempenho de consultas complexas e reduzir a carga do servidor, pois os resultados são armazenados em vez de serem calculados toda vez que a consulta é executada.

CREATE MATERIALIZED VIEW mv_sales_by_department
REFRESH FAST ON COMMIT
AS
SELECT
  department_id,
  SUM(amount) AS total_sales
FROM
  sales
GROUP BY
  department_id;

Triggers (Gatilhos)

Os triggers são procedimentos armazenados que são executados automaticamente em resposta a eventos específicos, como INSERT, UPDATE ou DELETE em uma tabela. Eles são úteis para implementar lógicas adicionais, como manter a integridade referencial ou registrar alterações.

CREATE OR REPLACE TRIGGER trg_audit_employee
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO employee_audit (employee_id, action, audit_date)
    VALUES (:NEW.employee_id, 'INSERT', SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO employee_audit (employee_id, action, audit_date)
    VALUES (:NEW.employee_id, 'UPDATE', SYSDATE);
  ELSIF DELETING THEN
    INSERT INTO employee_audit (employee_id, action, audit_date)
    VALUES (:OLD.employee_id, 'DELETE', SYSDATE);
  END IF;
END;

Stored Procedures

Stored Procedures são conjuntos de instruções SQL que podem ser armazenados no banco de dados e executados posteriormente por meio de uma chamada. Eles são usados para encapsular lógicas complexas e processos repetitivos no banco de dados.

CREATE OR REPLACE PROCEDURE sp_update_employee_salary(
  p_employee_id NUMBER,
  p_new_salary NUMBER
) AS
BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
  COMMIT;
END;

Packages

As packages (pacotes) em Oracle SQL são coleções de procedimentos, funções e variáveis que permitem organizar e encapsular a lógica de negócios em unidades reutilizáveis. Abaixo, vamos incluir um exemplo de como criar uma package em Oracle SQL:

-- Criação de Package
CREATE OR REPLACE PACKAGE pkg_employee_info AS
  PROCEDURE get_employee_salary(p_employee_id NUMBER);
  FUNCTION get_employee_name(p_employee_id NUMBER) RETURN VARCHAR2;
END;
/

-- Corpo da Package
CREATE OR REPLACE PACKAGE BODY pkg_employee_info AS
  PROCEDURE get_employee_salary(p_employee_id NUMBER) AS
    v_salary NUMBER;
  BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id || ', Salary: ' || v_salary);
  END;
  
  FUNCTION get_employee_name(p_employee_id NUMBER) RETURN VARCHAR2 AS
    v_name VARCHAR2(100);
  BEGIN
    SELECT first_name || ' ' || last_name INTO v_name
    FROM employees
    WHERE employee_id = p_employee_id;
    
    RETURN v_name;
  END;
END;
/

Neste exemplo, criamos uma package chamada pkg_employee_info, que contém dois elementos: um procedimento chamado get_employee_salary que retorna o salário de um funcionário com base em seu ID e uma função chamada get_employee_name que retorna o nome completo do funcionário pelo seu ID.

As packages são extremamente úteis para modularizar e organizar a lógica do banco de dados, promovendo a reutilização de código e facilitando a manutenção. Elas são uma parte fundamental da programação avançada em Oracle SQL.

Conclusão

A programação avançada em Oracle SQL envolve o uso inteligente de funções analíticas, CTEs, particionamento de tabelas, materialized views, triggers e stored procedures para melhorar o desempenho, a eficiência e a manutenção das consultas e operações em bancos de dados Oracle. Com os exemplos práticos apresentados neste artigo, você pode começar a explorar e aplicar essas técnicas em seus projetos com Oracle SQL.

Abs