declare
i integer;--定义变量
j integer;--外键ID关联
k VARCHAR2(19);--编号生成使用
l VARCHAR2(19);--编号生成使用
m CLOB;--报文
begin
i :=1;
loop
--插入数据
--现券
select to_char(sysdate,'yyyymmdd') into k from dual;
select round(dbms_random.value(1,9)*100000) into l from dual;
k:='CBT'||k||l;
m:='<message><header><BeginString>IMIX.1.0</BeginString><BodyLength>1842</BodyLength><MsgSeqNum>60</MsgSeqNum><MsgType>8</MsgType><SenderCompID>CFETS-RMB-CSTP</SenderCompID><SendingTime>20181116-07:20:05.635</SendingTime><TargetCompID>303836</TargetCompID><TargetSubID>zjwxhtcstp1</TargetSubID><OnBehalfOfCompID>CFETS-RMB</OnBehalfOfCompID><DeliverToCompID>303836</DeliverToCompID><DeliverToSubID>zjwxhtcstp1</DeliverToSubID></header><body><ClOrdID>995323542380875003</ClOrdID><ExecID>'||k||'</ExecID><LastQty>2000000</LastQty><Price>97.0000</Price><SecurityID>111697310M</SecurityID><Side>4</Side><Symbol>16东莞农村商业银行CD070_M</Symbol><Text>-</Text><TransactTime>20181116-15:20:13.107</TransactTime><SettlType>1</SettlType><SettlDate>20181116</SettlDate><TradeDate>20181116</TradeDate><QuoteID>201811160420000185</QuoteID><SettlCurrAmt>1956445.04</SettlCurrAmt><SettlCurrency>CNY</SettlCurrency><ExecType>F</ExecType><AccruedInterestAmt>0.82225</AccruedInterestAmt><NoStipulations>1</NoStipulations><NoPartyIDs>2</NoPartyIDs><DeliveryType>0</DeliveryType><AccruedInterestTotalAmt>16445.04</AccruedInterestTotalAmt><ContingencyIndicator>N</ContingencyIndicator><DirtyPrice>97.8223</DirtyPrice><DealTransType>0</DealTransType><MarketIndicator>4</MarketIndicator><RemarkIndicator>N</RemarkIndicator><TradeCashAmt>1940000.00</TradeCashAmt><TradeMethod>1</TradeMethod><TradeTime>15:20:13</TradeTime><TradeType>1</TradeType><CashBondIndicator>Y</CashBondIndicator><DataCategoryIndicator>0</DataCategoryIndicator><ClearingMethod>13</ClearingMethod><NoStipulations><StipulationType>Yield2</StipulationType><StipulationValue>0.8595</StipulationValue></NoStipulations><NoPartyIDs><PartyID>304162</PartyID><PartyRole>119</PartyRole><NoContactInfos>1</NoContactInfos><NoPartySubIDs>14</NoPartySubIDs><NoPartySubIDs><PartySubID>上海银行对手方</PartySubID><PartySubIDType>101</PartySubIDType><PartySubID>上海银行对手方</PartySubID><PartySubIDType>126</PartySubIDType><PartySubID>31001550400050000076</PartySubID><PartySubIDType>15</PartySubIDType><PartySubID>中国建设银行上海市分行营业部</PartySubID><PartySubIDType>110</PartySubIDType><PartySubID>-</PartySubID><PartySubIDType>112</PartySubIDType><PartySubID>香港上海汇丰银行有限公司2</PartySubID><PartySubIDType>22</PartySubIDType><PartySubID>上海清算所</PartySubID><PartySubIDType>111</PartySubIDType><PartySubID>B0054321</PartySubID><PartySubIDType>10</PartySubIDType><PartySubID>公司账户六八</PartySubID><PartySubIDType>23</PartySubIDType><PartySubID>CFETS</PartySubID><PartySubIDType>29</PartySubIDType><PartySubID>xxxxxxxx</PartySubID><PartySubIDType>6</PartySubIDType><PartySubID>广发基金量子对冲1号</PartySubID><PartySubIDType>125</PartySubIDType><PartySubID>广发基金公司工行广发量子对冲资产管理计划1号</PartySubID><PartySubIDType>124</PartySubIDType><PartySubID>广发基金量子对冲1号</PartySubID><PartySubIDType>102</PartySubIDType></NoPartySubIDs><NoContactInfos><ContactInfoID>12345678</ContactInfoID><ContactInfoIDType>6</ContactInfoIDType></NoContactInfos><PartyID>303836</PartyID><PartyRole>120</PartyRole><NoContactInfos>1</NoContactInfos><NoPartySubIDs>14</NoPartySubIDs><NoPartySubIDs><PartySubID>上海银行首席</PartySubID><PartySubIDType>101</PartySubIDType><PartySubID>上海银行首席</PartySubID><PartySubIDType>126</PartySubIDType><PartySubID>30005611011</PartySubID><PartySubIDType>15</PartySubIDType><PartySubID>上海银行软件</PartySubID><PartySubIDType>110</PartySubIDType><PartySubID>130005611011</PartySubID><PartySubIDType>112</PartySubIDType><PartySubID>上海银行上清所</PartySubID><PartySubIDType>22</PartySubIDType><PartySubID>上海清算所</PartySubID><PartySubIDType>111</PartySubIDType><PartySubID>B0000056</PartySubID><PartySubIDType>10</PartySubIDType><PartySubID>上海银行软件</PartySubID><PartySubIDType>23</PartySubIDType><PartySubID>CFETS</PartySubID><PartySubIDType>29</PartySubIDType><PartySubID>zjwxht地址测试1</PartySubID><PartySubIDType>6</PartySubIDType><PartySubID>第一创业证券创金灵活成长1期</PartySubID><PartySubIDType>125</PartySubIDType><PartySubID>第一创业证券兴业银行创金灵活成长1期集合资产管理计划</PartySubID><PartySubIDType>124</PartySubIDType><PartySubID>第一创业证券创金灵活成长1期</PartySubID><PartySubIDType>102</PartySubIDType></NoPartySubIDs><NoContactInfos><ContactInfoID>0571-11223344</ContactInfoID><ContactInfoIDType>6</ContactInfoIDType></NoContactInfos></NoPartyIDs></body><trailer><SignatureLength>60</SignatureLength><CheckSum>076</CheckSum></trailer></message>';
select SEQ_CBMSELFDEALT.nextval into j from dual;
Insert into FICSTP_CBMSELFDEALT (ID,CONVERTTIME,CONTACTINFOIDIN,CONTACTINFOIDOUT,CONTACTINFOIDTYPEIN,CONTACTINFOIDTYPEOUT,COUNTERID,COUNTERPARTYID,COUNTERTRADERGROUP,COUNTERTRADERNAME,EXECID,ISCONFIRMED,MARKETINDICATOR,PARTYIDIN,PARTYIDOUT,PARTYSUBIDIN_10,PARTYSUBIDIN_101,PARTYSUBIDIN_102,PARTYSUBIDIN_107,PARTYSUBIDIN_108,PARTYSUBIDIN_109,PARTYSUBIDIN_110,PARTYSUBIDIN_111,PARTYSUBIDIN_112,PARTYSUBIDIN_116,PARTYSUBIDIN_117,PARTYSUBIDIN_121,PARTYSUBIDIN_122,PARTYSUBIDIN_124,PARTYSUBIDIN_125,PARTYSUBIDIN_126,PARTYSUBIDIN_128,PARTYSUBIDIN_132,PARTYSUBIDIN_15,PARTYSUBIDIN_155,PARTYSUBIDIN_192,PARTYSUBIDIN_194,PARTYSUBIDIN_196,PARTYSUBIDIN_22,PARTYSUBIDIN_23,PARTYSUBIDIN_239,PARTYSUBIDIN_240,PARTYSUBIDIN_241,PARTYSUBIDIN_256,PARTYSUBIDIN_257,PARTYSUBIDIN_29,PARTYSUBIDIN_6,PARTYSUBIDOUT_10,PARTYSUBIDOUT_101,PARTYSUBIDOUT_102,PARTYSUBIDOUT_107,PARTYSUBIDOUT_108,PARTYSUBIDOUT_109,PARTYSUBIDOUT_110,PARTYSUBIDOUT_111,PARTYSUBIDOUT_112,PARTYSUBIDOUT_116,PARTYSUBIDOUT_117,PARTYSUBIDOUT_121,PARTYSUBIDOUT_122,PARTYSUBIDOUT_124,PARTYSUBIDOUT_125,PARTYSUBIDOUT_126,PARTYSUBIDOUT_128,PARTYSUBIDOUT_132,PARTYSUBIDOUT_15,PARTYSUBIDOUT_155,PARTYSUBIDOUT_192,PARTYSUBIDOUT_194,PARTYSUBIDOUT_196,PARTYSUBIDOUT_22,PARTYSUBIDOUT_23,PARTYSUBIDOUT_239,PARTYSUBIDOUT_240,PARTYSUBIDOUT_241,PARTYSUBIDOUT_256,PARTYSUBIDOUT_257,PARTYSUBIDOUT_29,PARTYSUBIDOUT_6,SELFID,SELFPARTYID,SELFTRADERGROUP,SELFTRADERNAME,ACCRUEDINTERESTAMT,ACCRUEDINTERESTTOTALAMT,CASHBONDINDICATOR,CLORDID,CLEARINGMETHOD,CONTINGENCYINDICATOR,DATACATEGORYINDICATOR,DEALTRANSTYPE,DELIVERYTYPE,DIRTYPRICE,EXECTYPE,LASTQTY,ORDERID,PRICE,PRINCIPAL,QUOTEID,REFERENCE,REMARKINDICATOR,SECURITYID,SETTLCURRAMT,SETTLCURRENCY,SETTLDATE,SETTLTYPE,SIDE,SYMBOL,TERMTOMATURITY,TEXT,TOTALPRINCIPAL,TRADECASHAMT,TRADEDATETIME,TRADEMETHOD,TRADETYPE,TRANSACTTIME,IMIXXML,INSERTTIME,COUNTERPARTYNAME,SELFPARTYNAME) values (j,to_timestamp('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),'0571-11223344','-85108888','电话','电话','100003','ABCI','CFETS','上海银行对手方',k,0,'4','303836','100003','0030005611011','上海银行交易接口','第一创业证券创金灵活成长1期',null,null,null,'上海银行','国债登记结算公司','130005611011',null,null,null,null,'第一创业证券兴业银行创金灵活成长1期集合资产管理计划','第一创业证券创金灵活成长1期','上海银行交易接口',null,null,'30005611011',null,null,null,null,'上海银行国债登','上海银行',null,null,null,null,null,'CFETS','zjwxht地址测试1','125649412','上海银行对手方','ABCI',null,null,null,'测试信息','国债登记结算公司','11111',null,null,null,null,'中国农业银行股份有限公司','农业银行','上海银行对手方',null,null,'777',null,null,null,null,'国债测试1号','888',null,null,null,null,null,'CFETS','北京市建国门内大街69号农业银行资金交易中心','303836','第一创业证券创金灵活成长1期','CFETS','上海银行接口','0.28558','85673.08','现券','ODjwxhttrd57750384','自行清算','非应急','0','录入','券款对付','102.6756','已成交','30000000',null,'102.3900',null,'201811160440006581',null,'无','010011','30802673.08','CNY','20181119','T+1','买入','01国债11',null,'-',null,'30717000.00',to_date('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),'一次点击成交','全部','20181116-14:32:51.036', m,to_timestamp('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),null,null);--select max(id) into j from FICSTP_CBMSELFDEALT;
Insert into FICSTP_CBMNOSTIPULATIONS (ID,STIPULATIONVALUE,STIPULATIONTYPE,CBMNOSTIPULATIONSUUID) values (SEQ_CBMNoStipulations.nextval,'4.5686','到期收益率',j);--外汇远期
select round(dbms_random.value(1,9)*1000) into l from dual;
k:=l||'-';
select round(dbms_random.value(1,9)*1000) into l from dual;
k:=k||l||'-';
select round(dbms_random.value(1,9)*1000) into l from dual;
k:=k||l||'-';
select dbms_random.string('X',2) into l from dual;
k:=k||l;
m:='<message><header><BeginString>IMIX.1.0</BeginString><BodyLength>1666</BodyLength><MsgSeqNum>15</MsgSeqNum><MsgType>8</MsgType><SenderCompID>CFETS-FXCSTP</SenderCompID><SendingTime>20180723-05:55:39.893</SendingTime><TargetCompID>BCOH</TargetCompID><TargetSubID>wxht.settle@bcoh</TargetSubID><OnBehalfOfCompID>CFETS-FXCSTP</OnBehalfOfCompID><NoHops>2</NoHops><NoHops><HopCompID>Hub-BKSH_1937</HopCompID><HopRefID>17</HopRefID><HopSendingTime>20180723-05:55:11.852</HopSendingTime><HopCompID>BKSH_1937</HopCompID><HopRefID>17</HopRefID><HopSendingTime>20180723-05:55:11.856</HopSendingTime></NoHops></header><body><Currency>AUD</Currency><ExecID>'||k||'</ExecID><LastPx>5.019102</LastPx><LastQty>1000000.00</LastQty><Side>4</Side><Symbol>AUD.CNY</Symbol><TransactTime>20180723-13:23:02</TransactTime><SettlType>3W</SettlType><SettlDate>20180723</SettlDate><TradeDate>20180723</TradeDate><ExecType>F</ExecType><LastSpotRate>5.0191</LastSpotRate><LastForwardPoints>0.02</LastForwardPoints><NetGrossInd>2</NetGrossInd><NoPartyIDs>2</NoPartyIDs><CalculatedCcyLastQty>5019102.00</CalculatedCcyLastQty><DateConfirmed>20180723</DateConfirmed><Currency1>AUD</Currency1><Currency2>CNY</Currency2><DealTransType>0</DealTransType><MarketIndicator>14</MarketIndicator><RiskLastQty>737073.50</RiskLastQty><TradeInstrument>Q</TradeInstrument><TradeMethod>4</TradeMethod><TradeTime>13:23:02</TradeTime><IsExDeltaIndic>N</IsExDeltaIndic><CFETSCnfmIndicator>N</CFETSCnfmIndicator><ContraCurrency>CNY</ContraCurrency><Currency1Amt2>1000000.00</Currency1Amt2><Currency2Amt2>5019102.00</Currency2Amt2><AccessMethod>UI</AccessMethod><TradingMode>1</TradingMode><NoPartyIDs><PartyID>100000531000000102001</PartyID><PartyRole>114</PartyRole><NoPartySubIDs>13</NoPartySubIDs><NoPartySubIDs><PartySubID>bcoh.dealer@bcoh</PartySubID><PartySubIDType>101</PartySubIDType><PartySubID>BCOH</PartySubID><PartySubIDType>102</PartySubIDType><PartySubID>Bank of Communications</PartySubID><PartySubIDType>5</PartySubIDType><PartySubID>NATIONAL AUSTRALIA BANK, MELBOURNE</PartySubID><PartySubIDType>110</PartySubIDType><PartySubID>NATAAU33033</PartySubID><PartySubIDType>138</PartySubIDType><PartySubID>BANK OF COMMUNICATIONS,H.O.</PartySubID><PartySubIDType>23</PartySubIDType><PartySubID>COMMCNSH</PartySubID><PartySubIDType>16</PartySubIDType><PartySubID>1803010286500</PartySubID><PartySubIDType>15</PartySubIDType><PartySubID>交通银行股份有限公司</PartySubID><PartySubIDType>140</PartySubIDType><PartySubID>115500722</PartySubID><PartySubIDType>173</PartySubIDType><PartySubID>交通银行股份有限公司</PartySubID><PartySubIDType>142</PartySubIDType><PartySubID>301290000007</PartySubID><PartySubIDType>143</PartySubIDType><PartySubID>BCOH</PartySubID><PartySubIDType>255</PartySubIDType></NoPartySubIDs><PartyID>100000211000000101001</PartyID><PartyRole>113</PartyRole><NoPartySubIDs>14</NoPartySubIDs><NoPartySubIDs><PartySubID>bcoh.dealer@abci</PartySubID><PartySubIDType>101</PartySubIDType><PartySubID>ABCI</PartySubID><PartySubIDType>102</PartySubIDType><PartySubID>Agricultural Bank of China Limited</PartySubID><PartySubIDType>5</PartySubIDType><PartySubID>NATIONAL AUSTRALIA BANK LIMITED, MELBOURNE </PartySubID><PartySubIDType>110</PartySubIDType><PartySubID>NATAAU33033</PartySubID><PartySubIDType>138</PartySubIDType><PartySubID>AGRICULTURAL BANK OF CHINA, THE HEAD OFFICE</PartySubID><PartySubIDType>23</PartySubIDType><PartySubID>ABOCCNBJ</PartySubID><PartySubIDType>16</PartySubIDType><PartySubID>1803072583500</PartySubID><PartySubIDType>15</PartySubIDType><PartySubID>中国农业银行资金清算中心</PartySubID><PartySubIDType>140</PartySubIDType><PartySubID>110400389</PartySubID><PartySubIDType>173</PartySubIDType><PartySubID>中国农业银行</PartySubID><PartySubIDType>142</PartySubIDType><PartySubID>103100000026</PartySubID><PartySubIDType>143</PartySubIDType><PartySubID>请转中国农业银行北京海淀支行,账号ABC123456789011。</PartySubID><PartySubIDType>145</PartySubIDType><PartySubID>ABCI</PartySubID><PartySubIDType>255</PartySubIDType></NoPartySubIDs></NoPartyIDs></body><trailer><SignatureLength>57</SignatureLength><CheckSum>178</CheckSum></trailer></message>';
Insert into FXCSTP_FOWSELFDEALT (ID,CONVERTTIME,COUNTERID,COUNTERSHORTNAME,COUNTERSIDE,EXECID,ISCONFIRMED,MARKETINDICATOR,PARTYIDIN,PARTYIDOUT,PARTYSUBIDIN_101,PARTYSUBIDIN_102,PARTYSUBIDIN_110,PARTYSUBIDIN_138,PARTYSUBIDIN_139,PARTYSUBIDIN_140,PARTYSUBIDIN_142,PARTYSUBIDIN_143,PARTYSUBIDIN_144,PARTYSUBIDIN_145,PARTYSUBIDIN_146,PARTYSUBIDIN_15,PARTYSUBIDIN_16,PARTYSUBIDIN_173,PARTYSUBIDIN_207,PARTYSUBIDIN_208,PARTYSUBIDIN_209,PARTYSUBIDIN_210,PARTYSUBIDIN_211,PARTYSUBIDIN_212,PARTYSUBIDIN_213,PARTYSUBIDIN_214,PARTYSUBIDIN_23,PARTYSUBIDIN_255,PARTYSUBIDIN_5,PARTYSUBIDOUT_101,PARTYSUBIDOUT_102,PARTYSUBIDOUT_110,PARTYSUBIDOUT_138,PARTYSUBIDOUT_139,PARTYSUBIDOUT_140,PARTYSUBIDOUT_142,PARTYSUBIDOUT_143,PARTYSUBIDOUT_144,PARTYSUBIDOUT_145,PARTYSUBIDOUT_146,PARTYSUBIDOUT_15,PARTYSUBIDOUT_16,PARTYSUBIDOUT_173,PARTYSUBIDOUT_207,PARTYSUBIDOUT_208,PARTYSUBIDOUT_209,PARTYSUBIDOUT_210,PARTYSUBIDOUT_211,PARTYSUBIDOUT_212,PARTYSUBIDOUT_213,PARTYSUBIDOUT_214,PARTYSUBIDOUT_23,PARTYSUBIDOUT_255,PARTYSUBIDOUT_5,SELFID,SELFSHORTNAME,SELFSIDE,ACCESSMETHOD,CFETSCNFMINDICATOR,CALCULATEDCCYLASTQTY,CONTRACURRENCY,CURRENCY,CURRENCY1,CURRENCY1AMT2,CURRENCY2,CURRENCY2AMT2,DATECONFIRMED,DEALTRANSTYPE,DELIVERYTYPE,EXECTYPE,ISEXDELTAINDIC,ISOPSPOTINDIC,LASTFORWARDPOINTS,LASTPX,LASTQTY,LASTSPOTRATE,NETGROSSIND,REFERENCE,RISKLASTQTY,SECURITYTERM,SETTLDATE,SETTLTYPE,SIDE,SYMBOL,TRADEDATETIME,TRADEINSTRUMENT,TRADEMETHOD,TRADINGMODE,TRANSACTTIME,IMIXXML,INSERTTIME,COUNTERPARTYNAME,SELFPARTYNAME) values (SEQ_FOWSELFDEALT.nextval,to_timestamp('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),'100000211000000101001','ABCI','买',k,0,'外汇远期','100000211000000101001','100000531000000102001','bcoh.dealer@abci','ABCI','NATIONAL AUSTRALIA BANK LIMITED, MELBOURNE ','NATAAU33033',null,'中国农业银行资金清算中心','中国农业银行','103100000026',null,'请转中国农业银行北京海淀支行,账号ABC123456789011。',null,'1803072583500','ABOCCNBJ','110400389',null,null,null,null,null,null,null,null,'AGRICULTURAL BANK OF CHINA, THE HEAD OFFICE','ABCI','Agricultural Bank of China Limited','bcoh.dealer@bcoh','BCOH','NATIONAL AUSTRALIA BANK, MELBOURNE','NATAAU33033',null,'交通银行股份有限公司','交通银行股份有限公司','301290000007',null,null,null,'1803010286500','COMMCNSH','115500722',null,null,null,null,null,null,null,null,'BANK OF COMMUNICATIONS,H.O.','BCOH','Bank of Communications','100000531000000102001','BCOH','卖','UI','其他场所确认','5019102.00','CNY','AUD','AUD','1000000.00','CNY','5019102.00','20180723','录入',null,'已成交','否','否','0.02','5.019102','1000000.00','5.0191','双边全额清算',null,'737073.50',null,'20180723','3W','卖','AUD.CNY',to_date('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),'QDM','RFQ','询价','20180723-13:23:02',m,to_timestamp('2018-12-04 09:00:00','YYYY-MM-DD HH24:MI:SS'),null,null);
i := i +1;--停止条件
exit when i >=10001;
end loop;
commit;
end;