and Initializing Variables
Oracle Tips by
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
declare the same variable and assign it the value "Lewis".
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:
CONSTANT VARCHAR2(30) := 'Lewis';
Booleans are just as easy to declare and assign as a Varchar2.
v_number_2 NUMBER(5,3) := 42.5;
v_number_3 CONSTANT NUMBER(5,3) := 42.5;
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.
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_2 DATE := TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY
v_date_3 CONSTANT DATE :=
TO_DATE('01-JAN-2006 01:24:55', 'DD-MON-YYYY
v_timestamp_2 TIMESTAMP(6) :=
v_timestamp_3 CONSTANT TIMESTAMP(6) :=
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:
v_first_name := 'Lewis';
v_age := 21; -- yeah, I wish!
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.
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.
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:
name > <table name>.<column name>%TYPE ;
The format for
name> <table name>%ROWTYPE;
You can see
this in the example below:
v_emp_record.ename := 'Lewis';
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
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.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.