Oracle源表数据校验与分流SQL实现求助：薪资及日期格式校验

Hey there! Let's tackle this Oracle SQL problem to split your string-only source data into valid and invalid tables efficiently—no ETL tool needed, just a single pass over your source table which will boost performance compared to separate operations.

Step 1: Define Validation Rules #

First, we need two key checks for each row:

• Salary Validation: Confirm the SALARY string is a valid number and ≥ 0.
• Birthday Validation: Ensure the BIRTHDAY string strictly follows the dd/mm/yyyy format.

We'll use Oracle's VALIDATE_CONVERSION (available in 12c+) for reliable, efficient validation—it avoids messy exception handling and works at the row level.

Step 2: Single-Pass Insert with INSERT ALL #

Oracle's INSERT ALL lets us insert into multiple tables in one statement, meaning we only scan the source table once. This is the biggest performance gain over running separate INSERTs for valid/invalid rows.

Here's the full, ready-to-use SQL code:

INSERT ALL
    -- Insert valid records into VALID_EMPLOYEE (convert to target data types)
    WHEN salary_is_valid = 1 AND birthday_is_valid = 1 THEN
        INTO VALID_EMPLOYEE (ID, NAME, SALARY, BIRTHDAY)
        VALUES (TO_NUMBER(ID), NAME, TO_NUMBER(SALARY), TO_DATE(BIRTHDAY, 'dd/mm/yyyy'))
    -- Insert invalid records into INVALID_EMPLOYEE (keep original strings + error reason)
    WHEN salary_is_valid = 0 OR birthday_is_valid = 0 THEN
        INTO INVALID_EMPLOYEE (ID, NAME, SALARY, BIRTHDAY, ERROR_REASON)
        VALUES (ID, NAME, SALARY, BIRTHDAY, 
                CASE 
                    WHEN salary_is_valid = 0 AND birthday_is_valid = 0 THEN 'Invalid salary and birthday format'
                    WHEN salary_is_valid = 0 THEN 'Invalid salary (non-numeric or negative)'
                    ELSE 'Invalid birthday (expected dd/mm/yyyy format)'
                END)
SELECT 
    ID,
    NAME,
    SALARY,
    BIRTHDAY,
    -- Flag valid salaries (numeric and ≥0)
    CASE 
        WHEN VALIDATE_CONVERSION(SALARY AS NUMBER) = 1 AND TO_NUMBER(SALARY) >= 0 THEN 1 
        ELSE 0 
    END AS salary_is_valid,
    -- Flag valid birthdays (matches dd/mm/yyyy)
    CASE 
        WHEN VALIDATE_CONVERSION(BIRTHDAY AS DATE, 'dd/mm/yyyy') = 1 THEN 1 
        ELSE 0 
    END AS birthday_is_valid
FROM YOUR_SOURCE_TABLE; -- Replace with your actual source table name
COMMIT;

Key Notes: #

• Older Oracle Versions: If you're pre-12c (no VALIDATE_CONVERSION), use regex checks as a fallback:
  • Salary: REGEXP_LIKE(SALARY, '^[0-9]+(\.[0-9]+)?$') to validate positive numbers
  • Birthday: REGEXP_LIKE(BIRTHDAY, '^\d{2}/\d{2}/\d{4}$') to check format structure
• Date Storage: The VALID_EMPLOYEE table stores BIRTHDAY as a native DATE type (best practice). If you need to display it as MM/dd/yyyy, use TO_CHAR(TO_DATE(BIRTHDAY, 'dd/mm/yyyy'), 'MM/dd/yyyy') in queries, but avoid storing dates as strings.
• Error Debugging: The ERROR_REASON column in INVALID_EMPLOYEE helps quickly identify why a row was rejected—you can omit it if you don't need this detail.

This approach ensures minimal source table reads, which is far more performant than running separate ETL-style operations.

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