Oracle APEX - 具有多个链接的查找表 [英] Oracle APEX - Lookup Table with several links

问题描述

我有一个与查找表创建相关的问题.

I have a question related to the lookup table creation.

假设这种情况:我有几个与世界人口相关的表格，其中包含不同的信息，例如 TOTAL、FEMALE、MALE、OVER_65、UNDER_15.这些都是不同的表.每个表都有一个国家名称作为一列.我可以从基于 1 个表的国家/地区名称创建一个查找表，例如 TOTAL.

Assume this scenario: I have several tables related to the World Populations with different info, such as TOTAL, FEMALE, MALE, OVER_65, UNDER_15. These are all different tables. Every table has the country name as a column. I can create a lookup table from the country name based on 1 table, like TOTAL.

我怎样才能对其他表执行相同的操作以映射新的查找表?我无法重新创建，因为对象已经存在.数据是一样的.我想根据创建的查找表使用其他表中的国家/地区 ID 更新国家/地区名称列.

How can I also implement same action to other tables to map the new lookup table? I can't recreate, because the object is already there. The data is same. I want to update the country name column with the country id in other tables based on the created look up table.

我也在寻找答案.如果我在这段时间内找到，我会发布它.

I am also searching for the answer. If i find during this time, i will post it.

我还创建了解释我的问题的 APEX 屏幕截图.

I have also created APEX screenshots that explains my question.

推荐答案

当您在 SQL Workshop 中创建查找表时，APEX 会生成多个 DDL 和 DML 语句来完成这项工作.在向导的最后一步中，您应该能够展开底部的 SQL 区域以查看代码.不幸的是，它的格式不是很好，但清理起来并不难.

When you create a lookup table in the SQL Workshop, APEX generates several DDL and DML statements to do the job. In the last step of the wizard, you should be able to expand the SQL region at the bottom to see the code. Unfortunately, it doesn't come out well-formatted, but it's not too hard to clean up.

作为测试，我进去在 EMP 表的 JOB 列上创建了一个查找表.这是生成的代码.我已经对其进行了格式化并添加了注释来解释您需要和不需要的部分.

As a test, I went in and created a lookup table on the JOB column of the EMP table. Here's the code that was generated. I've formatted it and added comments to explain the parts you'll need and the ones you will not.

/*
* Creates the lookup table. Not needed after the first pass.
*/
create table "JOB_LOOKUP"(
  "JOB_ID" number not null primary key, 
  "JOB" varchar2(4000) not null
);

/*
* Creates the sequence for the primary key of the lookup table. 
* Not needed after the first pass.
*/
create sequence "JOB_LOOKUP_SEQ";

/*
* Creates the trigger that links the sequence to the table.
* Not needed after the first pass. 
*/
create or replace trigger "T_JOB_LOOKUP" 
before insert or update on "JOB_LOOKUP" 
for each row 
begin 
if inserting and :new."JOB_ID" is null then 
  for c1 in (select "JOB_LOOKUP_SEQ".nextval nv from dual) loop 
    :new."JOB_ID" := c1.nv;   end loop; end if; 
end;
/

/*
* Inserts the distinct values from the source table into the lookup
* table. If the lookup table already contains ALL of the needed values,
* country names in your case, then you can skip this step. However, if
* the source table has some values that are not in the lookup table, then
* you'll need to execute a modified version of this step. See notes below.
*/
insert into "JOB_LOOKUP" ( "JOB" ) 
select distinct "JOB" from "DMCGHANTEST"."EMP"
where "JOB" is not null;

/*
* The rest of the statements add the foreign key column, populate it,
* remove the old column, rename the new column, and add the foreign key.
* All of this is still needed.
*/
alter table "EMP" add "JOB2" number;

update "EMP" x set "JOB2" = (select "JOB_ID" from "JOB_LOOKUP" where "JOB" = x."JOB");

alter table "EMP" drop column "JOB";
alter table "EMP" rename column "JOB2"  to "JOB_ID";
alter table "EMP" add foreign key ("JOB_ID") references "JOB_LOOKUP" ("JOB_ID");

至于填充查找表的插入语句，这是您需要的修改版本:

As for the insert statement that populates the lookup table, here's the modified version you'll need:

insert into "JOB_LOOKUP" ( "JOB" ) 
select distinct "JOB" from "DMCGHANTEST"."EMP"
where "JOB" is not null
  and "JOB" not in (
    select "JOB"
    from JOB_LOOKUP
  );

这将确保只有新的、唯一的值被添加到查找表中.

That will ensure only new, unique values are added to the lookup table.

这篇关于Oracle APEX - 具有多个链接的查找表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！