使用PLSQL游标实现临时表到主表的数据迁移问题

现有代码错误点 #

• 游标取数语法错误：将INSERT C_REQ INTO V_REQ错误用作取数语句，正确语法为FETCH C_REQ INTO V_REQ
• 字段类型定义错误：自定义record中v_state字段错误引用了address字段的类型，需改为对应主表state字段的类型
• 输出语法错误：dbms_output.put_line仅支持单字符串参数，原代码传入多参数会触发语法报错
• 缺失核心插入逻辑：原代码仅读取了临时表数据，没有写入主表的逻辑

修正后可运行代码 #

DECLARE
    type R_REQ is record ( v_branch accountrequest.branch%type,
                           v_account_type accountrequest.account_type%type,
                           v_title accountrequest.title%type,
                           v_firstname accountrequest.firstname%type,
                           v_lastname accountrequest.lastname%type,
                           v_birthday accountrequest.birthday%type,
                           v_workphone accountrequest.workphone%type,
                           v_homephone accountrequest.homephone%type,
                           v_address accountrequest.address%type,
                           v_state accountrequest.state%type,
                           v_zip accountrequest.zip%type,
                           v_email accountrequest.email%type );
    V_REQ R_REQ;
    CURSOR C_REQ IS SELECT * FROM ACCOUNTREQUEST_TEMP;
    
BEGIN
     OPEN C_REQ;
     LOOP
      FETCH C_REQ INTO V_REQ;
      exit when c_req%notfound;
      -- 字段拼接输出，按需调整分隔符
      dbms_output.put_line('处理数据：'||V_REQ.BRANCH||' '|| V_REQ.ACCOUNT_TYPE||' '|| V_REQ.TITLE||' '|| V_REQ.FIRSTNAME||' '|| V_REQ.LASTNAME);
      -- 插入主表，自增ID和默认值字段无需显式赋值，Oracle自动处理
      INSERT INTO accountrequest(branch,account_type,title,firstname,lastname,birthday,workphone,homephone,address,state,zip,email)
      VALUES (V_REQ.v_branch,V_REQ.v_account_type,V_REQ.v_title,V_REQ.v_firstname,V_REQ.v_lastname,V_REQ.v_birthday,V_REQ.v_workphone,V_REQ.v_homephone,V_REQ.v_address,V_REQ.v_state,V_REQ.v_zip,V_REQ.v_email);
     END LOOP;
     CLOSE C_REQ;
     -- 提交事务
     COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常回滚
        ROLLBACK;
        dbms_output.put_line('插入失败，错误信息：'||SQLERRM);
END;
/

补充说明 #

主表的自增ID和带默认值的字段不需要写在INSERT的字段列表中，数据库会按照字段定义自动生成对应值。如果默认值字段你需要自定义值而非使用默认，在INSERT语句中补充对应字段和赋值即可。

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