如何用PL/SQL生成跨库数据迁移所需的INSERT脚本？

问题背景 #

已知两张关联表CUSTOMER和CUSTOMERINFO，结构如下：

-- CUSTOMER表结构
ID
CUSTOMER_NUMBER
CUSTOMERINFO_ID
FOREIGN KEY (CUSTOMERINFO_ID) REFERENCES CUSTOMERINFO (ID)

-- CUSTOMERINFO表结构
ID
CUSTOMER_NAME
CUSTOMER_SURNAME

已知CUSTOMER_NUMBER，需要生成包含完整数据的INSERT脚本，格式示例：

INSERT INTO CUSTOMER (ID, CUSTOMER_NUMBER, CUSTOMERINFO_ID)
VALUES(1,12345,1);

INSERT INTO CUSTOMERINFO (ID, CUSTOMER_NAME, CUSTOMER_SURNAME)
VALUES(1,'NAME','SURNAME');

目前手动生成效率极低，现有PL/SQL脚本仅能输出查询语句，需修改为直接生成INSERT脚本：

DECLARE 
lv_customerNo varchar2(20):=:customerNumber;
lv_customer CUSTOMER%ROWTYPE;
BEGIN
    SELECT * INTO lv_customer FROM CUSTOMER WHERE CUSTOMER_NUMBER = lv_customerNo ;
dbms_output.put_line('SELECT * FROM CUSTOMER bp WHERE ID =' || lv_customer.ID);
dbms_output.put_line('SELECT * FROM CUSTOMERINFO WHERE ID =' || lv_customer.CUSTOMERINFO_ID);
END;

解决方案 #

通过字符串拼接生成INSERT语句，重点处理字符串类型的单引号转义（避免语法错误），同时兼容不同数据类型的输出格式。修改后的PL/SQL脚本如下：

DECLARE 
    lv_customerNo varchar2(20) := :customerNumber;
    lv_customer CUSTOMER%ROWTYPE;
    lv_customerinfo CUSTOMERINFO%ROWTYPE;
    v_insert_customer VARCHAR2(1000);
    v_insert_customerinfo VARCHAR2(1000);
BEGIN
    -- 获取指定CUSTOMER_NUMBER的CUSTOMER数据
    SELECT * INTO lv_customer FROM CUSTOMER WHERE CUSTOMER_NUMBER = lv_customerNo;
    -- 通过关联ID获取对应的CUSTOMERINFO数据
    SELECT * INTO lv_customerinfo FROM CUSTOMERINFO WHERE ID = lv_customer.CUSTOMERINFO_ID;

    -- 生成CUSTOMER表的INSERT语句
    v_insert_customer := 'INSERT INTO CUSTOMER (ID, CUSTOMER_NUMBER, CUSTOMERINFO_ID)' || CHR(10) ||
                         'VALUES(' || lv_customer.ID || ', ' || lv_customer.CUSTOMER_NUMBER || ', ' || lv_customer.CUSTOMERINFO_ID || ');';
    
    -- 生成CUSTOMERINFO表的INSERT语句，处理字符串单引号转义
    v_insert_customerinfo := 'INSERT INTO CUSTOMERINFO (ID, CUSTOMER_NAME, CUSTOMER_SURNAME)' || CHR(10) ||
                             'VALUES(' || lv_customerinfo.ID || ', ''' || REPLACE(lv_customerinfo.CUSTOMER_NAME, '''', '''''') || ''', ''' || REPLACE(lv_customerinfo.CUSTOMER_SURNAME, '''', '''''') || ''');';

    -- 输出最终INSERT脚本
    DBMS_OUTPUT.PUT_LINE(v_insert_customer);
    DBMS_OUTPUT.PUT_LINE(CHR(10)); -- 空行分隔两张表的脚本
    DBMS_OUTPUT.PUT_LINE(v_insert_customerinfo);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到匹配CUSTOMER_NUMBER: ' || lv_customerNo || '的数据');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('找到多条匹配CUSTOMER_NUMBER: ' || lv_customerNo || '的数据');
END;
/

关键细节说明 #

• 单引号转义：使用REPLACE函数将字符串中的单引号'替换为两个单引号''，避免INSERT语句出现语法错误。
• 数据类型适配：数字类型直接拼接；若涉及日期类型，需用TO_CHAR格式化并包裹单引号，例如TO_CHAR(lv_date_col, 'YYYY-MM-DD HH24:MI:SS')。
• 异常捕获：添加了NO_DATA_FOUND和TOO_MANY_ROWS异常处理，避免脚本因数据异常中断。
• 批量扩展：若需处理多个CUSTOMER_NUMBER，可改为游标循环遍历，批量生成所有目标数据的INSERT语句。

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