A SQL script that generates large amounts of data in batches

Business logic: The use of cursors from the database of existing table data, query out some properties to insert into the target table, in the target table to generate large quantities of data, SQL implementation is as follows:

Create or Replace procedure Add_subscription_c (i_count in number,--I_count input value to loop insert record
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; --Ordering party number
V_usercode Zxdbm_ismp.ssrv_subscription.usercode%type; --Use number
V_payuser Zxdbm_ismp.ssrv_subscription.payuser%type; --Pay number
V_servicekeyid Zxdbm_ismp.ssys_service_type.servicekeyid%type; --Internal business key ID
V_status Zxdbm_ismp.ssrv_subscription.status%type; --State 0-to be effective; 1-normal; 2: Pause + pending entry into force; 3: Pause; 4: to be invalidated; 5: suspend + pending expiration; 6: Log Off,
V_subscriptionindex Zxdbm_ismp.ssrv_subscription.subscriptionindex%type; --order number of relationship orders
V_serviceindex Zxdbm_ismp.ssrv_subscription.serviceindex%type; --Business serial number
V_serviceid Zxdbm_ismp.ssrv_subscription.serviceid%type; --New Business ID
V_productid Zxdbm_ismp.ssrv_subscription.productid%type; --Product ID
V_productindex Zxdbm_ismp.ssrv_subscription.productindex%type; --Product serial number
V_cpid Zxdbm_ismp.ssrv_subscription.cpid%type; --cpid
V_cpindex Zxdbm_ismp.ssrv_subscription.cpindex%type; --CP Serial Number
V_corpindex Zxdbm_ismp.ssrv_subscription.corpindex%type; --Group serial number
V_corpid Zxdbm_ismp.ssrv_subscription.corpid%type; --Group ID
V_msisdntype Zxdbm_ismp.ssrv_subscription.msisdntype%type; --User number type, 1-MSISDN,2-PHS,3-PSTN,4-IPTV access number, broadband access number, 6-Fixed IP
V_servicetype Zxdbm_ismp.ssrv_subscription.servicetype%type; --type of business capability
V_newcode Zxdbm_ismp.ssrv_subscription.usercode%type;
V_subnumber number (10);
Type cur_t is REF CURSOR;
Cur_product cur_t;
Begin
--Defaults to 300 000000
V_startcount: = 30000000;
If I_startaccount is not NULL then
V_startcount: = I_startaccount;
End If;
--Construct the user number
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 input value is the number of loops V_count is the default 0
While V_count <= (V_startcount + I_count) loop
--Combination Build number
V_newcode: = V_usercode | | V_count;
--Find a ready-made data from the product table to get some property values from the Order relational table to insert the subscription relationship
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;
--the number of cycles per 1000 commit or v_count=v_startcount+
If mod (v_subnumber, 1000) = 0 or V_count = (V_startcount + i_count) Then
Commit
End If;
--on the basis of 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;