Oracle中用随机数更新字段----将一张表的数据插入另一张表----环境设置

DECLARE
        CURSOR recordCursor IS
               SELECT longitude,latitude FROM WR_WIUST_B_SEC FOR UPDATE;
recordRow  recordCursor % ROWTYPE;
BEGIN
        OPEN  recordCursor;
        LOOP
             FETCH recordCursor  INTO  recordRow;
             IF  recordCursor % NOTFOUND THEN
                   EXIT;
             ELSE
                   UPDATE WR_WIUST_B_SEC SET longitude= (select SUBSTR(to_char(dbms_random.value()*(116.280858-108.362376)+108.362376),0, 10) from dual ),
                    latitude= (select SUBSTR(to_char(dbms_random.value()*(33.257267-29.040321)+29.040321),0, 10) from dual )
                    WHERE CURRENT OF  recordCursor;
             END IF;
         END LOOP;
         COMMIT;
         CLOSE  recordCursor;
END;

commit;

 

insert into st_pptn_r_temp(stcd, tm, drp, intv, pdr, dyp, wth, datetime)
(
select distinct t.stcd, t.tm ,t.drp, t.intv, t.pdr, t.dyp, t.wth, t.datetime from st_pptn_r t
inner join ST_STBPRP_B_temp b on b.stcd=t.stcd
where t.tm >= to_date('2014-05-01 08:00:00','yyyy-mm-dd hh24:mi:ss') and t.tm <= to_date('2014-10-01 08:00:00','yyyy-mm-dd hh24:mi:ss') 
);

insert into st_river_r_temp(stcd, tm , z, q, xsa, xsavv, xsmxv, flwchrcd, wptn, msqmt, msamt, msvmt)
(
select distinct t.stcd, t.tm , t.z, t.q, t.xsa, t.xsavv, t.xsmxv, t.flwchrcd, t.wptn, t.msqmt, t.msamt, t.msvmt from st_river_r t
inner join ST_STBPRP_B_temp b on b.stcd=t.stcd
where t.tm >= to_date('2014-05-01 08:00:00','yyyy-mm-dd hh24:mi:ss') and t.tm <= to_date('2014-10-01 08:00:00','yyyy-mm-dd hh24:mi:ss') 
);

insert into ST_RSVR_R_temp(stcd, tm, rz, inq, w, blrz, otq, rwchrcd, rwptn, inqdr, msqmt)
(
select distinct r.stcd, r.tm, r.rz, r.inq, r.w, r.blrz, r.otq, r.rwchrcd, r.rwptn, r.inqdr, r.msqmt from ST_RSVR_R r
inner join ST_STBPRP_B_temp b on b.stcd=r.stcd
where r.tm >= to_date('2014-05-01 08:00:00','yyyy-mm-dd hh24:mi:ss') and r.tm <= to_date('2014-10-01 08:00:00','yyyy-mm-dd hh24:mi:ss') 
);

 

1.语言与编码



2.时间格式
1）用alter session来修改
SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


2）在OS中设置nls_date_format环境变量
SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS