Download - EJERPLSQL1
![Page 1: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/1.jpg)
EJERCICIOS 1 PL/SQL
2012
Javier García Cambronel SEGUNDO DE ASIR
23/01/2012
![Page 2: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/2.jpg)
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
![Page 3: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/3.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 2
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.
![Page 4: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/4.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 3
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
![Page 5: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/5.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 4
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;
![Page 6: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/6.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 5
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);
![Page 7: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/7.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 6
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;
![Page 8: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/8.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 7
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
DEPT_NO
----------
7782 CEREZO DIRECTOR 7839 09-JUN-81 318500
10
7788 GIL ANALISTA 7566 09-NOV-81 390000
![Page 9: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/9.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 8
20
7839 REY PRESIDENTE 17-NOV-81 650000
10
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
DEPT_NO
----------
7902 FERNANDEZ ANALISTA 7566 03-DEC-81 390000
20
7934 MU??OZ EMPLEADO 7782 23-JAN-82 169000
10
14 rows selected.
![Page 10: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/10.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 9
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;
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 228800 39000
30
7521 SALA VENDEDOR 7698 22-FEB-81 178750 65000
30
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
DEPT_NO
----------
![Page 11: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/11.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 10
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
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
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
DEPT_NO
----------
7902 FERNANDEZ ANALISTA 7566 03-DEC-81 390000
20
![Page 12: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/12.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 11
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;
![Page 13: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/13.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 12
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;
EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION
---------- ---------- ---------- ---------- --------- ---------- ----------
DEPT_NO
----------
7934 MU??OZ EMPLEADO 7782 23-JAN-82 169000
10
7935 JAVIER EMPLEADO 7777 23-JAN-12 152000
40
![Page 14: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/14.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 13
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;
![Page 15: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/15.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 14
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');
![Page 16: EJERPLSQL1](https://reader033.vdocuments.co/reader033/viewer/2022051609/547fb1bb5806b5c25e8b495f/html5/thumbnails/16.jpg)
EJERCICIOS 1 PL/SQL[ ] 23 de enero de 2012
SEGUNDO DE ASIR Página 15
Por último solo faltaría la comprobación haciendo el select pertinente y vemos como los
cambios han tenido efecto.
select * from depart;