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;