DAM - DAW - MP 0484 Bases de Dades

Introducció

MariaDB igual que altres SGBD té un llenguatge específic per a programar Funcions, Procediments i Disparadors i emmagatzemar-los a la Base de Dades. Veurem la sintaxi bàsica del llenguatge propi del gestor MariaDB, però tot i que altres SGBD n’utilitzaran un d’específic la diferència entre uns i altres no serà massa gran i ens haurà de ser fàcil adaptar-nos a altres sabent com funciona aquest.

MariaDB, igual que altres gestors de bases de dades sí permet crear blocs anònims, és a dir, comandes entre un BEGIN i un END que podem executar sense incorporar-les en una estructura de la base de dades com ara una funció, procediment o disparador. En MariaDB la comanda BEGIN s’ha de substituir per BEGIN NOT ATOMIC.

D'aquesta manera podrem provar les primeres instruccions dins de blocs de codi que començaran amb la comanda BEGIN NOT ATOMIC.

La sintaxi bàsica d’un petit programa en Llenguatge procedimental serà:

BEGIN NOT ATOMIC
    DECLARE . . .
    . . .
    FOR c1 IN c_enrollments
    LOOP
        IF c1.final_numeric_grade BETWEEN 66 AND 75 THEN
            SET v_new_letter_grade = 'A';
        ELSIF c1.final_numeric_grade BETWEEN 56 AND 65 THEN
            SET v_new_letter_grade = 'B';
        . . .
        ELSE
            SET v_new_letter_grade = 'F';
        END IF;
        UPDATE enrollments
            SET final_letter_grade = v_new_letter_grade
            WHERE class_id=1 AND stu_id=c1.stu_id;
    END LOOP;
END;

Compte, no copieu aquest codi, ja que no està complet i, per tant, dona errors

On trobem per ordre de dalt a baix: un cursor, una estructura d’iteració, un bloc de codi que s’executa segons una condició i una assignació a una variable.

No vulguem saber què fa el codi, sinó, simplement cal veure que l’estructura s’assembla més o menys a qualsevol llenguatge de programació, però amb la incorporació de quelcom nou i propi dels llenguatges de SGBD, els cursors.

Tot programa el podem dividir en tres blocs principals:

• Declaració: per a declarar les diferents variables i cursors que utilitzarem

• Execució: part principal del codi i responsable bàsic de què es farà

• Excepcions: Part de codi que s’executarà davant d'algun problema o error de la part d’execució.

Rutines emmagatzemades

MariaDB admet les rutines emmagatzemades: procediments i funcions. Una rutina emmagatzemada és un conjunt d’instruccions SQL que es poden emmagatzemar al servidor. Un cop fet això, els clients no necessiten continuar reeditant les declaracions individuals sinó que poden referir-se a la rutina emmagatzemada.

Les rutines emmagatzemades poden ser especialment útils en determinades situacions:

• Quan diverses aplicacions de client s’escriuen en diferents llenguatges de programació o treballen en diferents plataformes, però cal efectuar les mateixes operacions de base de dades.

• Quan la seguretat és primordial. Els bancs, per exemple, utilitzen procediments i funcions emmagatzemades per a totes les operacions habituals. Això proporciona un entorn consistent i segur i les rutines poden assegurar que cada operació està registrada correctament. En aquesta configuració, les aplicacions i els usuaris no tenen accés directament a les taules de la base de dades, sinó que només poden executar rutines emmagatzemades específiques.

Les rutines emmagatzemades també ens permeten tenir llibreries de funcions al servidor de bases de dades. Aquesta és una característica compartida per llenguatges d'aplicacions moderns que permeten aquest disseny internament (per exemple, mitjançant classes). L'ús d'aquestes funcions del llenguatge d'aplicacions del client és beneficiós per al programador, fins i tot fora de l'àmbit d'ús de la base de dades.

MariaDB segueix la sintaxi SQL:2003 per a les rutines emmagatzemades, que també utilitza el DB2 d’IBM.

Sintaxi

Una rutina emmagatzemada és un procediment o una funció. Les rutines emmagatzemades es creen amb les sentències CREATE PROCEDURE i CREATE FUNCTION.

Per cridar un procediment (executar-lo) utilitzarem la instrucció CALL i només pot retornar valors mitjançant paràmetres de sortida.

Una funció es pot cridar des de dins d’una instrucció igual que qualsevol altra funció , és a dir, invocant el nom de la funció, i pot retornar un valor escalar.

El cos d'una rutina emmagatzemada pot usar diverses instruccions que veurem posteriorment.

Les rutines emmagatzemades es poden eliminar amb les instruccions DROP PROCEDURE i DROP FUNCTION i es poden modificar amb les instruccions ALTER PROCEDURE i ALTER FUNCTION.

Un procediment o funció emmagatzemada està associat amb una base de dades particular. Això té diverses implicacions:

• Quan s'invoca la rutina, es fa un USE db_name implícit (i es desfarà quan finalitzi la rutina). Dins d’una rutina no podem utilitzar la sentència USE.

• Podeu cridar una rutina afegint el nom de la base de dades quan no es trobi a la base de dades actual. Per exemple, per invocar un procediment emmagatzemat p o funció f associada a la base de dades de test , podeu escriure CALL test.p() o test.f().

• Quan s’elimina una base de dades, també s’eliminaran totes les rutines emmagatzemades associades a aquesta.

Les funcions emmagatzemades no poden ser recursives.

La recursivitat en procediments emmagatzemats està deshabilitada, per habilitar-la cal modificar una variable del servidor, max_sp_recursion_depth, i posar-li un valor major a zero.

Per a poder crear funcions i procediments cal tenir el privilegi CREATE ROUTINE.

Per a poder executar funcions i procediments cal tenir el privilegi EXECUTE.

Diccionari de dades

Per a consultar les rutines al diccionari de dades cal referir-nos a la taula INFORMATION_SCHEMA.ROUTINES.

Utilitza la comanda SHOW CREATE PROCEDURE i SHOW CREATE FUNCTION per veure’n la definició.

Utilitza la comanda SHOW PROCEDURE STATUS i SHOW FUNCTION STATUS per veure les característiques de les rutines.