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 contain detailed 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, packet-level variables can be cached at the session level (Session-level) to reduce data read time.
4) Minimization of program unit recompilation
External programs (not defined in the package) can only invoke subroutines in the package declaration. If you change and recompile the package body, those external programs
Will not expire.

Here's a look at the charm of the package:

11 Simple packages:
Suppose my Employees table is defined 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 that 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 closely, 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 want to show in all reports and messages: First_Name "Space" last_name do? If you have been in the N process
Using this structure, do you go to one by one to find out to modify it?
3) Finally, we are very likely to write some duplicate SQL in different processes, which will greatly reduce the efficiency and performance

At this time, we need to hide this common logic in our package and 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, so you can:

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 care about how Employee_pkg.fullname is implemented! How 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;

Function overloading is used here so that external procedures can invoke different versions of a function simply by passing in different 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.

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 subroutine or an anonymous block, called a local variable, its declaration period is limited to one subroutine call or anonymous block execution.

The packet-level data will survive throughout the session.

If you define package data (variables and constants) in the package body, the data also survives during the session, but such 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 is available to all programs that have Execute package permissions.

Take a look at an 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 custom packages 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, as follows:

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

Oh, whoa! Good job!

You no longer need to declare local variables, and you no longer need to understand how get_cpu_time function works!

3 Sub-Program overloading
We all know dbms_output. Put_Line is used to print character data to 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! Comparing a Boolean type cannot be converted to a character type!

Many 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!
However, 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
This is a problem that no one in the development package can avoid.
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!

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

Also, once ORA-04068 is thrown, all state packages in the session, such as Dbms_output, are identified as uninitialized. This usually means that the user
The session must be disconnected from reconnection.

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 version-based redefinition capabilities (edition-based redefinition feature).

For more information, 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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Encapsulating PL/SQL code in a smart package