What is Difference between %type and %rowtype with examples?

I have written so many articles on SQL difference between subject. I would like to focus on Difference between %TYPE and %ROWTYPE in this article. We need to give the clear idea about difference between %type and %rowtype.

What we will cover in this article?

1.What is mean by %TYPE and its use.

2. What is mean by %ROWTYPE and its use.

3.Difference between %type and %rowtype in tabular format.

%Type with its use :

  1. The %Type datatype is use to define the variable as column name datatype for specified table.
  2. Lets take a small example of customer table where Customer_name datatype is varchar2(30). If you give the %type then the specified column or variable will take the datatype as varchar2(30).
  3. How to define this? vCustomerName Customer.Customer_Name%TYPE; In the above define statement the datatype of Customer_Name will be assign to the variable named vCustomerName.
  4. Real life example : If you want to create a simple PL SQL block to check the assignment for the datatype from direct table. PL SQL block :

DECLARE v_Customer_name Customer.Customer_name%TYPE;

— This will assign the declaration value of Customer_name to

—-v_Customer_name variable

BEGIN

SELECT Name INTO v_Customer_name

FROM Customer

WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE(v_Customer_name);

END;

%ROWTYPE with its use:

  1. If you dont know the datatype of specified column and you require to assign that datatype to the variable then you can use %ROWTYPE.

2.It is nothing but the assignment in array where we can define the whole row datatype to specified variable.

3.How to define this?

Rt_var_Customer Customer%ROWTYPE;

It will assign the datatype of Customer table to Rt_var_Customer variable.

4.Real life example : If you want to create a simple PL SQL block to check the assignment for the whole row datatype from direct table.

Declare

Rt_var_Customer Customer%ROWTYPE;

BEGIN

Rt_var_Customer.Customer_Name = ‘Amit’;

Rt_var_Customer.Customer_id=’12342′;

DBMS_OUTPUT.PUT_LINE(Rt_var_Customer.Customer_Name);

DBMS_OUTPUT.PUT_LINE(Rt_var_Customer.Customer_id);

End;

Difference between %type and %rowtype

Difference between %type and %rowtype in tabular format.

%TYPE%ROWTYPE
The %Type datatype is use to define the variable as column name datatype for specified tableIf you dont know the datatype of specified column and you require to assign that datatype to the variable then you can use %ROWTYPE.
vCustomerName Customer.Customer_Name%TYPE;Rt_var_Customer Customer%ROWTYPE;

I hope this article is useful to you and you will get clear idea about difference between %type and %rowtype. If you like this article or if you have any concerns with the same kindly comment in comments section.