PL/SQL Variables and Constants

Variables:-

                PL/SQL variables are the temporary data storage for any of the available oracle data-type which can be used/modify within PL/SQL block for any calculation or data retrieval.

Syntax:-

                emp_id varchar(5);

Later we can assign some value in PL/SQL block as below:

emp_id := ‘10001’;

Declaring a PL/SQL variable with some default value (its not a constant, we can change it further) as below:

emp_id varchar(5) := ‘10001’;

Later we can change the above as below:

emp_id := ‘20001’;

                We can declare a variable as NOT NULL and then we have to give some default value at the time of declaration, later we can re-assign the value:

        emp_dept varchar2(10) NOT NULL := ‘IT Dept’;

Scope of PL/SQL variable:-

                There are two ways to declare a PL/SQL variable:

• Local variables - declared in an inner block and cannot be referenced by outside Blocks.
• Global variables - declared in an outer block and can be referenced anywhere in PL/SQL program.

Example as below:

DECLARE

 var_1 number := 10;   // can be used anywhere (global)

 var_2 number;   // can be used anywhere (global)

 BEGIN

  var_2 := 20;

  DECLARE

   var_res number; // can be used locally (local)

   BEGIN

    var_res := var_1 * var_2;   // var_1 & var_2 getting used here

   END;

END;

/            

Constants:-

                As the name itself suggests that a constant is a placeholder which can not be changed once defined in a PL/SQL block. Benefit of a constant is that you can declare it at one place assigning the value and use the same variable at number of places and even in future when you want to update/change the value of constant you have to do it at single place only.

There is a reserved keyword ‘CONSTANT’ to declare a constant and you have to assign the value there itself. . If you will not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get an error.

For Example the below code snippet will throw an error:

DECLARE

    pie CONSTANT numeric(1,2);

BEGIN

    pie := 3.14;

    dbms_output.put_line (pie);

END;

So must be written as below:

DECLARE

 pie CONSTANT numeric(1,2) := 3.14;

BEGIN

    dbms_output.put_line (pie);

END;