PLSQL學習

[color=darkblue][/color]貼下最近寫的一點代碼：
[color=violet]create or replace package 包名as
type report_row_type is ref cursor;

-- Author : KAKA
-- Created : 2010/8/19  01:52:15
-- Purpose :
function Query_dalay_partno(vtooling_no in varchar2,valiasno in varchar2, vdelay in varchar2,vpartname in varchar2,vbeginTime in varchar2,vendTime in varchar2,vLoginId in varchar2)return report_row_type;

function Query_dalay_partno_status(vruncardno in varchar2,vprocessinfo_oid in varchar2,
ventrytime in date,vOUTSOURCING in varchar2)return varchar2;

function Query_partno_cname(vruncardno in varchar2)return varchar2;

function Query_finish_time(vruncardno in varchar2,vprocessinfo_oid in varchar2,vOUTSOURCING in varchar2)return varchar2;

end mrp1_query_delay_partno;

create or replace package body 包名as

function Query_dalay_partno(vtooling_no in varchar2,valiasno in varchar2,vdelay in varchar2,vpartname in varchar2,vbeginTime in varchar2,vendTime in varchar2,vLoginId in varchar2)
return report_row_type is result report_row_type;

PM_SQLTEM varchar2(2000);
PM_early_date date;
PM_later_date date;



begin

if vdelay = '1'
then
PM_SQLTEM := PM_SQLTEM ||' and ((to_char(wpi.station_demand_date,''yyyy-mm-dd'') < mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing)'
||' or (to_char(wpi.station_demand_date,''yyyy-mm-dd'') < to_char(sysdate,''yyyy-mm-dd'')'
||' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) is null )) ) ';

end if;
if vtooling_no is not null

then PM_SQLTEM := PM_SQLTEM || ' and mtd.toolingno = ' || '''' || vtooling_no || '''' ;
end if;
if valiasno is not null
then PM_SQLTEM := PM_SQLTEM ||' and bw.aliasno = ' || '''' || valiasno || '''' ;
end if;

if vpartname = '1'
then PM_SQLTEM := PM_SQLTEM ||' and ( wr.part_no like ''_%301%'' or wr.part_no like ''_%410%'' or wr.part_no like ''_%101%'') ';
end if;
if vbeginTime is not null and vendTime is not null
then
--PM_SQLTEM := PM_SQLTEM ||' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) > '||''''||vbeginTime||'''';
-- ||' or mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) < ' ||''''||vendTime||'''' ;
--PM_SQLTEM := PM_SQLTEM ||' and to_char(wpi.station_demand_date,''yyyy-mm-dd'') > ' ||''''||vbeginTime||''''
--||' and to_char(wpi.station_demand_date,''yyyy-mm-dd'') < ' ||''''||vendTime||'''';
-- ||' and to_char(wpi.station_demand_date,''yyyy-mm-dd'') < to_char(sysdate,''yyyy-mm-dd'')' ;
PM_SQLTEM := PM_SQLTEM ||' and (
(mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) > '||''''||vbeginTime||''''||' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) < '||''''||vendTime||''''||')
or (mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) is null and to_char(wpi.station_demand_date,''yyyy-mm-dd'') < '||''''|| vendTime||''''|| ')
or (mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) > '||''''||vendTime||''''||' and to_char(wpi.station_demand_date,''yyyy-mm-dd'') < ' ||''''|| vendTime || '''' || ')
)';
end if;

open result for
'select distinct wr.part_no ,mtd.toolingno ,
bw.aliasno,
.runcardno Runcard,
mrp1_query_delay_partno.Query_partno_cname(wr.runcardno),
to_char(wpi.station_demand_date,''yyyy-MM-dd'')
wr.qty Runcard,
mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) finish_time
from wip_processinfo wpi ,wip_runcard wr,mold_toolingno_data mtd,
basicdata_workshop bw
where wpi.runcardno = wr.Runcardno
and wr.tooling_no_oid = mtd.toolingno_oid
and wpi.work_shop_oid = bw.work_shop_oid
and wpi.station_demand_date is not null '
||PM_SQLTEM;

return result;
end;

-- wip_processinfo wpi,wip_workpieceinfo wwi ,
-- wip_workpieceinfo_qty wwq,wip_reason_code wrc
/**vEntryTime (vExitTime (),vbeginTime (丁),vendTime (审丁)**/
2010/8/17 09:22:57
function Query_dalay_partno_status(vruncardno in varchar2,vprocessinfo_oid in varchar2,
vEntryTime in date,vOUTSOURCING in varchar2) return varchar2 is pm_status varchar2(100) := '';


pm_is_part_up varchar2(1) := '0';
pm_ok_qty number := 0;
pm_runcardQTY number;
pm_endTime date;

begin


if vEntryTime is null
then pm_status :='ゼ';
end if;

for rec in (
select w.workpiece_begin_time,ww.reason_code,ww.qty
from wip_workpieceinfo w,WIP_WorkpieceInfo_QTY ww
where w.processinfo_oid = vprocessinfo_oid
and w.workpieceinfo_oid = ww.workpieceinfo_oid
)
loop
pm_is part_up 1
if rec.workpiece_begin_time is not null
then
pm_is_part_up := '1';
end if ;
runcard
if rec.reason_code = '01'
then
pm_ok_qty := pm_ok_qty + rec.qty;
end if;

end loop;
if pm_is_part_up = 0
then

if vOUTSOURCING = 1
then
pm_status := '';
else
pm_status := ';
end if;

end if;
-- ―runcard
select count(wpi.runcardno) RuncardQTY
into pm_runcardQTY
from wip_processinfo wpi
where wpi.runcardno =vruncardno;
if pm_ok_qty = pm_runcardQTY
then

select ww.workpiece_end_time
into pm_endTime
from wip_processinfo wp,wip_workpieceinfo ww
where wp.processinfo_oid = ww.processinfo_oid
and wp.runcardno = vruncardno;
pm_status := ';
end if;

if pm_status is null
then
pm_status := ';
end if;
--dbms_output.put_line(''|| pm_endTime);
return pm_status || '+'|| pm_endTime;
end;


function Query_partno_cname(vruncardno in varchar2)
return varchar2 is pm_part_name varchar2(200):='';

begin
for rec in(
select distinct mb.part_name_c
into pm_part_name
from wip_runcard wr ,mold_bom mb
where wr.bomfacade_oid = mb.bomfacade_oid
and wr.runcardno = vruncardno
)
/*(
/*(mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) > '2010-09-11' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) < '2010-09-17')
or (to_char(wpi.station_demand_date,'yyyy-mm-dd') < '2010-09-17' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) is null)
or (to_char(wpi.station_demand_date,'yyyy-mm-dd')< '2010-09-17' and mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) > '2010-09-17')

)*/
loop
pm_part_name := pm_part_name ||rec.part_name_c;
end loop;
return pm_part_name;


end;
function Query_finish_time(vruncardno in varchar2,vprocessinfo_oid in varchar2,vOUTSOURCING in varchar2)
return varchar2 is pm_endTime varchar2(100):='';
pm_ok_qty number := 0;
pm_runcardQTY number := 0; pm_outsourcingTime varchar2(100);


begin
if vOUTSOURCING = 1
then

select to_char(wp.exittime,'yyyy-MM-dd')
into pm_outsourcingTime
from wip_processinfo wp,basicdata_workshop bw
where wp.work_shop_oid = bw.work_shop_oid
and wp.runcardno = vruncardno
and bw.outsourcing = vOUTSOURCING;
--dbms_output.put_line('丁'||pm_outsourcingTime);
else
for rec in
(
select wf.qty,wq.reason_code,wf.workpiece_begin_time
from wip_workpieceinfo wf,wip_workpieceinfo_qty wq
where wf.workpieceinfo_oid = wq.workpieceinfo_oid
and wf.processinfo_oid = vprocessinfo_oid
)
loop
if rec.reason_code ='01'
then
pm_ok_qty := pm_ok_qty + rec.qty;
-- dbms_output.put_line(pm_ok_qty);
end if;
end loop;
-- ―runcard
select count(wpi.runcardno) RuncardQTY
into pm_runcardQTY
from wip_processinfo wpi
where wpi.runcardno =vruncardno;
-- dbms_output.put_line('runcar计秖'||pm_runcardQTY);

if pm_ok_qty = pm_runcardQTY
then

select to_char(ww.workpiece_end_time,'yyyy-MM-dd')
into pm_endTime
from wip_processinfo wp,wip_workpieceinfo ww
where wp.processinfo_oid = ww.processinfo_oid
and wp.runcardno = vruncardno
and wp.processinfo_oid = vprocessinfo_oid;
-- dbms_output.put_line('ЧΘ丁'||pm_endTime);
end if ;

pm_outsourcingTime := pm_endTime;
end if;

return pm_outsourcingTime;

end;


end 包名;[/color]

額,不支持繁體中文，下次再改吧。。
[color=red]心得：
1.能不用select into 最好是不用，如果表里有空值,就會出現EOF異常.

2.不要隨便用distinc 和 orderBy 速度會變的很慢.
如果表里有空值,上面的兩個關鍵字,即使是沒有重複的值，查出來的值會比實際應該查 出來的值少了很多.原因還不是很清楚.[/color]
3.insert table的時候 一定要把table的欄位寫上，不要用默認的所有欄位，要不然以後沒辦法加欄位了


[b][color=darkblue]修改過後的PLSQL:[/color][/b]

open result for
'select
wr.part_no 工件 ,mtd.toolingno 模號,
bw.aliasno 工段,wr.runcardno Runcard,
mrp1_query_delay_partno.Query_partno_cname(wr.runcardno) 品名,
to_char(wpi.station_demand_date,''yyyy-MM-dd'') 需求日期,
wr.qty Runcard數量, mrp1_query_delay_partno.Query_finish_time(wr.Runcardno,wpi.processinfo_oid,bw.outsourcing) finish_time
from wip_processinfo wpi ,wip_runcard wr,mold_toolingno_data mtd,
basicdata_workshop bw
where wpi.runcardno = wr.Runcardno
and wr.tooling_no_oid = mtd.toolingno_oid
and wpi.work_shop_oid = bw.work_shop_oid
and wpi.station_demand_date is not null '||PM_SQLTEM;

return result;


--求出站的時間即可
for rec_a in (
select to_char(wp.exittime,'yyyy-MM-dd') b
from wip_processinfo wp,basicdata_workshop bw
where wp.work_shop_oid = bw.work_shop_oid
and wp.runcardno = vruncardno
and bw.outsourcing = vOUTSOURCING
)
loop
pm_outsourcingTime := rec_a.b;
end loop;


for rec in --求出入站后是否上機，是否下機OK 工件的數量
(
select wf.qty,wq.reason_code,wf.workpiece_begin_time
from wip_workpieceinfo wf,wip_workpieceinfo_qty wq
where wq.workpieceinfo_oid = wf.workpieceinfo_oid(+)
and wf.processinfo_oid = vprocessinfo_oid
)
loop

if rec.reason_code ='01'
then
pm_ok_qty := pm_ok_qty + rec.qty;
end if;
end loop;

-- 求出同一個runcard的工件數量
for rec_runcardQTY in(
select count(wpi.runcardno) RuncardQTY
--into pm_runcardQTY
from wip_processinfo wpi
where wpi.runcardno = vruncardno
)
loop
--dbms_output.put_line('pm_ok_qty==='||pm_ok_qty);
--dbms_output.put_line('pm_runcardQTY==='||pm_runcardQTY);
-- dbms_output.put_line('runcar數量'||pm_runcardQTY);
--如果ok的數量和入站的數量一致，則表示加工完成了

if pm_ok_qty = rec_runcardQTY.RuncardQTY--pm_runcardQTY
then
for rec_endTime in(
select to_char(ww.workpiece_end_time,'yyyy-MM-dd') end_Time
--into pm_endTime
from wip_processinfo wp,wip_workpieceinfo ww
where wp.processinfo_oid = ww.processinfo_oid
and wp.runcardno = vruncardno
and wp.processinfo_oid = vprocessinfo_oid
)
loop
pm_endTime := rec_endTime.end_Time;
end loop;
-- dbms_output.put_line('完成時間'||pm_endTime);
end if ;
end loop;

pm_outsourcingTime := pm_endTime;
end if;