DAI-C8-EC: PHP i Oracle

De wikijoan
Salta a la navegació Salta a la cerca

Objectius

Unitat Didàctica: UD 8. PHP i Accés a Dades

En una pràctica anterior has treballat sobre els procediments inserir_servei, inserir_detall_servei, calcular_servei, mostra_servei, resum_cambrer, informe_mensual. Es tracta de fer una aplicació mínima PHP que implementi les funcions bàsiques d'un restaurant: el cambrer pren nota d'una taula, es calcula cada taula quan ha de pagar, i es dóna un informe de què han de cobrar els cambrers.

Desenvolupament

Pots veure un exemple del funcionament de la pràctica a

http://192.168.0.10/ec/restaurant/oracle/

Aquesta és una pràctica acadèmica, on no s'ha tingut en compte el disseny ni la usabilitat, per tal de centrar-nos en el que ens interessa, que és l'execució de procediments emmagatzemats des de PHP. D'aquesta manera, aconseguim ficar les regles del negoci dins la base de dades (les línies de codi PHP són menys, augmenta el codi pl/sql).

  • agafar_taula.php crida a agafar_taula2.php, que executa el procediment inserir_servei. Els codis dels procediments se't proporcionen més avall.
  • prendre_nota.php crida a prendre_nota2.php, que executa el procediment inserir_detall_servei.
  • calcular_servei.php crida a calcular_servei2.php, que executa el procediment calcular_servei.
  • mostra_servei.php crida a mostra_servei2.php, que executa el procediment mostra_servei.
  • resum_cambrer.php crida a resum_cambrer2.php, que executa el procediment resum_cambrer.
  • informe_mensual.php crida a informe_mensual2.php, que executa el procediment informe_mensual.

La particularitat dels tres últims informes és que són procediments que elaboren un informe on la sortida són línies DBMS_OUTPUT.PUT_LINE. El que s'explica més avall és la manera com aquest buffer de sortida es pot visualitzar des de PHP.

Si és necessari, el professor facilitarà el codi per tal de què els alumnes puguin avançar. Al final de la pràctica es donarà la solució.

Els alumnes poden treballar en local o en remot.

Entrega

  • Els alumnes poden treballar en local o en remot. A mida que l'alumne obté resultats en les diferents seccions ho ensenyarà al professor.

Recursos

script.sql

REM projecte restaurant
REM IES Jaume Balmes

REM les taules amb què treballaràs són:
REM TAULA(id_taula,taula,zona)
REM CAMBRER(id_cambrer,nom)
REM CARTA(id_plat,plat,tipus,preu)
REM SERVEI(id_servei,id_cambrer,id_taula,dia_hora,preu)
REM 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 NUMBER(2) PRIMARY KEY,
taula CHAR(3),
zona CHAR(1) CHECK (zona IN ('A','B','C','D'))
);

CREATE TABLE CAMBRER(
id_cambrer NUMBER(2) PRIMARY KEY,
nom VARCHAR2(10)
);

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

CREATE TABLE SERVEI(
id_servei NUMBER(4) PRIMARY KEY,
id_cambrer NUMBER(2) REFERENCES CAMBRER,
id_taula NUMBER(2) REFERENCES TAULA,
dia_hora DATE,
preu NUMBER(6,2)
);

CREATE SEQUENCE seq_servei START WITH 1;

CREATE TABLE DETALL_SERVEI(
id_servei NUMBER(4) REFERENCES SERVEI,
id_plat NUMBER(2) REFERENCES CARTA,
quantitat NUMBER(1),
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);

COMMIT;

REM *********** PROCEDIMENTS ****************************

REM 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.
REM 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.
REM 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
REM mostra_servei(id_servei): mostrar tots els plats que s'han de servir a la taula (de l'últim servei que s'ha demanat en aquesta taula)
REM 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.
REM informe_mensual(mes,any): per al mes indicat, fer un informe del que han facturat tots els cambrers, ordenats de major a menor. 


CREATE OR REPLACE PROCEDURE inserir_servei(v_id_cambrer NUMBER, v_id_taula NUMBER, v_id_servei OUT NUMBER)
AS
   vid_cambrer CAMBRER.id_cambrer%TYPE;
   vid_taula TAULA.id_taula%TYPE;
BEGIN
   SELECT id_cambrer into vid_cambrer FROM CAMBRER WHERE id_cambrer=v_id_cambrer;
   SELECT id_taula into vid_taula FROM TAULA WHERE id_taula=v_id_taula;
   INSERT INTO SERVEI VALUES(seq_servei.nextval,v_id_cambrer,v_id_taula,sysdate,NULL);
   SELECT seq_servei.currval INTO v_id_servei FROM DUAL;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('cambrer o taula no existeixen');
END;
/
show errors

CREATE OR REPLACE PROCEDURE inserir_detall_servei(v_id_servei NUMBER, v_id_plat NUMBER, v_quantitat NUMBER)
AS
   vid_servei SERVEI.id_servei%TYPE;
   vid_plat CARTA.id_plat%TYPE;
   vquantitat DETALL_SERVEI.quantitat%TYPE;
   quantitat_malament EXCEPTION;
BEGIN
   IF (v_quantitat<1) THEN 
      RAISE quantitat_malament;
   END IF;
   SELECT id_servei into vid_servei FROM SERVEI WHERE id_servei=v_id_servei;
   SELECT id_plat into vid_plat FROM CARTA WHERE id_plat=v_id_plat;
   INSERT INTO DETALL_SERVEI VALUES(v_id_servei,v_id_plat,v_quantitat);
EXCEPTION
   WHEN quantitat_malament THEN
      DBMS_OUTPUT.PUT_LINE('quantitat ha de ser un valor positiu');
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('servei o plat no existeixen');
END;
/
show errors

CREATE OR REPLACE PROCEDURE calcular_servei(v_id_servei NUMBER,v_total OUT NUMBER)
AS
   vid_servei SERVEI.id_servei%TYPE;
   vtotal NUMBER(6,2);
BEGIN
   SELECT id_servei into vid_servei FROM SERVEI WHERE id_servei=v_id_servei;
   SELECT SUM(preu*quantitat) into vtotal FROM DETALL_SERVEI DS, CARTA C WHERE DS.id_plat=C.id_plat GROUP BY id_servei having id_servei=v_id_servei;
   UPDATE SERVEI SET preu=vtotal WHERE id_servei=v_id_servei;
   DBMS_OUTPUT.PUT_LINE('Total: '|| vtotal);
   v_total:=vtotal;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('no existeix el servei');
END;
/
show errors


CREATE OR REPLACE PROCEDURE mostra_servei(v_id_servei NUMBER)
AS
   vtotal NUMBER(6,2);
   CURSOR c_plats IS SELECT plat,tipus,preu,quantitat FROM DETALL_SERVEI DS, CARTA C WHERE DS.id_plat=C.id_plat and id_servei=v_id_servei;
   vr_plats c_plats%ROWTYPE;
BEGIN
   DBMS_OUTPUT.PUT_LINE('<table border="1"><tr><td>id_cambrer</td><td>nom</td><td>total</td></tr>');
   OPEN c_plats;
   FETCH c_plats INTO vr_plats;
   WHILE (c_plats%FOUND) LOOP
      DBMS_OUTPUT.PUT_LINE('<tr><td>' || vr_plats.plat|| '</td><td>' || vr_plats.preu || '</td><td>' || vr_plats.quantitat || '</td></tr>');
      FETCH c_plats INTO vr_plats;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('</table>');

   SELECT SUM(preu*quantitat) into vtotal FROM DETALL_SERVEI DS, CARTA C WHERE DS.id_plat=C.id_plat GROUP BY id_servei having id_servei=v_id_servei;
   DBMS_OUTPUT.PUT_LINE('<b>Total:</b> '|| vtotal || ' euros');
END;
/
show errors


CREATE OR REPLACE PROCEDURE resum_cambrer(v_id_cambrer NUMBER,dia DATE)
AS
   CURSOR c_cambrer IS SELECT C.id_cambrer,id_servei,nom,preu FROM CAMBRER C,SERVEI S WHERE C.id_cambrer=S.id_cambrer and c.id_cambrer=v_id_cambrer and to_char(dia_hora,'dd/mm/yyyy')=dia;
   vr_cambrer c_cambrer%ROWTYPE;
BEGIN
   DBMS_OUTPUT.PUT_LINE('<table border="1"><tr><td>id_cambrer</td><td>id_servei</td><td>nom</td><td>preu</td></tr>');
   OPEN c_cambrer;
   FETCH c_cambrer INTO vr_cambrer;
   WHILE (c_cambrer%FOUND) LOOP
      DBMS_OUTPUT.PUT_LINE('<tr><td>' || vr_cambrer.id_cambrer || '</td><td>' || vr_cambrer.id_servei || '</td><td>' || vr_cambrer.nom || '</td><td>' || vr_cambrer.preu || '</td></tr>');
      FETCH c_cambrer INTO vr_cambrer;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('</table>');

END;
/
show errors


CREATE OR REPLACE PROCEDURE informe_mensual(v_mes NUMBER,v_any NUMBER)
AS
   CURSOR c_informe IS SELECT C.id_cambrer,nom,SUM(preu) as total FROM CAMBRER C, SERVEI S WHERE C.id_cambrer=S.id_cambrer and to_char(dia_hora,'mm')=v_mes and to_char(dia_hora,'yyyy')=v_any GROUP BY C.id_cambrer,nom;
   vr_informe c_informe%ROWTYPE;
BEGIN
   DBMS_OUTPUT.PUT_LINE('<table border="1"><tr><td>id_cambrer</td><td>nom</td><td>total</td></tr>');
   OPEN c_informe;
   FETCH c_informe INTO vr_informe;
   WHILE (c_informe%FOUND) LOOP

      DBMS_OUTPUT.PUT_LINE('<tr><td>' || vr_informe.id_cambrer || '</td><td>' || vr_informe.nom || '</td><td>' || vr_informe.total || '</td></tr>');
      FETCH c_informe INTO vr_informe;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('</table>');
END;
/
show errors


REM *********** JOC DE PROVES ****************************

declare
vid_servei NUMBER(3);
begin 
inserir_servei(1,1,vid_servei);
dbms_output.put_line(vid_servei);
END;
/

REM substituir el primer argument pel id_servei actual
EXECUTE inserir_detall_servei(1,1,1)
EXECUTE inserir_detall_servei(1,2,2)
EXECUTE inserir_detall_servei(1,4,2)
EXECUTE inserir_detall_servei(1,5,1)
EXECUTE inserir_detall_servei(1,11,3)
EXECUTE inserir_detall_servei(1,15,1)
EXECUTE inserir_detall_servei(1,16,1)
EXECUTE inserir_detall_servei(1,17,1)
EXECUTE inserir_detall_servei(1,19,1)
EXECUTE inserir_detall_servei(1,20,1)

declare
vid_servei NUMBER(3) :=1;
vtotal NUMBER(6,2);
begin 
calcular_servei(vid_servei,vtotal);
dbms_output.put_line(vtotal);
END;
/
EXECUTE mostra_servei(1)

declare
vid_servei NUMBER(3);
begin 
inserir_servei(1,2,vid_servei);
dbms_output.put_line(vid_servei);
END;
/

EXECUTE inserir_detall_servei(2,1)
EXECUTE inserir_detall_servei(3,2)
EXECUTE inserir_detall_servei(7,1)
EXECUTE inserir_detall_servei(8,1)
EXECUTE inserir_detall_servei(11,1)
EXECUTE inserir_detall_servei(12,2)
EXECUTE inserir_detall_servei(15,3)
EXECUTE inserir_detall_servei(19,2)
EXECUTE inserir_detall_servei(20,1)

declare
vid_servei NUMBER(3) :=2;
vtotal NUMBER(6,2);
begin 
calcular_servei(vid_servei,vtotal);
dbms_output.put_line(vtotal);
END;
/
EXECUTE mostra_servei(2)

EXECUTE resum_cambrer(1,'28/10/2009')

EXECUTE mostra_servei(1)

EXECUTE resum_cambrer(2,'28/10/2009')

EXECUTE informe_mensual(10,2009)

Per saber com pots accedir al buffer d'un procediment pl/sql, prova a executar i analitzar el fitxer prova_dbms_output.php:

<?php include( "inc_head.php" ); ?>
<?php include( "open_db.php" ); ?>
<?php

$ramble = array("This is an array", "of text lines", "used to demonstrate", "how to call DBMS_OUTPUT.");
$enable = "begin dbms_output.enable; end;";
$put = "begin dbms_output.put_line(:BUFF); end;";
$get = "begin dbms_output.get_line(:LINE,:STAT); end;";
# First, we enable the use of DBMS_OUTPUT.
$stmt = oci_parse($ora_conn, $enable);
if (!$stmt) {
echo "Could not parse the DBMS_OUTPUT.ENABLE command.\n";
exit;
}

$result = oci_execute($stmt);
if (!$result) {
echo "Could not enable output.\n";
exit;
}

# Now, let's put some lines into the buffer.
$stmt = oci_parse($ora_conn, $put);
if (!$stmt) {
echo "Could not parse PUT query.\n";
exit;
}

foreach($ramble as & $r) {
oci_bind_by_name($stmt, ":BUFF", $r);
$result = oci_execute($stmt);
if (!$result) {
echo "Could not execute the PUT query.\n";
exit;
}
}

# Get'em!
$stmt = oci_parse($ora_conn, $get);
if (!$stmt) {
echo "Could not parse GET query.\n";
exit;
}

oci_bind_by_name($stmt, ":LINE", $line, 255);
oci_bind_by_name($stmt, ":STAT", $stat);
while ($stat == 0) {
$result = oci_execute($stmt);
if (!$result) {
echo "Could not execute the GET query.\n";
exit;
}
if ($stat == 0) {
echo "$line\n";
}
}

?>

<?php include( "close_db.php" ); ?>
<?php include( "inc_peu.php" ); ?>

Codi que se't proporciona:

open_db.php:

<?php
//oracle connection variables
$ora_user = 	'profe';	//username
$ora_pass =	'profe';			//user password
$ora_host =	'localhost';		//host name or server ip address
$ora_db   = 	'BBDD';	//database name

// place variable into oci_connect function, then place funtion in variable
$ora_conn = oci_connect($ora_user,$ora_pass,'//'.$ora_host.'/'.$ora_db);

// error handling
if (!ora_conn){	// if variable $ora_conn fails to connect
	// do the following if it fails
	$ora_conn_erno = oci_error(); 	// insert oci_error() function into variable
	echo ($ora_conn_erno['message']."\n"); 	// print the $ora_conn_erno variable/oci_error() function selecting only the message (human readable)			// close the connection just in case php doesn't close it
}
?>

close_db.php:

<?php
oci_close($ora_conn); 	// close the connection
?>

Durarda

6 hores


creat per Joan Quintana Compte, octubre 2009