如何检查mysql游标结果null而不使用循环 [英] How to check mysql cursor result null without using a loop

问题描述

我有一个返回varchar值的mysql函数。在这个函数里面我定义了一个只给出单个值的游标。这意味着在我的select语句中，我已经使用表主键组合具有特定的值。因为我知道这个游标只返回一个值，我不想添加一个循环来检查游标是否返回值。

  DELIMITER // 
 CREATE FUNCTION PROGRAM_API_Get_Name（
 program_id_ VARCHAR（15），
 uni_id_ VARCHAR（15），
 fac_id_ VARCHAR（15））RETURNS VARCHAR（100）
 BEGIN 
 
声明未找到的继续处理程序集done = TRUE; 
 DECLARE degree_name_ VARCHAR（100）; 
 
 DECLARE get_name_ CURSOR FOR 
 SELECT program_name 
 FROM degree_program_tab 
 WHERE program_id = program_id_ 
 AND uni_id = uni_id_ 
 AND fac_id = fac_id_; 
 
 OPEN get_name_; 
 IF（！done）THEN 
 FETCH get_name_ INTO degree_name_; 
 CLOSE get_name_; 
 RETURN degree_name_; 
 END IF; 
 
 RETURN NULL; 
 
 END // 
  

这个函数给了我下面的错误

错误代码：1064.您的SQL语法错误;检查与您的MySQL服务器版本对应的
手册，在
NOT FOUND SET done = TRUE'BEGIN DECLARE CONTINUE HANDLER'附近使用正确的
语法。 '在第5行0。

如果您知道如何解决此问题，请帮助我

 >  DELIMITER // 
 
如果存在`PROGRAM_API_Get_Name`，则删除功能// 
 
 CREATE FUNCTION`PROGRAM_API_Get_Name`（
`program_id_` VARCHAR 15），
`uni_id_` VARCHAR（15），
`fac_id_` VARCHAR（15）
）
 RETURNS VARCHAR（100）
 BEGIN 
 RETURN （SELECT`program_name` 
 FROM`degree_program_tab` 
 WHERE`program_id` =`program_id_` 
 AND`uni_id` =`uni_id_` 
 AND`fac_id` =`fac_id_`） ; 
 END // 
 
 DELIMITER; 
  

I have a mysql function that returns a varchar value. Inside this function I have define a cursor which only gives a single value. This means in my select statement, I have taken a specific value using table primary key combination. Since I know that this cursor only return one value I don't want to add a loop to check whether the cursor return a value or not.

DELIMITER //
CREATE FUNCTION PROGRAM_API_Get_Name(
        program_id_     VARCHAR(15),
        uni_id_         VARCHAR(15),
        fac_id_         VARCHAR(15)) RETURNS VARCHAR(100)
BEGIN

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE degree_name_ VARCHAR(100);

    DECLARE get_name_ CURSOR FOR
        SELECT program_name 
            FROM degree_program_tab
            WHERE program_id = program_id_
            AND   uni_id     = uni_id_
            AND   fac_id     = fac_id_;    

    OPEN get_name_;
    IF(!done) THEN
        FETCH get_name_ INTO degree_name_;
        CLOSE get_name_;
        RETURN degree_name_;
    END IF;

    RETURN NULL;

END//

This function gives me the following error

Error Code: 1064. 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 'BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; ' at line 5 0.

If you know how to overcome this, Please help me

解决方案

Maybe you can avoid the cursor with a function as:

DELIMITER //

DROP FUNCTION IF EXISTS `PROGRAM_API_Get_Name`// 

CREATE FUNCTION `PROGRAM_API_Get_Name` (
  `program_id_` VARCHAR(15),
  `uni_id_`     VARCHAR(15),
  `fac_id_`     VARCHAR(15)
)
RETURNS VARCHAR(100)
BEGIN
    RETURN (SELECT `program_name`
            FROM `degree_program_tab`
            WHERE `program_id` = `program_id_`
            AND   `uni_id`     = `uni_id_`
            AND   `fac_id`     = `fac_id_`);
END//

DELIMITER ;

这篇关于如何检查mysql游标结果null而不使用循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！