快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

在不同的数据库中使用CURSOR

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

介绍

在本文中,我们将在SQL Server,Oracle,MySQL,PostgreSQL等数据库中创建游标。在这里,当在不同数据库中使用相同的CURSOR子句时,我们将能够发现实现和执行过程之间的基本异同。

背景

我们会做什么?

  1. 创建具有目标行的CURSOR
  2. 打开/关闭游标
  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服务器

在这里,我们为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创建一个游标。结果将打印在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,请DBMS_OUTPUT在Toad for Oracle中启用: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企业版11.2.0.4.0版-64位生产
  • MySQL 5.5.61
  • PostgreSQL 10.5,由Visual C ++ build 1800 64位编译

检查数据库版本

以下是一些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文件作为附件。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638