Encapsulate PL/SQL code in a smart package

Encapsulate PL/SQL code in a smart package

The vast majority of PL/SQL-based applications are composed of thousands or even millions of lines of code, which contains detailed and variable user requirements.
Commercial logic is initially implemented by stored procedures and functions, but developers need to consider maintaining these processes and functions in the package.

What is a package?
A package is a set of PL/SQL code elements (cursors, types, variables, processes, and functions.

It usually consists of a package Declaration (Object Declaration) and a package body (specific implementation.

Why use a package?
1) organize and maintain a set of functional objects;
2) Hide external implementations;
3) to improve performance:
When you call the package for the first time, the entire package is loaded into the memory. Next, call the same package element without additional disk I/O.
In addition, the package-level variables can be cached at the session-level to reduce the data read time.
4) Minimize Program unit re-Compilation
An external program (not defined in a package) can only call a subroutine in a package declaration. If you change and re-compile the package body, those external programs
Will not expire.

The charm of the package is shown below:

1. A simple package:
Suppose my employees table is defined as follows:

SQL> desc employeesName             Type————————————     —————————————EMPLOYEE_ID      NUMBER(38)FIRST_NAME       VARCHAR2(30)LAST_NAME        VARCHAR2(50)

Next I need to define a process_employee process and return the full name of the employee (last_name, first_name) for other
Program call.

Code Listing 1: The process_employee procedure

CREATE OR REPLACE PROCEDURE process_employee (   employee_id_in IN employees.employee_id%TYPE)IS   l_fullname VARCHAR2(100);BEGIN   SELECT last_name || ',' || first_name     INTO l_fullname     FROM employees    WHERE employee_id = employee_id_in;    ...END; 

Take a closer look, there are several problems in this process:
1) The length of l_fullname is fixed as 100?
2) The expression of l_fullname is invariably set to last_name | ',' | first_name? In case the customer changes his mind one day:
What should I do if I want to display first_name [space] last_name in all reports and information? If you have already
If this structure is used, can you find and modify it one by one?
3) At the end, we are likely to write some repeated SQL statements in different processes, which will greatly reduce the efficiency and performance.

At this time, we need to hide this general logic in the package to ensure that one maintenance service will benefit from the following:

CREATE OR REPLACE PACKAGE employee_pkg2    AS3        SUBTYPE fullname_t IS VARCHAR2 (100);4     5        FUNCTION fullname (6           last_in  employees.last_name%TYPE,7           first_in  employees.first_name%TYPE)8           RETURN fullname_t;9     10        FUNCTION fullname (11           employee_id_in IN employees.employee_id%TYPE)12           RETURN fullname_t;13    END employee_pkg;

You can rewrite the process as follows:

CREATE OR REPLACE PROCEDURE process_employee (   employee_id_in IN employees.employee_id%TYPE)IS   l_name employee_pkg.fullname_t;   employee_id_in   employees.employee_id%TYPE := 1;BEGIN   l_name := employee_pkg.fullname (employee_id_in);   ...END;

The code is neat, and you don't need to worry about how to implement employee_pkg.fullname! Worry-free!

Let's see how the package body is implemented:

CREATE OR REPLACE PACKAGE BODY employee_pkg2    AS3       FUNCTION fullname (4          last_in employees.last_name%TYPE,5          first_in employees.first_name%TYPE6       )7          RETURN fullname_t8       IS9       BEGIN10         RETURN last_in || ', ' || first_in;11      END;12     13      FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)14         RETURN fullname_t15      IS16         l_fullname fullname_t;17      BEGIN18         SELECT fullname (last_name, first_name) INTO l_fullname19           FROM employees20          WHERE employee_id = employee_id_in;21     22         RETURN l_fullname;23       END;24    END employee_pkg;

Function Overloading is used here, so that the external process can call functions of different versions only by passing in different parameters.
Fullname will be returned eventually!

2 Package-level data
This type of data consists of package declarations and global variables and constants in the package body.

For example:

CREATE OR REPLACE PACKAGE plsql_limitsIS   c_varchar2_length CONSTANT       PLS_INTEGER := 32767;   g_start_time PLS_INTEGER;END;

When you declare a variable in a sub-program or anonymous block, called a local variable, its declaration cycle is limited to one subroutine call or anonymous block execution.

Packet-level data will survive throughout the session.

If you define package data (variables and constants) in the package body, the data will survive during the session, but this type of data can only be used by programs in the package, that is, private data.
On the other hand, if the package data is defined in the package declaration, it can be used for all programs with the permission to execute the package.

Let's look at an example:
The GET_CPU_TIME function in the DBMS_UTILITY package can be used to calculate the time consumed by your program.

Code Listing 5: DBMS_UTILITY.GET_CPU_TIME measures

DECLARE   l_start   PLS_INTEGER;BEGIN   /* Get and save the starting time. */   l_start := DBMS_UTILITY.get_cpu_time;   /* Run your code. */   FOR indx IN 1 .. 10000   LOOP      NULL;   END LOOP;   /* Subtract starting time from current time. */   DBMS_OUTPUT.put_line (      DBMS_UTILITY.get_cpu_time - l_start);END;/

It's easy enough, but you still need to declare a local variable to store time!
So, we have a quicker way to use the custom package timer_pkg !!!

Code Listing 6: The timer_pkg package

CREATE OR REPLACE PACKAGE timer_pkgIS   PROCEDURE start_timer;   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);END timer_pkg;/CREATE OR REPLACE PACKAGE BODY timer_pkgIS   g_start_time   NUMBER := NULL;   PROCEDURE start_timer   IS   BEGIN      g_start_time := DBMS_UTILITY.get_cpu_time;   END;   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL)   IS   BEGIN      DBMS_OUTPUT.put_line (            message_in         || ': '         || TO_CHAR (DBMS_UTILITY.get_cpu_time - g_start_time));      start_timer;   END;END timer_pkg;/ 

The anonymous block is rewritten as follows:

BEGIN   timer_pkg.start_timer;   FOR indx IN 1 .. 10000   LOOP      NULL;   END LOOP;   timer_pkg.show_elapsed ('10000 Nothings');END;/

Wow! Good job!

No need to declare local variables, no need to understand how the GET_CPU_TIME function works!

3. subroutine Overloading
We all know that DBMS_OUTPUT.PUT_LINE is used to print character data to the console,

BEGIN   DBMS_OUTPUT.PUT_LINE (100);END;

It has one drawback: only the character type can be output!

SQL> BEGIN  2     DBMS_OUTPUT.PUT_LINE (TRUE);  3  END;  4  /   DBMS_OUTPUT.PUT_LINE (TRUE);   *ERROR at line 2:ORA-06550: line 2, column 4:PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’

How embarrassing! The BOOLEAN type cannot be converted to the character type!

Many developers have to do this:

IF l_student_is_registeredTHEN   DBMS_OUTPUT.PUT_LINE ('TRUE');ELSE   DBMS_OUTPUT.PUT_LINE ('FALSE');END IF;

I have to say that the spirit is commendable!
However, we have a better way:
Code Listing 7: The my_output package without overloading

CREATE OR REPLACE PACKAGE my_outputIS   PROCEDURE put_line (value_in IN VARCHAR2);   PROCEDURE put_line (value_in IN BOOLEAN);   PROCEDURE put_line (      value_in   IN DATE,      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');END my_output;/

This gives full play to the value of heavy load!

4 pack status and ORA-04068 errors
This problem cannot be avoided by any developer.
Package status?
When a package has at least one constant or variable declaration at the package level, the package will be in a state!
When a session call has a status package, PGA stores all package-level data!

If a status package is recompiled, all sessions that use the package will throw a: ORA-04068 error at the next call.
Because the data stored in the PGA package has expired (out of date )! So the package must be initialized again!

Also, once the ORA-04068 throws, all State packages in the session, for example, DBMS_OUTPUT, are identified as uninitialized. This usually means that the user
You must disconnect the session and try again.

This potential error means that when the IT department needs to upgrade the application, they need to ensure that all users have logged out. But in the Internet world, this is
Intolerable.

Therefore, in Oracle 11g r2, oracle provides version-Based Redefinition feature ).

For details, see container and docs.oracle.com/cd/e11882_01/appdev.#/e10471/adfns_editions.htm.

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills