Oracle PL/SQL - Update table using %ROWTYPE

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. %ROWTYPE Attribute

Description

Update table using %ROWTYPE

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w w  w . j  a  v a2  s  . c  o  m

Elapsed: 00:00:00.02
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(7499, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> declare
  2         v_emp1_rec emp%ROWTYPE;
  3         v_emp2_rec emp%ROWTYPE;
  4  begin
  5         select * into v_emp1_rec
  6            from emp
  7         where empNo=7369;
  8         select * into v_emp2_rec
  9            from emp
 10         where empNo=7499;
 11
 12         v_emp1_rec .empNo:=7499;
 13         v_emp2_rec .empNo:=7369;
 14
 15         update emp
 16            set row = v_emp1_rec
 17         where empNo = 7499;
 18         update emp
 19            set row = v_emp2_rec
 20         where empNo = 7369;
 21  end;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>

The structure of the row and the variable must be exactly the same.

The right side of the set row must contain a variable. It cannot be a subquery.

If you use a record variable in an INSERT/UPDATE statement, you cannot use any other variables in the statement.

Related Topic