Oracle PL/SQL - Retrieving Cursor Variables with a Record Variable

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

Here, this code declares the RECORD datatype, indicating that you need a place to store a row of data consisting of two numbers.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w ww.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> declare
  2       cursor c_countEmps is
  3          select count(*) , sum(sal)
  4             from emp;
  5         type rt_testRecType is record
  6               (v_count_nr NUMBER,
  7                v_sum_nr NUMBER);
  8         r_testRec rt_testRecType;
  9  begin
 10         open c_countEmps;
 11         fetch c_countEmps into r_testRec;
 12         close c_countEmps;
 13         DBMS_OUTPUT.put_line('number of emps is:'||
 14              r_testRec.v_count_nr);
 15         DBMS_OUTPUT.put_line('sum of emp salaries is:'||
 16              r_testRec.v_sum_nr);
 17  end;
 18  /
number of emps is:4
sum of emp salaries is:13275

PL/SQL procedure successfully completed.
SQL>

Related Topic