Create and apply PL/SQL packages

Create and apply PL/SQL packages

Create and apply PL/SQL packages

① Introduction
A package is a combination of PL/SQL programming elements, such as related processes, functions, variables, constants, and cursors. It has the characteristics of an object-oriented programming language, is the encapsulation of these PL/SQL program design elements.
The package is similar to the class in C ++ and JAVA. The variable is equivalent to the member variable in the class, and the process and function are equivalent to the class method.
By categorizing related modules into packages, developers can use the object-oriented method to develop stored procedures to improve system performance.
Similar to the class, program elements in the package are also divided into two types: public elements and private elements. The difference between the two elements is that they allow different program scopes, that is, they have different scopes.
Public elements can be called by functions and procedures in the package, and can also be accessed by PL/SQL programs outside the package. Private elements can only be accessed by functions and programs in the package.
In PL/SQL programming, packages can not only modularize the program design, but also hide the information used in the package (by using private variables), and improve the program execution efficiency.
Because, when the program calls a function or process in the package for the first time, Oracle transfers the entire package to the memory. When the package element is accessed again, ORACLE directly reads it from the memory, disk I/O operations are not required,
This improves the program execution efficiency.

A package consists of two separate parts:
PACKAGE: the PACKAGE definition part declares the data types, variables, constants, cursors, subprograms, and error handling elements in the PACKAGE. These elements are the public elements of the PACKAGE.
Package body: the package body is the specific implementation of the PACKAGE definition part. It defines the cursor and subroutine declared in the PACKAGE definition part, you can also declare the private elements of the package in the package body.
The package definition and package body are compiled separately and stored in the database dictionary as two separate objects. For details, see the data dictionary user_source, all_source, dba_source.

② Package Definition
The syntax of the package definition is as follows:
1. Create a package definition:
CREATE [or replace] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS |}
[Public Data Type Definition [Public Data Type Definition]…]
[Public cursor Declaration [Public cursor Declaration]…]
[Public variables and constant declarations [public variables and constant declarations]…]
[Public subroutine Declaration [Public subroutine Declaration]…]
END [package_name];
The options AUTHID CURRENT_USER and authid definer indicate the permission mode used by the application to call the function,
They serve the same purpose as the invoker_right_clause In the create function statement.
Create a package subject:
CREATE [or replace] PACKAGE BODYpackage_name
{IS |}
[Private Data Type Definition [Private Data Type Definition]…]
[Private variables, constant declarations [private variables, constant declarations]…]
[Private subroutine description and Definition [private subroutine description and definition]…]
[Public cursor Definition [Public cursor definition]…]
[Public subroutine Definition [Public subroutine definition]…]
BEGIN
PL/SQL statements
END [package_name];
Where: when the package subject defines a public program, they must be exactly the same as the format of the declared subroutine in the package definition.
The call format for common elements in the package is: package name. element name

③ Subroutine overload
PL/SQL allows you to reload subprograms in the package and local subprograms.
In overload mode, two or more subprograms have the same name but have different parameter variables, parameter sequence, or parameter data types.

④ Delete a package
You can use the drop package command to delete unnecessary packages. The syntax is as follows:
Drop package [BODY] [user.] package_name;

⑤ Package management
DBA_SOURCE, USER_SOURCE, USER_ERRORS, DBA-OBJECTS

6. Example:
The created package is demo_pack, which contains a record variable DeptRec, two functions, and a process.
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

How to Create a package subject. It implements the package definition 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 the error code that violates the 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 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 ('the database is not encoded as '| dept_no |' Department ');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('program running error! Use the cursor ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '----' | SQLERRM );
END query_dept;
BEGIN
Null;
END demo_pack;

Oracle -- plsql Composite data type

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------