TIMESTAMP 毫秒级增量处理方法

近日利用PL/SQL创建Oracle测试数据时，遇到时间类型的毫秒级数据处理问题。

Oracle日期类型DATE最小单位为秒，INTERVAL相关函数处理精度也是秒级，TIMESTAMP加减运算的结果也是秒级，因此当1ms内生成多条测试数据时，其TIMESTAMP类型字段数值相同。

为了解决TIMESTAMP 毫秒级增量问题，采用了间隔函数NUMTODSINTERVAL处理秒级增量，利用数值计算和字符串运算联合处理毫秒级增量，最终实现了TIMESTAMP类型字段的毫秒级增量处理(具体处理方法见51行和52行)。

BEGIN  EXECUTE IMMEDIATE 'DROP INDEX date_bigdata_10k_5s_01';  EXCEPTION WHEN OTHERS THEN NULL;END;/BEGIN  EXECUTE IMMEDIATE 'DROP TABLE bigdata_10k_5s_01';  EXCEPTION WHEN OTHERS THEN NULL;END;/CREATE TABLE bigdata_10k_5s_01 (  fl_varchar2       VARCHAR2(20),  fl_nvarchar2      NVARCHAR2(20),  fl_number_long    NUMBER(20),  fl_number_double  NUMBER(20,2),  fl_float          FLOAT,  fl_long           LONG,  fl_date           DATE,  fl_binary_float   BINARY_FLOAT,  fl_binary_double  BINARY_DOUBLE,  fl_timestamp      TIMESTAMP,  fl_interval       INTERVAL YEAR TO MONTH,  fl_raw            RAW(20),  fl_char           CHAR(20),  fl_nchar          NCHAR(20),  fl_clob           CLOB,  fl_nclob          NCLOB,  fl_blob           BLOB);CREATE INDEX date_bigdata_10k_5s_01  ON bigdata_10k_5s_01 (fl_date DESC);SET timing ON;SET serveroutput ON;DECLARE  i NUMBER(12);  n NUMBER(12);  t TIMESTAMP;  ct TIMESTAMP;  dt NUMBER(20,6);BEGIN  n:=10000;  -- total number of rows  t:=TO_TIMESTAMP('2017-08-10 12:00:00.000000','yyyy-mm-dd hh24:mi:ss.ff');  dt:=5000/n;  -- delta time (ms) between rows  dbms_output.put_line('## n is: ' || i || '; time is: 5s; dt is: ' || dt);  << outer_loop >>  FOR i IN 0..n-1 LOOP    ct:=t + NUMTODSINTERVAL(floor(i*dt/1000), 'SECOND');    ct:=TO_TIMESTAMP(TO_CHAR(ct,'yyyy-mm-dd hh24:mi:ss')||'.'||TO_CHAR(floor(Mod(i*dt,1000)*1000),'FM000000'), 'yyyy-mm-dd hh24:mi:ss.ff');    INSERT INTO bigdata_10k_5s_01 (        fl_varchar2,        fl_nvarchar2,        fl_number_long,        fl_number_double,        fl_float,        fl_long,        fl_date,        fl_binary_float,        fl_binary_double,        fl_timestamp,        fl_interval,        fl_raw,        fl_char,        fl_nchar,        fl_clob,        fl_nclob,        fl_blob    ) VALUES (        'r'||i||'_10k_5s_01',        'r'||i||'_nvarchar2',        i,        i+0.1,        i+0.2,        i*10+1,        ct,        i+0.3,        i+0.4,        ct,        NUMTOYMINTERVAL(MOD(i,100), 'MONTH'),        HEXTORAW(TO_CHAR(i+100)),        'r'||i||'_char',        'r'||i||'_nchar',        TO_CLOB('r'||i||'_clob'),        TO_NCLOB('r'||i||'_nclob'),        TO_BLOB(HEXTORAW(TO_CHAR(i+200)))    );  END LOOP outer_loop;END;/COMMIT;EXIT;