存储过程中结构体的使用.相当于数组
type Type_BorrowID is table of varchar2(20); 定义一个表的一个varchar2(20)类型的一个字段
type Type_Date is table of varchar2(14);
type Type_Number is table of number(10, 2);
type Type_Dept is table of number(4);
type Type_Speed is table of number(10, 2);
type Type_Status is table of char(1);
type Type_ShelfID is table of NUMBER(4);
type Type_DeliverRouteID is table of varchar2(20);
type REC_PickStatusGather is record(
t_BorrowID Type_BorrowID,
t_PickDate Type_Date,
t_PickTeamID Type_Dept,
t_PickBeginTime Type_Date,
t_PickEndTime Type_Date,
t_PickUseTime Type_Date,
t_CustomerNum Type_Number,
t_BarQuantity Type_Number,
t_PieceQuantity Type_Number,
t_BoxQuantity Type_Number,
t_PackQuantity Type_Number,
t_BarSpeed Type_Speed,
t_PackSpeed Type_Speed,
t_ShelfID Type_ShelfID,
t_DeliverRouteID Type_DeliverRouteID,
t_DeliverID Type_DeliverRouteID,
t_Status Type_Status);
r_PickStatusGather REC_PickStatusGather;定义一个类似于struct的一个结构体,这里我把他称为一个记录集合,相当于临时的一个内存表.
sSQL := 'select distinct t1."BorrowID",TO_CHAR(t1."PickDate",''yyyymmdd''),t1."Status" from D_Route@' ||
sDataSource ||
' t1, LTMP.TT_PIK_Borrow t2 where t1."BorrowID"=t2.BorrowID ';
execute immediate sSQL bulk collect
into r_PickStatusGather.t_BorrowID, r_PickStatusGather.t_PickDate, r_PickStatusGather.t_Status;
此段是向这个内存表里插入记录.
forall i in 1 .. r_PickStatusGather.t_BorrowID.count
update LPMS.PIK_PickStatusGather
set BarSpeed = r_PickStatusGather.t_BarSpeed(i),
PackSpeed = r_PickStatusGather.t_PackSpeed(i)
where BorrowID = r_PickStatusGather.t_BorrowID(i);
r_PickStatusGather.t_BarSpeed.delete;
r_PickStatusGather.t_PackSpeed.delete;
r_PickStatusGather.t_BorrowID.delete;
此段是对内存表的循环对表的更新操作