Oracle Apex 24.2中如何为人员表设置组织选择下拉菜单

1. 构建合并组织数据的查询 #

要让下拉菜单展示三个表的组织数据，先写一个联合查询，把不同类型的组织ID和名称合并，同时标注类型方便用户区分：

SELECT restaurant_id AS org_id, 
       '餐厅: ' || restaurant_name AS org_name,
       'RESTAURANT' AS org_type
FROM restaurant
UNION ALL
SELECT supplier_id AS org_id,
       '供应商: ' || supplier_name AS org_name,
       'SUPPLIER' AS org_type
FROM suppliers
UNION ALL
SELECT company_id AS org_id,
       '司机公司: ' || company_name AS org_name,
       'DRIVER_COMPANY' AS org_type
FROM driver_company
ORDER BY org_type, org_name

如果需要复用这个查询，可创建视图：

CREATE OR REPLACE VIEW vw_organizations AS
SELECT restaurant_id AS org_id, 
       '餐厅: ' || restaurant_name AS org_name,
       'RESTAURANT' AS org_type
FROM restaurant
UNION ALL
SELECT supplier_id AS org_id,
       '供应商: ' || supplier_name AS org_name,
       'SUPPLIER' AS org_type
FROM suppliers
UNION ALL
SELECT company_id AS org_id,
       '司机公司: ' || company_name AS org_name,
       'DRIVER_COMPANY' AS org_type
FROM driver_company;

2. 配置页面下拉选择项 #

假设已有人员录入表单页面，找到organization_id对应的页面项，按以下步骤设置：

• 编辑页面项，将类型改为Select List
• 在List of Values区域：
  • 设置Type为SQL Query
  • 输入查询语句（直接用联合查询或视图：SELECT org_id, org_name FROM vw_organizations ORDER BY org_type, org_name）
  • Return Value选择org_id，Display Value选择org_name
• （可选）添加合法性验证：新增一个验证，检查选中的ID是否存在于三个表中，验证SQL如下（替换PX_ORGANIZATION_ID为实际页面项名称）：

SELECT 1
FROM dual
WHERE EXISTS (
    SELECT 1 FROM restaurant WHERE restaurant_id = :PX_ORGANIZATION_ID
    UNION ALL
    SELECT 1 FROM suppliers WHERE supplier_id = :PX_ORGANIZATION_ID
    UNION ALL
    SELECT 1 FROM driver_company WHERE company_id = :PX_ORGANIZATION_ID
)

验证失败时提示“所选组织不存在，请重新选择”。

3. 保障数据合法性（可选） #

标准外键约束只能指向单表，因此可通过触发器确保organization_id的合法性：

CREATE OR REPLACE TRIGGER trg_persons_org_valid
BEFORE INSERT OR UPDATE ON persons
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM (
        SELECT 1 FROM restaurant WHERE restaurant_id = :NEW.organization_id
        UNION ALL
        SELECT 1 FROM suppliers WHERE supplier_id = :NEW.organization_id
        UNION ALL
        SELECT 1 FROM driver_company WHERE company_id = :NEW.organization_id
    );
    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '组织ID无效，请选择合法的组织');
    END IF;
END;
/

这样插入或更新人员数据时，会自动校验组织ID的有效性。

内容的提问来源于stack exchange，提问作者Phaphathi Hlaka