Difference between %TYPE and %ROWTYPE in Oracle

Difference between %TYPE and %ROWTYPE in Oracle

%TYPE: is used to defined the data type of variable as the column name datatype specified for a table. If table column has datatype varchar2(30) then %type variable has same datatype varchar2(30).
Define as: vEmployeeName Employee.Name%TYPE;

Example

SET SERVEROUTPUT ON
DECLARE
vEmployeeName Employee.Name%TYPE;
BEGIN
SELECT Name INTO vEmployeeName
FROM Employee
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(vEmployeeName);
END;
/

%ROWTYPE: Used to declare a record with the same types as found in the specified table, (it is like an array which defined all columns of a table with their datatype representation used with variable name with column_name of table).
Define as:rEmployee Employee%ROWTYPE;

Example

SET SERVEROUTPUT ON
DECLARE
rEmployee Employee%ROWTYPE;
BEGIN
rEmployee.Name := 'RAM';
rEmployee.sex := 'MALE';
DBMS_OUTPUT.PUT_LINE(rEmployee.Name);
DBMS_OUTPUT.PUT_LINE(rEmployee.Age);
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.