Using %TYPE:
The %TYPE attribute gives the datatype of a variable or database column. Consider a variable has to be defined to be of the similar datatype as a column in a table, and then this attribute is used. This describes the exact datatype. The syntax for % type is as given below:
Variablename <tablename>.<columnname>%TYPE
Example
Refining example,
DECLARE
CURSOR c1 IS SELECT ename,job,deptno FROM emp where deptno=10;
Mname emp.ename%type;
Mjob emp.job%type;
mdno emp.deptno%type;
BEGIN
OPEN c1; LOOP
FETCH c1 INTO mname,mjob,mdno;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mname||' is a ' ||mjob||' working in '||mdno);
END LOOP;
CLOSE c1;
END;
There is no change in the output.
Using %ROWTYPE
The %ROWTYPE attribute gives a record type which represents a row in a table. The variable which is declared as this type can store a whole row of data selected from the table or fetched from a cursor. This category is used in cases where all the column names required to be retrieved from the table.
Example
This query shows all the records from the table.
DECLARE
CURSOR c1 IS SELECT * from emp;
e_rec emp%rowtype;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO e_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(e_rec.ename||' is a ' ||e_rec.job||' and earns'||e_rec.sal);
END LOOP;
CLOSE c1;
END;
The output of the program will be:
KING is a PRESIDENT and earns 5000
BLAKE is a MANAGER and earns 2850
CLARK is a MANAGER and earns 2450
JONES is a MANAGER and earns 2975
MARTIN is a SALESMAN and earns 1250
ALLEN is a SALESMAN and earns 1600
TURNER is a SALESMAN and earns 1500
JAMES is a CLERK and earns 950
WARD is a SALESMAN and earns 1250
FORD is a ANALYST and earns 3000
SMITH is a CLERK and earns 800
SCOTT is a ANALYST and earns 3000
ADAMS is a CLERK and earns 1100
MILLER is a CLERK and earns 1300
The queries which appear in the Cursor declaration can be a SUB-QUERY or a JOIN.