The %TYPE attribute can declare a data type as a previously declared variable or column without knowing what that type is.
If the data type of the referenced item changes, then the declaration of the referencing item changes accordingly.
The syntax of the declaration is:
The referencing item inherits the following from the referenced item:
your_variable does not inherit the initial value from the referenced item.
Therefore, if your_variable specifies or inherits the NOT NULL constraint, you must specify an initial value for your_variable.
The %TYPE attribute is useful when declaring variables to hold values from a table row.
The syntax for declaring a variable of the same type as a column is:
In the following code, the variable surname inherits the data type and size of the column emp.last_name, which has a NOT NULL constraint.
Because surname does not inherit the NOT NULL constraint, its declaration does not need an initial value.
SQL> SQL> drop TABLE emp; Table dropped.-- w w w . jav a 2 s . c o m SQL> SQL> CREATE TABLE emp( 2 empid NUMBER(6), 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(25)) ; SQL> SQL> DECLARE 2 surname emp.last_name%TYPE; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('surname=' || surname); 5 END; 6 / surname= PL/SQL procedure successfully completed. SQL>