Cursors

De wikijoan
Salta a la navegació Salta a la cerca

Cursors

Els cursors serveixen per iterar a través de totes les files (recordset) que ha retornat una sentència SELECT. Recórrer totes les files d'una SELECT és com fer un bucle.

els cursors a MySQL són de només-lectura (read-only), non-scrollable i asensitive:

  • Read-only: la taula sobre la que recorrem el cursor no es pot actualitzar/modificar mentre l'estem recorrent.
  • Non-scrollable: només podem agafar les files en l'ordre que ens proporciona la SELECT. Tampoc podem canviar l'ordre, saltar files, anar a una fila concreta,...
  • Asensitive: hi ha dos tipus de cursors: asensitive i insensitive. Els cursors asensitive apunten a la data actual, mentre que els cursors insensitive treballen amb una còpia temporal de les dades. Els asensitive són més ràpids perquè no han de fer cap còpia temporal. S'ha d'anar en compte amb els cursors asensitive si fem canvis (updates) en les dades, doncs les dades també es poden modificar des d'altres connexions que hi poden haver a la base de dades.

Per declarar un cursor l'hem de declarar després de què haguem declarat les variables (si no, dóna error):

DECLARE cursor_name CURSOR FOR SELECT_statement;

Després de declarar-lo ja podem obrir el cursor:

OPEN cursor_name;

Amb el OPEN estem inicialitzant el resultset del cursor. Un cop obert, ja podem agafar les dades:

FETCH cursor_name INTO variables list;

Agafarem les dades a les que apunta el cursor. Cada vegada que em un FETCH el cursor apuntarà a la línia següent. Necessitarem algun sistema de bucle per anar navegant a través del recordset, i anar agafant totes les dades.

Finalment, ja podem tancar el cursor:

CLOSE cursor_name;

També haurem de controlar què passarà quan arribem al final del cursor. Podem declarar un handler:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Exemple de cursor

Tenim la taula employees de la base de dades classicmodels.

Creem el procediment createEmailList() de la següent manera:

DELIMITER $$
CREATE PROCEDURE createEmailList (
	INOUT emailList varchar(4000)
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail CURSOR FOR SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

	OPEN curEmail;

	getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
	CLOSE curEmail;

END$$
DELIMITER ;

Per provar aquest procediment:

SET @emailList = ""; 
CALL createEmailList(@emailList); 
SELECT @emailList;

Gestió d'errors (error handling)

De forma general un handler el declarem de la següent manera:

DECLARE action HANDLER FOR condition_value statement;
  • action pot ser: CONTINUE o bé EXIT.
  • La condition_value és la condició que activa el gestor. Pot ser un codi d'error de MySQL, o bé un SQLSTATE, SQLWARNING , NOTFOUND o SQLEXCEPTION

Per exemple:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasError = 1;

Si es produeix una excepció el procediment continua, i la variable hasError pren el valor 1.

Una declaració més complicada:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

La que ja hem vist que pot estar associada a un cursor:

DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET RowNotFound = 1;

Per exemple, si fem un insert a dins d'un cursor i ens trobem amb una clau duplicada, podem gestionar-ho de la següent manera:

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

Exemple de gestió d'errors

CREATE TABLE SupplierProducts (
    supplierId INT,
    productId INT,
    PRIMARY KEY (supplierId , productId)
);

I creem el procediment InsertSupplierProduct():

CREATE PROCEDURE InsertSupplierProduct(
    IN inSupplierId INT, 
    IN inProductId INT
)
BEGIN
    -- exit if the duplicate key occurs
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
 	SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);
    
    -- return the products supplied by the supplier id
    SELECT COUNT(*) 
    FROM SupplierProducts
    WHERE supplierId = inSupplierId;
    
END$$

DELIMITER ;

Fem la inserció de 3 productes:

CALL InsertSupplierProduct(1,1);
CALL InsertSupplierProduct(1,2);
CALL InsertSupplierProduct(1,3);

I si repetim un producte provocarem l'excepció:

CALL InsertSupplierProduct(1,3);

+------------------------------+
| message                      |
+------------------------------+
| Duplicate key (1,3) occurred |
+------------------------------+
1 row in set (0.01 sec

Sentència SIGNAL


creat per Joan Quintana Compte, març 2022