DAM - DAW - MP 0484 Bases de Dades
Exercici proposat – Procediments i Funcions
La proposta és anar creant procediments i funcions per tal que els pugui utilitzar el treballador de l’oficina (userbanc). La proposta de procediments i funcions a realitzar és la següent:
Si voleu tenir ja algunes dades inserides per a fer proves...
Podeu agafar el fitxer adjunt o utilitzar les següents instruccions
DELETE FROM moviment;
DELETE FROM compte;
DELETE FROM oficina;
INSERT INTO `oficina` VALUES
(1,'Una','Avda. Montilivi',1,30.00),
(2,'Dues','Pl. Catalunya',2,450.00),
(3,'Tres','C. Amunt',0,0.00);
INSERT INTO `compte` VALUES
(1,1,1,2,30.00),
(2,1,3,3,250.00),
(2,2,2,1,200.00);
INSERT INTO `moviment` VALUES
(1,1,1,200.00,CURRENT_DATE() - INTERVAL 12 DAY,'Ingrés'),
(1,1,2,-70.00,CURRENT_DATE() - INTERVAL 7 DAY,'Reintegre'),
(1,1,3,-100.00,CURRENT_DATE() - INTERVAL 1 DAY,'Traspas a 1234 0002 15 0000000001'),
(2,1,1,100.00,CURRENT_DATE() - INTERVAL 13 DAY,'Ingrés'),
(2,1,2,350.00,CURRENT_DATE() - INTERVAL 8 DAY,'Ingrés'),
(2,1,3,-50.00,CURRENT_DATE() - INTERVAL 6 DAY,'Reintegre'),
(2,1,4,-125.00,CURRENT_DATE() - INTERVAL 5 DAY,'Reintegre'),
(2,1,5,100.00,CURRENT_DATE() - INTERVAL 1 DAY,'Traspas de 1234 0001 75 0000000001'),
(2,1,6,-125.00,CURRENT_DATE(),'Traspas a 1234 0002 11 0000000002'),
(2,2,1,90.00,CURRENT_DATE() - INTERVAL 8 DAY,'Ingrés'),
(2,2,2,-15.00,CURRENT_DATE() - INTERVAL 1 DAY,'Reintegre'),
(2,2,3,125.00,CURRENT_DATE(),'Traspas de 1234 0002 15 0000000001');
LlistaOficines - Procediment
-
Mostra el codi, nom i adreça de les oficines ordenat segons el paràmetre: 1:
Codi, Altre:NomSELECT
Exemple d'ús i execucióCALL LlistaOficines(0); +------+-------+-----------------+ | Codi | Nom ^ | Adreça | +------+-------+-----------------+ | 2 | Dues | Pl. Catalunya | | 3 | Tres | C. Amunt | | 1 | Una | Avda. Montilivi | +------+-------+-----------------+ CALL LlistaOficines(1); +--------+------+-----------------+ | Codi ^ | Nom | Adreça | +--------+------+-----------------+ | 1 | Una | Avda. Montilivi | | 2 | Dues | Pl. Catalunya | | 3 | Tres | C. Amunt | +--------+------+-----------------+
MostraOficina - Procediment
-
Mostra el codi, nom i adreça de la oficina que passem com a paràmetre.
SELECT
Exemple d'ús i execucióCALL MostraOficina(2); +------+------+---------------+ | Codi | Nom | Adreça | +------+------+---------------+ | 2 | Dues | Pl. Catalunya | +------+------+---------------+
DigitControlBO - Funció
-
Donat el número de banc i el d’oficina, retorna el dígit de control. El del nostre Banc ens imaginarem que és l'1234.
Càlcul dígits de Control (La Sexta)
Crida a una altra funció.
Exemple d'ús i execucióSELECT DigitControlBO(1234, 1111); -- Ha de donar 1 SELECT DigitControlBO(1234, 2222); -- Ha de donar 7
DigitControlCte - Funció
-
Donat el número de compte, retorna el dígit de control del Cte.
Càlcul dígits de Control (La Sexta)
Utilitzeu com a tipus de paràmetre
BIGINTDETERMINISTIC - SET - IF / ELSEIF - CASE - FOR - TRUNCATE - MOD
Exemple d'ús i execucióSELECT DigitControlCte(0123456789); -- Ha de donar 1 SELECT DigitControlCte(1234567890); -- Ha de donar 6
Cte2Char - Funció
-
Donat el codi de banc, oficina i número de compte, retorna un string formatant el compte de la següent forma:
BBBB OOOO DD CCCCCCCCCCCàlcul dígits de Control (La Sexta)
SET - CONCAT - CONVERT - LPAD - Crida a Funcions
Exemple d'ús i execucióSELECT Cte2Char(1234,1111,0123456789); -- Retorna '1234 1111 11 0123456789' SELECT Cte2Char(1234,2222,1234567890); -- Retorna '1234 2222 76 1234567890'
DarrersMoviments - Procediment
-
Donat un número d’oficina, un número de compte i una data ens mostra els moviments des d’aquella data en ordre de més nou a més antic. Cal mostar al final de cada línia el saldo després d'aquell moviment.
SELECT - CURSOR ( FOR ) - SIGNAL
Exemple d'ús i execucióCALL darrersMoviments(2,1, CURRENT_DATE - INTERVAL 1 WEEK); Mov Data Descripció Import Saldo 004 2023-02-21 Reintegre -125.00 275.00 003 2023-02-20 Reintegre -50.00 400.00 CALL darrersMoviments(2,1, CURRENT_DATE - INTERVAL 1 MONTH); Mov Data Descripció Import Saldo 004 2023-02-21 Reintegre -125.00 275.00 003 2023-02-20 Reintegre -50.00 400.00 002 2023-02-14 Ingrés 350.00 450.00 001 2023-02-09 Ingrés 100.00 100.00 CALL darrersMoviments(2,2, CURRENT_DATE + INTERVAL 1 DAY); Mov Data Descripció Import Saldo No hi ha moviments CALL darrersMoviments(1,2, CURRENT_DATE - INTERVAL 1 DAY); ERROR 1643 (02000): ERROR: Compte Inexistent
Cte2IBAN - Funció opcional
Problemes en el càlcul al treballar amb nombres amb tants dígits
-
Donat el codi de banc, oficina i número de compte, retorna un string amb el codi IBAN del compte de la següent forma:
ESxx BBBB OOOO DD CCCCCCCCCC -
Suposarem que TOTS els comptes són de l’estat espanyol (ES).
Càlcul dígits de Control (La Sexta)
El compte no es pot tractar tot sencer com un número ja que no podem representar números amb tants dígits
BBBBOOOODDCCCCCCCCCC142800.Cal anar dividint, ens quedem el residu, de mica en mica, com si ho féssim a mà en paper, agafant els dígits des de l'esquerra de mica en mica fins agafar-los tots.
Exemple d'ús i execucióSELECT Cte2IBAN(1234,1111,0123456789); -- Ha de donar 53 ES53 1234 1111 11 0123456789 SELECT Cte2IBAN(1234,2222,1234567890); -- Ha de donar 90 ES90 1234 2222 76 1234567890
ComprovaOficines - Procediment
-
Mostra les oficines que no tenen el saldo correcte. Es comprova, oficina a oficina, que la suma dels imports dels seus comptes sigui el saldo de la oficina. Si no ho son, mostra les dades de la Oficina.
Exemple d'execucióCALL ComprovaOficines(); START TRANSACTION; UPDATE oficina SET saldoOficina = saldoOficina - 10; CALL ComprovaOficines(); 1 Una 20.00 30.00 2 Dues 440.00 450.00 3 Tres -10.00 NULL ROLLBACK; CALL ComprovaOficines();
ComprovaMoviments - Procediment
-
Mostra els comptes que no tenen el saldo correcte. Es comprova, compte a compte, que la suma dels imports dels seus moviments sigui el saldo del compte. Si no ho son, mostra el número de compte.
Exemple d'execucióCALL ComprovaMoviments(); START TRANSACTION; UPDATE compte SET saldoCompte = saldoCompte - 10; CALL ComprovaMoviments(); 1 1 20.00 30.00 2 1 240.00 250.00 2 2 190.00 200.00 ROLLBACK; CALL ComprovaMoviments();
ComprovaSaldosNegatius - Procediment
-
Mostra els comptes que en algun moment han tingut el saldo en negatiu. Per comprovar-ho, cal anar, per cada compte, moviment a moviment calculant el saldo que tenia a cada moment. Si trobem un saldo negatiu, cal mostrar el compte.
Exemple d'execucióCALL ComprovaSaldosNegatius(); START TRANSACTION; UPDATE moviment SET import = import * 2 WHERE import < 0; CALL ComprovaSaldosNegatius(); 1 1 3 -200.00 -140.00 2 1 6 -250.00 -50.00 ROLLBACK; CALL ComprovaSaldosNegatius();