DAI-C8-EC: Introducció al Pl/SQL. Exercicis de classe

Contingut

1 Objectius
2 Avantatges d'utilitzar codi pl/sql
3 Desenvolupament
4 Entrega
5 Recursos
6 Durarda

Objectius

Unitat Didàctica: UD 1. Introducció i Fonaments del llenguatge Pl/SQL

Introducció al llenguatge Pl/SQL. Primers exercicis.
Familiaritzar-se amb una sessió remota de SSH contra el servidor.
Treballar amb el sqlplus en consola i de forma remota

Avantatges d'utilitzar codi pl/sql

Els procediments emmagatzemats són particularment útils en certes situacions:

Quan diferents aplicacions client estan escrites en llenguatges diferents o funcionen en diferents plataformes, però necessiten realitzar les mateixes operacions a la base de dades.
Quan la seguretat és primordial. Per exemple, a la banca s'utilitzen procediments emmagatzemats i funcions per a totes les operacions comunes. Això proporciona un entorn consistent i segur, i podem assegurar que totes les operacions estan auditades. Amb aquesta configuració, les aplicacions i els usuaris no tenen accés directe a les taules de la base de dades, i només tenen permisos per executar procediments emmagatzemats.

A més, els procediments emmagatzemats proporcionen millor comportament perquè es guarden com a objectes compilats, i possibiliten minimitzar l'enviament d'informació entre el client i el servidor. La contrapartida és que incrementa la càrrega del servidor de base de dades perquè la major part del treball es fa en el cantó del servidor. Hem de considerat aquest fet si moltes màquines clients (per ex servidors web) són servits per un únic servidor de base de dades.

A més, els procediments emmagatzemats et permeten tenir una llibreria de funcions en el servidor de base de dades. Aquesta és una característica compartida pels llenguatges de programació actuals, que permeten dissenyar llibreries de funcions (per ex classes, paquets).

Desenvolupament

Necessites les taules mestres per poder treballar:

REM ############### SCRIPT CREACIÓ TAULES D'EXEMPLE 333###################

REM ######################################################################
REM                        DEPART I EMPLE 
REM ######################################################################

REM ### ELIMINACIÓ TAULA DEPART ANTIGA (SI EXISTEIX)

DROP TABLE DEPART CASCADE CONSTRAINTS;

REM ### CREACIÓ TABLA DEPART

CREATE TABLE DEPART (
dept_no NUMBER(2) PRIMARY KEY,
dnom VARCHAR2(14),
loc VARCHAR2(14)
);


REM ### ELIMINACIÓ TAULA EMPLE ANTIGA (SI EXISTEIX)

DROP TABLE EMPLE CASCADE CONSTRAINTS; 

REM ### CREACIÓ TAULA EMPLE

CREATE TABLE EMPLE (
emp_no NUMBER(4) PRIMARY KEY,
cognom VARCHAR2(10),
ofici VARCHAR2(10),
dir NUMBER(4),
data_alt DATE,
salari NUMBER(10),
comissio NUMBER(10),
dept_no NUMBER(2) NOT NULL REFERENCES DEPART
);

REM ALTRES OPCIONS
REM clau primària de la taula DEPART
REM: CONSTRAINT PK_DEPT_NO PRIMARY KEY (dept_no)
REM: clau primària de la taula EMPLE i clau forània a la taula DEPART:
REM: CONSTRAINT PK_EMP_NO PRIMARY KEY (emp_no),
REM: CONSTRAINT FK_EMP_DEPART FOREIGN KEY (dept_no) REFERENCES DEPART(dept_no) ON DELETE CASCADE,

REM ## INSERCIÓ DE FILES EN LA TAULA DEPART

INSERT INTO DEPART VALUES (10,'COMPTABILITAT','GRANOLLERS');
INSERT INTO DEPART VALUES (20,'INVESTIGACIÓ','MATARÓ');
INSERT INTO DEPART VALUES (30,'VENDES','BARCELONA');
INSERT INTO DEPART VALUES (40,'PRODUCCIÓ','SABADELL');
COMMIT;


REM ## INSERCIÓ DE FILES EN LA TAULA EMPLE

INSERT INTO EMPLE VALUES (7369,'Sánchez','EMPLEAT',7902,'17/12/2002',1200,NULL,20);
INSERT INTO EMPLE VALUES (7499,'Domènech','VENEDOR',7698,'20/02/1996', 1800,500,30);
INSERT INTO EMPLE VALUES (7521,'Sala','VENEDOR',7698,'22/02/2003', 1850,600,30);
INSERT INTO EMPLE VALUES (7566,'Pomera','DIRECTOR',7839,'02/04/2000', 2400,NULL,20);
INSERT INTO EMPLE VALUES (7654,'Martí','VENEDOR',7698,'29/09/1998', 1800,500,30);
INSERT INTO EMPLE VALUES (7698,'Cirera','DIRECTOR',7839,'01/05/2002', 2500,NULL,30);
INSERT INTO EMPLE VALUES (7782,'Matas','DIRECTOR',7839,'09/06/2001', 2400,NULL,10);
INSERT INTO EMPLE VALUES (7788,'Gili','ANALISTA',7566,'09/11/1997', 2000,NULL,20);
INSERT INTO EMPLE VALUES (7839,'Rovira','PRESIDENT',NULL,'17/11/2004', 2600,NULL,10);
INSERT INTO EMPLE VALUES (7844,'Cladelles','VENEDOR',7698,'08/09/2003', 1600,0,30);
INSERT INTO EMPLE VALUES (7876,'Vives','EMPLEAT',7788,'23/09/2002', 1200,NULL,20);
INSERT INTO EMPLE VALUES (7900,'Viaplana','EMPLEAT',7698,'03/12/1998', 1250,NULL,30);
INSERT INTO EMPLE VALUES (7902,'Rueda','ANALISTA',7566,'03/12/1998',1950,NULL,20);
INSERT INTO EMPLE VALUES (7934,'Perera','EMPLEAT',7782,'23/01/2003', 1300,NULL,10);                      
COMMIT;


REM ############################################################################
REM                       CLIENTS, PRODUCTES I VENDES
REM ############################################################################


REM ### ELIMINACIÓ TAULA CLIENTS ANTIGA (SI EXISTEIX).

DROP TABLE clients CASCADE CONSTRAINTS;

REM ### CREACIÓ TAULA CLIENTS

CREATE TABLE CLIENTS (
nif VARCHAR2(10) NOT NULL,
nom VARCHAR2(15) NOT NULL,
domicili VARCHAR2(15),
CONSTRAINT pk_clientes PRIMARY KEY (nif)
);


REM ### ELIMINACIÓ TAULA PRODUCTES ANTIGA (SI EXISTEIX).

DROP TABLE PRODUCTES CASCADE CONSTRAINTS;

REM ### CREACIÓ TAULA PRODUCTES


CREATE TABLE PRODUCTES (
cod_prod NUMBER(4) NOT NULL,
descripcio VARCHAR2(15) NOT NULL,
linia_prod VARCHAR2(6) NOT NULL,
preu_uni NUMBER(6) NOT NULL,
stock NUMBER(5) NOT NULL,
CONSTRAINT pk_productes PRIMARY KEY (cod_prod),
CONSTRAINT ck_preu CHECK (preu_uni > 0)
);

REM ### ELIMINACIÓ TAULA VENDES ANTIGA (SI EXISTEIX)

DROP TABLE VENDES CASCADE CONSTRAINTS;

REM ### CREACIÓ TAULA VENDES


CREATE TABLE VENDES (
nif VARCHAR2(10) NOT NULL,
cod_prod NUMBER(4) NOT NULL,
dia_hora DATE NOT NULL,
unitats NUMBER(3) DEFAULT 1 NOT NULL,
CONSTRAINT pk_vendes PRIMARY KEY (nif,cod_prod,dia_hora),
CONSTRAINT fk_vendes_client FOREIGN KEY (nif) REFERENCES CLIENTS(nif) ON DELETE CASCADE,
CONSTRAINT fk_vendes_prod FOREIGN KEY (cod_prod) REFERENCES PRODUCTES(cod_prod) ON DELETE CASCADE,
CONSTRAINT ck_unitats CHECK (unitats > 0)
);


REM ### INSERCIÓ DE FILES EN LA TAULA  Productes

INSERT INTO PRODUCTES VALUES (1, 'PROCESSADOR 2.4', 'PROCES', 250, 0);
INSERT INTO PRODUCTES VALUES (2, 'PLACA BASE VX', 'PB', 180, 0);
INSERT INTO PRODUCTES VALUES (3, 'SIMM EDO 256MB', 'MEM', 60, 0);
INSERT INTO PRODUCTES VALUES (4, 'DISC SCSI 40G', 'DISCOS', 200, 0);  
INSERT INTO PRODUCTES VALUES (5, 'PROCESSADOR AMD', 'PROCES', 150, 0);
INSERT INTO PRODUCTES VALUES (6, 'DISC IDE 160G', 'DISCOS', 90, 0);
INSERT INTO PRODUCTES VALUES (7, 'P. CENTRINO', 'PROCES', 220, 0);
INSERT INTO PRODUCTES VALUES (8, 'PLACA BASE ATL', 'PB', 150, 0);
INSERT INTO PRODUCTES VALUES (9, 'DIMM SDRAM 1GB', 'MEM', 65, 0);
COMMIT;


REM ### INSERCIÓ DE FILES EN LA TAULA CLIENTS

INSERT INTO CLIENTS VALUES('111A', 'ANDRES', 'POZUELO' ); 
INSERT INTO CLIENTS VALUES('222B', 'JAUME', 'ARAVACA'); 
INSERT INTO CLIENTS VALUES('333C', 'TERESA', 'LAS ROZAS'); 
INSERT INTO CLIENTS VALUES('444D', 'VICENT', 'MADRID');
INSERT INTO CLIENTS VALUES('555E', 'SANDRA', 'MADRID');
INSERT INTO CLIENTS VALUES('666F', 'ALBERT', 'POZUELO');
INSERT INTO CLIENTS VALUES('777G', 'MIQUEL', 'POZUELO');
INSERT INTO CLIENTS VALUES('888H', 'MARINA','ARAVACA');
INSERT INTO CLIENTS VALUES('999I', 'ANTONI', 'LAS ROZAS');
COMMIT;


REM ### INSERCIÓ DE FILES A LA TAULA VENDES

INSERT INTO VENDES VALUES('333C', 2, '22/09/1997', 2);
INSERT INTO VENDES VALUES('888H', 4, '22/09/1997', 1);
INSERT INTO VENDES VALUES('555E', 6, '23/09/1997', 3);
INSERT INTO VENDES VALUES('222B', 5, '26/09/1997', 5);
INSERT INTO VENDES VALUES('111A', 9, '28/09/1997', 3);
INSERT INTO VENDES VALUES('222B', 4, '28/09/1997', 1);
INSERT INTO VENDES VALUES('444D', 6, '02/10/1997', 2);
INSERT INTO VENDES VALUES('555E', 6, '02/10/1997', 1);
INSERT INTO VENDES VALUES('888H', 2, '04/10/1997', 4);
INSERT INTO VENDES VALUES('333C', 9, '04/10/1997', 4);
INSERT INTO VENDES VALUES('222B', 6, '05/10/1997', 2);
INSERT INTO VENDES VALUES('666F', 7, '07/10/1997', 1);
INSERT INTO VENDES VALUES('555E', 4, '10/10/1997', 3);
INSERT INTO VENDES VALUES('222B', 4, '16/10/1997', 2);
INSERT INTO VENDES VALUES('111A', 3, '18/10/1997', 3);
INSERT INTO VENDES VALUES('222B', 4, '18/10/1997', 5);
INSERT INTO VENDES VALUES('444D', 6, '22/10/1997', 2);
INSERT INTO VENDES VALUES('555E', 6, '02/11/1997', 2);
INSERT INTO VENDES VALUES('888H', 2, '04/11/1997', 3);
INSERT INTO VENDES VALUES('333C', 9, '04/12/1997', 3);
INSERT INTO VENDES VALUES('222B', 2, '05/12/1997', 2);
COMMIT;


REM ########################### FI DEL SCRIPT #########################

A partir d'aquest codi, crea en el teu directori personal el fitxer script.sql, i executa'l.

Una sessió típica seria:

maquina_local$ ssh dai2aXX@192.168.0.10
password: dai

ara crees el fitxer en el teu directori personal, que és /var/www/alumnes/dai2aXX. Necessites un editor de fitxers, com pot ser vi, vim o joe:

ubuntu-bbdd$ joe script.sql

Un cop has editat el fitxer, ja el pots executar des del sqlplus:

ubuntu-bbdd$ sqlplus
login: dai2aXX
password: dai
SQL>
SQL> @/var/www/alumnes/dai2aXX/script.sql
...
SQL> select * from tab;

Ara ja pots començar a treballar amb els exercicis vistos a classe:

Introducció al llenguatge PL/SQL
================================

Blocs PL/SQL

[DECLARE
	<declaracions>]
BEGIN
	<ordres>
[EXCEPTION
	<gestió d'excepcions> ]
END;

DECLARE
	v_num_empleats NUMBER(2);
BEGIN
	INSERT INTO depart VALUES (99,'PROVISIONAL',NULL);
	UPDATE emple SET dept_no=99 WHERE dept_no=20;
	v_num_empleats := SQL%ROWCOUNT;
	DELETE FROM depart WHERE dept_no=20;
	DBMS_OUTPUT.PUT_LINE(v_num_empleats || ' empleats ubicats en PROVISIONAL');
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE_APPLICATION_ERROR(-20000, 'Error aplicació');
END;


1. Definició de dades compatibles amb SQL

DECLARE
	import NUMBER(8,2);
	comptador NUMBER(2) DEFAULT 0;
	nom CHAR(20) NOT NULL := 'MIQUEL';
	nou VARCHAR2(15);
BEGIN
...

2. Estructures de control

IF <condició1> THEN
	<instruccions>;
ELSIF <condició2> THEN
	<instruccions>;
...
ELSE
	<instruccions>

END IF;

WHILE <condició> LOOP
	<instruccions>
	...
END LOOP;

FOR<variable> IN <mínim>..<màxim> LOOP
	<instruccions>;
	...
END LOOP;

3.Ús de cursors

DECLARE
	v_cog VARCHAR(10);
	v_ofici VARCHAR(10);
BEGIN
	SELECT cognom,ofici INTO v_cog, v_ofici
	FROM EMPLE WHERE EMP_NO=7900;
	DBMS_OUTPUT.PUT_LINE(v_cog || '*' || v_ofici);
END;

4. Gestió d'excepcions
DECLARE
	v_cog VARCHAR(10);
	v_ofici VARCHAR(10);
BEGIN
	SELECT cognom,ofici INTO v_cog, v_ofici
	FROM EMPLE WHERE EMP_NO=7900;
	DBMS_OUTPUT.PUT_LINE(v_cog || '*' || v_ofici);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	INSERT INTO temp(col1) VALUES('ERROR no hi ha dades');
	WHEN TOO_MANY_ROWS THEN
	INSERT INTO temp(col1) VALUES('ERROR massa dades');
	WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR(-20000, 'Error aplicació');
END;

5. Us de procediments

CREATE OR REPLACE PROCEDURE veure_client(nomcli VARCHAR2)
AS
	nifcli VARCHAR2(10);
	domicli VARCHAR2(15);
BEGIN
	SELECT nif, domicili INTO nifcli, domicli FROM CLIENTS
	WHERE nom=nomcli;
	DBMS_OUTPUT.PUT_LINE('Nom: ' || nomcli || ' Domicili: ' || domicli);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('no sha trobat el client '||nomcli);
END VEURE_CLIENT;
/

executa els blocs anònims anteriors, i familiaritza't amb la sintaxi. Fés proves, modificacions,...
quan ja tinguis per la mà els exercicis i hagis resolt dubtes, posa tot el codi (el que se t'ha donat i el que has modificat) en un script, que anomenaràs DAI2AXX_script_NA1.sql
Fica tots els comentaris que vulguis amb REM
al principi i al final del script, posa

SPOOL DAI2AXX_NA1.log
SET ECHO ON
...
SPOOL OFF

i així faràs un log de la sortida per pantalla. D'aquesta manera, analitzant el log podràs veure quines instruccions han donat error.

Entrega

entregar els fitxers DAI2AXX_script_NA1.sql i DAI2AXX_NA1.log. Si has treballat per parelles: DAI2AXXXX_script_NA1.sql i DAI2AXXXX_NA1.log, indicant a dins clarament el nom i el número de classe (comentaris amb REM). Exercici individual o per parelles.
Entre d'altres, faràs el següent exercici que mostra l'ús de IF

Crear el procediment emmagatzemat pujar_salari(cognom), que pugi el salari un 10% a l'empleat si aquest pertaby al departament 10 ó 20

CREATE OR REPLACE PROCEDURE pujar_salari(vcognom VARCHAR)
AS

   v_salari EMPLE.salari%TYPE;

   v_dept_no EMPLE.dept_no%TYPE;

BEGIN

	SELECT salari, dept_no

	INTO v_salari, v_dept_no

	FROM EMPLE WHERE cognom=vcognom;

	IF (v_dept_no=10 OR v_dept_no=20) THEN v_salari:=v_salari+v_salari*0.1;
    UPDATE EMPLE SET SALARI=v_salari WHERE cognom=vcognom;

	ELSE DBMS_OUTPUT.PUT_LINE('No esta en el departament 10 o 20');

	END IF;



END;

/

SELECT cognom,salari FROM EMPLE;
Perera	1300

EXECUTE pujar_salari('Perera')

SELECT cognom,salari FROM EMPLE;
Perera	1430

entregar al Moodle: http://192.168.0.15/moodle

Recursos

En el Moodle també pots descarregar el codi per crear les taules, i el codi que s'ha vist a classe.

Durarda

1,5 hores

creat per Joan Quintana Compte, octubre 2009 - setembre 2010

DAI-C8-EC: Introducció al Pl/SQL. Exercicis de classe

Contingut

Objectius

Avantatges d'utilitzar codi pl/sql

Desenvolupament

Entrega

Recursos

Durarda

Menú de navegació

Eines de l'usuari

Espais de noms

Variants

Vistes

Més

Cerca

Navegació

Institut Jaume Balmes

Màquines recreatives

CNC

Informàtica musical

joanillo.org Planet

Eines