1、创建货品表
create table goods(
billing_cycle_id VARCHAR2(8) NOT NULL PRIMARY KEY, -- 账期, 其值例如:1029
sum_num NUMBER DEFAULT NULL, -- 当日数据总量, 其值例如:1400
re_sum_num NUMBER, -- 昨日数据量, 其值例如: 1320
ratio VARCHAR2(41), -- 波动百分比, 其值例如: 20.15%
create_date date, -- 创建时间, 其值例如:1029 02:01:33
update_date date, -- 更新时间, 其值例如:1029 02:01:33
remark varchar2(2000) -- 备注, 其值例如:这是一条测试数据.
);
给表添加注释
COMMENTONTABLEgoods IS'货品信息表';
3、给字段添加注释
COMMENT ON COLUMN goods.billing_cycle_id IS '账期';
COMMENT ON COLUMN goods.sum_num IS '当日数据总量';
COMMENT ON COLUMN goods.re_sum_num IS '昨日数据总量';
COMMENT ON COLUMN goods.ratio IS '波动百分比';
COMMENT ON COLUMN goods.create_date IS '创建时间';
COMMENT ON COLUMN goods.update_date IS '更新时间';
COMMENT ON COLUMN goods.remark IS '备注';
查询列注释:select * from user_col_comments
你可以用Oracle工具来修改,例如PL/Sql Developer,TOAD
mybatis 批量插入数据
<insert id="insertByBatch" parameterType="java.util.List">
insert into BPM_D_DATA_INDEX (THE_MONTH, BU_CODE, PHASE_CODE,
PHASE_NM, AREA_CODE, AREA_NAME,DATA_LEVEL, INDEX_CODE, INDEX_NAME,
MONTH_VALUE, OP_TIME, EXECUTION_ID,LOAD_DATE, BU, DATEKEY,INSERT_DATA_TIME)
(<foreach collection="list" index="" item="item" separator="union all">
SELECT
#{item.theMonth,jdbcType=VARCHAR}, #{item.buCode,jdbcType=VARCHAR}, #{item.phaseCode,jdbcType=VARCHAR},
#{item.phaseNm,jdbcType=VARCHAR}, #{item.areaCode,jdbcType=VARCHAR}, #{item.areaName,jdbcType=VARCHAR},
#{item.dataLevel,jdbcType=VARCHAR}, #{item.indexCode,jdbcType=VARCHAR}, #{item.indexName,jdbcType=VARCHAR},
#{item.monthValue,jdbcType=DECIMAL}, #{item.opTime,jdbcType=VARCHAR}, #{item.executionId,jdbcType=VARCHAR},
#{item.loadDate,jdbcType=VARCHAR}, #{item.bu,jdbcType=VARCHAR}, #{item.datekey,jdbcType=VARCHAR},
#{item.insertDataTime,jdbcType=TIMESTAMP}
FROM DUAL
</foreach>)
</insert>
----------------------------------
mybatis 默认插入当前时间
<insert id="importSickTree" parameterType="list" >
insert into tb_sick_tree (treeCode,projectID,
lat, lon, distCode, distName,
identifyTime,processed,year)
VALUES
<foreach collection="list" item="sickTree" separator=",">
( #{sickTree.treecode,jdbcType=VARCHAR},#{sickTree.projectid,jdbcType=INTEGER},
#{sickTree.lat,jdbcType=DOUBLE}, #{sickTree.lon,jdbcType=DOUBLE},
#{sickTree.distcode,jdbcType=VARCHAR}, #{sickTree.distname,jdbcType=VARCHAR},
#{sickTree.identifytime,jdbcType=TIMESTAMP}, #{sickTree.processed,jdbcType=INTEGER},SUBSTR(NOW(),1,4) )
</foreach>
</insert>