Total Pageviews

August 10, 2015

8/10/2015 09:49:00 PM

Ordering Data using the MAP method –
Map method will be used when the data of a user defined column needs to be sorted.

Instances of an object type have no predefined order
To put them in order, use a MAP method
A MAP method is a parameter less function with a scalar return type of DATE, NUMBER, VARCHAR2, or an ANSI SQL type such as CHARACTER or REAL
A MAP method maps object values into scalar values (which are easier to compare), then compares the scalar values
Example –

Step 1  -- Create the type specification –

create or replace type company as object
(city varchar2(20),
  sales number,
  MAP MEMBER FUNCTION sorting return number);

Step 2 – Create the type body in which sorting is mentioned. ---

create or replace type body company as
MAP MEMBER FUNCTION sorting return number is
begin
return SELF.sales;
end;
end;

Step 3 – Create the relational table

create table telco
 (year varchar2(30),
   details company);

 Step 4 – Insert records

insert into telco
values('1999',company('pune',900))

 insert into telco
 values('2000',company('mumbai',600))

Step 6 – Due to the use of MAP method we can now sort the Details column due to which the sales figures will get sorted.
select * from telco
order by details;