Oracle PL/SQL - Using Record Type

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

Record stores a whole set of variables as one entity in a single variable.

A record is a group of related data items stored in attributes.

Each data item has its own name and data type.

A record type can be defined either explicitly or implicitly.

An explicit declaration means that you first define your own data type and then create a variable of that type.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w w w  .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(7369, '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> declare
  2        type emp_ty is record (emp_tx VARCHAR2(256),
  3                               deptNo emp.deptNo%TYPE);
  4        v_emp_rty emp_ty;
  5  begin
  6        select empNo||' '||eName,  deptNo into v_emp_rty
  7        from emp
  8        where empNo=7369;
  9        DBMS_OUTPUT.put_line('Emp:'||v_emp_rty.emp_tx ||'('||v_emp_rty.deptno ||')');
 10  end;
 11  /
Emp:7369 KING(10)

PL/SQL procedure successfully completed.
SQL>

Related Topic