|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
August 16, 2005: On storing objects in tables
In this article, I want to demonstrate how to achieve a parent-child relationship with PL/SQL objects stored in tables.
First, I create an object that will act as the parent:
create type number_typ as object ( num number(2), stars varchar2(20), constructor function number_typ(n in number) return self as result ); / create type body number_typ as constructor function number_typ(n in number) return self as result is begin num := n; stars := lpad('*', n, '*'); return; end number_typ; end; / show errors
The next object will act as the children. That is, one number can have multiple translations. This is achieved through the ref modifier:
create type translation_typ as object ( trs varchar2(20), lang char(2), num_ref ref number_typ ); /
I also create two seperate tables, one for each of my types:
The following function will add a number's translation to the number. If the number is not yet stored in the table, it will first insert the number:
create procedure insert_number (p_num in number, p_trans in varchar2, p_lang in char) as v_number ref number_typ; begin begin select ref(n) into v_number from number_tab n where num = p_num; exception when no_data_found then insert into number_tab n values (number_typ(p_num)) returning ref(n) into v_number; end; insert into translation_tab values (p_trans, p_lang, v_number); end; /
The numbers and its translations are filled using the procedure:
Finally, I can show the real cool feature about objects stored in tables.
I clearly select across two tables: translation_tab and number_tab. However, the
select t.num_ref.num, t.num_ref.stars, t.trs, t.lang from translation_tab t where t.num_ref.num between 2 and 3;
Here's the output:
NUM_REF.NUM TRS LA ----------- -------------------- -- 2 two en 3 trois fr 3 drei de 2 deux fr
More on Oracle
This is an on Oracle article. The most current articles of this series can be found here.