如何使用Oracle外部表加载CSV并拆分列建立一对多FK关联

Got it, let's walk through exactly how to get your CSV loaded into Oracle using an external table, then split the approver column into a child table with a proper one-to-many relationship. Here's the breakdown:

1. Create an Oracle Directory for the CSV File #

First, you need a directory object pointing to where your CSV is stored on the server (or a network-accessible path). Run this as a privileged user like SYS:

CREATE OR REPLACE DIRECTORY csv_dir AS '/path/to/your/csv/folder';
GRANT READ, WRITE ON DIRECTORY csv_dir TO your_database_user;

2. Create the External Table #

This external table maps directly to your CSV structure. I kept the column names as you provided (note: that PRE_APPROVED_REQUESTEREXT_PRE_APPROVED_REQUESTER looks like a possible typo—double-check if it's supposed to be two separate columns!):

CREATE TABLE ext_sox_data (
    NAME VARCHAR2(50),
    OPCO VARCHAR2(50),
    CATEGORY VARCHAR2(50),
    PRE_APPROVED NUMBER(1),
    PRE_APPROVED_REQUESTEREXT_PRE_APPROVED_REQUESTER VARCHAR2(20), -- Verify this column name!
    EXT_PRE_APPROVED_REQUESTER VARCHAR2(20),
    AUTHORIZED_FW_REQUEST_APPROVER VARCHAR2(1000), -- Renamed for easier handling
    AUTHORIZED_UPDATE_TEAM_ENTRY VARCHAR2(50),
    WORK_INSTRUCTIONS_COMMENTS VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY csv_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1 -- Skip the CSV header row
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"' -- Use this if your CSV has quoted fields with commas inside
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('your_target_file.csv')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Pro tip: If your Authorized FW-Request Approver column has multiple emails separated by a delimiter (like ; or ,), we'll split those into individual rows in table2 next.

3. Create Target Tables (table1 and table2) #

We'll use Oracle's identity column feature (12c+) to auto-generate primary keys. table2 will have a foreign key linking back to table1.ID:

-- Create table1 with primary key
CREATE TABLE table1 (
    ID NUMBER(10,0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(50 BYTE),
    OPCO VARCHAR2(50 BYTE),
    CATEGORY VARCHAR2(50 BYTE),
    PRE_APPROVED NUMBER(1,0),
    PRE_APPROVED_REQUESTER VARCHAR2(20 BYTE), -- Adjust if the CSV column was a typo
    EXT_PRE_APPROVED_REQUESTER VARCHAR2(20 BYTE),
    AUTHORIZED_UPDATE_TEAM_ENTRY VARCHAR2(50 BYTE),
    WORK_INSTRUCTIONS_COMMENTS VARCHAR2(2000 BYTE)
);

-- Create table2 with foreign key to table1
CREATE TABLE table2 (
    ID NUMBER(10,0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    SOX_ID NUMBER(10,0) REFERENCES table1(ID),
    EMAIL VARCHAR2(50 BYTE)
);

Note: If that long CSV column name is indeed two separate fields (e.g., PRE_APPROVED_REQUESTER and EXT_PRE_APPROVED_REQUESTER), you'll need to adjust the external table and table1 to split it during loading—just let me know if you need help with that!

4. Load Data into table1 #

Insert data from the external table into table1, mapping columns correctly:

INSERT INTO table1 (
    NAME, OPCO, CATEGORY, PRE_APPROVED,
    PRE_APPROVED_REQUESTER, EXT_PRE_APPROVED_REQUESTER,
    AUTHORIZED_UPDATE_TEAM_ENTRY, WORK_INSTRUCTIONS_COMMENTS
)
SELECT
    NAME, OPCO, CATEGORY, PRE_APPROVED,
    -- If the CSV column was a typo, split it here; otherwise use the full column name
    SUBSTR(PRE_APPROVED_REQUESTEREXT_PRE_APPROVED_REQUESTER, 1, 20) AS PRE_APPROVED_REQUESTER,
    EXT_PRE_APPROVED_REQUESTER,
    AUTHORIZED_UPDATE_TEAM_ENTRY, WORK_INSTRUCTIONS_COMMENTS
FROM ext_sox_data;

COMMIT;

5. Split Authorized FW-Request Approver into table2 #

Assuming approver emails are separated by commas (adjust the regex delimiter if it's something else like ;), we'll use REGEXP_SUBSTR to split them into individual rows:

INSERT INTO table2 (SOX_ID, EMAIL)
SELECT
    t1.ID,
    TRIM(REGEXP_SUBSTR(t_ext.AUTHORIZED_FW_REQUEST_APPROVER, '[^,]+', 1, LEVEL)) AS EMAIL
FROM ext_sox_data t_ext
JOIN table1 t1
    ON t1.NAME = t_ext.NAME 
    AND t1.OPCO = t_ext.OPCO 
    -- Add more join conditions if NAME+OPCO isn't unique; use a unique identifier if available
CONNECT BY LEVEL <= REGEXP_COUNT(t_ext.AUTHORIZED_FW_REQUEST_APPROVER, ',') + 1
AND PRIOR t_ext.NAME = t_ext.NAME
AND PRIOR t_ext.OPCO = t_ext.OPCO
AND PRIOR SYS_GUID() IS NOT NULL; -- Avoid duplicate rows in the hierarchical query

COMMIT;

Important: If NAME and OPCO don't form a unique pair in your CSV, you'll need a better join condition—maybe add a unique ID column to the CSV, or use all columns as a composite key for the join.

6. Verify the Data #

Double-check everything loaded correctly:

-- Check table1 data
SELECT * FROM table1;

-- Check table2 with linked table1 records
SELECT t1.NAME, t1.OPCO, t2.EMAIL
FROM table1 t1
JOIN table2 t2 ON t1.ID = t2.SOX_ID;

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