Anchor Data types (Using %ROWTYPE) in Oracle

I have discussed about the usage of %TYPE in my previous post. Here lets see how %ROWTYPE can be used.
The employees table structure in HR schema is

NAME	NULL?	TYPE
EMPLOYEE_ID		NUMBER(6)
FIRST_NAME		VARCHAR2(20)
LAST_NAME	NOT NULL	VARCHAR2(25)
EMAIL	NOT NULL	VARCHAR2(25)
PHONE_NO		VARCHAR2(20)
HIRE_DATE	NOT NULL	DATE
JOB_ID	NOT NULL	VARCHAR2(20)
SALARY		NUMBER(8,2)
COMMISSION_PCT		NUMBER(2,2)
MANAGER_ID		NUMBER(6)

Suppose two rows from the employees table needs to be retrieved and compared for some business requirement. The program will look like

CREATE OR REPLACE PROCEDURE pr_comp_emp ( p_employee_id_1 employees.employee_id%TYPE , p_employee_id_2 employees.employee_id%TYPE) IS --Variables to stores the FIRST record values v_employee_id_1 employees.employee_id%TYPE; v_first_name_1 employees.first_name%TYPE; v_last_name_1 employees.last_name%TYPE; v_email_1 employees.email%TYPE; v_phone_number_1 employees.phone_number%TYPE; v_hire_date_1 employees.hire_date%TYPE; v_job_id_1 employees.job_id%TYPE; v_salary_1 employees.salary%TYPE; v_commission_pct_1 employees.commission_pct%TYPE; v_manager_id_1 employees.manager_id%TYPE; v_department_id_1 employees.department_id%TYPE; --Variables to stores the SECOND record values v_employee_id_2 employees.employee_id%TYPE; v_first_name_2 employees.first_name%TYPE; v_last_name_2 employees.last_name%TYPE; v_email_2 employees.email%TYPE; v_phone_number_2 employees.phone_number%TYPE; v_hire_date_2 employees.hire_date%TYPE; v_job_id_2 employees.job_id%TYPE; v_salary_2 employees.salary%TYPE; v_commission_pct_2 employees.commission_pct%TYPE; v_manager_id_2 employees.manager_id%TYPE; v_department_id_2 employees.department_id%TYPE; BEGIN --Fetch the first record SELECT * INTO v_employee_id_1 , v_first_name_1 , v_last_name_1 , v_email_1 , v_phone_number_1 , v_hire_date_1 , v_job_id_1 , v_salary_1 , v_commission_pct_1 , v_manager_id_1 , v_department_id_1 FROM employees WHERE employee_id = p_employee_id_1; --Fetch the Second record SELECT * INTO v_employee_id_2 , v_first_name_2 , v_last_name_2 , v_email_2 , v_phone_number_2 , v_hire_date_2 , v_job_id_2 , v_salary_2 , v_commission_pct_2 , v_manager_id_2 , v_department_id_2 FROM employees WHERE employee_id = p_employee_id_1; --More statements will go here EXCEPTION WHEN OTHERS THEN --Handle the exception DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM); END;

The problems with the select statement are:

• What if the no of columns in employees table is increased or decreased?
• The same type of variables needs to be declared twice.

Lets re-write the same program again

CREATE OR REPLACE PROCEDURE pr_comp_emp ( p_employee_id_1 employees.employee_id%TYPE , p_employee_id_2 employees.employee_id%TYPE) IS --Instead of individual variables, lets declare a record type v_emp_rec_1 employees%ROWTYPE; v_emp_rec_2 employees%ROWTYPE; BEGIN --Fetch the first record SELECT * INTO v_emp_rec_1 FROM employees WHERE employee_id = p_employee_id_1; --Fetch the second record SELECT * INTO v_emp_rec_2 FROM employees WHERE employee_id = p_employee_id_2; --More statements will go here… EXCEPTION WHEN OTHERS THEN --Handle the exception DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM); END;

The above program eliminated the issues that were discussed earlier.
v_emp_rec_1 will have the same no of columns and the data type of the columns. So changing the table will effect the record variable also.#160;

Question may arise here, why should I declare a record type variables if few columns only required to be selected? When to use the record type variable? Well, its depends on developer to developer. I prefer to use a record type variable if more than 5 columns needs to be selected from a table.

Lets have look at the following program which has a select statement retrieving data from more than one table.

CREATE OR REPLACE PROCEDURE pr_comp_emp ( p_employee_id_1 employees.employee_id%TYPE , p_employee_id_2 employees.employee_id%TYPE) IS --Variables to store FIRST record v_employee_id_1 employees.employee_id%TYPE; v_first_name_1 employees.first_name%TYPE; v_last_name_1 employees.last_name%TYPE; v_salary_1 employees.salary%TYPE; v_department_id_1 departments.department_id%TYPE; v_department_name_1 departments.department_name%TYPE; --Variables to store SECOND record v_employee_id_2 employees.employee_id%TYPE; v_first_name_2 employees.first_name%TYPE; v_last_name_2 employees.last_name%TYPE; v_salary_2 employees.salary%TYPE; v_department_id_2 departments.department_id%TYPE; v_department_name_2 departments.department_name%TYPE; BEGIN SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name INTO v_employee_id_1 , v_first_name_1 , v_last_name_1 , v_salary_1 , v_department_id_1 , v_department_name_1 FROM employees e, departments d WHERE e.department_id = d.department_id AND employee_id = p_employee_id_1; SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name INTO v_employee_id_2 , v_first_name_2 , v_last_name_2 , v_salary_2 , v_department_id_2 , v_department_name_2 FROM employees e, departments d WHERE e.department_id = d.department_id AND employee_id = p_employee_id_2; --More statements will go here… EXCEPTION WHEN OTHERS THEN --Handle the exception DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM); END;

In this case how to declare a record type?

Well, a cursor with the same select statement can be declared and a variable of the cursor type can be declare as shown below

CREATE OR REPLACE PROCEDURE pr_comp_emp ( p_employee_id_1 employees.employee_id%TYPE , p_employee_id_2 employees.employee_id%TYPE) IS --Declare a cursor with the same select statement that is in use CURSOR emp_det_cur IS SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id ; --Variables to store FIRST record v_emp_det_1 emp_det_cur%ROWTYPE; --Variables to store SECOND record v_emp_det_2 emp_det_cur%ROWTYPE; BEGIN SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name INTO v_emp_det_1 FROM employees e, departments d WHERE e.department_id = d.department_id AND employee_id = p_employee_id_1; SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name INTO v_emp_det_2 FROM employees e, departments d WHERE e.department_id = d.department_id AND employee_id = p_employee_id_2; --More statements will go here… EXCEPTION WHEN OTHERS THEN --Handle the exception DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM); END;

So, by using a dummy cursor as shown, record types of the cursor type can be declared. If the same record type of needs to be declared in my programs, then its better to declare the cursor in a package body and use the type in the variable declaration.
Below is the common package containing the cursor.

CREATE OR REPLACE PACKAGE pkg_common AS CURSOR emp_det_cur IS SELECT employee_id , first_name , last_name , salary , d.department_id , d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id ; END pkg_common;

Now lets re-write the declaration section again

CREATE OR REPLACE PROCEDURE pr_comp_emp ( p_employee_id_1 employees.employee_id%TYPE , p_employee_id_2 employees.employee_id%TYPE) IS --Variables to store FIRST record v_emp_det_1 pkg_common.emp_det_cur%ROWTYPE; --Variables to store SECOND record v_emp_det_2 pkg_common.emp_det_cur%ROWTYPE; BEGIN --Code will go here NULL; END;