Defining a Record Type for a Cursor by Using %ROWTYPE
SQL> SQL> drop table emp; Table dropped.-- from www .j a v a 2 s .c o m SQL> create table emp( 2 empno number(4,0), 3 ename varchar2(10), 4 job varchar2(9), 5 mgr number(4,0), 6 hiredate date, 7 sal number(7,2), 8 comm number(7,2), 9 deptno number(2,0) 10 ); Table created. SQL> SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10); SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30); SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10); SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20); SQL> SQL> SQL> declare 2 cursor c_countEmps is 3 select count(*) count_nr, sum(sal) sum_nr 4 from emp; 5 r_testRec c_countEmps%ROWTYPE; 6 begin 7 open c_countEmps; 8 fetch c_countEmps into r_testRec; 9 close c_countEmps; 10 DBMS_OUTPUT.put_line('number of emps is:'||r_testRec.count_nr); 11 DBMS_OUTPUT.put_line('sum of emp salaries is:'||r_testRec.sum_nr); 12 end; 13 / number of emps is:4 sum of emp salaries is:13275 PL/SQL procedure successfully completed. SQL>