jcon.no: Oracle blog

One feature I very seldom see in use is the possibility to insert and update data using Oracle records. A record is a composite datatype having the capability of holding more than one single value all with possible different datatypes. If the number of attributes within a record is the same as in a table (or cursor), and the datatypes are the same, a record can in an easy way get populated by a row from a table (or cursor).

SQL> create table empc as select * from emp where 1=2;

Tabell opprettet.

SQL> select * from empc;

No rows selected

SQL> declare
   l_emp emp%rowtype;
begin
   for rec in (select * from emp)
   loop
      insert into empc values rec;
   end loop;
   commit;
end;
10 /

PL/SQL procedure successfully completed.

SQL> select * from empc;

     EMPNO ENAME      JOB	       MGR HIREDATE	     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17.12.1980	   10000		    20
      7499 ALLEN      SALESMAN	      7698 20.02.1981	    1600	300	    30
      7521 WARD       SALESMAN	      7698 22.02.1981	    1250	500	    30
      7566 JONES      MANAGER	      7839 02.04.1981	    2975		    20
      7654 MARTIN     SALESMAN	      7698 28.09.1981	    1250       1400	    30
      7698 BLAKE      MANAGER	      7839 01.05.1981	    2850		    30
      7782 CLARK      MANAGER	      7839 09.06.1981	    2450		    10
      7788 SCOTT      ANALYST	      7566 19.04.1987	    3000		    20
      7844 TURNER     SALESMAN	      7698 08.09.1981	    1500	  0	    30
      7876 ADAMS      CLERK	      7788 23.05.1987	    1100		    20
      7900 JAMES      CLERK	      7698 03.12.1981	     950		    30
      7902 FORD       ANALYST	      7566 03.12.1981	    3000		    20
      7934 MILLER     CLERK	      7782 23.01.1982	    1300		    10
      1000 TERJE MJ?S CLERK	      7788 19.03.2012	     100		    20

14 rows selected.       
   

Above we used the record as basis for setting the values in the INSERT statement. This was done by leaving out the parantheses after the VALUES clause and referencing the record name:

INSERT INTO emp VALUES l_emp;

When doing an update we can also use a record for setting the values bye using the SET ROW clause:

SQL> declare
   l_emp emp%rowtype;
begin
   for rec in (select * from emp)
   loop
      rec.sal:=rec.sal*1.1;
      UPDATE emp SET ROW = rec WHERE empno=rec.empno;
   end loop;
   commit;
end;
10 /

Note! This update syntax should be used with care!
The SET ROW syntax updates the value for every column in the table, even for the columns not getting a new value (also your primary key). This’ll definitly increase the amount of undo and redo generated in your system.