Pacote Oracle PL / SQL: Tipo, Especificação, Corpo [Exemplo]

O que é pacote no Oracle?

O pacote PL / SQL é um agrupamento lógico de um subprograma relacionado (procedimento / função) em um único elemento. Um pacote é compilado e armazenado como um objeto de banco de dados que pode ser usado posteriormente.

Neste tutorial, você aprenderá-

Componentes de Pacotes

O pacote PL / SQL possui dois componentes.

  • Especificação do Pacote
  • Corpo da embalagem

Especificação do Pacote

A especificação do pacote consiste em uma declaração de todas as variáveis ​​públicas, cursores, objetos, procedimentos, funções e exceção.

Abaixo estão algumas características da especificação do pacote.

  • Os elementos que são declarados na especificação podem ser acessados ​​de fora do pacote. Esses elementos são conhecidos como elementos públicos.
  • A especificação do pacote é um elemento autônomo, o que significa que pode existir sozinho sem o corpo do pacote.
  • Sempre que um pacote faz referência, uma instância do pacote é criada para essa sessão específica.
  • Depois que a instância é criada para uma sessão, todos os elementos do pacote que são iniciados nessa instância são válidos até o final da sessão.

Sintaxe

CREATE [OR REPLACE] PACKAGE IS . . END 

A sintaxe acima mostra a criação da especificação do pacote.

Corpo da embalagem

Consiste na definição de todos os elementos que estão presentes na especificação da embalagem. Ele também pode ter uma definição de elementos que não estão declarados na especificação, esses elementos são chamados de elementos privados e podem ser chamados apenas de dentro do pacote.

Abaixo estão as características de um corpo de pacote.

  • Ele deve conter definições para todos os subprogramas / cursores que foram declarados na especificação.
  • Ele também pode ter mais subprogramas ou outros elementos que não são declarados na especificação. Esses são chamados de elementos privados.
  • É um objeto confiável e depende da especificação do pacote.
  • O estado do corpo do pacote torna-se 'inválido' sempre que a especificação é compilada. Portanto, ele precisa ser recompilado a cada vez após a compilação da especificação.
  • Os elementos privados devem ser definidos primeiro antes de serem usados ​​no corpo do pacote.
  • A primeira parte do pacote é a parte da declaração global. Isso inclui variáveis, cursores e elementos privados (declaração de encaminhamento) que são visíveis para todo o pacote.
  • A última parte do pacote é a parte de inicialização do pacote que é executada uma vez sempre que um pacote é referido pela primeira vez na sessão.

Sintaxe:

CREATE [OR REPLACE] PACKAGE BODY IS . END 
  • A sintaxe acima mostra a criação do corpo do pacote.

Agora veremos como referir-se a elementos de pacote no programa.

Elementos de pacote de referência

Uma vez que os elementos são declarados e definidos no pacote, precisamos consultar os elementos para usá-los.

Todos os elementos públicos do pacote podem ser referidos chamando o nome do pacote seguido pelo nome do elemento separado por um ponto, ou seja, '.'.

A variável pública do pacote também pode ser usada da mesma maneira para atribuir e buscar valores deles, por exemplo, '.'.

Criar pacote em PL / SQL

Em PL / SQL, sempre que um pacote é referido / chamado em uma sessão, uma nova instância será criada para esse pacote.

O Oracle fornece um recurso para inicializar elementos de pacote ou para executar qualquer atividade no momento da criação desta instância por meio de 'Inicialização de pacote'.

Isso nada mais é do que um bloco de execução escrito no corpo do pacote após a definição de todos os elementos do pacote. Este bloco será executado sempre que um pacote for referido pela primeira vez na sessão.

Sintaxe

CREATE [OR REPLACE] PACKAGE BODY IS . BEGINE END 
  • A sintaxe acima mostra a definição de inicialização do pacote no corpo do pacote.

Declarações para a frente

A declaração / referência de encaminhamento no pacote nada mais é do que declarar os elementos privados separadamente e defini-los na parte posterior do corpo do pacote.

Os elementos privados podem ser referenciados apenas se já estiverem declarados no corpo do pacote. Por esse motivo, a declaração de encaminhamento é usada. Mas é bastante incomum de usar porque na maioria das vezes os elementos privados são declarados e definidos na primeira parte do corpo do pacote.

A declaração de encaminhamento é uma opção fornecida pela Oracle, não é obrigatória e o uso e não uso fica a critério do programador.

Sintaxe:

 CREATE [OR REPLACE] PACKAGE BODY IS . . . . . . BEGIN ; END 

A sintaxe acima mostra a declaração direta. Os elementos privados são declarados separadamente na parte anterior do pacote e foram definidos na parte posterior.

Uso de cursores no pacote

Ao contrário de outros elementos, é necessário ter cuidado ao usar cursores dentro do pacote.

Se o cursor estiver definido na especificação do pacote ou na parte global do corpo do pacote, o cursor, uma vez aberto, persistirá até o final da sessão.

Portanto, deve-se sempre usar os atributos do cursor '% ISOPEN' para verificar o estado do cursor antes de referenciá-lo.

Sobrecarregando

Sobrecarga é o conceito de ter muitos subprogramas com o mesmo nome. Esses subprogramas serão diferentes uns dos outros por uma série de parâmetros ou tipos de parâmetros ou tipo de retorno, ou seja, subprograma com o mesmo nome, mas com número diferente de parâmetros, diferentes tipos de parâmetros ou diferentes tipos de redefinição são considerados como sobrecarga.

Isso é útil quando muitos subprogramas precisam fazer a mesma tarefa, mas a forma de chamar cada um deles deve ser diferente. Neste caso, o nome do subprograma será mantido o mesmo para todos e os parâmetros serão alterados de acordo com a instrução de chamada.

Exemplo 1 : Neste exemplo, vamos criar um pacote para obter e definir os valores das informações do funcionário na tabela 'emp'. A função get_record retornará a saída do tipo de registro para o número de funcionário fornecido, e o procedimento set_record irá inserir o registro do tipo de registro na tabela emp.

Passo 1) Criação de Especificação de Pacote

CREATE OR REPLACE PACKAGE on2vhf_get_set IS PROCEDURE set_record (p_emp_rec IN emp%ROWTYPE); FUNCTION get record (p_emp no IN NUMBER) RETURN emp%ROWTYPE; END on2vhf_get_set: /

Saída:

 Package created 

Explicação do código

  • Linha de código 1-5 : Criando a especificação do pacote para on2vhf_get_set com um procedimento e uma função. Esses dois são agora elementos públicos deste pacote.

Passo 2) O pacote contém o corpo do pacote, onde todos os procedimentos e definições reais de funções serão definidos. Nesta etapa, o Corpo do pacote é criado.

 CREATE OR REPLACE PACKAGE BODY on2vhf_get_set IS PROCEDURE set_record(p_emp_rec IN emp%ROWTYPE) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO emp VALUES(p_emp_rec.emp_name,p_emp_rec.emp_no; p_emp_rec.salary,p_emp_rec.manager); COMMIT; END set_record; FUNCTION get_record(p_emp_no IN NUMBER) RETURN emp%ROWTYPE IS l_emp_rec emp%ROWTYPE; BEGIN SELECT * INTO l_emp_rec FROM emp where emp_no=p_emp_no RETURN l_emp_rec; END get_record; BEGUN dbms_output.put_line(‘Control is now executing the package initialization part'); END on2vhf_get_set: / 

Saída:

 Package body created 

Explicação do código

  • Linha de código 7 : Criando o corpo da embalagem.
  • Linha de código 9-16 : Definindo o elemento 'set_record' que é declarado na especificação. Isso é o mesmo que definir o procedimento independente em PL / SQL.
  • Linha de código 17-24: Definindo o elemento 'get_record'. É o mesmo que definir a função autônoma.
  • Linha de código 25-26: Definindo a parte de inicialização do pacote.

Etapa 3) Criação de um bloco anônimo para inserir e exibir os registros, referindo-se ao pacote criado acima.

 DECLARE l_emp_rec emp%ROWTYPE; l_get_rec emp%ROWTYPE; BEGIN dbms output.put line(‘Insert new record for employee 1004'); l_emp_rec.emp_no:=l004; l_emp_rec.emp_name:='CCC'; l_emp_rec.salary~20000; l_emp_rec.manager:=’BBB’; on2vhf_get_set.set_record(1_emp_rec); dbms_output.put_line(‘Record inserted'); dbms output.put line(‘Calling get function to display the inserted record'): l_get_rec:=on2vhf_get_set.get_record(1004); dbms_output.put_line(‘Employee name: ‘||l_get_rec.emp_name); dbms_output.put_line(‘Employee number:‘||l_get_rec.emp_no); dbms_output.put_line(‘Employee salary:‘||l_get_rec.salary'); dbms output.put line(‘Employee manager:‘||1_get_rec.manager); END: / 

Saída:

 Insert new record for employee 1004 Control is now executing the package initialization part Record inserted Calling get function to display the inserted record Employee name: CCC Employee number: 1004 Employee salary: 20000 Employee manager: BBB 

Explicação do código:

  • Linha de código 34-37: Preencher os dados da variável de tipo de registro em um bloco anônimo para chamar o elemento 'set_record' do pacote.
  • Linha de código 38: A chamada foi feita para 'set_record' do pacote on2vhf_get_set. Agora o pacote está instanciado e irá persistir até o final da sessão.
  • A parte de inicialização do pacote é executada, pois esta é a primeira chamada para o pacote.
  • O registro é inserido pelo elemento 'set_record' na tabela.
  • Linha de código 41: Chamar o elemento 'get_record' para exibir os detalhes do funcionário inserido.
  • O pacote é referido pela segunda vez durante a chamada 'get_record' para o pacote. Mas a parte de inicialização não é executada desta vez porque o pacote já foi inicializado nesta sessão.
  • Linha de código 42-45: Imprimindo os detalhes do funcionário.

Dependência em pacotes

Como o pacote é o agrupamento lógico de coisas relacionadas, ele possui algumas dependências. A seguir estão as dependências que devem ser cuidadas.

  • Uma especificação é um objeto autônomo.
  • O corpo do pacote depende da especificação.
  • O corpo do pacote pode ser compilado separadamente. Sempre que a especificação é compilada, o corpo precisa ser recompilado, pois se tornará inválido.
  • O subprograma no corpo do pacote que depende de um elemento privado deve ser definido somente após a declaração do elemento privado.
  • Os objetos de banco de dados que são referidos na especificação e no corpo precisam estar em status válido no momento da compilação do pacote.

Informação do pacote

Depois que as informações do pacote são criadas, as informações do pacote, como origem do pacote, detalhes do subprograma e detalhes de sobrecarga, estão disponíveis nas tabelas de definição de dados Oracle.

A tabela abaixo fornece a tabela de definição de dados e as informações do pacote que estão disponíveis na tabela.

Nome da tabela Descrição Consulta
ALL_OBJECTDá os detalhes do pacote como object_id, creation_date, last_ddl_time, etc. Ele conterá os objetos criados por todos os usuários.SELECT * FROM all_objects onde object_name = ''
USER_OBJECTDá os detalhes do pacote como object_id, creation_date, last_ddl_time, etc. Ele conterá os objetos criados pelo usuário atual.SELECT * FROM user_objects onde object_name = ''
ALL_SOURCEFornece a origem dos objetos criados por todos os usuários.SELECT * FROM all_source onde name = ''
USER_SOURCEFornece a origem dos objetos criados pelo usuário atual.SELECT * FROM user_source onde name = ''
ALL_PROCEDURESDá os detalhes do subprograma, como object_id, detalhes de sobrecarga, etc. criados por todos os usuários.SELECT * FROM all_procedures Onde object_name = ''
USER_PROCEDURESFornece os detalhes do subprograma como object_id, detalhes de sobrecarga, etc. criados pelo usuário atual.SELECT * FROM user_procedures Onde object_name = ''

UTL FILE - Uma Visão Geral

O arquivo UTL é o pacote de utilitário separado fornecido pela Oracle para executar tarefas especiais. Isso é usado principalmente para ler e gravar os arquivos do sistema operacional de pacotes ou subprogramas PL / SQL. Ele tem as funções separadas para colocar as informações e obter as informações dos arquivos. Também permite ler / escrever no conjunto de caracteres nativos.

O programador pode usar isso para gravar arquivos do sistema operacional de qualquer tipo e o arquivo será gravado diretamente no servidor de banco de dados. O nome e o caminho do diretório serão mencionados no momento da escrita.

Resumo

Agora aprendemos os pacotes em PL / SQL e agora você deve ser capaz de trabalhar com o seguinte.

  • Pacotes PL / SQL e seus componentes
  • Características das embalagens
  • Referência e sobrecarga de elementos do pacote
  • Gerenciando dependências em pacotes
  • Visualizando informações do pacote
  • O que é arquivo UTL