Unidades de Programa em PL/SQL: Functions, Procedures, Packages e Triggers

Unidades de Programa em PL/SQL: Functions, Procedures, Packages e Triggers

O que são unidades de programa em PL/SQL?

Unidades de programa são blocos de código que podem ser executados de forma independente ou em conjunto com outros blocos. Elas permitem que você organize o seu código em módulos lógicos, que podem ser chamados de outros programas ou de forma interativa. As unidades de programa também facilitam a manutenção, a depuração e a documentação do seu código, pois você pode dividir um problema complexo em partes menores e mais simples.

As principais unidades de programa em PL/SQL são:

• Funções (Functions): são blocos de código que retornam um único valor, que pode ser de qualquer tipo de dado. As funções são usadas para realizar cálculos, conversões, validações ou outras operações que dependem de um ou mais parâmetros de entrada. As funções podem ser chamadas de qualquer lugar onde uma expressão seja permitida, como em uma cláusula SELECT, em uma atribuição, em um predicado ou em outra função.
• Procedimentos (Procedures): são blocos de código que realizam uma tarefa específica, mas não retornam nenhum valor. Os procedimentos são usados para executar ações que não dependem de um resultado, como inserir, atualizar ou deletar dados, imprimir mensagens, gerar relatórios ou interagir com o usuário. Os procedimentos podem ser chamados de outros blocos de código, usando a palavra-chave CALL ou o operador EXECUTE.
• Pacotes (Packages): são coleções de funções, procedimentos, variáveis, constantes, tipos, cursores e exceções relacionados, que são agrupados em uma única unidade lógica. Os pacotes permitem que você organize o seu código em categorias funcionais, que podem ser compartilhadas por vários programas. Os pacotes também melhoram o desempenho, a segurança e a modularidade do seu código, pois você pode controlar a visibilidade e a persistência dos seus objetos.
• Gatilhos (Triggers): são blocos de código que são executados automaticamente quando um evento específico ocorre no banco de dados, como uma operação de DML (Data Manipulation Language), uma operação de DDL (Data Definition Language), um erro, uma conexão ou uma desconexão. Os gatilhos permitem que você implemente regras de negócios, auditorias, restrições de integridade ou outras ações que dependem do estado do banco de dados.

Como criar unidades de programa em PL/SQL?

Para criar uma unidade de programa em PL/SQL, você precisa usar a sintaxe apropriada para cada tipo de unidade. A sintaxe geral de uma unidade de programa é:

CREATE [OR REPLACE] UNIT_TYPE UNIT_NAME [(PARAMETER_LIST)]
[IS | AS]
[DECLARATION_SECTION]
BEGIN
  EXECUTION_SECTION
[EXCEPTION]
  EXCEPTION_SECTION
END [UNIT_NAME];

Onde:

• UNIT_TYPE é o tipo da unidade de programa, que pode ser FUNCTION, PROCEDURE, PACKAGE ou TRIGGER.
• UNIT_NAME é o nome da unidade de programa, que deve ser único dentro do seu escopo e seguir as regras de nomenclatura do Oracle.
• PARAMETER_LIST é a lista de parâmetros da unidade de programa, que são variáveis que recebem ou retornam valores. Cada parâmetro deve ter um nome, um tipo de dado e um modo de passagem, que pode ser IN, OUT ou IN OUT. Os parâmetros são opcionais para os procedimentos e os gatilhos, mas obrigatórios para as funções.
• IS ou AS são palavras-chave que indicam o início da definição da unidade de programa.
• DECLARATION_SECTION é a seção onde você pode declarar variáveis, constantes, tipos, cursores e exceções locais à unidade de programa. Essa seção é opcional, mas recomendada para melhorar a legibilidade e a organização do seu código.
• BEGIN é a palavra-chave que indica o início da seção de execução da unidade de programa.
• EXECUTION_SECTION é a seção onde você escreve o código que realiza a tarefa da unidade de programa, usando comandos SQL, PL/SQL ou chamadas a outras unidades de programa. Essa seção é obrigatória e deve terminar com um ponto-e-vírgula.
• EXCEPTION é a palavra-chave que indica o início da seção de tratamento de exceções da unidade de programa.
• EXCEPTION_SECTION é a seção onde você pode capturar e tratar os erros que ocorrem durante a execução da unidade de programa, usando blocos WHEN … THEN … END. Essa seção é opcional, mas recomendada para melhorar a robustez e a confiabilidade do seu código.
• END é a palavra-chave que indica o fim da definição da unidade de programa.
• UNIT_NAME é o nome da unidade de programa, que pode ser repetido no final para facilitar a identificação.

Como gerenciar unidades de programa em PL/SQL?

Para gerenciar as unidades de programa em PL/SQL, você pode usar os seguintes comandos:

• ALTER: permite modificar a definição de uma unidade de programa existente, usando a mesma sintaxe do comando CREATE, mas com a palavra-chave ALTER no início. Por exemplo:

ALTER FUNCTION f_soma (p_x NUMBER, p_y NUMBER) RETURN NUMBER
IS
  v_resultado NUMBER;
BEGIN
  v_resultado := p_x + p_y;
  RETURN v_resultado;
END f_soma;

• DROP: permite excluir uma unidade de programa existente, usando a sintaxe:

DROP UNIT_TYPE UNIT_NAME;

Por exemplo:

DROP FUNCTION f_soma;

• CALL ou EXECUTE: permitem executar uma unidade de programa existente, passando os parâmetros necessários, usando a sintaxe:

CALL UNIT_NAME (PARAMETER_LIST);

ou

EXECUTE UNIT_NAME (PARAMETER_LIST);

Por exemplo:

CALL f_soma (10, 20);

ou

EXECUTE f_soma (10, 20);

• SHOW ERRORS: permite exibir os erros de compilação de uma unidade de programa, usando a sintaxe:

SHOW ERRORS [UNIT_TYPE] [UNIT_NAME];

Por exemplo:

SHOW ERRORS FUNCTION f_soma;

Exemplos práticos de unidades de programa em PL/SQL

Agora que você já sabe o que são e como criar e gerenciar unidades de programa em PL/SQL, vamos ver alguns exemplos práticos de como usar cada tipo de unidade.

Funções (Functions)

As funções são unidades de programa que retornam um único valor, que pode ser de qualquer tipo de dado. As funções são usadas para realizar cálculos, conversões, validações ou outras operações que dependem de um ou mais parâmetros de entrada. As funções podem ser chamadas de qualquer lugar onde uma expressão seja permitida, como em uma cláusula SELECT, em uma atribuição, em um predicado ou em outra função.

Vamos ver um exemplo de como criar e usar uma função que calcula o fatorial de um número inteiro positivo. O fatorial de um número n é o produto de todos os números inteiros positivos menores ou iguais a n, e é representado por n!. Por exemplo, o fatorial de 5 é 5! = 5 x 4 x 3 x 2 x 1 = 120.

Para criar a função, usamos o comando CREATE FUNCTION, especificando o nome da função, a lista de parâmetros, o tipo de retorno e o código que realiza o cálculo. No nosso caso, vamos usar um laço FOR para multiplicar os números de 1 até o parâmetro de entrada, e armazenar o resultado em uma variável local. Em seguida, vamos retornar o valor dessa variável. Veja o código abaixo:

CREATE FUNCTION f_fatorial (p_n NUMBER) RETURN NUMBER
IS
  v_fatorial NUMBER := 1;
BEGIN
  FOR i IN 1 .. p_n LOOP
    v_fatorial := v_fatorial * i;
  END LOOP;
  RETURN v_fatorial;
END f_fatorial;

Para usar a função, podemos chamá-la de qualquer lugar onde uma expressão seja permitida, passando o valor do parâmetro. Por exemplo, podemos usar a função em uma cláusula SELECT, para exibir o fatorial de um número digitado pelo usuário:

SELECT f_fatorial(5) AS fatorial FROM DUAL;

– Resultado: exibe o fatorial de 5 FATORIAL
120

Ou podemos usar a função em uma atribuição, para armazenar o fatorial de um número em uma variável:

DECLARE
  v_numero NUMBER := 6;
  v_resultado NUMBER;
BEGIN
  v_resultado := f_fatorial(v_numero);
  DBMS_OUTPUT.PUT_LINE('O fatorial de ' || v_numero || ' é ' || v_resultado);
END;
/

-- Resultado: exibe o fatorial de 6

O fatorial de 6 é 720

Ou podemos usar a função em um predicado, para filtrar os dados de uma tabela que satisfaçam uma condição:

-- Suponha que temos uma tabela chamada numeros, com uma coluna chamada valor

CREATE TABLE numeros (
  valor NUMBER
);

-- Suponha que inserimos alguns valores na tabela

INSERT INTO numeros VALUES (1);
INSERT INTO numeros VALUES (2);
INSERT INTO numeros VALUES (3);
INSERT INTO numeros VALUES (4);
INSERT INTO numeros VALUES (5);
INSERT INTO numeros VALUES (6);

-- Podemos usar a função f_fatorial em um predicado, para exibir apenas os valores cujo fatorial seja maior que 100

SELECT valor FROM numeros WHERE f_fatorial(valor) > 100;

-- Resultado: exibe os valores 5 e 6

VALOR
-----
5
6

Ou podemos usar a função em outra função, para compor operações mais complexas:

-- Vamos criar uma função que calcula o coeficiente binomial de dois números, usando a fórmula n! / (k! * (n - k)!)

CREATE FUNCTION f_binomial (p_n NUMBER, p_k NUMBER) RETURN NUMBER
IS
BEGIN
  RETURN f_fatorial(p_n) / (f_fatorial(p_k) * f_fatorial(p_n - p_k));
END f_binomial;

Como você pode ver, as funções são unidades de programa muito úteis e versáteis, que permitem retornar valores a partir de parâmetros de entrada, e que podem ser usadas em diversos contextos. As funções também facilitam a reutilização do código, pois você pode definir uma vez e usar várias vezes, sem precisar repetir a lógica. Além disso, as funções melhoram a legibilidade e a manutenção do código, pois você pode dar nomes significativos às suas funções, e alterar a definição de uma função sem afetar os outros programas que a chamam.

Procedimentos (Procedures)

Os procedimentos são unidades de programa que realizam uma tarefa específica, mas não retornam nenhum valor. Os procedimentos são usados para executar ações que não dependem de um resultado, como inserir, atualizar ou deletar dados, imprimir mensagens, gerar relatórios ou interagir com o usuário. Os procedimentos podem ser chamados de outros blocos de código, usando a palavra-chave CALL ou o operador EXECUTE.

Vamos ver um exemplo de como criar e usar um procedimento que imprime uma mensagem de boas-vindas ao usuário, usando o seu nome como parâmetro. Para criar o procedimento, usamos o comando CREATE PROCEDURE, especificando o nome do procedimento, a lista de parâmetros e o código que realiza a ação. No nosso caso, vamos usar o pacote DBMS_OUTPUT, que permite exibir mensagens na tela, e concatenar o parâmetro de entrada com uma string fixa. Veja o código abaixo:

CREATE PROCEDURE p_boas_vindas (p_nome VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Olá, ' || p_nome || '! Seja bem-vindo ao mundo do PL/SQL!');
END p_boas_vindas;

Para usar o procedimento, podemos chamá-lo de outro bloco de código, passando o valor do parâmetro. Por exemplo, podemos usar o procedimento em um bloco anônimo, para exibir uma mensagem de boas-vindas ao usuário que digitar o seu nome:

DECLARE
  v_nome VARCHAR2(50);
BEGIN
  v_nome := '&nome';
  p_boas_vindas(v_nome);
END;
/

-- Resultado: exibe uma mensagem de boas-vindas ao usuário
-- Suponha que o usuário digitou 'Pedro'

Olá, Pedro! Seja bem-vindo ao mundo do PL/SQL!

Ou podemos usar o procedimento em outro procedimento, para compor ações mais complexas:

-- Vamos criar um procedimento que solicita o nome e a idade do usuário, e exibe uma mensagem de boas-vindas e uma mensagem informando se o usuário é maior ou menor de idade

CREATE PROCEDURE p_cadastro
IS
  v_nome VARCHAR2(50);
  v_idade NUMBER;
BEGIN
  v_nome := '&nome';
  v_idade := '&idade';
  p_boas_vindas(v_nome);
  IF v_idade >= 18 THEN
    DBMS_OUTPUT.PUT_LINE('Você é maior de idade.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Você é menor de idade.');
  END IF;
END p_cadastro;

Como você pode ver, os procedimentos são unidades de programa que realizam ações específicas, mas não retornam valores. Os procedimentos são usados para executar tarefas que não dependem de um resultado, mas que podem alterar o estado do banco de dados ou do programa. Os procedimentos também facilitam a reutilização do código, pois você pode definir uma vez e usar várias vezes, sem precisar repetir a ação. Além disso, os procedimentos melhoram a legibilidade e a manutenção do código, pois você pode dar nomes significativos aos seus procedimentos, e alterar a definição de um procedimento sem afetar os outros programas que o chamam.

Pacotes (Packages)

Os pacotes são coleções de funções, procedimentos, variáveis, constantes, tipos, cursores e exceções relacionados, que são agrupados em uma única unidade lógica. Os pacotes permitem que você organize o seu código em categorias funcionais, que podem ser compartilhadas por vários programas. Os pacotes também melhoram o desempenho, a segurança e a modularidade do seu código, pois você pode controlar a visibilidade e a persistência dos seus objetos.

Vamos ver um exemplo de como criar e usar um pacote que contém funções e procedimentos relacionados ao cálculo de áreas de figuras geométricas. Para criar o pacote, usamos o comando CREATE PACKAGE, especificando o nome do pacote e a sua especificação. A especificação do pacote é a parte que define os objetos que fazem parte do pacote, e que podem ser acessados por outros programas. No nosso caso, vamos definir quatro funções que calculam as áreas de um círculo, de um quadrado, de um retângulo e de um triângulo, e um procedimento que imprime o resultado de uma dessas funções, de acordo com o tipo da figura. Veja o código abaixo:

CREATE PACKAGE pkg_areas
IS
  -- Constante que representa o valor de pi

  c_pi CONSTANT NUMBER := 3.14159;
  
  -- Função que calcula a área de um círculo, dado o raio

  FUNCTION f_area_circulo (p_raio NUMBER) RETURN NUMBER;
  
  -- Função que calcula a área de um quadrado, dado o lado

  FUNCTION f_area_quadrado (p_lado NUMBER) RETURN NUMBER;
  
  -- Função que calcula a área de um retângulo, dados o comprimento e a largura

  FUNCTION f_area_retangulo (p_comprimento NUMBER, p_largura NUMBER) RETURN NUMBER;
  
  -- Função que calcula a área de um triângulo, dados a base e a altura

  FUNCTION f_area_triangulo (p_base NUMBER, p_altura NUMBER) RETURN NUMBER;
  
  -- Procedimento que imprime a área de uma figura, dado o tipo e os parâmetros da figura

  PROCEDURE p_imprime_area (p_tipo VARCHAR2, p_parametros NUMBER);
END pkg_areas;

Para implementar o pacote, usamos o comando CREATE PACKAGE BODY, especificando o nome do pacote e o seu corpo. O corpo do pacote é a parte que contém o código que realiza as ações definidas na especificação do pacote. No nosso caso, vamos escrever o código das quatro funções e do procedimento, usando as fórmulas matemáticas para calcular as áreas das figuras. Veja o código abaixo:

CREATE PACKAGE BODY pkg_areas
IS
  -- Função que calcula a área de um círculo, dado o raio

  FUNCTION f_area_circulo (p_raio NUMBER) RETURN NUMBER
  IS
    v_area NUMBER;
  BEGIN
    v_area := c_pi * p_raio * p_raio;
    RETURN v_area;
  END f_area_circulo;
  
  – Função que calcula a área de um quadrado, dado o lado 

FUNCTION f_area_quadrado (p_lado NUMBER) RETURN NUMBER 
IS
  v_area NUMBER; 
BEGIN 
  v_area := p_lado * p_lado; 
  RETURN v_area; 
END f_area_quadrado;

– Função que calcula a área de um retângulo, dados o comprimento e a largura 

FUNCTION f_area_retangulo (p_comprimento NUMBER, p_largura NUMBER) RETURN NUMBER 
IS
  v_area NUMBER; 
BEGIN
  v_area := p_comprimento * p_largura; 
  RETURN v_area; 
END f_area_retangulo;

– Função que calcula a área de um triângulo, dados a base e a altura 

FUNCTION f_area_triangulo (p_base NUMBER, p_altura NUMBER) RETURN NUMBER 
IS
  v_area NUMBER; 
BEGIN
  v_area := p_base * p_altura / 2; 
  RETURN v_area; 
END f_area_triangulo;

– Procedimento que imprime a área de uma figura, dado o tipo e os parâmetros da figura 

PROCEDURE p_imprime_area (p_tipo VARCHAR2, p_parametros NUMBER) 
IS
  v_area NUMBER; 
BEGIN
  – Verifica o tipo da figura e chama a função correspondente 

CASE p_tipo WHEN ‘CIRCULO’ THEN
  v_area := f_area_circulo(p_parametros(1));
WHEN ‘QUADRADO’ THEN
  v_area := f_area_quadrado(p_parametros(1)); 
WHEN ‘RETANGULO’ THEN
  v_area := f_area_retangulo(p_parametros(1), p_parametros(2)); 
WHEN ‘TRIANGULO’ THEN
  v_area := f_area_triangulo(p_parametros(1), p_parametros(2)); 
ELSE
  RAISE_APPLICATION_ERROR(-20001, ‘Tipo de figura inválido.’); 
END CASE;

 – Imprime a área da figura 

DBMS_OUTPUT.PUT_LINE('A área do ’ || p_tipo || ’ é ’ || v_area); 
END p_imprime_area;
END pkg_areas; 

Para usar o pacote, podemos chamá-lo de outro bloco de código, usando o nome do pacote e o nome do objeto que queremos acessar, separados por um ponto. Por exemplo, podemos usar as funções do pacote em uma cláusula SELECT, para exibir as áreas de algumas figuras:

SELECT pkg_areas.f_area_circulo(2) AS area_circulo,
       pkg_areas.f_area_quadrado(3) AS area_quadrado,
       pkg_areas.f_area_retangulo(4, 5) AS area_retangulo,
       pkg_areas.f_area_triangulo(6, 7) AS area_triangulo
FROM DUAL;

-- Resultado: exibe as áreas das figuras

AREA_CIRCULO AREA_QUADRADO AREA_RETANGULO AREA_TRIANGULO
------------ ------------- -------------- --------------
12.56636     9             20             21

Ou podemos usar o procedimento do pacote em um bloco anônimo, para imprimir as áreas de algumas figuras:

BEGIN
  pkg_areas.p_imprime_area('CIRCULO', 2);
  pkg_areas.p_imprime_area('QUADRADO', 3);
  pkg_areas.p_imprime_area('RETANGULO', 4, 5);
  pkg_areas.p_imprime_area('TRIANGULO', 6, 7);
END;
/

-- Resultado: imprime as áreas das figuras

A área do CIRCULO é 12.56636
A área do QUADRADO é 9
A área do RETANGULO é 20
A área do TRIANGULO é 21

Como você pode ver, os pacotes são unidades de programa que contêm coleções de objetos relacionados, que podem ser compartilhados por vários programas. Os pacotes permitem que você organize o seu código em categorias funcionais, que facilitam a localização e o uso dos seus objetos. Os pacotes também melhoram o desempenho, a segurança e a modularidade do seu código, pois você pode controlar a visibilidade e a persistência dos seus objetos, e alterar a implementação de um pacote sem afetar os outros programas que o usam.

Gatilhos (Triggers)

Os gatilhos são unidades de programa que são executadas automaticamente quando um evento específico ocorre no banco de dados, como uma operação de DML (Data Manipulation Language), uma operação de DDL (Data Definition Language), um erro, uma conexão ou uma desconexão. Os gatilhos permitem que você implemente regras de negócios, auditorias, restrições de integridade ou outras ações que dependem do estado do banco de dados.

Vamos ver um exemplo de como criar e usar um gatilho que registra as operações de inserção, atualização e exclusão em uma tabela de clientes, em uma tabela de auditoria. Para criar o gatilho, usamos o comando CREATE TRIGGER, especificando o nome do gatilho, o tipo do gatilho, o evento que o ativa, e o código que realiza a ação. No nosso caso, vamos criar um gatilho do tipo AFTER EACH ROW, que é ativado após cada operação de DML na tabela de clientes, e que insere um registro na tabela de auditoria, informando o tipo da operação, a data e a hora, e os dados do cliente afetado. Veja o código abaixo:

-- Suponha que temos uma tabela chamada clientes, com as colunas id, nome, email e telefone

CREATE TABLE clientes (
  id NUMBER PRIMARY KEY,
  nome VARCHAR2(50) NOT NULL,
  email VARCHAR2(50) NOT NULL,
  telefone VARCHAR2(20) NOT NULL
);

-- Suponha que temos uma tabela chamada auditoria, com as colunas operacao, data_hora, id_cliente, nome_cliente, email_cliente e telefone_cliente

CREATE TABLE auditoria (
  operacao VARCHAR2(10) NOT NULL,
  data_hora DATE NOT NULL,
  id_cliente NUMBER,
  nome_cliente VARCHAR2(50),
  email_cliente VARCHAR2(50),
  telefone_cliente VARCHAR2(20)
);

-- Criamos o gatilho que registra as operações de DML na tabela de clientes

CREATE TRIGGER trg_auditoria_clientes
AFTER INSERT OR UPDATE OR DELETE ON clientes
FOR EACH ROW
IS
BEGIN
  -- Verifica o tipo da operação e insere um registro na tabela de auditoria
  IF INSERTING THEN
    INSERT INTO auditoria VALUES ('INSERT', SYSDATE, :NEW.id, :NEW.nome, :NEW.email, :NEW.telefone);
  ELSIF UPDATING THEN
    INSERT INTO auditoria VALUES ('UPDATE', SYSDATE, :NEW.id, :NEW.nome, :NEW.email, :NEW.telefone);
  ELSIF DELETING THEN
    INSERT INTO auditoria VALUES ('DELETE', SYSDATE, :OLD.id, :OLD.nome, :OLD.email, :OLD.telefone);
  END IF;
END trg_auditoria_clientes;

Para usar o gatilho, basta executar as operações de DML na tabela de clientes, e verificar os registros na tabela de auditoria. Por exemplo, vamos inserir, atualizar e deletar alguns clientes, e ver o que acontece na tabela de auditoria:

-- Insere alguns clientes na tabela de clientes

INSERT INTO clientes VALUES (1, 'Ana', 'ana@gmail.com', '11-1111-1111');
INSERT INTO clientes VALUES (2, 'Bruno', 'bruno@gmail.com', '22-2222-2222');
INSERT INTO clientes VALUES (3, 'Carla', 'carla@gmail.com', '33-3333-3333');

-- Atualiza o email do cliente 2 na tabela de clientes

UPDATE clientes SET email = 'bruno@hotmail.com' WHERE id = 2;

-- Deleta o cliente 3 na tabela de clientes

DELETE FROM clientes WHERE id = 3;

-- Consulta a tabela de auditoria

SELECT * FROM auditoria;

-- Resultado: exibe os registros da tabela de auditoria

OPERACAO DATA_HORA           ID_CLIENTE NOME_CLIENTE EMAIL_CLIENTE     TELEFONE_CLIENTE
-------- ------------------- ---------- ------------ ------------------ ----------------
INSERT   2021-10-15 10:00:00 1          Ana          ana@gmail.com      11-1111-1111
INSERT   2021-10-15 10:01:00 2          Bruno        bruno@gmail.com    22-2222-2222
INSERT   2021-10-15 10:02:00 3          Carla        carla@gmail.com    33-3333-3333
UPDATE   2021-10-15 10:03:00 2          Bruno        bruno@hotmail.com  22-2222-2222
DELETE   2021-10-15 10:04:00 3          Carla        carla@gmail.com    33-3333-3333
``

Como você pode ver, os gatilhos são unidades de programa que são executadas automaticamente quando um evento específico ocorre no banco de dados. Os gatilhos permitem que você implemente regras de negócios, auditorias, restrições de integridade ou outras ações que dependem do estado do banco de dados.

Espero que este artigo tenha sido útil e divertido para você. Se você gostou, compartilhe com seus amigos e colegas que também querem aprender mais sobre PL/SQL.

Valeu !

Referências

• Oracle Database PL/SQL Language Reference