117. VARIABLE DECLERATIONS

Variables can be declared in declarative section of the block;

Ex:

DECLARE

      a number;

      b number := 5;

      c number default 6;

CONSTANT DECLERATIONS

To declare a constant, you include the CONSTANT keyword, and you must supply a default value.

Ex:

DECLARE

      b constant number := 5;

      c constant number default 6;

NOT NULL CLAUSE

You can also specify that the variable must be not null.

Ex:

DECLARE

      b constant number not null:= 5;

      c number not null default 6;

ANCHORED DECLERATIONS

PL/SQL offers two kinds of achoring.

Ø  Scalar anchoring

Ø  Record anchoring

SCALAR ANCHORING

Use the %TYPE attribute to define your variable based on table’s column of some other PL/SQL scalar variable.

Ex:

                        DECLARE

                              dno dept.deptno%type;

                              Subtype t_number is number;

                           a t_number;

                              Subtype t_sno is student.sno%type;

                              V_sno t_sno;

RECORD ANCHORING

Use the %ROWTYPE attribute to define your record structure based on a table.

Ex:

                                DECLARE

                                V_dept dept%rowtype;

BENEFITS OF ANCHORED DECLARATIONS

Ø  Synchronization with database columns.

Ø  Normalization of local variables.