在SQL里面新建函数的步骤汇总---wms收发存报表例子-爱代码爱编程

在SQL Server中函数算是比较高阶的sql,在数据库的报表开发中，我们常常会遇到一些收发存的报表需求，一般的报表都可以通过用sql新建试图来更合理的完成开发，但是在收发存报表里面，仅仅是一个视图是解决不了的，因为收发存的参数设置是通过时间来控制的，所以需要用过函数来实现，逻辑是中间表的基础数据＋期初库存＋入库数量-出库数量=期末库存，中间表的数据需要我们在数据库里面新建一个自定义的UDF表，表中的数据通过新建一个insert存储过程来注入表中，存储过程是通过新建一个定时脚本来每天00：00：00 来执行一次存储过程，也是说11月16的期末库存 = 11月17的期初库存，每天的期末库存是通过计算得来的，计算的逻辑是：期初库存+入库数量-出库数量=期末库存，下面是新建函数的SQL语句：

CREATE OR REPLACE FUNCTION “WMWHSE1”.“UDF_SHOUFACUN_1”
(v_date_str in nvarchar2,v_date_end in nvarchar2)

return UDF_SFCTABLE
as

irow UDF_SFC;
itable UDF_SFCTABLE := UDF_SFCTABLE();
cursor loclist is

-------sql的主体部分，每个函数的主题都不一样，这里举一个例子---------
select SKU.库区,SKU.po, QICHU.版本号, SKU.storerkey ,
SKU.sku ,SKU.descr,SKU.BUSR3 , SKU.BUSR4 , SKU.BUSR5 , SKU.BUSR6,sku.BUSR7,
SKU.busr1,SKU.udf5,SKU.udf3,SKU.SEASON ,SKU.style,SKU.skusize,SKU.LOTT01,SKU.LOTT07,SKU.LOTT08,SKU.LOTT10,SKU.XIAOSHOU,
nvl(QICHU.期初库存,0) as 期初库存
,nvl(SHOU.收货数量,0) as 收货数量 ,nvl(CHU.发货数量,0) as 发货数量,
nvl(QICHU.期初库存,0)+nvl(SHOU.收货数量,0)-nvl(CHU.发货数量,0) as 期末库存

from
(
(
select distinct
vib.lottable03 as po, vib.sku as SKU, vib.DESCR as DESCR, vib.storerkey as STORERKEY,pz.descr as 库区,
SKU.SEASON ,SKU.style,SKU.skusize,sku.BUSR1, SKU.BUSR4 , SKU.BUSR5 , SKU.BUSR6,sku.BUSR7,sku.BUSR3,vib.LOTTABLE09 as 版本号,
vib.lottable06 as UDF3 ,it.udf5 as UDF5,vib.LOTTABLE01 as LOTT01,vib.LOTTABLE07 as LOTT07,vib.LOTTABLE08 as LOTT08,vib.LOTTABLE10 as LOTT10,c.udf3 as XIAOSHOU
from WMWHSE1.vibdetail vib
left join(select cd.udf1,cd.udf2,cd.udf5,cd.udf3 from codelkup cd where listname=‘UDF_WLDMXT’ ) it
on vib.sku = it.udf2 and it.udf3 = vib.lottable06
left join WMWHSE1.loc on loc.loc = vib.LOC
left join WMWHSE1.putawayzone pz on loc.putawayzone = pz.putawayzone
left join wmwhse1.sku on sku.sku = vib.sku and sku.storerkey = vib.storerkey
left join codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
)SKU
left join
(
select udf.库区,udf.po, udf.版本号, sku.storerkey ,
sku.sku ,sku.descr,sku.BUSR3 , sku.BUSR4 , sku.BUSR5 , sku.BUSR6,sku.BUSR7,
sku.busr1,udf.udf5,udf.udf3,sku.SEASON ,cd.udf4 as 对照码,sku.style,sku.skusize,udf.LOTT01,udf.LOTT07,udf.LOTT08,udf.LOTT10,udf.XIAOSHOU,
sum(udf.qty) as 期初库存
from wmwhse1.UDFSHOUFACUN udf
left join wmwhse1.sku on sku.sku = udf.sku and sku.storerkey = udf.storerkey
left join wmwhse1.codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
left join wmwhse1.codelkup cd on cd.listname=‘UDF_WLDMXT’ and sku.sku = cd.udf2 and decode(sku.storerkey,‘160’,‘150’,sku.storerkey) = cd.udf1 and cd.udf3 = udf.udf3
where invdate = To_char(Trunc(TO_DATE(v_date_str,‘yyyy/mm/dd’)),
‘yyyy/mm/dd’)
group by sku.sku,udf.po, udf.库区,sku.storerkey,sku.descr,udf.invdate,udf.版本号,udf.LOTT01,udf.LOTT07,udf.LOTT08,udf.LOTT10,
sku.BUSR3 , sku.BUSR4 , sku.BUSR5, sku.BUSR6,sku.busr1,sku.skusize,udf.udf5 ,sku.SEASON ,cd.udf4,sku.style,udf.XIAOSHOU,udf.udf3,sku.BUSR7
)QICHU
on SKU.库区 = QICHU.库区 and SKU.版本号 = QICHU.版本号 and
SKU.po = QICHU.PO and SKU.storerkey = QICHU.storerkey
and SKU.sku = QICHU.sku and sku.udf3 = QICHU.udf3 --and SKU.对照码 = SHOU.udf4 and SKU.udf5 = SHOU.itemno
and SKU.LOTT08 = QICHU.LOTT08
left join
(
select area,
PO,lottable09,
storerkey,
sku,货品名称,BUSR3,BUSR4,BUSR5,BUSR6,u8,itemno,商标,udf4,颜色,规格,LOTTABLE06,LOTTABLE01,LOTTABLE07,LOTTABLE08,LOTTABLE10,udf3,
sum(abs(收货数量)) as 收货数量 from
(
select pz.descr as area ,
itrn.lottable03 as PO,itrn.lottable09,
itrn.adddate,
itrn.storerkey ,
itrn.sku , sku.descr as 货品名称 , sku.BUSR3 , sku.BUSR4 , sku.BUSR5 , sku.BUSR6 ,sku.busr1 as u8,
cd.udf5 as itemno,sku.SEASON as 商标,cd.udf4,sku.style as 颜色,sku.skusize as 规格,itrn.LOTTABLE06,
itrn.LOTTABLE01,itrn.LOTTABLE07,itrn.LOTTABLE08,itrn.LOTTABLE10,c.udf3,
abs(itrn.qty) as 收货数量
from wmwhse1.itrn
left join wmwhse1.loc on loc.loc = itrn.toloc
left join wmwhse1.sku on sku.sku = itrn.sku and sku.storerkey = itrn.storerkey
left join wmwhse1.codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
left join wmwhse1.putawayzone pz on loc.putawayzone = pz.putawayzone
left join wmwhse1.codelkup cd on cd.listname=‘UDF_WLDMXT’ and sku.sku = cd.udf2 and decode(sku.storerkey,‘160’,‘150’,sku.storerkey) = cd.udf1 and cd.udf3 = itrn.lottable06
where itrn.trantype =‘DP’
union all
select
pz.descr as area ,
itrn.lottable03 as PO,itrn.lottable09 ,
itrn.adddate,
itrn.storerkey ,
itrn.sku ,sku.descr as 货品名称 ,
sku.BUSR3 , sku.BUSR4 ,
sku.BUSR5 , sku.BUSR6 ,sku.busr1 as u8,
cd.udf5 as itemno,sku.SEASON as 商标,cd.udf4,sku.style as 颜色,sku.skusize as 规格,
itrn.LOTTABLE06,itrn.LOTTABLE01,itrn.LOTTABLE07,itrn.LOTTABLE08,itrn.LOTTABLE10,c.udf3,
abs(itrn.qty) as 收货数量
from wmwhse1.itrn
left join wmwhse1.sku on sku.sku = itrn.sku and sku.STORERKEY = itrn.STORERKEY
left join wmwhse1.codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
left join wmwhse1.loc on loc.loc = itrn.toloc
left join wmwhse1.PUTAWAYZONE pz on pz.PUTAWAYZONE = loc.PUTAWAYZONE
left join wmwhse1.loc lc on lc.loc = itrn.fromloc
left join wmwhse1.PUTAWAYZONE pzz on pzz.PUTAWAYZONE = lc.PUTAWAYZONE
left join wmwhse1.codelkup cd on cd.listname=‘UDF_WLDMXT’ and sku.sku = cd.udf2 and decode(sku.storerkey,‘160’,‘150’,sku.storerkey) = cd.udf1 and cd.udf3 = itrn.lottable06
where itrn.TRANTYPE = 'MV’and pz.descr <> pzz.descr

)
where to_char(adddate+8/24,‘YYYY/MM/DD’)
between To_char(Trunc(TO_DATE(v_date_str,‘yyyy/mm/dd’)),
‘yyyy/mm/dd’)
and v_date_end
group by area,
PO,lottable09,
storerkey,sku,货品名称,BUSR3,BUSR4,BUSR5,BUSR6,u8,itemno,商标,udf4,颜色,规格,LOTTABLE06,LOTTABLE01,LOTTABLE07,LOTTABLE08,LOTTABLE10,udf3

) SHOU on SKU.库区 = SHOU.area and SKU.版本号 = SHOU.lottable09 and
SKU.po = SHOU.PO and SKU.storerkey = SHOU.storerkey
and SKU.sku = SHOU.sku and sku.udf3 = SHOU.LOTTABLE06 --and SKU.udf4 = SHOU.udf4 and SKU.udf5 = SHOU.itemno
and SKU.LOTT08 = SHOU.LOTTABLE08
left join (
select area,PO,lottable09,storerkey,
sku,货品名称,BUSR3,BUSR4,BUSR5,BUSR6,u8,itemno,商标,udf4,
颜色,规格,LOTTABLE06,LOTTABLE01,LOTTABLE07,LOTTABLE08,LOTTABLE10,udf3,
sum(abs(发货数量))as 发货数量 from
(
select pz.descr as area ,itrn.lottable03 as PO,itrn.lottable09 ,itrn.adddate,
itrn.storerkey ,
itrn.sku ,sku.descr as 货品名称 ,
sku.BUSR3 , sku.BUSR4 ,
sku.BUSR5 , sku.BUSR6 ,sku.busr1 as u8,
cd.udf5 as itemno,sku.SEASON as 商标,cd.udf4,sku.style as 颜色,sku.skusize as 规格,itrn.LOTTABLE06,itrn.LOTTABLE01,itrn.LOTTABLE07,itrn.LOTTABLE08,itrn.LOTTABLE10,c.udf3,
abs(itrn.qty) as 发货数量
from wmwhse1.itrn
left join wmwhse1.loc on loc.loc = itrn.toloc
left join wmwhse1.sku on sku.sku = itrn.sku and sku.storerkey = itrn.storerkey
left join wmwhse1.codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
left join wmwhse1.putawayzone pz on loc.putawayzone = pz.putawayzone
left join wmwhse1.codelkup cd on cd.listname=‘UDF_WLDMXT’ and sku.sku = cd.udf2 and decode(sku.storerkey,‘160’,‘150’,sku.storerkey) = cd.udf1 and cd.udf3 = itrn.lottable06
where itrn.trantype = ‘WD’
union all
select
pzz.descr as area ,itrn.lottable03 as PO,itrn.lottable09 ,itrn.adddate,
itrn.storerkey ,
itrn.sku ,sku.descr as 货品名称 ,
sku.BUSR3 , sku.BUSR4 ,
sku.BUSR5 , sku.BUSR6 ,sku.busr1 as u8,
cd.udf5 as itemno,sku.SEASON as 商标,cd.udf4,sku.style as 颜色,
sku.skusize as 规格,itrn.LOTTABLE06,itrn.LOTTABLE01,itrn.LOTTABLE07,itrn.LOTTABLE08,itrn.LOTTABLE10,c.udf3,
abs(itrn.qty) as 发货数量
from wmwhse1.itrn
left join wmwhse1.sku on sku.sku = itrn.sku and sku.STORERKEY = itrn.STORERKEY
left join wmwhse1.codelkup c on c.UDF2=sku.busr8 and c.udf1=sku.storerkey and c.listname=‘UDF_QD’
left join wmwhse1.loc on loc.loc = itrn.toloc
left join wmwhse1.PUTAWAYZONE pz on pz.PUTAWAYZONE = loc.PUTAWAYZONE
left join wmwhse1.loc lc on lc.loc = itrn.fromloc
left join wmwhse1.PUTAWAYZONE pzz on pzz.PUTAWAYZONE = lc.PUTAWAYZONE
left join wmwhse1.codelkup cd on cd.listname=‘UDF_WLDMXT’ and sku.sku = cd.udf2 and decode(sku.storerkey,‘160’,‘150’,sku.storerkey) = cd.udf1 and cd.udf3 = itrn.lottable06
where itrn.TRANTYPE = 'MV’and pz.descr <> pzz.descr

)
where to_char(adddate+8/24,‘YYYY/MM/DD’)
between v_date_str and v_date_end
group by area,PO,lottable09,storerkey,sku,货品名称,BUSR3,BUSR4,BUSR5,BUSR6,u8,itemno,商标,udf4,颜色,规格,
LOTTABLE06,LOTTABLE01,LOTTABLE07,LOTTABLE08,LOTTABLE10,udf3

) CHU on SKU.库区 = CHU.area and SKU.版本号 = CHU.lottable09 and
SKU.po = CHU.PO and SKU.storerkey = CHU.storerkey
and SKU.sku = CHU.sku and sku.udf3 = CHU.LOTTABLE06 --and SKU.对照码 = CHU.udf4 --and SKU.udf5 = CHU.itemno
and SKU.LOTT08 = CHU.LOTTABLE08
)
where 期初库存<>0 or 收货数量 <> 0 or 发货数量 <> 0
;

---

pragma autonomous_transaction;

begin
for curinv in loclist loop
irow :=UDF_SFC(curinv.库区,curinv.po ,curinv.版本号 ,curinv.storerkey ,curinv.sku ,curinv.descr ,curinv.BUSR3 , curinv.BUSR4
,curinv.BUSR5 , curinv.BUSR6,curinv.BUSR7,curinv.busr1 ,curinv.udf5,curinv.SEASON,
curinv.style ,curinv.skusize ,curinv.udf3,curinv.LOTT01 ,curinv.LOTT07 ,curinv.LOTT08 ,curinv.LOTT10 ,
curinv.期初库存 ,curinv.收货数量 ,curinv.发货数量,curinv.期末库存
);
itable.extend();
itable(itable.count) := irow;
end loop;
return itable;
end ;

函数新建完成之后我们需要将UDF表的基础数据维护一下，当UDF表新建完成之后，我们需要新建一个存储过程来为UDF表新增数据，存储过程代码如下：
CREATE OR REPLACE PROCEDURE “WMWHSE1”.“WMS_SHOUFACUN”

Authid Current_User

is

begin
–插入UDF库存表
insert into WMWHSE1.UDFINVENTORY
( INVDATE,PO,udf4 , SKU,DESCR,STORERKEY,QTY,udf1,udf2,udf3,udf5)

select to_char(sysdate,‘yyyy/mm/dd’) as invendate,
vib.lottable03 as po, vib.lottable09 as sqnum, vib.sku, vib.DESCR, vib.storerkey,
sum(vib.qty) as QTYAVAILABLE,pz.descr,vib.lottable02 as ex2,
vib.lottable06 as customer ,cd.udf5
from WMWHSE1.vibdetail vib
left join codelkup cd on cd.udf1 = vib.storerkey and cd.udf2 = vib.sku and cd.udf3 = vib.LOTTABLE06
left join WMWHSE1.loc on loc.loc = vib.LOC
left join WMWHSE1.putawayzone pz on loc.putawayzone = pz.putawayzone
where vib.qty > 0 group by cd.udf5,vib.lottable03 ,pz.descr,vib.lottable02,vib.lottable06,
vib.sku, vib.storerkey ,vib.DESCR,to_char(sysdate,‘yyyy/mm/dd’),vib.lottable09 ;

commit;

end;

下一步就是新建一个脚本来定时执行存储过程 ，脚本的代码如下
declare
job1 number;
begin
dbms_job.submit(job1, ‘WMS_SHOUFACUN;’,Trunc(Sysdate)+1 + 1/(24), ‘Trunc(Sysdate)+1 + 1/(24)’);
end;
这个脚本的意思是每天的00：00：01执行一次存储过程WMS_SHOUFACUN，新建完脚本之后最好是把时间周期调整的小一点，这样可以通过中间表的数据来判断，脚本又没有生效，脚本生效之后，下一步要做的是在函数的结尾出定义一下表类型的对象类型，定义队形类型的代码如下：

CREATE TYPE WMWHSE1.UDF_SFC as object
(
库区 nvarchar2(255),
po nvarchar2(255) ,
版本号 nvarchar2(255) ,
storerkey nvarchar2(255) ,
SKU nvarchar2(255) ,
DESCR nvarchar2(255) ,
BUSR3 nvarchar2(255) ,
BUSR4 nvarchar2(255) ,
BUSR5 nvarchar2(255) ,
BUSR6 nvarchar2(255) ,
BUSR7 nvarchar2(255) ,
busr1 nvarchar2(255) ,
udf5 nvarchar2(255) ,
SEASON nvarchar2(255) ,
style nvarchar2(255) ,
skusize nvarchar2(255) ,
udf3 nvarchar2(255) ,
LOTT01 nvarchar2(255) ,
LOTT07 nvarchar2(255) ,
LOTT08 nvarchar2(255) ,
LOTT10 nvarchar2(255) ,
期初库存 nvarchar2(255) ,
收货数量 nvarchar2(255) ,
发货数量 nvarchar2(255) ,
期末库存 nvarchar2(255)
);
GO

紧接通过过对象类新建一个表类型代码如下：
CREATE TYPE WMWHSE1.UDFTABLE AS TABLE OF WMWHSE1.UDFROW
GO

这时候函数的新建工作就完成了，可以通过一条sql语句来测速一下新建的函数：

select * from table (wmwhse1.UDF_SHOUFACUN_1(‘2020/10/23’,‘2020/10/23’))