1、存储过程的在头里面分别定义两个游标，在循环体内执行的游标用变量传参数进去 格式如下CURSOR 游标名(变量名 类型 ) is 语句
2、在存储过程体里面嵌套遍历两游标即可，跟C#的for循环一样的原理。
例子如下
create or replace procedure PriceManager_VassignImport(formKind_in in varchar2,
createuser_in in varchar2) is
r_vendorvassignbatch vendorvassignbatch%ROWTYPE;
r_VASSIGNFORMDETAIL VASSIGNFORMDETAIL%ROWTYPE;
ErrMsg varchar2(1024);
str_index number;
str_vendors varchar2(500);
str_vendor varchar2(20);
vendors_len number;
str_VASSIGNs varchar2(500);
str_VASSIGN varchar2(20);
VASSIGN_len number;
vendor_index number;
vassign_index number;
vendor_Total number;
vassign_total number;
vassign_sum number;
i number;
t number;
str_vassignformid varchar2(40);
str_ASSIGNCODE varchar2(40);
str_LINEID varchar2(40);
maxSEQ number;
countMaxSEQ number;
SEQIndex number;
CURSOR c_vendorvassignbatch IS
select t.*
from vendorvassignbatch t
where t.batchid = (select max(y.batchid) from vendorvassignbatch y)
and verifyflag = 1;
CURSOR c_temp(str_vassignformid varchar2,str_ASSIGNCODE varchar2) IS
select t.*
from VASSIGNFORMDETAIL t
where FORMID = str_vassignformid
and FORMNO = str_ASSIGNCODE
and ASSIGNCODE = str_ASSIGNCODE
order by ASSIGNSCALE desc;
BEGIN
for r_vendorvassignbatch in c_vendorvassignbatch LOOP
vendor_index := 0;
vendor_Total := 0; --供应商数量-1（冒号数量）
vassign_index := 0;
vassign_total := 0; --比例数量-1（冒号数量）
vassign_sum := 0;
vendors_len := 0; --供应商字符串的长度
VASSIGN_len := 0; --比例字符串的长度
ErrMsg := '';
--判断是否没有等号
str_index := instr(r_vendorvassignbatch.itemvassign, '=');
if str_index > 1 then
str_vendors := substr(r_vendorvassignbatch.itemvassign,
1,
str_index - 1);
str_VASSIGNs := substr(r_vendorvassignbatch.itemvassign,
str_index + 1,
length(r_vendorvassignbatch.itemvassign));
--判断供应商跟比例是否配合,判断等号左右两边的冒号数是否相等
while instr(str_vendors, ':') > 0 loop
vendor_Total := vendor_Total + 1;
vendors_len := instr(str_vendors, ':');
str_vendors := substr(str_vendors,
vendors_len + 1,
length(str_vendors) - vendors_len);
end loop;
while instr(str_VASSIGNs, ':') > 0 loop
vassign_total := vassign_total + 1;
VASSIGN_len := instr(str_VASSIGNs, ':');
str_VASSIGNs := substr(str_VASSIGNs,
VASSIGN_len + 1,
length(str_VASSIGNs) - VASSIGN_len);
end loop;
if vendor_Total = vassign_total then
if vendor_Total <> 0 then
if vendor_Total > 0 then
--判断供应商是否存在
vendor_index := 0;
str_vendors := substr(r_vendorvassignbatch.itemvassign,
1,
str_index - 1);
str_VASSIGNs := substr(r_vendorvassignbatch.itemvassign,
str_index + 1,
length(r_vendorvassignbatch.itemvassign));
--插入主表数据
select seq_vassignformid.nextval
into str_vassignformid
from dual;
str_ASSIGNCODE := 'VF' || to_char(sysdate, 'yymmdd ') ||
str_vassignformid;
insert into VASSIGNFORM
(FORMID,
FORMNO,
VASSIGNCODE,
EFFECTDATE,
INVALIDDATE,
COMPANYCODE,
PLANTCODE,
ITEMCODE,
CREATEUSER,
CREATEDATE,
CREATETIME,
COMMITDATE,
COMMITTIME,
FORMKIND,
REMARK,
BUYER)
values
(str_vassignformid,
str_ASSIGNCODE,
str_ASSIGNCODE,
to_number(to_char(r_vendorvassignbatch.effectivedate,
'yyyymmdd')),
to_number(to_char(r_vendorvassignbatch.ineffectivedate,
'yyyymmdd')),
r_vendorvassignbatch.COMPANYCODE,
r_vendorvassignbatch.PLANTCODE,
r_vendorvassignbatch.ITEMCODE,
createuser_in,
to_number(to_char(sysdate, 'yyyymmdd')),
to_number(to_char(sysdate, 'hh24MMSS')),
to_number(to_char(sysdate, 'yyyymmdd')),
to_number(to_char(sysdate, 'hh24MMSS')),
formKind_in,
to_char(sysdate) || '批量导入',
r_vendorvassignbatch.buyer);
---循环找出供应商
for i in 0 .. vendor_Total loop
if i = vendor_Total then
str_vendor := substr(str_vendors, 0, length(str_vendors));
str_VASSIGN := substr(str_VASSIGNs, 0, length(str_VASSIGNs));
else
vendor_index := instr(str_vendors, ':');
vassign_index := instr(str_VASSIGNs, ':');
str_vendor := substr(str_vendors, 0, vendor_index - 1);
str_VASSIGN := substr(str_VASSIGNs, 0, vassign_index - 1);
end if;
---start插入数据--
select SEQ_VASSIGNLINEID.nextval into str_LINEID from dual;
insert into VASSIGNFORMDETAIL
(ASSIGNSCALE,
VENDORCODE,
ASSIGNCODE,
ASSIGNPERCENT,
CREATEUSER,
CREATEDATE,
CREATETIME,
FORMID,
FORMNO,
LINEID,
REMARK)
values
(to_number(str_VASSIGN),
str_vendor,
str_ASSIGNCODE,
to_number(str_VASSIGN) * 10,
createuser_in,
to_number(to_char(sysdate, 'yyyymmdd')),
to_number(to_char(sysdate, 'hh24MMSS')),
str_vassignformid,
str_ASSIGNCODE,
str_LINEID,
to_char(sysdate) || '批量导入');
---end 插入数据--
str_vendors := substr(str_vendors,
vendor_index + 1,
length(str_vendors) - vendor_index);
str_VASSIGNs := substr(str_VASSIGNs,
vassign_index + 1,
length(str_VASSIGNs) - vassign_index);
end loop;
---优先级（有待做修改 目前4:3:3 的优先级是1:2:2的 ）
SEQIndex :=1;
for r_VASSIGNFORMDETAIL in c_temp(str_vassignformid,str_ASSIGNCODE) LOOP
update VASSIGNFORMDETAIL
set SEQ = SEQIndex
where FORMID = str_vassignformid
and FORMNO = str_ASSIGNCODE
and ASSIGNCODE = str_ASSIGNCODE
and LINEID= r_VASSIGNFORMDETAIL.Lineid;
SEQIndex := SEQIndex + 1;
END LOOP;
---送签
insert into FormHead
(formno,
formkind,
Formstatus,
Createuser,
Billcode,
Createdate,
Createtime,
Currentlevel)
values
(str_ASSIGNCODE,
formKind_in,
'Checking',
createuser_in,
str_ASSIGNCODE,
to_number(to_char(sysdate, 'yyyymmdd')),
to_number(to_char(sysdate, 'hh24MMSS')),
1);
end if;
end if;
end if;
end if;
END LOOP;
commit;
end PriceManager_VassignImport;