Introduction
In this post, we are going to create a cursor in databases like SQL Server, Oracle, MySQL, PostgreSQL. Here, we will be able to find basic similarities and differences between implementation and execution process while working with the same CURSOR clause in different databases.
Background
What are we going to do?
- Create a CURSOR with targeted rows
- Open/Close a CURSOR
- Iterate/loop through CURSOR rows
- Skip a row depending on conditions
- Break loop if needed
Table and Data
Let us create some sample data before getting started with the cursor.
CREATE TABLE People (
FirstName VARCHAR(100),
LastName VARCHAR(100)
);
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
Here, we are creating a cursor for SQL Server. Have a look at the message window, the result should look as expected.
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
Here, we are creating a cursor for Oracle. The result will be printed at the DBMS_OUTPUT
message window.
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;
If you are using Toad, enable DBMS_OUTPUT
in Toad for Oracle: https://www.foxinfotech.in/2018/09/how-to-enable-dbms_output-in-toad-for-oracle.html.
MySQL
MySQL doesn't have the option to print a message. So we are going to SELECT
the entire result.
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;
Now, calling the created stored procedure.
CALL sp_people();
Unlike SQL Server or Oracle, in MySQL, we can use a cursor only inside a stored procedure or a function.
Cursor without a stored procedure, possible or not: https://stackoverflow.com/questions/14739940/can-i-run-a-loop-in-mysql-without-using-a-procedure-function
PostgreSQL
Like MySQL, here we are also going to SELECT
the entire result.
But there is also an option of print, using RAISE NOTICE
.
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;
Now, calling the created function.
SELECT fn_people();
Cursor without a function, possible or not: https://stackoverflow.com/questions/2569504/how-can-i-execute-pl-pgsql-code-without-creating-a-function
Limitations
Things may vary depending on database versions.
My working database versions are:
- 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
Check Database Version
Here are few SQL queries to check our working database versions:
SELECT @@VERSION;
SELECT * FROM V$VERSION;
SELECT VERSION();
SELECT VERSION();
Please find the SQL files as an attachment.
History
- 28th May, 2019: Initial version