PL/SQL programs normally are used to manipulate database information. You commonly do this by declaring variables and data structures in your programs, and then working with that PL/SQL-specific data.
A variable is a named instantiation of a data structure declared in a PL/SQL block (either locally or in a package). Unless you declare a variable as a CONSTANT
, its value can be changed at any time in your program.
The following table summarizes the different types of program data:
Type | Description |
Scalar | Variables made up of a single value, such as a number, date, or Boolean. |
Composite | Variables made up of multiple values, such as a record, collection, or instance of a user-defined object type. |
Reference | Logical pointers to values or cursors. |
LOB | Variables containing large object (LOB) locators. |
PL/SQL Scalar Datatypes
Scalar datatypes divide into four families: number, character, datetime, and Boolean. Subtypes further define a base datatype by restricting the values or size of the base datatype.
Numeric datatypes
Numeric datatypes represent real numbers, integers, and floating-point numbers. They are stored as NUMBER, PLS_INTEGER, and IEEE floating-point storage types.
Decimal numeric datatypes store fixed and floating-point numbers of just about any size. They include the subtypes NUMBER, DEC, DECIMAL, NUMERIC, FLOAT, REAL, and DOUBLE PRECISION. The maximum precision of a variable with type NUMBER is 38 digits, which yields a range of values from 1.0E-129 through 9.999E125.
Variables of type NUMBER can be declared with precision and scale, as follows:
NUMBER(precision, scale)
where precision is the number of digits, and scale is the number of digits to the right (positive scale) or left (negative scale) of the decimal point at which rounding occurs. Legal values for scale range from −84 to 127. The following table shows examples of precision and scale:
Declaration | Assigned value | Stored value |
NUMBER | 6.02 | 6.02 |
NUMBER(4) | 8675 | 8675 |
NUMBER(4) | 8675309 | Error |
NUMBER(12,5) | 3.14159265 | 3.14159 |
NUMBER(12,−5) | 8675309 | 8700000 |
Oracle provides a variety of datatypes to store whole numbers: BINARY_INTEGER, INTEGER, INT, SMALLINT, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, and PLS_INTEGER.
Binary integer datatypes store signed integers in the range of −231 + 1 to 231 − 1. The subtypes include NATURAL (0 through 231 − 1) and POSITIVE (1 through 231 − 1) together with the NOT NULL variations NATURALN and POSITIVEN. SIGNTYPE is restricted to three values (−1, 0, 1). PLS_INTEGER is an unconstrained subtype (alias) of BINARY_INTEGER.
SIMPLE_INTEGER has the same range as BINARY_INTEGER except that it does not allow for null values and does not raise an exception if an overflow occurs. For example, 2147483647 + 1 = −2147483648 (note the negative value!). SIMPLE_INTEGER datatypes can result in significantly faster execution speeds when the PL/SQL code is compiled to native machine code.
IEEE 754–compliant floating-point numbers are available in both SQL and PL/SQL. These subtypes are the single-precision BINARY_FLOAT and the double-precision BINARY_DOUBLE. Because these datatypes require less memory and use native machine arithmetic, they perform much better for scientific or engineering applications that are computer-intensive or that require comparison to infinity or not a number (NaN). These two datatypes have binary precision instead of the decimal precision used in the NUMBER family. So, if you are developing financial applications that are concerned with rounding errors or require decimal precision, you probably should not use these floating-point datatypes.
The following table lists the PL/SQL numeric datatypes with ANSI and IBM compatibility. In this table:
- precision is the precision for the subtype.
- scale is the scale of the subtype.
- binary is the binary precision of the subtype.
PL/SQL datatype | Compatibility | Oracle database datatype |
DEC(precision,scale) | ANSI | NUMBER(precision,scale) |
DECIMAL(precision,scale) | IBM | NUMBER(precision,scale) |
DOUBLE PRECISION | ANSI | NUMBER |
FLOAT(binary) | ANSI, IBM | NUMBER |
INT | ANSI | NUMBER(38) |
INTEGER | ANSI, IBM | NUMBER(38) |
NUMERIC(precision,scale) | ANSI | NUMBER(precision,scale) |
REAL | ANSI | NUMBER |
SMALLINT | ANSI, IBM | NUMBER(38) |
BINARY_FLOAT | IEEE 754 | BINARY_FLOAT |
BINARY_ DOUBLE | IEEE 754 | BINARY_ DOUBLE |
Character datatypes
Character datatypes store alphanumeric text and are manipulated by character functions. As with the numeric family, there are several subtypes in the character family, shown in the following table:
Family | Description |
CHAR | Fixed-length alphanumeric strings. Valid sizes are 1 to 32,767 bytes. |
VARCHAR2 | Variable-length alphanumeric strings. Valid sizes are 1 to 32,767 bytes. |
LONG | Variable-length alphanumeric strings. Valid sizes are 1 to 32,760 bytes. LONG is included primarily for backward compatibility; instead use VARCHAR2(32767), CLOB, BLOB, or NCLOB for large character strings. |
RAW | Variable-length binary strings. Valid sizes are 1 to 32,767 bytes (which is larger than the database limit of 2,000). RAW data does not undergo character set conversion when selected from a remote database. |
LONG RAW | Variable-length binary strings. Valid sizes are 1 to 32,760 bytes. LONG RAW is included primarily for backward compatibility; BLOB and BFILE are the preferred datatypes for large binary data. |
ROWID | Fixed-length binary data. Every row in a database has a physical address or ROWID. A ROWID has four parts in base 64: OOOOOOFFFBBBBBBRRR where:
|
UROWID | Universal ROWID. Variable-length hexadecimal string depicting a logical, physical, or non-Oracle row identifier. Valid sizes are up to 4,000 bytes. |
Unicode character datatypes
The standard WE8MSWIN1252 or WE8ISO8859P2 character set does not support some languages, such as Chinese and Greek. To support multiple languages, the database allows two character sets—the database character set and a Unicode character set, sometimes called the national character set (NLS).
The two NLS datatypes, NCHAR and NVARCHAR2, are used to represent data in the Unicode character set. NCHAR values are fixed-length character data; the maximum length is 32,767 bytes. NVARCHAR2 values are variable-length character data; the maximum length also is 32,767 bytes.
Datetime datatypes
The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. There are also two interval datatypes, INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
DATE
values are fixed-length, date-plus-time values. The DATE datatype can store dates from January 1, 4712 BC to December 31, 9999 AD. Each DATE includes the century, year, month, day, hour, minute, and second. Subsecond granularity is not supported via the DATE datatype; use one of the TIMESTAMP datatypes instead. The time portion of a DATE defaults to midnight (12:00:00 a.m.) if it is not included explicitly.
TIMESTAMP
values store date and time to subsecond granularity. The subsecond precision (the number of digits to the right of the decimal) either defaults to 6 or is set to 0 through 9 digits by declaration, as in:
DECLARE
mytime_declared TIMESTAMP(9); -- max precision
mytime_default TIMESTAMP; -- default 6 digits precision
mytime_to_seconds TIMESTAMP(0); -- no fractional seconds
TIMESTAMP
WITH TIME ZONE values store date and time values like a TIMESTAMP but also store the hourly offset from Coordinated Universal Time (UTC, which is essentially equivalent to Greenwich Mean Time). As with TIMESTAMP, the subsecond precision is 0 to 9 digits, either declared or inherited from the default 6 digits of precision:
DECLARE
mytime_declared TIMESTAMP(9) WITH TIME ZONE;
mytime_default TIMESTAMP WITH TIME ZONE;
TIMESTAMP WITH LOCAL TIME ZONE
values store date and time values together with the UTC offset, like a TIMESTAMP WITH TIME ZONE. The principal difference between these timestamp datatypes occurs when values are saved to or retrieved from a database table. TIMESTAMP WITH LOCAL TIME ZONE values are converted to the database time zone and saved without an offset. The values retrieved from the database table are converted from the database time zone to the session’s time zone.
The offset from UTC for both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE can be hours and minutes or a time zone region (found in the V$TIMEZONE_NAMES
data dictionary view) with the optional daylight savings time name (also found in V$TIMEZONE_NAMES). For example:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT=
'DD-Mon-YYYY HH24:MI:SS.FF TZR';
DECLARE
my_tswtz TIMESTAMP(4) WITH TIME ZONE;BEGIN
my_tswtz := '31-MAR-2016 07:32:45.1234 US/Pacific';
INTERVAL YEAR TO MONTH
values store a period of time in years and months:
DECLARE
myy2m INTERVAL YEAR TO MONTH;
BEGIN
myy2m := INTERVAL '1–6' YEAR TO MONTH; -- interval of 18 months
INTERVAL DAY TO SECOND
values store a period of time in days, hours, minutes, seconds, and fractional seconds:
DECLARE
myd2s INTERVAL DAY TO SECOND;
BEGIN
myd2s := INTERVAL '2 10:32:15.678' DAY TO SECOND;
BOOLEAN datatype
The BOOLEAN datatype can store one of three values: TRUE, FALSE, or NULL. BOOLEAN variables usually are used in logical control structures such as IF . . . THEN or LOOP statements.
The following truth tables show the results of logical AND, OR, and NOT operations with PL/SQL’s three-value Boolean model:
AND | TRUE | FALSE | NULL |
TRUE | TRUE | FALSE | NULL |
FALSE | FALSE | FALSE | FALSE |
NULL | NULL | FALSE | NULL |
OR | TRUE | FALSE | NULL |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | NULL |
NULL | TRUE | NULL | NULL |
NOT (TRUE) | NOT (FALSE) | NOT (NULL) |
FALSE | TRUE | NULL |
LOB Datatypes
PL/SQL supports a number of large object (LOB) datatypes, which can store objects up to 8 terabytes in size, or even up to 128 TB, depending on database block size. Unlike the scalar datatypes, variables declared for LOBs use locators, or pointers to the actual data. LOBs are manipulated in PL/SQL using the built-in package DBMS_LOB. The LOB datatypes are:
BFILE
File locators pointing to read-only large binary objects in operating system files. With BFILEs, the large objects are outside the database, and the maximum size is system-dependent.
BLOB
LOB locators that point to large binary objects inside the database.
CLOB
LOB locators that point to large character (alphanumeric) objects inside the database.
NCLOB
LOB locators that point to large Unicode character objects inside the database.
Implicit Datatype Conversions
Whenever PL/SQL detects that a datatype conversion is necessary, it attempts to change the values as required to perform the operation. Not all values in each datatype can be converted to another datatype. For example, an attempt to convert BINARY_FLOAT_NAN to a number datatype will raise an INVALID NUMBER exception.
NULLs in PL/SQL
PL/SQL represents unknown or inapplicable values as NULL values. Because a NULL is unknown, a NULL is never equal or not equal to anything (including another NULL value). In addition, most functions and operators return a NULL when passed a NULL argument. You cannot check for equality or inequality to NULL; therefore, you must use the IS NULL or IS NOT NULL syntax to check for NULL values.
Here is an example of the IS NULL syntax used to check the value of a variable:
BEGIN
/* Correct usage */
IF myvar IS NULL THEN ...
/* Dangerous! Always evaluates to NULL */
IF myvar = NULL THEN ...
There are several ways in SQL and PL/SQL to substitute a non-null value for a null variable or expression. NVL is commonly used:
NVL (exprn1, exprn2)
With NVL, Oracle will always evaluate both arguments, even if exprn1 is not null. A potentially more efficient function is COALESCE:
COALESCE(exprn1, exprn2 [, exprn3, ...] )
COALESCE has two distinct characteristics: first, it accepts more than two arguments; second, it uses short-circuit evaluation, meaning it stops looking for a non-null value when it finds one (and, therefore, does not evaluate every expression in the list, which NVL does).
Declaring Variables
Before you can use a variable, you must first declare it in the declaration section of your PL/SQL block or in a package as a global. When you declare a variable, PL/SQL allocates memory for the variable’s value and names the storage location so that the value can be retrieved and changed. The syntax for a variable declaration is:
variable_name [CONSTANT] datatype [NOT NULL]
[{ := | DEFAULT } initial_value];
Constrained declarations
A programmer may constrain certain datatypes at declaration so that variables have a size, scale, or precision that is less than the maximum allowed. Some examples:
total_sales NUMBER(15,2); -- Constrained
emp_id VARCHAR2(9); -- Constrained
company_number NUMBER; -- Unconstrained
book_title VARCHAR2; -- Not valid
Constrained declarations can require less memory than unconstrained declarations. Not all datatypes can be declared unconstrained; VARCHAR2 is a notable exception.
Constants
The CONSTANT keyword in a declaration requires an initial value and does not allow that value to be changed in the program. For example:
min_order_qty NUMBER(1) CONSTANT := 5;
Default values
The PL/SQL runtime engine assigns a default value of NULL to each variable you explicitly declare.1 If you want to initialize a variable to a value other than NULL, you can do so in the declaration with either the assignment operator (:=) or the DEFAULT keyword:
counter BINARY_INTEGER := 0;
priority VARCHAR2(8) DEFAULT 'LOW';
A NOT NULL constraint can be appended to the variable’s datatype declaration to indicate that NULL is not a valid value. If you add the NOT NULL constraint, you must explicitly assign an initial value for that variable.
Anchored Declarations
Use the %TYPE
attribute to anchor the datatype of a scalar variable to either another variable or to a column in a database table or view. Use %ROWTYPE to anchor a record’s declaration to a cursor or table.
The following block shows several variations of anchored declarations:
DECLARE
tot_sales NUMBER(20,2);
-- Anchor to a local variable
monthly_sales tot_sales%TYPE;
-- Anchor to a database column
v_ename employee.last_name%TYPE;
-- Anchor to a cursor
CURSOR mycur IS SELECT * FROM employee;
myrec mycur%ROWTYPE;
-
The NOT NULL clause on a variable declaration (but not on a database column definition) follows the %TYPE anchoring and requires anchored declarations to have a default in their declaration. The default value for an anchored declaration can be different from that for the base declaration:
tot_sales NUMBER(20,2) NOT NULL DEFAULT 0;
monthly_sales tot_sales%TYPE DEFAULT 10;
Programmer-Defined Subtypes
PL/SQL allows you to define unconstrained scalar subtypes, which you might want to do for greater “self-documentation” of your code or for future-proofing. An unconstrained subtype provides an alias to the original underlying datatype; for example:
CREATE OR REPLACE PACKAGE std_types
IS
-- Declare reusable app-specific type
SUBTYPE dollar_amt_t IS NUMBER;
END std_types;
CREATE OR REPLACE PROCEDURE process_money
IS
-- Use standardized type declared in package
credit std_types.dollar_amt_t;
A constrained subtype limits or constrains the new datatype to a subset of the original datatype. For example, POSITIVE is a constrained subtype of BINARY_INTEGER. The declaration for POSITIVE in the STANDARD package is:
SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1..2147483647;
You can define your own constrained subtypes in your programs:
PACKAGE std_types
IS
SUBTYPE currency_t IS NUMBER (15, 2);
END;