尝试转换 Microsoft SQL 函数后 MySQL 出错

Question

我正在尝试将我在 Microsoft SQL 中创建的函数转换为 MySQL 的函数，但我完全不知道如何做到这一点。我尝试通过 SQLines 转换原始代码并对其进行修改，但无济于事。

有效的微软代码是

 ALTER FUNCTION [dbo].[TotalTripsGuideFunc] (@guideid CHAR(4))
 RETURNS VARCHAR
 BEGIN
    DECLARE @trip_counts INT
    DECLARE @results VARCHAR
    SELECT @trip_counts = COUNT(*) FROM dbo.Reservation,dbo.TripGuides WHERE Reservation.TripID = TripGuides.TripID AND TripGuides.GuideNum = @guideid
    SELECT @results = @guideid + ' has ' + CAST (@trip_counts AS VARCHAR(4))+ ' guides '
 RETURN @results
 END

尝试的 MySql 代码是

DELIMITER //

CREATE FUNCTION TotalTripsGuideFunc (p_guideid CHAR(4))
 RETURNS VARCHAR(1)
 BEGIN
    DECLARE v_trip_counts INT DEFAULT 0;
    DECLARE v_results VARCHAR(1);
    SELECT COUNT(*) INTO v_trip_counts FROM Reservation,TripGuides 
    WHERE Reservation.TripID = TripGuides.TripID AND TripGuides.GuideNum = p_guideid;
    SELECT v_results = concat(p_guideid , ' has ', CAST(v_trip_counts AS CHAR), ' guides ');
    RETURN v_results;
END;
//

DELIMITER ;

返回错误

1415 - Not allowed to return a result set from a function

编辑

这是修改后的代码

DELIMITER //

CREATE FUNCTION TotalTripsGuideFunc (p_guideid CHAR(4))
 RETURNS VARCHAR
 BEGIN
    DECLARE v_trip_counts INT DEFAULT 0;
    DECLARE v_results VARCHAR(30);
    SELECT COUNT(*) INTO v_trip_counts FROM Reservation,TripGuides 
    WHERE Reservation.TripID = TripGuides.TripID AND TripGuides.GuideNum = p_guideid;
    SET v_results = concat(p_guideid , ' has ', v_trip_counts, ' guides ');
    RETURN v_results;
END;
//

DELIMITER ;

返回新错误

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN DECLARE v_trip_counts INT DEFAULT 0; DECLARE v_results VARCHAR(3' at line 3

Answer 1

小错误:

DECLARE v_results VARCHAR(1); 应该像 DECLARE v_results VARCHAR(30); 一样大一点

RETURNS VARCHAR(1) 应为 RETURNS VARCHAR

您在第一个选择中使用了select into，但在第二个选择中使用了错误。只需要修复它:

-- from
SELECT v_results = concat(p_guideid , ' has ', CAST(v_trip_counts AS CHAR), ' guides ');
-- to
SELECT concat(p_guideid , ' has ', v_trip_counts, ' guides ') INTO v_results;
-- OR just:
SET v_results = concat(p_guideid , ' has ', v_trip_counts, ' guides ');
-- no need for CAST, MySQL will do implicit conversion

编辑:这是一个有效的最终版本:

DELIMITER //
CREATE FUNCTION TotalTripsGuideFunc (p_guideid VARCHAR(30)) RETURNS VARCHAR(30)
BEGIN
    DECLARE v_trip_counts INTEGER;
    DECLARE v_results VARCHAR(30);
    SELECT COUNT(*) INTO v_trip_counts FROM Reservation,TripGuides 
    WHERE Reservation.TripID = TripGuides.TripID AND TripGuides.GuideNum = p_guideid;
    SET v_results = concat(p_guideid , ' has ', v_trip_counts, ' guides ') ;
    RETURN v_results;
END
//
DELIMITER ;