Creation and application of Pl-sql package



Creation and application of Pl-sql package

① Introduction
A package is a combination of a set of PL/SQL programming elements, such as related procedures, functions, variables, constants, and cursors, which are characterized by an object-oriented programming language and are encapsulated in these PL/SQL programming elements.
Packages are similar to classes in the C + + and Java languages, where variables are equivalent to member variables in a class, and procedures and functions are equivalent to class methods.
By categorizing the relevant modules into packages, developers can use an object-oriented approach to develop stored procedures to improve system performance.
As with classes, the program elements in a package are also divided into common and private elements, which differ in the scope of the programs they allow to access, that is, their scope is different.
Common elements can be accessed 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 programs within the package.
In PL/SQL program design, the use of packages can not only make the programming modular, hide the information used in the package (by using the private variables), but also improve the execution efficiency of the program.
Because Oracle calls the entire package into memory when the program first invokes the in-package function or procedure, Oracle reads directly from memory when the elements inside the package are accessed again, without the need for disk I/O operations.
Thus, the efficiency of program execution is improved.

A package consists of two separate parts:
Package Definition: The package Definition section declares elements of data types, variables, constants, cursors, subroutines, and exception handling in a package that are public elements of a package.
Package Body: The package body is the specific implementation of the package Definition section, which defines the cursors and subroutines declared in the package Definition section, and the private elements of the package can also be declared in the package body.
The package definition and the package body are compiled separately and stored in the database dictionary as two separate objects, see Data Dictionary user_source, All_source, Dba_source.

Definition of ② Package
The syntax for package definitions is as follows:
1. Create the package definition:
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 variables, constant declarations [public variables, constant declarations] ...]
[Public Sub-Program declaration [public subroutine declaration] ...]
End[package_name];
Where: The AUTHID current_user and AUTHID definer options describe the permission mode that the application uses to invoke the function,
They work the same as the Invoker_right_clause clause in the CREATE FUNCTION statement.
To create a package body:
Create[or REPLACE] Package Bodypackage_name
{is| As}
[Private data type definition [private data type definition] ...]
[private variable, constant declaration [private variable, constant declaration] ...]
[Private subroutine declaration and definition [private subroutine declaration and definition] ...]
[Public cursor definition [public cursor definition] ...]
[Public Sub-program definition [public subroutine definition] ...]
BEGIN
PL/SQL statements
End[package_name];
Where: When a package body defines a public program, they must be in exactly the same format as the child program declared in the package definition.
The invocation format for common elements within a package is: Package name. element name

③ Sub-Program overloading
PL/SQL allows overloading of both the child and local sub-programs.
Overloading means that two or more sub-programs have the same name, but have different parameter variables, parameter order, or parameter data types.

④ Deleting a package
You can use the Drop Package command to delete unwanted packages with the following syntax:
DROP package [BODY] [user.] Package_name;

Management of ⑤ Package
Dba_source, User_source, User_errors, dba-objects

⑥ Example:
The package created is Demo_pack, which contains a record variable Deptrec, two functions, and a procedure.
CREATE OR REPLACE Package Demo_pack
Is
Deptrec Dept%rowtype;
FUNCTION add_dept (dept_no number, dept_name VARCHAR2, location VARCHAR2)
RETURN number;
FUNCTION remove_dept (dept_no number)
RETURN number;
PROCEDURE query_dept (dept_no in number);
END Demo_pack

The creation method of the package body, which implements the package definition declared above
CREATE OR REPLACE Package BODY Demo_pack
Is
--Function definition
FUNCTION add_dept (dept_no number, dept_name VARCHAR2, location VARCHAR2)
RETURN number
Is
Empno_remaining EXCEPTION;
PRAGMA Exception_init (empno_remaining,-1);
/*-1 is an error code that violates UNIQUE constraints */
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 definition
   function remove_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 and then
           RETURN-1;
   END remove_dept;

--Process definition
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 (' No Code in database ' | | dept_no| | ' Departments ');
When Too_many_rows Then
Dbms_output. Put_Line (' program run Error! Use cursor ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ----' | | SQLERRM);
END query_dept;
BEGIN
Null;
END Demo_pack;

Creation and application of Pl-sql package