sql - 如何在ORACLE中创建函数来计算列中的值？

Question

 ID_ROLE      NAME_OF_ROLE    
----------   -------------   
  1           Manager        
  2           Director        
  4           Supervisor          
  8           Secretary
  16          Controlor
  ...
  2048        Boss

这是我的 ORACLE 表。如您所见，列 ID_ROLE 是数字 2 的幂。第一个角色是 2 的 0 次方，第二个角色是 2 的 1 次方，最后一个角色是 2 的 11 次方，等等。这很容易有人只有一个作用，我可以使 CASE 或 DECODE 功能。例如，如果 ID_ROLE 为 2，则此人为“Director”。但问题是当某人有两个或多个角色时。如果 ID_ROLE 为 24(例如)，则表示此人具有“秘书”和“控制员”角色 (8 + 16)，并且应该返回类似“秘书/控制员”的文本。

知道怎么做吗？

Answer 1

解决方法是

• BITAND执行按位与操作；和
• LISTAGG加入由斜杠分隔的角色名称(从 11g 开始可用)

SELECT LISTAGG(NAME_OF_ROLE, ' / ')
WITHIN GROUP (ORDER BY ID_ROLE) "ROLE_NAMES"
FROM ROLES
WHERE BITAND(ID_ROLE, 24) = ID_ROLE;

这导致输出:

+------------------------+
| ROLE_NAMES             |
+------------------------+
| Secretary / Controller |
+------------------------+

我使用的 DDL:

CREATE TABLE  "ROLES" (
    "ID_ROLE" NUMBER NOT NULL ENABLE, 
    "NAME_OF_ROLE" VARCHAR2(4000), 
     CONSTRAINT "ROLES_PK" PRIMARY KEY ("ID_ROLE") USING INDEX ENABLE
);

还有我的测试数据:

INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Manager', 1);
INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Director', 2);
INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Supervisor', 4);
INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Secretary', 8);
INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Controller', 16);
INSERT INTO ROLES (NAME_OF_ROLE, ID_ROLE) VALUES ('Boss', 2048);