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