Para poder trabajar con estos ejemplos deberás tener creadas las tablas de demostración. Para obtener el fichero que las crea automáticamente haz click aquí.

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

BEGIN
UPDATE EMP
SET SAL=SAL+SAL*(10/100)
WHERE COMM>(SAL*5/100);
END;
/

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.

ALTER TABLE EMP ADD(TOTAL2 NUMBER(7,2));
DECLARE
CURSOR CURSOR2 IS SELECT COMM,SAL FROM EMP
WHERE COMM IS NOT NULL AND COMM<>0 FOR UPDATE;

BEGIN
FOR REG IN CURSOR2 LOOP
UPDATE EMP
SET TOTAL2=SAL+COMM
WHERE CURRENT OF CURSOR2;
END LOOP;
END;
/

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.

DECLARE
NUM_EMPLEADO EMP.EMPNO%TYPE;
FECHA EMP.HIREDATE%TYPE;
BEGIN
SELECT MAX(EMPNO) INTO NUM_EMPLEADO FROM EMP;
SELECT SYSDATE INTO FECHA FROM DUAL;
NUM_EMPLEADO:=NUM_EMPLEADO +1;
INSERT INTO EMP VALUES (NUM_EMPLEADO,'PEDRO','MATEMATIC',7839,FECHA,3000,NULL,20,NULL);
END;
/

4.- Realizar un procedimiento para borrar un empleado recibiendo como parámetro el número de empleado.

DECLARE
NUM_EMPLEADO EMP.EMPNO%TYPE;
MRW ROWID;
BEGIN
SELECT EMPNO,ROWID INTO NUM_EMPLEADO,MRW FROM EMP WHERE EMPNO=&EMPLEADO;
DELETE FROM EMP WHERE ROWID=MRW;
END;
/

UNDEFINE 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.

DECLARE
LOCALIDAD DEPT.LOC%TYPE;
NUMERO DEPT.DEPTNO%TYPE;
R ROWID;
BEGIN
SELECT LOC,DEPTNO,ROWID INTO LOCALIDAD,NUMERO,R FROM DEPT WHERE DEPTNO=&&NUMERO;
UPDATE DEPT
SET LOC='&LOCALIDAD' WHERE ROWID=R;
END;
/

UNDEFINE NUMERO;
UNDEFINE LOCALIDAD;

6.- Realizar un procedimiento para cambiar la fecha por el número de año.

DECLARE
FECHA VARCHAR2(4);
CURSOR CURSOR1 IS SELECT TO_CHAR(HIREDATE,'YYYY') FROM EMP FOR UPDATE;
BEGIN
OPEN CURSOR1;
LOOP
FETCH CURSOR1 INTO FECHA;
EXIT WHEN CURSOR1%NOTFOUND;
UPDATE EMP
SET HIREDATE='FECHA' WHERE CURRENT OF CURSOR1;
END LOOP;
CLOSE CURSOR1;
END;
/

7.- Añadir un nuevo empleado en la tabla emp. El número de empleado será el del último + 10. La fecha la actual, el departamento el 40.

DECLARE
EMPLEADO EMP.EMPNO%TYPE;
FECHAA EMP.HIREDATE%TYPE;

PROCEDURE EJERCICIO_7_1(FECHA IN EMP.HIREDATE%TYPE,
NUMERO_EMP IN EMP.EMPNO%TYPE,DEPARTAMENTO IN EMP.DEPTNO%TYPE) IS
BEGIN
INSERT INTO EMP VALUES (NUMERO_EMP,'NOMBRE','JOB',NULL,FECHA,999,NULL,DEPARTAMENTO);
END EJERCICIO_7_1;

FUNCTION TRANSFORMACION1 RETURN NUMBER IS
RETORNO EMP.EMPNO%TYPE;
MAYOR_NUMERO EMP.EMPNO%TYPE;
BEGIN
SELECT MAX(EMPNO) INTO MAYOR_NUMERO FROM EMP;
RETORNO:=MAYOR_NUMERO+10;
RETURN(RETORNO);
END TRANSFORMACION1;
FUNCTION TRANSFORMACION2 RETURN DATE IS
RETORNO EMP.HIREDATE%TYPE;
BEGIN
RETORNO:=SYSDATE;
RETURN(RETORNO);
END TRANSFORMACION2;

BEGIN

EMPLEADO:=TRANSFORMACION1;
FECHAA:=TRANSFORMACION2;
EJERCICIO_7_1(FECHAA,EMPLEADO,40);
END;
/

8.- Sobre la tabla EMP insertar un nuevo 'SALESMAN'. Su número de empleado será consecutivo. Su departamento será el mismo que el de su jefe 'CLARK'.

DECLARE
EMPLEADO EMP.EMPNO%TYPE;
DEPARTAMENTO EMP.DEPTNO%TYPE;
MANAGER EMP.EMPNO%TYPE;

PROCEDURE EJERCICIO_7_2(NUMERO_EMP IN EMP.EMPNO%TYPE,DEPARTAMENTO_EMP IN EMP.DEPTNO%TYPE,
MANAGER_EMP IN EMP.EMPNO%TYPE)
IS
BEGIN
INSERT INTO EMP VALUES(NUMERO_EMP,'PEDRO','SALESMAN',MANAGER,'17/12/81',1500,NULL,DEPARTAMENTO);
END EJERCICIO_7_2;

FUNCTION TRANSFORMACION3 RETURN NUMBER IS
RETORNO EMP.EMPNO%TYPE;
MAYOR_NUMERO EMP.EMPNO%TYPE;
BEGIN
SELECT MAX(EMPNO) INTO MAYOR_NUMERO FROM EMP;
RETORNO:=MAYOR_NUMERO+1;
RETURN(RETORNO);
END TRANSFORMACION3;

FUNCTION TRANSFORMACION4 RETURN NUMBER IS
DEPARTAMENTO1 EMP.DEPTNO%TYPE;
BEGIN
SELECT DEPTNO INTO DEPARTAMENTO1 FROM EMP WHERE UPPER(ENAME)='CLARK';
RETURN(DEPARTAMENTO1);
END TRANSFORMACION4;

FUNCTION TRANSFORMACION5 RETURN NUMBER IS
MANAGER1 EMP.EMPNO%TYPE;
BEGIN
SELECT EMPNO INTO MANAGER1 FROM EMP WHERE UPPER(ENAME)='CLARK';
RETURN(MANAGER1);
END TRANSFORMACION5;

BEGIN
EMPLEADO:=TRANSFORMACION3;
DEPARTAMENTO:=TRANSFORMACION4;
MANAGER:=TRANSFORMACION5;
EJERCICIO_7_2 (EMPLEADO,DEPARTAMENTO,MANAGER);
END;
/

9.- Que encuentre el primer empleado que tiene un salario superior a 2000.

DECLARE
PROCEDURE ENCONTRAR IS
CURSOR C1 IS SELECT EMPNO,ENAME FROM EMP WHERE SAL>2000;
REGISTRO C1%ROWTYPE;

BEGIN
OPEN C1;
FETCH C1 INTO REGISTRO;
IF C1 %ROWCOUNT=1 THEN
DBMS_OUTPUT.PUT_LINE(REGISTRO.EMPNO || ' '||REGISTRO.ENAME);
ELSE
DBMS_OUTPUT.PUT_LINE('NO SE ENCONTRO');
END IF;
CLOSE C1;
END ENCONTRAR;

BEGIN
ENCONTRAR;
END;

10.- Listar todos los empleados que tienen un nombre de 5 letras.

DECLARE
PROCEDURE EJERCICIO_7_4 IS
CURSOR C1 IS SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE LENGTH(ENAME)=&long;
REGISTRO C1%ROWTYPE;

BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REGISTRO;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (REGISTRO.ENAME ||' '||REGISTRO.JOB ||' '||REGISTRO.SAL||' '||REGISTRO.DEPTNO);
END LOOP;
CLOSE C1;
END EJERCICIO_7_4;
BEGIN
EJERCICIO_7_4;
END;
/

11.- Buscar todos los empleados que tienen un salario + comisión superior a 2000 y asignarles como nuevo salario esta suma. Sólo para los que tienen comisión.

DECLARE
PROCEDURE ACTUALIZAR IS
CURSOR C1 IS SELECT ENAME,SAL,COMM FROM EMP WHERE SAL+COMM>2000 FOR UPDATE;
REGISTRO C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REGISTRO;
EXIT WHEN C1%NOTFOUND;
UPDATE EMP
SET SAL=SAL+COMM WHERE CURRENT OF C1;
END LOOP;
CLOSE C1;
END ACTUALIZAR;

BEGIN
ACTUALIZAR;
END;
/

12.- Realizar un procedimiento que guarde en una vista el nombre y la fecha de alta de los empleados por nombre.

DECLARE
PROCEDURE GUARDAR IS
CURSOR CURSOR1 IS SELECT ENAME,HIREDATE FROM EMP ORDER BY ENAME ;
REGISTRO CURSOR1%ROWTYPE;

BEGIN
OPEN CURSOR1;
LOOP
FETCH CURSOR1 INTO REGISTRO;
EXIT WHEN CURSOR1%NOTFOUND;
INSERT INTO BORRARE6
VALUES (REGISTRO.ENAME,REGISTRO.HIREDATE);
END LOOP;
CLOSE CURSOR1;
END GUARDAR;

BEGIN
GUARDAR;

END;

13.- Realizar un procedimiento para crear una vista con el nombre de cada departmento y número de empleados que tiene.

CREATE OR REPLACE PROCEDURE DINAMICO (INSTRUCCION VARCHAR2) AS
cid INTEGER;
DUMMY INTEGER;
BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, INSTRUCCION, dbms_sql.v7);
DUMMY:=DBMS_SQL.EXECUTE(CID);
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END DINAMICO;
/

EXECUTE DINAMICO('CREATE VIEW VISTA7 AS (SELECT COUNT(DISTINCT(ENAME)) N_EMP,DEPTNO FROM EMP GROUP BY DEPTNO)');

14.- Realizar un procedimiento para guardar el nombre y el número de empleado de los empleados cuyo apellido contenga una cadena. La cadena se le pasa al procedimiento.

DECLARE
--T EMP.ENAME%TYPE;
PROCEDURE BUSCAR718(CADENA IN EMP.ENAME%TYPE) IS
E EMP.EMPNO%TYPE;
P EMP.ENAME%TYPE;
BEGIN
SELECT EMPNO,ENAME INTO E,P FROM EMP WHERE P LIKE 'SMITH';
DBMS_OUTPUT.PUT_LINE(E || P);
END BUSCAR718;
BEGIN

BUSCAR718('SMITH');
END;

15.- Cómo borrar o crear una tabla utilizando PL*SQL.

DECLARE
PROCEDURE drop_table IS
cid INTEGER;
BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'DROP TABLE ' || 'EMP', dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END drop_table;

BEGIN
drop_table;
END;

VOLVER