You declare PL/SQL variables, constants and types in declare block. The syntax is
<name> [CONSTANT] <datatype> [NOT NULL] [:= | DEFAULT <expr>]
- <name> is the name of the variable or constant;
- <datatype> may be scalar, composite datatype, reference or LOB;
- <expr> is a literal value, another variable or any PL/SQL expression. The result of the expression is used to assign the default value of the variable or constant.
- If you are defining a constant, you must provide the value of the constant in the definition.
- TABLE, RECORD, NESTED TABLE and VARRAY are composite types.
- To define scalar type you can refer to [schema.]object%TYPE or cursor%TYPE.
- For record type you can refer to [schema.]object%ROWTYPE.
Examples:
DECLARE
--Define some scalar variables
v_year NUMBER(4);
v_pin VARCHAR2(12);
v_min_amount NUMBER(20, 2) := 25000;
--Define a record type
TYPE t_customer IS RECORD (
id NUMBER(18),
first_name VARCHAR2(32),
last_name VARCHAR2(32),
);
--Declare a record variable
v_customer t_customer;
--Declare a composite type to hold a list of records
TYPE t_customer_list IS TABLE OF t_customer;
--Declare a record variable refering an existing table
v_employee employee%ROWTYPE;
BEGIN
v_year := 2010;
-- More PL/SQL code here.
END;
Declare RECORD variables and types
A specific RECORD TYPE corresponding to a fixed number (and datatype) of underlying table columns can simplify the job of defining variables.
%TYPE is used to declare a field with the same type as that of a specified table’s column.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.
Declare RECORD type
TYPE <type_name> IS RECORD( <field_declaration>,...);
Each record field is declared with its ‘field_declaration’:
<field_name> {<field_type> | <variable>%TYPE | <table.column>%TYPE | <table>%ROWTYPE} [ [NOT NULL] {:= | DEFAULT} <expr> ]
The <field_type> can be any PL/SQL data type except REF CURSOR.
To declare a record variable:
<variable_identifier> <type_name>;
You can declare a variable based on a record reference using %ROWTYPE:
<variable_name> <table_or_curosr_name>%ROWTYPE
The number of variables and their data type is evaluated at run-time.