Procediments emmagatzemats
Llic猫ncia: CC BY-NC-SA 4.0
(Reconeixement - NoComercial - CompartirIgual) 馃叚
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.
Documentaci贸
Documentaci贸 oficial sobre procediments emmagatzemats:
Creaci贸 de procediments
Els procediments es creen amb la sent猫ncia CREATE PROCEDURE i es defineixen amb la seg眉ent sintaxi:
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).
Dades d'exemple
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);
Exemple
Procediment que realitza una trasnfer猫ncia entre dos comptes bancaris:
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.
Documentaci贸
Documentaci贸 oficial sobre la crida a procediments:
CALL sp_name([parameter[,...]])
sp_name: nom del procediment.parameter: par脿metres d'entrada i eixida del procediment.
Exemple
Crida al procediment transferencia:
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.
Par脿metres d'entrada
L'exemple del procediment transferencia t茅 tres par脿metres d'entrada (id_origen, id_dest铆 i import).
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.
Informaci贸
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).
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:
CALL retirada(1, 100, @saldo_restant);
SELECT @saldo_restant;
+----------------+
| @saldo_restant |
+----------------+
| 900.00 |
+----------------+
馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent
a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
