Table of contents
The advantages of the package can be summarized as follows:
A package consists of two separate parts:
When creating a package body, there are a few things to keep in mind:
Syntax for creating a package:
Syntax for creating a package body:
Description of package definitions
Introduction to the package:
A package (PACKAGE, package for short) is a combination of PL/SQL programming elements such as a group of related procedures, functions, variables, constants, and cursors, which are stored in the database as a complete unit, and the package is identified by a name. It has the characteristics of object-oriented programming language and is the encapsulation of these PL/SQL programming elements. Packages are similar to classes in c# and JAVA languages, where variables are equivalent to member variables in the class, and procedures and functions are equivalent to class methods. Classifying related modules into packages enables developers to develop stored procedures using object-oriented methods, thereby improving system performance.
Like classes in high-level languages, program elements in packages are also divided into public elements and private elements. The difference between these two elements is that they allow access to different program scopes, that is, their scopes are different. Public elements can be called not only by functions and procedures in the package, but also by PL/SQL programs outside the package, while private elements can only be accessed by functions and procedures in the package.
Of course, procedures and functions that are not included in the package exist independently. Generally, independent procedures and functions are written first, and after they are relatively complete or fully verified, they are organized into packages according to their logical dependencies.
Advantages of the package
Simplify application design:
The description part and the package body part of the package can be created separately for each compilation. It is mainly reflected in the following three aspects:
- When designing an application, you can create only the description section of each compiled package, and then write the PL/SQL blocks that reference the package.
- When the overall framework of the entire application is completed, come back to define the package body part. As long as the package description section is not changed, the contents of the package body can be individually debugged, added or replaced without affecting other applications.
- After updating the description of the package, the application of the referenced package must be recompiled, but if the package body is updated, the application of the referenced package does not need to be recompiled, so that the prototype of the application can be rapidly developed.
Modular:
Logically related PL/SQL blocks or elements, etc. can be grouped together to uniquely identify a package with a name. Divide a large functional module into an appropriate number of small functional modules to complete their respective functions. Packages organized in this way are easier to write, easier to understand, and easier to manage.
Information hiding:
Because the elements in the package can be divided into public elements and private elements. Public elements can be accessed by procedures, functions, etc. within the package, as well as by PL/SQL outside the package. But private elements can only be accessed by procedures, functions, etc. within the package. For users, only need to know the description of the package, without knowing the specific details of the package.
efficient:
Package When the application calls an element in the package for the first time, ORACLE will load the entire package into memory, and when the element in the package is accessed for the second time, ORACLE will directly read from the inner, There is no need to perform disk I/O operations to affect the speed, and the packages located in the inner can be shared by other applications during the same session. Thus, packages increase reusability and improve efficiency in a multi-user, multi-application environment.
The advantages of the package can be summarized as follows:
In PL/SQL programming, the use of packages can not only modularize the program design, but also hide the information used in the package from the outside (by using private variables), while writing can improve the execution efficiency of the program. Because, when the program calls the function or procedure in the package for the first time, ORACLE transfers the entire package into memory, and when the element in the package is accessed again, ORACLE directly reads from the memory without performing disk I/O operations, thus making the program Execution efficiency is improved.
A package consists of two separate parts:
Package description (PACKAGE):
The package description part declares elements such as data types, variables, constants, cursors, subroutines, and exception error handling in the package, which are public elements of the package.
PACKAGE BODY:
The package body is the specific implementation of the package definition part, which defines the cursors and subprograms declared in the package definition part, and the private elements of the package can also be declared in the package body.
The package description and the package body are compiled separately and stored in the database dictionary as two separate objects. You can view the data dictionary user_source, all_source, dba_source to understand the detailed information of the package description and the package body respectively.
Package definition:
The definition of the package is divided into two parts: the definition of the package description and the definition of the package body.
Package descriptions are used to declare common components of the package, such as variables, constants, custom data types, exceptions, procedures, functions, cursors, and so on. The public components defined in the package specification can be used not only within the package, but also by other procedures and functions outside the package. However, it should be noted and noted that, in order to achieve information hiding, it is recommended not to declare all components in the package description, and only public components should be placed in the package declaration part. The name of the package is unique, but the names of public components in two packages can be the same, which is distinguished by "package name.public component name".
The package body is the specific implementation details of the package, which are actually all public procedures, functions, cursors, etc. declared in the package description. Of course, you can also declare your own private procedures, functions, cursors, etc. in the package body.
When creating a package body, there are a few things to keep in mind:
① A package body can only be created or compiled after the package specification has been created or compiled.
② The names of procedures, functions, and cursors implemented in the package body must be consistent with the procedures, functions, and cursors in the package description, including names, parameter names, and parameter modes (IN, OUT, IN OUT). And the construction defines the specific implementation in the package body according to the order in the package description.
③ The data types, variables, and constants declared in the package body are private and can only be used in the package body and cannot be accessed and used by applications outside the print.
④ In the execution part of the package body, the package description, the public or private variables declared in the package body can be initialized or other settings can be performed.
Syntax for creating a package:
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
[public data type definition[public data type definition]...]
[public cursor declaration[public cursor declaration]...]
[Public variable, constant declaration[Public variable, constant declaration]...]
[public function declaration[public function declaration]...]
[Public Process Statement[Public Process Statement]...]
END [package_name];Syntax for creating a package body:
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
[Private Data Type Definition[Private Data Type Definition]...]
[Private variable, constant declaration[Private variable, constant declaration]...]
[private exception error declaration[private exception error declaration]...]
[Private function declarations and definitions[Private function declarations and definitions]...]
[Private function declaration and definition[Private function declaration and definition]...]
[public cursor definition[public cursor definition]...]
[public function definition[public function definition]...]
[public process definition[public process definition]...]
BEGIN
executive part(initialization part)
END package_name;Description of package definitions
example 1:
The created package is demo_pkg, which contains a record variable deptrec, two functions and a procedure. Realize the addition, deletion and query of the dept table.
create or replace package demo_pkg
is
deptrec dept%rowtype;
--add dept...
function add_dept(
dept_no number,
dept_name varchar2,
location varchar2)
return number;
--delete dept...
function delete_dept(dept_no number)
return number;
--query dept...
procedure query_dept(dept_no in number);
end demo_pkg;How to create a package body
It implements the package definition declared above, and declares a private variable flag and a private function check_dept in the package body. Since the check_dpet function needs to be called in functions such as add_dept and remove_dept, the check_dept function is first defined before the function is defined. declaration, this method of declaration is called forward declaration.
create or replace package body demo_pkg
is
function add_dept
(
dept_no number,
dept_name varchar2,
location varchar2
)
return number
is
empno_remaining exception; --custom exception
pragma exception_init(empno_remaining, -1);
/* -1 is the error code for violating the unique constraint */
begin
insert into dept values(dept_no, dept_name, location);
if sql%found then
return 1;
end if;
exception
when empno_remaining then
return 0;
when others then
return -1;
end add_dept;
function delete_dept(dept_no number)
return number
is
begin
delete from dept where deptno = dept_no;
if sql%found then
return 1;
else
return 0;
end if;
exception
when others then
return -1;
end delete_dept;
procedure query_dept
(dept_no in number)
is
begin
select * into deptrec from dept where deptno=dept_no;
exception
when no_data_found then
dbms_output.put_line('Kind tips:is not encoded in the database as'||dept_no||'department');
when too_many_rows then
dbms_output.put_line('program run error,Please use the cursor to operate!');
when others then
dbms_output.put_line(sqlcode||'----'||sqlerrm);
end query_dept;
begin
null;
end demo_pkg;Remove the package:
drop package name;