Procedures %TYPE and %ROWTYPE Attributes Usage

Procedures %TYPE and %ROWTYPE Attributes Usage

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)


This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

Dept_number number(2);
...
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);


Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:


--------------------------------------------------------------------------------
Caution:
To execute the following, use CREATE OR REPLACE PROCEDURE...

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


PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab
WHERE Empno = Emp_number;
END;


You could call this procedure from a PL/SQL block as follows:

DECLARE
Emp_row Emp_tab%ROWTYPE; -- declare a record matching a
-- row in the Emp_tab table
BEGIN
Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno);
DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr);
DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal);
DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno);
DBMS_OUTPUT.NEW_LINE;
END;


Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
RETURN Emp_tab%ROWTYPE IS ...