 |
|
EnterpriseDB: Declaring
and Initializing Variables
Oracle Tips by
Burleson
|
SPL supports
all of the data types that I covered in Chapter 2. It also supports
some unique typing when declaring variables.
A variable is
declared by giving it a name and following it with the data type. Some
variables can be declared CONSTANT and some variables can be
optionally assigned a value in the declaration. The declaration ends
with a semi-colon.
To declare a
variable named v_first_name of type VARCHAR2 and length 30, I would
use:
v_first_name
VARCHAR2(30);
I could
declare the same variable and assign it the value "Lewis".
v_first_name
VARCHAR2(30) := 'Lewis';
A CONSTANT is
a variable that does not change its value during the course of a
program. As a matter of a fact, by using the CONSTANT keyword, you
prevent the program from being able to change the value. The constant
variable’s value MUST be assigned when it is created:
v_first_name
CONSTANT VARCHAR2(30) := 'Lewis';
Numbers and
Booleans are just as easy to declare and assign as a Varchar2.
v_number_1
NUMBER;
v_number_2 NUMBER(5,3) := 42.5;
v_number_3 CONSTANT NUMBER(5,3) := 42.5;
v_bool_1 BOOLEAN;
v_bool_2 BOOLEAN := TRUE;
v_bool_3 CONSTANT BOOLEAN := FALSE;
Dates in SPL
are implemented as timestamp(0), which is a DATE and TIME with no
partial seconds; in other words just like an Oracle date data type.
Dates and
timestamps are declared just as easily as character or numeric data
but they get a little more complicated when you assign them a value:
v_date_1
DATE;
v_date_2 DATE := TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY
HH24:MI:SS');
v_date_3 CONSTANT DATE :=
TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY
HH24:MI:SS');
v_TIMESTAMP_1 TIMESTAMP(6);
v_timestamp_2 TIMESTAMP(6) :=
TO_TIMESTAMP('01-JAN-2006 01:24:55.123456',
'DD-MON-YYYY HH24:MI:SS.us');
v_timestamp_3 CONSTANT TIMESTAMP(6) :=
TO_TIMESTAMP('01-JAN-2006 01:24:55.123456',
'DD-MON-YYYY HH24:MI:SS.us');
When assigning
a literal value to a date or timestamp, always use the appropriate
function to ensure that it is formatted as you expect it to be. As we
discovered in the last chapter, TO_DATE converts a character string to
a date and TO_TIMESTAMP converts a character string to a timestamp.
As you can see
from these examples, := is the assignment operator. This is also the
assignment operator when you are writing code:
DECLARE
v_first_name VARCHAR2(30);
v_age NUMBER;
BEGIN
v_first_name := 'Lewis';
v_age := 21; -- yeah, I wish!
END;
There is a
special way to declare variables in an SPL program called an "anchored
declaration". An anchored declaration is identified by the percent
sign (%) followed by either the keyword TYPE or the keyword ROWTYPE.
I will cover the ROWTYPE keyword in greater detail in the section on
records below but it is declared just like the TYPE.
An anchored
declaration is called that because it is anchored to the database.
Unlike a boat anchor that holds you in place, this is an anchor in the
sense of a relationship. When you use an anchored declaration, you
relate the data type in your program to a data structure in the
database. For %TYPE, you refer to the declared structure of a column
in the database. The %ROWTYPE declaration refers to the entire table
structure (all columns) of a database table.
The greatest
benefit of using %TYPE or %ROWTYPE is that since it is related to a
database object, you are ensured that should the database object
change, your code will automatically pick up that change when you
recompile. If you alter the data type of a column for %TYPE, or add
or remove a column for %ROWTYPE, your SPL will be aware of that. If
you drop a column referred to by a %TYPE, your code will no longer
compile. If you drop a table or view referred to by a %TYPE or a %ROWTYPE,
your code will no longer compile.
The format of
the TYPE anchor is:
<variable
name > <table name>.<column name>%TYPE ;
The format for
ROWTYPE is:
<variable
name> <table name>%ROWTYPE;
You can see
this in the example below:
DECLARE
v_name emp.ename%TYPE;
v_emp_record emp%ROWTYPE;
BEGIN
v_name :=
'Lewis';
v_emp_record.ename := 'Lewis';
END;
To initialize
the variable declared with %TYPE, you access it as you would any other
variable. It can be a constant and it can be initialized as part of
the declaration.
The ROWTYPE
variable is accessed differently. When you use ROWTYPE, you are
creating an SPL "record". An SPL record is different than a database
record but is very similar in concept.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
