DAI-C8-EC: Programació PL/pgSQL

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

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:

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

Recursos

Durarda

4 hores



creat per Joan Quintana Compte, octubre 2009

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines