Oracle / PLSQL: Declaring Variables

This Oracle tutorial explains how to declare variables in Oracle/PLSQL with syntax and examples.

What is a variable in Oracle?

In Oracle/PLSQL, a variable allows a programmer to store data temporarily during the execution of code.

Syntax

The syntax for declaring variables in Oracle is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Parameters or Arguments

variable_name

The name to assign to the variable.

CONSTANT

Optional. If specified, the variable's value is constant and can not be changed.

datatype

The datatype to assign to the variable.

Example - Declaring a variable

Below is an example of how to declare a variable in Oracle called LDescription.

LDescription varchar2(40);

You can then later set or change the value of the LDescription variable, as follows:

LDescription := 'aodba.com Example';

Example - Declaring a variable with an initial value (not a constant)

Below is an example of how to declare a variable in Oracle and give it an initial value. This is different from a constant in that the variable's value can be changed later.

LType varchar2(40) := 'aodba.com Example';

You could later change the variable's value, as follows:

LType := 'My value has changed';

Example - Declaring a constant

Below is an example of how to declare a constant in Oracle. The value of a constant can not be changed.

LTotal CONSTANT numeric(8,1) := 8363934.1;