Using %TYPE, %ROWTYPE, & %RECORD Attributes

 The %TYPE attribute lets you set a constant variable, field and parameter of the same data type declared previously. Changing the referencing item changes automatically changes the declarations of variable. Item declared with %TYPE always inherits the data type from the referenced item.

%TYPE Example:

DECLARE
      min_salary INTEGER NOT NULL := 1;
      max_salary INTEGER NULL;
      avg_salary min_salary%TYPE := min_salary;
      -- Not NUll requires a variable assignment.
      base_salary max_salary%TYPE;             
     -- NULL, no variable assignment is needed
BEGIN
      avg_salary := 500000;
      base_salary := 42000;
      dbms_output.put_line(avg_salary);
      dbms_output.put_line(base_salary);
END;
/

Failure to assign a variable to avg_salary will result into ORA-06550 error, therefore we have
avg_salary min_salary%TYPE := min_salary; to above PL/SQL block.

Error Report:

Error report -
ORA-06550: line 4, column 13:
PLS-00218: a variable declared NOT NULL must have an initialization 
assignment
06550. 00000 - "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

To demo the %TYPE attribute use within a block that reference to table column, we have an employee table with 16 records and a stored procedure to explain %TYPE attribute uses. %TYPE is also used when declaring variables that refer to database table column. Before using this attribute, you need to know the datatype of each columns and the restriction. Using %TYPE is the best way to declare a variable and don’t have to worry about the change in column datatype at table. %TYPE updates the reference items automatically.

Stop hard coding the variable datatype while using the table column in PL/SQL code. This makes the code hard to maintain and breaks the application when the underlining table column changes. The referencing items do not inherit column constraints or default values from database column. The variable assignment should be defined within PL/SQL block.

SYNTAX:

table_name.column_name%TYPE;

Demo Employee Table DDL:

CREATE TABLE baniya.employee
 (
  EMPNO  NUMBER(4,0) PRIMARY KEY,
  ENAME  VARCHAR2(10),
  JOB  VARCHAR2(9),
  MGR  NUMBER(4,0),
  HIREDATE   DATE,
  SAL  NUMBER(7,2),
  COMM  NUMBER(7,2),
  DEPTNO NUMBER(2,0) 
   );

Employee Search Procedure that uses %TYPE:

CREATE OR REPLACE PROCEDURE baniya.employee_search(
 empno_in IN employee.empno%TYPE)
IS
 l_empno employee.empno%TYPE;
 l_ename employee.ename%TYPE;
 l_job  employee.job%TYPE;
 l_deptno employee.deptno%TYPE;
BEGIN
 SELECT  empno,
   ename,
   job,
   deptno
 INTO   l_empno,
   l_ename,
   l_job,
   l_deptno
 FROM baniya.employee
 WHERE empno = empno_in;
END;
/

%ROWTYPE attribute lets you declare a record that represents a row in a table or a view. The record has a same field with same name and data type. The change in reference table structure does not impart the code that is declaring those columns.

Employee Search Procedure that uses %ROWTYPE:

CREATE OR REPLACE PROCEDURE baniya.employee_search_rowtype(
  empno_in IN employee.empno%TYPE)
IS
 loc_employee employee%ROWTYPE;
BEGIN
 SELECT  
  empno,
  ename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno
 INTO  loc_employee
 FROM baniya.employee
 WHERE empno = empno_in;
END;
/

Always remember to include all the columns on the SELECT statement; you cannot miss a single column. Missing single column will result into ORA-00913 error.

Errors: check compiler log
Errors for PROCEDURE BANIYA.EMPLOYEE_SEARCH_ROWTYPE:

LINE/COL ERROR

-------- ----------------------------------

6/2      PL/SQL: SQL Statement ignored

15/2     PL/SQL: ORA-00913: too many values

The example below is an alternative to above example. The code here looks clean, short and more manageable. What do you think? Which do you like it better?

Employee Search Procedure that uses %ROWTYPE:

CREATE OR REPLACE PROCEDURE baniya.employee_search_rowtype(
empno_in IN employee.empno%TYPE)
IS
 loc_employee employee%ROWTYPE;
BEGIN
 SELECT  *
 INTO  loc_employee
 FROM baniya.employee
 WHERE empno = empno_in;
END;
/

Why select everything from a table when you just need few columns? Well, there are times you only need to work with few columns out of a monster table and the example above may not be your good choice. Using CURSOR is the way to go. The example below clearly solves your problem by using few columns from employee table.

Cursor that uses %ROWTYPE:

DECLARE
 CURSOR employee_data_cur
 IS
  SELECT empno, ename, job, hiredate FROM baniya.employee;
  l_employee_data employee_data_cur%ROWTYPE;
BEGIN
 OPEN employee_data_cur;
 LOOP
  FETCH employee_data_cur INTO l_employee_data;
  EXIT WHEN employee_data_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(l_employee_data.empno 
  || ' '||l_employee_data.ename);
 END LOOP;
 CLOSE employee_data_cur;
END;
/

%RECORD: We have covered %TYPE, and ROWTYPE declaration on a table, or a cursor. You can declare your own user-defined record types by using the TYPE RECORD. I have hardly used this features during my decade long IT career but I have used it as part of school assignment.  User Defined Records are mostly used when dealing with sets of variables or data .

DECLARE
 l_student_id1 NUMBER;
 l_fname1  VARCHAR(30);
 l_name1  VARCHAR(30);
 l_phone1  VARCHAR(10);

 l_student_id2 NUMBER;
 l_fname2  VARCHAR(30);
 l_name2  VARCHAR(30);
 l_phone2  VARCHAR(10);

 l_student_id3 NUMBER;
 l_fname3  VARCHAR(30);
 l_name3  VARCHAR(30);
 l_phone3  VARCHAR(10);
BEGIN
 NULL;
END;
/

Instead of declaring variable for each student, we create our own Record type and do something like this:

DECLARE 
 TYPE student_info_rt IS RECORD
 (
  l_student_id NUMBER,
  l_fname  VARCHAR(30),
  l_name  VARCHAR(30),
  l_phone  VARCHAR(10)
 );
 
 l_student1 student_info_rt;
 l_student2 student_info_rt;
 l_student3 student_info_rt;
 
BEGIN
 NULL;
END;
/

How RECORDS are used in practice today? The first example is a traditional way of doing things without declaring RECORD. The next example uses RECORD and makes the code short, readable and maintainable.

Traditional Coding Example:

set serveroutput on;

DECLARE
 l_student_id1 NUMBER;
 l_fname1  VARCHAR2(30);
 l_lname1  VARCHAR2(30);
 l_phone1  VARCHAR2(10);

 l_student_id2 NUMBER;
 l_fname2  VARCHAR2(30);
 l_lname2  VARCHAR2(30);
 l_phone2  VARCHAR2(10);
 
 l_student_id3 NUMBER;
 l_fname3  VARCHAR2(30);
 l_lname3  VARCHAR2(30);
 l_phone3  VARCHAR2(10);

BEGIN
 l_student_id1  := 4026371234;
 l_fname1  := 'John';
 l_lname1  := 'Doe';
 l_phone1  := 4026313214;
 
 l_student_id2  := 4036371234;
 l_fname2  := 'Donald';
 l_lname2  := 'King';
 l_phone2  := 5731236547;
 
 l_student_id3  := 4101231231;
 l_fname3  := 'Rajesh';
 l_lname3  := 'Patel';
 l_phone3  := 4101234567;
 
 --Student Info 1
 dbms_output.put_line( 'Student ID: ' || l_student_id1);
 dbms_output.put_line( 'First Name: ' || l_fname1);
 dbms_output.put_line( 'Last Name: ' || l_lname1);
 dbms_output.put_line( 'l_phone1: ' || l_phone1);

 --Student Info 1
 dbms_output.put_line( 'Student ID: ' || l_student_id2);
 dbms_output.put_line( 'First Name: ' || l_fname2);
 dbms_output.put_line( 'Last Name: ' || l_lname2);
 dbms_output.put_line( 'l_phone1: ' || l_phone2);
 
 --Student Info 1
 dbms_output.put_line( 'Student ID: ' || l_student_id3);
 dbms_output.put_line( 'First Name: ' || l_fname3);
 dbms_output.put_line( 'Last Name: ' || l_lname3);
 dbms_output.put_line( 'l_phone1: ' || l_phone3); 
END;
/

Record Example: The above example is converted to use RECORD. This code is short, readable and manageable after using RECORD.

DECLARE
 TYPE student_info_rt IS RECORD (
  student_id NUMBER,
  fname      VARCHAR(30),
  lname      VARCHAR(30),
  phone      VARCHAR(10));
 
 student1 student_info_rt;
 student2 student_info_rt;
 student3 student_info_rt;
BEGIN
 student1.student_id := 4026371234;
 student2.fname  := 'John';
 student1.lname  := 'Doe';
 student1.phone  := 4026313214;
 
 student2.student_id := 4036371234;
 student2.fname  := 'Donald';
 student2.lname  := 'King';
 student2.phone  := 5731236547;
 
 student3.student_id := 4101231231;
 student3.fname  := 'Rajesh';
 student3.lname  := 'Patel';
 student3.phone  := 4101234567;
 
 /*you can print these records on whatever way you want
 either use simple dbms_output.put_line package or
 the subprogram */ 
END;
/

Use of RECORD makes the code readable and maintainable. %TYPE and %ROWTYPE makes the code unbreakable when the column datatype changes. Where possible use %TYPE or %ROWTYPE. Use of these attributes is one of the best practice of PL/SQL programming.