Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, part 1

%ROWTYPE attribute - Oracle

This first article in a series focusing on database interactions with Oracle PL/SQL serves as a good introduction to the topic. It mainly focuses on the basics of retrieving information from the Oracle database using the SELECT..INTO statement, as well as two attributes, namely "%TYPE" and "%ROWTYPE." Readers should have some knowledge of RDBMS, Oracle SQL, and some very fundamental grounding in PL/SQL.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 1
  2. SELECT…INTO Statement
  3. %TYPE attribute
  4. %ROWTYPE attribute
  5. Using Other DML Commands
By: Jagadish Chatarji
Rating: starstarstarstarstar / 36
May 24, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

What if we want more and more columns of values to be retrieved and pushed into variables? Consider a table of fifteen columns. Is it necessary to declare all fifteen variables to retrieve those values? This is where %ROWTYPE comes in. It can be used to declare a variable to store an entire row from a specified table. Let us go through a simple example again.

Declare
     v_empRec emp%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp
     where empno=v_empno;
     dbms_output.put_line('Name: '||v_emprec.ename);
     dbms_output.put_line('Salary: '||v_emprec.sal);
     dbms_output.put_line('Job: '||v_emprec.job);
end;

In the above example, we declared a variable "v_empRec," which can hold an entire row from the table "emp" (which is what "emp%rowtype" means). Within the body, we fetch an entire row (all of the columns) of information (using "*" in the "select" statement) and place it in "v_emprec." To fetch the value of each and every column, we specify the column name with the "%rowtype" variable separated with a dot (watch the dbms_output statements).

Note that it is not compulsory to use all of the columns in our program. This concept of "%rowtype" is not suitable for retrieving information based on joins, but indeed it helps a lot by excluding unnecessary variable declarations. Let us conclude this section with one more example.

Declare
     v_empRec emp%rowtype;
     v_deptRec dept%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp where empno=v_empno;
     Select * into v_deptRec
     from dept where deptno=v_empRec.deptno;
     dbms_output.put_line('Name: '||v_emprec.ename);
     dbms_output.put_line('Salary: '||v_emprec.sal);
     dbms_output.put_line('Job: '||v_emprec.job);
     dbms_output.put_line('Department: '||v_deptrec.dname);
     dbms_output.put_line('Location: '||v_deptrec.loc);
end;

Since this is not very complicated, I leave it to the readers to understand the above program.



 
 
>>> 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: