SQL Basics

• A cursor provides access to the result of a SQL query
• Use a cursor to iterate through the rows in a result
• Process the result one row at a time to:
  • Perform an operation on each row
  • Perform an administrative task for each row, such as copying or logging during a backup
  • Perform administrative work such as consistency checks
  • Reduce peak memory use when processing a large result on a server with limited memory
• Cursors can support database development by a DBA during ETL processes

There are often alternatives to these cursor-based operations. A cursor is not always the best solution. Some programmers never use them, others use them only as a last resort and others use them frequently. Performance and the associated cost are important considerations.

• An Oracle cursor is usually used within a PL/SQL block or procedure and is embedded in that context
• This Oracle cursor reads rows; it does not modify the underlying data through the cursor
• This Oracle cursor is not scrollable and processes rows from beginning to end
• Oracle provides a consistent view of the data for the query when the cursor is opened

The example creates Cursus and fills it with sample data. It then creates an equivalent empty table, BackupCursus. A procedure uses a cursor to read Cursus one row at a time and copy each row into BackupCursus. Invoke it from an anonymous PL/SQL block, for example BEGIN BackupProc; END;. Finally, query BackupCursus to check that the data was copied.

-- Create an empty Cursus table
CREATE TABLE Cursus (
   ID NUMBER PRIMARY KEY,
   TITLE VARCHAR2(100),
   COURSE_LEVEL VARCHAR2(40),
   "DATE" VARCHAR2(40)
);

-- Fill the new table with sample data
INSERT INTO Cursus VALUES(1, 'Oracle', 'Hard', '2022-09-01');
INSERT INTO Cursus VALUES(2, 'MySQL', 'Easy', '2020-05-01');
INSERT INTO Cursus VALUES(3, 'SQL Server', 'Medium', '2020-05-01');
INSERT INTO Cursus VALUES(4, 'MariaDB', 'Novice', '2019-03-01');
INSERT INTO Cursus VALUES(5, 'SQLite', 'Expert', '2019-04-06');

-- Create a second empty table
CREATE TABLE BackupCursus (
   ID NUMBER,
   TITLE VARCHAR2(100),
   COURSE_LEVEL VARCHAR2(40),
   "DATE" VARCHAR2(40)
);

-- Create a procedure that copies Cursus into BackupCursus
CREATE OR REPLACE PROCEDURE BackupProc IS
   CURSOR cur IS SELECT ID, TITLE, COURSE_LEVEL, "DATE" FROM Cursus;
   rec cur%ROWTYPE;
BEGIN
   OPEN cur;
   LOOP
      FETCH cur INTO rec;
      EXIT WHEN cur%NOTFOUND;
      INSERT INTO BackupCursus VALUES(rec.ID, rec.TITLE, rec.COURSE_LEVEL, rec."DATE");
   END LOOP;
   CLOSE cur;
END;
/

-- Call the procedure
BEGIN
   BackupProc;
END;
/

-- Check that BackupCursus now contains the copied data
SELECT * FROM BackupCursus;	

• A MySQL cursor is embedded within a stored program
• A MySQL cursor is read-only: it cannot update data through the cursor
• It is non-scrollable, so rows are fetched only from beginning to end
• It is asensitive: MySQL may or may not copy the result rows. Therefore, visibility of changes to the underlying data is not guaranteed.

The example creates Cursus with sample data and an equivalent empty table, BackupCursus. A procedure uses a cursor to copy rows from Cursus into BackupCursus one at a time. Start the procedure with CALL, then query BackupCursus to check that the data was copied.

-- Create an empty Cursus table
CREATE TABLE Cursus (
   ID INT PRIMARY KEY,
   TITLE VARCHAR(100),
   LEVEL VARCHAR(40),
   DATE VARCHAR(40)
);

-- Fill the new table with sample data
insert into Cursus values(1, 'Oracle', 'Hard', '2022-09-01');
insert into Cursus values(2, 'MySQL', 'Easy', '2020-05-01');
insert into Cursus values(3, 'SQL Server', 'Medium', '2020-05-01');
insert into Cursus values(4, 'MariaDB', 'Novice', '2019-03-01');
insert into Cursus values(5, 'SQLite', 'Expert', '2019-04-06');

-- Create a second empty table
CREATE TABLE BackupCursus (
   ID INT,
   TITLE VARCHAR(100),
   LEVEL VARCHAR(40),
   DATE VARCHAR(40)
);

-- Create a procedure that copies the contents of Cursus
-- into the empty BackupCursus table using a cursor.
-- DELIMITER is a mysql client and MySQL Workbench command, not SQL.
DELIMITER //
CREATE PROCEDURE BackupProc()
   BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE CursusID INTEGER;
      DECLARE CursusTitle VARCHAR(100);
      DECLARE CursusLevel, CursusDate VARCHAR(40);
      DECLARE cur CURSOR FOR SELECT * FROM Cursus;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      OPEN cur;
      label: LOOP
      FETCH cur INTO CursusID, CursusTitle, CursusLevel, CursusDate;
      IF done = 1 THEN
         LEAVE label;
      END IF;
      INSERT INTO BackupCursus VALUES(CursusID, CursusTitle, CursusLevel, CursusDate);
      END LOOP;
      CLOSE cur;
   END//
DELIMITER ;

-- Call the new procedure
CALL BackupProc();

-- Check that the new table contains the sample data
SELECT * FROM BackupCursus;

The cursor below backs up all active databases in Microsoft SQL Server. Set the backup directory to the appropriate location in the SQL code.

-- SQL SERVER
-- Declare variables
DECLARE @name VARCHAR(50) 		-- database name 
DECLARE @path VARCHAR(256) 		-- path for the backup files 
DECLARE @fileName VARCHAR(256)		-- backup filename 
DECLARE @fileDate VARCHAR(20)		-- used to construct the filename 

-- Change the path as needed, for example: 'C:\Backup\'
SET @path = '/Users/mike/Backup/'

-- Used to include the date in the filename
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- Define a cursor that queries the DBMS system catalog
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

-- Open the cursor and iterate through the available databases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

-- While a database remains, back it up to a file in the specified directory 
WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
      BACKUP DATABASE @name TO DISK = @fileName 
      FETCH NEXT FROM db_cursor INTO @name 
END 

-- Close the cursor and release its resources
CLOSE db_cursor  
DEALLOCATE db_cursor