java - MySQL 函数在像(Toad)这样的 MySQL 数据库管理器中工作正常，但在 Java 中却不行

Question

我编写了 mysql 函数，它在 Toad/MySQL workbench/Heidi 中运行良好。但它给出了以下语法错误

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION FindAcid ( cust_id_in VARCHAR(20) )RETURNS VARCHAR(20) BEGIN DEC' at line 1

下面是我的查询示例:

DROP FUNCTION IF EXISTS FindAcid; 
            CREATE FUNCTION FindAcid ( cust_id_in VARCHAR(20) ) 
            RETURNS VARCHAR(20) 
            BEGIN 
                 DECLARE done INT DEFAULT FALSE; 
                 DECLARE div_val VARCHAR(128); 
                 DECLARE for_acid VARCHAR(16); 
                 DECLARE divval VARCHAR(128); 
                 DECLARE rtnforacid  VARCHAR(16); 
                 DECLARE scheme_type VARCHAR(10); 
                 DECLARE rtn VARCHAR(10); 
                 DECLARE cur CURSOR FOR 
                 SELECT (appDiv.clr_bal_amt_lkr /(SELECT SUM(COALESCE(appSum.CLR_BAL_AMT_LKR, 0)) AS PORTPOLIO FROM app_dms_daily appSum WHERE appSum.cust_id = appDiv.cust_id GROUP BY appDiv.cust_id )) AS DIV_VAL, schm_type AS SCHM_TYPE, FORACID AS FORACID 
                 FROM app_dms_daily appDiv 
                 WHERE appDiv.cust_id = cust_id_in 
                 ORDER BY DIV_VAL DESC; 
                 DECLARE cur_acid CURSOR FOR 
                 SELECT DISTINCT(COALESCE(foracid, '--')) AS EE 
                 FROM app_dms_daily 
                 WHERE CLR_BAL_AMT_LKR = (SELECT MAX(CLR_BAL_AMT_LKR) FROM app_dms_daily maxapp WHERE maxapp.CUST_ID = cust_id_in AND maxapp.SCHM_TYPE = 'ODA'); 
                 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 
            OPEN cur; 
            OPEN cur_acid; 
            read_loop: LOOP 
            FETCH  cur INTO div_val, scheme_type, for_acid; 
            IF done THEN 
                 LEAVE read_loop; 
            END IF; 
            END LOOP; 
            if div_val >= 0.3 AND scheme_type = 'ODA' then 
            read_loop_acid: LOOP 
            FETCH  cur_acid INTO rtn; 
            IF done THEN 
                 LEAVE read_loop_acid; 
            END IF; 
            END LOOP; 
            SET rtnforacid = rtn; 
            end if; 
            CLOSE cur; 
            CLOSE cur_acid; 
            RETURN rtnforacid; 
            END;

SELECT FindAcid('300315759');

这是我的java代码

String query ="DROP FUNCTION IF EXISTS FindAcid; "
            + "CREATE FUNCTION FindAcid ( cust_id_in VARCHAR(20) ) "
            + "RETURNS VARCHAR(20) "
            + "BEGIN "
            + "DECLARE done INT DEFAULT FALSE; "
            + "DECLARE div_val VARCHAR(128); "
            + "DECLARE for_acid VARCHAR(16); "
            + "DECLARE divval VARCHAR(128); "
            + "DECLARE rtnforacid  VARCHAR(16); "
            + "DECLARE scheme_type VARCHAR(10); "
            + "DECLARE rtn VARCHAR(10); "
            + "DECLARE cur CURSOR FOR "
            + "SELECT (appDiv.clr_bal_amt_lkr /(SELECT SUM(COALESCE(appSum.CLR_BAL_AMT_LKR, 0)) AS PORTPOLIO FROM app_dms_daily appSum WHERE appSum.cust_id = appDiv.cust_id GROUP BY appDiv.cust_id )) AS DIV_VAL, schm_type AS SCHM_TYPE, FORACID AS FORACID "
            + "FROM app_dms_daily appDiv "
            + "WHERE appDiv.cust_id = cust_id_in "
            + "ORDER BY DIV_VAL DESC; "
            + "DECLARE cur_acid CURSOR FOR "
            + "SELECT DISTINCT(COALESCE(foracid, '--')) AS EE "
            + "FROM app_dms_daily "
            + "WHERE CLR_BAL_AMT_LKR = (SELECT MAX(CLR_BAL_AMT_LKR) FROM app_dms_daily maxapp WHERE maxapp.CUST_ID = cust_id_in AND maxapp.SCHM_TYPE = 'ODA'); "
            + "DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; "
            + "OPEN cur; "
            + "OPEN cur_acid; "
            + "read_loop: LOOP "
            + "FETCH  cur INTO div_val, scheme_type, for_acid; "
            + "IF done THEN "
            + "LEAVE read_loop; "
            + "END IF; "
            + "END LOOP; "
            + "if div_val >= 0.3 AND scheme_type = 'ODA' then "
            + "read_loop_acid: LOOP "
            + "FETCH  cur_acid INTO rtn; "
            + "IF done THEN "
            + "LEAVE read_loop_acid; "
            + "END IF; "
            + "END LOOP; "
            + "SET rtnforacid = rtn; "
            + "end if; "
            + "CLOSE cur; "
            + "CLOSE cur_acid; "
            + "RETURN rtnforacid; "
            + "END;";

    getJdbcTemplate().execute(query);

Answer 1

你没有粘贴 Java 代码所以我从你的异常中猜测你在用 sql 创建字符串时做了这样的事情:

"CREATE FUNCTION FindAcid ( cust_id_in VARCHAR(20) )" + 
"RETURNS VARCHAR(20)" + 
"BEGIN" + ...

您必须在行尾添加空格字符:

"CREATE FUNCTION FindAcid ( cust_id_in VARCHAR(20) ) " + 
"RETURNS VARCHAR(20) " + 
"BEGIN " + ...