This section journeys into the “Oracle Style Package” for LightDB. A package, by very definition, is an object or a group of objects packed together. In terms of databases, this translates into a named schema object that packages within itself a logically grouped collection of procedures, functions, variables, cursors, user-defined record types, and reference records.
Like similar constructs in various other programming languages, there are good reasons for using packages with SQL. In this section, we are going to cover a few.
Reliability and Reusability of Code Packages provide the ability to create modular objects that encapsulate code. This makes the overall design and implementation simpler. The ability to encapsulate variables, related types, stored procedures/functions, and cursors, essentially allows creating a self-contained module that is simple and easy to understand, maintain and use. Encapsulation comes into play through the exposure of a package interface, rather than its implementation details, i.e., package body. This, therefore, benefits in many ways. Furthermore it allows applications and users to refer to a consistent interface and not worry about the contents of its body. Also, it prevents users from making any decisions based on code implementation as that’s never exposed to them.
Ease of Use The ability to create a consistent functional interface in LightDB helps simplify application development by allowing the compilation of packages without their bodies. Beyond the development phase, the package allows a user to manage access control on the entire package rather than individual objects. This is rather valuable especially if the package contains lots of schema objects.
Performance Packages are loaded into memory for maintenance and therefore utilize minimal I/O resources. Recompilation is simple and only limited to object(s) changed; dependent objects are not recompiled.
Additional Features In addition to performance and ease of use, packages offer session-wide persistence for variables and cursors. This means variables and cursors have the same lifetime as a database session and are destroyed when the session is destroyed.
Package consists of two components. Package specification and Package body.
Package Specification Any object within the package that is to be used from the outside is specified in the package specification section. This is the publicly accessible interface we have been referring to in earlier sections. It does not contain the definition or implementation of them, i.e. the functions and the procedures. It only has their headers defined without the body definitions. The variables can be initialized. The following is the list of objects that can be listed in the package specification:
Functions
Procedures
Cursors
Types
Variables
Record types
Package Body the body contains all the implementation code of a package, including the public interfaces and the private objects. A package body is optional if the specification does not contain any subprogram or cursor.
It must contain the definition of the subprograms declared in specification and the corresponding definitions must match.
A package body can contain its own subprogram and type declarations of any internal objects not specified in the specifications. These objects are then considered private. Private objects cannot be accessed from outside the package.
In addition to subprogram definitions, it can optionally contain a initializer block that initializes the variables declared in specification and is executed only once when the first call to the package is made in a session.
CREATE PACKAGE —— Define a new package specification.
Syntax
CREATE [ OR REPLACE ] PACKAGE [schema.]package_name [IS | AS]
item_list[, item_list ...]
END [package_name];
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
function_declaration:
FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype;
procedure_declaration:
PROCEDURE procedure_name [(parameter_declaration[, ...])]
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration:
CURSOR name [(cur_param_decl[, ...])] RETURN rowtype;
item_declaration:
cursor_declaration |
cursor_variable_declaration |
record_variable_declaration |
variable_declaration |
record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];
cursor_variable_declaration:
curvar curtype;
record_variable_declaration:
recvar { record_type | rowtype_attribute | record_type%TYPE };
variable_declaration:
varname datatype [ [ NOT NULL ] := expr ]
parameter_declaration:
parameter_name [IN] datatype [[:= | DEFAULT] expr]
Description
Creates the package specification that contains public declarations. The declared items in the package specification are accessible from anywhere in the package and to any other subprograms in the same database.
CREATE PACKAGE defines a new package. CREATE OR REPLACE PACKAGE will either create a new package or replace an existing definition.
If a schema name is included, then the package is created in the specified schema. Otherwise, it is created in the current schema. The name of the new package must be unique within the schema.
When CREATE OR REPLACE PACKAGE is used to replace an existing package, the ownership and permissions of the package do not change. All other package properties are assigned the values specified or implied in the command. Only the owner and member of the owning roles are allowed to replace the packages.
The user that creates the package becomes the owner of the package.
CREATE PACKAGE BODY —— Define a new package definition.
Syntax
CREATE [ OR REPLACE ] PACKAGE BODY [schema.]package_name [IS | AS]
[item_list[, item_list ...]] |
item_list_2 [, item_list_2 ...]
[initialize_section]
END [package_name];
initialize_section:
BEGIN statement[, ...]
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
item_list_2:
[
function_declaration
function_definition
procedure_declaration
procedure_definition
cursor_definition
]
function_definition:
FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype [IS | AS]
[declare_section] body;
procedure_definition:
PROCEDURE procedure_name [(parameter_declaration[, ...])] [IS | AS]
[declare_section] body;
cursor_definition:
CURSOR name [(cur_param_decl[, ...])] RETURN rowtype IS select_statement;
body:
BEGIN statement[, ...] END [name];
statement:
[LABEL] pl_statments[, ...];
Description
CREATE PACKAGE BODY defines the package body for a package. CREATE OR REPLACE PACKAGE body will either create a new package body for the package or replace an existing package body definition. Package specification must be created first to create the package body. The package body contains the implementation of every cursor and subprogram declared in the package specification created through “CREATE PACKAGE”. objects defined in a package body are only accessible to outside the package if their specification is listed in the package specification. For all objects that are only defined in the package body and are not included in the package specification, they become private members to the package and are not accessible outside of the package. Both the package and its body must be created in the same schema.
package_name The name (optionally schema-qualified) of the package to create.
item_list This is the list of items that can be part of a package.
procedure_declaration The procedure signature, i.e. procedure_name(argument_list ).
procedure_declaration can appear in both package specification and package body.
Procedure declarations listed in the Package specification makes the procedure
public and accessible from outside of the package. While the procedure declared
in the package body is considered as a forward declaration and becomes
a private member to the package.
procedure_definition Implementation/definition of the package procedure.
procedure_definition can only be provided in the package body.
Procedure access specifier is determined by procedure declaration
and the procedures defined in the package body without corresponding
declaration automatically becomes private to the package.
function_declaration The function signature and it’s return type,
i.e. function_name(argument_list) RETURN return_type;.
function_declaration can appear in both package specification and package body. Function declarations listed in the Package specification makes the function public and accessible from outside the package. While the function declaration in the package body is considered as a forward declaration and becomes a private member to the package.
function_definition Implementation/definition of the package function.
function_definition can only be provided in the package body.
Function access specifier is determined by function declaration
and the function defined in the package body without corresponding
declaration automatically becomes private to the package.
type_definition Either a RECORD, or CURSOR type definition.
cursor_declaration CURSOR declaration along with its arguments and return type as the desired ROWTYPE.
item_declaration Allows declaration of: Cursors, Cursor variables, Record variable, Variables.
parameter_declaration Defines the syntax for declaring a parameter.
The keyword “IN” if specified indicates that this is an input parameter.
The DEFAULT keyword followed by an expression (or value) may only be specific
for an input parameter.
declare_section This contains all the elements that are local to the function
or procedure and can be referenced within its body.
body The body consists of the SQL statements or PL control structures
that are supported by plorasql language.
In the previous sections, we have gone through the syntax that dictates the structure of a package. In this section, we are going to take this a step further by understanding the construction process of a package and how we can access its public elements.
As a package is created, LightDB will compile it and report any issues it may find. Once the package is successfully compiled, it becomes ready for use.
A package is instantiated and initialized when it is referenced for the first time in a session. The following actions are performed in the same order during this process:
Assignment of initial values to public variables
Execution of the initializer block of the package
There are several ways to access package elements:
Package functions can be utilized just as any other function in a SELECT statement or from other PL blocks
Package procedure can be invoked directly using CALL or from other PL blocks
Package variables can be directly read and written using the package name qualification in a PL block.
Direct Access Using Dot Notation: In the dot notation, elements can be accessed in the following manner:
package_name.func('foo');
package_name.proc('foo');
package_name.variable;
These statements can be used from inside a PL block or in a SELECT statement if the element is a function.
SQL Call Statement: Another way is to use the CALL statement. The CALL statement executes a standalone procedure in a type or package.
CALL package_name.proc('foo');
The scope of variables declared in a PL/oraSQL block is limited to that block. If it has nested blocks, then it will be a global variable to the nested blocks.
Similarly, if both blocks declare the same name variable, then inside of the nested block, its own declared variable is visible and the parent one becomes invisible. To access the parent variable, that variable must be fully qualified.
Consider the following code snippet.
Example: Visibility and Qualifying Variable Names
-- blk_1
DECLARE
x INT;
y INT;
BEGIN
-- both blk_1.x and blk_1.y are visible
-- blk_2
DECLARE
x INT;
z INT;
BEGIN
-- blk_2.x, y and z are visible
-- to access blk_1.x it has to be a qualified name. blk_1.x := 0; NULL;
NULL;
END;
-- both x and y are visible
END;
/
The above example shows how you must fully qualify a variable name in case a nested package contains a variable with the same name.
Variable name qualification helps in resolving possible confusion that gets introduced by scope precedence in the following scenarios:
Package and nested packages variables: without qualification, nested takes precedence
Package variable and column names: without qualification, column name takes precedence
Function or procedure variable and package variable: without qualification, package variable takes precedence.
create package example.
CREATE TABLE test(x INT, y VARCHAR2(100)); create table rectype(a int,b varchar2(100)); INSERT INTO test VALUES (1, 'One'); INSERT INTO test VALUES (2, 'Two'); INSERT INTO test VALUES (3, 'Three'); CREATE OR REPLACE PACKAGE example AS -- Declare public type, cursor, and exception: CURSOR curtype RETURN rectype%rowtype; rec rectype%rowtype; -- Declare public subprograms: FUNCTION somefunc (v int) RETURN NUMBER; -- Overload preceding public subprogram: PROCEDURE xfunc (emp_id NUMBER); PROCEDURE xfunc (emp_email VARCHAR2); END; /
create package body example.
CREATE OR REPLACE PACKAGE BODY example
AS
nelems NUMBER; -- private variable, visible only in this package
-- Define cursor declared in package specification:
CURSOR curtype RETURN rectype%rowtype IS SELECT x, y FROM test ORDER BY x;
-- Define subprograms declared in package specification:
FUNCTION somefunc (v int) RETURN NUMBER
IS
id NUMBER := 0;
BEGIN
OPEN curtype;
FETCH curtype INTO rec;
RETURN rec.a;
END;
PROCEDURE xfunc (emp_id NUMBER) IS
BEGIN
NULL;
END;
PROCEDURE xfunc (emp_email VARCHAR2) IS
BEGIN
NULL;
END;
END;
/
Record types are supported as package variables, however they can only be used within package elements i.e., Package function/procedure can utilize them. They can not be accessed outside the package.