mysql编写函数tax_mysql 函数

-- 函数

CREATE DEFINER=`taxms`@`10.%` FUNCTION `getPartVATchildren`(

p_root VARCHAR (50),

p_GJAHR VARCHAR (4),

p_MONAT VARCHAR (2),

p_SUBTYPE VARCHAR (60)

) RETURNS varchar(5000) CHARSET utf8

READS SQL DATA

DETERMINISTIC

BEGIN

DECLARE returnstring VARCHAR (5000) DEFAULT '' ;

DECLARE tmpstring VARCHAR (5000) ;

SET tmpstring = p_root ;

WHILE

tmpstring IS NOT NULL DO SET returnstring = CONCAT(returnstring, ',', tmpstring) ;

SELECT

GROUP_CONCAT(b.TAX_PAY_SUB) INTO tmpstring

FROM

taxms.b_ztfi_tax_detail b

JOIN taxms.b_ztfi_tax_control c

ON (c.TAX_PAY_SUB = b.TAX_PAY_SUB AND NOW() BETWEEN c.BEGIN_TIME AND c.END_TIME)

WHERE FIND_IN_SET(b.TAX_ENT_COL, tmpstring) > 0

AND b.GJAHR = p_GJAHR

AND b.MONAT = p_MONAT

AND b.SUBTYPE = p_SUBTYPE

AND c.VAT_SUM = '3' ;

END WHILE ;

RETURN returnstring ;

END

分享到：

2017-05-24 11:30

浏览 285

分类:数据库

评论