O'Reilly logo

Oracle SQL: the Essential Reference by David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required