业务逻辑：利用游标从数据库中现有的表数据，查询出部分属性，来插入到目标表中，来在目标表中生成大批量的数据，SQL实现如下：
create or replace procedure add_subscription_c(i_count in number,-- i_count输入值为循环插入记录的次数
i_usersegment in varchar2,
i_startaccount in number,
o_resultcode out number,
o_resulstring out varchar2) as
v_count number;
v_startcount number;
v_sql varchar2(1024);
v_subscriber zxdbm_ismp.ssrv_subscription.subscriber%type; --订购方号码
v_usercode zxdbm_ismp.ssrv_subscription.usercode%type; --使用号码
v_payuser zxdbm_ismp.ssrv_subscription.payuser%type; --付费号码
v_servicekeyid zxdbm_ismp.ssys_service_type.servicekeyid%type; --内部业务键id
v_status zxdbm_ismp.ssrv_subscription.status%type; --状态0-待生效；1-正常；2：暂停+待生效；3：暂停；4：待失效；5：暂停＋待失效；6：注销，
v_subscriptionindex zxdbm_ismp.ssrv_subscription.subscriptionindex%type; --定购关系顺序号
v_serviceindex zxdbm_ismp.ssrv_subscription.serviceindex%type; --业务序号
v_serviceid zxdbm_ismp.ssrv_subscription.serviceid%type; --新业务ID
v_productid zxdbm_ismp.ssrv_subscription.productid%type; --产品ID
v_productindex zxdbm_ismp.ssrv_subscription.productindex%type; --产品序号
v_cpid zxdbm_ismp.ssrv_subscription.cpid%type; --CPID
v_cpindex zxdbm_ismp.ssrv_subscription.cpindex%type; --CP序号
v_corpindex zxdbm_ismp.ssrv_subscription.corpindex%type; --集团序号
v_corpid zxdbm_ismp.ssrv_subscription.corpid%type; --集团ID
v_msisdntype zxdbm_ismp.ssrv_subscription.msisdntype%type; --用户号码类型，1-msisdn,2-phs,3-pstn，4-IPTV接入号， 5-宽带接入号，6-固定IP
v_servicetype zxdbm_ismp.ssrv_subscription.servicetype%type; --业务能力类型
v_newCode zxdbm_ismp.ssrv_subscription.usercode%type;
v_subnumber number(10);
type cur_t is ref cursor;
cur_product cur_t;
begin
--缺省为300 000000
v_startcount := 30000000;
if i_startaccount is not null then
v_startcount := i_startaccount;
end if;
--构造用户号码
v_usercode := '191';
if i_usersegment is not null then
v_usercode := i_usersegment;
end if;
v_msisdntype := 1;
v_corpindex := 0;
v_corpid := ' ';
-------------------------------------------------------------------------
-------------------------------------------------------------------------
v_subnumber := v_startcount;
v_count := v_startcount;
-- i_count输入值为循环的次数 v_count为缺省0
while v_count <= (v_startcount + i_count) loop
--组合生成号码
v_newCode := v_usercode || v_count;
--从产品表中找一条现成的数据来得到订购关系表中一些属性值来插入订购关系
open cur_product for 'select servicetype,serviceindex,serviceid,productindex,productid,status,cpid,cpindex from v_ssrv_product where productid =111100009020000000030';
loop
fetch cur_product
into v_servicetype, v_serviceindex, v_serviceid, v_productindex, v_productid, v_status, v_cpid, v_cpindex;
sp_get_next_seq('slp_order_prdindex', v_subscriptionindex);
exit when cur_product%notfound;
insert into zxdbm_500.s500_user_subscription
(subscriptionindex,
subscriptionid,
servicetype,
serviceindex,
serviceid,
productindex,
productid,
corpindex,
corpid,
subscriber,
usercode,
payuser,
status,
msisdntype,
cpid,
cpindex)
values
(v_subscriptionindex,
v_newCode||v_subscriptionindex,
v_servicetype,
v_serviceindex,
v_serviceid,
v_productindex,
v_productid,
v_corpindex,
v_corpid,
v_newCode,
v_newCode,
v_newCode,
v_status,
v_msisdntype,
v_cpid,
v_cpindex);
v_subnumber := v_subnumber + 1;
end loop;
--每1000条commit一次 或 v_count=v_startcount+循环次数
if mod(v_subnumber, 1000) = 0 or v_count = (v_startcount + i_count) then
commit;
end if;
--在v_startcount的基础上+1
v_count := v_count + 1;
v_usercode := i_usersegment;
end loop;
o_resultcode := 0;
o_resulstring := v_count || 'Success';
commit;
exception
when others then
rollback;
o_resultcode := SQLCODE;
o_resulstring := v_newCode || ':' || SQLCODE || ':' || SQLERRM;
end ;