本文介绍了如何检查mysql游标结果null而不使用循环的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个返回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 //
这个函数给了我下面的错误
如果您知道如何解决此问题,请帮助我
> 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
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而不使用循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!