Oracl sql小结

当前时间

select to_char(sysdate,‘yyyy-mm-dd,hh24:mi:ss’) from dual;

迁移数据

CREATE TABLE BRANCH_CONTACT_COLLOCATION
(	
"ID" NUMBER(*,0) NOT NULL ENABLE, 
"ORG_CODE" VARCHAR2(256), 
"COLLOCATION" VARCHAR2(256)
);

create sequence SEQ_BRANCH_COLLOCATION
increment by 1
start with 1
minvalue 1
maxvalue 9999999
order
cache 20
cycle;
INSERT INTO BRANCH_CONTACT_COLLOCATION (ID,ORG_CODE,COLLOCATION) SELECT SEQ_BRANCH_COLLOCATION.Nextval,BIM_BRANCH_CONTACT_ADMIN.ORG_CODE,BIM_BRANCH_CONTACT_ADMIN.COLLOCATION FROM BIM_BRANCH_CONTACT_ADMIN WHERE BIM_BRANCH_CONTACT_ADMIN.COLLOCATION IS NOT NULL;

先获取序列再插入数据库，并在实体类中返回id

<insert id="insertTaskProcess" parameterType="com.htsc.bim.model.process.EscrowChange">
        <selectKey resultType="int" order="BEFORE" keyProperty="id">
            SELECT TASK_PROCESS_ID_SEQ.Nextval as ID from DUAL
        </selectKey>
        insert into TASK_PROCESS
        (
        ID,
        CRE_TIME
        )VALUES
        (
        #{id},
        CURRENT_TIMESTAMP
        )
</insert>