oracle数据库oracle数据库增不进去数据

Question

CREATE OR REPLACE PROCEDURE PROC_IMPORT_VILLAGE_DATA AUTHID CURRENT_USER AS

zhs VARCHAR2(126);
zrks VARCHAR2(126);
zrcs VARCHAR2(126);
pkhs VARCHAR2(126);
pkrks VARCHAR2(126);
dbrks VARCHAR2(126);
dbhs VARCHAR2(126);
wbrks VARCHAR2(126);
wbhs VARCHAR2(126);
ssmzrks VARCHAR2(126);
fnrks VARCHAR2(126);
cjrks VARCHAR2(126);
ldlrk VARCHAR2(126);
wcwgrs VARCHAR2(126);
villageid VARCHAR2(126);
p VARCHAR2(126);
--public VARCHAR2(126) := '0';

cursor cur is
select * from village_import; --定义游标

cursor dict is
SELECT *
FROM TBL_SYS_DICT D
WHERE D.DICT_TYPE_ID =
(SELECT DT.DICT_TYPE_ID
FROM TBL_SYS_DICT_TYPE DT
WHERE DT.DICT_TYPE_ID = '188FF57D8BF06D2CE0537E64120AF200')
ORDER BY D.DICT_VALUE ASC;
BEGIN

for temp in cur loop

--temp为临时变量名,自己任意起
zhs     := temp.总户数;
zrks    := temp.总人口数;
zrcs    := temp.自然村数;
pkhs    := temp.贫困户数;
pkrks   := temp.贫困人口数;
dbrks   := temp.低保人口数;
dbhs    := temp.低保户数;
wbrks   := temp.五保人口数;
wbhs    := temp.五保户数;
ssmzrks := temp.少数民族人口数;
fnrks   := temp.妇女人口数;
cjrks   := temp.残疾人口数;
ldlrk   := temp.劳动力人数;
wcwgrs  := temp.外出务工人数;
villageid := temp.村主键;

for dd in dict loop

 if dd.dict_value = 'B2' then p := temp.总户数 ; end if;
 if dd.dict_value = 'B3' then p := temp.总人口数 ; end if;
 if dd.dict_value = 'B1' then p := temp.自然村数 ; end if;
 if dd.dict_value = 'B2a' then p := temp.贫困户数 ; end if;
 if dd.dict_value = 'B3a' then p := temp.贫困人口数 ; end if;
 if dd.dict_value = 'B3b' then p := temp.低保人口数 ; end if;
 if dd.dict_value = 'B2b' then p := temp.低保户数 ; end if;
 if dd.dict_value = 'B3c' then p := temp.五保人口数 ; end if;
 if dd.dict_value = 'B2c' then p := temp.五保户数 ; end if;
 if dd.dict_value = 'B3d' then p := temp.少数民族人口数 ; end if;
 if dd.dict_value = 'B3e' then p := temp.妇女人口数 ; end if;
 if dd.dict_value = 'B3f' then p := temp.残疾人口数 ; end if;
 if dd.dict_value = 'B4' then p := temp.劳动力人数 ; end if;
 if dd.dict_value = 'B4a' then p := temp.外出务工人数 ; end if;

  insert into tbl_fil_village_basic
    (pk_village_basic_id,
     fk_village_id,
     fk_dict_name,
     basic_value,
     stat_year,
     create_time,
     creater,
     creater_org,
     year)
    select sys_guid(),
           villageid,
           dd.dict_value,
           p,
           '2018',
           to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
           'admin',
           '',
           '2018'
      from dual;
  commit;
end loop;

end loop;

END PROC_IMPORT_VILLAGE_DATA;

Answer 1

 select sys_guid(),
           villageid,
           dd.dict_value,
           p,
           '2018',
           to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
           'admin',
           '',
           '2018'
      from dual;

语句的执行结果是多条记录

 insert into tbl_fil_village_basic
    (pk_village_basic_id,
     fk_village_id,
     fk_dict_name,
     basic_value,
     stat_year,
     create_time,
     creater,
     creater_org,
     year)

后面跟具体的数据或占位符，多个记录用foreach遍历，就是说这样写是语法错误

 insert into testtable1(col1) select col2 from testtable2

正确使用

 insert into cardtable (pk_system, name, code,
        i18n, gateway, radiodatacontroller,
        radiodataassociate, combodatamodel, tag,
        secretkey,system, tenant, ts, dr)
         <foreach collection="list" item="item" index="index" separator="union" open="(" close=")">
            select
            #{item.pksystem,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.code,jdbcType=VARCHAR},
            #{item.i18n,jdbcType=VARCHAR}, #{item.gateway,jdbcType=VARCHAR},
            #{item.radiodatacontroller,jdbcType=VARCHAR},
            #{item.radiodataassociate,jdbcType=VARCHAR},
            #{item.combodatamodel,jdbcType=VARCHAR},
            #{item.tag,jdbcType=VARCHAR},
            #{item.secretkey,jdbcType=VARCHAR},#{item.system,jdbcType=VARCHAR},
            #{item.tenant,jdbcType=VARCHAR}, #{item.ts,jdbcType=TIMESTAMP},
            #{item.dr,jdbcType=NUMERIC}
            from dual
        </foreach>

这是我自己的语句，作为参考