mysql自定义函数完成项目功能
CREATE
FUNCTION `get_out_storage_qty`
(
bill_codes VARCHAR(64),
symbol INTEGER
)
RETURNS INTEGER
BEGIN
DECLARE v1 VARCHAR(20); if symbol = 0 THEN
SET
v1 = '+' ; ELSE
SET
v1 = '-';
END
if; RETURN (SELECT
SUM(o.qty) QTY
FROM
( SELECT
wsl.qty,
CONCAT(wsl.id,'b') id,
(
CASE
WHEN wsl.qty <= 0
THEN '+'
WHEN wsl.qty > 0
THEN '-'
END ) STATE
FROM
wrm_stock_log wsl
WHERE
wsl.BILL_CODE IN (bill_codes)
GROUP BY
wsl.id ,
(
CASE
WHEN wsl.qty <= 0
THEN '+'
WHEN wsl.qty > 0
THEN '-'
END )
)
o
GROUP BY
o.STATE
HAVING
o.STATE = v1
)
;
END
调用
select get_out_storage_qty('FP101801150003',0)