home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam

## 1.5 Variables and Program Data

PL/SQL programs are normally 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 describes several 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 or collection.

Reference

Pointers to values.

LOB

Variables containing Large OBject (LOB) locators.

### 1.5.1 Scalar Datatypes

Scalar datatypes divide into four families: number, character, date-time, and Boolean.

#### 1.5.1.1 Numeric datatypes

Numeric datatypes are further divided into decimal, binary integer, and PLS_INTEGER storage types.

Decimal numeric datatypes store fixed and floating-point numbers of just about any size. They include 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. This range of numbers would include the mass of an electron over the mass of the universe or the size of the universe in angstroms.

Variables of type NUMBER can be declared with precision and scale, as follows:

`NUMBER(precision, scale)`

Precision is the number of digits, and scale denotes the number of digits to the right (positive scale) or left (negative scale) of the decimal point at which rounding occurs. Legal values for the 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

Binary integer numeric datatypes store whole numbers. They include BINARY_INTEGER, INTEGER, INT, SMALLINT, NATURAL, NATURALN, POSITIVE, POSITIVEN, and SIGNTYPE. Binary integer datatypes store signed integers in the range of -2 31 + 1 to 2 31 - 1. The subtypes include NATURAL (0 through 2 31 ) and POSITIVE (1 through 2 31 ) together with the NOT NULL variations NATURALN and POSITIVEN. SIGNTYPE is restricted to three values (-1, 0, 1).

PLS_INTEGER datatypes have the same range as the BINARY_INTEGER datatype, but use machine arithmetic instead of library arithmetic, so are slightly faster for computation-heavy processing.

The following table lists the PL/SQL numeric datatypes with ANSI and IBM compatibility.

PL/SQL Datatype

Compatibility

Oracle RDNMS Datatype

DEC(prec,scale)

ANSI

NUMBER(prec,scale)

DECIMAL(prec,scale)

IBM

NUMBER(prec,scale)

DOUBLE PRECISION

ANSI

NUMBER

FLOAT(binary)

ANSI, IBM

NUMBER

INT

ANSI

NUMBER(38)

INTEGER

ANSI, IBM

NUMBER(38)

NUMERIC(prec,scale)

ANSI

NUMBER(prec,scale)

REAL

ANSI

NUMBER

SMALLINT

ANSI, IBM

NUMBER(38)

In the preceding table:

• prec is the precision for the subtype.

• scale is the scale of the subtype.

• binary is the binary precision of the subtype.

#### 1.5.1.2 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 32767 bytes (which is larger than the Oracle7 limit of 2000 and the Oracle8 limit of 4000).

VARCHAR2

Variable-length alphanumeric strings. Valid sizes are 1 to 32767 bytes (which is larger than the Oracle7 limit of 2000 and the Oracle8 limit of 4000).

LONG

Variable-length alphanumeric strings. Valid sizes are 1 to 32760 bytes. LONG is included primarily for backward compatibility since longer strings can now be stored in VARCHAR2 variables.

RAW

Variable-length binary strings. Valid sizes are 1 to 32767 bytes (which is larger than the Oracle7 and Oracle8 limit of 2000). RAW data do not undergo character set conversion when selected from a remote database.

LONG RAW

Variable-length binary strings. Valid sizes are 1 to 32760 bytes. LONG RAW is included primarily for backward compatibility since longer strings can now be stored in RAW variables.

ROWID

Fixed-length binary data. Every row in a database has a physical address or ROWID.

An Oracle7 (restricted) ROWID has 3 parts in base 16 (hex):

BBBBBBBB.RRRR.FFFF.

An Oracle8 (extended) ROWID has 4 parts in base 64:

OOOOOOFFFBBBBBBRRR.

where:

OOOOOO is the object number.

FFFF (FFF) is the absolute (Oracle 7) or relative (Oracle8) file number.

BBBBBBBB (BBBBBB) is the block number within the file.

RRRR (RRR) is the row number within the block.

UROWID (Oracle8 i )

Universal ROWID. Variable-length hexadecimal string depicting a logical ROWID. Valid sizes are up to 4000 bytes. Used to store the addresses of rows in index organized tables or IBM DB2 tables via Gateway.

#### 1.5.1.3 Date-time datatypes

DATE values are fixed-length, date-plus-time values. The DATE datatype can store dates from January 1, 4712 B.C. to December 31, 4712 A.D. Each DATE includes the century, year, month, day, hour, minute, and second. Sub-second granularity is not supported via the DATE datatype. The time portion of a DATE defaults to midnight (12:00:00 AM) if it is not included explicitly. The internal calendar follows the Papal standard of Julian to Gregorian conversion in 1582 rather than the English standard (1752) found in many operating systems.

#### 1.5.1.4 Boolean datatype

The BOOLEAN datatype can store one of only three values: TRUE, FALSE, or NULL. BOOLEAN variables are usually used in logical control structures such as IF...THEN or LOOP statements.

Following are truth tables showing 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

### 1.5.2 NLS Character Datatypes

The standard ASCII character set does not support some languages, such as Chinese, Japanese, or Korean. To support these multibyte character sets, PL/SQL8 supports two character sets, the database character set and the national character set (NLS). There are two datatypes, NCHAR and NVARCHAR2, that can be used to store data in the national character set.

NCHAR values are fixed-length NLS character data; the maximum length is 32767 bytes. For variable-length character sets (like JA16SJIS), the length specification is in bytes; for fixed-length character sets, it is in characters.

NVARCHAR2 values are variable-length NLS character data. The maximum length is 32767 bytes, and the length specification follows the same fixed/variable-length rule as NCHAR values.

### 1.5.3 LOB Datatypes

PL/SQL8 supports a number of Large OBject (LOB) datatypes, which can store objects of up to four gigabytes of data. 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.

BFILE

File locators pointing to read-only large binary objects in operating system files. With BFILEs, the large objects are outside the database.

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 national character set objects inside the database.

### 1.5.4 NULLs in PL/SQL

PL/SQL represents unknown values as NULL values. Since a NULL is unknown, a NULL is never equal or not equal to anything (including another NULL value). Additionally, most functions return a NULL when passed a NULL argument -- the notable exceptions are NVL, CONCAT, and REPLACE. 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 to check the value of a variable:

```BEGIN
IF myvar IS NULL
THEN
...```

### 1.5.5 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 datatype [CONSTANT] [NOT NULL]
[:= | DEFAULT initial_value]```

#### 1.5.5.1 Constrained declarations

The datatype in a declaration can be constrained or unconstrained. Constrained datatypes have a size, scale, or precision limit that is less than the unconstrained datatype. For example:

```total_sales    NUMBER(15,2);  -- Constrained.
emp_id         VARCHAR2(9);   -- Constrained.
company_number NUMBER;        -- Unconstrained.
book_title     VARCHAR2;      -- Not valid.```

Constrained declarations require less memory than unconstrained declarations. Not all datatypes can be specified as unconstrained. You cannot, for example, declare a variable to be of type VARCHAR2. You must always specify the maximum size of a variable-length string.

#### 1.5.5.2 Constants

The CONSTANT keyword in a declaration requires an initial value and does not allow that value to be changed. For example:

`min_order_qty   NUMBER(1) CONSTANT := 5;`

#### 1.5.5.3 Default values

Whenever you declare a variable, it is assigned a default value of NULL. Initializing all variables is distinctive to PL/SQL; in this way, PL/SQL differs from languages such as C and Ada. If you want to initialize a variable to a value other than NULL, you 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.

### 1.5.6 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 (see the Section 1.11, "Records in PL/SQL " section for more detail on the %ROWTYPE attribute).

The following block shows several variations of anchored declarations:

```DECLARE
tot_sales NUMBER(20,2);
-- Anchor to a PL/SQL variable.
monthly_sales tot_sales%TYPE;

-- Anchor to a database column.
v_ename employee.last_name%TYPE;

CURSOR mycur IS
SELECT * FROM employee;

-- Anchor to a cursor.
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;```

### 1.5.7 Programmer-Defined Subtypes

PL/SQL allows you to define unconstrained scalar subtypes. An unconstrained subtype provides an alias to the original underlying datatype, for example:

```CREATE OR REPLACE PACKAGE std_types
IS
-- Declare standard types as globals.
TYPE dollar_amt_t IS NUMBER;
END std_types;

CREATE OR REPLACE PROCEDURE process_money
IS
-- Use the global type declared above.
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 cannot define constrained subtypes in your own programs; this capability is reserved for Oracle itself. You can, however, achieve the same effect as a constrained subtype by using %TYPE. Here is a rewriting of the previous subtype that enforces a constraint on the size of dollar amount variables:

```PACKAGE std_types
IS
v_dollar NUMBER (10, 2);

TYPE dollar_amt_t IS v_dollar%TYPE;
END;```

 1.4 PL/SQL Language Fundamentals 1.6 Conditional and Sequential Control