Variables in Oracle PL/SQL - Part 2

In this PL/SQL Tutorial, We will explore more about the variables in Oracle PL/SQL. If you are on this post so I will suggest to you please learn our previous topic Variable in Oracle PL/SQL Part-1.

In this Session we will cover rest of keywords which we can use with the of Variable creation.

1. Constant
2. Not Null
3. Default

What is Constant in Oracle PL/SQL?

Constant is a constraint which use for when you don't want to change variable value throughout the program. Once you declare a constant variable  then  it is not changeable within the program.

Syntax of Constant in Oracle PL/SQL

variable_name constant data_types:=expression;

How to Declare Constant in Oracle PL/SQL

v_name constant varchar2(200):='Javainhand Tutorial';

Example of Constant in Oracle PL/SQL

DECLARE
V_NAME CONSTANT VARCHAR2(100):='Javainhand Tutorial';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

Points to be remember before creating constant variables

1. Constant variables must have an assigned value using default keyword or assignment operator otherwise program returns an error. Your will be facing this type of error (PLS-00322: declaration of a constant 'V_NAME' must contain an initialization assignment);
2. You can not assign  new value to the constant variables otherwise program will not  be execute because of the error. Your will be facing this type of error (expression 'V_NAME' cannot be used as an assignment target);

Let's understand above statements with the help of some program which will help more about the constant variables.

Example 1:-

DECLARE
V_NAME CONSTANT VARCHAR2(100);
BEGIN
null;
END;

Given the above example, if you will run same PL/SQL block in your environment then declaration of a constant variable must contain an initialization assignment error will display.

Example:-2

DECLARE
V_NAME CONSTANT VARCHAR2(100):='javainhand Tutorial';
BEGIN
V_NAME:='Test';
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

Given the above example, if you will run same PL/SQL block in your environment then (expression 'V_NAME' cannot be used as an assignment target)  error will display after the program execution.

NOT NULL

In a PL/SQL program,if you are using NOT NULL keyword with variables declaration means you have to assign some values to that variables otherwise error will display at the time of program execution.

Syntax of define NOT NULL keyword

variable_name  data_types not null:=expression;

Example of NOT NULL Keyword

DECLARE
V_NAME  VARCHAR2(100) not null:='test';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

Point to be remember

Assigning the value of NOT NULL variables must be at the time of declaration otherwise program return error like (PLS-00218: a variable declared NOT NULL must have an initialization assignment).

Default

Instead of assignment operator(:=), You can use default keyword to assigning a value to the variables.

Syntax of define Default Keyword in variables declaration

variable_name  data_types default expression;

Example of define Default Keyword in  variables declaration

DECLARE
V_NAME  VARCHAR2(100) default 'test';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

Can we use all three keyword in a single variable?

Yes,we can use all three keyword in a single variable declaration.

Example

DECLARE
V_NAME constant VARCHAR2(100) not null default 0 ;
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

That's all for today, I think this post will help for understanding about the variables declaration in Oracle PL/SQL. If you have any doubt about this topic then ask the questions through comment.