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 :
- The %Type datatype is use to define the variable as column name datatype for specified table.
- 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).
- 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.
- 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:
- 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.
| %TYPE | %ROWTYPE |
| The %Type datatype is use to define the variable as column name datatype for specified table | If 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.