DB2造数据存储过程

最新推荐文章于 2020-12-24 10:32:49 发布

Mr.madong

最新推荐文章于 2020-12-24 10:32:49 发布

阅读量1k

点赞数 1

分类专栏： db2

本文链接：https://blog.csdn.net/M983373615/article/details/87696320

版权

db2 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

应测试要求，需要造大量数据以验证脚本及程序的性能，总结效率
表原型：

DROP TABLE IALCLAIMCHECK;
CREATE TABLE
    IALCLAIMCHECK
    (
	    ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
        CLAIMQUERYNO VARCHAR(50) NOT NULL,
        USERCODE VARCHAR(30) NOT NULL,
        AREACODE VARCHAR(10) NOT NULL,
        COMPANYCODE VARCHAR(8) NOT NULL,
        POLICYCONFIRMNO VARCHAR(50) NOT NULL,
        REGISTNO VARCHAR(50),
        ACCIDENTTYPE CHARACTER(3),
        INDEMNITYDUTY CHARACTER(1),
        OPTIONTYPE CHARACTER(1),
        ACCIDENTCAUSE CHARACTER(6),
        INPUTDATE TIMESTAMP,
        VALIDSTATUS CHARACTER(1),
        PAYSELFFLAG SMALLINT,
        CHECKSTARTTIME TIMESTAMP,
        CHECKENDTIME TIMESTAMP,
        UPDATETIME TIMESTAMP,
        RISKQUERYSEQUENCENO VARCHAR(50),
        RISKTYPE CHARACTER(2),
        ISPERSONINJURED CHARACTER(1),
        ISPROTECTLOSS CHARACTER(1),
        ISSINGLEACCIDENT CHARACTER(1),
		INCOME_DATE   VARCHAR(10),
        PRIMARY KEY (ID)
    );

存储过程（这个可以直接在dbvis软件中执行）：

 --/  
  
 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); 
   declare num INTEGER;
   
   
   set  v_date = to_date( '2014-01-01' ,'yyyy-mm-dd'); 
   set v_date_str =  to_char( v_date ,'yyyy-mm-dd') ;
   set num = 100000;
   
   REPEAT   
       
              set i = 1 ;
			  
              REPEAT  
			  set num=num+1;
		  
		 SET sqlStr =' INSERT INTO IALClaimCheck 
		 (CLAIMQUERYNO,USERCODE,AREACODE,COMPANYCODE,POLICYCONFIRMNO,REGISTNO,ACCIDENTTYPE,INDEMNITYDUTY,OPTIONTYPE,ACCIDENTCAUSE,INPUTDATE,VALIDSTATUS,PAYSELFFLAG,CHECKSTARTTIME,CHECKENDTIME,UPDATETIME,RISKQUERYSEQUENCENO,RISKTYPE,ISPERSONINJURED,ISPROTECTLOSS,ISSINGLEACCIDENT,INCOME_DATE) VALUES  ' 
         ||  '  ( ''50ABIC3700200012345659786'||''||  num ||''',''PAIC4200'',''420000'',''PAIC'',''02PAIC420018001544058971729104'',''PAIC201801420004'',''100'',''1'', ''1'',''A19030'',current timestamp,''1'',''0'',current timestamp,current timestamp,current timestamp,'''','''',''1'',''1'',''0'','''||  v_date_str ||''' ) ' ; 
               EXECUTE IMMEDIATE sqlStr;       
               set i = i +1 ;   
               UNTIL ( i > 101)  
               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 > 20180101)  
   END REPEAT;  
END   
/