Fundamentals of PL/SQL
There are six essentials in painting.
The first is called spirit; the second, rhythm; the third, thought; the
fourth, scenery; the fifth, the brush; and the last is the ink. --Ching Hao
The previous chapter provided an overview of PL/SQL. This
chapter focuses on the small-scale aspects of the language. Like every
other programming language, PL/SQL has a character set, reserved words,
punctuation, datatypes, rigid syntax, and fixed rules of usage and
statement formation. You use these basic elements of PL/SQL to represent
real-world objects and operations.
This chapter discusses the following topics:
- Character Set
- Lexical Units
- Declarations
- PL/SQL Naming Conventions
- Scope and Visibility of PL/SQL Identifiers
- Variable Assignment
- PL/SQL Expressions and Comparisons
- Built-In Functions
Character Set
You write a PL/SQL program as lines of text using a specific set of characters. The PL/SQL character set includes
- the upper- and lower-case letters
A..Zanda..z - the numerals
0..9 - the symbols
()+-*/<>=!~^;:.'@%,"#$&_|{}?[] - tabs, spaces, and carriage returns
PL/SQL is not case sensitive, so lower-case letters are
equivalent to corresponding upper-case letters except within string and
character literals.
Lexical Units
A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:
To improve readability, you can separate lexical units by
spaces. In fact, you must separate adjacent identifiers by a space or
punctuation. The following line is not allowed because the reserved
words
END and IF are joined:IF x > y THEN high := x; ENDIF; -- not allowed
However, you cannot embed spaces in lexical units except
for string literals and comments. For example, the following line is not
allowed because the compound symbol for assignment (
:=) is split:count : = count + 1; -- not allowed
To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare these
IF statements for readability:IF x>y THEN max:=x;ELSE max:=y;END IF; | IF x > y THEN | max := x; | ELSE | max := y; | END IF;
Delimiters
A delimiter is a simple or
compound symbol that has a special meaning to PL/SQL. For example, you
use delimiters to represent arithmetic operations such as addition and
subtraction. Simple symbols consist of one character. A list follows:
Compound symbols consist of two characters. A list follows:
Identifiers
You use identifiers to name PL/SQL program items and
units, which include constants, variables, exceptions, cursors, cursor
variables, subprograms, and packages. Some examples of identifiers
follow:
X t2 phone# credit_limit LastName oracle$number
An identifier consists of a letter optionally followed by
more letters, numerals, dollar signs, underscores, and number signs.
Other characters such as hyphens, slashes, and spaces are not allowed,
as the following examples show:
mine&yours -- not allowed because of ampersand debit-amount -- not allowed because of hyphen on/off -- not allowed because of slash user id -- not allowed because of space
The next examples show that adjoining and trailing dollar signs, underscores, and number signs are allowed:
money$$$tree SN## try_again_
You can use upper, lower, or mixed case to write
identifiers. PL/SQL is not case sensitive except within string and
character literals. So, if the only difference between identifiers is
the case of corresponding letters, PL/SQL considers the identifiers to
be the same, as the following example shows:
lastname LastName -- same as lastname LASTNAME -- same as lastname and LastName
The size of an identifier cannot exceed 30 characters.
But, every character, including dollar signs, underscores, and number
signs, is significant. For example, PL/SQL considers the following
identifiers to be different:
lastname last_name
Identifiers should be descriptive. So, avoid obscure names such as
cpm. Instead, use meaningful names such as cost_per_thousand.Reserved Words
Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL and so should not be redefined. For example, the words
BEGIN and END,
which bracket the executable part of a block or subprogram, are
reserved. As the next example shows, if you try to redefine a reserved
word, you get a compilation error:DECLARE end BOOLEAN; -- not allowed; causes compilation error
However, you can embed reserved words in an identifier, as the following example shows:
DECLARE end_of_game BOOLEAN; -- allowed
Often, reserved words are written in upper case to promote
readability. However, like other PL/SQL identifiers, reserved words can
be written in lower or mixed case. For a list of reserved words, see Appendix F.
Predefined Identifiers
Identifiers globally declared in package
STANDARD, such as the exception INVALID_NUMBER,
can be redeclared. However, redeclaring predefined identifiers is error
prone because your local declaration overrides the global declaration.Quoted Identifiers
For flexibility, PL/SQL lets you enclose identifiers
within double quotes. Quoted identifiers are seldom needed, but
occasionally they can be useful. They can contain any sequence of
printable characters including spaces but excluding double quotes. Thus,
the following identifiers are valid:
"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***"
The maximum size of a quoted identifier is 30 characters
not counting the double quotes. Though allowed, using PL/SQL reserved
words as quoted identifiers is a poor programming practice.
Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word
TYPE in a CREATE TABLE
statement to name a database column. But, if a SQL statement in your
program refers to that column, you get a compilation error, as the
following example shows:SELECT acct, type, bal INTO ... -- causes compilation error
To prevent the error, enclose the uppercase column name in double quotes, as follows:
SELECT acct, "TYPE", bal INTO ...
The column name cannot appear in lower or mixed case (unless it was defined that way in the
CREATE TABLE statement). For example, the following statement is invalid:SELECT acct, "type", bal INTO ... -- causes compilation error
Alternatively, you can create a view that renames the
troublesome column, then use the view instead of the base table in SQL
statements.
Literals
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal
147 and the Boolean literal FALSE are examples.Numeric Literals
Two kinds of numeric literals can be used in arithmetic
expressions: integers and reals. An integer literal is an optionally
signed whole number without a decimal point. Some examples follow:
030 6 -14 0 +32767
A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:
6.6667 0.0 -12.0 3.14159 +8300.00 .5 25.
PL/SQL considers numbers such as
12.0 and 25. to be reals even though they have integral values.
Numeric literals cannot contain dollar signs or commas,
but can be written using scientific notation. Simply suffix the number
with an
E (or e) followed by an optionally signed integer. A few examples follow:2E5 1.0E-7 3.14159e0 -1E38 -9.5e-3
E stands for "times ten to the power of." As the next example shows, the number after E is the power of ten by which the number before E must be multiplied (the double asterisk (**) is the exponentiation operator):5E3 = 5 * 10**3 = 5 * 1000 = 5000
The number after
E also corresponds to the
number of places the decimal point shifts. In the last example, the
implicit decimal point shifted three places to the right. In this
example, it shifts three places to the left:5E-3 = 5 * 10**-3 = 5 * 0.001 = 0.005
As the following example shows, if the value of a numeric literal falls outside the range
1E-130 .. 10E125, you get a compilation error:DECLARE n NUMBER; BEGIN n := 10E127; -- causes a 'numeric overflow or underflow' error
Character Literals
A character literal is an individual character enclosed by
single quotes (apostrophes). Character literals include all the
printable characters in the PL/SQL character set: letters, numerals,
spaces, and special symbols. Some examples follow:
'Z' '%' '7' ' ' 'z' '('
PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals
'Z' and 'z' to be different. Also, the character literals '0'..'9'
are not equivalent to integer literals but can be used in arithmetic
expressions because they are implicitly convertible to integers.String Literals
A character value can be represented by an identifier or
explicitly written as a string literal, which is a sequence of zero or
more characters enclosed by single quotes. Several examples follow:
'Hello, world!' 'XYZ Corporation' '10-NOV-91' 'He said "Life is like licking honey from a thorn."' '$1,000,000'
All string literals except the null string ('') have datatype
CHAR.
Given that apostrophes (single quotes) delimit string
literals, how do you represent an apostrophe within a string? As the
next example shows, you write two single quotes, which is not the same
as writing a double quote:
'Don''t leave without saving your work.'
PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:
'baker' 'Baker'
Boolean Literals
Boolean literals are the predefined values
TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Remember, Boolean literals are values, not strings. For example, TRUE is no less a value than the number 25.Datetime Literals
Datetime literals have various formats depending on the datatype. For example:
DECLARE d1 DATE := DATE '1998-12-25'; t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01'; t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00'; -- Three years and two months -- (For greater precision, we would use the day-to-second interval) i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH; -- Five days, four hours, three minutes, two and 1/100 seconds i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND; ...
You can also specify whether a given interval value is
YEAR TO MONTH or DAY TO SECOND. For example, current_timestamp - current_timestamp produces a value of type INTERVAL DAY TO SECOND by default. You can specify the type of the interval using the formats:
For details on the syntax for the date and time types, see the Oracle9i SQL Reference. For examples of performing date/time arithmetic, see Oracle9i Application Developer's Guide - Fundamentals.
Comments
The PL/SQL compiler ignores comments, but you should not.
Adding comments to your program promotes readability and aids
understanding. Generally, you use comments to describe the purpose and
use of each code segment. PL/SQL supports two comment styles:
single-line and multi-line.
Single-Line Comments
Single-line comments begin with a double hyphen (
--) anywhere on a line and extend to the end of the line. A few examples follow:-- begin processing SELECT sal INTO salary FROM emp -- get current salary WHERE empno = emp_id; bonus := salary * 0.15; -- compute bonus amount
Notice that comments can appear within a statement at the end of a line.
While testing or debugging a program, you might want to
disable a line of code. The following example shows how you can
"comment-out" the line:
-- DELETE FROM emp WHERE comm IS NULL;
Multi-line Comments
Multi-line comments begin with a slash-asterisk (
/*), end with an asterisk-slash (*/), and can span multiple lines. Some examples follow:BEGIN ... /* Compute a 15% bonus for top-rated employees. */ IF rating > 90 THEN bonus := salary * 0.15 /* bonus is based on salary */ ELSE bonus := 0; END IF; ... /* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. */ area := pi * radius**2; END;
You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:
/* LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... END LOOP; */
Restrictions on Comments
You cannot nest comments. Also, you cannot use single-line
comments in a PL/SQL block that will be processed dynamically by an
Oracle Precompiler program because end-of-line characters are ignored.
As a result, single-line comments extend to the end of the block, not
just to the end of a line. So, use multi-line comments instead.
Declarations
Your program stores values in variables and constants. As
the program executes, the values of variables can change, but the values
of constants cannot.
You can declare variables and constants in the declarative
part of any PL/SQL block, subprogram, or package. Declarations allocate
storage space for a value, specify its datatype, and name the storage
location so that you can reference it.
A couple of examples follow:
birthday DATE; emp_count SMALLINT := 0;
The first declaration names a variable of type
DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable.
The next examples show that the expression following the
assignment operator can be arbitrarily complex and can refer to
previously initialized variables:
pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2;
By default, variables are initialized to
NULL. So, these declarations are equivalent:birthday DATE; birthday DATE := NULL;
In the declaration of a constant, the keyword
CONSTANT must precede the type specifier, as the following example shows:credit_limit CONSTANT REAL := 5000.00;
This declaration names a constant of type
REAL
and assigns an initial (also final) value of 5000 to the constant. A
constant must be initialized in its declaration. Otherwise, you get a
compilation error when the declaration is elaborated. (The processing of
a declaration by the PL/SQL compiler is called elaboration.)Using DEFAULT
You can use the keyword
DEFAULT instead of the assignment operator to initialize variables. For example, the declarationblood_type CHAR := 'O';
can be rewritten as follows:
blood_type CHAR DEFAULT 'O';
Use
DEFAULT for variables that have a typical
value. Use the assignment operator for variables (such as counters and
accumulators) that have no typical value. A couple of examples follow:hours_worked INTEGER DEFAULT 40; employee_count INTEGER := 0;
You can also use
DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.Using NOT NULL
Besides assigning an initial value, declarations can impose the
NOT NULL constraint, as the following example shows:acct_id INTEGER(4) NOT NULL := 9999;
You cannot assign nulls to a variable defined as
NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. The NOT NULL constraint must be followed by an initialization clause. For example, the following declaration is not allowed:acct_id INTEGER(5) NOT NULL; -- not allowed; not initialized
PL/SQL provide subtypes
NATURALN and POSITIVEN that are predefined as NOT NULL. For instance, the following declarations are equivalent:emp_count NATURAL NOT NULL := 0; emp_count NATURALN := 0;
In
NATURALN and POSITIVEN
declarations, the type specifier must be followed by an initialization
clause. Otherwise, you get a compilation error. For example, the
following declaration is not allowed:line_items POSITIVEN; -- not allowed; not initialized
Using %TYPE
The
%TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:credit REAL(7,2); debit credit%TYPE;
Variables declared using
%TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable. The next example shows that a %TYPE declaration can include an initialization clause:balance NUMBER(7,2); minimum_balance balance%TYPE := 10.00;
The
%TYPE attribute is particularly useful
when declaring variables that refer to database columns. You can
reference a table and column, or you can reference an owner, table, and
column, as inmy_dname scott.dept.dname%TYPE;
Using
%TYPE to declare my_dname has two advantages. First, you need not know the exact datatype of dname. Second, if the database definition of dname changes, the datatype of my_dname changes accordingly at run time.
However,
%TYPE variables do not inherit the NOT NULL column constraint. In the next example, even though the database column empno is defined as NOT NULL, you can assign a null to the variable my_empno:DECLARE my_empno emp.empno%TYPE; ... BEGIN my_empno := NULL; -- this works
Using %ROWTYPE
The
%ROWTYPE attribute provides a record type
that represents a row in a table (or view). The record can store an
entire row of data selected from the table or fetched from a cursor or
strongly typed cursor variable. In the example below, you declare two
records. The first record stores a row selected from the emp table. The second record stores a row fetched from cursor c1.DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;
Columns in a row and corresponding fields in a record have the same names and datatypes. However, fields in a
%ROWTYPE record do not inherit the NOT NULL column constraint.
In the following example, you select column values into record
emp_rec:BEGIN SELECT * INTO emp_rec FROM emp WHERE ...
The column values returned by the
SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:IF emp_rec.deptno = 20 THEN ...
Also, you can assign the value of an expression to a specific field, as the following examples show:
emp_rec.ename := 'JOHNSON'; emp_rec.sal := emp_rec.sal * 1.15;
In the final example, you use
%ROWTYPE to define a packaged cursor:CREATE PACKAGE emp_actions AS CURSOR c1 RETURN emp%ROWTYPE; -- declare cursor specification ... END emp_actions; CREATE PACKAGE BODY emp_actions AS CURSOR c1 RETURN emp%ROWTYPE IS -- define cursor body SELECT * FROM emp WHERE sal > 3000; ... END emp_actions;
Aggregate Assignment
A
%ROWTYPE declaration cannot include an
initialization clause. However, there are two ways to assign values to
all fields in a record at once. First, PL/SQL allows aggregate
assignment between entire records if their declarations refer to the
same table or cursor. For example, the following assignment is allowed:DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec3 c1%ROWTYPE; BEGIN ... dept_rec1 := dept_rec2;
However, because
dept_rec2 is based on a table and dept_rec3 is based on a cursor, the following assignment is not allowed:dept_rec2 := dept_rec3; -- not allowed
Second, you can assign a list of column values to a record by using the
SELECT or FETCH statement, as the example below shows. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement.DECLARE dept_rec dept%ROWTYPE; ... BEGIN SELECT * INTO dept_rec FROM dept WHERE deptno = 30; ... END;
However, you cannot assign a list of column values to a
record by using an assignment statement. So, the following syntax is not
allowed:
record_name := (value1, value2, value3, ...); -- not allowed
Using Aliases
Select-list items fetched from a cursor associated with
%ROWTYPE must have simple names or, if they are expressions, must have aliases. In the following example, you use an alias called wages:-- available online in file 'examp4' DECLARE CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (NULL, my_rec.wages, my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;
Restrictions on Declarations
PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of
maxi is not allowed:maxi INTEGER := 2 * mini; -- not allowed mini INTEGER := 15;
However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Declaring PL/SQL Subprograms".
Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is not allowed:
i, j, k SMALLINT; -- not allowed
You must declare each variable separately:
i SMALLINT; j SMALLINT; k SMALLINT;
PL/SQL Naming Conventions
The same naming conventions apply to all PL/SQL program
items and units including constants, variables, cursors, cursor
variables, exceptions, procedures, functions, and packages. Names can be
simple, qualified, remote, or both qualified and remote. For example,
you might use the procedure name
raise_salary in any of the following ways:raise_salary(...); -- simple emp_actions.raise_salary(...); -- qualified raise_salary@newyork(...); -- remote emp_actions.raise_salary@newyork(...); -- qualified and remote
In the first case, you simply use the procedure name. In
the second case, you must qualify the name using dot notation because
the procedure is stored in a package called
emp_actions. In the third case, using the remote access indicator (@), you reference the database link newyork
because the procedure is stored in a remote database. In the fourth
case, you qualify the procedure name and reference a database link.Synonyms
You can create synonyms to provide location transparency
for remote schema objects such as tables, sequences, views, standalone
subprograms, packages, and object types. However, you cannot create
synonyms for items declared within subprograms or packages. That
includes constants, variables, cursors, cursor variables, exceptions,
and packaged subprograms.
Scoping
Within the same scope, all declared identifiers must be
unique. So, even if their datatypes differ, variables and parameters
cannot share the same name. In the following example, the second
declaration is not allowed:
DECLARE valid_id BOOLEAN; valid_id VARCHAR2(5); -- not allowed duplicate identifier
For the scoping rules that apply to identifiers, see "Scope and Visibility of PL/SQL Identifiers".
Case Sensitivity
Like all identifiers, the names of constants, variables,
and parameters are not case sensitive. For instance, PL/SQL considers
the following names to be the same:
DECLARE zip_code INTEGER; Zip_Code INTEGER; -- same as zip_code
Name Resolution
In potentially ambiguous SQL statements, the names of
database columns take precedence over the names of local variables and
formal parameters. For example, the following
DELETE statement removes all employees from the emp table, not just 'KING', because Oracle assumes that both enames in the WHERE clause refer to the database column:DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = ename; ...
In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with
my_, as follows:DECLARE my_ename VARCHAR2(10);
Or, use a block label to qualify references, as in
<<main>> DECLARE ename VARCHAR2(10) := 'KING'; BEGIN DELETE FROM emp WHERE ename = main.ename; ...
The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:
FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS job CHAR(10); BEGIN SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job; ...
For a full discussion of name resolution, see Appendix D.
Scope and Visibility of PL/SQL Identifiers
References to an identifier are resolved according to its scope and visibility. The scope
of an identifier is that region of a program unit (block, subprogram,
or package) from which you can reference the identifier. An identifier
is visible only in the regions from which you can reference the identifier using an unqualified name. Figure 2-1 shows the scope and visibility of a variable named
x, which is declared in an enclosing block, then redeclared in a sub-block.
Identifiers declared in a PL/SQL block are considered
local to that block and global to all its sub-blocks. If a global
identifier is redeclared in a sub-block, both identifiers remain in
scope. Within the sub-block, however, only the local identifier is
visible because you must use a qualified name to reference the global
identifier.
Although you cannot declare an identifier twice in the
same block, you can declare the same identifier in two different blocks.
The two items represented by the identifier are distinct, and any
change in one does not affect the other. However, a block cannot
reference identifiers declared in other blocks at the same level because
those identifiers are neither local nor global to the block.
Figure 2-1 Scope and Visibility
Text description of the illustration pls81007_scope_and_visibility.gif
The example below illustrates the scope rules. Notice that
the identifiers declared in one sub-block cannot be referenced in the
other sub-block. That is because a block cannot reference identifiers
declared in other blocks nested at the same level.
DECLARE a CHAR; b REAL; BEGIN -- identifiers available here: a (CHAR), b DECLARE a INTEGER; c REAL; BEGIN -- identifiers available here: a (INTEGER), b, c END; DECLARE d REAL; BEGIN -- identifiers available here: a (CHAR), b, d END; -- identifiers available here: a (CHAR), b END;
Recall that global identifiers can be redeclared in a
sub-block, in which case the local declaration prevails and the
sub-block cannot reference the global identifier unless you use a
qualified name. The qualifier can be the label of an enclosing block, as
the following example shows:
<<outer>> DECLARE birthdate DATE; BEGIN DECLARE birthdate DATE; BEGIN ... IF birthdate = outer.birthdate THEN ... END; ... END;
As the next example shows, the qualifier can also be the name of an enclosing subprogram:
PROCEDURE check_credit (...) IS rating NUMBER; FUNCTION valid (...) RETURN BOOLEAN IS rating NUMBER; BEGIN ... IF check_credit.rating < 3 THEN ... END; BEGIN ... END;
However, within the same scope, a label and a subprogram cannot have the same name.
Variable Assignment
Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to
NULL. Unless you expressly initialize a variable, its value is undefined:DECLARE count INTEGER; BEGIN -- COUNT began with a value of NULL. -- Thus the expression 'COUNT + 1' is also null. -- So after this assignment, COUNT is still NULL. count := count + 1;
To avoid unexpected results, never reference a variable before you assign it a value.
You can use assignment statements to assign values to a
variable. For example, the following statement assigns a new value to
the variable
bonus, overwriting its old value:bonus := salary * 0.15;
The expression following the assignment operator can be
arbitrarily complex, but it must yield a datatype that is the same as or
convertible to the datatype of the variable.
Assigning Boolean Values
Only the values
TRUE, FALSE, and NULL can be assigned to a Boolean variable. For example, given the declarationDECLARE done BOOLEAN;
the following statements are allowed:
BEGIN done := FALSE; WHILE NOT done LOOP ... END LOOP;
When applied to an expression, the relational operators return a Boolean value. So, the following assignment is allowed:
done := (count > 500);
Assigning a SQL Query Result to a PL/SQL Variable
You can use the
SELECT statement to have
Oracle assign values to a variable. For each item in the select list,
there must be a corresponding, type-compatible variable in the INTO list. An example follows:DECLARE emp_id emp.empno%TYPE; emp_name emp.ename%TYPE; wages NUMBER(7,2); BEGIN -- assign a value to emp_id here SELECT ename, sal + comm INTO emp_name, wages FROM emp WHERE empno = emp_id; ... END;
However, you cannot select column values into a Boolean variable.
PL/SQL Expressions and Comparisons
Expressions are constructed using operands and operators. An operand
is a variable, constant, literal, or function call that contributes a
value to an expression. An example of a simple arithmetic expression
follows:
-X / 2 + 3
Unary operators such as the negation operator (
-) operate on one operand; binary operators such as the division operator (/) operate on two operands. PL/SQL has no ternary operators.
The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates
(finds the current value of) an expression by combining the values of
the operands in ways specified by the operators. This always yields a
single value and datatype. PL/SQL determines the datatype by examining
the expression and the context in which it appears.
Operator Precedence
The operations within an expression are done in a particular order depending on their precedence (priority). Table 2-1 shows the default order of operations from first to last (top to bottom).
Table 2-1 Order of Operations
Operators with higher precedence are applied first. In the
example below, both expressions yield 8 because division has a higher
precedence than addition. Operators with the same precedence are applied
in no particular order.
5 + 12 / 4 12 / 4 + 5
You can use parentheses to control the order of
evaluation. For example, the following expression yields 7, not 11,
because parentheses override the default operator precedence:
(8 + 6) / 2
In the next example, the subtraction is done before the
division because the most deeply nested subexpression is always
evaluated first:
100 + (20 / 5 + (7 - 3))
The following example shows that you can always use parentheses to improve readability, even when they are not needed:
(salary * 0.05) + (commission * 0.25)
Logical Operators
The logical operators
AND, OR, and NOT follow the tri-state logic shown in Table 2-2. AND and OR are binary operators; NOT is a unary operator.Table 2-2 Logic Truth Table
As the truth table shows,
AND returns TRUE only if both its operands are true. On the other hand, OR returns TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.NOT NULL returns NULL because nulls are indeterminate. It follows that if you apply the NOT operator to a null, the result is also indeterminate. Be careful. Nulls can cause unexpected results; see "Handling Null Values in Comparisons and Conditional Statements".Order of Evaluation
When you do not use parentheses to specify the order of
evaluation, operator precedence determines the order. Compare the
following expressions:
NOT (valid AND done) | NOT valid AND done
If the Boolean variables
valid and done have the value FALSE, the first expression yields TRUE. However, the second expression yields FALSE because NOT has a higher precedence than AND. Therefore, the second expression is equivalent to:(NOT valid) AND done
In the following example, notice that when
valid has the value FALSE, the whole expression yields FALSE regardless of the value of done:valid AND done
Likewise, in the next example, when
valid has the value TRUE, the whole expression yields TRUE regardless of the value of done:valid OR done
Short-Circuit Evaluation
When evaluating a logical expression, PL/SQL uses short-circuit evaluation.
That is, PL/SQL stops evaluating the expression as soon as the result
can be determined. This lets you write expressions that might otherwise
cause an error. Consider the following
OR expression:DECLARE ... on_hand INTEGER; on_order INTEGER; BEGIN .. IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN ... END IF; END;
When the value of
on_hand is zero, the left operand yields TRUE, so PL/SQL need not evaluate the right operand. If PL/SQL were to evaluate both operands before applying the OR operator, the right operand would cause a division by zero error. In any case, it is a poor programming practice to rely on short-circuit evaluation.Comparison Operators
Comparison operators compare one expression to another.
The result is always true, false, or null. Typically, you use comparison
operators in conditional control statements and in the
WHERE clause of SQL data manipulation statements. Here are a couple of examples:IF quantity_on_hand > 0 THEN UPDATE inventory SET quantity = quantity - 1 WHERE part_number = item_number; ELSE ... END IF;
Relational Operators
The relational operators allow you to compare arbitrarily
complex expressions. The following list gives the meaning of each
operator:
| Operator | Meaning |
|---|---|
= |
equal to
|
<>, !=, ~=, ^= |
not equal to
|
< |
less than
|
> |
greater than
|
<= |
less than or equal to
|
>= |
greater than or equal to
|
IS NULL Operator
The
IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. So, test for nullity (the state of being null), as follows:IF variable IS NULL THEN ...
LIKE Operator
You use the
LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match.
The patterns matched by
LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression is true:ename LIKE 'J%SON'
BETWEEN Operator
The
BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression is false:45 BETWEEN 38 AND 44
IN Operator
The
IN operator tests set membership. It
means "equal to any member of." The set can contain nulls, but they are
ignored. For example, the following statement does not delete rows in which the ename column is null:DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');
Furthermore, expressions of the form
value NOT IN set
yield
FALSE if the set contains a null. For example, instead of deleting rows in which the ename column is not null and not 'KING', the following statement deletes no rows:DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');
Concatenation Operator
Double vertical bars (
||) serve as the concatenation operator, which appends one string (CHAR, VARCHAR2, CLOB, or the equivalent Unicode-enabled type) to another. For example, the expression'suit' || 'case'
returns the following value:
'suitcase'
If both operands have datatype
CHAR, the concatenation operator returns a CHAR value. If either operand is a CLOB value, the operator returns a temporary CLOB. Otherwise, it returns a VARCHAR2 value.Boolean Expressions
PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called Boolean expressions,
consist of simple or complex expressions separated by relational
operators. Often, Boolean expressions are connected by the logical
operators
AND, OR, and NOT. A Boolean expression always yields TRUE, FALSE, or NULL.
In a SQL statement, Boolean expressions let you specify
the rows in a table that are affected by the statement. In a procedural
statement, Boolean expressions are the basis for conditional control.
There are three kinds of Boolean expressions: arithmetic, character, and
date.
Boolean Arithmetic Expressions
You can use the relational operators to compare numbers
for equality or inequality. Comparisons are quantitative; that is, one
number is greater than another if it represents a larger quantity. For
example, given the assignments
number1 := 75; number2 := 70;
the following expression is true:
number1 > number2
Boolean Character Expressions
You can compare character values for equality or
inequality. By default, comparisons are based on the binary values of
each byte in the string.
For example, given the assignments
string1 := 'Kathy'; string2 := 'Kathleen';
the following expression is true:
string1 > string2
By setting the initialization parameter
NLS_COMP=ANSI, you can make comparisons use use the collating sequence identified by the NLS_SORT initialization parameter. A collating sequence
is an internal ordering of the character set in which a range of
numeric codes represents the individual characters. One character value
is greater than another if its internal numeric value is larger. Each
language might have different rules about where such characters occur in
the collating sequence. For example, an accented letter might be sorted
differently depending on the database character set, even though the
binary value is the same in each case.
There are semantic differences between the
CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, see Appendix B.
Many types can be converted to character types. For example, you can compare, assign, and do other character operations using
CLOB variables. For details on the possible conversions, see "Character Types".Boolean Date Expressions
You can also compare dates. Comparisons are chronological;
that is, one date is greater than another if it is more recent. For
example, given the assignments
date1 := '01-JAN-91'; date2 := '31-DEC-90';
the following expression is true:
date1 > date2
Guidelines for PL/SQL Boolean Expressions
- In general, do not
compare real numbers for exact equality or inequality. Real numbers are
stored as approximate values. So, for example, the following
IFcondition might not yieldTRUE:count := 1; IF count = 1.0 THEN ... END IF;
- It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because
100<taxyields a Boolean value, which cannot be compared with the number 500:100 < tax < 500 -- not allowed
The debugged version follows:(100 < tax) AND (tax < 500)
- A Boolean variable is itself either true or false. So, comparisons with the Boolean values
TRUEandFALSEare redundant. For example, assuming the variabledoneis of typeBOOLEAN, theWHILEstatementWHILE NOT (done = TRUE) LOOP ... END LOOP;
can be simplified as follows:WHILE NOT done LOOP ... END LOOP;
- Using
CLOBvalues with comparison operators, or functions such asLIKEandBETWEEN, can result in creation of temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs. For details, see the "Modeling and Design" chapter in Oracle9i Application Developer's Guide - Large Objects (LOBs).
CASE Expressions
A
CASE expression selects a result from one or more alternatives, and returns the result. The CASE expression uses a selector, an expression whose value determines which alternative to return. A CASE expression has the following form:CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1] END;
The selector is followed by one or more
WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. An example follows:DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(20); BEGIN appraisal := CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' WHEN 'D' THEN 'Fair' WHEN 'F' THEN 'Poor' ELSE 'No such grade' END; END;
The optional
ELSE clause works similarly to the ELSE clause in an IF statement. If the value of the selector is not one of the choices covered by a WHEN clause, the ELSE clause is executed. If no ELSE clause is provided and none of the WHEN clauses are matched, the expression returns NULL.
An alternative to the
CASE expression is the CASE statement, where each WHEN clause can be an entire PL/SQL block. For details, see "CASE Statement".Searched CASE Expression
PL/SQL also provides a searched
CASE expression, which has the form:CASE WHEN search_condition1 THEN result1 WHEN search_condition2 THEN result2 ... WHEN search_conditionN THEN resultN [ELSE resultN+1] END;
A searched
CASE expression has no selector. Each WHEN
clause contains a search condition that yields a Boolean value, which
lets you test different variables or multiple conditions in a single WHEN clause. An example follows:DECLARE grade CHAR(1); appraisal VARCHAR2(20); BEGIN ... appraisal := CASE WHEN grade = 'A' THEN 'Excellent' WHEN grade = 'B' THEN 'Very Good' WHEN grade = 'C' THEN 'Good' WHEN grade = 'D' THEN 'Fair' WHEN grade = 'F' THEN 'Poor' ELSE 'No such grade' END; ... END;
The search conditions are evaluated sequentially. The Boolean value of each search condition determines which
WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. After any WHEN clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields TRUE, the optional ELSE clause is executed. If no WHEN clause is executed and no ELSE clause is supplied, the value of the expression is NULL.Handling Null Values in Comparisons and Conditional Statements
When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:
- Comparisons involving nulls always yield
NULL - Applying the logical operator
NOTto a null yieldsNULL - In conditional control statements, if the condition yields
NULL, its associated sequence of statements is not executed - If the expression in a simple
CASEstatement orCASEexpression yieldsNULL, it cannot be matched by usingWHEN NULL. In this case, you would need to use the searched case syntax and testWHENexpressionIS NULL.
In the example below, you might expect the sequence of statements to execute because
x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.x := 5; y := NULL; ... IF x != y THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;
In the next example, you might expect the sequence of statements to execute because
a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.a := NULL; b := NULL; ... IF a = b THEN -- yields NULL, not TRUE sequence_of_statements; -- not executed END IF;
NOT Operator
Recall that applying the logical operator
NOT to a null yields NULL. Thus, the following two statements are not always equivalent:IF x > y THEN | IF NOT x > y THEN high := x; | high := y; ELSE | ELSE high := y; | high := x; END IF; | END IF;
The sequence of statements in the
ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.Zero-Length Strings
PL/SQL treats any zero-length string like a null. This
includes values returned by character functions and Boolean expressions.
For example, the following statements assign nulls to the target
variables:
null_string := TO_CHAR(''); zip_code := SUBSTR(address, 25, 0); valid := (name != '');
So, use the
IS NULL operator to test for null strings, as follows:IF my_string IS NULL THEN ...
Concatenation Operator
The concatenation operator ignores null operands. For example, the expression
'apple' || NULL || NULL || 'sauce'
returns the following value:
'applesauce'
Functions
If a null argument is passed to a built-in function, a null is returned except in the following cases.
The function
DECODE compares its first
argument to one or more search expressions, which are paired with result
expressions. Any search or result expression can be null. If a search
is successful, the corresponding result is returned. In the following
example, if the column rating is null, DECODE returns the value 1000:SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000) INTO credit_limit FROM accts WHERE acctno = my_acctno;
The function
NVL returns the value of its second argument if its first argument is null. In the example below, if hire_date is null, NVL returns the value of SYSDATE. Otherwise, NVL returns the value of hire_date:start_date := NVL(hire_date, SYSDATE);
The function
REPLACE returns the value of its
first argument if its second argument is null, whether the optional
third argument is present or not. For instance, after the assignmentnew_string := REPLACE(old_string, NULL, my_string);
the values of
old_string and new_string are the same.
If its third argument is null,
REPLACE returns its first argument with every occurrence of its second argument removed. For example, after the assignmentssyllabified_name := 'Gold-i-locks'; name := REPLACE(syllabified_name, '-', NULL);
the value of
name is 'goldilocks'
If its second and third arguments are null,
REPLACE simply returns its first argument.Built-In Functions
PL/SQL provides many powerful functions to help you
manipulate data. These built-in functions fall into the following
categories:
Table 2-3 shows the functions in each category. For descriptions of the error-reporting functions, see Chapter 13. For descriptions of the other functions, see Oracle9i SQL Reference.
Except for the error-reporting functions
SQLCODE and SQLERRM, you can use all the functions in SQL statements. Also, except for the object-reference functions DEREF, REF, and VALUE and the miscellaneous functions DECODE, DUMP, and VSIZE, you can use all the functions in procedural statements.
Although the SQL aggregate functions (such as
AVG and COUNT) and the SQL analytic functions (such as CORR and LAG) are not built into PL/SQL, you can use them in SQL statements (but not in procedural statements).Table 2-3 Built-In Functions
| Error | Number | Character | Conversion | Date | Obj Ref | Misc |
|---|---|---|---|---|---|---|
SQLERRM |
TRUNC |
UPPER |
TO_SINGLE_BYTE |
TRUNC |
TREAT |
VSIZE |
No comments:
Post a Comment