CURSORES
1. Usa esquema HR. Listar todos los empleados (ID,APELLIDO Y SALARIO) de la tabla
EMPLOYEES.
DECLARE
CURSOR CUR_EMP IS
SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMPLOYEES;
REC_EMP CUR_EMP%ROWTYPE;
BEGIN
OPEN CUR_EMP;
LOOP
FETCH CUR_EMP INTO REC_EMP;
EXIT WHEN CUR_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('CODIGO DEPART ' || REC_EMP.EMPLOYEE_ID);
DBMS_OUTPUT.PUT_LINE('NOMBRE DEPART ' || REC_EMP.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('LOCALIDAD '|| REC_EMP.SALARY);
DBMS_OUTPUT.PUT_LINE('****************************');
END LOOP;
CLOSE CUR_EMP;
END;
2. Usar esquema HR. Crear una tabla VENDEDORES compuesta por 3 columnas: número
empleado, salario y comisión. Hacer un programa PL/SQL que busque a todos los empleados de la
tabla EMPLOYEES que tengan comisión no nula y los inserte en la tabla creada anteriormente.
-- Resuelto con los 3 tipos de bucles
CREATE TABLE VENDEDORES (
EMPNO NUMBER(4,0),
SAL NUMBER(7,2),
COMM NUMBER(7,2)
);
-- Usando LOOP
DECLARE
CURSOR C1 IS
SELECT employee_id,salary,commission_pct FROM EMPLOYEES WHERE
COMMISSION_PCT IS NOT NULL; --
-- definimos registro sobre el cursor
reg_vend C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO reg_vend;
EXIT WHEN C1%NOTFOUND;
INSERT INTO VENDEDORES VALUES
(reg_vend.EMPLOYEE_ID,reg_vend.SALARY,reg_vend.COMMISSION_PCT);
END LOOP;
CLOSE C1;
COMMIT;
END;
/
-- Usando WHILE:
DECLARE
CURSOR C1 IS
SELECT employee_id,salary,commission_pct FROM EMPLOYEES WHERE
COMMISSION_PCT IS NOT NULL; --
-- definimos registro sobre el cursor
reg_vend C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO reg_vend; -- primer registro
WHILE C1%FOUND LOOP
INSERT INTO VENDEDORES VALUES
(reg_vend.EMPLOYEE_ID,reg_vend.SALARY,reg_vend.COMMISSION_PCT);
FETCH C1 INTO reg_vend;
END LOOP;
CLOSE C1;
COMMIT;
END;
/
-- Usando FOR
DECLARE
CURSOR C1 IS
SELECT employee_id,salary,commission_pct FROM EMPLOYEES WHERE
COMMISSION_PCT IS NOT NULL; --
BEGIN
FOR reg_vend IN C1 LOOP -- registro se define aquí
INSERT INTO VENDEDORES VALUES
(reg.EMPLOYEE_ID,[Link],reg.COMMISSION_PCT);
END LOOP;
COMMIT;
END;
-- PROBAR DESDE AQUÍ!!!!!!!!
3. Crear una tabla SALARIOS de dos columnas, nombre y salario. Insertar en esta tabla el nombre y
el salario de los primeros 3 empleados con mayor salario(ordenar por salario) de la tabla
EMPLOYEES.
NOTA: Se recuerda que NOMBRE_CURSOR%ROWCOUNT devuelve el número de filas del
cursor
CREATE TABLE SALARIOS
(NOMBRE VARCHAR2(30),
SALARIO NUMBER)
/
DECLARE
CURSOR C_SALARIOS IS
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC;
V_SALARIOS C_SALARIOS%ROWTYPE;
BEGIN
OPEN C_SALARIOS;
FETCH C_SALARIOS INTO V_SALARIOS;
WHILE C_SALARIOS%ROWCOUNT < 4 LOOP
INSERT INTO SALARIOS VALUES(V_SALARIOS.ENAME, V_SALARIOS.SAL);
FETCH C_SALARIOS INTO V_SALARIOS;
END LOOP;
CLOSE C_SALARIOS;
COMMIT;
END;
4. Actualizar el trabajo a DIRECTOR a todos aquellos empleados cuyo salario sea mayor que 2000.
Almacenar el número de empleados actualizados por la operación en la tabla TEMP. Si los
afectados son mas de 5 personas, borrar los empleados cuyo salario sea mayor que 3000, insertar en
la tabla TEMP el número de empleados borrados y validar la transacción.
NOTA: SQL%ROWCOUNT devuelve el número de filas afectadas por una operación DML
(insert,update,delete).
DECLARE
NUM_MODIF NUMBER(3);
NUM_BORR NUMBER(3);
BEGIN
UPDATE EMP
SET JOB = 'DIRECTOR'
WHERE SAL > 2000;
NUM_MODIF := SQL%ROWCOUNT;
INSERT INTO TEMP VALUES (NUM_MODIF,'PERSONAS MODIFICADAS');
DBMS_OUTPUT.PUT_LINE(NUM_MODIF || ' PERSONAS MODIFICADAS');
IF NUM_MODIF > 5 THEN
DELETE FROM EMP
WHERE SAL > 3000;
NUM_BORR := SQL%ROWCOUNT;
INSERT INTO TEMP VALUES (NUM_BORR,'PERSONAS BORRADAS');
DBMS_OUTPUT.PUT_LINE(NUM_BORR || ' PERSONAS BORRADAS');
END IF;
END;
TRIGGERS
[Link] esquema HR. Crearemos dos tablas para realizar pruebas.
- Crear una tabla NUEVO_DEPT basada en DEPARTMENTS. (CREATE TABLE .. AS
SELECT…)
- Crear otra tabla de históricos de DEPARTMENTS, llamada HIST_DEPT.
- Crear un disparador que inserte en la tabla HIST_DEPT, todos los registros que se borren de la
tabla NUEVO_DEPT (por cada fila, antes de borrar en NUEVO_DEPT).
DROP TABLE NUEVO_DEPT;
DROP TABLE HIST_DEPT;
CREATE TABLE NUEVO_DEPT AS SELECT * FROM DEPARTMENTS;
CREATE TABLE HIST_DEPT AS SELECT * FROM DEPARTMENTS;
CREATE OR REPLACE TRIGGER DEPT_HISTORICO
BEFORE DELETE ON NUEVO_DEPT
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE ('INSERTAMOS EN HISTORICO EL DEPARTAMENTO: '
||:OLD.DEPARTMENT_ID);
INSERT INTO HIST_DEPT VALUES (:OLD.DEPARTMENT_ID,
:OLD.DEPARTMENT_NAME, :OLD.MANAGER_ID,:OLD.LOCATION_ID);
END;
DELETE FROM NUEVO_DEPT WHERE DEPARTMENT_ID=10;
SELECT * FROM HIST_DEPT;
SELECT * FROM NUEVO_DEPT;
ROLLBACK;
2. Usar esquema HR. Crear una tabla APELLIDOS, con un campo apellidos varchar2 (100), que va
a guardar los apellidos antes de ser actualizados. Crear un disparador (trigger) llamado
ACTUALIZA_APELLIDO. Debe comprobar:
- Antes de actualizar el campo apellido en la tabla de empleados
- Por cada fila
- Cuando el apellido sea LOPEZ
- Insertar en la tabla APELLIDOS el apellido antiguo
Realizar un update en la tabla EMPLOYEES para verificarlo.
Mostrar los datos de la tabla apellidos.
CREATE TABLE APELLIDOS (APELLIDO VARCHAR2(100));
CREATE OR REPLACE TRIGGER actualiza_apellido
BEFORE UPDATE OF LAST_NAME ON EMPLOYEES
FOR EACH ROW
WHEN (NEW.LAST_NAME = 'LOPEZ')
BEGIN
INSERT INTO APELLIDOS VALUES (:OLD.LAST_NAME);
END;
rollback;
UPDATE EMPLOYEES SET LAST_NAME = 'LOPEZ' WHERE EMPLOYEE_ID=103;
SELECT * FROM apellidos;
-- SEGUIR POR AQUI
3. Crear un disparador que impida insertar o actualizar un departamento con DEPTNO = 99.
Mostrar un mensaje de error.
CREATE OR REPLACE TRIGGER DEPTNO_99 BEFORE INSERT OR UPDATE
OF DEPTNO ON DEPT
FOR EACH ROW
WHEN ([Link]=99)
BEGIN
– Lanzar mensaje en lugar de excepción
RAISE_APPLICATION_ERROR(-20002,‘ERROR: DEPARTAMENTO 99 NO PERMITIDO’);
END;
/
INSERT INTO DEPT VALUES (99, ‘NAME’, ‘LOC’);
UPDATE DEPT SET DEPTNO = 99 WHERE DEPTNO=10;
