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