Oracle PL/SQL Variables and Constants

Variables and Constants

In a programming unit, Variables & constants are the places for us to store our data/value. This section would help you to understand the use of variables and constants in a PL/SQL program.


Variables

Value of a variable can be changed anywhere with in the PL/SQL block (between BEGIN & END). In the previous chapter also, we have seen an example with the usage of variables. However, we would see its usage in more detail. Below is the syntax for defining variables.

Syntax

  <<Variable_name>> <data-type> [NOT NULL] := <<initial_value>>; 


Where,

Variable_name

is user defined (given by us).
This should start with alphabet.
It can have only alphabets, numbers & underscore (_).

data-type is any of the oracle defined data types. We can see more about this in a separate chapter.

NOT NULL This is a reserved key word used to eliminate NULL values.

initial_value should be if the same type as the data-type. Character and date values should be enclosed in quotes (´).

Constants

Constant is a place holder whose value cannot be changed after definition or declaration. We need to define the value of a constant with in the declaration statement itself (in the definition). Declaration of constant is similar to declaration of a variable with a additional key word called CONSTANT. CONSTANT keyword is used to define a constant. When a variable is defined using CONSTANT key word, PL/SQL engine will not allow the changes in its value any where else. Below is the syntax for defining a constant.


Syntax

<<Constant_Name>> CONSTANT <data-type> := <<value_of_constant>>;

Where,

Constant_Name

      is user defined (given by us).
      this should start with alphabet.
      can have only alphabets, numbers & underscore (_).


data-type is any of the oracle defined data types. We can see more about this in a separate chapter.

value_of_constant should be if the same type as the data-type. Character and date values should be enclosed in quotes (´).

Example


Below is an example where i am deriving the area of a circle. Value of PI is defined as a constant. Radius and Area of circle is defined as variables.

 DECLARE
    CONST_PI CONSTANT NUMBER := 3.14;
    VAR_RADIUS NUMBER := 0;
    VAR_AREA NUMBER :=0; 
 BEGIN
    VAR_RADIUS := 10;
    VAR_AREA := CONST_PI * VAR_RADIUS * VAR_RADIUS;
    DBMS_OUTPUT.PUT_LINE(´Area = ´ || VAR_AREA );
 END;
 /

Scope of a variable

Scope or life time of a variable is always with in the PL/SQL Block which is between BEGIN & END. Please note we can have a PL/SQL block with in another PL/SQL block. The scope of a variable defined in a inner PL/SQL block lies only with in that inner block. Please refer below PL/SQL with multiple blocks.

DECLARE
     CONST_GLOBAL CONSTANT NUMBER := 100;
     VAR_GLOBAL NUMBER NOT NULL :=0;
BEGIN
     -- First inner block
     DECLARE
          VAR1_LOCAL NUMBER;
     BEGIN
          VAR_GLOBAL := VAR1_LOCAL * CONST_GLOBAL;
     END;
     DBMS_OUTPUT.PUT_LINE(´GLOBAL VAR IS NOW :´ || VAR_GLOBAL );
     -- Second inner block
     DECLARE
          VAR2_LOCAL NUMBER;
     BEGIN
          VAR_GLOBAL := VAR2_LOCAL * CONST_GLOBAL;
     END;
     DBMS_OUTPUT.PUT_LINE(´GLOBAL VAR IS NOW :´ || VAR_GLOBAL );
END;
/

Please note in above example, we have 3 PL/SQL blocks (BEGIN .. END). One PL/SQL acts as the main or outer block and the two other are inner to main. In this example,

VAR_GLOBAL is valid in all the 3 blocks.
VAR1_LOCAL is valid only within first inner block.
VAR2_LOCAL is valid only within second inner block.

More by this Author


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working