Re: Is there a work-around to implement enum types?
Date: 2000/05/02
Message-ID: <8emhkk$qjn$1_at_nnrp1.deja.com>#1/1
In article <390e2c8f$0$224_at_nntp1.ba.best.com>,
"Willy Chua" <willy_at_navin.com> wrote:
> Hi,
>
> Is there a solution for ORACLE to implement an enum types?
>
> i.e.
>
> create table enum_table
> (
> enum_id number(3),
> enum_text char(10),
> primary key ( enum_id )
> );
>
> create table other_table
> (
> some_field char(10),
> enum_id number(3)
> );
>
> create view new_view
> some_field,
> enum_text
> as select some_field, enum_text
> from other_table, enum_table
> where other_field.enum_id = enum_table.enum_id ;
>
> I would like to be do the following:
>
> select some_field, enum_text from new_view ;
>
> 'field1', 'enum_text1'
>
> and also
>
> insert into new_view
> ( some_field, enum_text )
> values ( 'new field 2', 'enum text1' );
>
> Thanks in Advance,
>
> -Willy
>
>
Yes, it could look something like the following. A couple of notes:
o suggest you rethink using CHAR. Use VARCHAR or VARCHAR2 instead.
o make sure to unique the enum_text in the enum_table (that'll index it for you as well)
o use a sequence to create enum_ids -- non-blocking, fast.
o use a foreign key to ensure integrity between the enum table and the other table
o I've set this up to auto create enum entries, you might not want that behaviour....
ops$tkyte_at_8i> create table enum_table 2 (
3 enum_id number(3), 4 enum_text varchar2(25) UNIQUE, 5 primary key ( enum_id )
6 );
Table created.
ops$tkyte_at_8i> create table other_table 2 (
3 some_field varchar2(25), 4 enum_id number(3) references enum_table5 );
Table created.
ops$tkyte_at_8i> create view new_view
2 as
3 select some_field, enum_text
4 from other_table, enum_table
5 where other_table.enum_id = enum_table.enum_id;
View created.
ops$tkyte_at_8i> create sequence ENUM_SEQ; Sequence created.
ops$tkyte_at_8i> create or replace trigger new_view_trigger
2 instead of insert on new_view
3 for each row
4 begin
5 insert into other_table (some_field, enum_id) 6 select :new.some_field, enum_id 7 from enum_table 8 where enum_text = :new.enum_text; 9 10 if ( sql%rowcount = 0 ) 11 then 12 insert into enum_table ( enum_id, enum_text ) 13 values ( ENUM_SEQ.nextval, :new.enum_text ); 14 15 insert into other_table (some_field, enum_id) 16 select :new.some_field, enum_id 17 from enum_table 18 where enum_text = :new.enum_text; 19 end if;
20 end;
21 /
Trigger created.
ops$tkyte_at_8i> insert into new_view
2 ( some_field, enum_text )
3 values ( 'new field 1', 'enum text1' );
1 row created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select some_field, enum_text from new_view ;
SOME_FIELD ENUM_TEXT ------------------------- ------------------------- new field 1 enum text1 ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into new_view
2 ( some_field, enum_text )
3 values ( 'new field 2', 'enum text1' );
1 row created.
ops$tkyte_at_8i> ops$tkyte_at_8i> insert into new_view 2 ( some_field, enum_text )
3 values ( 'new field 3', 'enum text2' );
1 row created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select some_field, enum_text from new_view ;
SOME_FIELD ENUM_TEXT ------------------------- ------------------------- new field 1 enum text1 new field 2 enum text1 new field 3 enum text2
ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from enum_table;
ENUM_ID ENUM_TEXT
---------- -------------------------
1 enum text1
2 enum text2
ops$tkyte_at_8i> select * from other_table;
SOME_FIELD ENUM_ID ------------------------- ---------- new field 1 1 new field 2 1 new field 3 2 -- Thomas Kyte tkyte_at_us.oracle.comOracle Service Industries
http://osi.oracle.com/~tkyte/index.html
-- Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue May 02 2000 - 00:00:00 CEST