PL/SQL Variables – Oracle PL/SQL Tutorial

What is a variable?

A PL/SQL variable is a placeholder for a value. This value can be a number, date, or text string. You can use variables in your Oracle PL/SQL programs to store values temporarily.

Syntax

To create a variable, you use the following syntax:

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

You can also declare multiple variables in a single statement as follows:

variable1 datatype; 
variable2 datatype; 
... 
variableN datatype;

Example simple variable

For example, the following statement creates a variable named my_num of type NUMBER and assigns the value 10 to it:

my_num number := 10;

Example multiple variables

declare
string_variable   varchar2(50) := 'TEST';
number_variable   number(3) := 456;
date_variable     date(20) := sysdate;
type_variable     table_name.column_name%TYPE;
record_variable   table_name%ROWTYPE;
boolean_variable  BOOLEAN;
file_variable     Bfile;
store_variable    BLOB;
begin
--PL/SQL statements
end;

Variable declaration

PL/SQL allows multiple types of data that can be used to declare variables. You must declare a PL/SQL variable before using it in your Oracle database. A PL/SQL variable declaration consists of a variable name, its data type, and an optional default value.

You can declare PL/SQL variables in two ways:
– In the declaration section of the PL/SQL block
– In a procedure or function

When you declare a variable in the declaration section, it is called a local variable. A local variable is only visible to the block in which it is declared.

You can also declare variables in a procedure or function. These variables are called parameters. Parameters are input values that are passed to procedures and functions.

Types of variables

There are various types of variables that you can use in Oracle PL/SQL. Some of the most common variable types are listed below:

DATE

The DATE data type stores date and time values. A DATE value consists of a year, month, day, hour, minute, and second. You can use the TO_DATE and TO_CHAR functions to convert a date from one format to another.

NUMBER

The NUMBER data type is used to store numeric values. Numeric values can be positive or negative whole numbers or decimal numbers. Oracle database stores numeric data in variable-length format.

VARCHAR2

The VARCHAR2 data type is used to store character strings of up to 4,000 characters for table columns and 32767 characters for variables. Oracle stores character strings in variable-length format.

PLS_INTEGER

The PLS_INTEGER data type is used to store integers. Integers are whole numbers that can be positive or negative. Oracle stores PLS_INTEGER data in fixed-length format.

CLOB

The CLOB data type is used to store character strings of up to 4 GB. Oracle stores CLOB data in variable-length format.