function GET_TIMES_BY_TYPE(p_planstartdate date,
p_prodlineidnumber,
p_scheduletypenumber)return STT
pipelined is
l_start_time date;
l_end_time date;
l_currentdatedate;
l_scheduleTypeTime STT;
cursor c_schedule is
select s.starttime, s.endtime, s.scheduleid, s.scheduletype
from bms
s,
bmc
ct,
bmcg ctg,
bmp
pl
where s.cid = ct.cid
and ct.cgroupid = ctg.cgroupid
and ctg.cgroupid = pl.cgroupid
and pl.pid = p_pid
order by ct.ctorder, s.sno asc;
begin
if p_scheduletype0 and p_scheduletype1 then
raise G_INPUTTYPE_NOT_RIGHT;
end if;
l_currentdate := p_planstartdate;
for r_s in c_s loop
l_start_time := to_date(to_char(l_currentdate, 'YYYY-MM-DD') || ' ' ||
to_char(r_s.starttime, 'HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS');
if to_char(r_s.starttime, 'HH24') =
to_char(r_s.endtime, 'HH24') then
if to_char(r_s.starttime, 'MI') >
to_char(r_s.endtime, 'MI') then
l_currentdate := l_currentdate + 1;
elsifto_char(r_s.starttime, 'MI') =
to_char(r_s.endtime, 'MI') then
if to_char(r_s.starttime, 'SS') >
to_char(r_s.endtime, 'SS') then
l_currentdate := l_currentdate + 1;
end if;
end if;
elsif to_char(r_s.starttime, 'HH24') >
to_char(r_s.endtime, 'HH24') then
l_currentdate := l_currentdate + 1;
end if;
l_end_time := to_date(to_char(l_currentdate, 'YYYY-MM-DD') || ' ' ||
to_char(r_s.endtime, 'HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS');
if r_s.scheduletype = p_scheduletype then
l_scheduleTypeTime.starttime:= l_start_time;
l_scheduleTypeTime.endtime:= l_end_time;
l_scheduleTypeTime.scheduletype := p_scheduletype;
PIPE ROW(l_scheduleTypeTime);
end if;
end loop;
end;复制代码这个要怎么优化 谢谢！
下面是我修改的不知道会不会好些？
function GET_TIMES_BY_TYPE(p_planstartdate date,
p_prodlineidnumber,
p_scheduletypenumber)return STT
pipelined is
l_start_time date;
l_end_time date;
l_currentdatedate;
l_scheduleTypeTime STT;
cursor c_s is
select s.starttime, s.endtime, s.scheduleid, s.scheduletype
from bms
s,
bmc
ct,
bmcg ctg,
bmp
pl
where s.cid = ct.cid
and ct.cgroupid = ctg.cgroupid
and ctg.cgroupid = pl.cgroupid
and pl.pid = p_pid
order by ct.ctorder, s.sno asc;
TYPE bms_rec_type IS RECORD
(
starttimebms.starttime%TYPE,
endtimebms.endtime%TYPE,
scheduleid bms.scheduleid%TYPE,
scheduletype bms.scheduletype%TYPE
);
TYPE nested_bms_type IS TABLE OF bms_rec_type;
r_s nested_bms_type;
v_limit PLS_INTEGER := 5;
begin
if p_scheduletype0 and p_scheduletype1 then
raise G_INPUTTYPE_NOT_RIGHT;
end if;
l_currentdate := p_planstartdate;
OPEN c_s;
LOOP
FETCH c_s
BULK COLLECT INTO r_s
LIMIT v_limit;
EXIT WHEN r_s.COUNT = 0;
FOR i IN r_s.FIRST .. r_s.LAST
LOOP
l_start_time := to_date(to_char(l_currentdate, 'YYYY-MM-DD') || ' ' ||
to_char(r_s(i).starttime, 'HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS');
if to_char(r_s(i).starttime, 'HH24:MI:SS') >
to_char(r_s(i).endtime, 'HH24:MI:SS') THEN
l_currentdate := l_currentdate + 1;
end if;
l_end_time := to_date(to_char(l_currentdate, 'YYYY-MM-DD') || ' ' ||
to_char(r_s(i).endtime, 'HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS');
if r_s(i).scheduletype = p_scheduletype then
l_scheduleTypeTime.starttime:= l_start_time;
l_scheduleTypeTime.endtime:= l_end_time;
l_scheduleTypeTime.scheduletype := p_scheduletype;
PIPE ROW(l_scheduleTypeTime);
end if;
END LOOP;
END LOOP;
CLOSE c_s;
end;复制代码
|
