游标+bulk collect into limit的不同方法查询数据

Starヾ释怀

已于 2022-08-13 21:14:56 修改

阅读量192

点赞数 1

文章标签：数据库开发 oracle

于 2022-04-26 14:21:16 首次发布

本文链接：https://blog.csdn.net/weixin_47940024/article/details/124426718

版权

游标+bulk collect into limit的不同方法查询数据

数据迁移
- 游标+bulk collect into limit
- 参考

数据迁移

需求：由于数据量亿级别，insert into 插入数据非常慢，于是用游标批量提交
GZ45用户的数据迁移到FDC用户

游标+bulk collect into limit

DECLARE

  TYPE V_ROWID IS TABLE OF T_SUBJECTINFO%ROWTYPE; --定义type类型

  VAR_ROWID V_ROWID; --定义rowid变量

  CURSOR V_CUR IS
    SELECT N_ID, N_YEAR, C_PLAN_CODE, C_PORT_CODE, C_ASSET_CODE, C_SUBJ_CODE,
           C_SUBJ_NAME, C_SUBJ_TYPE, C_PA_CODE, C_CURY_CODE, C_SUBJ_CODE_P,
           C_MKT_CODE, C_SEC_VAR, C_SEC_VAR_MX, C_IVT_CLSS, C_HLD_ATTR,
           C_TD_ATTR, C_ML_ATTR, C_SEC_CODE, C_CA_CODE, C_PI_CODE, N_SUBJ_DETAIL,
           C_DV_BOOL_TYPE_AM, C_DV_JD_WAY, C_SUBJ_LEVEL, C_DS_CODE, C_ORG_CODE,
           C_DV_INVEST_MODE, C_DV_SEC_DUR, C_WART_TYPE, C_EXER_MODE,
           C_UPDATE_TIME, C_TIMESTAMP, FSYSTYPE
    FROM   GZ45.T_SUBJECTINFO;

BEGIN

  OPEN V_CUR;
  LOOP
    **FETCH V_CUR BULK COLLECT
      INTO VAR_ROWID LIMIT 5000;
    FORALL I IN 1 .. VAR_ROWID.COUNT**
      INSERT INTO FDC.T_SUBJECTINFO
        (N_ID, N_YEAR, C_PLAN_CODE, C_PORT_CODE, C_ASSET_CODE, C_SUBJ_CODE,
         C_SUBJ_NAME, C_SUBJ_TYPE, C_PA_CODE, C_CURY_CODE, C_SUBJ_CODE_P,
         C_MKT_CODE, C_SEC_VAR, C_SEC_VAR_MX, C_IVT_CLSS, C_HLD_ATTR, C_TD_ATTR,
         C_ML_ATTR, C_SEC_CODE, C_CA_CODE, C_PI_CODE, N_SUBJ_DETAIL,
         C_DV_BOOL_TYPE_AM, C_DV_JD_WAY, C_SUBJ_LEVEL, C_DS_CODE, C_ORG_CODE,
         C_DV_INVEST_MODE, C_DV_SEC_DUR, C_WART_TYPE, C_EXER_MODE, C_UPDATE_TIME,
         C_TIMESTAMP, FSYSTYPE)
      VALUES
        (VAR_ROWID(I).N_ID, VAR_ROWID(I).N_YEAR, VAR_ROWID(I).C_PLAN_CODE,
         VAR_ROWID(I).C_PORT_CODE, VAR_ROWID(I).C_ASSET_CODE,
         VAR_ROWID(I).C_SUBJ_CODE, VAR_ROWID(I).C_SUBJ_NAME,
         VAR_ROWID(I).C_SUBJ_TYPE, VAR_ROWID(I).C_PA_CODE,
         VAR_ROWID(I).C_CURY_CODE, VAR_ROWID(I).C_SUBJ_CODE_P,
         VAR_ROWID(I).C_MKT_CODE, VAR_ROWID(I).C_SEC_VAR,
         VAR_ROWID(I).C_SEC_VAR_MX, VAR_ROWID(I).C_IVT_CLSS,
         VAR_ROWID(I).C_HLD_ATTR, VAR_ROWID(I).C_TD_ATTR, VAR_ROWID(I).C_ML_ATTR,
         VAR_ROWID(I).C_SEC_CODE, VAR_ROWID(I).C_CA_CODE, VAR_ROWID(I).C_PI_CODE,
         VAR_ROWID(I).N_SUBJ_DETAIL, VAR_ROWID(I).C_DV_BOOL_TYPE_AM,
         VAR_ROWID(I).C_DV_JD_WAY, VAR_ROWID(I).C_SUBJ_LEVEL,
         VAR_ROWID(I).C_DS_CODE, VAR_ROWID(I).C_ORG_CODE,
         VAR_ROWID(I).C_DV_INVEST_MODE, VAR_ROWID(I).C_DV_SEC_DUR,
         VAR_ROWID(I).C_WART_TYPE, VAR_ROWID(I).C_EXER_MODE,
         VAR_ROWID(I).C_UPDATE_TIME, VAR_ROWID(I).C_TIMESTAMP,
         VAR_ROWID(I).FSYSTYPE);
  
   COMMIT;
   EXIT WHEN V_CUR%NOTFOUND OR V_CUR%NOTFOUND IS NULL;
  
  END LOOP;

  CLOSE V_CUR;

END;