存储过程中游标的使用
发布网友
发布时间：2022-04-23 04:50
共2个回答
热心网友
时间：2022-04-08 11:15
下面是一个样例，参考一下吧：
procere BAK_ONHAND(p_batch_code in varchar2,
p_calc_date_code in varchar2) is
r_onhand CUX_MTL_ONHAND_BAK_TEMP%rowtype;
v_bak_date date;
Cursor C1 is
select moq.inventory_item_id,
msib.segment1 item_code,
msib.description item_des,
msib.primary_unit_of_measure item_uom,
moq.subinventory_code,
moq.locator_id,
nvl(sum(moq.transaction_quantity), 0) trans_qty,
moq.organization_id
from mtl_onhand_quantities_detail moq, mtl_system_items_b msib
where moq.inventory_item_id = msib.inventory_item_id
and moq.organization_id = msib.organization_id
and msib.organization_id = g_organization_id
and exists (select 1
from CUX_ITEM_VENDOR_INFOR_T ci
where msib.inventory_item_id = ci.item_id
and msib.organization_id = ci.organization_id
and ci.plan_or_delivery = '计划')
group by moq.inventory_item_id,
msib.segment1,
msib.description,
msib.primary_unit_of_measure,
moq.subinventory_code,
moq.locator_id,
moq.organization_id
order by msib.segment1;
begin
v_bak_date := sysdate;
r_onhand.BATCH_CODE := p_batch_code;
r_onhand.catc_date_code := p_calc_date_code;
r_onhand.created_by := g_user_id;
r_onhand.creation_date := sysdate;
r_onhand.last_updated_by := g_user_id;
r_onhand.last_update_date := sysdate;
r_onhand.last_update_login := g_login_id;
for c11 in C1 loop
r_onhand.ITEM_ID := c11.inventory_item_id;
r_onhand.ITEM_CODE := c11.item_code;
r_onhand.ITEM_DES := c11.item_des;
r_onhand.ITEM_UOM := c11.item_uom;
r_onhand.SUB_CODE := c11.subinventory_code;
r_onhand.LOCATOR_ID := c11.locator_id;
r_onhand.TRANS_QTY := c11.trans_qty;
r_onhand.ORGANIZATION_ID := g_organization_id;
r_onhand.BAK_DATE := v_bak_date;
r_onhand.ATTRIBUTE1 := '';
r_onhand.ATTRIBUTE2 := '';
r_onhand.ATTRIBUTE3 := '';
r_onhand.ATTRIBUTE4 := '';
r_onhand.ATTRIBUTE5 := '';
insert into CUX_MTL_ONHAND_BAK_TEMP values r_onhand;
end loop; ---end C1
commit;
end BAK_ONHAND;
热心网友
时间：2022-04-08 12:33
什么数据库？追问Sqlserver2008
追答CREATE procere ProctCheck @bgndate varchar(10),@enddate varchar(10),@customerno varchar(12)
as
declare Cur_d cursor for select distinct Kind from BO_ProctImport where Deliverdate between @bgndate and @enddate and CustomerNo=@customerno
open Cur_d
fetch next from Cur_d into @Kind
while @@FETCH_STATUS=0
begin
insert into tmp_d
select CustomerNo,DeliverDate,DeliverNo,Deliverweight,Kind from BO_ProctImport where Deliverdate between @bgndate and @enddate and kind=@Kind and CustomerNo=@customerno
order by Deliverdate
fetch next from Cur_d into @Kind
end
CLOSE Cur_C
DEALLOCATE Cur_C
GO
