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 isvar_nm datatype [NOT NULL := var_value ];We can assign value to variables in one of the following two ways -
- 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.
- direct assignment (Eg.
var_nm:= var_value;) - 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.
