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); //删除重复记录