Packages in Oracle

Basic concepts of a package

A package can organize several functions or stored procedures and store them as an object. A package usually consists of two parts: specification and body ). The program reports can also contain constants and variables. All functions and stored procedures in the package can use these variables or constants.

Ii. Specification

1. Create a specification (SQL window)

Create or replace package pkg_staff
StaffString varchar2 (500 );
StafftAge number: = 18;
Function get_staff_string return varchar2;
Procedure insert_staff (in_staff_id in number, in_staff_name in varchar2 );
Procedure update_staff (in_staff_id in number );
Procedure delete_staff (in_staff_id in number );
End pkg_staff;

2. view the package specification information in the data dictionary

Select object_name, object_type, status from user_objects
Where lower (OBJECT_NAME) = 'pkg _ staff'

Three Subjects

The so-called specification is like an interface in object-oriented programming. The subject of this specification must implement all the methods of this specification. Oracle will automatically look for a specification with the same name as the subject to see if all the functions or stored procedures have been implemented. If no, the compilation is incorrect.

1. Create a subject

Create or replace package body pkg_staff
Function get_staff_string return varchar2
Begin
Return 'staff ';
End get_staff_string;
Procedure insert_staff (in_staff_id in number, in_staff_name in varchar2)
Begin
Insert into staff values (in_staff_id, in_staff_name );
End insert_staff;
Procedure update_staff (in_staff_id in number)
Begin
Update staff set name = 'xy' where num = in_staff_id;
End update_staff;
Procedure delete_staff (in_staff_id in number)
Begin
Delete from staff where num = '1 ';
End delete_staff;
End pkg_staff;

2. view the information of the package owner in the data dictionary.

Select object_name, object_type, status from user_objects
Where lower (OBJECT_NAME) = 'pkg _ staff'

4. Call functions or stored procedures in the package

Call a function (SQL window)
Select pkg_staff.get_staff_string () as result from dual

Call a stored procedure (Command window)
Begin
Pkg_staff.delete_staff (1 );
End;
/