如何在 Oracle 中使用枚举?

2021-12-29 00:00:00 database enums oracle

如何在仅使用 SQL 的 Oracle 中使用枚举?(无 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 枚举，我猜最接近的等价物是一个简单的检查约束

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.

相关文章