PL/SQL

Printing Variables

Sometimes we might want to print the value of a PL/SQL local variable. A ``quick-and-dirty'' way is to store it as the sole tuple of some relation and after the PL/SQL statement print the relation with a SELECT statement. A more couth way is to define a bind variable, which is the only kind that may be printed with a print command. Bind variables are the kind that must be prefixed with a colon in PL/SQL statements, such as :new discussed in the section on triggers.

The steps are as follows:

1. We declare a bind variable as follows:

VARIABLE <name> <type>

Where the type can be only one of three things: NUMBER, CHAR, or CHAR(n).

2. We may then assign to the variable in a following PL/SQL statement, but we must prefix it with a colon.

3. Finally, we can execute a statement

PRINT :<name>;

outside the PL/SQL statement

Here is a trivial example, which prints the value 1.

VARIABLE x NUMBER

BEGIN

:x := 1;

END;

.

run;

PRINT :x;