PLSQL學習

[/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 包名;

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

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


修改過後的PLSQL:

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;