%TYPE vs %ROWTYPE: What's the Difference? (Basic 1 - 24)

 

%TYPE and %ROWTYPE are two special data types in Oracle PL/SQL that are used to declare variables and records. %TYPE declares a variable with the same data type as a specified table's column, while %ROWTYPE declares a record with the same types as found in the specified database table, view, or cursor.

%TYPE

%TYPE is used to declare a variable with the same data type as a specified table's column. This can be useful if you need to store the value of a column in a variable, or if you need to pass the value of a column to a function or procedure.

For example, the following code declares a variable v_EmpName with the same data type as the ENAME column in the EMP table:

SQL

DECLARE
  v_EmpName EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO v_EmpName FROM EMP WHERE ROWNUM = 1;
  DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/

This code will print the name of the first employee in the EMP table to the console.

%ROWTYPE

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view, or cursor. This can be useful if you need to work with multiple columns from a table, or if you need to pass a row of data to a function or procedure.

For example, the following code declares a record v_emp with the same types as the columns in the EMP table:

SQL

DECLARE
  v_emp EMP%ROWTYPE;
BEGIN
    v_emp.empno := 10;
    v_emp.ename := 'XXXXXXX';
  END;

This code can be used to insert a new row into the EMP table, or to update an existing row in the EMP table.

Advantages of using %TYPE and %ROWTYPE

One of the main advantages of using %TYPE and %ROWTYPE is that they can make your code more readable and maintainable. By using %TYPE and %ROWTYPE, you can avoid having to explicitly declare the data type of each variable or record. This can make your code easier to read and understand, and it can also make your code easier to maintain if the database schema changes.

Another advantage of using %TYPE and %ROWTYPE is that they can improve the performance of your code. When you use %TYPE and %ROWTYPE, Oracle can optimize the code by avoiding unnecessary data conversions.

Conclusion

%TYPE and %ROWTYPE are two powerful tools that can be used to simplify your PL/SQL code. By using %TYPE and %ROWTYPE, you can make your code more readable, maintainable, and performant.