Oracle PL/SQL - PL SQL Introduction Declaring variables

Introduction

In PL/SQL, variables must be included in the declaration block before they can be used.

The most common way to declare a variable is as shown here:

declare 
       variable_name [constant] DATATYPE 
                      [DEFAULT value |DEFAULT NULL]; 
begin 
... 

Variable names are PL/SQL identifiers.

The data type of the variable may be a standard SQL datatype such as NUMBER, VARCHAR2, DATE or a PL/SQL datatype such as BOOLEAN, BINARY_INTEGER.

The keyword constant means that the variable's value can't be changed.

If you declare a variable as a constant, you must assign a default value to it by using the optional DEFAULT value clause.

If you don't use a DEFAULT clause, the variable will have a NULL value, indicating that the variable has been declared but not yet initialized.

The following shows an example of correct declarations of variables:

declare 
      v_sal_nr   NUMBER; 
      v_name_tx  VARCHAR2(10) DEFAULT 'KING'; 
      v_start_dt DATE := SYSDATE; -- same as DEFAULT SYSDATE 
begin 
      ... 

You can declare a variable by reference.

%TYPE is for simple variables and %ROWTYPE is for variables that can store the whole row:

declare 
      --the variable has the same datatype as the specified column. 
      variable_name  table.column%TYPE;             
  
      --References a variable already defined in the code. 

      --Creates a record variable that can store all 
      --the columns from the row in the specified table. 

      variable_name2 variable_name%TYPE;          
      variable_row         table%ROWTYPE;          
begin 
      ... 

In addition to tables, views, cursors, and other record variables could be also used as a point of reference.

The following code shows some examples of defining datatypes:

declare 
      v_empno1 emp.empNo%TYPE; 
      v_empno2 v_empNo%TYPE; 
      v_dept_rec dept%ROWTYPE; 
begin 
      ... 

Related Topic