DAI-C8-EC: Programació PL/pgSQL

De wikijoan
Salta a la navegació Salta a la cerca

Objectius

Unitat Didàctica: UD 7. Programació PL/pgSQL

Assentament dels conceptes i sintaxi de PL/pgSQL.

Desenvolupament

Farem un projecte sobre la base de dades d'un restaurant.

En principi treballaràs en local, tot i que també hi hauria la possibilitat de treballar contra el servidor PostgreSQL de l'institut (192.168.0.10).

El primer que has de fer és crear la base de dades restaurant, i crear un usuari per a aquesta base de dades que no sigui l'usuari administratiu: 

$ psql -h localhost -p 5432 -U postgres postgres
postgres# CREATE DATABASE restaurant ENCODING 'UTF-8';
postgres=# CREATE USER profe WITH ENCRYPTED PASSWORD 'profe';
postgres=# grant all privileges on database restaurant to profe;
postgres=# \q

$ psql -h localhost -p 5432 -U profe restaurant
restaurant#

Encara ens falta una cosa, crear el llenguatge plpgsql per a aquesta base de dades. Això ho ha de fer el propietari de la base de dades restaurant, que és l'usuari postgres: 

$ psql -h localhost -p 5432 -U postgres restaurant
restaurant=# CREATE language plpgsql;
CREATE LANGUAGE
restaurant=# \q

Se't proporciona el script per crear l'estructura de la base de dades: 

-- projecte restaurant
-- IES Jaume Balmes

-- les taules amb què treballaràs són:
-- TAULA(id_taula,taula,zona)
-- CAMBRER(id_cambrer,nom)
-- CARTA(id_plat,plat,tipus,preu)
-- SERVEI(id_servei,id_cambrer,id_taula,dia_hora,preu)
-- DETALL_SERVEI(id_servei,id_plat,quantitat)

DROP SEQUENCE seq_servei;

DROP TABLE DETALL_SERVEI;
DROP TABLE SERVEI;
DROP TABLE CARTA;
DROP TABLE CAMBRER;
DROP TABLE TAULA;

CREATE TABLE TAULA(
id_taula INTEGER PRIMARY KEY,
taula CHAR(3),
zona CHAR(1) CHECK (zona IN ('A','B','C','D'))
);

CREATE TABLE CAMBRER(
id_cambrer INTEGER PRIMARY KEY,
nom VARCHAR(10)
);

CREATE TABLE CARTA(
id_plat INTEGER PRIMARY KEY,
plat VARCHAR(20),
tipus CHAR(1) CHECK (tipus IN ('E','P','S','D','B')),
preu NUMERIC(4,2)
);

CREATE TABLE SERVEI(
id_servei INTEGER PRIMARY KEY,
id_cambrer INTEGER REFERENCES CAMBRER,
id_taula INTEGER REFERENCES TAULA,
dia_hora TIMESTAMP,
preu NUMERIC(4,2)
);

CREATE SEQUENCE seq_servei START 1;

CREATE TABLE DETALL_SERVEI(
id_servei INTEGER REFERENCES SERVEI,
id_plat INTEGER REFERENCES CARTA,
quantitat INTEGER,
PRIMARY KEY(id_servei,id_plat)
);

INSERT INTO TAULA(id_taula,taula,zona) VALUES(1,'T1','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(2,'T2','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(3,'T3','A');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(4,'T4','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(5,'T5','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(6,'T6','B');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(7,'T7','C');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(8,'T8','C');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(9,'T9','D');
INSERT INTO TAULA(id_taula,taula,zona) VALUES(10,'T10','D');

INSERT INTO CAMBRER(id_cambrer,nom) VALUES(1,'Pere');
INSERT INTO CAMBRER(id_cambrer,nom) VALUES(2,'Maria');
INSERT INTO CAMBRER(id_cambrer,nom) VALUES(3,'Tomàs');

INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(1,'Amanida catalana','E',4.25);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(2,'Amanida Russa','E',5.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(3,'Meló amb pernil','E',6);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(4,'Paella','P',7);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(5,'Crema de carbassó','P',3.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(6,'Sopa de peix','P',6.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(7,'Sopa aranesa','P',8);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(8,'Torrades escalivada','P',5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(9,'Verduretes de l''hort','P',5.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(10,'Pollastre farcit','S',8.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(11,'Peus de porc','S',7);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(12,'Bistec tàrtar','S',8);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(13,'Vedella amb bolets','S',8.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(14,'Lluç a la Biscaïna','S',10);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(15,'Mousse de xocolata','D',3);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(16,'Plat de músic','D',2);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(17,'Mel i metó','D',2.5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(18,'Fruita del temps','D',1);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(19,'Vi Negre','B',5);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(20,'Vi Blanc','B',6);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(21,'Aigua mineral','B',2);
INSERT INTO CARTA(id_plat,plat,tipus,preu) VALUES(22,'Cervesa','B',2.5);

Per executar aquest script, crees el fitxer restaurant.sql i executes: 

restaurant# \i restaurant.sql
o bé
restaurant# \i restaurant.sql \g arxiu_XX.log

També es pot executar des del prompt del sistema: 

$ psql -h localhost -U postgres restaurant < script.sql > fitxer.log

veiem els ERROR i els NOTICE en la pantalla, i en el fitxer.log hi ha les sentències correctes 

$ psql -h localhost -U postgres restaurant < script.sql > fitxer.log 2>&1

no veiem res a la pantalla: tota la sortida s'envia a fitxer.log, tant sentències correctes com incorrectes

Un cop tens creada l'estructura i has estudiat els lligams que hi ha entre les taules, ja pots crear els següents procediments que se't demana:

Crear els següents procediments:

  • inserir_servei(id_cambrer,id_taula): ha d'inserir un registre en la taula servei, amb la data del sistema. El preu es deixa NULL (es calcularà a posteriori). El valor id_servei s'agafa a partir de la seqüència corresponent. És el procediment que s'executa quan el cambrer comença a prendre nota a una taula.
  • inserir_detall_servei(id_servei,id_plat,quantitat): insereix un registre en la taula DETALL_SERVEI. És el procediment que s'executa quan el cambrer pren nota de què menjaran els comensals.
  • calcular_servei(id_servei): s'executa quan hem de generar el ticket, per saber quan ha de pagar la taula pel servei. S'ha de fer un UPDATE de SERVEI.preu
  • mostra_servei(id_servei): mostrar tots els plats que s'han de servir en un servei. Mostra la taula i el cambrer.
  • resum_cambrer(id_cambrer,dia): mostrar un informe de tots els serveis que ha fet un cambrer en un dia. Informar de què ha facturat a cada servei, i el total del dia.
  • informe_mensual(mes): per al mes indicat, fer un informe del que han facturat tots els cambrers, ordenats de major a menor.

Per a cadascun dels procediments, hauràs de fer un joc de proves. Com sempre, els jocs de proves han de ser significatius i han de contemplar les diferents situacions que es poden donar. Integra tot aquest codi dins del script restaurant.sql, i sigues generós amb els comentaris.

Aquests procediments seran la base per crear una aplicació PHP que treballarà contra Postgres, Oracle i MySQL,

Entrega

  • entregar els fitxers DAI2AXX_restaurant_NA13.sql i DAI2AXX_NA13.log. Si has treballat per parelles: DAI2AXXXX_script_NA12.sql i DAI2AXXXX_NA12.log, indicant a dins clarament el nom i el número de classe. Exercici individual o per parelles.
  • entregar al Moodle: http://192.168.0.15/moodle

Recursos

Durarda

4 hores


creat per Joan Quintana Compte, octubre 2009

Obtingut de «http://wiki.joanillo.org/index.php?title=DAI-C8-EC:_Programació_PL/pgSQL&oldid=3828»