时间戳

首页 > 解决方案 > 需要用外键和主键填充表

oracle - 需要用外键和主键填充表

问题描述

我需要从all_tab_col系统表中创建三个表，以便架构详细信息在一个schema_detail table表中，表详细信息在table_detail表中，列详细信息在col_table。这三个表将通过存储过程同时填充，其中PK(generated using SEQUENCE)in schema_detailis FKin table_detailtable 和PK(generated using SEQUENCE)in table_detailis FKin col_detail table。

标签： oracleforeign-keysprimary-keyoracle12c

解决方案

SQL 是一种基于集合的语言，因此我很想通过三个集合基础步骤来解决您的任务。

一些模拟表（只需为您感兴趣的详细信息添加列）：

CREATE TABLE schema_detail (
  schema_id    NUMBER              GENERATED ALWAYS AS IDENTITY NOT NULL,
  schema_name  VARCHAR2(128 BYTE)  NOT NULL,
  CONSTRAINT schema_detail_pk PRIMARY KEY (schema_id)
);

CREATE TABLE table_detail (
  schema_id   NUMBER,
  table_id    NUMBER              GENERATED ALWAYS AS IDENTITY NOT NULL,
  table_name  VARCHAR2(128 BYTE)  NOT NULL,
  CONSTRAINT table_detail_pk PRIMARY KEY (table_id),
  CONSTRAINT table_detail_fk FOREIGN KEY (schema_id) 
    REFERENCES schema_detail(schema_id)
     ON DELETE CASCADE
);

CREATE INDEX table_detail_schema_idx ON table_detail(schema_id);
CREATE INDEX table_detail_name_idx ON table_detail(table_name);

CREATE TABLE col_detail (
  table_id   NUMBER,
  col_id     NUMBER              GENERATED ALWAYS AS IDENTITY NOT NULL,
  col_name   VARCHAR2(128 BYTE)  NOT NULL,
  CONSTRAINT col_detail_pk PRIMARY KEY (col_id),
  CONSTRAINT col_detail_fk FOREIGN KEY (table_id) 
    REFERENCES table_detail(table_id)
     ON DELETE CASCADE
);

CREATE INDEX col_detail ON col_detail(table_id);

我会schema_detail先填满桌子。PK是自动生成的：

INSERT INTO schema_detail(schema_name)
SELECT DISTINCT c.owner FROM all_tab_columns c ORDER BY owner;

SCHEMA_ID SCHEMA_NAME
1         APPQOSSYS
2         AUDSYS
3         CTXSYS
...

接下来，我会填写表格。schema_id 需要在schema_detail表中查找。同样，我们让 PK 自动生成：

INSERT INTO table_detail(schema_id, table_name)
SELECT DISTINCT s.schema_id, c.table_name 
  FROM all_tab_columns c
  JOIN schema_detail s ON c.owner = s.schema_name
 ORDER BY table_name;

SCHEMA_ID TABLE_ID TABLE_NAME
1         8403     WLM_CLASSIFIER_PLAN
1         8404     WLM_FEATURE_USAGE
1         8405     WLM_METRICS_STREAM
...

最后，我会填写这些列：

INSERT INTO col_detail(table_id, col_name)
SELECT DISTINCT t.table_id, c.column_name 
  FROM all_tab_columns c
  JOIN table_detail    t ON c.table_name = t.table_name
  JOIN schema_detail   s ON c.owner = s.schema_name
 ORDER BY s.schema_id, t.table_id, c.column_name;

这能解决您的问题还是您需要 PL/SQL 过程？

推荐阅读