<div class="page">
<div class="cover text-center">
<img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo">
# Programació de bases de dades
<div class="text-end fit-content ms-auto my-3 mt-auto pt-3">
<p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p>
<p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p>
<p><strong>Curs:</strong> 2023/2024</p>
</div>
<div>
<p class="fw-bold mb-0">Llicència: BY-NC-SA</p>
<p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p>
<a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank">
<img class="mx-auto" src="/itb/images/license.png" alt="Licence"/>
</a>
</div><!--license-->
</div><!--cover-->
</div><!--page-->
{:toc}
## 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`.
::: danger
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.__
:::
::: docs
Documentació oficial sobre sentències compostes:
- MariaDB: https://mariadb.com/kb/en/programmatic-compound-statements/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html
:::
La sintaxi general és la següent:
```sql
[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`.
::: docs
Documentació oficial sobre la paraula clau `DELIMITER`:
- MariaDB: https://mariadb.com/kb/en/delimiters/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html
:::
La sintaxi és la següent:
```sql
DELIMITER new_delimiter
```
::: example
Un exemple de sentència composta amb la paraula clau `DELIMITER`.
```sql
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.
::: docs
Documentació oficial sobre variables:
- MariaDB: https://mariadb.com/kb/en/declare-variable/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
:::
La sintaxi és la següent:
```sql
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.
::: info
Les variables han de ser declarades a l'inici de la sentència composta.
:::
::: example
```sql
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.
```sql
DECLARE variable_name TYPE OF table_name.column_name;
```
::: example
```sql
BEGIN
DECLARE nom TYPE OF persona.nom;
END;
```
:::
Per assignar un valor a una variable es fa servir la paraula clau `SET`.
```sql
SET variable_name = value;
```
::: example
```sql
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`.
```sql
SELECT
column_name INTO variable_name
...
```
::: example
```sql
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.
::: docs
Documentació oficial sobre l'estructura `IF`:
- MariaDB: https://mariadb.com/kb/en/if/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/if.html
:::
La sintaxi és la següent:
```sql
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.
::: example
```sql
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ó.
::: docs
Documentació oficial sobre l'estructura `CASE`:
- MariaDB: https://mariadb.com/kb/en/case-statement/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/case.html
:::
La sintaxi és la següent:
```sql
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`.
::: example
```sql
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.
::: docs
Documentació oficial sobre l'estructura `LOOP`:
- MariaDB: https://mariadb.com/kb/en/loop/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/loop.html
:::
La sintaxi és la següent:
```sql
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).
::: example
Exemple d'una estructura `LOOP` que compta de 1 a 10.
```sql
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.
::: docs
Documentació oficial sobre l'estructura `WHILE`:
- MariaDB: https://mariadb.com/kb/en/while/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/while.html
:::
La sintaxi és la següent:
```sql
WHILE condition DO
statement_list
END WHILE;
```
::: example
Exemple d'una estructura `WHILE` que compta de 1 a 10.
```sql
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.
::: docs
Documentació oficial sobre l'estructura `REPEAT`:
- MariaDB: https://mariadb.com/kb/en/repeat-loop/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/repeat.html
:::
La sintaxi és la següent:
```sql
REPEAT
statement_list
UNTIL condition
END REPEAT;
```
::: example
Exemple d'una estructura `REPEAT` que compta de 1 a 10.
```sql
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.
::: warning
L'estructura `FOR` no està disponible a MySQL 8.0.
:::
::: docs
Documentació oficial sobre l'estructura `FOR`:
- MariaDB: https://mariadb.com/kb/en/for/
:::
La sintaxi és la següent:
```sql
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.
::: example
Exemple d'una estructura `FOR` que compta de 1 a 10.
```sql
BEGIN
FOR v_num IN 1 .. 10 DO
SELECT v_num;
END FOR;
END;
```
:::
## Bibliografia
- https://mariadb.com/kb/en/programming-customizing-mariadb/
- https://mariadb.com/kb/en/programmatic-compound-statements/
- https://dev.mysql.com/doc/refman/8.3/en/sql-compound-statements.html
- https://dev.mysql.com/doc/refman/8.3/en/create-procedure.html
- https://dev.mysql.com/doc/refman/8.3/en/create-function-loadable.html
- https://dev.mysql.com/doc/refman/8.3/en/create-trigger.html
- https://ca.wikipedia.org/wiki/PL/SQL
Aquest lloc web utilitza galetes per millorar l'experiència de l'usuari