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.

  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



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.

     v_empRec emp%rowtype;
     v_empno emp.empno%type := &empno;
     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);

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.

     v_empRec emp%rowtype;
     v_deptRec dept%rowtype;
     v_empno emp.empno%type := &empno;
     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);

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