database 如何在 Oracle 中使用枚举？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/203469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use enums in Oracle?
提问by Robert Gould
How do you use enums in Oracle using SQL only? (No PSQL)
您如何仅使用 SQL 在 Oracle 中使用枚举？（无 PSQL）
In MySQL you can do:
在 MySQL 中，您可以执行以下操作：
CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);
What would be a similar way to do this in Oracle?
在 Oracle 中执行此操作的类似方法是什么？
回答by Justin Cave
Reading a bit about the MySQL enum, I'm guessing the closest equivalent would be a simple check constraint
阅读有关MySQL enum的一些内容，我猜最接近的等价物将是一个简单的检查约束
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.
现在，如果您承认 PL/SQL 解决方案，您可以创建自定义对象类型，其中可以包含逻辑来限制它们可以保存的值集，并具有获取 ID 和获取值的方法等。
回答by giacomino
At this link you can find an alternative solution/workaround for Oracle, inspired by C language enums: http://www.petefinnigan.com/weblog/archives/00001246.htm
在此链接中，您可以找到受 C 语言枚举启发的 Oracle 替代解决方案/解决方法：http: //www.petefinnigan.com/weblog/archives/00001246.htm
Shortly put, Pete suggests to define some integer constants and to use a SUBTYPE to constrait them:
简而言之，Pete 建议定义一些整数常量并使用 SUBTYPE 来限制它们：
RED constant number(1):=1;
GREEN constant number(1):=2;
BLUE constant number(1):=3;
YELLOW constant number(1):=4;
subtype COLORS is binary_integer range 1..4;
After that you can declare variables, pass parameters and return values from functions and so on, with type COLORS.
之后，您可以声明变量、传递参数和从函数返回值等，类型为 COLORS。
回答by ezzadeen
Why not use a constraint for the column? It will do the same thing:
为什么不对列使用约束？它会做同样的事情：
ALTER TABLE x ADD CONSTRAINT size_constraint check (x_size in ('small', 'medium', 'large'))
ALTER TABLE x ADD CONSTRAINT size_constraint check (x_size in ('small', 'medium', 'large'))
