SQL PLUS或SQL Developer中的映射值

Question

我有一个表，其中有一列包含整数。那些整数指的是C枚举。

如果能够在SQL Plus和/或SQL Developer中将数字映射到枚举，那就太好了。

我想我需要使用column命令，但还没有弄清楚具体的方法。

例如，如果我有以下查询：

SELECT * FROM employees

我会得到这样的东西：

name	部门
john doe	2
jane doe	5

我不认为它存在于Oracle中。我们通常要做的是连接两个(规范化的(表，它们可能通过外键约束相互引用。如果我错了，并且enum确实存在，我想看看它。

因此：样本表：

SQL> create table employees as
2  select 'john doe' name, 2 department from dual union all
3  select 'jane doe', 5 from dual;
Table created.
SQL> create table departments as
2  select 2 department, 'sales' name from dual union all
3  select 5, 'support' from dual;
Table created.
SQL> select * From employees;
NAME     DEPARTMENT
-------- ----------
john doe          2
jane doe          5
SQL> select * from departments;
DEPARTMENT NAME
---------- -------
2 sales
5 support

这就是我们通常所做的：

SQL> select e.name, d.name department
2  from employees e join departments d on d.department = e.department;
NAME     DEPARTM
-------- -------
john doe sales
jane doe support

如果你很懒(不想每次都键入(，创建一个视图，然后从视图中选择：

SQL> create or replace view v_employees as
2  select e.name, d.name department
3  from employees e join departments d on d.department = e.department;
View created.
SQL> select * From v_employees;
NAME     DEPARTM
-------- -------
john doe sales
jane doe support
SQL>

我最终使用了一个不可见的虚拟列。

我创建了一个小程序，它循环遍历枚举并生成SQL:

ALTER TABLE employees ADD departmentLbl INVISIBLE AS CASE
WHEN department = 2 THEN 'sales'
WHEN department = 5 THEN 'support'
ELSE TO_CHAR(department)

我仍然需要来回切换可见性，因为SQL Developer不会显示不可见的列。