如何在Oracle中使用枚举？ [英] How to use enums in Oracle?

问题描述

如何在Oracle中仅使用SQL使用枚举？ （无PSQL）

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

在MySQL中，您可以：

In MySQL you can do:

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

在Oracle中，类似的做法是什么？

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

推荐答案

阅读有关 MySQL enum ，我猜最接近的等价是一个简单的检查约束

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.

现在，如果你承认PL / SQL解决方案，您可以创建自定义对象类型，其中可以包含用于限制其可以包含的值集合的逻辑，以及具有获取ID和获取值的方法等。

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.

这篇关于如何在Oracle中使用枚举？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！