Programació de bases de dades
Llicència: CC BY-NC-SA 4.0
(Reconeixement - NoComercial - CompartirIgual) 🅭
Introducció
A més de tots els aspectes estudiants fins al moment, els SGBD moderns normalment proporcionen
una itnerfície de programació que permet als desenvolupadors escriure codi per automatitzar
tasques sobre les bases de dades.
Aquest tipus de llenguatge es coneix com PL/SQL (Procedural Language/Structured Query Language) a les bases de dades Oracle,
però cada SGBD té el seu propi llenguatge de programació amb les seves pròpies particularitats.
Segons el pròposit d'aquest codi, el podem classificar en:
- Procediments (Procedures): Conjunt de sentències SQL, identificades per un nom, que reben paràmetres d'entrada i eixida. No poden ser utilitzades en sentències SQL.
- Funcions: Conjuntes de sentències SQL, identificades per un nom, que reben paràmetres d'entrada i retornen un valor. Poden ser utilitzades en sentències SQL.
- Disparadors (Triggers): Conjunt de sentències SQL que s'executen automàticament quan es produeix un esdeveniment (INSERT, UPDATE, DELETE) sobre una taula.
Característiques
Tots aquests elements tenen en comú les següents característiques:
- Identificació: Tenen un nom que els identifica.
- Compilació: Es compilen i s'emmagatzemen en el SGBD.
- Reutilització: Es poden cridar en diferents moments i executaran el codi que contenen.
- Paràmetres: Poden rebre paràmetres d'entrada i eixida.
BEGIN i END
Tots la programació de bases de dades es basa en sentències compostes (compound statements).
Aquestes sentències es defineixen amb la paraula clau BEGIN i finalitzen amb la paraula clau END.
Perill
MySQL 8.0 no permet l'execució de sentències compostes directament.
Les sentències compostes conformaran el cos d'un procediment, funció o disparador.
Els exemples d'aquest material no es poden executar directament sobre la base de dades.
Documentació
Documentació oficial sobre sentències compostes:
La sintaxi general és la següent:
[begin_label:] BEGIN
[statement_list]
END [end_label]
BEGIN i END són les paraules clau que marquen l'inici i el final de la sentència composta.statement_list és la llista de sentències que formen la sentència composta.begin_label i end_label són opcionals i permeten etiquetar la sentència composta.
DELIMITER
Les sentències compostes consisteixen en un conjunt de sentències SQL que s'executen de manera seqüencial.
Per defecte, totes les sentències SQL finalitzen amb el caràcter punt i coma (;).
El SGBD utilitza el punt i coma per identificar el final de la sentència i executar-la,
però les sentències compostes també utilitzen el punt i coma per identificar el final de cada sentència.
Per evitar conflictes entre els punts i comes de les sentències compostes i les sentències SQL,
es pot canviar el caràcter que s'utilitza per identificar el final de la sentència composta amb la paraula clau DELIMITER.
Documentació
Documentació oficial sobre la paraula clau DELIMITER:
La sintaxi és la següent:
Exemple
Un exemple de sentència composta amb la paraula clau DELIMITER.
DELIMITER // -- Canviem el delimitador a //
BEGIN
SELECT 'Hello, world!'; -- El punt i coma NO finalitza la sentència composta
END; // -- El delimitador // finalitza la sentència composta
DELIMITER ; -- Restablim el delimitador a ;
Variables
Dins d'una sentència composta es poden declarar variables
per emmagatzemar valors temporals sobre els quals es pot operar.
Per declarar una variable es fa servir la paraula clau DECLARE
seguida del nom de la variable i el tipus de dades.
Documentació
Documentació oficial sobre variables:
La sintaxi és la següent:
DECLARE variable_name datatype [DEFAULT value];
DECLARE és la paraula clau que indica que es declara una variable.variable_name és el nom de la variable.datatype és el tipus de dades de la variable.DEFAULT value és el valor per defecte de la variable.
Informació
Les variables han de ser declarades a l'inici de la sentència composta.
Exemple
BEGIN
DECLARE v_num INT DEFAULT 0;
DECLARE v_str VARCHAR(100) DEFAULT 'Hello, world!';
DECLARE v_date DATE DEFAULT CURRENT_DATE;
DECLARE v_bool BOOLEAN DEFAULT TRUE;
END;
El tipus de dades pot ser qualsevol tipus de dades que es pugui emmagatzemar en una variable.
A més, el tipus pot ser extret de la definició d'una columna d'una taula amb
les paraules clau TYPE OF, indicant el nom de la taula i el nom de la columna.
DECLARE variable_name TYPE OF table_name.column_name;
Exemple
BEGIN
DECLARE nom TYPE OF persona.nom;
END;
Per assignar un valor a una variable es fa servir la paraula clau SET.
SET variable_name = value;
Exemple
BEGIN
DECLARE v_num INT DEFAULT 0;
SET v_num = 42;
END;
Si es vol assignar el valor d'una columna d'una taula a una variable es pot fer servir la clàusula INTO en una sentència SELECT.
SELECT
column_name INTO variable_name
...
Exemple
BEGIN
DECLARE v_nom VARCHAR(100);
SELECT nom INTO v_nom FROM persona WHERE dni = '12345678A';
END;
Estrucures de selecció
Dins d'una sentència composta es poden utilitzar les estructures de selecció
per controlar el flux d'execució del codi.
Podem trobar les següents estructures de selecció:
IF, ELSEIF i ELSE: Executa un bloc de codi si una condició és certa.CASE: Executa un bloc de codi segons el valor d'una expressió.
IF, ELSEIF i ELSE
Les clàusules IF, ELSEIF i ELSE permeten executar un bloc de codi si una condició és certa.
Documentació
Documentació oficial sobre l'estructura IF:
La sintaxi és la següent:
IF condition THEN statement_list
[ELSEIF condition THEN statement_list] ...
[ELSE statement_list]
END IF;
IF és la paraula clau que indica que es comença una estructura IF.condition és una expressió que es valora com a certa o falsa.THEN és la paraula clau que indica que comença el bloc de codi que s'executarà si la condició és certa.statement_list és la llista de sentències que formen el bloc de codi.ELSEIF és la paraula clau que indica que es comença una nova condició.- De la mateixa manera que
IF, si la condició és certa s'executarà el bloc de codi especificat.
- Es poden afegir tantes clàusules
ELSEIF com es desitge.
ELSE és la paraula clau que indica que es comença el bloc de codi que s'executarà
si cap de les condicions anteriors és certa.
Exemple
BEGIN
DECLARE v_num INT DEFAULT 0;
-- Generem un número aleatori entre -100 i 100
SELECT RAND() * 200 - 100 INTO v_num;
IF v_num = 0 THEN
SELECT 'Zero';
ELSEIF v_num > 0 THEN
SELECT 'Positiu';
ELSE
SELECT 'Negatiu';
END IF;
END;
CASE
L'estructura CASE permet executar un bloc de codi segons el valor d'una expressió.
Documentació
Documentació oficial sobre l'estructura CASE:
La sintaxi és la següent:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
CASE és la paraula clau que indica que es comença una estructura CASE.case_value és una expressió que es valora per comparar-la amb els valors de when_value, normalment una variable.WHEN when_value THEN statement_list és la paraula clau que indica que comença el bloc de codi que s'executarà si case_value és igual a when_value.ELSE statement_list és la paraula clau que indica que comença el bloc de codi que s'executarà si cap dels valors de when_value és igual a case_value.
Exemple
BEGIN
DECLARE v_num INT DEFAULT 0;
-- Generem un número aleatori entre 1 i 7
SELECT FLOOR(RAND() * 7) + 1 INTO v_num;
CASE v_num
WHEN 1 THEN SELECT 'Dilluns';
WHEN 2 THEN SELECT 'Dimarts';
WHEN 3 THEN SELECT 'Dimecres';
WHEN 4 THEN SELECT 'Dijous';
WHEN 5 THEN SELECT 'Divendres';
WHEN 6 THEN SELECT 'Dissabte';
WHEN 7 THEN SELECT 'Diumenge';
ELSE SELECT 'Error';
END CASE;
END;
Estructures de repetició
Dins d'una sentència composta es poden utilitzar les estructures de repetició
per repetir un bloc de codi múltiples vegades.
Podem trobar les següents estructures de repetició:
LOOP: Executa un bloc de codi de manera indefinida.WHILE: Executa un bloc de codi mentre una condició siga certa.REPEAT: Executa un bloc de codi fins que una condició siga certa.FOR: Executa un bloc de codi un nombre determinat de vegades.
LOOP
L'estructura LOOP executa un bloc de codi de manera indefinida.
Documentació
Documentació oficial sobre l'estructura LOOP:
La sintaxi és la següent:
LOOP
statement_list
END LOOP;
Aquesta estructura es repeteix indefinidament.
- Per acabar aquesta estructura es fa servir la paraula clau
LEAVE.
- Per saltar a la següent iteració es fa servir la paraula clau
ITERATE (equivalent a CONTINUE en altres llenguatges).
Exemple
Exemple d'una estructura LOOP que compta de 1 a 10.
DELIMITER //
BEGIN
DECLARE v_num INT DEFAULT 1;
LOOP
SELECT v_num;
SET v_num = v_num + 1;
IF v_num > 10 THEN
LEAVE;
END IF;
END LOOP;
END; //
DELIMITER ;
WHILE
L'estructura WHILE executa un bloc de codi mentre una condició siga certa.
Documentació
Documentació oficial sobre l'estructura WHILE:
La sintaxi és la següent:
WHILE condition DO
statement_list
END WHILE;
Exemple
Exemple d'una estructura WHILE que compta de 1 a 10.
BEGIN
DECLARE v_num INT DEFAULT 1;
WHILE v_num <= 10 DO
SELECT v_num;
SET v_num = v_num + 1;
END WHILE;
END;
REPEAT
L'estructura REPEAT executa un bloc de codi fins que una condició siga certa.
Documentació
Documentació oficial sobre l'estructura REPEAT:
La sintaxi és la següent:
REPEAT
statement_list
UNTIL condition
END REPEAT;
Exemple
Exemple d'una estructura REPEAT que compta de 1 a 10.
BEGIN
DECLARE v_num INT DEFAULT 1;
REPEAT
SELECT v_num;
SET v_num = v_num + 1;
UNTIL v_num > 10
END REPEAT;
END;
FOR
L'estructura FOR executa un bloc de codi un nombre determinat de vegades.
Advertència
L'estructura FOR no està disponible a MySQL 8.0.
Documentació
Documentació oficial sobre l'estructura FOR:
La sintaxi és la següent:
FOR var_name IN [reverse] lower_bound .. upper_bound DO
statement_list
END FOR;
var_name és el nom de la variable que es farà servir com a comptador.reverse és una paraula clau opcional que indica que el bucle s'executarà en ordre invers.lower_bound és el valor inicial del comptador.upper_bound és el valor final del comptador.
Exemple
Exemple d'una estructura FOR que compta de 1 a 10.
BEGIN
FOR v_num IN 1 .. 10 DO
SELECT v_num;
END FOR;
END;
Bibliografia
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent
a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
