Question

sql 用户自定义函数

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE
FUNCTION F_BOM(@MD003 VARCHAR(20),@MD006 INT)
returns
@t table(MD003 VARCHAR(20),MD006 INT)
as
begin
insert into @t(MD003,MD006) select MD003,MD006*@MD006

FROM BOMMD
WHERE MD001=@MD003 AND MD014='Y'

while @@rowcount<>0
begin
insert into @t(MD003,MD006)
select
A.MD003,A.MD006*B.MD006
from
BOMMD A,@t B
where
A.MD014='Y' AND
A.MD001=B.MD003 collate Chinese_PRC_CI_AI_WS
and
not exists(select 1 from @t where MD003=A.MD003 collate Chinese_PRC_CI_AI_WS)
end
return
end

谁能帮我解释下这段代码啊？
要具体点的啊~!
@t是不是不真正存在的表啊？？

Answer 1

CREATE
FUNCTION F_BOM(@MD003 VARCHAR(20),@MD006 INT) --创建一个函数
returns
@t table(MD003 VARCHAR(20),MD006 INT) ---返回一个表，字段是自己定义的
begin
insert into @t(MD003,MD006) select MD003,MD006*@MD006 FROM BOMMD WHERE MD001=@MD003 AND MD014='Y' -----开始函数，向自己定义的表中插入数据
while @@rowcount<>0 ----如果上个语句插入了，开始下面的
insert into @t(MD003,MD006)
select
A.MD003,A.MD006*B.MD006
from
BOMMD A,@t B
where
A.MD014='Y' AND
A.MD001=B.MD003 collate Chinese_PRC_CI_AI_WS
and
not exists(select 1 from @t where MD003=A.MD003 collate Chinese_PRC_CI_AI_WS)
----根据where条件再次插入数据

Answer 2

查看BOM的物料的结构

计算BOM参照