Examen 3ºEvaluacion PL/SQL

DESC TCLIENTE   DESC EMPLEADO DESC TVENDEDOR   DESC TPROVINCIA    DESC DEPARTAMENTO
  DNI       NUM_EMP       COD_VEN          CODIGO             NUM_DEPTO
  NOMBRE        NOMBRE        NOM_VEN          NOMBRE             NOMBRE
  PROVINCIA     PUESTO        PORCEN           TOTAL_COMPRA       LOCALIDAD
  TIPO          NUM_SUP       TITULACION
  FECHA_ALTA    FECHA_ALTA    SUPERIOR
 VENDEDOR        SALARIO
 COMPRAS        COMISION
                NUM_DEP




--1ºEjercicio Crear Una Vista con los cliente de la tabla TCLIENTE  cuya provincia esta en la tabla TPROVINCIA

    CREATE VIEW AS
        SELECT *
        FROM TPROVINCIA IN
            (SELECT *
             FROM TPROVINCIA
               WHERE PROVINCIA=CODIGO)

--2ºEjercicio Crea una secuencia llamada SEC con el valor 2000

CREATE SECUENCE SEC START WITH 2000

--3ºEjercicio Insertar una tupla en la tabla EMPLEADO  con el valor de la secuencia SEC como numero de superior ,como nombre PEPE GARCIA  , con la fecha actual del sistema y numero de departamento 7 , no rellenando el resto de campos

   INSERT INTO EMPLEADO --SERA NECESARIO INDICAR EN QUE VALORES DE LA COLUMNA INTRODUCIMOS LOS CAMPOS  EN ESTE CASO , AL ESTAR DENTRO DE UNA VISTA
               (NUM_EMP,NOMBRE,NUM_SUP,FECHA_ALTA,NUM_DEP)
  VALUES (SEC NEXTVAL,'PEPE GARCIA',SEC_CURVAL,SYSDATE,7);

--4ºEjercicio Crea una restriccion para el campo superior de la tabla TVENDEDOR  que se encuentra en una clave ajena que apunta al campo COD_VEN de la --misma tabla
   ALTER TABLE TVENDEDOR
          ADD CONSTRAINT CA_TVENDEDOR FOREIGN KEY(SUPERIOR)
              REFERENCES TVENDEDOR (COD_VEN)

--5ºEjercicio Borra la restriccion anterior

ALTER TABLE TVENDEDOR
      DROP CONSTRAINT CA_TVENDEDOR;


--6ºEjercicio  Programa que utilizando cursor y la funcion CONTARBLANCOS  escriba en pantalla los nombres de los empleados junto los nombres de las --localidades donde trabajan solo si su superior es el 1003. Cada nombre con su localidad debera escribirse tantas veces como blancos tenga el nombre

SET SERVEROUTPUT ON;
      CURSOR CEMP IS
                 SELECT EMPLEADO.NOMBRE , NOMBRE ,LOCALIDAD
                   FROM EMPLEADO,DEPARTAMENTO
                   WHERE NUM_DEP=NUM_DEPTO
                  AND NUM_SUP='1003');

     X CEMP%ROWTYPE;

BEGIN
   OPEN CEMP;
         LOOP
        FETCH X INTRO CEMP;
        EXIT WHEN CEMP%NOTFOUND;

          FOR I IN 1 .. CONTARBLANCOS(X.NOMBRE) LOOP

         DBMS_OUTPUT.PUT_LINE(X.NOMBRE)    LOOP
         END LOOP;
     END LOOP;
  CLOSE CEMP;
 END;
/


--7ºEjercicio El programa hace una llamada asl procedimiento 'CAMBIAR' .Modificar el procedimiento haciendo que se controle la excepcion cuando la --sentencia SELECT que incluye no devuelve ninguna tupla

CREATE OR REPLACE PROCEDURE CAMBIAR                        IS





      EXCEPTION
              WHEN NO_DATA_FOUND THEN  --CUANDO NO ENCUENTRE DATOS  MOSTRAR MENSAJE
                  DBMS_OUTPUT.PUT_LINE('NO SE HAN ENCONTRADO TUPLAS');
              WHEN OTHERS THEN --SI SUCEDE OTRA EXCEPCION
                 DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||', ,'||SUBSTR(SQLERRM,1,50);



--8ºEjercicio  Realiza un procedimiento almacenado que reciba como parametro un puesto (POR EJEMPLO,ANALISTA)  y sunba un 5% el sueldo de las dos personas que ocupan ese puesto y cobran menos .

--A)Al resto de personas que tienen ese puesto se les subira un 3%.

--B)Si no hay ninguna persona que tiene ese puesto , se considerará que es una excepcion y se tendra que mostrar en pantalla un mensaje como 'NO HAY NADIE EN ESE PUESTO'.

CREATE OR REPLACE PROCEDURE  SUBIRSUELDO (PUES EMPLEADO.TCLIENTE%TYPE) IS
    CURSOR CEMP IS
         SELECT * FROM EMPLEADO
                WHERE PUESTO=PUES
           ORDER BY SALARIO;
   X  CEMP%ROWTYPE;

   BEGIN
       OPEN CEMP;
    LOOP;
           FETCH  CEMP INTO X;
           EXIT WHEN CEMP%NOTFOUND;
      --SI LA CANTIDAD DE FILAS DE CEMP ES MENOR O IGUAL QUE 2
          IF CEMP%ROWCOUNT <=2 THEN

           UPDATE EMPLEADO SET SALARIO=SALARIO*1,0
           WHERE X.NUM_EMP=NUM_EMP;
       END IF;
     --SI SOLO QUEREMOS PILLAR LAS DOS PRIMERAS QUITAMOS EL ELSE
    EXIT WHEN CEMP%NOTFOUND OR CEMP%ROWCOUNT>2;
         UPDATE EMPLEADO SET SALARIO=SALARIO *1,05
         WHERE X.NUM_EMP=NUM_EMP;

--9ºEjercicio Crear un TRIGGER que impida la insercion o modificacion de una tupla en la tabla TCLIENTE cuando el codigo de vendedor no sea uno de los existentes en la tabla TVENDEDOR  o la provincia no sea una de las de la tabla PROVINCIA

--En el caso de que alguno de esos campos tenga valor NULL  se llevara a cabo la sentencia (no se impedira) pero se informara sobre ello

INSERT INTO TCLIENTE ('5555','PEPE','BU','13/10/2005','002',10000)
-----------------------------------------------------------------------
CREATE OR REPLACE TRIGGER AS
     BEFORE INSERT OR UPDATE ON TCLIENTE FOR EACH ROW
      Y TPROVINCIA.CODIGO%TYPE;
      X TVENDEDOR.COD_VEN%TYPE;
BEGIN
       --SI ESTAN VACIOS LOS CAMPOS
       IF :NEW.PROVINCIA IS NULL
        OR :NEW.VENDEDOR IS NULL    THEN
             DBMS_OUTPUT.PUT_LINE ('LA PROVINCIA O EL VENDEDOR SON NULOS');

       ELSE
           SELECT CODIGO INTO Y FROM TPROVINCIA
            WHERE CODIGO:=NEW.PROVINCIA;

           SELECT COD_VEN INTO X FROM TVENDEDOR
            WHERE COD_VEN:=NEW.VENDEDOR;

     END IF;
           WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATTION_ERROR (10001,'NO SE PUEDEN INSERTAR TUPLAS EN TCLIENTE');