Cursores

Clasificado en Otras materias

Escrito el en español con un tamaño de 6,67 KB

 

1.Escribir un bloque PL que utilice un cursor explicito para visualizar el nombre y la localidad de todos los departamentos: DECLARE CURSOR C1 IS SELECT APELLIDO FROM EMPLE WHERE dept_no=20;V_APELLIDO VARCHAR (20);BEGIN OPEN C1; LOOP FETCH C1 INTO V_APELLIDO;EXIT WHEN C1%NOTFOUND;DMBS_OUTPUT.PUT_LINE (TO_CHAR (C1%ROWCOUNT, ‘99’) || V_APE);END LOOP;CLOSE C1;END;/ 2.Escribir un procedimiento que subirá el sueldo de todos los empleados del departamento indicado en la llamada. La subida será el porcentaje indicado en la llamada.CREATE OR REPLACE PROCEDURE SUBIR_SAL(V_NUMDEP NUMBER, V_PORSUBIDA NUMBER)AS CURSOR C1 IS SELECT DEPT_NO, SALARIO FROM EMPLE WHERE DEPT_NO=V_NUMDEP FOR UPDATE; V_REG_EMP C1% V_INC NUMBER;BEGIN  OPEN C1;   FETCH C1 INTO V_REG_EMP;V_REG_EMP C1%FOUND LOOP;V_INC:= (V_REG_EMP.SALARIO/100)*V_SUBIDA;UPDATE EMPLE SET SALARIO=SALARIO+V_INC WHERE  CURRENT OF C1;FETCH C1 INTO V_REG_EMP;END LOOP;END; 3. Crear un bloque donde se define la excepción err_blancos asociada con un error definido por el programador y la excepción no hay espacio asociándolo con el error numero –1547 de Oracle. DECLARE Cod_err number(6);Men_err varchar2(30);Vcod varchar2(10);Vnom varchar2(15);Err_blancos EXCEPTION;No_hay_espacio EXCEPTION;PRAGMA EXCEPTION_INIT(no_hay_espacio,-1547);BEGIN SELECT emp_no,apellido INTO vcod, vnom FROM EMPLE;IF SUBSTR(vnom,1,1) = ‘  ‘ THEN RAISE err_blancos; END IF; UPDATE clientes SET nombre=vnom WHERE nif=vnif; EXCEPTION WHEN err_blancos THEN    INSERT INTO terror(col1,col2) VALUES (vcod,‘ERR blancos’);WHEN no_hay_espacio THEN  INSERT INTO terror(col2) VALUES (‘ERR tablespace’);WHEN NO_DATA_FOUND THEN INSERT INTO terror(col2) VALUES (‘ERR no habia datos’);WHEN TOO_MANY_ROWS THEN  INSERT INTO terror(col2) VALUES (‘ERR demasiados datos’);WHEN OTHERS THEN Cod_err := SQLCODE;Men_err := SQLERRM; INSERT INTO terror(col1,col2) VALUES(cod_err , men_err);END;4. Escribir un procedimiento que reciba los datos de un nuevo empleado. El procedimiento debera controlar los siguientes errores:No existe departamento con RAISE_APPLICATION_ERROR.No existe director con RAISE_APPLICATION_ERROR.Numero de empleado duplicado.CREATE OR REPLACE PROCEDURE alta_emp( numemple.emp_no%TYPE,ape emple.apellido%TYPE,ofi emple.oficio%TYPE,jef emple.dir%TYPE, fec emple.fecha_alt%TYPE,sal emple.salario%TYPE,com emple.comision%TYPE DEFAULT NULL,dep emple.dept_no%TYPE) AS 11   v_dummy_jef EMPLE.DIR%TYPE DEFAULT NULL;v_dummy_dep DEPART.DEPT_NO%TYPE DEFAULT NULL;BEGINSELECT dept_no INTO v_dummy_dep FROM depart WHERE dept_no = dep;SELECT emp_no INTO v_dummy_jef  FROM emple WHERE emp_no = jef;INSERT INTO EMPLE VALUES (num, ape, ofi, jef, fec, sal, com, dep);EXCEPTION  WHEN NO_DATA_FOUND THEN IF v_dummy_dep IS NULL THEN  RAISE_APPLICATION_ERROR(-20005, 'Err. Departamento inexistente');ELSIF v_dummy_jef IS NULL THEN RAISE_APPLICATION_ERROR(-20005,33  'Err. No existe el jefe');ELSE RAISE_APPLICATION_ERROR(-20005,36  'Err. Datos no encontrados(*)'); END IF;WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Err.numero de empleado duplicado');RAISE;END alta_emp;/



5.Crear el trigger auditar_subida_salario, que se disparará después de cada modificación de la columna salario de la tabla EMPLE:CREATE TABLE AUDITAR_EMPLE (COL1 VARCHAR2 (200));CREATE OR REPLACE TRIGGER auditar_subida_sueldo     AFTER UPDATE OF salario ON emple FOR EACH ROW BEGIN INSERT INTO auditar_emple VALUES('SUBIDA SALARIO EMPLEADO' || :old.emp_no); END;/6.Crear un trigger que se disparará cada vez que se borre un empleado, guardando su número de empleado, apellido y el departamento en una fila de la tabla AUDITAR_EMPLE:CREATE OR REPLACE TRIGGER auditar_borrado_emple BEFORE DELETE ON emple FOR EACH ROW BEGIN INSERT INTO auditar_emple VALUES   ('BORRADO EMPLEADO'||'*'||:OLD.EMP_NO||'*'||:OLD.APELLIDO||'*DEPT.'||:OLD.DEPT_NO);END;7.Escribe un disparador que inserte en la tabla AUDITAR_EMPLE cualquier cambio que supere el 5% del salario del  empleado indicando la fecha y hora, el nº del empleado y el salario anterior y posterior.CREATE OR REPLACE TRIGGER auditar_sal_5por   AFTER UPDATE OF SALARIO ON EMPLE  FOR EACH ROW  WHEN (ABS(NEW.SALARIO - OLD.SALARIO) > (0.05 * OLD.SALARIO)) BEGIN   INSERT INTO AUDIT_EMPLE VALUES(TO_CHAR(SYSDATE,'DD/MM/YY*HH24:MI*')||USER||'*'||:OLD.EMP_NO||'CAMBIO SALARIO SUPERA EL 5%'||'*||:OLD.SALARIO||'*'||:NEW.SALARIO);END;7. Escribe un disparador que permite auditar las operaciones de inserción o borrado de datos que se realicen en la tabla EMPLE según las siguientes especificaciones:-Se creara la tabla audit_emple con col1 varchar2 (200).-Cuando se produzca cualquier manipulación, se insertara un fila en la tabla creada que contendrá: fecha y hora, nº empleado,  apellido y la palabra INSERCIÓN O BORRADO según la actualización.  CREATE OR REPLACE TRIGGER AUDITAR_ACT_EMP BEFORE INSERT OR DELETE ON EMPLE  FOR EACH ROW BEGIN  IF DELETING THEN INSERT INTO AUDIT_EMPLE  VALUES(TO_CHAR(SYSDATE,'DD/MM/YY*HH24:MI*')||:OLD.EMP_NO||'*'||:OLD.APELLIDO||'*BORRADO');END IF;IF INSERTING THEN INSERT INTO AUDIT_EMPLE VALUES(TO_CHAR(SYSDATE,'DD/MM/YY*HH24:MI*')||:NEW.EMP_NO||'*'||:NEW.APELLIDO||'*INSERCION');END IF;END;7- Escribir un disparador que controle las conexiones de los usuarios en la base de datos. Para ello crearemos la siguiente tabla:  CREATE TABLE CONTROL_CONEXIONES(USUARIO VARCHAR2(20),MOMENTO TIMESTAMP,EVENTO VARCHAR2(20)); CREATE OR REPLACE TRIGGER CTL_CONX AFTER LOGON ON DATABASE BEGIN  INSERT INTO CONTROL_CONEXIONES(USUARIO, MOMENTO, EVENTO) VALUES(ORA_LOGIN_USER,SYSTIMESTAMP, ORA_SYSEVENT); END;

Entradas relacionadas: