PL/SQL Variables

Declaration Syntax

          identifier [CONSTANT] datatype [NOT NULL] [:= expr | DEFAULT expr]

          Note: Square brace indicates optional  

Valid variable declarations                                      

DECLARE

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) := ’Atlanta’;

c_comm CONSTANT NUMBER := 1400;

v_NoOfSeats  NUMBER DEFAULT 45;

v_FirstName VARCHAR2(20) DEFAULT ‘SCOTT’

  

Invalid variable Declarations

          v_deptno number(2) NOT NULL;

          v_name varchar2 DEFAULT ‘Sachin’;

Inheriting data type

          You can declare variables to inherit the data type of a database column or other variable

          v_empno emp.empno%TYPE ;

Constants: using the CONSTANT keyword we can declare a constant

          c_max_size CONSTANT NUMBER := 100;

Bind Variable: a variable declared in a host environment and used in many blocks by referencing it with “:” prefix

Row Type Variable: holds one record/row at a time

          e.g. v_record emp%ROWTYPE;

PL/SQL Record type Variable: A record is a group of related data items stored in fields, each with its own name and data type.

A record containing a field for each item lets you treat the data as a logical unit.

The variable based on a PL/SQL record type is a composite data member having fields as defined in the corresponding record type 

 

Syntax:

          TYPE type_name IS RECORD (field_declaration[,field_declaration]...);

       variable_name type_name;

Example:

                   -- Type declaration

      TYPE DeptRec IS RECORD (                     

                             dept_id dept.deptno%TYPE,

                             dept_name VARCHAR2(14),

                             dept_loc VARCHAR2(13)

                                         );

            -- Record type variable declaration

        vDeptRec DeptRec;     

Guidelines for Declaring PL/SQL Variables 

Ø Follow the naming conventions 

Ø Initialize variables designated as NOT NULL and CONSTANT 

Ø Declare one identifier per line 

Ø Initialize identifiers by using the assignment operator (:=) or the reserved word “DEFAULT”