DECLARE

Declare variables and constants in a PL/SQL declare block.

Syntax:

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

key
   name      : The name of the variable

   datatype  : may be scalar, composite, reference or LOB

   expr      : a literal value, another variable 
               or any plsql expression involving operators & functions

For readability put only one declaration per line .

A constant MUST have it's initial value in the declaration.

Composite datatypes are TABLE, RECORD, NESTED TABLE and VARRAY

You can use [schema.]object%TYPE to define variables based on actual object datatypes.

Declaring RECORD variables

A specific RECORD TYPE corresponding to a fixed number (and datatype) of underlying table columns can simplify the job of defining variables.

Syntax:

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

Options
 'field_declaration' is defined as:

   field_name {field_type |
               variable%TYPE |
               table.column%TYPE |
               table%ROWTYPE}
               [ [NOT NULL] {:= | DEFAULT} expr ]

        Where field_type is the datatype of the field
        (any plsql datatype except REF CURSOR)

        expr is the field_type or an initial value

Then to declare a record variable of this type..

   identifier type_name;

Declare %ROWTYPE% Record variables:

   DECLARE
      variable_name table_name%ROWTYPE%   

At runtime the system will evaluate the number of variables and their datatype; The columns may be based on an underlying table or a cursor.

Declare SQL*Plus bind variables.

Syntax:

plus > VARIABLE g_bar VARCHAR2(30)
plus > ACCEPT p_foo PROMPT 'enter the value required'

You can reference host variables in PL/SQL statements *unless* the statement is in a procedure, function or package.
This is done by prefixing with & (to read the variable) or prefix with : (writing to the variable)

Examples:

DECLARE
-- Declare individual variables:

   v_ename emp.ename%TYPE;
   v_balance NUMBER(7,2);
   v_min_bal v_balance%TYPE := 10;

-- Declare RECORD TYPE variable:

   TYPE job_type IS RECORD
      (jobid    NUMBER(7,2),
       jobname  t_jobs.jb_name%Type);
   
   job_record job_type;

-- Declare a variable based on SQL*Plus Bind variable

   v_amount NUMBER(6,2) := &p_foo

-- Assign value to a SQL*Plus variable from a PL/SQL variable

BEGIN
   :g_bar := v_amount *12

Related Commands:

Related Views: