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

O que é o pacote Oracle?

Oracle PL/SQL package is a logical grouping de relacionado subprogramas (procedure/function) into a single element. A package is compiled and stored as a database object that can be reused later.

Componentes de Pacotes

A PL/SQL package has two components.

• Especificação do Pacote
• Corpo do pacote

Especificação do Pacote

The package specification consists of a declaration of all the public variáveis, cursores, objects, procedures, functions, and exceções.

Below are a few characteristics of the package specification.

• The elements declared in the specification can be accessed from outside the package. Such elements are known as public elements.
• The package specification is a standalone element, which means it can exist alone without a package body.
• Whenever a package is referred to, an instance of the package is created for that particular session.
• Depois que a instância for criada para uma sessão, todos os elementos do pacote iniciados nessa instância serão válidos até o final da sessão.

Sintaxe

CREATE [OR REPLACE] PACKAGE <package_name> 
IS
<sub_program and public element declaration>
.
.
END <package name>

The above syntax shows the creation of the package specification.

Corpo do pacote

The package body consists of the definition of all the elements that are present in the package specification. It can also have definitions of elements that are not declared in the specification; these elements are called private elements and can be called only from inside the package.

Below are the characteristics of a package body.

• It should contain definitions for all the subprograms/cursors that have been declared in the specification.
• It can also have more subprograms or other elements that are not declared in the specification. These are called private elements.
• It is a dependent object, and it depends on the package specification.
• The state of the package body becomes ‘Invalid’ whenever the specification is compiled. Therefore, it needs to be recompiled each time after the compilation of the specification.
• Os elementos privados devem ser definidos primeiro, antes de serem usados ​​no corpo do pacote.
• The first part of the package body is the global declaration part. This includes variables, cursors, and private elements (forward declaration) that are visible to the entire package.
• The last part of the package is the package initialization part that executes one time whenever a package is referred to for the first time in the session.

Sintaxe:

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<global_declaration part>
<Private element definition>
<sub_program and public element definition>
.
<Package Initialization> 
END <package_name>

The above syntax shows the creation of the package body.

Now we are going to see how to refer to package elements in the program.

Referindo Elementos do Pacote

Once the elements are declared and defined in the package, we need to refer to the elements to use them.

All the public elements of the package can be referred to by calling the package name followed by the element name separated by a period, i.e. ‘<package_name>.<element_name>’.

The public variables of the package can also be used in the same way to assign and fetch values from them, i.e. ‘<package_name>.<variable_name>’.

Criar pacote em PL/SQL

In PL/SQL, whenever a package is referred to or called in a session, a new instance is created for that package.

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

This is nothing but an execution block that is written in the package body after defining all the package elements. This block will be executed whenever a package is referred to for the first time in the session.

The screenshot below shows how the package initialization block is placed inside the package body when creating a package.

Sintaxe

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<Private element definition>
<sub_program and public element definition>
.
BEGIN
<Package Initialization> 
END <package_name>

A sintaxe acima mostra a definição de inicialização do pacote no corpo do pacote.

Declarações para a frente

Forward declaration or reference in the package is nothing but declaring the private elements separately and defining them in the later part of the package body.

Private elements can be referred to only if they are already declared in the package body. For this reason, forward declaration is used. But it is rather unusual to use, because most of the time private elements are declared and defined in the first part of the package body.

A declaração futura é uma opção fornecida por Oracle. It is not mandatory, and using it or not is up to the programmer’s requirement.

The screenshot below shows how a private element is forward-declared and later defined in the package body.

Sintaxe:

CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<Private element declaration>
.
.
.
<Public element definition that refer the above private element>
.
.
<Private element definition> 
.
BEGIN
<package_initialization code>; 
END <package_name>

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

Unlike other elements, one needs to be careful when using cursors inside the package.

If the cursor is defined in the package specification or in the global part of the package body, then the cursor, once opened, will persist till the end of the session.

So one should always use the cursor attribute ‘%ISOPEN’ to verify the state of the cursor before referring to it.

Sobrecarregando

Overloading is the concept of having many subprograms with the same name. These subprograms differ from each other by the number of parameters, the types of parameters, or the return type. In other words, subprograms with the same name but with a different number of parameters, different types of parameters, or a different return type are considered overloading.

This is useful when many subprograms need to do the same task, but the way of calling each of them should be different. In this case, the subprogram name is kept the same for all, and the parameters are changed as per the calling statement.

1 exemplo: In this example, we are going to create a package to get and set the values of an employee’s information in the ’emp’ table. The get_record function will return the record type output for the given employee number, and the set_record procedure will insert the record type record into the emp table.

Step 1) Package Specification Creation

The screenshot below shows the guru99_get_set package specification being created in Oracle.

CREATE OR REPLACE PACKAGE guru99_get_set
IS
PROCEDURE set_record (p_emp_rec IN emp%ROWTYPE);
FUNCTION get_record (p_emp_no IN NUMBER) RETURN emp%ROWTYPE;
END guru99_get_set;
/

Saída:

Package created

Code Explicação

• Code linhas 1-5: Creating the package specification for guru99_get_set with one procedure and one function. These two are now public elements of this package.

Passo 2) The package contains a package body, where the actual definitions of all procedures and functions are defined. In this step, the package body is created.

The screenshot below shows the guru99_get_set package body definition in Oracle.

CREATE OR REPLACE PACKAGE BODY guru99_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;
BEGIN
dbms_output.put_line('Control is now executing the package initialization part');
END guru99_get_set;
/

Saída:

Package body created

Code Explicação

• Code linha 7: Creating the package body.
• Code linhas 9-16: Defining the element ‘set_record’ that is declared in the specification. This is the same as defining a standalone procedure in PL/SQL.
• Code linhas 17-24: Defining the element ‘get_record’. It is the same as defining a standalone function.
• Code linhas 25-26: Definindo a parte de inicialização do pacote.

Passo 3) Creating an anonymous block to insert and display the records by referring to the above-created package.

The screenshot below shows the anonymous block that calls the package, along with its output in Oracle.

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:=1004;
l_emp_rec.emp_name:='CCC';
l_emp_rec.salary:=20000;
l_emp_rec.manager:='BBB';
guru99_get_set.set_record(l_emp_rec);
dbms_output.put_line('Record inserted');
dbms_output.put_line('Calling get function to display the inserted record');
l_get_rec:=guru99_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:'||l_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

Code Explicação:

• Code linhas 34-37: Populating the data for the record type variable in an anonymous block to call the ‘set_record’ element of the package.
• Code linha 38: A call has been made to ‘set_record’ of the guru99_get_set package. Now the package is instantiated, and it will persist until the end of the session. The package initialization part is executed since this is the first call to the package, and the record is inserted by the ‘set_record’ element into the table.
• Code linha 41: Calling the ‘get_record’ element to display the details of the inserted employee. The package is referred to for the second time during this call, but the initialization part is not executed again, as the package is already initialized in this session.
• Code linhas 42-45: Imprimindo os dados do funcionário.

Dependência em Pacotes

Since the package is a logical grouping of related things, it has some dependencies. Following are the dependencies that are to be taken care of.

• A specification is a standalone object.
• A package body is dependent on the specification.
• The package body can be compiled separately. Whenever the specification is compiled, the body needs to be recompiled, as it will become invalid.
• The subprogram in the package body that is dependent on a private element should be defined only after the private element declaration.
• The database objects that are referred to in the specification and body need to be in valid status at the time of package compilation.

Informações do pacote

Once the package is created, the package information such as the package source, subprogram details, and overload details are available in the Oracle data dictionary tables.

The below table gives the data dictionary table and the package information that is available in each table.

UTL_FILE – An Overview

UTL_FILE is a separate utility package provided by Oracle to perform special tasks. It is mainly used for reading and writing operating system files from PL/SQL packages or subprograms. It has separate functions to put information into and get information from files. It also allows reading and writing in the native character set.

The programmer can use this to write operating system files of any type, and the file will be written directly to the database server. The name and directory path are mentioned at the time of writing.

Perguntas Frequentes