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.
- Constant
- Not Null
- 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
- 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);
- 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.
0 Comments
If you have any doubts, Please let me know