Oralce建立模擬資料

暖楓無敵發表於2015-03-16
原文網址 : https://blog.csdn.net/zdw_wym/article/details/44307657

create or replace  TYPE oneDate as object(TM DATE) ;

create or replace TYPE TABLE_ONEDATE as TABLE OF ONEDATE ;


create or replace  FUNCTION fu_getdatetable
      (dtb DATE
      ,dte DATE
      ,splicnum INT
      ,splic VARCHAR2)
      RETURN table_onedate pipelined IS
         dt_dtt   DATE ;
         dt_dtb   DATE ;
         dt_dte   DATE ;
         i_splicnum   INT ;
         c_splic   VARCHAR2(10) ;
         r_daterow onedate ;
      BEGIN
         dt_dtb := dtb;
         dt_dte := dte ;
         r_daterow:= oneDate(sysdate);
         i_splicnum := splicnum;
         c_splic := splic;
         IF dt_dte IS NULL THEN
            SELECT SYSDATE INTO dt_dte FROM dual ;
         END IF ;


         IF dtb>dt_dte THEN
            dt_dtt := dt_dte ;
            dt_dte := dt_dtb ;
            dt_dtb := dt_dtt ;
         END IF ;
         dt_dtt :=  dt_dtb ;


         loop
            SELECT (CASE upper(c_splic)
               WHEN 'YEAR' THEN add_months(dt_dtt,i_splicnum*12)
               WHEN 'Y' THEN add_months(dt_dtt,i_splicnum*12)
               WHEN 'YY' THEN add_months(dt_dtt,i_splicnum*12)
               WHEN 'YYYY' THEN add_months(dt_dtt,i_splicnum*12)
               WHEN 'QUARTER' THEN add_months(dt_dtt,i_splicnum*3)
               WHEN 'Q' THEN add_months(dt_dtt,i_splicnum*3)
               WHEN 'QQ' THEN add_months(dt_dtt,i_splicnum*3)
               WHEN 'MONTH' THEN add_months(dt_dtt,i_splicnum)
               WHEN 'M' THEN add_months(dt_dtt,i_splicnum)
               WHEN 'MM' THEN add_months(dt_dtt,i_splicnum)
               WHEN 'WEEK' THEN dt_dtt + 7*i_splicnum
               WHEN 'W' THEN dt_dtt + 7*i_splicnum
               WHEN 'WK' THEN dt_dtt + 7*i_splicnum
               WHEN 'WW' THEN dt_dtt + 7*i_splicnum
               WHEN 'DAY' THEN dt_dtt + i_splicnum
               WHEN 'DD' THEN dt_dtt + i_splicnum
               WHEN 'D' THEN dt_dtt + i_splicnum
               WHEN 'HOUR' THEN dt_dtt + i_splicnum/24
               WHEN 'HH' THEN dt_dtt + i_splicnum/24
               WHEN 'H' THEN dt_dtt + i_splicnum/24
               WHEN 'MINUTE' THEN dt_dtt + i_splicnum/(24*60)
               WHEN 'MI' THEN dt_dtt + i_splicnum/(24*60)
               WHEN 'N' THEN dt_dtt + i_splicnum/(24*60)
               WHEN 'SECOND' THEN dt_dtt + i_splicnum/(24*60*60)
               WHEN 'SS' THEN dt_dtt + i_splicnum/(24*60*60)
               WHEN 'S' THEN  dt_dtt + i_splicnum/(24*60*60)
               WHEN 'MILLISECOND' THEN dt_dtt + i_splicnum/(24*60*60*1000)
               WHEN 'L' THEN dt_dtt + i_splicnum/(24*60*60*1000)
               WHEN 'MS' THEN dt_dtt + i_splicnum/(24*60*60*1000)
               ELSE  dt_dtt + i_splicnum
               END )
               INTO dt_dtt FROM dual ;
            IF dt_dtt>dt_dte THEN exit ;  END IF ;
            r_daterow.tm:=dt_dtt ;
            pipe ROW (r_daterow) ;
         END loop ;
         RETURN ;
   END fu_getdatetable ;
   
create or replace 
FUNCTION F_Random_Data(in_text IN number) RETURN   number
IS
BEGIN
        IF in_text>0.5 THEN
            RETURN 593-(in_text-0.5)/5;
        ELSE
            RETURN 593+in_text/5;
        END IF;
END F_Random_Data;


alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


select F_Random_Data(DBMS_RANDOM.VALUE()) d,tm from table(fu_getdatetable(sysdate-1,sysdate,1,'h'));


insert into ST_RIVER_R(STCD,TM,Z)
select '62919414',tm,F_Random_Data(DBMS_RANDOM.VALUE()) d from table(fu_getdatetable(to_date('2013-11-25','yyyy-mm-dd'),to_date('2015-03-16 16:00:00','yyyy-mm-dd hh24:mi:ss'),1,'h'))

update ST_RIVER_R set Z=trunc(Z,2) where STCD='62919414';


相關文章