oracle+给表字段插入值,Oracle 安插表(从另外的表复制内容+其他字段插入值)

Oracle 插入表(从另外的表复制内容+其他字段插入值)

空表插入数据：一些字段从加一张表中取得

一些字段直接插入值

insert into tableName

select studentCode,studentName,                      //另一表中字段

'01','本表中字段赋值',32,                              //给本表中字段赋值

to_date('substr(IdCode,7,15)','yy-MM-dd'), //根据另一表中字段内容插入值：出生日期

case when mod(substr(IdCode,17),2)=0 then '0' else '1' end, //case when 用法

to_number(enterYear)+2,       //char型转成数值型

from anotherTable a

where not exists(select 1 from student st where st.studentCode=a.studentCode));  //去重复

Java：db:创建数据库连接

String sql = "select * from tableName";

String insertSql = "insert into tableName......";

ResultSet rs = db.executeQuery(sql);       //查询结果

int row = db.exectueUpdate(insertSql);        //更新(条数)

只取日期年份、月份、日：

String year=to_char(stu.date,'yyyy');

String month=to_char(stu.date,'MM');

String day=to_char(stu.date,'dd);

表中记录去除重复

select distinct coborrowerCode from historyInfo; //查询时去除重复

delete from coborrower c where c.rowid != (

select max(b.rowid) from coborrower b

where c.coborrowerCode = b.coborrowerCode); //删除重复记录