oracle 存储过程造数据,oracle存储过程生成数据

最新推荐文章于 2024-09-09 08:52:02 发布

学术与青椒

最新推荐文章于 2024-09-09 08:52:02 发布

阅读量440

点赞数

文章标签： oracle 存储过程造数据

declare

i integer;

m integer;

clientEquivId number;

clientId number;

personnameId number;

peridentId number;

addressId number;

contactmethodId number;

risklevelId number;

relpersonId number;

sourceclientNO number;

clientlevelId number;

idnum number;

ctcmthNum number;

begin

i := 11;

m := 1;

clientEquivId:=17688222000000000;

clientId:=11886555000000000;

personnameId:=17082600000170000;

peridentId:=17072600000170000;

addressId:=17112100100000000;

contactmethodId:=17052600000000000;

risklevelId:=17044400000000000;

relpersonId:=17110100000236000;

sourceclientNO:=115600000000000;

clientlevelId:=11710260000000000;

idnum:=1100000000;

ctcmthNum:=15234176666;

loop

INSERT INTO ecif.clientEquiv (CLIENT_EQUIV_ID,CLIENT_ID,SOURCE_SYS_TP_CD,SOURCE_CLIENT_NO,DESCRIPTION,ACTIVE_IND,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(clientEquivId+i,clientId+i,'1000001',to_char(sourceclientNO+i),NULL,'Y','00010001','A1234',TO_DATE('2017-07-26 15:58:30','YYYY-MM-DD HH24:MI:SS'),1707260000446002,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.CLIENT (CLIENT_ID,CLIENT_NAME,ID_TP_CD,ID_NUM,NICK_NAME,CLIENT_CODE,CREATE_INST,CREATE_TLR,CREATE_TMS,DEVELOP_EMP_NO,CLIENT_ST_TP_CD,CUS_LVL_TP_CD,LANG_TP_CD,INFO_INTEG_FLAG,PREM_COUNTRY_TP_CD,CHECK_IND,OFFSHORE_IND,RESIDENT_TP_CD,QUERY_PWD,CORE_IND,CANCEL_INST,CANCEL_TLR,CANCEL_TMS,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(clientId+i,CONCAT('哈利波特',i),'1000001',CONCAT('PIBS',idnum+i),CONCAT('个人客户',i),'P','张家港银行','123',current_date,'3001','1000001','1','1000001','N','1000044','Y','1','1000001','123456','y',NULL,NULL,NULL,NULL,'00010001','A1234',current_date,1707260000446002,current_date);

INSERT INTO ecif.person (CLIENT_ID,MARITAL_TP_CD,COUNTRY_TP_CD,ZONE_TP_CD,NATION_TP_CD,EDU_TP_CD,DEGREE_TP_CD,GENDER_TP_CD,BIRTH_DT,DECEASED_DT,SVC_LVL_TP_CD,SVC_LVL_DT,ONLINE_CHECK_IND,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(clientId+i,'1000001','1000001',NULL,'1000001','1000001','1000001','1000001',TO_DATE('1985-12-12 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,NULL,TO_DATE('1985-12-12 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,'Y',NULL,'00010001','A1234',TO_DATE('2017-07-26 15:58:30','YYYY-MM-DD HH24:MI:SS'),1707260000446002,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.xperson (CLIENT_ID,TITLE,COMPANY_NAME,ORG_CHARTIC_TP_CD,INDUSTRY_TP_CD,DEPARTMENT,EMP_NO,CMPN_INST,OCCUPATION_TP_CD,POSITION_TP_CD,TITLE_TP_CD,BANK_EMP_IND,DOMICILE,MARITAL_DT,HOUSE_TP_CD,DOMICILE_LO,FAMILY_PPN,SUPPORT_PPN,CHILD_COUNT,CHK_RSLT_TP_CD,POLICE_STATION_LO,CLIENT_FLAG,INVEST_INTENTION,INVEST_DIRECTION,GRDT_DT,GRDT_SCHOOL,MAJOR,POLITICAL_TP_CD,RELIGION_TP_CD,ACTIVE_IND,INCREASE_IND,CDCHKRSLT_TP,"AS",LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(clientId+i,CONCAT('小哈',i),'张家口银行','1000001','1000001','1000001','1000001','1000001','1000001','1000001','1000001','1','北京市朝阳门 ',TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1000001','天上人间',55,55,55,NULL,'朝阳门',1,1354345,123123,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),'哈弗','自动化',NULL,NULL,'Y',NULL,NULL,NULL,NULL,'00010001','A1234',TO_DATE('2017-07-26 15:58:30','YYYY-MM-DD HH24:MI:SS'),1707260000446002,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.PERSONNAME (PERSON_NAME_ID,CLIENT_ID,NAME_USAGE_TP_CD,NAME,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(personnameId+i,clientId+i,'1000013',CONCAT('王五',i),'Y',NULL,'00010001','A1234',TO_DATE('2017-07-26 15:58:30','YYYY-MM-DD HH24:MI:SS'),1707260000446002,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.perident (PERIDENT_ID,CLIENT_ID,ID_TP_CD,ID_NUM,ID_NAME,ID_ADDRESS,ID_POST_CODE,LAST_CHECK_DT,START_DT,EXPIRY_DT,ISSUE_ZONE_TP_CD,ISSUE_INST,ISSUE_DT,PIC1URL,PIC2URL,PIC3URL,ID_PHOTO_RSRV_IND,ID_PHOTO_RSRV_INST,ID_PHOTO_SAVE_DT,ID_PHOTO_NO,OPEN_IDENT_IND,EXAMINE_TP_CD,ID_PHOTO_RSRV_DT,RESERVE1,RESERVE2,SOURCE_SYS_TP_CD,ID_SEQ_NO,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(peridentId+i,clientId+i,'1000005',CONCAT('PIBS',idnum+i),'东城区','西直门','123123',TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,'公安局',TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),'昌平区',NULL,NULL,'1','赵六',TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'),'123456','Y',NULL,NULL,NULL,NULL,NULL,NULL,'Y',NULL,'00010001','A1234',TO_DATE('2017-07-26 15:58:30','YYYY-MM-DD HH24:MI:SS'),1707260000446002,TO_DATE('2017-07-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.address (ADDRESS_ID,CLIENT_ID,ADDR_USAGE_TP_CD,ADDRESS,ADDR_ONE_CODE,ADDR_TWO_CODE,ADDR_THREE_CODE,POST_CODE,START_TMS,END_TMS,SOURCE_SYS_TP_CD,DESCRIPTION,ACTIVE_IND,CLIENT_ATTR_IND,PREF_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(addressId+i,clientId+i,'1000001','北京市','北京市','大兴区','西红门镇','123',TO_DATE('2017-11-09 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2017-11-09 00:00:00','YYYY-MM-DD HH24:MI:SS'),NULL,NULL,'Y','Y',NULL,NULL,'00010001','A1234',TO_DATE('2017-11-09 10:52:29','YYYY-MM-DD HH24:MI:SS'),1711090001434001,TO_DATE('2017-11-09 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.ContactMethod (CONTACT_METHOD_ID,CLIENT_ID,CTCMTH_TP_CD,CTCMTH_NUM,SOURCE_SYS_TP_CD,PREF_IND,START_TMS,END_TMS,DESCRIPTION,ACTIVE_IND,CLIENT_ATTR_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(contactmethodId+i,clientId+i,'1000002',varchar(ctcmthNum+i),NULL,NULL,NULL,NULL,NULL,'Y','Y',NULL,'00010001','A1234',TO_DATE('2017-05-25 17:45:33','YYYY-MM-DD HH24:MI:SS'),1705250000076002,TO_DATE('2017-05-25 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO ecif.risklevel (RISK_LEVEL_ID,CLIENT_ID,RISK_LEVEL_TP_CD,RISK_LEVEL_VALUE,ESTIMATE_INST,ESTIMATE_USER,REMARK,ESTIMATE_DT,START_TMS,END_TMS,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(risklevelId+i,clientId+i,'1000000','高','12','1234','1111',TO_DATE('2017-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2017-11-01 06:40:48','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2017-11-01 06:40:48','YYYY-MM-DD HH24:MI:SS'),'1',TO_DATE('2017-11-01 06:40:48','YYYY-MM-DD HH24:MI:SS'),'00010001','A1234',TO_DATE('2017-11-08 17:35:15','YYYY-MM-DD HH24:MI:SS'),1711080001416001,TO_DATE('2017-11-08 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO relperson (REL_PERSON_ID,REL_TP_CD,CLIENT_ID,BANK_ACCOUNTRELATION_ID,CHINESE_NAME,ENGLISH_NAME,CHINESE_SHORT_NAME,ENGLISH_SHORT_NAME,ID_TP_CD,ID_NUM,ID_EXPIRY_DT,GENDER_TP_CD,MOBILE,TELEPHONE,COMPANY_NAME,POSITION_TP_CD,ADDRESS,POST_CODE,FAX,EMAIL,COUNTRY_TP_CD,BIRTH_DT,HIGHESTEDU_TP_CD,ONLINE_CHECK_IND,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(relpersonId+i,'1000001',clientId+i,'1','蔡文姬','Mr.vegetable','菜菜','vegetable','1000001','1',TO_DATE('2017-06-10 00:00:00','YYYY-MM-DD HH24:MI:SS'),'1','13872373647','18829212299','csii','1','北京儿胡同儿','100001','666','78687@qq.com','010',TO_DATE('2017-06-10 00:00:00','YYYY-MM-DD HH24:MI:SS'),'3','3','1',TO_DATE('2017-06-10 11:07:03','YYYY-MM-DD HH24:MI:SS'),'00010001','A1234',TO_DATE('2017-06-10 11:07:03','YYYY-MM-DD HH24:MI:SS'),NULL,TO_DATE('2017-10-26 00:00:00','YYYY-MM-DD HH24:MI:SS'));

INSERT INTO clientlevel (CLIENT_LEVEL_ID,CLIENT_ID,LEVEL_TP_CD,LEVEL_VALUE,ESTIMATE_INST,ESTIMATE_USER,ESTIMATE_DT,START_TMS,END_TMS,REMARK,ACTIVE_IND,LAST_SRC_UPDATE_TMS,LAST_UPDATE_INST,LAST_UPDATE_TLR,LAST_UPDATE_TMS,LAST_UPDATE_TXN_ID,LAST_ACCOUNT_DT) VALUES

(clientlevelId+i,clientId+i,'1000001','高','北京评估集团','张三',current_date,current_date,current_date,'评估的好',NULL,NULL,'00010001','A1234',TO_DATE('2017-11-08 16:44:47','YYYY-MM-DD HH24:MI:SS'),1711080001404001,TO_DATE('2017-11-08 00:00:00','YYYY-MM-DD HH24:MI:SS'));

i:=i+1;

m:=m+1;

if m>100000 then

commit;

m:=1;

end if;

exit when i >10000000 ;

end loop;

commit;

end;