Oracle PL/SQL - Assigning Record Variables

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

Description

Assigning Record Variables

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>
SQL>
SQL> declare
  2        v_emp_rec emp%ROWTYPE;
  3        v_empStart_rec emp%ROWTYPE;
  4  begin
  5        v_emp_rec.deptNo:=10;
  6        p_generateNewEmp(v_emp_rec);
  7        v_empStart_rec:=v_emp_rec; -- store original data
  8        DBMS_OUTPUT.put_line('Generated: '||
  9             v_empStart_rec.empNo||' '||v_empStart_rec.eName);
 10        p_processEmp(v_emp_rec); -- continue working
 11  end;
 12  /

SQL>

You can use direct assignment of records if:

  • If both variables are identical user-defined record data types.
  • If the source variable is defined by reference using %ROWTYPE and all the target variable fields are in the same order and of the same data type.

Related Topic