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 in tabular format.

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.