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;
[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;
分享到：
发表评论
相关推荐
PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 PLSQL学习资料 ...
本压缩包文件“一个很全的PLSQL学习资料”显然是一份丰富的学习资源，包含多种格式的文档，旨在帮助用户全面理解和掌握PLSQL。 首先，我们有plsql.doc，这可能是一个详细的PLSQL教程文档，涵盖了基础语法、数据类型...
本“PLSQL学习笔记”将逐步讲解这些概念，通过实例和练习帮助读者巩固理解，进一步提升在Oracle数据库开发中的技能。无论你是初学者还是有经验的开发者，这份笔记都能提供有价值的参考。通过深入学习和实践，你将...
作为学习PLSQL学习的一些好资料。大家好好评价评价
本PLSQL学习基础课件PPT旨在为初学者提供深入浅出的教程，帮助他们掌握这一强大的数据库编程工具。 首先，我们要了解PLSQL的基本结构，它由声明部分、执行部分和异常处理部分组成。声明部分包括变量、常量、游标、...
PLSQL学习资料
PLSQL培训（二） PLSQL培训（一） oracle系列培训教材（PL-SQL）.doc PL SQL教程.pdf PLSQL基础.pdf plsql配置自动显示my object_陈慧兵.doc SQL21自学通.pdf SQL复制指南及参考.pdf SQL语句优化.doc SQL语言参考...
Oracle9i SQL/PLSQL 学习笔记 自己照着书学习时候的笔记。TXT的，比较简单，希望对大家学习有帮助。 其中0 mysqlCmd.txt是我自学mysql时候的笔记，是有关于命令的。大家看看有帮助没有。 都比较精华和简单，适合...
PLSQL学习进阶PPT，十分适合初学者学习
"PLSQL学习经典例子"这个压缩包文件显然是为了帮助学习者通过实践来深入理解PLSQL的各种概念和用法。 1. **基础语法与结构** PLSQL由声明部分、执行部分和异常处理部分组成。声明部分定义变量、游标、常量等；执行...
本文将深入探讨"PLSQL学习基础资料"中的关键知识点，旨在为初学者提供一个全面的入门指南。 首先，让我们从"Oracle-SQL语言基础"开始。SQL（Structured Query Language）是用于查询、更新和管理关系数据库的标准...
PLSQL学习资料2，推荐下载
PLSQL学习资料3 ，欢迎大家下载
在“PLSQL Developer操作学习文档”中，你可以期待学习到以下关键知识点： 1. **PLSQL基础**：了解PLSQL的基本语法结构，包括声明变量、常量、游标、异常处理等。学习如何定义过程（PROCEDURE）、函数（FUNCTION）...
这个“oracle_plsql学习资料”压缩包包含了一个名为“oracle_plsql.pdf”的文件，可以作为学习Oracle PL/SQL的基础教程。 Oracle PL/SQL是Oracle数据库的核心组成部分，主要用于开发存储过程、函数、触发器等数据库...
在"Oracle and PLSQL学习课件"中，我们可以预期涵盖以下几个核心知识点： 1. **Oracle数据库基础**：了解Oracle数据库的基本架构，包括表空间、数据文件、控制文件、重做日志、实例和数据库的区别等。学习如何安装...
【PLSQL学习笔记】 PLSQL（Procedural Language/Structured Query Language）是Oracle数据库系统中的一个强大编程语言，它结合了SQL的查询能力与过程式编程语言的特点，为数据库开发提供了丰富的功能。这篇学习笔记...
很好的学习PLSQL学习的资料,不可不下
Java相关课程系列笔记之三PLSQL学习笔记
plsql学习资源 plsql.ncd