Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
The previous chapter provided an overview of PL/SQL. This chapter focuses on the detailed aspects of the language. Like other programming languages, PL/SQL has a character set, reserved words, punctuation, datatypes, and fixed syntax rules.
This chapter contains these topics:
PL/SQL programs are written as lines of text using a specific set of characters:
Upper- and lower-case letters A
.. Z
and a
.. z
Numerals 0
.. 9
Symbols (
)
+
-
*
/
<
>
=
!
~
^
;
:
.
'
@
%
,
"
#
$
&
_
|
{
}
?
[
]
Tabs, spaces, and carriage returns
PL/SQL keywords are not case-sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.
A line of PL/SQL text contains groups of characters known as lexical units:
Delimiters (simple and compound symbols)
Identifiers, which include reserved words
Literals
Comments
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, must be END
IF
You cannot embed spaces inside 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, must be :=
To show structure, you can split lines using carriage returns, and indent lines using spaces or tabs. This formatting makes the first IF
statement more readable.
IF x>y THEN max:=x;ELSE max:=y;END IF;
The following is easier to read:
IF x > y THEN
max := x;
ELSE
max := y;
END IF;
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. Table 2-1 contains a list of PL/SQL delimiters.
Table 2-1 PL/SQL Delimiters
Symbol | Meaning |
---|---|
+ |
addition operator |
% |
attribute indicator |
' |
character string delimiter |
. |
component selector |
/ |
division operator |
( |
expression or list delimiter |
) |
expression or list delimiter |
: |
host variable indicator |
, |
item separator |
* |
multiplication operator |
" |
quoted identifier delimiter |
= |
relational operator |
< |
relational operator |
> |
relational operator |
@ |
remote access indicator |
; |
statement terminator |
- |
subtraction/negation operator |
:= |
assignment operator |
=> |
association operator |
|| |
concatenation operator |
** |
exponentiation operator |
<< |
label delimiter (begin) |
>> |
label delimiter (end) |
/* |
multi-line comment delimiter (begin) |
*/ |
multi-line comment delimiter (end) |
.. |
range operator |
<> |
relational operator |
!= |
relational operator |
~= |
relational operator |
^= |
relational operator |
<= |
relational operator |
>= |
relational operator |
-- |
single-line comment indicator |
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
is not allowed because of the ampersanddebit-amount
is not allowed because of the hyphenon/off
is not allowed because of the slashuser id
is not allowed because of the spaceAdjoining 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. If the only difference between identifiers is the case of corresponding letters, PL/SQL considers them the same:
lastname
LastName
-- same as lastnameLASTNAME
-- same as lastname and LastNameThe size of an identifier cannot exceed 30 characters. 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. Avoid obscure names such as cpm
. Instead, use meaningful names such as cost_per_thousand
.
Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL. For example, the words BEGIN
and END
are reserved. Often, reserved words are written in upper case for readability.
Trying to redefine a reserved word causes a compilation error. Instead, you can embed reserved words as part of a longer identifier. For example:
DECLARE -- end BOOLEAN; the use of "end" is not allowed; causes compilation error end_of_game BOOLEAN; -- allowed
In addition to reserved words, there are keywords that have special meaning in PL/SQL. PL/SQL keywords can be used for identifiers, but this is not recommended. For a list of PL/SQL reserved words and keywords, see Table D-1, "PL/SQL Reserved Words" and Table D-2, "PL/SQL Keywords".
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.
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.
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. For information on the PL/SQL datatypes, see "Overview of Predefined PL/SQL Datatypes".
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.
A numeric literal value that is composed only of digits and falls in the range -2147483648 to 2147483647 has a PLS_INTEGER
datatype; otherwise this literal has the NUMBER
datatype. You can add the f
of d
suffix to a literal value that is composed only of digits to specify the BINARY_FLOAT
or BINARY_TABLE
respectively. For the properties of the datatypes, see "PL/SQL Number Types".
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
is 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
The absolute value of a NUMBER
literal can be in the range 1.0E-130
up to (but not including) 1.0E126
. The literal can also be 0
. See Example 2-1. For information on results outside the valid range, see "NUMBER Datatype".
Example 2-1 NUMBER Literals
DECLARE n NUMBER; -- declare n of NUMBER datatype BEGIN n := -9.999999E-130; -- valid n := 9.999E125; -- valid -- n := 10.0E125; -- invalid, "numeric overflow or underflow" END; /
Real literals can also use the trailing letters f
and d
to specify the types BINARY_FLOAT
and BINARY_DOUBLE
, as shown in Example 2-2.
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.
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. All string literals except the null string ('') have datatype CHAR
.
The following are examples of string literals:
'Hello, world!'
'XYZ Corporation'
'10-NOV-91'
'He said "Life is like licking honey from a thorn."'
'$1,000,000'
PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:
'baker'
'Baker'
To represent an apostrophe within a string, you can write two single quotes, which is not the same as writing a double quote:
'I''m a string, you''re a string.'
Doubling the quotation marks within a complicated literal, particularly one that represents a SQL statement, can be tricky. You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then do not need to escape other single quotation marks inside the literal:
-- q'!...!' notation allows the of use single quotes
-- inside the literal
string_var := q'!I'm a string, you're a string.!';
You can use delimiters [, {, <, and (, pair them with ], }, >, and ), pass a string literal representing a SQL statement to a subprogram, without doubling the quotation marks around 'INVALID' as follows:
func_call(q'[select index_name from user_indexes where status =
'INVALID']');
For NCHAR
and NVARCHAR2
literals, use the prefix nq
instead of q
:
where_clause := nq'#where col_value like '%é'#';
For more information about the NCHAR
datatype and unicode strings, see Oracle Database Globalization Support Guide.
BOOLEAN
literals are the predefined values TRUE
, FALSE
, and NULL
. NULL
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 have various formats depending on the datatype. For example:
Example 2-3 Using DateTime Literals
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:
(
interval_expression
) DAY TO SECOND
(
interval_expression
) YEAR TO MONTH
For details on the syntax for the date and time types, see the Oracle Database SQL Reference. For examples of performing date and time arithmetic, see Oracle Database Application Developer's Guide - Fundamentals.
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 begin with a double hyphen (--
) anywhere on a line and extend to the end of the line. A few examples follow:
Example 2-4 Using Single-Line Comments
DECLARE howmany NUMBER; num_tables NUMBER; BEGIN -- begin processing SELECT COUNT(*) INTO howmany FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables num_tables := howmany; -- Compute some other value END; /
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 disable a line by making it a comment:
-- DELETE FROM employees WHERE comm_pct IS NULL;
Multi-line comments begin with a slash-asterisk (/*
), end with an asterisk-slash (*/
), and can span multiple lines, as shown in Example 2-5. You can use multi-line comment delimiters to comment-out whole sections of code.
Example 2-5 Using Multi-Line Comments
DECLARE some_condition BOOLEAN; pi NUMBER := 3.1415926; radius NUMBER := 15; area NUMBER; BEGIN /* Perform some simple tests and assignments */ IF 2 + 2 = 4 THEN some_condition := TRUE; /* We expect this THEN to always be performed */ END IF; /* The following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. After the area is computed, the result is displayed. */ area := pi * radius**2; DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area)); END; /
You cannot nest comments. You cannot use single-line comments in a PL/SQL block that will be processed 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. In this case, use the /* */
notation instead.
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.
Some examples follow:
DECLARE 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:
DECLARE pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2;
By default, variables are initialized to NULL
, so it is redundant to include ":= NULL"
in a variable declaration.
To declare a constant, put the keyword CONSTANT
before the type specifier. The following declaration names a constant of type REAL
and assigns an unchangeable value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, a compilation error occurs.
DECLARE credit_limit CONSTANT REAL := 5000.00; max_days_in_year CONSTANT INTEGER := 366; urban_legend CONSTANT BOOLEAN := FALSE;
You can use the keyword DEFAULT
instead of the assignment operator to initialize variables. For example, the declaration
blood_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. For example:
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.
Besides assigning an initial value, declarations can impose the NOT
NULL
constraint:
DECLARE
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.
PL/SQL provide subtypes NATURALN
and POSITIVEN
that are predefined as NOT
NULL
. You can omit the NOT NULL
constraint when declaring variables of these types, and you must include an initialization clause.
The %TYPE
attribute provides the datatype of a variable or database column. As shown in Example 2-6, variables declared with %TYPE
inherit the datatype of a variable, plus default values and constraints.
Example 2-6 Using %TYPE With the Datatype of a Variable
DECLARE credit PLS_INTEGER RANGE 1000..25000; debit credit%TYPE; v_name VARCHAR2(20); name VARCHAR2(20) NOT NULL := 'JoHn SmItH'; -- If we increase the length of NAME, the other variables become longer also upper_name name%TYPE := UPPER(name); lower_name name%TYPE := LOWER(name); init_name name%TYPE := INITCAP(name); BEGIN -- display inherited default values DBMS_OUTPUT.PUT_LINE('name: ' || name || ' upper_name: ' || upper_name || ' lower_name: ' || lower_name || ' init_name: ' || init_name); -- lower_name := 'jonathan henry smithson'; invalid, character string is too long -- lower_name := NULL; invalid, NOT NULL CONSTRAINT -- debit := 50000; invalid, value out of range END; /
Note that 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 PLS_INTEGER
variable. A %TYPE
declaration can also include an initialization clause.
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 in:
DECLARE -- If the length of the column ever changes, this code -- will use the new length automatically. the_trigger user_triggers.trigger_name%TYPE;
When you use table_name.column_name.%TYPE
to declare a variable, you do not need to know the actual datatype, and attributes such as precision, scale, and length. If the database definition of the column changes, the datatype of the variable changes accordingly at run time. However, %TYPE
variables do not inherit column constraints, such as the NOT
NULL
or check constraint, or default values. For example, even though the database column empid
is defined as NOT
NULL
in Example 2-7, you can assign a NULL
to the variable v_empid
.
Example 2-7 Using %TYPE With Table Columns
CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200), deptname VARCHAR2(30) DEFAULT 'Sales'); DECLARE v_empid employees_temp.empid%TYPE; v_deptid employees_temp.deptid%TYPE; v_deptname employees_temp.deptname%TYPE; BEGIN v_empid := NULL; -- this works, null constraint is not inherited -- v_empid := 10000002; -- invalid, number precision too large v_deptid := 50; -- this works, check constraint is not inherited -- the default value is not inherited in the following DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname); END; /
See "Constraints and Default Values With Subtypes" for information on column constraints that are inherited by subtypes declared using %TYPE
.
The %ROWTYPE
attribute provides a record type that represents a row in a table or view. 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 constraints, such as the NOT
NULL
or check constraint, or default values, as shown in Example 2-8. See also Example 3-11.
Example 2-8 Using %ROWTYPE With Table Rows
DECLARE emprec employees_temp%ROWTYPE; BEGIN emprec.empid := NULL; -- this works, null constraint is not inherited -- emprec.empid := 10000002; -- invalid, number precision too large emprec.deptid := 50; -- this works, check constraint is not inherited -- the default value is not inherited in the following DBMS_OUTPUT.PUT_LINE('emprec.deptname: ' || emprec.deptname); END; /
The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable as shown in Example 2-9.
Example 2-9 Using the %ROWTYPE Attribute
DECLARE -- %ROWTYPE can include all the columns in a table... emp_rec employees%ROWTYPE; -- ...or a subset of the columns, based on a cursor. CURSOR c1 IS SELECT department_id, department_name FROM departments; dept_rec c1%ROWTYPE; -- Could even make a %ROWTYPE with columns from multiple tables. CURSOR c2 IS SELECT employee_id, email, employees.manager_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id; join_rec c2%ROWTYPE; BEGIN -- We know EMP_REC can hold a row from the EMPLOYEES table. SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2; -- We can refer to the fields of EMP_REC using column names -- from the EMPLOYEES table. IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN emp_rec.salary := emp_rec.salary * 1.15; END IF; END; /
Although a %ROWTYPE
declaration cannot include an initialization clause, there are ways to assign values to all fields in a record at once. You can assign one record to another if their declarations refer to the same table or cursor. Example 2-10 shows record assignments that are allowed.
Example 2-10 Assigning Values to a Record With a %ROWTYPE Declaration
DECLARE dept_rec1 departments%ROWTYPE; dept_rec2 departments%ROWTYPE; CURSOR c1 IS SELECT department_id, location_id FROM departments; dept_rec3 c1%ROWTYPE; BEGIN dept_rec1 := dept_rec2; -- allowed -- dept_rec2 refers to a table, dept_rec3 refers to a cursor -- dept_rec2 := dept_rec3; -- not allowed END; /
You can assign a list of column values to a record by using the SELECT
or FETCH
statement, as the following example 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 departments%ROWTYPE; BEGIN SELECT * INTO dept_rec FROM departments WHERE department_id = 30 and ROWNUM < 2; END; /
However, there is no constructor for a record type, so you cannot assign a list of column values to a record by using an assignment statement.
Select-list items fetched from a cursor associated with %ROWTYPE
must have simple names or, if they are expressions, must have aliases. Example 2-11 uses an alias called complete_name
to represent the concatenation of two columns:
Example 2-11 Using an Alias for Column Names
BEGIN -- We assign an alias (complete_name) to the expression value, because -- it has no column name. FOR item IN ( SELECT first_name || ' ' || last_name complete_name FROM employees WHERE ROWNUM < 11 ) LOOP -- Now we can refer to the field in the record using this alias. DBMS_OUTPUT.PUT_LINE('Employee name: ' || item.complete_name); END LOOP; END; /
PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements.
PL/SQL does allow the forward declaration of subprograms. For more information, see "Declaring Nested PL/SQL Subprograms".
Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. You must declare each variable separately:
DECLARE -- Multiple declarations not allowed. -- i, j, k, l SMALLINT; -- Instead, declare each separately. i SMALLINT; j SMALLINT; -- To save space, you can declare more than one on a line. k SMALLINT; l SMALLINT;
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(...);
-- simpleemp_actions.raise_salary(...);
-- qualifiedraise_salary@newyork(...);
-- remoteemp_actions.raise_salary@newyork(...);
-- qualified and remoteIn 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; even if their datatypes differ, variables and parameters cannot share the same name. In Example 2-12, the second declaration is not allowed.
Example 2-12 Errors With Duplicate Identifiers in Same Scope
DECLARE valid_id BOOLEAN; valid_id VARCHAR2(5); -- not allowed, duplicate identifier BEGIN -- The error occurs when the identifier is referenced, -- not in the declaration part. valid_id := FALSE; -- raises an error here END; /
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:
Example 2-13 Case Sensitivity of Identifiers
DECLARE zip_code INTEGER; Zip_Code INTEGER; -- duplicate identifier, despite Z/z case difference BEGIN zip_code := 90120; -- raises error here because of duplicate identifiers END; /
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, if a variable and a column with the same name are both used in a WHERE
clause, SQL considers that both cases refer to the column.
To avoid ambiguity, add a prefix to the names of local variables and formal parameters, or use a block label to qualify references as shown in Example 2-14.
Example 2-14 Using a Block Label for Name Resolution
CREATE TABLE employees2 AS SELECT last_name FROM employees; <<main>> DECLARE last_name VARCHAR2(10) := 'King'; v_last_name VARCHAR2(10) := 'King'; BEGIN -- deletes everyone, because both LAST_NAMEs refer to the column DELETE FROM employees2 WHERE last_name = last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; -- OK, column and variable have different names DELETE FROM employees2 WHERE last_name = v_last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; -- OK, block name specifies that 2nd last_name is a variable DELETE FROM employees2 WHERE last_name = main.last_name; DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.'); ROLLBACK; END; /
Example 2-15 shows that you can use a subprogram name to qualify references to local variables and formal parameters.
Example 2-15 Using a Subprogram Name for Name Resolution
DECLARE FUNCTION dept_name (department_id IN NUMBER) RETURN departments.department_name%TYPE IS department_name departments.department_name%TYPE; BEGIN -- DEPT_NAME.department_name specifies the local variable -- instead of the table column SELECT department_name INTO dept_name.department_name FROM departments WHERE department_id = dept_name.department_id; RETURN department_name; END; BEGIN FOR item IN (SELECT department_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name(item.department_id)); END LOOP; END; /
For a full discussion of name resolution, see Appendix B, "How PL/SQL Resolves Identifier Names".
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.
Example 2-16 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.
Example 2-16 Scope Rules
DECLARE a CHAR; b REAL; BEGIN -- identifiers available here: a (CHAR), b DECLARE a INTEGER; c REAL; BEGIN NULL; -- identifiers available here: a (INTEGER), b, c END; DECLARE d REAL; BEGIN NULL; -- 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 shown in Example 2-17.
Example 2-17 Using a Label Qualifier With Identifiers
<<outer>> DECLARE birthdate DATE := '09-AUG-70'; BEGIN DECLARE birthdate DATE; BEGIN birthdate := '29-SEP-70'; IF birthdate = outer.birthdate THEN DBMS_OUTPUT.PUT_LINE ('Same Birthday'); ELSE DBMS_OUTPUT.PUT_LINE ('Different Birthday'); END IF; END; END; /
As Example 2-18 shows, the qualifier can also be the name of an enclosing subprogram:
Example 2-18 Using Subprogram Qualifier With Identifiers
CREATE OR REPLACE PROCEDURE check_credit(limit NUMBER) AS rating NUMBER := 3; FUNCTION check_rating RETURN BOOLEAN IS rating NUMBER := 1; over_limit BOOLEAN; BEGIN IF check_credit.rating <= limit THEN over_limit := FALSE; ELSE rating := limit; over_limit := TRUE; END IF; RETURN over_limit; END check_rating; BEGIN IF check_rating THEN DBMS_OUTPUT.PUT_LINE( 'Credit rating over limit (' || TO_CHAR(limit) || ').' || ' Rating: ' || TO_CHAR(rating)); ELSE DBMS_OUTPUT.PUT_LINE( 'Credit rating OK. ' || 'Rating: ' || TO_CHAR(rating) ); END IF; END; / CALL check_credit(1);
However, within the same scope, a label and a subprogram cannot have the same name. The use of duplicate labels, illustrated in Example 2-19, should be avoided.
Example 2-19 PL/SQL Block Using Multiple and Duplicate Labels
<<compute_ratio>> <<another_label>> DECLARE numerator NUMBER := 22; denominator NUMBER := 7; the_ratio NUMBER; BEGIN <<inner_label>> <<another_label>> DECLARE denominator NUMBER := 0; BEGIN -- first use the denominator value = 7 from global DECLARE -- to compute a rough value of pi the_ratio := numerator/compute_ratio.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); -- now use the local denominator value = 0 to raise an exception -- inner_label is not needed but used for clarification the_ratio := numerator/inner_label.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); -- if you use a duplicate label, you might get errors -- or unpredictable results the_ratio := numerator/another_label.denominator; DBMS_OUTPUT.PUT_LINE('Ratio = ' || the_ratio); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide ' || numerator || ' by ' || denominator); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unexpected error.'); END inner_label; END compute_ratio; /
You can use assignment statements to assign values to variables. For example, the following statement assigns a new value to the variable bonus
, overwriting its old value:
bonus := salary * 0.15;
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 (NULL
) as shown in Example 2-20.
Example 2-20 Initialization of Variables and Constants
DECLARE counter INTEGER; BEGIN -- COUNTER is initially NULL, so 'COUNTER + 1' is also null. counter := counter + 1; IF counter IS NULL THEN DBMS_OUTPUT.PUT_LINE('COUNTER is NULL not 1.'); END IF; END; /
To avoid unexpected results, never reference a variable before you assign it a value. 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.
Only the values TRUE
, FALSE
, and NULL
can be assigned to a BOOLEAN
variable as shown in Example 2-21. You can assign these literal values, or expressions such as comparisons using relational operators.
Example 2-21 Assigning BOOLEAN Values
DECLARE done BOOLEAN; -- DONE is initially NULL counter NUMBER := 0; BEGIN done := FALSE; -- Assign a literal value WHILE done != TRUE -- Compare to a literal value LOOP counter := counter + 1; done := (counter > 500); -- If counter > 500, DONE = TRUE END LOOP; END; /
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 as shown in Example 2-22.
Example 2-22 Assigning a Query Result to a Variable
DECLARE emp_id employees.employee_id%TYPE := 100; emp_name employees.last_name%TYPE; wages NUMBER(7,2); BEGIN SELECT last_name, salary + (salary * nvl(commission_pct,0)) INTO emp_name, wages FROM employees WHERE employee_id = emp_id; DBMS_OUTPUT.PUT_LINE('Employee ' || emp_name || ' might make ' || wages); END; /
Because SQL does not have a BOOLEAN
type, you cannot select column values into a BOOLEAN
variable. For additional information on assigning variables with the DML statements, including situations when the value of a variable is undefined, see "Data Manipulation".
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 an expression by combining the values of the operands in ways specified by the operators. An expression always returns a single value. PL/SQL determines the datatype of this value 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-2 shows the default order of operations from first to last (top to bottom).
Table 2-2 Order of Operations
Operator | Operation |
---|---|
** |
exponentiation |
+ , - |
identity, negation |
* , / |
multiplication, division |
+ , - , || |
addition, subtraction, concatenation |
= , < , > , <= , >= , <> , != , ~= , ^= , IS NULL , LIKE , BETWEEN , IN |
comparison |
NOT |
logical negation |
AND |
conjunction |
OR |
inclusion |
Operators with higher precedence are applied first. In the following example, 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)
The logical operators AND
, OR
, and NOT
follow the tri-state logic shown in Table 2-3. AND
and OR
are binary operators; NOT
is a unary operator.
Table 2-3 Logic Truth Table
x | y | x AND y | x OR y | NOT x |
---|---|---|---|---|
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
FALSE |
TRUE |
FALSE |
TRUE |
NULL |
NULL |
TRUE |
FALSE |
FALSE |
TRUE |
FALSE |
TRUE |
TRUE |
FALSE |
FALSE |
FALSE |
FALSE |
TRUE |
FALSE |
NULL |
FALSE |
NULL |
TRUE |
NULL |
TRUE |
NULL |
TRUE |
NULL |
NULL |
FALSE |
FALSE |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
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. Be careful to avoid unexpected results in expressions involving nulls; see "Handling Null Values in Comparisons and Conditional Statements".
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
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 OR
expression in Example 2-23.
Example 2-23 Short-Circuit Evaluation
DECLARE on_hand INTEGER := 0; on_order INTEGER := 100; BEGIN -- Does not cause divide-by-zero error; evaluation stops after first expression IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.'); END IF; END; /
When the value of on_hand
is zero, the left operand yields TRUE
, so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR
operator, the right operand would cause a division by zero error.
Short-circuit evaluation applies to IF
statements, CASE
statements, and CASE
expressions in PL/SQL.
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. Example 2-24 provides some examples of comparisons for different types.
Example 2-24 Using Comparison Operators
DECLARE PROCEDURE assert(assertion VARCHAR2, truth BOOLEAN) IS BEGIN IF truth IS NULL THEN DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is unknown (NULL)'); ELSIF truth = TRUE THEN DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('Assertion ' || assertion || ' is FALSE'); END IF; END; BEGIN assert('2 + 2 = 4', 2 + 2 = 4); assert('10 > 1', 10 > 1); assert('10 <= 1', 10 <= 1); assert('5 BETWEEN 1 AND 10', 5 BETWEEN 1 AND 10); assert('NULL != 0', NULL != 0); assert('3 IN (1,3,5)', 3 IN (1,3,5)); assert('''A'' < ''Z''', 'A' < 'Z'); assert('''baseball'' LIKE ''%all%''', 'baseball' LIKE '%all%'); assert('''suit'' || ''case'' = ''suitcase''', 'suit' || 'case' = 'suitcase'); END; /
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
. Test whether a value is null as follows:
IF variable IS NULL THEN ...
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 last_name
is 'JOHNSON'
, the following expression is true:
last_name LIKE 'J%S_N'
To search for the percent sign and underscore characters, you define an escape character and put that character before the percent sign or underscore. The following example uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard:
IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN...
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
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 expression tests whether a value is part of a set of values:
letter IN ('a','b','c')
Be careful when inverting this condition. Expressions of the form:
value NOT IN set
yield FALSE
if the set contains a null.
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.
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.
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
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 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.
Depending on the value of the NLS_SORT
parameter, you can perform comparisons that are case-insensitive and even accent-insensitive. A case-insensitive comparison still returns true if the letters of the operands are different in terms of uppercase and lowercase. An accent-insensitive comparison is case-insensitive, and also returns true if the operands differ in accents or punctuation characters. For example, the character values 'True'
and 'TRUE'
are considered identical by a case-insensitive comparison; the character values 'Cooperate'
, 'Co-Operate'
, and 'coöperate'
are all considered the same. To make comparisons case-insensitive, add _CI
to the end of your usual value for the NLS_SORT
parameter. To make comparisons accent-insensitive, add _AI
to the end of the NLS_SORT
value.
There are semantic differences between the CHAR
and VARCHAR2
base types that come into play when you compare character values. For more information, see "Differences between the CHAR and VARCHAR2 Datatypes".
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 "PL/SQL Character and String Types".
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
In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. For example, the following IF
condition might not yield TRUE
:
DECLARE fraction BINARY_FLOAT := 1/3; BEGIN IF fraction = 11/33 THEN DBMS_OUTPUT.PUT_LINE('Fractions are equal (luckily!)'); END IF; END; /
It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because 100
<
tax
yields 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. You can just use the variable in a conditional test, rather than comparing it to the literal values TRUE
and FALSE
. In Example 2-25 the loops are all equivalent.
Example 2-25 Using BOOLEAN Variables in Conditional Tests
DECLARE done BOOLEAN ; BEGIN -- Each WHILE loop is equivalent done := FALSE; WHILE done = FALSE LOOP done := TRUE; END LOOP; done := FALSE; WHILE NOT (done = TRUE) LOOP done := TRUE; END LOOP; done := FALSE; WHILE NOT done LOOP done := TRUE; END LOOP; END; /
Using CLOB
values with comparison operators, or functions such as LIKE
and BETWEEN
, can create temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs.
There are two types of expressions used in CASE statements: simple and searched. These expressions correspond to the type of CASE statement in which they are used. See "Using CASE Statements".
A simple CASE
expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a procedure call. The CASE
expression uses a selector, an expression whose value determines which alternative to return.
A CASE
expression has the form illustrated in Example 2-26. The selector (grade
) is followed by one or more WHEN
clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN
clause that matches the value of the selector determines the result value, and subsequent WHEN
clauses are not evaluated. If there are no matches, then the optional ELSE
clause is performed.
Example 2-26 Using the WHEN Clause With a CASE Statement
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; DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal); 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
.
A searched CASE
expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 2-27.
A searched CASE
expression has no selector. Each WHEN
clause contains a search condition that yields a BOOLEAN
value, so you can test different variables or multiple conditions in a single WHEN
clause.
Example 2-27 Using a Search Condition With a CASE Statement
DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(120); id NUMBER := 8429862; attendance NUMBER := 150; min_days CONSTANT NUMBER := 200; FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS BEGIN RETURN TRUE; END; BEGIN appraisal := CASE WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled' -- Have to test this condition early to detect good students with bad attendance WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)' WHEN grade = 'A' THEN 'Excellent' WHEN grade = 'B' THEN 'Very Good' WHEN grade = 'C' THEN 'Good' WHEN grade = 'D' THEN 'Fair' ELSE 'No such grade' END; DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal); 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
.
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 NOT
to a null yields NULL
In conditional control statements, if the condition yields NULL
, its associated sequence of statements is not executed
If the expression in a simple CASE
statement or CASE
expression yields NULL
, it cannot be matched by using WHEN NULL
. In this case, you would need to use the searched case syntax and test WHEN
expression
IS NULL
.
In Example 2-28, 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.
Example 2-28 Using NULLs in Comparisons
DECLARE x NUMBER := 5; y NUMBER := NULL; BEGIN IF x != y THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('x != y'); -- not executed ELSIF x = y THEN -- also yields NULL DBMS_OUTPUT.PUT_LINE('x = y'); ELSE DBMS_OUTPUT.PUT_LINE('Can''t tell if x and y are equal or not.'); END IF; END; /
In the following 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.
DECLARE a NUMBER := NULL; b NUMBER := NULL; BEGIN IF a = b THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('a = b'); -- not executed ELSIF a != b THEN -- yields NULL, not TRUE DBMS_OUTPUT.PUT_LINE('a != b'); -- not executed ELSE DBMS_OUTPUT.PUT_LINE('Can''t tell if two NULLs are equal'); END IF; END; /
Recall that applying the logical operator NOT
to a null yields NULL
. Thus, the following two IF
statements are not always equivalent:
IF x > y THEN high := x; ELSE high := y; END IF;
IF NOT x > y THEN high := y; ELSE high := x; 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
.
NULLs and 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:
DECLARE null_string VARCHAR2(80) := TO_CHAR(''); address VARCHAR2(80); zip_code VARCHAR2(80) := SUBSTR(address, 25, 0); name VARCHAR2(80); valid BOOLEAN := (name != '');
Use the IS
NULL
operator to test for null strings, as follows:
IF v_string IS NULL THEN ...
NULLs and the Concatenation Operator
The concatenation operator ignores null operands. For example, the expression
'apple' || NULL || NULL || 'sauce'
returns the following value:
'applesauce'
NULLs as Arguments to Built-In 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 Example 2-29, if the column manager_id
is null, DECODE
returns the value 'nobody
':
Example 2-29 Using the Function DECODE
DECLARE the_manager VARCHAR2(40); name employees.last_name%TYPE; BEGIN -- NULL is a valid argument to DECODE. In this case, manager_id is null -- and the DECODE function returns 'nobody'. SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name INTO the_manager, name FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(name || ' is managed by ' || the_manager); END; /
The function NVL
returns the value of its second argument if its first argument is null. In Example 2-30, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:
Example 2-30 Using the Function NVL
DECLARE the_manager employees.manager_id%TYPE; name employees.last_name%TYPE; BEGIN -- NULL is a valid argument to NVL. In this case, manager_id is null -- and the NVL function returns -1. SELECT NVL(manager_id, -1), last_name INTO the_manager, name FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(name || ' is managed by employee Id: ' || the_manager); END; /
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 example, the call to REPLACE
in Example 2-31 does not make any change to the value of OLD_STRING
:
Example 2-31 Using the Function REPLACE
DECLARE string_type VARCHAR2(60); old_string string_type%TYPE := 'Apples and oranges'; v_string string_type%TYPE := 'more apples'; -- NULL is a valid argument to REPLACE, but does not match -- anything so no replacement is done. new_string string_type%TYPE := REPLACE(old_string, NULL, v_string); BEGIN DBMS_OUTPUT.PUT_LINE('Old string = ' || old_string); DBMS_OUTPUT.PUT_LINE('New string = ' || new_string); END; /
If its third argument is null, REPLACE
returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE
removes all the dashes from DASHED_STRING
, instead of changing them to another character:
DECLARE string_type VARCHAR2(60); dashed string_type%TYPE := 'Gold-i-locks'; -- When the substitution text for REPLACE is NULL, -- the text being replaced is deleted. name string_type%TYPE := REPLACE(dashed, '-', NULL); BEGIN DBMS_OUTPUT.PUT_LINE('Dashed name = ' || dashed); DBMS_OUTPUT.PUT_LINE('Dashes removed = ' || name); END; /
If its second and third arguments are null, REPLACE
just returns its first argument.
Using conditional compilation, you can customize the functionality in a PL/SQL application without having to remove any source code. For example, using conditional compilation you can customize a PL/SQL application to:
Utilize the latest functionality with the latest database release and disable the new features to run the application against an older release of the database
Activate debugging or tracing functionality in the development environment and hide that functionality in the application while it runs at a production site
See the discussion of new features in "Conditional Compilation". For business use scenarios and best practices information, visit the Oracle Technology Web site at http://www.oracle.com/technology/tech/pl_sql/
.
Conditional compilation uses selection directives, inquiry directives, and error directives to specify source text for compilation. Inquiry directives access values set up through name-value pairs in the PLSQL_CCFLAGS
initialization parameter. Selection directives can test inquiry directives or static package constants.
The DBMS_DB_VERSION
package provides database version and release constants that can be used for conditional compilation. The DBMS_PREPROCESSOR
package provides subprograms for accessing the post-processed source text that is selected by conditional compilation directives in a PL/SQL unit.
Note:
The conditional compilation feature and related PL/SQL packages are available for Oracle release 10.1.0.4 and later releases.The conditional compilation trigger character is $ and is used to identify code that is processed before the application is compiled. A conditional compilation control token is of the form:
preprocessor_control_token ::= $plsql_identifier
The $ must be at the beginning of the identifier name and there cannot be a space between the $ and the name. The $ can also be embedded in the identifier name, but it has no special meaning. The reserved preprocessor control tokens are $IF
, $THEN
, $ELSE
, $ELSIF
, $END
, and $ERROR
. For an example of the use of the conditional compilation control tokens, see Example 2-34.
The conditional compilation selection directive evaluates static expressions to determine which text should be included in the compilation. The selection directive is of the form:
$IF boolean_static_expression $THEN text
[ $ELSIF boolean_static_expression $THEN text ]
[ $ELSE text ]
$END
boolean_static_expression
must be a BOOLEAN
static expression. For a description of BOOLEAN
static expressions, see "Using Static Expressions with Conditional Compilation". For information on PL/SQL IF
.. THEN
control structures, see "Testing Conditions: IF and CASE Statements".
The error directive $ERROR
raises a user-defined error and is of the form:
$ERROR varchar2_static_expression $END
varchar2_static_expression
must be a VARCHAR2
static expression. For a description of VARCHAR2
static expressions, see "Using Static Expressions with Conditional Compilation". See Example 2-33.
The inquiry directive is used to check the compilation environment. The inquiry directive is of the form:
inquiry_directive ::= $$
id
An inquiry directive can be predefined as described in "Using Predefined Inquiry Directives With Conditional Compilation" or be user-defined. The following describes the order of the processing flow when conditional compilation attempts to resolve an inquiry directive:
The id
is used as an inquiry directive in the form $$
id
for the search key.
The two-pass algorithm proceeds as follows:
The string in the PLSQL_CCFLAGS
initialization parameter is scanned from right to left, searching with id
for a matching name (case insensitive); done if found.
The predefined inquiry directives are searched; done if found.
If the $$
id
cannot be resolved to a value, then the PLW-6003
warning message is reported if the source text is not wrapped. The literal NULL
is substituted as the value for undefined inquiry directives. Note that if the PL/SQL code is wrapped, then the warning message is disabled so that the undefined inquiry directive is not revealed.
For example, given the following session setting:
ALTER SESSION SET
PLSQL_CCFLAGS = 'plsql_ccflags:true, debug:true, debug:0';
The value of $$debug
is 0
and the value of $$plsql_ccflags
is TRUE
. Note that the value of $$plsql_ccflags
resolves to the user-defined plsql_ccflags
inside the value of the PLSQL_CCFLAGS
compiler parameter. This occurs because a user-defined directive overrides the predefined one.
Given this session setting:
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true'
Now the value of $$debug
is TRUE
, the value of $$plsql_ccflags
is 'debug:true'
, the value of $$my_id
is the literal NULL
, and the use of $$my_id
raises PLW-6003
if the source text is not wrapped.
For an example of the use of an inquiry directive, see Example 2-34.
This section describes the inquiry directive names that are predefined and can be used in conditional expressions. These include:
The Oracle initialization parameters for PL/SQL compilation, such as PLSQL_CCFLAGS
, PLSQL_DEBUG
, PLSQL_OPTIMIZE_LEVEL
, PLSQL_CODE_TYPE
, PLSQL_WARNINGS
, and NLS_LENGTH_SEMANTICS
. See "Initialization Parameters for PL/SQL Compilation". For an example, see Example 2-34.
Note that recompiling a PL/SQL unit with the REUSE
SETTINGS
clause of the SQL ALTER
statement can protect against changes made to initialization parameter values in the current PL/SQL compilation environment. See Example 2-35.
PLSQL_LINE
which is a PLS_INTEGER
literal value indicating the line number reference to $$PLSQL_LINE
in the current unit. For example:
$IF $$PLSQL_LINE = 32 $THEN ...
Note that the value of PLSQL_LINE
can be defined explicitly with PLSQL_CCFLAGS
.
PLSQL_UNIT
which is a VARCHAR2
literal value indicating the current source unit. For a named compilation unit, $$PLSQL_UNIT
contains, but might not be limited to, the unit name. For an anonymous block, $$PLSQL_UNIT
contains the empty string. For example:
IF $$PLSQL_UNIT = 'AWARD_BONUS' THEN ...
Note that the value of PLSQL_UNIT
can be defined explicitly with PLSQL_CCFLAGS
. Also note that the previous example shows the use of PLSQL_UNIT
in regular PL/SQL. Because $$PLSQL_UNIT = 'AWARD_BONUS'
is a VARCHAR2
comparison, not a static expression, it is not supported with $IF
. One valid use of $IF
with PLSQL_UNIT
is to determine an anonymous block:
$IF $$PLSQL_UNIT IS NULL $THEN ...
Only static expressions which can be fully evaluated by the compiler are allowed during conditional compilation processing. Any expression that contains references to variables or functions that require the execution of the PL/SQL are not available during compilation and cannot be evaluated. For information on PL/SQL datatypes, see "Overview of Predefined PL/SQL Datatypes".
A static expression is either a BOOLEAN
, PLS_INTEGER
, or VARCHAR2
static expression. Static constants declared in packages are also static expressions.
BOOLEAN
static expressions include:
TRUE
, FALSE
, and the literal NULL
x >
y, x <
y, x >=
y, x <=
y, x =
y, and x <>
y where x and y are PLS_INTEGER
static expressions
NOT
x, x AND
y, x OR
y, x >
y, x >=
y, x =
y, x <=
y, x <>
y where x and y are BOOLEAN
static expressions
x IS
NULL
and x IS
NOT
NULL
where x is a static expression
PLS_INTEGER
static expressions include:
-2147483648 to 2147483647, and the literal NULL
VARCHAR2
static expressions include:
'abcdef'
and 'abc' || 'def'
literal NULL
TO_CHAR(x)
, where x is a PLS_INTEGER
static expression
TO_CHAR
(x f, n
) where x
is a PLS_INTEGER
static expression andf
and n
are VARCHAR2
static expressions
x || y where x and y are VARCHAR2
or PLS_INTEGER
static expressions
Static constants are declared in a package specification as follows:
static_constant
CONSTANT
datatype
:=
static_expression
;
This is a valid declaration of a static constant if:
The declared datatype
and the type of static_expression
are the same
static_expression
is a static expression
datatype
is either BOOLEAN
or PLS_INTEGER
The static constant must be declared in the package specification and referred to as package_name
.constant_name
, even in the body of the package_name
package.
If a static package constant is used as the BOOLEAN
expression in a valid selection directive in a PL/SQL unit, then the conditional compilation mechanism automatically places a dependency on the package referred to. If the package is altered, then the dependent unit becomes invalid and needs to be recompiled to pick up any changes. Note that only valid static expressions can create dependencies.
If you choose to use a package with static constants for controlling conditional compilation in multiple PL/SQL units, then create only the package specification and dedicate it exclusively for controlling conditional compilation because of the multiple dependencies. Note that for control of conditional compilation in an individual unit, you can set a specific flag in PLSQL_CCFLAGS
.
In Example 2-32 the my_debug
package defines constants for controlling debugging and tracing in multiple PL/SQL units. In the example, the constants debug
and trace
are used in static expressions in procedures my_proc1
and my_proc2
, which places a dependency from the procedures to my_debug
.
Example 2-32 Using Static Constants
CREATE PACKAGE my_debug IS debug CONSTANT BOOLEAN := TRUE; trace CONSTANT BOOLEAN := TRUE; END my_debug; / CREATE PROCEDURE my_proc1 IS BEGIN $IF my_debug.debug $THEN DBMS_OUTPUT.put_line('Debugging ON'); $ELSE DBMS_OUTPUT.put_line('Debugging OFF'); $END END my_proc1; / CREATE PROCEDURE my_proc2 IS BEGIN $IF my_debug.trace $THEN DBMS_OUTPUT.put_line('Tracing ON'); $ELSE DBMS_OUTPUT.put_line('Tracing OFF'); $END END my_proc2; /
Changing the value of one of the constants forces all the dependent units of the package to recompile with the new value. For example, changing the value of debug
to FALSE
would cause my_proc1
to be recompiled without the debugging code. my_proc2
would also be recompiled, but my_proc2
would be unchanged because the value of trace
did not change.
You can set the dynamic PLSQL_CCFLAGS
initialization parameter to flag names with associated values to control conditional compilation on PL/SQL units. For example, the PLSQL_CCFLAGS
initialization parameter could be set dynamically with ALTER
SESSION
to turn on debugging and tracing functionality in PL/SQL units as shown in Example 2-34.
You can also set the PLSQL_CCFLAGS
initialization parameter to independently control conditional compilation on a specific PL/SQL unit with as shown in Example 2-35 with the SQL ALTER
PROCEDURE
statement.
The flag names can be set to any unquoted PL/SQL identifier, including reserved words and keywords. If a flag value is explicitly set, it must be set to a TRUE
, FALSE
, PLS_INTEGER
, or NULL
. The flag names and values are not case sensitive. For detailed information, including restrictions, on the PLSQL_CCFLAGS
initialization parameter, see Oracle Database Reference.
The DBMS_DB_VERSION
package provides constants that are useful when making simple selections for conditional compilation. The PLS_INTEGER
constants VERSION
and RELEASE
identify the current Oracle version and release numbers. The BOOLEAN
constants VER_LE_9
, VER_LE_9_1
, VER_LE_9_2
, VER_LE_10
, VER_LE_10_1
, and VER_LE_10_2
evaluate to TRUE
or FALSE
on the basis of less than or equal to the version and the release. For example, the constants in Oracle 10g release 2 evaluate as follows:
VER_LE_10
represents the condition that the database version is less than or equal to 10; it is TRUE
VER_LE_10_2
represents the condition that database version is less than or equal to 10 and release is less than or equal to 2; it is TRUE
All constants representing Oracle 10g release 1 or earlier are FALSE
Example 2-33 illustrates the use of a DBMS_DB_VERSION
constant with conditional compilation. Both the Oracle database version and release are checked. This example also shows the use of $ERROR
.
Example 2-33 Using DBMS_DB_VERSION Constants
BEGIN $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN $ERROR 'unsupported database release' $END $ELSE DBMS_OUTPUT.PUT_LINE ('Release ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' is supported.'); -- Note that this COMMIT syntax is newly supported in 10.2 COMMIT WRITE IMMEDIATE NOWAIT; $END END; /
For information on the DBMS_DB_VERSION
package, see Oracle Database PL/SQL Packages and Types Reference.
This section provides examples using conditional compilation.
In Example 2-34, conditional compilation is used to determine whether the BINARY_DOUBLE
datatype can be utilized in the calculations for PL/SQL units in the database. The BINARY_DOUBLE
datatype can only be used in a database version that is 10g or later. This example also shows the use of the PLSQL_CCFLAGS
parameter.
Example 2-34 Using Conditional Compilation With Database Versions
-- set flags for displaying debugging code and tracing info ALTER SESSION SET PLSQL_CCFLAGS = 'my_debug:FALSE, my_tracing:FALSE'; CREATE PACKAGE my_pkg AS SUBTYPE my_real IS $IF DBMS_DB_VERSION.VERSION < 10 $THEN NUMBER; -- check database version $ELSE BINARY_DOUBLE; $END my_pi my_real; my_e my_real; END my_pkg; / CREATE PACKAGE BODY my_pkg AS BEGIN -- set up values for future calculations based on DB version $IF DBMS_DB_VERSION.VERSION < 10 $THEN my_pi := 3.14016408289008292431940027343666863227; my_e := 2.71828182845904523536028747135266249775; $ELSE my_pi := 3.14016408289008292431940027343666863227d; my_e := 2.71828182845904523536028747135266249775d; $END END my_pkg; / CREATE PROCEDURE circle_area(radius my_pkg.my_real) IS my_area my_pkg.my_real; my_datatype VARCHAR2(30); BEGIN my_area := my_pkg.my_pi * radius; DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius) || ' Area: ' || TO_CHAR(my_area) ); $IF $$my_debug $THEN -- if my_debug is TRUE, run some debugging code SELECT DATA_TYPE INTO my_datatype FROM USER_ARGUMENTS WHERE OBJECT_NAME = 'CIRCLE_AREA' AND ARGUMENT_NAME = 'RADIUS'; DBMS_OUTPUT.PUT_LINE('Datatype of the RADIUS argument is: ' || my_datatype); $END END; /
If you want to set my_debug
to TRUE
, you can make this change only for procedure circle_area
with the REUSE
SETTINGS
clause as shown in Example 2-35.
DBMS_PREPROCESSOR
subprograms print or retrieve the post-processed source text of a PL/SQL unit after processing the conditional compilation directives. This post-processed text is the actual source used to compile a valid PL/SQL unit. Example 2-36 shows how to print the post-processed form of my_pkg
in Example 2-34 with the PRINT_POST_PROCESSED_SOURCE
procedure.
Example 2-36 Using PRINT_POST_PROCESSED_SOURCE to Display Source Code
CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PACKAGE', 'HR', 'MY_PKG');
When my_pkg
in Example 2-34 is compiled on a 10g release or later database using the HR
account, the output of Example 2-36 is similar to the following:
PACKAGE my_pkg AS
SUBTYPE my_real IS
BINARY_DOUBLE;
my_pi my_real; my_e my_real;
END my_pkg;
PRINT_POST_PROCESSED_SOURCE
replaces unselected text with whitespace. The lines of code in Example 2-34 that are not included in the post-processed text are represented as blank lines. For information on the DBMS_PREPROCESSOR
package, see Oracle Database PL/SQL Packages and Types Reference.
A conditional compilation directive cannot be used in the specification of an object type or in the specification of a schema-level nested table or varray. The attribute structure of dependent types and the column structure of dependent tables is determined by the attribute structure specified in object type specifications. Any changes to the attribute structure of an object type must be done in a controlled manner to propagate the changes to dependent objects. The mechanism for propagating changes is the SQL ALTER
TYPE
... ATTRIBUTE
statement. Use of a preprocessor directive would allow changes to the attribute structure of the object type without the use of an ALTER
TYPE
... ATTRIBUTE
statement. As a consequence, dependent objects could go out of sync or dependent tables could become inaccessible.
The SQL parser imposes restrictions on the placement of directives when performing SQL operations such as the CREATE
[OR
REPLACE]
statement or the execution of an anonymous block. When performing these SQL operations, the SQL parser imposes a restriction on the location of the first conditional compilation directive as follows:
A conditional compilation directive cannot be used in the specification of an object type or in the specification of a schema-level nested table or varray.
In a package specification, a package body, a type body, and in a schema-level function or procedure with no formal parameters, the first conditional compilation directive may occur immediately after the keyword IS/AS
.
In a schema-level function or procedure with at least one formal parameter, the first conditional compilation directive may occur immediately after the opening parenthesis that follows the unit's name. For example:
CREATE OR REPLACE PROCEDURE my_proc (
$IF $$xxx $THEN i IN PLS_INTEGER $ELSE i IN INTEGER $END
) IS BEGIN NULL; END my_proc;
/
In a trigger or an anonymous block, the first conditional compilation directive may occur immediately after the keyword BEGIN
or immediately after the keyword DECLARE
when the trigger block has a DECLARE
section.
If an anonymous block uses a placeholder, then this cannot occur within a conditional compilation directive. For example:
BEGIN
:n := 1;
-- valid use of placeholder $IF .... $THEN
:n := 1;
-- invalid use of placeholder$END
You can use PL/SQL to develop Web applications or server pages. These are briefly described in this section. For detailed information on using PL/SQL to create Web applications, see "Developing Applications with the PL/SQL Web Toolkit" in Oracle Database Application Developer's Guide - Fundamentals. For detailed information on using PL/SQL to create Web Server Pages (PSPs), see "Developing PL/SQL Server Pages" in Oracle Database Application Developer's Guide - Fundamentals.
With PL/SQL you can create applications that generate Web pages directly from an Oracle database, allowing you to make your database available on the Web and make back-office data accessible on the intranet.The program flow of a PL/SQL Web application is similar to that in a CGI Perl script. Developers often use CGI scripts to produce Web pages dynamically, but such scripts are often not optimal for accessing Oracle Database. Delivering Web content with PL/SQL stored procedures provides the power and flexibility of database processing. For example, you can use DML, dynamic SQL, and cursors. You also eliminate the process overhead of forking a new CGI process to handle each HTTP request.You can implement a Web browser-based application entirely in PL/SQL with PL/SQL Gateway and the PL/SQL Web Toolkit.
PL/SQL gateway enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener. mod_plsql
, one implementation of the PL/SQL gateway, is a plug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL stored procedures.
PL/SQL Web Toolkit is a set of PL/SQL packages that provides a generic interface to use stored procedures called by mod_plsql
at runtime.
PL/SQL Server Pages (PSPs) enable you to develop Web pages with dynamic content. They are an alternative to coding a stored procedure that writes out the HTML code for a web page, one line at a time.
Using special tags, you can embed PL/SQL scripts into HTML source code. The scripts are executed when the pages are requested by Web clients such as browsers. A script can accept parameters, query or update the database, then display a customized page showing the results.
During development, PSPs can act like templates with a static part for page layout and a dynamic part for content. You can design the layouts using your favorite HTML authoring tools, leaving placeholders for the dynamic content. Then, you can write the PL/SQL scripts that generate the content. When finished, you simply load the resulting PSP files into the database as stored procedures.
PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories:
Table 2-4 shows the functions in each category. For descriptions of the error-reporting functions, see "SQLCODE Function" and "SQLERRM Function". For descriptions of the other functions, see Oracle Database 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-4 Built-In Functions
Error | Number | Character | Conversion | Date | Obj Ref | Misc |
---|---|---|---|---|---|---|
SQLCODE
|
ABS
|
ASCII
|
CHARTOROWID
|
ADD_MONTHS
|
DEREF
|
BFILENAME
|