Oracle Declare Variable

Whether you are just getting started in development or a seasoned developer, you will be working with variables.

In programming, variables generally refer to a named storage container which is used to store the various data types. You can reuse those named variables in other code sections without redefining them.

Like all programming languages, the Oracle PL/SQL language allows us to define the variables that we can reuse in the various sections of our scripts. For example, you can define the variables in functions, procedures, loops, and more.

In this tutorial, we will walk you through the basics of working with variables in Oracle databases.

Oracle Variable Declaration

Before using any variable in your code, you need to declare it first. Variable declaration refers to creating the variable, giving it a name, and defining what data type it holds.

Variable declaration is a fundamental part of working with variables. It allows the system to know the variable’s existence, what data type it stores, and the allocated memory to it.

In Oracle, we can declare a variable as shown in the following syntax:

variable_name [CONSTANT] data_type [NOT NULL] [:= | DEFAULT initial_value];

The following are the parameters which are provided in the syntax:

variable_name – This defines the name that you wish to assign to your variable.
CONSTANT – The constant keyword allows you to define whether a variable can be changed. If the variable is set to CONSTANT, the database engine prevents the changing of the value later in the program after declaration and assignment.
data_type – This allows you to specify what data type that the variable holds. The variable can hold any supported Oracle data type such as numbers, varchar2, nvarchar2, clob, nclob, etc.
DEFAULT – The default clause allows you to set a default value to be used instead of a missing variable value.

Example Variable Declaration

The following code shows a simple example of declaring three variables that can store user the information:

DECLARE
first_name VARCHAR2(50);
last_name VARCHAR2(50);
email VARCHAR2(100);
BEGIN
NULL;
END;

This allows us to define three variables of the VARCHAR2 data type. It is good to note that you are not limited to VARCHAR2 data types.

Oracle Set Default Variable Value

We can also define a default value for a variable during declaration. This prevents the database engine from setting the variable’s value to NULL if the value is not provided.

For example:

DECLARE
first_name VARCHAR(50) DEFAULT ‘Jane Done’
BEGIN
NULL;
END;

The previous example defines a new variable called first_name of varchar2 data type with a default value of “Jane Doe”.

You can also use the assignment operator instead of the DEFAULT keyword as shown in the following:

DECLARE
first_name VARCHAR(50) :=‘Jane Done’
BEGIN
NULL;
END;

This works similarly to the previous example.

Oracle Variable Assignment

Once we declare a variable, we can assign a value to it using the assignment operator (:=) as shown in the following:

DECLARE
first_name VARCHAR(50) ;
BEGIN
first_name := ‘Martin L’;
END;

To print the value of the variable, we can use the following syntax:

DBMS_OUTPUT.PUT_LINE(variable_name);

Conclusion

In this tutorial, you learned how to use the declare keyword to declare a variable in Oracle database.