Home arrow Oracle arrow Database Interaction with PL/SQL, part 2

Database Interaction with PL/SQL, part 2

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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 2
  2. UPDATE with RETURNING clause
  3. TYPE with RECORD declaration
  4. Accessing more than one row in PL/SQL (TYPE with TABLE)
By: Jagadish Chatarji
Rating: starstarstarstarstar / 20
May 31, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: