Introdução ao DBMS_DDL no Oracle
O pacote DBMS_DDL no Oracle 19c fornece um conjunto de procedimentos e funções que permitem executar comandos DDL (Data Definition Language) dentro de blocos PL/SQL. Com ele, é possível criar, alterar e excluir objetos de banco de dados de forma programática. Este artigo explora o uso do DBMS_DDL, apresentando exemplos práticos e destacando suas funcionalidades principais.
O Que é DBMS_DDL?
DBMS_DDL é um pacote PL/SQL que oferece uma interface para executar comandos DDL em um ambiente PL/SQL. Ele é particularmente útil em situações onde há necessidade de criar ou modificar objetos de banco de dados dinamicamente, por exemplo, em aplicações que exigem a criação de tabelas temporárias ou esquemas personalizados para diferentes usuários.
Funcionalidades do DBMS_DDL
- Execução de DDL Dinâmico: Permite a execução de comandos DDL dinamicamente dentro de blocos PL/SQL.
- Recompilação de Objetos: Facilita a recompilação de objetos de banco de dados, como pacotes, funções e procedimentos.
- Manipulação de Identificadores: Inclui funcionalidades para gerar identificadores de objetos de banco de dados.
Exemplos Práticos
Exemplo Básico: Criação de uma Tabela Dinamicamente
Vamos começar com um exemplo simples de como criar uma tabela dinamicamente usando o DBMS_DDL.
BEGIN
DBMS_DDL.EXECUTE_IMMEDIATE('CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
)');
END;
/
PL/SQL procedure successfully completed.Exemplo Básico: Adicionar uma Coluna a uma Tabela
Outro uso básico do DBMS_DDL é adicionar uma nova coluna a uma tabela existente.
BEGIN
DBMS_DDL.EXECUTE_IMMEDIATE('ALTER TABLE employees ADD (department_id NUMBER)');
END;
/
PL/SQL procedure successfully completed.Exemplo Mais Complexo: Recompilação de Objetos
Em cenários onde objetos de banco de dados precisam ser recompilados, o DBMS_DDL pode ser utilizado para automatizar essa tarefa.
Passo 1: Criação de um Procedimento Simples
CREATE OR REPLACE PROCEDURE calculate_bonus AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Calculating bonus...');
END calculate_bonus;
/
Procedure created.Passo 2: Modificação da Procedure
Vamos alterar a procedure e recompilá-la usando DBMS_DDL.
BEGIN
DBMS_DDL.EXECUTE_IMMEDIATE('CREATE OR REPLACE PROCEDURE calculate_bonus AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Bonus calculated successfully.');
END calculate_bonus;');
END;
/
PL/SQL procedure successfully completed.Passo 3: Recompilação da Procedure
BEGIN
DBMS_DDL.ALTER_COMPILE('PROCEDURE', 'CALCULATE_BONUS');
END;
/
PL/SQL procedure successfully completed.Exemplo Avançado: Criação de Tabelas com Nomes Dinâmicos
Vamos criar um exemplo mais complexo onde utilizamos DBMS_DDL para criar tabelas com nomes dinâmicos baseados na data atual.
Passo 1: Criação de um Bloco PL/SQL para Gerar Nomes Dinâmicos
DECLARE
table_name VARCHAR2(50);
BEGIN
table_name := 'EMPLOYEES_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
DBMS_DDL.EXECUTE_IMMEDIATE('CREATE TABLE ' || table_name || ' (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
)');
DBMS_OUTPUT.PUT_LINE('Table ' || table_name || ' created successfully.');
END;
/
Table EMPLOYEES_20240721 created successfully.
PL/SQL procedure successfully completed.Passo 2: Inserção de Dados na Tabela Dinâmica
DECLARE
table_name VARCHAR2(50);
BEGIN
table_name := 'EMPLOYEES_' || TO_CHAR(SYSDATE, 'YYYYMMDD');
DBMS_DDL.EXECUTE_IMMEDIATE('INSERT INTO ' || table_name || ' (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, ''John'', ''Doe'', TO_DATE(''2023-01-15'', ''YYYY-MM-DD''), 50000)');
DBMS_OUTPUT.PUT_LINE('Data inserted into table ' || table_name || ' successfully.');
END;
/
Data inserted into table EMPLOYEES_20240721 successfully.
PL/SQL procedure successfully completed.Consultas e Verificação de Tabelas Criadas
Para verificar a tabela criada dinamicamente e os dados inseridos:
SELECT * FROM employees_20240721;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY
----------- ---------- --------- ---------- ------
1 John Doe 15-JAN-23 50000Conclusão
O pacote DBMS_DDL no Oracle 19c é uma ferramenta poderosa que permite a execução dinâmica de comandos DDL dentro de blocos PL/SQL. Suas funcionalidades facilitam a criação, modificação e recompilação de objetos de banco de dados de forma programática, tornando-o indispensável para administradores de banco de dados e desenvolvedores que necessitam de flexibilidade na gestão de objetos de banco de dados.
Abs
Referências