Saturday 5 December 2015

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; 
/

t> UA-39527780-1 back to top