【DB2】建造测试数据

建表语句

CREATE TABLE
     FUND_DAILY_INCOME
     (
         ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
         ACCOUNT_NO VARCHAR(32),
         CREATED_TIME TIMESTAMP,
         FUND_CODE VARCHAR(8),
         SHARE_HOLDING VARCHAR(15),
         INCOME_DATE VARCHAR(10),
         INCOME_AMOUNT BIGINT,
         MERGE_FLAG VARCHAR(1),
         OFFSET_FLAG VARCHAR(1),
         UPDATE_TIME TIMESTAMP,
         COMPANY_CODE VARCHAR(16),
         ALL_INCOME DECIMAL(19,0) DEFAULT 0,
         HOLDING_INCOME DECIMAL(19,0) DEFAULT 0,
         PLAT_ID BIGINT DEFAULT 10000,
         SERVICE_TYPE VARCHAR(2) DEFAULT '01',
         SERVICE_CODE VARCHAR(32) DEFAULT '10000',
         PRIMARY KEY (ID)
);

往表插入数据

BEGIN  
    DECLARE v_date  date  ; 
    DECLARE v_date_str varchar(20) ;
    DECLARE str varchar(20) ;
    DECLARE i  INTEGER ; 
    DECLARE val  INTEGER ;
    declare    sqlStr   varchar(1000);  
   
   
    set  v_date = to_date( '2014-01-01' ,'yyyy-mm-dd');
    set v_date_str =  to_char( v_date ,'yyyy-mm-dd') ;
    REPEAT  
       
               set i = 1 ;
               REPEAT     
               
                SET sqlStr =' INSERT INTO FUND_DAILY_INCOME (  ACCOUNT_NO, CREATED_TIME,  FUND_CODE, SHARE_HOLDING, INCOME_DATE,  INCOME_AMOUNT, MERGE_FLAG, OFFSET_FLAG, UPDATE_TIME, COMPANY_CODE  ) VALUES  '
                ||  '  (  ''1'', current timestamp , ''1'', ''1'',  '''||  v_date_str ||''' , 0, ''1'', ''1'', current timestamp , ''1'' ) ' ;  
                EXECUTE IMMEDIATE sqlStr;      
                set i = i +1 ;  
                UNTIL ( i > 1000001) 
                END REPEAT;   
        set v_date =   v_date +1 day ;
        set v_date_str = to_char( v_date ,'yyyy-mm-dd') ;
        set str =  to_char( v_date ,'yyyymmdd') ;
        set val = integer(str) ;
    UNTIL ( val > 2020101) 
    END REPEAT; 
END  

转载于:https://www.cnblogs.com/OliverQin/p/10096311.html