PL/SQL: Handling Variables

Shafeen

• Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such  array type VARRAY.
• Only TRUE and FALSE can be assigned to BOOLEAN type of variables.
• AND, OR, NOT operators can be used to connect BOOLEAN values.
• % TYPE attribute can be used to define a variable which is of type the same as a database column's type definition.
• Users can customize the variable types by using TYPE ... IS ... statement.

The following code block illustrates the use of TYPE..IS... and VARRAY. In this sample, a type v_arr is defined as an variable array of maximum 25 elements which are of type NUMBER(3).  Then a variable v1 is defined as type v_arr .    This sample code also demonstrates the use of %TYPE attribute.

DECLARE
TYPE v_arr IS VARRAY(25) of NUMBER(3);
v1 v_arr;
v_empno employee.empno%TYPE;

BEGIN
    v1(2) := 3;
    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2)); 
      v_empno  := 4;
END;