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 ...