Database Interaction with PL/SQL, part 2

This is part two of a series of articles focusing on database interactions with Oracle PL/SQL. In the previous article (part one), we covered the basics of retrieving information from the Oracle database using the SELECT..INTO statement as well as two attributes, namely "%TYPE" and "%ROWTYPE." We concluded that article with some DML statements working within the PL/SQL program. In this article, we will examine some tips and tricks for using %ROWTYPE and %TYPE and finally concentrate a bit on TYPE, RECORD and TABLE declarations in PL/SQL.

Please note that all of the examples in this series have been tested only with Oracle 10g. I didn't really test them with all the previous versions of Oracle. I suggest you to refer the documentation of the version you are using, if any of the programs failed to execute.

%ROWTYPE with INSERT and UPDATE

We already learned that %ROWTYPE can be used to declare a variable to hold an entire row of information. But how can we use it within DML statements directly? Let me explain through a very simple example.

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := 'sample';

v_dept.loc := 'chicago';

insert into dept values v_dept;

end;

The above program illustrates that an "insert" statement can accept a variable of its own table based row type. In the above example, I declared a variable "v_dept," which can store an entire row based on the structure of "dept" table (dept%rowtype). The first three statements within the body assign new department values. The "insert" statement gets all the column values from the variable "v_dept" and inserts them into "dept" table.

The above program concentrates only on the "insert" statement with the "%rowtype" variable. But how do we work with the "update" statement using the same type of variable? The following example illustrates that.

declare

v_dept dept%rowtype;

begin

v_dept.deptno := 60;

v_dept.dname := 'sample2';

v_dept.loc := 'dallas';

update dept set ROW=v_dept where deptno=v_dept.deptno;

end;

This program is very similar to the earlier program, except for the "update" statement. If you carefully observe the "update" statement, I used a new keyword, "ROW." That performs the magic. The "ROW" can accept any variable of "%rowtype" belonging to its own table (in this case, it is "dept"). This "ROW" keyword assigns each and every column of dept with the values available in "v_dept." And of course, the rest can be understood easily.

Note that we will not be able to work with "%rowtype" with the DELETE statement (except in the WHERE condition), as the DELETE statement does not actually insert or modify rows in the respective table.