PL/SQL User's Guide and Reference

Declarations

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. A couple of examples follow:

birthday  DATE;
emp_count SMALLINT := 0;

The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator (:=) to assign an initial value of zero to the variable.

The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

pi     REAL := 3.14159;
radius REAL := 1;
area   REAL := pi * radius**2;

By default, variables are initialized to NULL. For example, the following declarations are equivalent:

birthday DATE;
birthday DATE := NULL;

In constant declarations, the keyword CONSTANT must precede the type specifier, as the following example shows:

credit_limit CONSTANT REAL := 5000.00;

This declaration names a constant of type REAL and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated. (The processing of a declaration by the PL/SQL compiler is called elaboration.)

Using DEFAULT

If you prefer, you can use the reserved word DEFAULT instead of the assignment operator to initialize variables and constants. For example, the declarations

tax_year SMALLINT := 95;
valid    BOOLEAN := FALSE;

can be rewritten as follows:

tax_year SMALLINT DEFAULT 95;
valid    BOOLEAN DEFAULT FALSE;

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Using NOT NULL

Besides assigning an initial value, declarations can impose the NOT NULL constraint, as the following example shows:

acct_id INTEGER(4) NOT NULL := 9999;

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. The NOT NULL constraint must be followed by an initialization clause. For example, the following declaration is illegal:

acct_id INTEGER(5) NOT NULL;  -- illegal; not initialized

Recall that the subtypes NATURALN and POSITIVEN are predefined as NOT NULL. For instance, the following declarations are equivalent:

emp_count NATURAL NOT NULL := 0;
emp_count NATURALN := 0;

In NATURALN and POSITIVEN declarations, the type specifier must be followed by an initialization clause. Otherwise, you get a compilation error. For example, the following declaration is illegal:

line_items POSITIVEN;  -- illegal; not initialized

Using %TYPE

The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:

credit REAL(7,2);
debit  credit%TYPE;

Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable.

The next example shows that a %TYPE declaration can include an initialization clause:

balance         NUMBER(7,2);
minimum_balance balance%TYPE := 10.00;

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in

my_dname scott.dept.dname%TYPE;

Using %TYPE to declare my_dname has two advantages. First, you need not know the exact datatype of dname. Second, if the database definition of dname changes, the datatype of my_dname changes accordingly at run time.

Note, however, that a NOT NULL column constraint does not apply to variables declared using %TYPE. In the next example, even though the database column empno is defined as NOT NULL, you can assign a null to the variable my_empno:

DECLARE
   my_empno emp.empno%TYPE;
   ...
BEGIN
   my_empno := NULL;  -- this works

Using %ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. In the example below, you declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.

DECLARE
   emp_rec emp%ROWTYPE;
   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
   dept_rec c1%ROWTYPE;

Columns in a row and corresponding fields in a record have the same names and datatypes. In the following example, you select column values into a record named emp_rec:

DECLARE
   emp_rec emp%ROWTYPE;
   ...
BEGIN
   SELECT * INTO emp_rec FROM emp WHERE ...

The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:

IF emp_rec.deptno = 20 THEN ...

Also, you can assign the value of an expression to a specific field, as the following examples show:

emp_rec.ename := 'JOHNSON';
emp_rec.sal := emp_rec.sal * 1.15;

Aggregate Assignment

A %ROWTYPE declaration cannot include an initialization clause. However, there are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor. For example, the following assignment is legal:

DECLARE
   dept_rec1 dept%ROWTYPE;
   dept_rec2 dept%ROWTYPE;
   CURSOR c1 IS SELECT deptno, dname, loc FROM dept;
   dept_rec3 c1%ROWTYPE;
BEGIN
   ...
   dept_rec1 := dept_rec2;

However, because dept_rec2 is based on a table and dept_rec3 is based on a cursor, the following assignment is illegal:

dept_rec2 := dept_rec3;  -- illegal

Second, you can assign a list of column values to a record by using the SELECT or FETCH statement, as the example below shows. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement.

DECLARE
   dept_rec dept%ROWTYPE;
   ...
BEGIN
   SELECT deptno, dname, loc INTO dept_rec FROM dept
      WHERE deptno = 30;

However, you cannot assign a list of column values to a record by using an assignment statement. So, the following syntax is illegal:

record_name := (value1, value2, value3, ...);  -- illegal

Although you can retrieve entire records, you cannot insert or update them. For example, the following statement is illegal:

INSERT INTO dept VALUES (dept_rec);  -- illegal

Using Aliases

Select-list items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases. In the following example, you use an alias called wages:

-- available online in file EXAMP4
DECLARE
   CURSOR my_cursor IS SELECT sal + NVL(comm, 0) wages, ename
      FROM emp;
   my_rec  my_cursor%ROWTYPE;
BEGIN
   OPEN my_cursor;
   LOOP
      FETCH my_cursor INTO my_rec;
      EXIT WHEN my_cursor%NOTFOUND;
      IF my_rec.wages > 2000 THEN
         INSERT INTO temp VALUES (NULL, my_rec.wages,
            my_rec.ename);
      END IF;
   END LOOP;
   CLOSE my_cursor;
END;

For more information about database column aliases, see Oracle7 Server SQL Reference.

Restrictions

PL/SQL does not allow forward references. You must declare a variable or constant before referencing it in other statements, including other declarative statements. For example, the following declaration of maxi is illegal:

maxi INTEGER := 2 * mini;  -- illegal
mini INTEGER := 15;

However, PL/SQL does allow the forward declaration of subprograms. For more information, see "Forward Declarations" .

Some languages allow you to declare a list of variables that have the same datatype. PL/SQL does not allow this. For example, the following declaration is illegal:

i, j, k SMALLINT;  -- illegal

The legal version follows:

i SMALLINT;
j SMALLINT;
k SMALLINT;