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

TYPE with RECORD declaration - Oracle

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

Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in. Those who are familiar with the C language can consider this to be a structure declaration. Let me explain this to you in detail.

Let us consider a table of about 20 columns.  I always need to work with only seven of those columns. If I use %ROWTYPE, I get all 20 values unnecessarily. At the same time, my program will be bit clumsy if I use seven %TYPE declarations. A better way to solve this solution is by defining my own data type, which can hold seven values. Then I declare the variables of my new data type and work with them. The following example illustrates this.

declare

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

v_emp t_emprec;

v_empno emp.empno%type := &empno;

begin

select ename,sal,job into v_emp

from emp where empno = v_empno;

dbms_output.put_line ('Name : ' || v_emp.name);

dbms_output.put_line ('Salary : ' || v_emp.salary);

dbms_output.put_line ('Job : ' || v_emp.job);

end;

Let us examine the above program part by part. First, consider the following:

TYPE t_emprec IS RECORD

(

name emp.ename%type,

salary emp.sal%type,

job emp.job%type

);

The above defines a new data type named "t_emprec" (just like %ROWTYPE with limited specified fields) which can hold three fields, namely "name," "salary" and "job."

v_emp t_emprec;

The above statement declares a variable "v_emp" based on the datatype "t_emprec." This means that "v_emp" internally contains the fields "name," "salary" and "job."

select ename,sal,job into v_emp

from emp where empno = v_empno;

The above statement places the values of "ename," "sal" and "job" into fields "name," "salary" and "job" of the variable "v_emp" respectively.

dbms_output.put_line ('Name : ' || v_emp.name);

dbms_output.put_line ('Salary : ' || v_emp.salary);

dbms_output.put_line ('Job : ' || v_emp.job);

And the above displays all the values available fields of the variable "v_emp."



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