Declare variables in PL/SQL

Background

In this post we will see how to declare and initialize variables in PL/SQL block.

Syntax

General syntax to declare variable in PL/SQL is

var_nm datatype [NOT NULL := var_value ];

• var_nn is the name of the variable.
• datatype is a valid PL/SQL datatype.
• NOT NULL is an optional specification on the variable which this variable cannot be assigned null value.
• var_value or DEFAULT value is also an optional specification, where you can initialize a variable with some specific value.
• Each variable declaration is a separate statement and must be terminated by a semicolon.

We can assign value to variables in one of the following two ways -

1. direct assignment (Eg. var_nm:= var_value;)
2. Using select from (Eg. SELECT col_nm INTO var_nm FROM tbl_nm [WHERE clause];)

 Usage Examples

keeping above syntax in mind you can define variables as follows -

DECLARE 
 id number; 
BEGIN
 SELECT 1000 into id from dual;
 dbms_output.put_line('id : '|| id ); 
END; 
/

OR

DECLARE 
 id number := 1000; 
BEGIN
 dbms_output.put_line('id : '|| id ); 
END; 
/

NOTE : '/' i.e Back slash after END keyword indicates to execute the above PL/SQL Block.

Related Links

• Difference between DML and DDL statements in SQL   
• Installing Oracle instant database client in Ubuntu 
• Basic PL/SQL syntax and query executions on Oracle 11g R2 database