7.3. Declaration Section

The declaration section is required if any variables are to be used in a PL/SQL block. The declaration section also defines cursors, types, local procedures, and functions that are used in a block. If no variables or other elements need to be declared, then this section may be omitted.

A PL/SQL variable is not the same as a table column. A column name, which may appear, for example, in a SELECT statement or WHERE clause, is not a variable and does not need to be declared. However, the target of a SELECT...INTO... statement is a variable, and must be declared.

7.3.1. Declaring Variables

Several different types of variables may be defined in a PL/SQL block. You can use simple, scalar variables to hold numeric, character string, and date values. You can declare record variables that allow you to manipulate several related values together. You can even declare in-memory tables and arrays.

The syntax for declaring a scalar variable is shown here:

variable_name type [CONSTANT] [NOT NULL] [:=initial_value]

Declares a variable explicitly.

					variable_name [schema.] table_name.column_name%TYPE

Declares a variable by reference to an existing database column.

					variable_name pl/sql_variable%TYPE

Declares a variable by reference to a previously defined PL/SQL variable.

7.3.1.1. Keywords

variable_name

Specifies the variable's name. A valid name may be up to 30 characters ...

Get Oracle SQL: the Essential Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.