mysql - Mysql 中的光标在 Mysql 5.57 中不起作用

Question

Mysql 中的光标在 Mysql 5.57 中不起作用

我将 SQL 查询转换为 MySQL 查询，SQL 查询给我记录，但在 Mysql 查询中不提供任何记录，也没有给出任何错误

//以下是我的SQL查询

USE [Trackdb]
GO
/****** Object:  StoredProcedure [dbo].[GetRecords]    Script Date: 06-11-2019 09:48:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRecords]
AS 
BEGIN
DECLARE @Date VARCHAR(15)

create table #TempHotel
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
)

create table #TempRestaurant
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
)

DECLARE Track_CURSOR CURSOR  
LOCAL  FORWARD_ONLY  FOR  
SELECT Distinct(Convert(date,ms_date)) FROM tbltrackingrtt WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145
OPEN Track_CURSOR  
    FETCH NEXT FROM Track_CURSOR INTO  @Date
WHILE @@FETCH_STATUS = 0  
BEGIN  
    --PRINT  'EMP_ID: ' + @Date --+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  

    INSERT INTO #TempHotel
    SELECT TOP 100 Category AS CatID, Count(id) Cnt, convert(Date,ms_date) Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145 AND CONVERT(DATE,ms_date) = @Date
    GROUP BY Category, convert(Date,ms_date)
    ORDER BY convert(Date,ms_date) ASC, Count(id) DESC

    INSERT INTO #TempRestaurant
    SELECT TOP 100 Category AS CatID, Count(id) Cnt, convert(Date,ms_date) Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*169*%' AND category <> 0 AND category <> 169 AND CONVERT(DATE,ms_date) = @Date
    GROUP BY Category, convert(Date,ms_date)
    ORDER BY convert(Date,ms_date) ASC, Count(id) DESC

    FETCH NEXT FROM Track_CURSOR INTO @Date

END  
SELECT * FROM #TempHotel
SELECT * FROM #TempRestaurant

CLOSE Track_CURSOR  
DEALLOCATE Track_CURSOR 

DROP table #TempHotel
DROP table #TempRestaurant

END

// 下面是我的Mysql查询

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRecords`()
BEGIN
DECLARE NOT_FOUND INT DEFAULT 0;
DECLARE v_Date VARCHAR(15);

DECLARE Track_CURSOR 
CURSOR FOR
SELECT Distinct CAST(ms_date as Date) FROM tbltrackingrtt WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145;
create temporary table TempHotel
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);

create temporary table TempRestaurant
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);
OPEN Track_CURSOR;
   FETCH  Track_CURSOR INTO  v_Date;
WHILE NOT_FOUND = 0  
DO  
    -- PRINT  'EMP_ID: ' + @Date --+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  

    INSERT INTO TempHotel
    SELECT Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    select TempHotel;

    INSERT INTO TempRestaurant
    SELECT  Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date  FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*169*%' AND category <> 0 AND category <> 169 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    FETCH Track_CURSOR INTO v_Date;

END WHILE;  
SELECT * FROM TempHotel;
SELECT * FROM TempRestaurant

CLOSE;
DROP table TempHotel;
DROP table TempRestaurant;

END

任何人都可以帮我解决这个问题，我在这里没有做错，MySQL游标有什么问题吗

标签： mysqlsql

Answer 1

除了@solarflare 指出的语法错误之外，此代码唯一似乎有问题的是缺少处理程序，您应该查看https://dev.mysql.com/doc/refman/8.0/en/cursors。 html和https://dev.mysql.com/doc/refman/5.7/en/handler.html（您应该了解 mysql 中的错误处理）

drop procedure if exists p;
drop temporary table if exists temphotel,temprestaurant;

drop table if exists tbltrackingrtt;
create table tbltrackingrtt 
(id int auto_increment primary key,ms_date date,heirarchy varchar(20),category int);

insert into tbltrackingrtt (ms_date,heirarchy,category) values
('2017-11-02','*145*',10), ('2017-11-02','*145*',10),
('2017-11-02','*169*',10), ('2017-11-02','*145*',10);

delimiter $$

CREATE  PROCEDURE p()
BEGIN
DECLARE NOT_FOUND INT DEFAULT 0;
DECLARE v_Date VARCHAR(15);

DECLARE Track_CURSOR 
CURSOR FOR 
    SELECT Distinct CAST(ms_date as Date) 
    FROM tbltrackingrtt 
    WHERE ms_date > '2017-11-01' AND heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = TRUE;

create temporary table TempHotel
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);

create temporary table TempRestaurant
(
    CatID Varchar(50), 
    Cnt INT, 
    Date Date
);
OPEN Track_CURSOR;
   FETCH  Track_CURSOR INTO  v_Date;
WHILE NOT_FOUND = 0  DO  
    -- PRINT  'EMP_ID: ' + @Date --+  '  EMP_NAME '+@EMP_NAME +'  EMP_SALARY '  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  '  EMP_CITY ' +@EMP_CITY  

    INSERT INTO TempHotel
    SELECT Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date 
     FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*145*%' AND category <> 0 AND category <> 145 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    select * from TempHotel;

    INSERT INTO TempRestaurant
    SELECT  Category AS CatID, Count(id) Cnt, CAST(ms_date as Date) as Date  
     FROM tbltrackingrtt 
    WHERE heirarchy LIKE '%*169*%' AND category <> 0 AND category <> 169 AND CAST(ms_date as Date) = v_Date
    GROUP BY Category, CAST(ms_date as Date)
    ORDER BY CAST(ms_date as Date) ASC, Count(id) DESC LIMIT 100;

    FETCH Track_CURSOR INTO v_Date;

END WHILE;  
SELECT * FROM TempHotel;
SELECT * FROM TempRestaurant

CLOSE;
DROP table TempHotel;
DROP table TempRestaurant;

END $$

delimiter ;

call p();

+-------+------+------------+
| CatID | Cnt  | Date       |
+-------+------+------------+
| 10    |    3 | 2017-11-02 |
+-------+------+------------+
1 row in set (0.27 sec)

+-------+------+------------+
| CatID | Cnt  | Date       |
+-------+------+------------+
| 10    |    3 | 2017-11-02 |
+-------+------+------------+
1 row in set (0.29 sec)

+-------+------+------------+
| CatID | Cnt  | Date       |
+-------+------+------------+
| 10    |    1 | 2017-11-02 |
+-------+------+------------+
1 row in set (0.30 sec)