oracle从其他表查询数据填充表,oracle中从4个表查询数据并插入到另一张表里 扩展到查询到的数据和其他数据共同插入...

因为业务的需要，现在需要查询4个表里的数据并插入到另一个表内且对应自己想要对应的列。

且表的id应该是根据序列自动生成。

具体创建序列什么的都不说了，直接上代码，字段都没改，最近太忙了。

insert into TB_POST_REQUIRE_INFO

(id, BENCHMARK_POST_CODE, BENCHMARK_POST_NAME, POST_SEQUENCE, POST_RANK,

SUPERBENCHMARK_POST_CODE, PROVINCE_NO, DEP_NAME_0, POST_CODE_0, POST_NAME_0,

DEPT_CODE_0, unit_code_0, REVOKE_FLAG, unit_id_0, UNIT_NAME_0)

(select seq_post_require_info.nextval id, a.BENCHMARK_POST_CODE BENCHMARK_POST_CODE,

a.BENCHMARK_POST_NAME BENCHMARK_POST_NAME, a.POST_SEQUENCE POST_SEQUENCE,

a.POST_RANK POST_RANK, a.SUPERBENCHMARK_POST_CODE SUPERBENCHMARK_POST_CODE, a.PROVINCE_NO PROVINCE_NO,

b.DEPT_NAME DEP_NAME_0, c.POST_CODE POST_CODE_0, c.POST_NAME POST_NAME_0, c.DEPT_CODE DEPT_CODE_0,

c.ORG_CODE unit_code_0, c.REVOKE_FLAG REVOKE_FLAG, d.ORG_ID unit_id_0, d.ORG_NAME UNIT_NAME_0

from dual, TB_BENCHMARK_JOB_INTERFACE a,

TB_DEPT_INTERFACE b,

TB_POST_INTERFACE c,

T_SYS_ORG d where A.BENCHMARK_POST_CODE = C.BENCHMARK_POST_CODE

and B.DEPT_CODE = C.DEPT_CODE and D.ORG_CODE = C.ORG_CODE)

今天又用到，查询到的数据和不属于查找的数据一起插入表内，实现如下：

insert into TB_POST_RANK_MAINTAIN

(id, ORG_CODE, ORG_NAME, DEPT_ID, DEPT_NAME, POST_CODE,

POST_NAME, TRANSITION_POST_SEQ，APPLY_USER_ID, APPLY_USER_NAME,

APPLY_ORG_ID, APPLY_STATUS, APPLY_DATE)

(select seq_post_rank_maintain.nextval id, a.UNIT_CODE_0, a.UNIT_NAME_0,

a.DEPT_CODE_0, a.DEP_NAME_0, a.POST_CODE_0, a.POST_NAME_0, a.POST_SEQUENCE，

'aaa', 'bbb', 'ccc','aaa','aaa','2016-01-01'

from dual, TB_POST_REQUIRE_INFO a)在mybatis的xml中实现如下：

insert into TB_POST_RANK_MAINTAIN

(id, ORG_CODE, ORG_NAME, DEPT_ID, DEPT_NAME, POST_CODE,

POST_NAME, TRANSITION_POST_SEQ，APPLY_USER_ID, APPLY_USER_NAME,

APPLY_ORG_ID, APPLY_STATUS, APPLY_DATE)

(select seq_post_rank_maintain.nextval id, a.UNIT_CODE_0, a.UNIT_NAME_0,

a.DEPT_CODE_0, a.DEP_NAME_0, a.POST_CODE_0, a.POST_NAME_0, a.POST_SEQUENCE，

#{sub_user_id,jdbcType=VARCHAR}, #{sub_user_name,jdbcType=VARCHAR}, #{sub_org_id,jdbcType=VARCHAR},

#{status,jdbcType=VARCHAR}, SYSDATE

from dual, TB_POST_REQUIRE_INFO a)