Oracle PL/SQL - Record Implicit Declaration

Introduction

An implicit record declaration uses an existing table, view, or cursor as a reference.

You can reference the existing record type of the employee, emp%ROWTYPE.

Using this approach, you're always in sync with the database definitions.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w  w  w  .j a v a2s .com

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       v_emp_rec emp%ROWTYPE;
  3  begin
  4        select * into v_emp_rec
  5          from emp
  6          where empNo=7369;
  7        DBMS_OUTPUT.put_line('Emp:'||v_emp_rec.empNo ||
  8               ''||v_emp_rec.eName ||'('||v_emp_rec.deptNo ||')');
  9  end;
 10  /
Emp:7369KING(10)

PL/SQL procedure successfully completed.
SQL>

Related Topic