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.
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;
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:
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.
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.
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.
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.
The CLOB data type is used to store character strings of up to 4 GB. Oracle stores CLOB data in variable-length format.