mysql自定义函数

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)