sqloracle-databaseoracle-type

Retrieve objects in Oracle


I have a Oracle object called Menu which has a object type as an attribute.

menu type

Create Type Menu as Object( beer REF beertype, price float )

beertype

Create Type beertype as object( name char(20), seller char(20) )

table created using menu type

Create table Sells of Menu

table created using beertype

Create table beer of beertype

Now what I want to do is I want to write a select query which retrieves the name of the beer which has price more than 3.5.

I tried

SELECT beer.name 
FROM Sells; 

and

SELECT Sells.beer.name 
FROM Sells; 

Solution

  • Here's an example: test case first:

    SQL> create type beertype as object(
      2    name char(20),
      3    seller char(20)
      4  );
      5  /
    
    Type created.
    
    SQL> create type menu as object(
      2    beer ref beertype,
      3    price float
      4  );
      5  /
    
    Type created.
    
    SQL> create table sells of menu;
    
    Table created.
    
    SQL> create table beer of beertype;
    
    Table created.
    
    SQL> insert into beer (name, seller) values ('Heineken', 'KTC');
    
    1 row created.
    
    SQL> insert into beer (name, seller) values ('Tuborg', 'Plodine');
    
    1 row created.
    
    SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Heineken'), 10);
    
    1 row created.
    
    SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Tuborg'), 2.5);
    
    1 row created.
    

    Query which selects beer name whose price is larger than 3.5:

    SQL> select b.name, s.price
      2  from sells s join beer b on value(b) = deref(s.beer)
      3  where s.price > 3.5;
    
    NAME                      PRICE
    -------------------- ----------
    Heineken                     10
    
    SQL>
    

    Here's a nice article, if you want to read more: Using REF Values for Retrieval of Data Values