Creation and application of Pl-sql package

Tag: Type module Lin executes the program-oriented into main fine

??

Creation and application of Pl-sql package

① Brief Introduction
A package is a combination of related procedures, functions, variables, constants, and cursors, such as PL/SQL programming elements, which have the characteristics of object-oriented programming language. Is the encapsulation of 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 divided into common and private elements. The difference between the two elements is that they agree to the scope of the program, i.e., their scope is different.

Common elements can be called not only by the functions and procedures in the package, but also by a PL/SQL program outside the package. Private elements can only be accessed by the functions and procedures within the package.
In PL/SQL programming, the use of packages can not only make programming modular, but also hide the information used in the package (by using the private variables), and improve the efficiency of the program operation.
Because. When the program first invokes the in-package function or procedure, Oracle calls the entire package into memory, and Oracle reads it directly from memory when you revisit the inside element of the package. Without the need for disk I/O operations.
Thus, the efficiency of program operation 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 the package. These elements are the public elements of the package.
Package Body: The package body is a detailed implementation of the package Definition section, which defines the cursors and subroutines declared in the package Definition section, and also the ability to declare private elements of the package in the package body.
The package definition and the package body are compiled separately. And as two parts separate objects stored in the database dictionary, 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];
In: The AUTHID current_user and AUTHID definer options describe the permission mode that the application uses when invoking a function.
They act similarly to 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];
Middle: When the package body defines a public program. They must be completely consistent with the format of the subroutine that is declared in the package definition.

The invocation format for common-owning elements within a package is: Package name. element name

③ Sub-Program overloading
PL/SQL agrees to overload both the child and local sub-programs of the package.
Overloading refers to two or more sub-programs having the same name. However, they have different parameter variables, parameter sequences, or parameter data types.

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

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

Examples of ⑥:
The package created is Demo_pack, which includes 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 method by which the package body is created. It implements the package definition as stated 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 execution Error! Use cursors ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ----' | | SQLERRM);
END query_dept;
BEGIN
Null;
END Demo_pack;

Creation and application of Pl-sql package