在不同数据库中使用 CURSOR

• 下载源代码文件 - 2 KB

引言

在这篇文章中，我们将会在SQL Server、Oracle、MySQL、PostgreSQL等数据库中创建游标。在这里，我们将能够找到在不同数据库中使用相同的CURSOR子句时，实现和执行过程中的基本相似之处和不同之处。

背景

我们将要做什么？

1. 创建带有目标行的CURSOR
2. 打开/关闭CURSOR
3. 迭代/循环遍历CURSOR行
4. 根据条件跳过一行
5. 如果需要则中断循环

表和数据

让我们在开始使用游标之前创建一些示例数据。

/*table*/
--DROP TABLE People;
CREATE TABLE People (
  FirstName VARCHAR(100),
  LastName VARCHAR(100)
);

/*data*/
--DELETE FROM People;
INSERT INTO People VALUES ('Elon', 'Musk');       /*result 3*/
INSERT INTO People VALUES ('Den', 'Parker');      /*skip this item in loop, will not print*/
INSERT INTO People VALUES ('Gordon', 'Ramsay');   /*break loop here, will not print*/
INSERT INTO People VALUES ('Bryan', 'Adams');     /*result 2*/
INSERT INTO People VALUES ('Aaron', 'Lord');      /*result 1*/
INSERT INTO People VALUES ('Little', 'Alton');    /*as loop out, will not print*/

/*select*/
SELECT * FROM People ORDER BY FirstName;          /*this query will be used in all cursor*/

SQL Server

这里，我们正在为SQL Server创建一个游标。请查看消息窗口，结果应该如预期所示。

DECLARE 
    @firstName VARCHAR(100),
    @lastName VARCHAR(100),
    @fullName VARCHAR(200);
DECLARE
    peopleCrs CURSOR FOR
    SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName

OPEN peopleCrs
    FETCH NEXT FROM peopleCrs INTO @firstName, @lastName
    SET @fullName = '';

    WHILE @@FETCH_STATUS = 0    /*cursor iteration*/
    BEGIN  
        IF @firstName = 'Gordon'
        BEGIN
            BREAK;              /*get out of cursor*/
        END
        IF @firstName = 'Den'
        BEGIN
            GOTO Refatch;       /*ignore item*/
        END 

        SET  @fullName = @firstName +' ' +@lastName;
        PRINT @fullName;   

        Refatch:
            FETCH NEXT FROM peopleCrs INTO @firstName, @lastName 
    END
CLOSE peopleCrs
DEALLOCATE peopleCrs;

Oracle

这里，我们正在为Oracle创建一个游标。结果将打印在DBMS_OUTPUT消息窗口中。

DECLARE
    FIRST_NAME VARCHAR(100);
    LAST_NAME VARCHAR(100);
    FULL_NAME VARCHAR(100);
   
    CURSOR CUR_PEOPLE IS 
        SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName;
BEGIN
    OPEN CUR_PEOPLE;
        LOOP
            FETCH CUR_PEOPLE INTO FIRST_NAME, LAST_NAME;
            FULL_NAME := ''; 
            
            IF CUR_PEOPLE % NOTFOUND    /*cursor iteration*/      
            THEN
                EXIT;
            END IF;
            
            IF FIRST_NAME = 'Gordon'    /*get out of cursor*/
            THEN
                EXIT;                   
            END IF;
            IF FIRST_NAME = 'Den'       /*ignore item*/
            THEN
                CONTINUE;                   
            END IF;
            
            FULL_NAME := CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME);
            DBMS_OUTPUT.put_line(FULL_NAME);                                               
        END LOOP;   
    CLOSE CUR_PEOPLE;
END;

如果您正在使用Toad，请在Toad for Oracle中启用DBMS_OUTPUT：https://www.foxinfotech.in/2018/09/how-to-enable-dbms_output-in-toad-for-oracle.html。

MySQL

MySQL没有打印消息的选项。所以我们将SELECT整个结果。

/*create sp*/
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_people$$
CREATE
    PROCEDURE sp_people()
    BEGIN
     DECLARE first_name VARCHAR(100);
    DECLARE last_name VARCHAR(100);
    DECLARE full_name VARCHAR(200);
    DECLARE result VARCHAR(800);
    
    DECLARE people_cursor_finished BOOLEAN;
    DECLARE people_cursor CURSOR FOR 
        SELECT firstname, lastname FROM people ORDER BY Firstname ASC;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET people_cursor_finished = TRUE;

    OPEN people_cursor;
        SET result = '';
        people_cursor_loop: LOOP    
            FETCH people_cursor INTO first_name, last_name;    
            SET full_name = '';    
    
            IF people_cursor_finished THEN         /*cursor iteration*/
                LEAVE people_cursor_loop;
            END IF;                
            IF first_name = 'Gordon' THEN          /*get out of cursor*/
                LEAVE people_cursor_loop;
            END IF;    
            IF first_name = 'Den' THEN             /*ignore item*/
                ITERATE people_cursor_loop;
            END IF;    

            SET full_name = CONCAT(first_name, " ", last_name);
            SET result = CONCAT(result, ", ", full_name);
        END LOOP people_cursor_loop;
    CLOSE people_cursor;
    
    SELECT result;
    END$$
DELIMITER;

现在，调用创建的存储过程。

/*run*/
CALL sp_people();

与SQL Server或Oracle不同，在MySQL中，我们只能在存储过程或函数内部使用游标。

没有存储过程的游标，可能吗？https://stackoverflow.com/questions/14739940/can-i-run-a-loop-in-mysql-without-using-a-procedure-function

PostgreSQL

与MySQL类似，我们也将SELECT整个结果。

但是，也可以使用RAISE NOTICE打印。

/*create function*/
CREATE OR REPLACE FUNCTION fn_people()
    RETURNS VARCHAR(800) AS $$
DECLARE 
    first_name VARCHAR(100);
    last_name VARCHAR(100);
    full_name VARCHAR(200);
    result_string VARCHAR(800);    
    people_cursor CURSOR FOR 
        SELECT firstname, lastname FROM people ORDER BY Firstname ASC;
BEGIN    
    OPEN people_cursor; 
        result_string := '';
    LOOP
        full_name := '';
        FETCH people_cursor INTO first_name, last_name;
        EXIT WHEN NOT FOUND;                 /*cursor iteration*/

        IF first_name = 'Gordon' THEN 
            EXIT;                            /*get out of cursor*/
        END IF;
        IF first_name = 'Den' THEN 
            CONTINUE;                        /*ignore item*/
        END IF;

        full_name := first_name || ' ' || last_name;
        result_string := result_string || ', ' || full_name;
    END LOOP;
    CLOSE people_cursor;

    RETURN result_string;
END; $$
LANGUAGE plpgsql;

现在，调用创建的函数。

/*run*/
SELECT fn_people();

没有函数的游标，可能吗？https://stackoverflow.com/questions/2569504/how-can-i-execute-pl-pgsql-code-without-creating-a-function

限制

具体情况可能因数据库版本而异。

我正在使用的数据库版本是

• Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
• Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
• MySQL 5.5.61
• PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit

检查数据库版本

这里有一些SQL查询来检查我们正在使用的数据库版本

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/

请在附件中找到SQL文件。

历史

• 2019年5月28日：初始版本