Oracle / PLSQL: Declaring Variables

 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.

l_customer_name varchar2(40);

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

l_customer_name:= 'Ahmed and co';

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.

l_customer_type varchar2(40) := 'Retail';

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

l_customer_type := 'Whole Sale';

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.

l_total CONSTANT numeric(8,1) := 1051023.1;