Encapsulating PL/SQL code in a smart package

Encapsulate the code in a smart package
Http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13plsql-1872456.html

The vast majority of PL/SQL-based applications are made up of thousands or even millions of lines of code, which include specific and varied user requirements.
The implementation of business logic is initially done by stored procedures and functions, but developers need to consider putting these procedures and functions in the package for maintenance.

What is a package?
A package is a set of program units for a collection of PL/SQL code elements (cursors, types, variables, procedures, functions).

Typically consists of a package declaration (object declaration) and a inclusion (concrete implementation).

Why use a package?
1) Organize and maintain a set of function-related objects;
2) external hiding concrete realization;
3) Improve performance. This point to say:
When you first call the package. The entire package is loaded into memory. Next calls to the same package element do not require additional disk I/O.
In addition, package-level variables can be cached at the session level (Session-level). Thus reducing data read time.
4) Minimization of program unit recompilation
External programs (not defined in a package) can only invoke subroutines in the package declaration. Suppose you change and compile the package again. Those external programs
Will not expire.

The following shows the charm of the package:

11 Simple packages:
Suppose my Employees table definition is as follows:

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

Below I need to define a process_employee process. Returns the full name of the employee (last_name, first_name) for other
Program calls.

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  ;    begin  select  last_name | |  | | First_Name into  l_fullname from  Employees    Span class= "Hljs-keyword" >where  employee_id = employee_id_in; ... end ; 

Look carefully. There are several problems with this process:
1) l_fullname length fixed to 100?
2) l_fullname expression fixed to last_name | | ', ' | | First_Name? In case one day the customer changes his mind:
We would like to show in all reports and messages: First_Name "Space" last_name do? Let's say you've been in the N process
This structure is used. So are you going to one by one to find out and change it?
3) Finally, we are very likely to write some iterative SQL in different processes, which will greatly reduce efficiency and performance

At this time, we need to keep this general logic in the bag, to ensure that a maintenance benefit is everywhere:

CREATE ORREPLACE Package Employee_pkg2     as3Subtype fullname_t isVARCHAR2 ( -);4     5         FUNCTION fullname (6 last_in employees.last_name%TYPE, 7 first_in Employees.first_name%TYPE)8 RETURN fullname_t;9     Ten         FUNCTION fullname (employee_id_in in  employees.employee_id%TY PE) RETURN fullname_t; -    ENDemployee_pkg;

Go back and rewrite the process to do this:

CREATEORPROCEDURE process_employee (   employee_id_in IN employees.employee_id%TYPE)IS   l_name employee_pkg.fullname_t;   employee_id_in   employees.employee_id%TYPE1;BEGIN   l_name := employee_pkg.fullname (employee_id_in);   ...END;

The code is neat, and you don't have to worry about how employee_pkg.fullname is going to come true! More worry.

See how the package is implemented:

CREATEORREPLACE Package BODYEmployee_pkg2As3       FUNCTIONFullName (4Last_in employees.last_name%TYPE,5First_in employees.first_name%TYPE6)7          RETURNfullname_t8        is9       BEGINTen         RETURNlast_in | | ', ' | | first_in; One      END; A      -      FUNCTIONFullName (employee_id_ininchemployee.employee_id%TYPE) -         RETURNfullname_t the       is -L_fullname fullname_t; -      BEGIN -         SELECTFullName (last_name, first_name) into L_fullname +From Employees -WHERE employee_id = employee_id_in; +      A         RETURNL_fullname; at       END; -    ENDemployee_pkg;

The function overloads are used here. A function that allows external processes to invoke different version numbers simply by passing in a different number of parameters.

Will eventually return to FullName.

2 packet-level data
Such data consists of the package declaration and the global variables and constants of the package body.

Like what:

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 subroutine or an anonymous block. Called a local variable. Its declaration period is limited to one subroutine call or anonymous block run.

The packet-level data will survive throughout the session.

Suppose you define package data (variables and constants) in the package body, and the same data survives during the session, but such data can only be used by programs in the package. is the private data.
There is one more aspect. It is assumed that package data is defined in the package declaration and is available to all programs that have permission to run the package.

Take a look at the example:
The Get_cpu_time function in the dbms_utility package can be used to calculate your program's time-consuming

Code Listing 5:dbms_utility. Get_cpu_time measures

DECLARE   l_start   PLS_INTEGER;BEGIN   Getandtime. */   l_start := DBMS_UTILITY.get_cpu_time;   /* Run your code. */   FORIN110000   LOOP      NULL;   ENDLOOP;   timetime. */   DBMS_OUTPUT.put_line (      DBMS_UTILITY.get_cpu_time - l_start);END;/

It's easy enough to look at, but you still need to declare a local variable to store time!

So, we have a quicker way to use our own definition package TIMER_PKG!!!

Code Listing 6:the timer_pkg Package

CREATE ORREPLACE Package Timer_pkg is    PROCEDURE start_timer;    PROCEDURE show_elapsed (message_in  in VARCHAR2: = NULL);ENDtimer_pkg;/CREATE ORREPLACE Package BODY timer_pkg isG_start_time number: = NULL; PROCEDURE start_timerisBEGINg_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;ENDtimer_pkg;/

Overwrite the previous anonymous block, such as the following:

BEGIN   timer_pkg.start_timer;   FORIN110000   LOOP      NULL;   ENDLOOP;   timer_pkg.show_elapsed (‘10000 Nothings‘);END;/

Oh, whoa!

Good job!

No longer need to declare local variables, no longer need to understand how get_cpu_time function works!

3 Sub-Program overloading
We all know dbms_output. The put_line is used to print character data toward the console.

BEGIN   DBMS_OUTPUT.PUT_LINE (100);END;

It has a disadvantage, can only output character type!

SQL> BEGIN  2     DBMS_OUTPUT.PUT_LINE (TRUE);  3  END;  4  /   DBMS_OUTPUT.PUT_LINE (TRUE);   *ERROR2:ORA-0655024:PLS-00306orofincallto ‘PUT_LINE’

How embarrassing. A more Boolean type cannot be converted to a character type!

A lot of developers have to do this:

IF l_student_is_registeredTHEN   DBMS_OUTPUT.PUT_LINE (‘TRUE‘);ELSE   DBMS_OUTPUT.PUT_LINE (‘FALSE‘);ENDIF;

Have to say the spirit of commendable!

But. We have a better way:
Code Listing 7:the my_output package without overloading

CREATE ORREPLACE Package My_output is    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 ')
      ;ENDmy_output;/

This will give full play to the value of overloading!

4 Packet status and ORA-04068 error
The problem is that no one in any development package can avoid it.
What's the status of the package?
When a package has at least one constant or variable declared at the package level, the package has a state!
When a session invokes a stateful package, the PGA stores all packet-level data in the package.

Assuming that a state package is compiled again, all sessions that use the package will be thrown at the next call: ORA-04068 error.
Since the package level data stored in the PGA has expired (out of date)! So the package must be initialized again.

In addition Once the ORA-04068 is thrown. All State packages in a session, such as Dbms_output, are identified as uninitialized.

This usually means that the user
The session must be disconnected again.

This potential error means that when the IT department needs to upgrade the app, they need to make sure that all users are logged out.

But in the world of the Internet, this is
Intolerable.

So in Oracle 11g R2, Oracle provides a redefinition feature based on the version number (edition-based redefinition feature).

For details, please refer to: Oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf and Docs.oracle.com/cd/e11882_01/appdev.112/e10471/adfns_editions.htm

Encapsulating PL/SQL code in a smart package