 |
|
EnterpriseDB: Packaged Variables
Oracle Tips by
Burleson
|
Packages
offer three varieties of variable: local, public and private.
* Local
– Local variables are variables declared within an anonymous block or
within a procedure or function. The scope, or lifetime, of a local
variable is the scope of the block. When a block completes, the local
variable is removed. A local variable cannot be referenced from
outside of its scope. That means that a calling program cannot
reference a variable within a called procedure. Stand-alone
procedures and functions can only contain local variables.
* Public
– A public variable is declared within a package specification. A
public variable is also referred to as a global session variable
because it is available globally to any process in the same session.
Any procedure, function or external application that is connected to
the database can change the value of a public variable. Public
variables are available only in packages, not in standalone procedures
or functions.
*
Private – A private variable, also called protected in some languages,
is declared within a package body. Private variables are accessible
by any procedure or function within that package. A private variable
is a package global variable. Private variables are available only in
packages, not in standalone procedures or functions.
Overloading
Overloading is the ability to declare multiple procedures and
functions with the same name but different parameter signatures. For
example, the TO_CHAR function accepts DATE or NUMERIC input. That is
an overloaded function.
In SPL,
you may overload packaged procedures and functions and standalone
functions but not standalone procedures. I talk about this topic in
Chapter 8.
Declaring a Package
Below is
an example showing the creation of a package specification and a
package body. The specification example will show most of the topics
discussed above including the definition of a public record type,
public table type, public scalar variable, public ref cursor, public
procedure and a public function. I will step through the
specification line by line and will continue this example by creating
a package body and calling the code.
CREATE OR REPLACE PACKAGE sample_pkg
AS
-- Step 1
TYPE r_data IS RECORD (
text VARCHAR2(25),
text_length VARCHAR2(50) );
-- Step 2
TYPE a_table IS TABLE OF r_data;
--
Step 3
v_table a_table;
-- Step
4
g_global_empno emp.empno%TYPE;
--Step 5, Public constants are not currently supported
--gc_global_raise_pct CONSTANT NUMBER := 1.03;
-- Step 6
TYPE c_ref IS REF CURSOR;
-- Step
7
v_ref
c_ref;
--
Step 8
PROCEDURE public_proc(
p_in_char IN VARCHAR2,
p_data OUT SYS_REFCURSOR );
-- Step 9
FUNCTION public_func(
p_in_char IN VARCHAR2 )
RETURN SYS_REFCURSOR;
--
Step 10
PROCEDURE display_data( p_data IN VARCHAR2 );
-- Step 11
PROCEDURE display_data( p_data IN DATE );
-- Step 12
PROCEDURE display_data( p_data IN NUMBER );
END;
*
edb_chap_3_pkg_spc_complete.sql
CREATE OR REPLACE PACKAGE sample_pkg
AS
-- Step 1
TYPE r_data IS RECORD (
text VARCHAR2(25),
text_length VARCHAR2(50) );
--
Step 2
TYPE a_table IS TABLE OF r_data;
-- Step 3
--v_table a_table;
-- Step
4
g_global_empno emp.empno%TYPE;
--Step 5, Public constants are not currently supported
--gc_global_raise_pct CONSTANT NUMBER := 1.03;
-- Step 6
TYPE c_ref IS REF CURSOR;
-- Step
7
v_ref
c_ref;
-- Step 8
PROCEDURE public_proc(
p_in_char IN VARCHAR2,
p_data OUT SYS_REFCURSOR );
-- Step
9
FUNCTION public_func(
p_in_char IN VARCHAR2 )
RETURN SYS_REFCURSOR;
--
Step 10
PROCEDURE display_data( p_data IN VARCHAR2 );
-- Step
11
PROCEDURE display_data( p_data IN DATE );
--
Step 12
PROCEDURE display_data( p_data IN NUMBER );
END;
The
complete package specification is available in the Code Depot at:
edb_chap_3_pkg_body_complete.sql
In Step
1, I declare a record type. This is a record type just as I declared
in the section on records types. Nothing special here.
Step 2
declares a table type, a_table, of r_data, the record type declared in
Step 1.
Step 3
creates a variable, v_table, of the newly created type, a_table. This
step is commented out. The variable will instead be declared in the
package body so that it will be a protected variable.
Step 4
creates a scalar variable of the same type as the empno column in the
emp table. Remember that there are three types of variables:
composites, collections and scalar types. Scalars are the types like
varchar2, number and date.
Step 5
is a commented out declaration for a global constant. PL/SQL does
support global constants but SPL does not. I will talk more about
this in the chapters on programming for Oracle compatibility.
Step 6
creates a public, weakly typed ref cursor and step 7 creates a
variable of the type.
Step 8
defines a public procedure, public_proc. Public_proc has two
parameters, a varchar2 in parameter and a ref cursor out parameter.
Step 9
defines a public function. This declaration is much like the above
public procedure but the ref cursor will be returned as a return value
instead of an OUT parameter.
Steps
10, 11 and 12 all create a procedure called display_data. This is an
overloaded procedure. Each version of the procedure has a different
set of parameter types.
Below is
the package body for the spec above.
CREATE OR REPLACE PACKAGE BODY sample_pkg
AS
-- Step 1
v_table a_table;
--Step 2,
Private constants are not currently supported
--gc_global_raise_pct CONSTANT NUMBER := 1.03;
-- Step 3
PROCEDURE a_private_proc
AS
BEGIN
NULL;
END;
-- Step 4
PROCEDURE public_proc(
p_in_char IN VARCHAR2,
p_data OUT SYS_REFCURSOR )
AS
BEGIN
NULL;
END;
-- Step 5
FUNCTION public_func(
p_in_char IN VARCHAR2 )
RETURN SYS_REFCURSOR
AS
BEGIN
RETURN NULL;
END;
-- Step 6
PROCEDURE display_data( p_data IN VARCHAR2 )
AS
BEGIN
NULL;
END;
-- Step 7
PROCEDURE display_data( p_data IN DATE )
AS
BEGIN
NULL;
END;
-- Step 8
PROCEDURE display_data( p_data IN NUMBER )
AS
BEGIN
NULL;
END;
-- Step 9
PROCEDURE init
AS
BEGIN
NULL;
END;
-- Step 10
BEGIN
NULL;
END;
This is
the minimal example of the package body, for the package specification
above, that will successfully compile. Rather than repeat the full
listing repeatedly here in the book, you can get the complete script
from the Code Depot. The complete script is available at:
edb_chap_3_pkg_body_complete.sql
*
edb_chap_3_pkg_body_complete.sql
CREATE OR REPLACE PACKAGE BODY sample_pkg
AS
-- Step 1
v_table a_table;
--Step 2,
Private constants are not currently supported
--gc_global_raise_pct CONSTANT NUMBER := 1.03;
-- Step 3
PROCEDURE a_private_proc
AS
-- Step 3a
v_output_string VARCHAR2(32000) := 'Debug: ';
BEGIN
--
Step 3b
--
If the private variable v_table has records
--
Loop through them and display the values
--
Else just say that it's null
IF v_table.COUNT > 0
THEN
FOR i IN 1..v_table.COUNT
LOOP
v_output_string := v_output_string ||
' v_table(' || TO_CHAR(i) ||
'):' || nvl(v_table(i).txt, 'NA') || ', ' ||
nvl(v_table(i).txt_length,'NA' );
END LOOP;
ELSE
v_output_string := v_output_string ||
' v_table is empty';
END IF;
--
Step 3c
--
If g_global_empno has a value, display it
--
Else just say it's null
IF g_global_empno IS NOT NULL
THEN
v_output_string := v_output_string ||
', g_global_empno is: ' || TO_CHAR(g_global_empno);
ELSE
v_output_string := v_output_string ||
', g_global_empno is NULL';
END IF;
--
Step 3d
--
If the public cursor v_ref has been opened,
--
show how many records have been fetched
--
Else just say that it is not open
IF
v_ref%ISOPEN
THEN
v_output_string := v_output_string ||
', Cursor v_ref is open and has fetched ' ||
to_Char(v_ref%ROWCOUNT) || ' rows.';
ELSE
v_output_string := v_output_string ||
', Cursor v_ref is not open.';
END IF;
--
Step 3e
dbms_output.put_line( v_output_string );
END;
--
Step 4
PROCEDURE public_proc(
p_in_char IN VARCHAR2,
p_empno IN emp.empno%TYPE,
p_data OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_data FOR p_in_char USING p_empno;
a_private_proc;
END;
--
Step 5
FUNCTION
public_func(
p_in_char IN VARCHAR2,
p_empno IN emp.empno%TYPE )
RETURN SYS_REFCURSOR
AS
v_cursor SYS_REFCURSOR;
BEGIN
public_proc( p_in_char, p_empno, v_cursor );
RETURN v_cursor;
END;
--
Step 6
PROCEDURE display_data( p_data IN VARCHAR2 )
AS
v_next_row INTEGER := v_table.COUNT + 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('This is VARCHAR2 data: ' || p_data );
v_table(v_next_row).txt := p_data;
v_table(v_next_row).txt_length := LENGTH(p_data);
a_private_proc;
END;
--
Step 7
PROCEDURE
display_data( p_data IN DATE )
AS
v_char_version VARCHAR2(32000);
v_next_row INTEGER := v_table.COUNT + 1;
BEGIN
v_char_version := TO_CHAR(p_data, 'FMDD Month, YYYY');
DBMS_OUTPUT.PUT_LINE('This is DATE data: ' || v_char_version );
v_table(v_next_row).txt := v_char_version;
v_table(v_next_row).txt_length := LENGTH(v_char_version);
a_private_proc;
END;
--
Step 8
PROCEDURE
display_data( p_data IN NUMBER )
AS
v_char_version VARCHAR2(32000);
v_next_row INTEGER := v_table.COUNT + 1;
BEGIN
v_char_version := TO_CHAR(p_data, 'FM$999,990.00');
DBMS_OUTPUT.PUT_LINE('This is NUMBER data: ' || v_char_version );
v_table(v_next_row).txt := v_char_version;
v_table(v_next_row).txt_length := LENGTH(v_char_version);
a_private_proc;
END;
--
Step 9
PROCEDURE init
AS
temp_tab sample_pkg.a_table;
BEGIN
g_global_empno := 7788;
v_table := temp_tab;
END;
-- Step
10
BEGIN
init;
END;
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
