主页 / dba / 问题 / 111666
Accepted
Bikram Pahi
Asked: 2015-08-21 01:21:19 +0800 CST

如何从存储过程中获取数组作为输出参数？

  • 772

这是我的存储过程：

CREATE PROCEDURE `getAttritionData`(
 IN `supid` BIGINT, 
 IN `from_date` DATE, 
 IN `to_date` DATE, 
 OUT `attr_rate` FLOAT, 
 OUT `TERM_EMP_CNT` INT
) 
DETERMINISTIC 
BEGIN 
 DECLARE i INTEGER DEFAULT 0; 
 DECLARE CNT_EMP INTEGER DEFAULT 0; 
 DECLARE TOT_EMP INTEGER DEFAULT 0; 
 DECLARE AVG_HCOUNT INTEGER DEFAULT 0; 

 SELECT count(employee_id) INTO TERM_EMP_CNT FROM employee 
  WHERE term_date between from_date and to_date AND app_role=1; 
 WHILE from_date<=to_date DO 
   SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP FROM employee 
    WHERE sup_id=supid AND doj<from_date 
    AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1; 
   SET from_date=date_add(from_date, INTERVAL 1 DAY); 
   SET i=i+1; 
   SET TOT_EMP = TOT_EMP + CNT_EMP; 
 END WHILE; 
 SET AVG_HCOUNT=(TOT_EMP/i)*365/12; 
 SET attr_rate = TERM_EMP_CNT/AVG_HCOUNT; 
END

我想要attr_rate一个数组，它有 1 个键和另一个值，例如：

attr_rate[6-2015]=3.5
attr_rate[7-2015]=6.2
attr_rate[8-2015]=3.3

如何在 MySQL 存储过程中声明数组？

mysql stored-procedures

1 个回答

  1. Best Answer

    存储过程不支持数组，可以使用临时表。

    试试这个代码：

    CREATE PROCEDURE `getAttritionData`(IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT) 
DETERMINISTIC 
BEGIN 
    DECLARE i INTEGER DEFAULT 0;
    DECLARE CNT_EMP INTEGER DEFAULT 0;
    DECLARE CNT_EMP_SUS INTEGER DEFAULT 0;
    DECLARE TOT_EMP INTEGER DEFAULT 0;
    CREATE TEMPORARY TABLE T1(CDATE DATE,TOTAL_EMP INTEGER, TOTAL_SUSPENDED INTEGER);
    WHILE from_date<=to_date DO
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP 
        FROM employee 
        WHERE sup_id=supid AND doj<from_date AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1;
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP_SUS 
        FROM employee 
        WHERE sup_id=supid AND term_date=from_date AND app_role=1;
        INSERT INTO T1(CDATE,TOTAL_EMP,TOTAL_SUSPENDED) VALUES (from_date,CNT_EMP,CNT_EMP_SUS);
        SET from_date=date_add(from_date, INTERVAL 1 DAY);
    END WHILE;
    Select * from T1;
    SELECT *,count(CDATE),sum(TOTAL_EMP),SUM(TOTAL_SUSPENDED),((SUM(TOTAL_EMP)/count(CDATE))*365/12) AVG_HEAD_COUNT, (SUM(TOTAL_SUSPENDED)/((SUM(TOTAL_EMP)/count(CDATE))*365/12)) atr_rate FROM T1 group by MONTH(CDATE);

END

    希望你的问题能得到解决。

    • 1

相关问题