Declaring Variables and Constants

Previous

Next

Variables can have any SQL datatype, such as VARCHAR2, DATE, or NUMBER, or a PL/SQL-only datatype, such as a BOOLEAN or PLS_INTEGER. You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes. See "Working With PL/SQL Data Structures".

Declaring a constant is similar to declaring a variable except that you must add the CONSTANT keyword and immediately assign a value to the constant. No further assignments to the constant are allowed. For an example, see the avg_days_worked_month constant in Example: Declaring Variables in PL/SQL.

For example, assume that you want to declare variables for employee data, such as employee_id to hold 6-digit numbers and active_employee to hold the Boolean value TRUE or FALSE. You declare these and related employee variables and constants, as shown in Example: Declaring Variables in PL/SQL.

Note that there is a semi colon (;) at the end of each line in the declaration section. Also, note the use of the NULL statement that enables you to run and test the PL/SQL block.

You can choose any naming convention for variables that is appropriate for your application, but the names must be valid PL/SQL identifiers. See "Using Identifiers in PL/SQL".

DECLARE -- declare the variables in this section
  last_name              VARCHAR2(30);
  first_name             VARCHAR2(25);
  employee_id            NUMBER(6);
  active_employee        BOOLEAN;
  monthly_salary         NUMBER(6);
  number_of_days_worked  NUMBER(2);
  pay_per_day            NUMBER(6,2);
  avg_days_worked_month  CONSTANT NUMBER(2) := 21; -- a constant variable
BEGIN
  NULL; -- NULL statement does nothing, allows this block to executed and tested
END;
/