DB2 Version 10.1 for Linux, UNIX, and Windows

Variable declarations (PL/SQL)

Variables that are used in a block must generally be defined in the declaration section of the block unless they are global variables or package-level variables. A variable declaration consists of a name that is assigned to the variable and the data type of the variable. Optionally, the variable can be initialized to a default value within the variable declaration.

Syntax


>>-name--+----------+--type--+----------+----------------------->
         '-CONSTANT-'        '-NOT NULL-'   

>--+-----------------------------+-----------------------------><
   '-+-:=------+--+-expression-+-'   
     '-DEFAULT-'  '-NULL-------'

Description

name: Specifies an identifier that is assigned to the variable.
CONSTANT: Specifies that the variable value is constant. A default expression must be assigned, and a new value cannot be assigned to the variable within the application program.
type: Specifies a data type for the variable.
NOT NULL: Currently ignored by DB2®. Routines that specify NOT NULL for variable declarations compile successfully. However, such routines behave as though NOT NULL has not been specified. No run-time checking is performed to disallow null values in variables declared NOT NULL. See the following example, if your application needs to restrict null values in PL/SQL variables.
DEFAULT: Specifies a default value for the variable. This default is evaluated every time that the block is entered. For example, if SYSDATE has been assigned to a variable of type DATE, the variable resolves to the current invocation time, not to the time at which the procedure or function was precompiled.
:=: The assignment operator is a synonym for the DEFAULT keyword. However, if this operator is specified without expression, the variable is initialized to the value NULL.
expression: Specifies the initial value that is to be assigned to the variable when the block is entered.
NULL: Specifies the SQL value NULL, which has a null value.

Examples

The following procedure shows variable declarations that utilize defaults consisting of string and numeric expressions:

CREATE OR REPLACE PROCEDURE dept_salary_rpt (
    p_deptno        NUMBER
)
IS
    todays_date     DATE := SYSDATE;
    rpt_title       VARCHAR2(60) := 'Report For Department # ' || p_deptno 
    				     || ' on ' || todays_date;
    base_sal        INTEGER := 35525;
    base_comm_rate  NUMBER := 1.33333;
    base_annual     NUMBER := ROUND(base_sal * base_comm_rate, 2);
BEGIN
    DBMS_OUTPUT.PUT_LINE(rpt_title);
    DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;

The following sample output was obtained by calling this procedure:

CALL dept_salary_rpt(20);

Report For Department # 20 on 10-JUL-07 16:44:45
Base Annual Salary: 47366.55

The following example restricts null values by adding explicit checks using IS NULL or IS NOT NULL and handles error cases as required:

create table T(col1 integer);
insert into T values null;

declare
  N integer not null := 0;
  null_variable exception;
begin
  select col1 into N from T;
  if N is null then
    raise null_variable;
  end if;
exception
  when null_variable then
    -- Handle error condition here.
    dbms_output.put_line('Null variable detected');
end;