Oracle APEX中如何基于现有表创建新表并建立关联关系

前提说明 #

默认原SONGS表包含以下关键字段，你可以根据实际表结构调整字段名：

• 主键字段：SONG_ID（数值类型，唯一标识每首歌曲）
• 艺术家字段：ARTIST_NAME（字符串类型，存储单个艺术家或逗号分隔的多个艺术家名称）

步骤1：创建艺术家表（ARTISTS） #

存储去重后的所有艺术家信息，建表语句如下：

CREATE TABLE artists (
    artist_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    artist_name  VARCHAR2(255) NOT NULL UNIQUE
);

若使用Oracle 12c以下版本，不支持IDENTITY自增主键，可替换为序列实现：

-- 创建自增序列
CREATE SEQUENCE artist_seq START WITH 1 INCREMENT BY 1;
-- 建表语句
CREATE TABLE artists (
    artist_id    NUMBER PRIMARY KEY DEFAULT artist_seq.NEXTVAL,
    artist_name  VARCHAR2(255) NOT NULL UNIQUE
);

表中artist_name加唯一约束，自动避免重复艺术家数据。

步骤2：创建歌曲-艺术家关联表（SONG_ARTISTS） #

存储歌曲和艺术家的多对多关联关系，建表语句如下：

CREATE TABLE song_artists (
    song_id    NUMBER NOT NULL REFERENCES songs(song_id),
    artist_id  NUMBER NOT NULL REFERENCES artists(artist_id),
    PRIMARY KEY (song_id, artist_id)
);

联合主键song_id+artist_id避免重复关联，外键约束保证数据和两张主表一致性。

步骤3：拆分原表数据并填充两张新表 #

3.1 填充艺术家表 #

拆分ARTIST_NAME字段的逗号分隔值，去重后插入艺术家表：

INSERT INTO artists (artist_name)
SELECT DISTINCT TRIM(REGEXP_SUBSTR(s.artist_name, '[^,]+', 1, LEVEL)) AS artist_name
FROM songs s
CONNECT BY REGEXP_SUBSTR(s.artist_name, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR s.song_id = s.song_id
    AND PRIOR SYS_GUID() IS NOT NULL;

TRIM函数用于处理艺术家名称前后的多余空格，DISTINCT保证艺术家名称唯一。

3.2 填充关联表 #

匹配歌曲ID和对应的艺术家ID，插入关联表：

INSERT INTO song_artists (song_id, artist_id)
SELECT DISTINCT s.song_id, a.artist_id
FROM songs s
JOIN artists a ON TRIM(REGEXP_SUBSTR(s.artist_name, '[^,]+', 1, LEVEL)) = a.artist_name
CONNECT BY REGEXP_SUBSTR(s.artist_name, '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR s.song_id = s.song_id
    AND PRIOR SYS_GUID() IS NOT NULL;

数据验证（可选） #

执行以下SQL确认数据正确性：

• 查看艺术家总数：SELECT COUNT(*) FROM artists;
• 查看关联关系总数：SELECT COUNT(*) FROM song_artists;
• 校验指定歌曲的艺术家列表：

  SELECT a.artist_name 
  FROM song_artists sa 
  JOIN artists a ON sa.artist_id = a.artist_id 
  WHERE sa.song_id = 替换为实际歌曲ID;
  

内容的提问来源于stack exchange，提问作者Javi Fernandez