SQL Basis

• Een cursor is een opgeslagen SQL query
• Gebruik een cursor om: door rijen van het resultaat te lopen
• Bewerk met een cursor het resultaat per regel om:
  • Per rij een bewerking uit te voeren
  • Per rij een administratieve taak uit te voeren. Denk aan kopiëren en logging zoals bij een backup
  • Per rij een administratieve taak uit te voeren, zoals het uitvoeren van consistentiecontroles
  • De server minder te belasten wanneer er bewerkingen op grote datasets worden gedaan op een server met weinig geheugen.
• Cursors kunnen gebruikt worden voor databaseontwikkeling door de DBA bij ETL-processen

Vaak zijn er alternatieven beschikbaar voor bovenstaande cursor-toepassingen. Het gebruik van cursors is niet altijd de beste oplossing. Veel programmeurs maken nooit gebruik van cursors, anderen alleen in hoge nood en sommige maken er veel gebruik van. Performance en de daarmee samenhangende kosten zijn een belangrijke indicator voor het inzetten van cursors.

• Een cursor in Oracle wordt doorgaans gebruikt binnen PL/SQL-blokken of procedures en is ‘embedded’ in die context
• en cursor in Oracle is read-only: de onderliggende gegevens kunnen via de cursor niet gewijzigd worden
• Een Oracle-cursor is niet scrollbaar en doorloopt de rijen van boven naar beneden
• In Oracle wordt bij het openen van een cursor een momentopname van de gegevens gemaakt, tenzij gebruik wordt gemaakt van dynamische queries

In het onderstaande voorbeeld wordt een tabel Cursus gemaakt en gevuld met dummy content. Daarna volgt een query waarbij een vergelijkbare tabel wordt gemaakt: BackupCursus, maar deze wordt niet gevuld met dummy content. Een procedure die gebruik maakt van een cursor wordt zo geconfigureerd dat hij de tabel Cursus regel voor regel doorloopt en een kopie plaatst in de tabel BackupCursus. De procedure wordt aangeroepen met een anonieme PL/SQL-block, bijvoorbeeld: BEGIN BackupProc; END; Ter afsluiting nog een query om te controleren of de tabel BackupCursus daadwerkelijk de content bevat uit de tabel Cursus.

-- Maak een lege tabel Cursus
CREATE TABLE Cursus (
   ID NUMBER PRIMARY KEY,
   TITLE VARCHAR2(100),
   COURSE_LEVEL VARCHAR2(40),
   "DATE" VARCHAR2(40)
);

-- Vul de nieuwe lege tabel met dummy content
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');

-- Maak een tweede lege tabel
CREATE TABLE BackupCursus (
   ID NUMBER,
   TITLE VARCHAR2(100),
   COURSE_LEVEL VARCHAR2(40),
   "DATE" VARCHAR2(40)
);

-- Maak een procedure die de inhoud van Cursus kopieert naar 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;
/

-- Roep de procedure aan
BEGIN
   BackupProc;
END;
/

-- Controleer of BackupCursus nu gevuld is
SELECT * FROM BackupCursus;	

• Een cursor voor MySQL is een embedded cursor
• Een embedded MySQL cursor is 'read-only'. Data kunnen niet veranderd worden.
• Een embedded MySQL cursor is 'non-scrollable'. Ofwel: doorloopt records alleen van boven naar beneden.
• Een embedded MySQL cursor is 'asensitive'. MySQL mag wel of geen kopie van de resultaatrijen gebruiken. Daarom is niet gegarandeerd of wijzigingen in de onderliggende gegevens zichtbaar zijn via de cursor.

In het onderstaande voorbeeld wordt een tabel Cursus gemaakt en gevuld met dummy content. Daarna volgt een query waarbij een vergelijkbare tabel wordt gemaakt: BackupCursus, maar deze wordt niet gevuld met dummy content. Een procedure die gebruik maakt van een cursor wordt zo geconfigureerd dat hij de tabel Cursus regel voor regel doorloopt en een kopie plaatst in de tabel BackupCursus. De procedure wordt gestart met een CALL statement. Ter afsluiting nog een querie om te controleren of de tabel BackupCursus daadwerkelijk de content bevat uit de tabel Cursus.

-- Maak een lege tabel Cursus
CREATE TABLE Cursus (
   ID INT PRIMARY KEY,
   TITLE VARCHAR(100),
   LEVEL VARCHAR(40),
   DATE VARCHAR(40)
);

-- Vul de nieuwe lege tabel met dummy content
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');

-- Maak een tweede lege tabel
CREATE TABLE BackupCursus (
   ID INT,
   TITLE VARCHAR(100),
   LEVEL VARCHAR(40),
   DATE VARCHAR(40)
);

-- Maak een procedure die de content van de Cursus tabel kopieert
-- naar de lege BackupCursus tabel m.b.v. een cursor.
-- DELIMITER is een opdracht van de mysql-client en MySQL Workbench, geen 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 ;

-- Roep de nieuwe procedure aan (voer uit)
CALL BackupProc();

-- Controleer of de nieuwe tabel gevuld is met dummy content
SELECT * FROM BackupCursus;

Onderstaande cursor voor het maken van back-ups van alle actieve databases is voor MS SQL Server. In de SQL-code moet alleen nog het juiste pad worden opgegeven voor de locatie van de back-ups.

-- SQL SERVER
-- Declaratie van variabelen
DECLARE @name VARCHAR(50) 		-- database naam 
DECLARE @path VARCHAR(256) 		-- path voor de backup files 
DECLARE @fileName VARCHAR(256)		-- bestandsnaam voor de backup 
DECLARE @fileDate VARCHAR(20)		-- gebruikt voor de bestandsnaam 

-- Verander het path naar iets anders, bv. : 'C:\Backup\'
SET @path = '/Users/mike/Backup/'

-- Nodig om datum in bestandsnaam te plaatsen
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- Definieer een cursor die de hoofdtabel van het DBMS aanspreekt
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

-- Open de cursor en doorloop de beschikbare databases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

-- Zolang er nog een database is, back-up de database als een file op het eerder opgegeven path 
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 

-- Sluit de cursor en maak het gebruikte geheugen weer vrij
CLOSE db_cursor  
DEALLOCATE db_cursor