PLSQL 创建带返回数据集的Function
分享之前，真诚感谢IT 张工（巨林）的支持！！
聊起工序流程设置、条码追溯，就有点头大！！实话，接到BOSS下达的这个Task, 心里没个底，开始时心慌慌^_^ 。原因之前在Oracle仅仅是增、删、改查，至多加个transaction。
因为数据使用报表工具显示，因此不能使用C# 等后台代码进行控制与计算，初步判定： Procedure 或者 Function.
首先Procedure和Function 都支持输入参数与输出结果，但各有优劣： procedure 方便过程控制、Insert , Update 等操作，但返回结果集通常使用Cursor；Function 虽然能返回DataTable 数据集，但不支持DML 语句，Insert , Update 就会报错（其实有条件支持）;
在经历了N次尝试，失败，尝试，失败。。。。后，最终决定采用Function 来实现。
技术方面的总结如下：
- 由于Function 不能在函数体中声明Cursor(只能在is … Begin 之间)， 因此无法实现随着情况变化而变的Cursor(此前没有搞清楚，Fetch cursor 老是报错，郁闷了好久);
- 在IT 张工建议下，使用For in loop 替代Cursor ,非常好的建议；
- 在创建Function 前,先Create TYPE {typename} table of …; 然后才能在 定义Function 中return TypeName(变量列表之后)；
- Function 内部默认是只读的，也就是只能使用Select 或者 Select into ; 要使用Insert , Update , Delete 等DML语句，必须使用PRAGMA AUTONOMOUS_TRANSACTION 声明,且 commit 语句不能少哦；
- 一旦执行commit 语句，临时表就会清空；因此需要在commit 执行前转存临时表的结果；
- Select .. into … 当查询结果为空时，会报错： 没有查询记录；
- 前面工作准备好了，返回结果相当简单： Select * from table(fn_CalcLineProcTime_Nov('A4305','2021-11-2',4));
上代码：
/************************* 工序时间计算 *****************************/ create or replace function fn_CalcLineProcTime_Nov( p_LineNo varchar2, p_BegDate varchar2, p_Hours number --,p_ProcName varchar2 ) return PROC_Time_Table as PRAGMA AUTONOMOUS_TRANSACTION; v_procData PROC_Time_Table:=PROC_Time_Table(); v_CELLName varchar2(100):=''; --条码 v_ParentSN varchar2(100):=null; --父条码 --v_SubSN varchar2(100):=''; --子条码 v_bJump boolean:=false; --是否跳过 v_ProcName varchar2(32):=''; --工序名 v_rowcount number:=0; --记录行数 v_ParentSNCount number:=0; begin --将指定时期内 BarCode 最早出现的时间插入至临时表 Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type from( select ROW_Number() over(partition by a.SN order by a.Test_Time) rn,a.* FROM (Select SN,Proc_Name,Test_Time,null NextProcTime,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No is not null and Line_No=p_LineNo --and Line_No='A4305' and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24) a) --and Test_Time>To_Date('2021-10-27','YYYY-MM-DD') and Test_Time<To_Date('2021-10-27','YYYY-MM-DD')+4/24) a) where rn=1 order by Test_Time,SN; --select * from tb_LineProcTime for tb in(Select SN,Proc_Name,Test_Time,PN,Flow_Type from tb_LineProcTime_new order by Test_Time,SN,Proc_Name) loop v_CellName:=tb.SN; v_ProcName:=tb.Proc_Name; for flow in(Select cp.Proc_Name,cp.Proc_Idx,p.proc_flag,p.Data_Tb_Name,Proc_Tb_Name from Nvt_Cp_List_01 cp inner join NVT_Proc_Matchtb p on cp.Proc_Name=p.Proc_Name where cp.PN=tb.PN and cp.Flow_Type=tb.Flow_Type and cp.Test_Type=1 and cp.Status_Flag='生效' and cp.Proc_Idx>(Select Proc_Idx from NVT_CP_List_01 where PN=tb.PN and Flow_Type=tb.Flow_Type and Test_Type=1 and Proc_Name=tb.Proc_Name) order by cp.Proc_Idx) loop if flow.data_tb_name='NA' and flow.proc_tb_name='NA' then v_bJump:=true; --if v_ProcName='' then v_ProcName:=tb.Proc_Name; end if; continue; end if; if flow.Proc_Name='CLINK' then Select Count(SN) into v_ParentSNCount from NVT_CELL_GROUP where CELL_Name=v_CellName and LINK_DATE>To_Date(p_BegDate,'YYYY-MM-DD') and LINK_DATE<=To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select SN into v_ParentSN from NVT_CELL_GROUP where CELL_Name=v_CellName and LINK_DATE>To_Date(p_BegDate,'YYYY-MM-DD') and LINK_DATE<=To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then --插入过站数据 Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='PCM-ASM' then Select Count(CELL_NAME) into v_ParentSNCount from NVT_PCM_ASM where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select CELL_NAME into v_ParentSN from NVT_PCM_ASM where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='MOD-ASM' then --NVT_MODULE_ASM Select Count(CELL_NAME) into v_ParentSNCount from NVT_MODULE_ASM where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select CELL_NAME into v_ParentSN from NVT_MODULE_ASM where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='WELDING' then --NVT_WELDING Select Count(CELL_NAME) into v_ParentSNCount from NVT_WELDING where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select CELL_NAME into v_ParentSN from NVT_WELDING where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='MAPPING' then --NVT_LABEL_MAPPING Select Count(CELL_NAME) into v_ParentSNCount from NVT_LABEL_MAPPING where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select CELL_NAME into v_ParentSN from NVT_LABEL_MAPPING where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='CARTON' then --NVT_LOT_MAPPING --Select CELL_NAME into v_ParentSN from NVT_LABEL_MAPPING where SUB_CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_CellName and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_CellName and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_CellName) where SN=v_CellName and Proc_name=v_ProcName; elsif flow.Proc_Name='EXT-LINK' then --BIS_EXTCODE_LINK Select Count(EXT_CODE) into v_ParentSNCount from BIS_EXTCODE_LINK where CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; exit when v_ParentSNCount=0; Select EXT_CODE into v_ParentSN from BIS_EXTCODE_LINK where CELL_NAME=v_CellName and Update_Date>to_Date(p_BegDate,'YYYY-MM-DD') and Update_Date<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; --exit when v_ParentSN is null; select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_ParentSN and Proc_Name=flow.Proc_Name ; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_ParentSN and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_ParentSN) where SN=v_CellName and Proc_name=v_ProcName; else select Count(1) into v_rowcount from tb_LineProcTime_new where SN=v_CellName and Proc_Name=flow.Proc_Name; if v_rowcount=0 then Insert into tb_LineProcTime_new(SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN,Flow_Type) Select SN,Proc_Name,Test_Time,null NextProcName,null PassTime,Line_No,PN,Remark_1 Flow_Type from NVT_PROCESS_STATUS where Test_Type=1 and Test_CNT=1 and RT_CNT=0 and Line_No=p_LineNo and SN=v_CellName and Proc_Name=flow.Proc_Name and Test_Time>To_Date(p_BegDate,'YYYY-MM-DD') and Test_Time<To_Date(p_BegDate,'YYYY-MM-DD')+p_Hours/24; end if; --更新上一站的结束时间 Update tb_LineProcTime_new t1 SET t1.NextProctime=(Select t2.Test_Time from tb_LineProcTime_new t2 where t2.Proc_Name=flow.Proc_Name AND t2.SN=v_CellName) where SN=v_CellName and Proc_name=v_ProcName; end if; v_ProcName:=flow.proc_name; --更新工序名变量 v_CellName:=v_ParentSN; --更新条码变量 end loop; --Select * from NVT_CP_LIST_01 where PN='519110001435' and Flow_Type='方案一' and Test_Type=1; --select * from Nvt_Proc_Matchtb order by Proc_Name; /*Update tb_LineProctime t1 --更新下一站时间 SET NextProcTime=(Select Test_Time from (Select rownum,Test_Time from tb_LineProctime t2 where SN=tb.SN and Proc_Name<>tb.proc_name and Test_Time>=tb.test_time order by Test_Time) where rownum=1) where t1.SN=tb.Sn--'518110001000-00000-05-21A28-10153' and Proc_Name=tb.proc_name and Proc_Name=p_ProcName and t1.Test_Time=tb.test_time and EXISTS(Select Test_Time from (Select rownum,Test_Time from tb_LineProctime t2 where SN=tb.SN and Proc_Name<>tb.proc_name and Test_Time>=tb.test_time order by Test_Time) where rownum=1) ;*/ end loop; update tb_LineProcTime_new SET PassTime=Round(To_Number(NextProcTime-Test_Time)*24*60*60) where /*Proc_Name=p_ProcName and*/ NextProcTime is not null and PassTime is null; for c in (Select SN,Proc_Name,Test_Time,NextProcTime,PassTime,Line_No,PN from tb_LineProcTime_new /*where Proc_Name=p_ProcName*/ order by SN,Test_Time,Proc_Name) loop v_procData.extend(); v_procData(v_procData.count):=PROCRecord(c.SN,c.proc_name,c.Test_Time,c.NextProcTime,c.PassTime,c.Line_No,c.PN); end loop; commit; return v_procData; end fn_CalcLineProcTime_Nov;
调用 ： Select * from table(fn_CalcLineProcTime_Nov('A4305','2021-11-2',4));
