6. Utilització de cursors

Un cursor és una variable que ens permet desplaçar-nos per les files d’una consulta de SQL.

Hi ha dos tipus de cursors, els explícits i els implícits.

Els explícits s'han de declarar expressament en la zona de declaracions. Els implícits no, seran únicament una sentència SQL que guarda el resultat en una variable. Porten implícita tota la mecànica dels cursors (declarar, obrir, utilitzar, tancar) que haurem de fer en els explícits.

CURSORS IMPLÍCITS

Consistiran en una sentència SQL dins d'un procediment o funció PL/pgSQL. Han de tornar només un valor (només una fila), i es guardaran en una variable per mig del INTO de la sentència SQL. Per exemple, la següent funció tornarà el número de poblacions.

CREATE FUNCTION quantes() RETURNS INTEGER AS '
DECLARE
    N INTEGER;
BEGIN
    SELECT COUNT(*) INTO N FROM POBLACIONS;
    RETURN N;
END;
' LANGUAGE plpgsql;

SELECT quantes();

Però ara ho fa per l'eixida normal
 

CURSORS EXPLÍCITS

Aquestos cursors s'han de declarar, bé en la zona de declaracions, bé en el moment d'obrir-los, com veurem més avant. I en la zona d’instruccions, primer haurem d’obrir (OPEN) el cursor (que és quan es farà la consulta SQL, guardant-se en una zona de memòria i quedant-se situat el cursor en la primera fila). Després ens anirem desplaçant fila a fila per fer un determinat tractament (FETCH: torna el valor i se situa a la següent fila). Per últim l’haurem de tancar (CLOSE).

En la declaració del cursor seguirem la següent sintaxi:

Encara que també tenim la possibilitat de definir-lo únicament com a refcursor (referència a cursor), sense dir quina és la sentència select. Aleshores l'haurem de definir en el moment d'obrir-lo. També tenim la possibilitat de passar-li un paràmetre. Ací tenim alguns exemples de definició de cursors:

La manera d'obrir-lo depèn de la manera com s'havia declarat. Si només s'havia posat refcursor s'haurà de col·locar la consulta. Si tenia paràmetres s'hauran de posar entre parèntesi. Aquesta seria una manera d'obrir els 3 cursors definits anteriorment:

Hi ha una altra manera d'obrir els cursors definits com a refcursor, que permet especificar la consulta totalment en temps d'execució. Ací en tenim un exemple:

on la taula consultada ve donada com a paràmetre de la funció.

Per accedir als distints camps de les successives files del cursor ho farem per mig de la sentència FETCH cur INTO var1 [, var2, ...] i aleshores tindrem els valors disponibles en les variables var1 (, var2, ...) que estaran prèviament declarades del mateix tipus. Així en l'exemple de dalt, tenim que el cursor cur2 està definit en base a una sentència SQL que torna dues columnes (nom i altura). La sentència hauria de ser

on v_nom i v_altura serien respectivament de tipus varchar i numeric

També podríem haver definir una variable de tipus RECORD. En aquest exemple suposem que la variable f és de tipus RECORD

Després, per a accedir a un camp, posarem f.nom_camp.

Per a tancar el cursor utilitzarem la sentència CLOSE.

L'habitual serà recórrer tot el cursor. El següent exemple trau tots els noms de les poblacions d'una comarca introduïda com a paràmetre de la funció, utilitzant un cursor:

CREATE FUNCTION pobl_com(text) RETURNS void AS $cos$
DECLARE
    v_nom VARCHAR;
    cur CURSOR FOR SELECT nom FROM POBLACIONS
                  		WHERE nom_c = $1
                    	ORDER BY nom;
BEGIN
    OPEN cur;
    FETCH cur INTO v_nom;
    WHILE v_nom IS NOT NULL LOOP
        RAISE NOTICE '%',v_nom;
        FETCH cur INTO v_nom;
    END LOOP;
    CLOSE cur;
END; $cos$ 
LANGUAGE plpgsql;

La manera d'utilitzar aquesta funció serà:

SELECT pobl_com('Plana Alta');

Per a aquestes ocasions en les quals hem de recórrer totalment un cursor, tenim una variant del FOR que ens serà molt útil.

on var_fila és una variable de tipus RECORD, i que anirà agafant els valors de les diferents files (fixeu-vos que s'ha de declarar, en contra de les variables comptador d'un bucle FOR normal). Hem d'observar que en el cas del bucle FOR hem de declarar la sentència SELECT en el mateix moment de crear el FOR, és a dir, no podem utilitzar un cursor definit en la zona de declaracions.

Quan utilitzem el bucle FOR per a recórrer un cursor no caldrà fer l'acció d'obrir, ni de situar-nos al següent, ni de tancar, ja que es fan de forma implícita en el bucle. Reconstruïm l'exemple anterior:

CREATE FUNCTION pobl_com_2(text) RETURNS void AS $cos$
DECLARE
    f RECORD;
BEGIN
    FOR f IN SELECT nom FROM POBLACIONS
                WHERE nom_c = $1
                ORDER BY nom
    LOOP
        RAISE NOTICE '%',f.nom;
    END LOOP;
END;
$cos$ LANGUAGE plpgsql;

Si volguérem construir la sentència SELECT en temps d'execució, posaríem EXECUTE, encara que açò potser siga massa avançat per a aquest curs:

Anem a veure un exemple. Volem calcular la MEDIANA de l'altura de les poblacions. La mediana es defineix com el valor que està al mig de tots si el número d’elements és imparell (1, 2, 3, 4, 10), i com la mitjana entre els dos valors centrals si el número és parell (1, 2, 3, 4, 10, 20) -> 3’5 , tenint en compte sempre que han d'estar ordenats. És suficient amb que entengueu el seu funcionament.

CREATE OR REPLACE FUNCTION MEDIANA() RETURNS NUMERIC AS $cos$
DECLARE
    cur CURSOR FOR SELECT altura FROM POBLACIONS
                    ORDER BY altura;
    aux INT2;
    aux1 INT2;
    i INT2;
    n INT2;
    n1 INT2;
BEGIN
    OPEN cur;
    SELECT COUNT(altura) INTO n FROM POBLACIONS;
    IF MOD(n,2) = 0
        THEN n1 := n;
        ELSE n1 := n+1;
    END IF;
    FOR i IN 1..n1/2 LOOP
        FETCH cur INTO aux;
    END LOOP;
    IF MOD(n,2) = 0 THEN
        FETCH cur INTO aux1;
        aux := (aux + aux1) / 2;
    END IF;
    RETURN aux;
END;
$cos$ LANGUAGE plpgsql;

SELECT MEDIANA();