Artigos Banco de Dados Trabalhando com Packages PL/SQL

Um package é uma coleção de objetos PL/SQL agrupados logicamente sob o nome de pacote. Os packages podem incluir procedures, functions, cursores, declarações, tipos e também variáveis​​. Há inúmeras vantagens em coletar objetos em um package. Mais a frente serão vistos os benefícios de sua utilização.

Os packages geralmente possuem duas partes, uma que se refere a spec e a segunda parte que é o body. A spec é o que chamamos de interface para o package. É nela que declaramos os tipos, variáveis, constantes, exceções, cursores e subprogramas que poderão ser referenciados a partir de partes externas ao package. Por outro lado, no body definimos as consultas para os cursores e o código para os subprogramas. Em alguns casos pode ser que não haja a necessidade de se ter um body.

Em outras palavras, Package PL/SQL é um grupo de funções relacionadas, procedimentos, tipos, cursores, dentre outros. Os packages são como uma biblioteca, pois uma vez escrito é armazenado no banco de dados Oracle e pode ser usado por muitas aplicações.

De um certo modo, podemos imaginar a spec como sendo uma interface e o body como sendo uma caixa preta. Sabendo disso, podemos entender então que é possível depurar, aprimorar ou substituir bodies de um package sem precisar alterar as especificações do package. Para criar um spec de um package utilizamos a instrução SQL CREATE PACKAGE. E para a criação de um bod, utilizamos a instrução CREATE PACKAGE BODY.

A spec do package é a interface pública de suas aplicações. O público significa que a função armazenada, procedimentos, tipos, são acessíveis a partir de outras aplicações. Já o body contém o código que implementa as especificações do pacote.

O que podemos ter num package PL/SQL?

Vamos ver a seguir o que podemos definir em um pacote PL/SQL:

  • Podemos ter métodos get e set para as variáveis ​​do package.
  • Declaração de cursores com o texto da consulta SQL. Reutilizar exatamente o mesmo texto de consulta em vários locais se torna mais rápido do que digitar novamente a mesma consulta, com ligeiras diferenças. Torna-se também mais fácil de manter, se precisarmos mudar uma consulta que é usada em muitos lugares.
  • Declarações para exceções. Normalmente, precisamos ser capazes de referenciar estes a partir de diferentes procedures, de modo que podemos lidar com as exceções com chamadas de subprogramas.
  • Declarações para procedures e functions que chamam um ao outro. Não precisamos nos ​​preocupar com a ordem de compilação das procedures e functions “empacotadas”.
  • Declarações para procedimentos e funções de sobrecarga (overload). Podemos criar várias variações de uma procedure ou function usando os mesmos nomes, mas com diferentes conjuntos de parâmetros.
  • Podemos tratar variáveis num package como sendo variáveis globais.
  • Tipos de declarações para coleções de tipos PL/SQL. Para passar uma coleção, como um parâmetro entre stored procedures ou functions, devemos declarar o tipo em um package. Deste modo, tanto a chamada quanto o chamado do subprograma podem ser referenciados.

Quais as vantagens de utilização de packages PL/SQL?

A utilização de packages tem uma longa história no que diz respeito a engenharia de softwares, oferecendo características importantes de fácil manutenção, reutilização de código, muitas vezes em esforços dentro de uma equipe de desenvolvimento para o desenvolvimento de grandes sistemas. Apresentaremos então alguns dos principais pontos vantajosos na utilização de packages.

  • Modularidade - Packages permitem encapsular tipos relacionados logicamente, itens e sub-programas em um módulo PL/SQL. Cada package fica fácil de entender, e as interfaces entre eles tornam-se mais simples, claras e bem definidas. Isso ajuda e muito no desenvolver de aplicativos.
  • Aplicação simples de design - Ao projetarmos um aplicativo, tudo que precisamos, inicialmente, é a informação de interface nas especificações da embalagem. Desta forma, podemos codificar e compilar uma especificação sem o seu corpo. Em seguida, os subprogramas armazenados que referenciam o pacote podem ser compilados também. De certa forma, não precisamos definir os corpos dos packgaes totalmente até que seja necessário.
  • Ocultação de informações - Com os packages, podemos especificar quais tipos, itens e sub-programas serão públicos (visíveis e acessíveis) ou privados (escondido e inacessível). Por exemplo, se um pacote contém quatro subprogramas, três poderão ser públicos e um deles privado. O package esconde a implementação de subprogramas privados para que somente o próprio package (e não a sua aplicação) seja afetado se houver mudanças de implementação. Isso simplifica a manutenção e o aprimoramento no desenvolvimento. Além disso, ao esconder os detalhes da implementação dos usuários, isso remete a proteger a integridade dos packages.
  • Melhor performance - Quando chamamos um subprograma empacotado pela primeira vez, todo o package é carregado na memória. O que quer dizer que quando se tem a necessidade de reutilização do package, ele será recuperado de maneira mais rápida e eficiente por estar em memória, não havendo a necessidade de busca no disco.

Os Packages param as dependências em cascata e evitam recompilações desnecessárias. Podemos pensar no seguinte exemplo, se alterarmos o body de uma function que está num package, o Oracle não irá recompilar outros subprogramas que chamam por esta function. Estes subprogramas só dependeram dos parâmetros e valores de retorno que serão declarados na especificação, então eles só são recompilados se houverem mudanças na spec e não quando as mudanças são no body.

Criando PL/SQL package specification

A especificação do package é necessário quando você cria um novo package. A especificação do package lista todos os objetos que são acessíveis ao público a partir de outras aplicações. A especificação do package também fornece as informações que os desenvolvedores precisam saber para usar a sua interface. Em suma, a especificação do package é a sua API.

Se a especificação do package não contém quaisquer funções armazenadas, procedures e nenhum código privado sendo necessário, então não precisamos ter um corpo para o package. Estes packages podem conter apenas as definições dos tipos e declaração de variáveis. Essas variáveis ​​são conhecidas como dados do pacote. O escopo dos dados é global para as aplicações. Devido a isso é necessário encapsular tudo que não seja necessário como público e fazer uso das funções gets e sets para ler e escrever os dados. Ao fazer isso, podemos evitar que os dados dos package sejam alterados de forma errônea.

De acordo com a Listagem 1, seguem as instruções de como criar um package sem o body.

Listagem 1. Criação do package sem o body, apenas com as specs

CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
   [definitions of public TYPES
   ,declarations of public variables, types, and objects
   ,declarations of exceptions
   ,pragmas
   ,declarations of cursors, procedures, and functions
   ,headers of procedures and functions]
END [package_name];

A instrução CREATE PACKAGE é usada para definir uma nova especificação do pacote. Caso queiramos reconstruir o pacote existente, podemos usar a palavra-chave REPLACE em vez de CREATE. Na especificação do pacote podemos definir novos tipos, declarar variáveis ​​globais, tipos, objetos, exceções, cursores, procedimentos e funções.

De acordo com a Listagem 2, temos um exemplo de criação de uma nova especificação de pacote chamado funcionário. O pacote de funcionário contém duas funções: get_nomeCompleto() e get_salario() com base na identificação do funcionário.

Listagem 2. Criação do package funcionário

CREATE OR REPLACE PACKAGE funcionario AS
  // get nome completo do funcionario
  FUNCTION get_nomeCompleto(n_func_id NUMBER)
    RETURN VARCHAR2;
  // get salario do funcionario
  FUNCTION get_salario(n_func_id NUMBER)
    RETURN NUMBER;
END funcionario;

Agora criaremos o corpo para o package, de acordo com a Listagem 3.

No body do package PL/SQL fica todo o código que implementa as funções armazenadas, procedures e cursores listados na especificação do package.

Listagem 3. Criação do body para o package funcionário

CREATE [OR REPLACE] PACKAGE BODY package_name
   { IS | AS }

   [definitions of private TYPEs
   ,declarations of private variables, types, and objects
   ,full definitions of cursors
   ,full definitions of procedures and functions]
[BEGIN
   sequence_of_statements

[EXCEPTION
   exception_handlers ] ]

END [package_name];

A sintaxe é similar à da criação da spec, exceto pela palavra-chave BODY e o código implementado das especificações do package.

O corpo da package pode conter uma seção de inicialização do pacote opcional que aparece no final do corpo de package. As seções de inicialização das packages começam com a palavra-chave BEGIN e terminam com a seção do EXCEPTION ou a palavra-chave END que finaliza o pacote. A seção de inicialização do pacote é executada quando o aplicativo faz referência ao elemento do package pela primeira vez.

Agora que apresentamos o body do package, faremos um exemplo completo para o package funcionário iniciado anteriormente. Apresentamos o nosso exemplo na Listagem 4.

Listagem 4. Criação do exemplo completo de uma package com spec e body

/*
  Package funcionario body
*/
CREATE OR REPLACE PACKAGE BODY funcionario AS
  // get funcionário nomeCompleto
  FUNCTION get_nomeCompleto(n_func_id NUMBER) RETURN VARCHAR2 IS
      v_nomeCompleto VARCHAR2(46);
  BEGIN
    SELECT primeiro_nome || ',' ||  ultimo_nome
    INTO v_nomeCompleto
    FROM funcionarios
    WHERE empresa_id = n_func_id;

    RETURN v_nomeCompleto;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  WHEN TOO_MANY_ROWS THEN
    RETURN NULL;
  END; // end get_nomeCompleto

   get salario
  FUNCTION get_salario(n_func_id NUMBER) RETURN NUMBER IS
    n_salario NUMBER(8,2);
  BEGIN
    SELECT salario
    INTO n_salario
    FROM funcionarios
    WHERE empresa_id = n_func_id;

    RETURN n_salario;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
      WHEN TOO_MANY_ROWS THEN
        RETURN NULL;
  END;
END funcionario;

Ainda falta chamarmos nosso package, que será visto na Listagem 6. Para fazermos referência a nosso package utilizamos a seguinte notação mostrada na Listagem 5.

Listagem 5. Chamada do package

package_name.package_element

Listagem 6. chamando o package funcionário

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_salario NUMBER(8,2);
  v_nome   VARCHAR2(46);
  n_func_id NUMBER := &empresa_id;
BEGIN

  v_nome   := funcionario.get_nomeCompleto(n_func_id);
  n_salario := funcionario.get_salario(n_func_id);

  IF v_nome  IS NOT NULL AND
    n_salario IS NOT NULL
  THEN
    dbms_output.put_line('Funcionário: ' || v_nome);
    dbms_output.put_line('Recebe salário = ' || n_salario);
  END IF;
END;