Benefits of %TYPE and %ROWTYPE in Oracle PL/SQL Anonymous Block #Diksha Oracle Training Centre

 Benefits of  %TYPE and %ROWTYPE in Oracle PL/SQL Anonymous Block.

 

Hello Everyone,

Today I will discuss about %TYPE and %ROWTYPE in Oracle. In my previous blog I already discussed about uses of cursors in Anonymous PL/SQL block. I suggest you to please go through below blog for your reference so that you can easily understand %TYPE and %ROWTYPE in PL/SQL anonymous block.

https://dikshaoracletraining.blogspot.com/2020/10/cursor-and-types-of-cursor-in-oracle.html

Let’s Start with  both attribute  %TYPE and %ROWTYPE then we will write some Anonymous PL/SQL block  by using %TYPE and % ROWTYPE and see its advantage.

 %TYPE : %TYPE  is mainly used to defined the data type of variable as the column name data type specified for a table. If table column has data type varchar2 (50) then %type variable automatically take the data type that is varchar2 (50) no need to define data type while writing PL/SQL block as we simply want to know the table name which can be used in both Anonymous block and named block and for column we don’t need to worry because we can take multiple columns of different tables by simply using %TYPE.  From %TYPE we get the benefits that we don’t need to see the data type of each table and for any column we simply use %TYPE so that it will automatically take the data type from corresponding table.

So for programmer it’s very easy to take automatically data type from any tables that we need to use in procedure and it will save the time as well as programs looks in structured way as we are not defining data types in variables.

Syntax for defining %TYPE for any variable:

v_name Employee.Name%TYPE;

Here variable v_name automatically takes the data type of name from employee table . In previous blog we mentioned that data type should be match from table column when we declare any variable in Anonymous or Named block. For this we need to check the data type of table then same data type we use in variable like v_name varchar2(50) which is lengthy process , so now as we know %type automatically takes the data type for any column in tables so we can use %TYPE for any table columns for variable declaration.

Note: We can use %TYPE in Anonymous block as well as in Named block such as stored procedures, functions packages etc

Example of Anonymous Block by using %TYPE :

DECLARE

v_Name  employee.name%TYPE;

v_address employee.address%TYPE;

v_salary employee.salary%TYPE;

BEGIN

SELECT Name,address,salary INTO v_Name,v_address,v_salary

FROM Employee

WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE('Below is the name ,address and salary for employee from employee table');

DBMS_OUTPUT.PUT_LINE(v_name||' '||v_address||' '||v_salary);

END;

/

Here we can see the benefits of using %TYPE as we take two columns name and address from employee table but not mentioned data type in variable as %TYPE automatically takes the data type of both columns. We can also take different columns from other tables as well if we want to make complex query and required data types from multiple table.

In Above procedure we not used any loop so it will give only 1 record that’s why we use ROWNUM.

Note: We can use loop to display multiple records by using loop.

Below is the output of above program.

 

Another Program using  Explicit Cursor and %TYPE.

Example:

Declare

    CURSOR C1 IS SELECT name, address , salary FROM employee;

    v_name          employee.name%TYPE;

    v_address       employee.address%TYPE;

    v_salary        employee.salary%TYPE;

BEGIN

    OPEN C1;

    LOOP

        FETCH C1 INTO v_name,v_address,v_salary;

        EXIT WHEN C1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE( v_name ||' '|| v_address||' '||v_salary);

    END LOOP;

    CLOSE C1;

END;

/ 

Here we will get multiple outputs as we use loop in above program. Below is the output of above program.

 

 

%ROWTYPE: %ROWTYPE is dynamic as it will take the data types of all the columns in a table not like %TYPE where we need to mention it for several columns. In other word it is used in cursors to declare a single variable to contain a single record from the result set of a cursor or table without needing to specify individual variables. So benefits of %ROWTPE we can have data types of the entire column for any tables.

Syntax for defining %ROWTPE is as below :

v_employee employee%ROWTYPE;

Here we will have the data types of all the columns present in Employee Table in v_employee variable . No need to define data type of individual column on variable as %ROWTPE is dynamic and it will take all the columns data type from employee table.

Example of  PL/SQL Anonymous Block by using %ROWTYPE.

 

DECLARE

v_employee employee%ROWTYPE;

BEGIN

SELECT name,address,SALARY

 INTO v_employee.name,v_employee.address,v_employee.salary

FROM Employee

WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE('Employee details by using %ROWTYPE');

DBMS_OUTPUT.PUT_LINE(v_employee.name||' '||v_employee.address ||' '||v_employee.salary);

END;

/ 

Here we see we define only 1 variable by using %ROWTYPE but it has all the columns data type from employee table , I used 3 columns but we can use multiple columns to fetch and display the record by using %ROWTYPE. As we haven’t used any loop so it will give only 1 record that’s why in select statement we use ROWNUM=1 .

Below is the output of above Program:

 

Let’s create Anonymous PL/SQL block by using explicit Cursor with %ROWTYPE to display multiple records.

DECLARE

    CURSOR C1 IS SELECT name, address,salary FROM employee;

    v_employee           C1%ROWTYPE;

BEGIN

    OPEN C1;

    LOOP

        FETCH C1 INTO v_employee;

        EXIT WHEN C1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE( v_employee.name ||' '||v_employee.address || ' '||v_employee.salary);

    END LOOP;

    CLOSE C1;

END;

/

Here we will get multiple records as we used for cursor loop, Here we see v_employee  is %ROWTYPE and through cursor we fetch the data in v_employee variable and output we can get name  address and salary by using v_employee variable which uses %ROWTYPE that is dynamic and can hold multiple columns from tables as well as cursor.

Below is the output of above Program.

Hope you have clear picture about %TYPE and %ROWTYPE uses and benefits as we seen in the PL/SQL Anonymous block  via using cursor or plain SQL. Also we can use %TYPE and %ROWTYPE in Named block such as functions, packages, procedures. Once I start topic Named block in PL/SQL I will use both attributes for procedures , functions etc. Please try to practice and write some complex PL/SQL Anonymous block by using above methods and let me know if you have any doubts.

Please go through this blog carefully as this topic is important and in interview some questions asked from this topic as well. Also use this attribute as it can be used in both PL/SQL Anonymous as well as Named block such as Procedure , Functions, Packages etc.

Thanks.