在数据库编程中，游标是一种重要的机制，它允许开发者逐行处理查询结果。不同的数据库系统，如SQL Server、Oracle、MySQL和PostgreSQL，提供了各自独特的游标实现方式。本文旨在探讨这些数据库系统中游标操作的相似之处和差异性。
将通过创建一个游标，打开和关闭游标，遍历游标行，根据条件跳过行，以及在需要时中断循环等操作来比较这些数据库系统。
在开始使用游标之前，先创建一些示例数据。
-- 创建表
DROP TABLE People;
CREATE TABLE People (
FirstName VARCHAR(100),
LastName VARCHAR(100)
);
-- 插入数据
DELETE FROM People;
INSERT INTO People VALUES ('Elon', 'Musk');
INSERT INTO People VALUES ('Den', 'Parker');
INSERT INTO People VALUES ('Gordon', 'Ramsay'); -- 循环中将跳过此条数据
INSERT INTO People VALUES ('Bryan', 'Adams'); -- 循环中将中断
INSERT INTO People VALUES ('Aaron', 'Lord');
INSERT INTO People VALUES ('Little', 'Alton');
-- 查询
SELECT * FROM People ORDER BY FirstName;
在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
BEGIN
IF @firstName = 'Gordon' BEGIN BREAK; END -- 退出游标
IF @firstName = 'Den' BEGIN GOTO Refatch; END -- 忽略此条数据
SET @fullName = @firstName + ' ' + @lastName;
PRINT @fullName;
Refatch: FETCH NEXT FROM peopleCrs INTO @firstName, @lastName;
END
CLOSE peopleCrs;
DEALLOCATE peopleCrs;
在Oracle中，创建游标并打印结果。
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 THEN EXIT; END IF;
IF FIRST_NAME = 'Gordon' THEN EXIT; END IF; -- 退出游标
IF FIRST_NAME = 'Den' THEN CONTINUE; END IF; -- 忽略此条数据
FULL_NAME := CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME);
DBMS_OUTPUT.put_line(FULL_NAME);
END LOOP;
CLOSE CUR_PEOPLE;
END;
MySQL中没有直接打印消息的选项，因此将选择整个结果。
-- 创建存储过程
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 LEAVE people_cursor_loop; END IF;
IF first_name = 'Gordon' THEN LEAVE people_cursor_loop; END IF; -- 退出游标
IF first_name = 'Den' THEN 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 ;
在PostgreSQL中，选择整个结果。
-- 创建函数
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;
IF first_name = 'Gordon' THEN EXIT; END IF; -- 退出游标
IF first_name = 'Den' THEN CONTINUE; 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;
不同的数据库版本可能会有所不同。以下是所使用的数据库版本：
-- SQL Server
SELECT @@VERSION;
-- Oracle
SELECT * FROM V$VERSION;
-- MySQL
SELECT VERSION();
-- PostgreSQL
SELECT VERSION();