%TYPE & %ROWTYPE Attribute with Example

%TYPE Attribute

%TYPE is used to declare a field with the same type as that of a specified table's column. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased.

Example:

DECLARE

   v_EmpName  emp.ename%TYPE;

BEGIN

   SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;

   DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);

END;

/

%ROWTYPE Attribute

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.

The %ROWTYPE attribute enables a programmer to create table-based and cursor-based records.

Example-01(Table Based Records):

DECLARE

   customer_rec customers%rowtype;

BEGIN

   SELECT * INTO customer_rec

   FROM customers

   WHERE id = 5;

   dbms_output.put_line('Customer ID: ' || customer_rec.id);

   dbms_output.put_line('Customer Name: ' || customer_rec.name);

   dbms_output.put_line('Customer Address: ' || customer_rec.address);

   dbms_output.put_line('Customer Salary: ' || customer_rec.salary);

END;

/

Example-01(Cursor Based Records):

DECLARE

   CURSOR customer_cur is

      SELECT id, name, address 

      FROM customers;

   customer_rec customer_cur%rowtype;

BEGIN

   OPEN customer_cur;

   LOOP

      FETCH customer_cur into customer_rec;

      EXIT WHEN customer_cur%notfound;

      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);

   END LOOP;

END;

/