Oracle PL/SQL - %TYPE Attribute

Introduction

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:

your_variable referenced_item%TYPE; 

The referencing item inherits the following from the referenced item:

  • Data type and size
  • Constraints

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:

variable_name table_name.column_name%TYPE; 

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.

Demo

SQL>
SQL> drop TABLE emp;

Table dropped.--  w w w  . java  2 s .co 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>

Related Topics