Database Interaction with PL/SQL, part 2 (Page 1 of 4 )
This article picks up where part one left off. You will learn more about how to use %ROWTYPE and %TYPE, and be introduced to TYPE, RECORD, and TABLE declarations in PL/SQL.
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.
Next: UPDATE with RETURNING clause >>
More Oracle Articles
More By Jagadish Chatarji
