mysql 数据库函数

输入参数：m varchar(500),jgd varchar(20),md varchar(20),cd varchar(20),qk varchar(20),start_date varchar(20),end_date varchar(20)

BEGINdeclare dmo1 decimal(20,2) default 0; -- 售罄率1分母declare moe1 decimal(20,2) default 0; -- 售罄率1分子declare sale_fdate VARCHAR(20); -- 销售首次调拨日期declare sale_fkdat VARCHAR(20); -- 销售日期declare sale_zzcbrmb decimal(20,2); -- 销售单件成本declare stock_fdate VARCHAR(20); -- 库存首次调拨日期declare stock_zzcbrmb decimal(20,2); -- 库存单件成本declare sale_netwr decimal(20,2); -- 金额（判断退货标志）declare rate1 decimal(20,4); -- 售罄率1declare _now VARCHAR(20); -- 当前日期declare done int default 0; -- 循环暂停标志-- 定义游标DECLARE sale_cursor CURSOR FOR SELECT (CASE zzcbrmb when '' then 0.0 else  zzcbrmb end) zzcbrmb,fdate,fkdat,netwr from T_SALE_DATA where matnr=m AND (zjgd=jgd OR jgd IS NULL) AND (werks=md OR md IS NULL) AND (zcdlzhp=cd OR cd IS NULL) AND (zqk=qk OR qk IS NULL) AND fkdat BETWEEN start_date AND end_date;-- 取销售数据DECLARE stock_cursor CURSOR FOR SELECT (CASE zzcbrmb when '' then 0.0 else  zzcbrmb end) zzcbrmb,fdate from T_STOCK_DATA where matnr=m AND (zjgd=jgd OR jgd IS NULL) AND (werks=md OR md IS NULL) AND (zcdlzhp=cd OR cd IS NULL) AND (zqk=qk OR qk IS NULL) AND zkcrq=end_date;-- 取库存数据declare continue handler FOR SQLSTATE '02000' SET done = 1;  SET _now=DATE_FORMAT(NOW(),'%Y-%m-%d');open sale_cursor; open stock_cursor;  repeat     FETCH sale_cursor into sale_zzcbrmb,sale_fdate,sale_fkdat,sale_netwr; -- 取销售数据if done<>1 THEN IF DATEDIFF(_now,DATE_FORMAT(sale_fdate,'%Y-%m-%d'))>=30 THENIF sale_netwr<0 THEN -- 判断是否退货SET sale_zzcbrmb=-sale_zzcbrmb;-- 金额取负END IF;-- 售罄率1SET dmo1=dmo1+sale_zzcbrmb;IF DATEDIFF(DATE_FORMAT(sale_fkdat,'%Y-%m-%d'),DATE_FORMAT(sale_fdate,'%Y-%m-%d'))<=30 THENSET moe1=moe1+sale_zzcbrmb;END IF; END IF;END IF;until done end repeat; close sale_cursor;SET done=0; repeat   FETCH stock_cursor into stock_zzcbrmb,stock_fdate; -- 取库存数据if done<>1 THEN IF DATEDIFF(_now,DATE_FORMAT(stock_fdate,'%Y-%m-%d'))>=30 THEN-- 售罄率1SET dmo1=dmo1+stock_zzcbrmb;   END IF;END IF;   until done end repeat; close stock_cursor;-- 判断分母为0    IF dmo1=0 THENSET rate1=100;ELSESET rate1=moe1/dmo1;END IF;RETURN rate1;END