工作中需要自己创建一个表并且向其中加入数据,在这里记录一下,因为我们用的是oracle数据库,所以字段类型用的是varchar2如果是mysql的话,是没有varchar2的,只有varchar
首先创建表
create table A(
id varchar2(50) not null,
name varchar2(200) not null,
c varchar2(10),
d varchar2(20),
.
.
.
create_time varchar2(14) not null,
update_time varchar2(14) not null
)
字段不能为空的在后面加not null就好了,然后是加主键索引,举个例子,设定id为主键
alter table A add constraint z_y_x_id primary key(id);
然后就是添加注释,首先是给整个表添加注释,然后是给各个字段添加注释
comment on table A is '用户表';
comment on column A.id is '用户id';
comment on column A.name is '用户姓名';
comment on column A.c is 'C';
comment on column A.d is 'D';
.
.
.
comment on column A.create_time is '创建时间';
comment on column A.update_time is '更新时间';
这样表就创建好了,最后是向表中插入数据,这里用的是mybatis,传入参数为List
<update id="XXXXXXXX" parameterType="java.util.List">
merge into A
using
<foreach close=")" collection="list" item="item" index="index" open="(" separator="union all">
select
#{item.id} as "id",
#{item.name} as "name",
#{item.c} as "c",
#{item.d} as "d",
.
.
.
#{item.createTime} as "create_time",
#{item.updateTime} as "update_time"
from dual
</foreach> s
on (s.id = id)
when matched then
update set
name = s.name,
c = s.c,
d = s.d,
.
.
.
create_time = s.create_time,
update_time = s.update_time
when not matched then
insert
(id,
name,
c,
d,
.
.
.
create_time,
update_time)
values
(s.id,
s.name,
s.c,
s.d,
.
.
.
s.create_time,
s.update_time
)
</update>