批量生成大数据量的SQL脚本

1、批量生成大数据量的SQL脚本-电脑资料业务逻辑：利用游标从数据库中现有的表数据，查询出部分属性，来插入到目标表中，来在目标表中生成大批量的数据，SQL实现如下：createorreplaceprocedureadd_subscription_c(i_countinnumb er,-i_count 输入值为循环插入记录的次数 i_usersegmentinvarchar2,i_startaccountinnumber,o_resultcode outnumber,o_resulstringoutvarchar2)asv_countnumber;v_startc ountnumber;v_sqlv

2、archar2(1024);v_subscriberzxdbm_ismp.ssrv_ subscription.subscriber%type;-订购方号码v_usercodezxdbm_ismp.ssrv_subscription.usercode%type;- 使用 号码v_payuserzxdbm_ismp.ssrv_subscription.payuser%type;-付费号码 v_servicekeyidzxdbm_ismp.ssys_service_type.servicekeyid%typ e;- 内部业务键 idv_statuszxdbm_ismp.ssrv_subscript

3、ion.status%type;-状态0-待生效； 1-正常；2：暂停+待生效；3：暂停；4：待失效；5：暂停待失效；6：注销，v_subscriptionindexzxdbm_ismp.ssrv_subscription.subscriptio nindex%type;- 定购关系顺序号 v_serviceindexzxdbm_ismp.ssrv_subscription.serviceindex%typ e;- 业务序号 v_serviceidzxdbm_ismp.ssrv_subscription.serviceid%type;-新业务 IDv_productidzxdbm_ismp.s

4、srv_ductid%type;- 产品 IDv_productindexzxdbm_ismp.ssrv_ductindex%t ype;- 产品序号 v_cpidzxdbm_ismp.ssrv_subscription.cpid%type;- CPIDv_cpindexzxdbm_ismp.ssrv_subscription.cpindex%type;-CP序号v_corpindexzxdbm_ismp.ssrv_subscription.corpindex%type;-集团序号v_corpidzxdbm_ismp.ssrv_s

5、ubscription.corpid%type;-集团IDv_msisdntypezxdbm_ismp.ssrv_subscription.msisdntype%type;- 用户号码类型， 1-msisdn,2-phs,3-pstn ， 4-IPTV 接入号， 5- 宽带接入号， 6- 固定IPv_servicetypezxdbm_ismp.ssrv_subscription.servicetype%typ e;- 业务能力类型v_newCodezxdbm_ismp.ssrv_subscription.usercode%type;v_subnu mbernumber(10);typecur_

6、tisrefcursor;cur_productcur_t;begin- 缺省为300000000v_startcount:=30000000;ifi_startaccountisnotnullthenv_startcount:=i_startaccount;endif;- 构造用户号码v_usercode:=191;ifi_usersegmentisnotnullthenv_usercode:=i_usersegment;endif;v_msisdntype:=1;v_corpindex:=0;v_corpid: v_subnumber:=v_startcount;v_count:=v_st

7、artcount;-i_count输入值为循环的次数v_count 为缺省0whilev_count=(v_startcount+i_count)loop-组合生成号码v_newCode:=v_usercode|v_count;- 从产品表中找一条现成的数据 来得到订购关系表中一些属性值来插入订购关系opencur_productforselectservicetype,serviceindex,serviceid ,productindex,productid,status,cpid,cpindexfromv_ssrv_produ ctwhereproductid=1111000090200

8、00000030;loopfetchcur_produc tintov_servicetype,v_serviceindex,v_serviceid,v_productinde x,v_productid,v_status,v_cpid,v_cpindex;sp_get_next_seq(sl p_order_prdindex,v_subscriptionindex);exitwhencur_product% notfound;insertintozxdbm_500.s500_user_subscription(subscri ptionindex,subscriptionid,service

9、type,serviceindex,servicei d,productindex,productid,corpindex,corpid,subscriber,usercode,payuser,status,msisdntype,cpid,cpindex)values(v_subscrip tionindex,v_newCode|v_subscriptionindex,v_servicetype,v_se rviceindex,v_serviceid,v_productindex,v_productid,v_corpind ex,v_corpid,v_newCode,v_newCode,v_newCode,v_status,v_msisdn type,v_cpid,v_cpindex);v_subnumber:=v_subnumber+1;endloop;- -每 1000 条 commit 一次或 v_count=v_startcount+ 循环次数 ifmod(v_subnumber,1000)=0orv_count=(v_startcount+i_count)th encommit;endif;-在 v_startcount 的基础上+1v_count:=v_count+1;v_usercode:=i_usersegment;endloop;o_r