If only they had used %TYPE!
4.5.2 Anchoring at Compile Time
The %TYPE declaration attribute anchors the datatype of one variable to that of another data
structure at the time a PL/SQL block is compiled. If a change is made to the "source" datatype, then
any program which contains a declaration anchored to this datatype must be recompiled before it will
be able to use this new state of the datatype.
The consequences of this rule differ for PL/SQL modules stored in the database and those defined in
client-side tools, such as Oracle Forms.
Consider the following declaration of company_name in the procedure display_company:
PROCEDURE display_company (company_id_in IN INTEGER)
IS
company_name company.name%TYPE;
BEGIN
END;
When PL/SQL compiles this module, it looks up the structure of the company table in the data
dictionary, finds the column NAME, and obtains its datatype. It then uses this data dictionary-based
datatype to define the new variable.
What, then, is the impact on the compiled display_company procedure if the datatype for the name
column of the company table changes? There are two possibilities:
● If display_company is a stored procedure, then the compiled code will be marked as "invalid."
The next time a program tries to run display_company, it will be recompiled automatically
before it is used.
● If display_company is a client-side procedure, then the Oracle Server cannot mark the
program as invalid. The compiled client source code remains compiled using the old datatype.
The next time you execute this module, it could cause a VALUE_ERROR exception to be
raised.
Whether stored or in client-side code, you should make sure that all affected modules are recompiled
after data structure changes.
4.5.3 Nesting Usages of the %TYPE Attribute
You can nest usages of %TYPE in your declarations as well:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE
/* The "base" variable */
unlimited_revenue NUMBER;
/* Anchored to unlimited revenue */
total_revenue unlimited_revenue%TYPE;
/* Anchored to total revenue */
total_rev_94 total_revenue%TYPE;
total_rev_95 total_revenue%TYPE;
BEGIN
In this case total_revenue is based on unlimited_revenue and both variables for 1994 and 1995 are
based on the total_revenue variable. There is no practical limit on the number of layers of nested
usages of %TYPE.
4.5.4 Anchoring to Variables in Other PL/SQL Blocks
The declaration of the source variable for your %TYPE declarations does not need to be in the same
declaration section as the variables which use it. That variable must simply be visible in that section.
The variable could be a global PL/SQL variable (defined in a package) or be defined in an PL/SQL
block which contains the current block, as in the following example:
PROCEDURE calc_revenue
IS
unlimited_revenue NUMBER;
total_revenue unlimited_revenue%TYPE;
BEGIN
IF TO_CHAR (SYSDATE, 'YYYY') = '1994'
THEN
DECLARE
total_rev_94 total_revenue%TYPE;
BEGIN
END;
END IF;
END calc_revenue;
4.5.5 Anchoring to NOT NULL Datatypes
When you declare a variable, you can also specify the need for the variable to be NOT NULL This
NOT NULL declaration constraint is transferred to variables declared with the %TYPE attribute. If I
include a NOT NULL in my declaration of a source variable (one that is referenced afterwards in a %
TYPE declaration), I must also make sure to specify a default value for the variables which make use
of that source variable. Suppose I declare max_available_date NOT NULL in the following example:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE
max_available_date DATE NOT NULL :=
LAST_DAY (ADD_MONTHS (SYSDATE, 3));
last_ship_date max_available_date%TYPE;
The declaration of last_ship_date will then fail to compile, with the following message:
a variable declared NOT NULL must have an initialization
assignment.
If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default
value provided. The same is not true, however, for variables declared with %TYPE where the source
is a database column.
The NOT NULL column constraint does not apply to variables declared with the %TYPE attribute.
The following code will compile successfully:
DECLARE
Company name is a NOT NULL column in the company
table.
comp_name company.name%TYPE;
BEGIN
comp_name := NULL;
You will be able to declare the comp_name variable without specifying a default, and you will be
able to NULL out the contents of that variable.
Previous: 4.4 Variable
Declarations
Oracle PL/SQL
Programming, 2nd Edition
Next: 4.6 Programmer-
Defined Subtypes
4.4 Variable Declarations
Book Index
4.6 Programmer-Defined
Subtypes
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 4.3 NULLs in PL/
SQL
Chapter 4
Variables and Program Data
Next: 4.5 Anchored
Declarations
4.4 Variable Declarations
Before you can make a reference to a variable, you must declare it. (The only exception to this rule is
for the index variables of FOR loops.) All declarations must be made in the declaration section of
your anonymous block, procedure, function, or package (see Chapter 15, Procedures and Functions,
for more details on the structure of the declaration section).
When you declare a variable, PL/SQL allocates memory for the variable's value and names the
storage location so that the value can be retrieved and changed. The declaration also specifies the
datatype of the variable; this datatype is then used to validate values assigned to the variable.
The basic syntax for a declaration is:
<variable_name> <datatype> [optional default assignment];
where <variable_name> is the name of the variable to be declared and <datatype> is the datatype or
subtype which determines the type of data which can be assigned to the variable. The [optional
default assignment] clause allows you to initialize the variable with a value, a topic covered in the
next section.
4.4.1 Constrained Declarations
The datatype in a declaration can either be constrained or unconstrained. A datatype is constrained
when you specify a number which constrains or restricts the magnitude of the value which can be
assigned to that variable. A datatype is unconstrained when there are no such restrictions.
Consider the datatype NUMBER. It supports up to 38 digits of precision and uses up the memory
needed for all those digits. If your variable does not require this much memory, you could declare a
number with a constraint, such as the following:
itty_bitty_# NUMBER(1);
large_but_constrained_# NUMBER(20,5);
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Constrained variables require less memory than unconstrained number declarations like this:
no_limits_here NUMBER;
4.4.2 Declaration Examples
Here are some examples of variable declarations:
● Declaration of date variable:
hire_date DATE;
● This variable can only have one of three values: TRUE, FALSE, NULL:
enough_data BOOLEAN;
● This number rounds to the nearest hundredth (cent):
total_revenue NUMBER (15,2);
● This variable-length string will fit in a VARCHAR2 database column:
long_paragraph VARCHAR2 (2000);
● This constant date is unlikely to change:
next_tax_filing_date CONSTANT DATE := '15-APR-96';
4.4.3 Default Values
You can assign default values to a variable when it is declared. When declaring a constant, you must
include a default value in order for the declaration to compile successfully. The default value is
assigned to the variable with one of the following two formats:
<variable_name> <datatype> := <default_value>;
<variable_name> <datatype> DEFAULT <default_value>;
The <default_value> can be a literal, previously declared variable, or expression, as the following
examples demonstrate:
● Set variable to 3:
term_limit NUMBER DEFAULT 3;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
● Default value taken from Oracle Forms bind variable:
call_topic VARCHAR2 (100) DEFAULT :call.description;
● Default value is the result of a function call:
national_debt FLOAT DEFAULT POWER (10,10);
● Default value is the result of the expression:
order_overdue CONSTANT BOOLEAN :=
ship_date > ADD_MONTHS (order_date, 3) OR
priority_level (company_id) = 'HIGH';
I like to use the assignment operator (:=) to set default values for constants, and the DEFAULT
syntax for variables. In the case of the constant, the assigned value is not really a default, but an
initial (and unchanging) value, so the DEFAULT syntax feels misleading to me.
4.4.4 NOT NULL Clause
If you do assign a default value, you can also specify that the variable must be NOT NULL. For
example, the following declaration initializes the company_name variable to PCS R US and makes
sure that the name can never be set to NULL:
company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US';
If your code includes a line like this:
company_name := NULL;
then PL/SQL will raise the VALUE_ERROR exception. You will, in addition, receive a compilation
error with this next declaration:
company_name VARCHAR2(60) NOT NULL;
Why? Because your NOT NULL constraint conflicts instantly with the indeterminate or NULL value
of the company_name variable when it is instantiated.
Previous: 4.3 NULLs in PL/
SQL
Oracle PL/SQL
Programming, 2nd Edition
Next: 4.5 Anchored
Declarations
4.3 NULLs in PL/SQL
Book Index
4.5 Anchored Declarations
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Oracle Library
Navigation
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 4.2 Scalar
Datatypes
Chapter 4
Variables and Program Data
Next: 4.4 Variable
Declarations
4.3 NULLs in PL/SQL
Wouldn't it be nice if everything was knowable, and known? Hmmm. Maybe not. The question,
however, is moot. We don't know the answer to many questions. We are surrounded by the Big
Unknown, and because Oracle Corporation prides itself on providing database technology to reflect
the real world, it supports the concept of a null value.
When a variable, column, or constant has a value of NULL, its value is unknown indeterminate.
"Unknown" is very different from a blank or a zero or the Boolean value FALSE. "Unknown" means
that the variable has no value at all and so cannot be compared directly with other variables. The
following three rules hold for null values:
● A null is never equal to anything else. None of the following IF statements can ever evaluate
to TRUE:
my_string := ' ';
IF my_string = NULL THEN This will never be true.
max_salary := 0;
IF max_salary = NULL THEN This will never be
true.
IF NULL = NULL THEN Even this will never be true.
● A null is never not equal to anything else. Remember: with null values, you just never know.
None of the following IF statements can ever evaluate to TRUE.
my_string := 'Having Fun';
your_string := NULL;
IF my_string != your_string THEN This will never
be true.
max_salary := 1234;
IF max_salary != NULL THEN This will never be
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
true.
IF NULL != NULL THEN This will never be true.
● When you apply a function to a null value, you generally receive a null value as a result (there
are some exceptions, listed below). A null value cannot be found in a string with the INSTR
function. A null string has a null length, not a zero length. A null raised to the 10th power is
still null.
my_string := NULL;
IF LENGTH (my_string) = 0 THEN This will not
work.
new_value := POWER (NULL, 10); new_value is set to
null value.
4.3.1 NULL Values in Comparisons
In general, whenever you perform a comparison involving one or more null values, the result of that
comparison is also a null value which is different from TRUE or FALSE so the comparison
cannot help but fail.
Whenever PL/SQL executes a program, it initializes all locally declared variables to null (you can
override this value with your own default value). Always make sure that your variable has been
assigned a value before you use it in an operation.
You can also use special syntax provided by Oracle to check dependably for null values, and even
assign a null value to a variable. PL/SQL provides a special reserved word, NULL, to represent a null
value in PL/SQL. So if you want to actually set a variable to the null value, you simply perform the
following assignment:
my_string := NULL;
If you want to incorporate the possibility of null values in comparison operations, you must perform
special case checking with the IS NULL and IS NOT NULL operators. The syntax for these two
operators is as follows:
<identifier> IS NULL
<identifier> IS NOT NULL
where <identifier> is the name of a variable, a constant, or a database column. The IS NULL operator
returns TRUE when the value of the identifier is the null value; otherwise, it returns FALSE. The IS
NOT NULL operator returns TRUE when the value of the identifier is not a null value; otherwise, it
returns FALSE.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4.3.2 Checking for NULL Values
Here are some examples describing how to use operators to check for null values in your program:
● In the following example, the validation rule for the hire_date is that it cannot be later than the
current date and it must be entered. If the user does not enter a hire_date, then the comparison
to SYSDATE will fail because a null is never greater than or equal to (>=) anything. The
second part of the OR operator, however, explicitly checks for a null hire_date. If either
condition is TRUE, then we have a problem.
IF hire_date >= SYSDATE OR hire_date IS NULL
THEN
DBMS_OUTPUT.PUT_LINE (' Date required and cannot
be in future.');
END IF;
● In the following example, a bonus generator rewards the hard-working support people (not the
salespeople). If the employee's commission is over the target compensation plan target, then
send a thank you note. If the commission is under target, tell them to work harder, darn it! But
if the person has no commission at all (that is, if the commission IS NULL), give them a
bonus recognizing that everything they do aids in the sales effort. (You can probably figure
out what my job at Oracle Corporation was.) If the commission is a null value, then neither of
the first two expressions will evaluate to TRUE:
IF :employee.commission >= comp_plan.target_commission
THEN
just_send_THANK_YOU_note (:employee_id);
ELSIF :employee.commission < comp_plan.
target_commission
THEN
send_WORK_HARDER_singing_telegram (:employee_id);
ELSIF :employee.commission IS NULL
THEN
non_sales_BONUS (:employee_id);
END IF;
● PL/SQL treats a string of zero-length as a NULL. A zero-length string is two single quotes
without any characters in between. The following two assignments are equivalent:
my_string := NULL;
my_string := '';
4.3.3 Function Results with NULL Arguments
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét