Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
717 views
in Technique[技术] by (71.8m points)

database - How to use enums in Oracle?

How do you use enums in Oracle using SQL only? (No PSQL)

In MySQL you can do:

CREATE TABLE sizes (
   name ENUM('small', 'medium', 'large')
);

What would be a similar way to do this in Oracle?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Reading a bit about the MySQL enum, I'm guessing the closest equivalent would be a simple check constraint

CREATE TABLE sizes (
  name VARCHAR2(10) CHECK( name IN ('small','medium','large') )
);

but that doesn't allow you to reference the value by the index. A more complicated foreign key relationship would also be possible

CREATE TABLE valid_names (
  name_id   NUMBER PRIMARY KEY,
  name_str  VARCHAR2(10)
);

INSERT INTO valid_sizes VALUES( 1, 'small' );
INSERT INTO valid_sizes VALUES( 2, 'medium' );
INSERT INTO valid_sizes VALUES( 3, 'large' );

CREATE TABLE sizes (
  name_id NUMBER REFERENCES valid_names( name_id )
);

CREATE VIEW vw_sizes
  AS 
  SELECT a.name_id name, <<other columns from the sizes table>>
    FROM valid_sizes a,
         sizes       b
   WHERE a.name_id = b.name_id

As long as you operate through the view, it would seem that your could replicate the functionality reasonably well.

Now, if you admit PL/SQL solutions, you can create custom object types that could include logic to limit the set of values they can hold and to have methods to get the IDs and to get the values, etc.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...