EJERPLSQL1

EJERCICIOS 1 PL/SQL

2012

Javier García Cambronel SEGUNDO DE ASIR

23/01/2012

EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012

SEGUNDO DE ASIR Página 1

ENTRANDO UN NUESTRA BASE DE DATOS

CREANDO LAS TABLAS

INTRODUCIENDO DATOS

EJERCICIOS

1.- En la tabla emp incrementar el salario el 10% a los empleados que tengan una comisión

superior al 5% del salario.

2.- Añadir la columna total2 y en ella escribir la suma del salario y la comisión de los

empleados con comisión distinta de 0.

3.- Insertar un empleado en la tabla EMP. Su número será superior a los existentes y la

fecha de incorporación a la empresa será la actual.

4.- Realizar un procedimiento para borrar un empleado recibiendo como parámetro el

número de empleado.

5.- Realizar un procedimiento para modificar la localidad de un departamento. El

procedimiento recibe como parámetros la localidad y el número de departamento



ENTRANDO UN NUESTRA BASE DE DATOS

oracle@asir-VirtualBox1:~$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 23 10:10:11 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: asir1 as sysdba

Enter password:

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 339738624 bytes

Fixed Size 1219304 bytes

Variable Size 71304472 bytes

Database Buffers 264241152 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.



CREANDO LAS TABLAS

Creamos la tabla de los departamentos

CÓDIGO TABLA DEPART

CREATE TABLE DEPART (

DEPT_NO NUMBER(2) NOT NULL,

DNOMBRE VARCHAR2(14),

LOC VARCHAR2(14),

CONSTRAINT PK_DEPT_NO PRIMARY KEY (DEPT_NO)

);

Como vemos se crea perfectamente

Creamos la tabla de los empleados

CÓDIGO TABLA EMPLE

CREATE TABLE EMPLE (

EMP_NO NUMBER(4) NOT NULL,

APELLIDO VARCHAR2(10),

OFICIO VARCHAR2(10),

DIR NUMBER(4) ,

FECHA_ALT DATE ,

SALARIO NUMBER(10),

COMISION NUMBER(10),

DEPT_NO NUMBER(2) NOT NULL,

CONSTRAINT PK_EMP_NO PRIMARY KEY (EMP_NO)

);

Y vemos como se crea perfectamente



Y no hay que olvidarnos de hacer la integridad referencial para que nuestra base de datos se

mantenga fiable.

CREANDO LA INTEGRIDAD REFERENCIAL

ALTER TABLE EMPLE ADD CONSTRAINT fk_dept_no

FOREIGN KEY(DEPT_NO) REFERENCES DEPART(DEPT_NO)

ON DELETE CASCADE;



INTRODUCIENDO DATOS

INTRODUCIMOS LOS DEPARTAMENTOS

Introducimos los siguientes datos con el número de departamento al que pertenecen, el

nombre y su localización.

INSERT INTO DEPART VALUES (10,'CONTABILIDAD','SEVILLA');

INSERT INTO DEPART VALUES (20,'INVESTIGACION','MADRID');

INSERT INTO DEPART VALUES (30,'VENTAS','BARCELONA');

INSERT INTO DEPART VALUES (40,'PRODUCCION','BILBAO');

Hacemos un commit para que los datos queden correctamente guardados

COMMIT;

INTRODUCIMOS LOS EMPLEADOS

INSERT INTO EMPLE VALUES (7369,'SANCHEZ','EMPLEADO',7902,TO_DATE('17/12/1980',

'DD/MM/YYYY'),104000,NULL,20);

INSERT INTO EMPLE VALUES (7499,'ARROYO','VENDEDOR',7698,TO_DATE('20/02/1980',

'DD/MM/YYYY'), 208000,39000,30);

INSERT INTO EMPLE VALUES (7521,'SALA','VENDEDOR',7698,TO_DATE('22/02/1981',

'DD/MM/YYYY'), 162500,65000,30);

INSERT INTO EMPLE VALUES (7566,'JIMENEZ','DIRECTOR',7839,TO_DATE('02/04/1981',

'DD/MM/YYYY'), 386750,NULL,20);

INSERT INTO EMPLE VALUES (7654,'MARTIN','VENDEDOR',7698,TO_DATE('29/09/1981',

'DD/MM/YYYY'), 162500,182000,30);

INSERT INTO EMPLE VALUES (7698,'NEGRO','DIRECTOR',7839,TO_DATE('01/05/1981',

'DD/MM/YYYY'), 370500,NULL,30);

INSERT INTO EMPLE VALUES (7782,'CEREZO','DIRECTOR',7839,TO_DATE('09/06/1981',

'DD/MM/YYYY'), 318500,NULL,10);

INSERT INTO EMPLE VALUES (7788,'GIL','ANALISTA',7566,TO_DATE('09/11/1981',

'DD/MM/YYYY'), 390000,NULL,20);

INSERT INTO EMPLE VALUES (7839,'REY','PRESIDENTE',NULL,TO_DATE('17/11/1981',

'DD/MM/YYYY'), 650000,NULL,10);

INSERT INTO EMPLE VALUES (7844,'TOVAR','VENDEDOR',7698,TO_DATE('08/09/1981',

'DD/MM/YYYY'), 195000,0,30);

INSERT INTO EMPLE VALUES (7876,'ALONSO','EMPLEADO',7788,TO_DATE('23/09/1981',

'DD/MM/YYYY'), 143000,NULL,20);

INSERT INTO EMPLE VALUES (7900,'JIMENO','EMPLEADO',7698,TO_DATE('03/12/1981',

'DD/MM/YYYY'), 123500,NULL,30);



INSERT INTO EMPLE VALUES (7902,'FERNANDEZ','ANALISTA',7566,TO_DATE('03/12/1981',

'DD/MM/YYYY'),390000, NULL,20);

INSERT INTO EMPLE VALUES (7934,'MUÑOZ','EMPLEADO',7782,TO_DATE('23/01/1982',

'DD/MM/YYYY'), 169000,NULL,10);

Y hacemos un commit para verificarlo

COMMIT;



EJERCICIOS 1.- En la tabla emp incrementar el salario el 10% a los empleados que tengan una

comisión superior al 5% del salario.

Primero vemos todos los empleados que tenemos en nuestra tabla con sus características. Y

vemos los que en principio podrían resultar afectados, es decir, los que tienen algún tipo de

comisión.

SQL> select * from emple ;

EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION

---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7369 SANCHEZ EMPLEADO 7902 17-DEC-80 104000

20

7499 ARROYO VENDEDOR 7698 20-FEB-80 208000 39000

30

7521 SALA VENDEDOR 7698 22-FEB-81 162500 65000

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7566 JIMENEZ DIRECTOR 7839 02-APR-81 386750

20

7654 MARTIN VENDEDOR 7698 29-SEP-81 162500 182000

30

7698 NEGRO DIRECTOR 7839 01-MAY-81 370500

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7782 CEREZO DIRECTOR 7839 09-JUN-81 318500

10

7788 GIL ANALISTA 7566 09-NOV-81 390000



20

7839 REY PRESIDENTE 17-NOV-81 650000

10


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7844 TOVAR VENDEDOR 7698 08-SEP-81 195000 0

30

7876 ALONSO EMPLEADO 7788 23-SEP-81 143000

20

7900 JIMENO EMPLEADO 7698 03-DEC-81 123500

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7902 FERNANDEZ ANALISTA 7566 03-DEC-81 390000

20

7934 MU??OZ EMPLEADO 7782 23-JAN-82 169000

10

14 rows selected.



Ahora incrementamos el salario el 10% a los empleados que tengan una comisión superior al

5% del salario para ello introducimos el siguiente código en el que actualizamos la tabla

emple seleccionando el salario y diciéndole el porcentaje a aumentar donde el salario

cumpla la condición de que ese salarios sea mayor al a 5 entre cien o lo que viene siendo lo

mismo al cinco por ciento.

BEGIN

UPDATE EMPLE

SET SALARIO = SALARIO+SALARIO*(10/100)

WHERE COMISION > (SALARIO*5/100);

END;

/

Volvemos a seleccionar a todos los usuarios para comprobar que solo han cambiado los que

lo tienen que hacer, es decir, los que hayan cumplido las características.

SQL> select * from emple;


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7369 SANCHEZ EMPLEADO 7902 17-DEC-80 104000

20

7499 ARROYO VENDEDOR 7698 20-FEB-80 228800 39000

30

7521 SALA VENDEDOR 7698 22-FEB-81 178750 65000

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------



7566 JIMENEZ DIRECTOR 7839 02-APR-81 386750

20

7654 MARTIN VENDEDOR 7698 29-SEP-81 178750 182000

30

7698 NEGRO DIRECTOR 7839 01-MAY-81 370500

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7782 CEREZO DIRECTOR 7839 09-JUN-81 318500

10

7788 GIL ANALISTA 7566 09-NOV-81 390000

20

7839 REY PRESIDENTE 17-NOV-81 650000

10


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7844 TOVAR VENDEDOR 7698 08-SEP-81 195000 0

30

7876 ALONSO EMPLEADO 7788 23-SEP-81 143000

20

7900 JIMENO EMPLEADO 7698 03-DEC-81 123500

30


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7902 FERNANDEZ ANALISTA 7566 03-DEC-81 390000

20



2.- Añadir la columna total2 y en ella escribir la suma del salario y la comisión de los

empleados con comisión distinta de 0.

Como vemos creamos el siguiente bloque con el cual alteramos y/o modificamos la tabla

empleado, seleccionando con el cursor la comisión desde la tabla empleado que sea distinta

de 0 para seguidamente actualizar y crear esa columna con la suma de SALARIO+COMISION

ALTER TABLE EMPLE ADD(TOTAL2 NUMBER(10));

DECLARE

CURSOR CURSOR2 IS SELECT COMISION,SALARIO FROM EMPLE

WHERE COMISION <>0

FOR UPDATE;

BEGIN

FOR REG IN CURSOR2 LOOP

UPDATE EMPLE

SET TOTAL2 = SALARIO+COMISION

WHERE CURRENT OF CURSOR2;

END LOOP;

END;

/

Ahora lo comprobamos para ver que todo es correcto

select total2 from emple;



3.- Insertar un empleado en la tabla EMP. Su número será superior a los existentes y

la fecha de incorporación a la empresa será la actual.

Como podemos ver creamos este bloque recogiendo los datos necesarios en variables, para

que luego al ser llamadas, realicen su función y creemos un empleado que es superior a los

existentes y la fecha como indicamos en el código que sea la actual.

DECLARE

num_emple EMPLE.EMP_NO%TYPE;

fecha EMPLE.FECHA_ALT%TYPE;

BEGIN

SELECT MAX(emp_no) INTO num_emple FROM EMPLE;

SELECT SYSDATE INTO fecha FROM DUAL;

num_emple := num_emple+1;

INSERT INTO EMPLE VALUES (num_emple, 'JAVIER', 'EMPLEADO', 7777, fecha, 152000, NULL,

40);

END;

/

Lo comprobamos haciendo el siguiente select partiendo del que antes era el mayor ID y

vemos como ha tenido éxito nuestro bloque

select * from emple where emp_no>=7934;


---------- ---------- ---------- ---------- --------- ---------- ----------

DEPT_NO

----------

7934 MU??OZ EMPLEADO 7782 23-JAN-82 169000

10

7935 JAVIER EMPLEADO 7777 23-JAN-12 152000

40



4.- Realizar un procedimiento para borrar un empleado recibiendo como parámetro

el número de empleado.

Creamos el siguiente procedimiento en el que el único parámetro que se nos va a pedir va a

ser el número de empleado como podemos ver y a partir de ese numero como vemos en el

código ejecutara sobre el un DELETE.

CREATE OR REPLACE PROCEDURE borrar_emple(

num_emple emple.emp_no%TYPE)

AS

BEGIN

DELETE FROM emple WHERE emp_no = num_emple;

END borrar_emple;

/

Vemos que tenemos éxito al crear el procedimiento

Procedemos a borrar el empleado que habíamos creado anteriormente.

Exec borrar_emple(7935) y vemos que se completa correctamente

Por último lo comprobamos y vemos que hemos tenido éxito.

select * from emple where emp_no>=7934;



5.- Realizar un procedimiento para modificar la localidad de un departamento. El

procedimiento recibe como parámetros la localidad y el número de departamento.

En el siguiente procedimiento que creamos va a ser más complejo que el anterior y vamos a

recibir dos parámetros en este orden, número de departamento y el nombre de la localidad

nueva para ello indicamos los datos que se recogen y el tipo de datos y se hace una

actualiozación indicando cuales son los datos que se tienen que actualizar.

CREATE OR REPLACE

PROCEDURE modificar_localidad(

num_depart NUMBER,

localidad VARCHAR2)

AS

BEGIN

UPDATE depart SET loc = localidad

WHERE dept_no = num_depart;

END modificar_localidad;

/

Como vemos el procedimiento se ha creado con éxito

Y lo ejecutamos, pasándole los parámetros correspondientes, con el nombre de la nueva

localidad como cuando se pasa cualquier cadena, entre comillas simples.

exec modificar_localidad (10,'VALLADOLID');



Por último solo faltaría la comprobación haciendo el select pertinente y vemos como los

cambios han tenido efecto.

select * from depart;

Documents

EJERPLSQL1