<div class="page">
<div class="cover text-center">
<img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo">
# Procediments emmagatzemats
<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ó
Els __procediments emmagatzemats__ (_stored procedures_) són un conjunt de sentències SQL que es compilen i s'emmagatzemen
en el SGBD per a ser executades posteriorment.
Els procediments s'__identifiquen per un nom__ i poden rebre paràmetres __d'entrada i d'eixida__.
::: docs
Documentació oficial sobre procediments emmagatzemats:
- MariaDB: https://mariadb.com/kb/en/stored-procedures/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.3/en/create-procedure.html
:::
## Creació de procediments
Els procediments es creen amb la sentència `CREATE PROCEDURE` i es defineixen amb la següent sintaxi:
```sql
CREATE
[OR REPLACE]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
routine_body:
Valid SQL procedure statement
```
- `sp_name`: nom del procediment.
- `proc_parameter`: paràmetres d'entrada i eixida del procediment.
- `IN`: paràmetre d'entrada.
- `OUT`: paràmetre d'eixida.
- `INOUT`: paràmetre d'entrada i eixida.
- `param_name`: nom del paràmetre.
- `type`: tipus de dades del paràmetre.
- `characteristic`: característiques del procediment. Consulteu la documentació oficial per a més informació.
- `routine_body`: Cos del procediment. És una __sentència composta__ que comença amb `BEGIN` i acaba amb `END` (Vegeu [Material: Programació de bases de dades](/itb/DAM-BD/UD7/materials/01_plsql.html)).
::: info "Dades d'exemple"
```sql
CREATE DATABASE IF NOT EXISTS banc;
USE banc;
CREATE TABLE IF NOT EXISTS compte_bancari (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(100) NOT NULL,
saldo DECIMAL(10, 2) NOT NULL
);
INSERT INTO compte_bancari (nom, saldo) VALUES
('Pep', 1000),
('Maria', 2000),
('Mar', 3000);
```
:::
::: example
Procediment que realitza una trasnferència entre dos comptes bancaris:
```sql
DELIMITER //
CREATE PROCEDURE transferencia (
IN id_origen INT,
IN id_destí INT,
IN import DECIMAL(10,2)
)
BEGIN
-- Saldo del compte origen
DECLARE saldo_origen DECIMAL(10,2);
-- Inici de la transacció
START TRANSACTION;
SELECT saldo INTO saldo_origen
FROM compte_bancari
WHERE id = id_origen;
-- Comprovació si hi ha saldo suficient
IF saldo_origen < import THEN
ROLLBACK;
-- Llança una excepció per interrompre el procediment
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Saldo insuficient';
END IF;
-- Actualització del compte origen
UPDATE compte_bancari
SET saldo = saldo - import
WHERE id = id_origen;
-- Actualització del compte destí
UPDATE compte_bancari
SET saldo = saldo + import
WHERE id = id_destí;
COMMIT;
END //
DELIMITER ;
```
:::
## Crida a procediments
Els procediments es criden amb la sentència `CALL` seguida del nom del procediment i els paràmetres d'entrada i eixida.
::: docs
Documentació oficial sobre la crida a procediments:
- MariaDB: https://mariadb.com/kb/en/call/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.3/en/call.html
:::
```sql
CALL sp_name([parameter[,...]])
```
- `sp_name`: nom del procediment.
- `parameter`: paràmetres d'entrada i eixida del procediment.
::: example
Crida al procediment `transferencia`:
```sql
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom | saldo |
+----+-------+-------+
| 1 | Pep | 1000 |
| 2 | Maria | 2000 |
| 3 | Mar | 3000 |
+----+-------+-------+
CALL transferencia(1, 2, 100);
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom | saldo |
+----+-------+-------+
| 1 | Pep | 900 |
| 2 | Maria | 2100 |
| 3 | Mar | 3000 |
+----+-------+-------+
CALL transferencia(1, 2, 1000);
ERROR 1644 (45000): Saldo insuficient
```
:::
## Paràmetres
Els procediments poden rebre paràmetres, que poden ser de tres tipus:
- `IN`: __Paràmetre d'entrada.__ S'introdueix un valor al procediment.
El procediment pot modificar el valor, però els canvis no seran visibles fora del procediment.
- `OUT`: __Paràmetre d'eixida.__ El procediment retorna un valor. El valor inicial del paràmetre és `NULL`.
- `INOUT`: __Paràmetre d'entrada i eixida__ (`IN` i `OUT`). S'introdueix un valor al procediment,
que pot ser modificat pel procediment i es retorna com a resultat.
::: example "Paràmetres d'entrada"
L'exemple del procediment `transferencia` té tres paràmetres d'entrada (`id_origen`, `id_destí` i `import`).
:::
:::: example "Paràmetres d'eixida"
Aquest procediment retira diners d'un compte bancari i retorna els diners
restants en el compte. El paràmetre `saldo_restant` és de tipus `OUT`.
::: info
Per a poder cridar el procediment i obtenir el valor del paràmetre d'eixida,
s'ha d'utilitzar una variable d'usuari per emmagatzemar el valor retornat (`@saldo_restant`).
- MariaDB: https://mariadb.com/kb/en/user-defined-variables/
:::
```sql
DELIMITER //
CREATE PROCEDURE retirada (
IN id_compte INT,
IN import DECIMAL(10,2),
OUT saldo_restant DECIMAL(10,2)
)
BEGIN
-- Saldo del compte
DECLARE saldo_compte DECIMAL(10,2);
-- Inici de la transacció
START TRANSACTION;
SELECT saldo INTO saldo_compte
FROM compte_bancari
WHERE id = id_compte;
-- Comprovació si hi ha saldo suficient
IF saldo_compte < import THEN
ROLLBACK;
-- Llança una excepció per interrompre el procediment
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Saldo insuficient';
END IF;
-- Actualització del compte
UPDATE compte_bancari
SET saldo = saldo - import
WHERE id = id_compte;
-- Saldo restant
SELECT saldo INTO saldo_restant
FROM compte_bancari
WHERE id = id_compte;
COMMIT;
END //
DELIMITER ;
```
Crida al procediment `retirada`:
```sql
CALL retirada(1, 100, @saldo_restant);
SELECT @saldo_restant;
+----------------+
| @saldo_restant |
+----------------+
| 900.00 |
+----------------+
```
::::