Mi guia PL/SQL

1. Comprobacion de estado de  serveroutput

SHOW SERVEROUTPUT;

Estado  :

SERVEROUTPUT OFF

2. Escribir un bloque anónimo PL/SQL que muestre por pantalla el nombre de usuario de sesión en Oracle (usar la pseudoconstante USER).

BEGIN

        DBMS_OUTPUT.PUT_LINE(' USUARIO DE SESIÓN : ' || USER);
END;
/

3. Activar SERVEROUTPUT e incluir esta activación en el archivo login.SQL

SET SERVEROUTPUT ON;
@login.SQL;

4. Compruebe lo que ocurre ejecutando de nuevo el bloque de código del Ejercicio 2

Se mostro la salida del bloque PL/SQL:
USUARIO DE SESIÓN : OWN_GUIA5

El estado de SERVEROUTPUT  será  ON  en todas las sesiones de usuario en oracle

5. Escribe un bloque PL/SQL donde DECLARE una variable, inicializada a la fecha actual,  que imprime su valor en pantalla

DECLARE
FECHA DATE:=SYSDATE;
BEGIN
        DBMS_OUTPUT.PUT_LINE(' FECHA ACTUAL :  ' || TO_CHAR(FECHA));
END;
/

6. Escribe un bloque PL/SQL que almacene en una variable numérica el año actual. Muestra  en pantalla el año. Puede usar las funciones TO_NUMBER() y TO_CHAR().

DECLARE
YEAR NUMBER;
BEGIN
        SELECT TO_CHAR(SYSDATE, 'YYYY') INTO YEAR FROM DUAL;
        DBMS_OUTPUT.PUT_LINE(' AÑO ACTUAL : ' || YEAR);
END;
/

7. Cree una tabla PRODUCTO(CODPROD, NOMPROD, PRECIO), usando SQL (no usar un bloque PL/SQL)

CREATE TABLE PRODUCTO(
CODPROD INTEGER CONSTRAINT PK_CODIGO_PROD PRIMARY KEY,
NOMPROD VARCHAR2(100) NOT NULL,
PRECIO NUMBER (8,2) NOT NULL );


8. Añadir un producto a la tabla usando una sentencia INSERT dentro de un bloque PL/SQL

BEGIN
      INSERT INTO PRODUCTO VALUES(1,'LAPTOP TOSHIBA 1520',500);

END;
/

9. Añadir otro producto, ahora utilizando una lista de variables en la sentencia INSERT.

DECLARE
CODIGO INTEGER:=2;
PRODUCTO VARCHAR2(100):='DISCO DURO EXTERNO WESTERN 1TB';
PRECIO NUMBER(8,2):=100;
BEGIN
    INSERT INTO PRODUCTO VALUES(CODIGO,PRODUCTO,PRECIO);


END;
/

10. Añadir, ahora usando un registro PL/SQL, dos productos mas.

DECLARE
REGISTRO PRODUCTO%ROWTYPE;
 BEGIN
               REGISTRO.CODPROD:=3;
               REGISTRO.NOMPROD:= 'ANTIVIRUS BITDEFENDER TOTAL SECURITY 2012';
               REGISTRO.PRECIO :=80;

              INSERT INTO PRODUCTO VALUES REGISTRO;

               REGISTRO.CODPROD:=4;
               REGISTRO.NOMPROD:= 'MICROSOFT  WINDOWS 7 PROFESIONAL';
               REGISTRO.PRECIO :=175;

              INSERT INTO PRODUCTO VALUES REGISTRO;

END;
/

11. Borrar el primer producto insertado, e incrementar el precio de los demás en un 5%

DECLARE
PORCENTAJE NUMBER(8,2);
PRECIO_INCREMENTO NUMBER(8,2);
CODIGO INTEGER;
NUM_PRODUCTO INTEGER:=1; -- PRIMER PRODUCTO INSERTADO Ó FILA
CURSOR C_REGISTROS  IS SELECT ROWNUM,CODPROD,PRECIO FROM PRODUCTO;
 BEGIN

      FOR i IN  C_REGISTROS  LOOP

      IF i.ROWNUM=NUM_PRODUCTO THEN

             /* OBTENIENDO CODPROD PRIMER PRODUCTO INSERTADO */
              SELECT CODPROD INTO CODIGO FROM PRODUCTO WHERE ROWNUM=NUM_PRODUCTO;

              DELETE FROM PRODUCTO WHERE CODPROD=CODIGO;
      END IF;

       PORCENTAJE := (i.PRECIO *  0.05);
       PRECIO_INCREMENTO:= (i.PRECIO +  PORCENTAJE);

       UPDATE PRODUCTO SET PRECIO =  PRECIO_INCREMENTO WHERE CODPROD=i.CODPROD;

       END LOOP;
  END;
/

12.   Obtener y mostrar en pantalla el número de productos que hay almacenados, usando SELECT ... INTO y el mensaje “Hay n productos”.

DECLARE
NUMERO_PRODUCTOS INTEGER;
BEGIN
        SELECT COUNT(*) INTO  NUMERO_PRODUCTOS FROM  PRODUCTO;
        DBMS_OUTPUT.PUT_LINE(' Hay  ' || TO_CHAR( NUMERO_PRODUCTOS) ||  ' Productos ');
 END;
/

13. Obtener y muestra todos los datos de un producto (buscar a partir de la clave primaria, escogiendo un código de producto existente.
Usar SELECT ... INTO y un registro PL/SQL.

DECLARE
REGISTRO PRODUCTO%ROWTYPE;
BEGIN

     SELECT CODPROD,NOMPROD,PRECIO INTO REGISTRO FROM PRODUCTO  WHERE CODPROD =3;

     DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(REGISTRO.CODPROD) || ' NOMBRE : ' || TO_CHAR(REGISTRO.NOMPROD)
     || ' PRECIO : '  || TO_CHAR(REGISTRO.PRECIO) );

END;
/

14. Modificar el Ejercicio 13 de forma que busque un producto que no exista

DECLARE
CODIGO INTEGER:=5;
REGISTRO PRODUCTO%ROWTYPE;
EXISTENCIA INTEGER;
BEGIN

       SELECT COUNT(*) INTO EXISTENCIA FROM PRODUCTO WHERE CODPROD=CODIGO;

       IF EXISTENCIA = 0 THEN
                   DBMS_OUTPUT.PUT_LINE(' NO EXISTE EL PRODUCTO ' );

        ELSE
                  SELECT CODPROD,NOMPROD,PRECIO INTO REGISTRO FROM PRODUCTO  WHERE CODPROD=CODIGO;
                 DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(REGISTRO.CODPROD) || ' NOMBRE : ' || TO_CHAR(REGISTRO.NOMPROD)
                 || ' PRECIO : '  || TO_CHAR(REGISTRO.PRECIO) );
        END IF;

 END;
/

15 .  Modificar de nuevo el Ejercicio 13, ahora eliminando el WHERE de la consulta, de forma que
existan varios productos.

DECLARE
CURSOR C_REGISTROS  IS  SELECT CODPROD,NOMPROD,PRECIO  FROM PRODUCTO;

 BEGIN

      FOR REGISTRO IN C_REGISTROS  LOOP

        DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(REGISTRO.CODPROD) || ' NOMBRE : ' || TO_CHAR(REGISTRO.NOMPROD)
       || ' PRECIO : '  || TO_CHAR(REGISTRO.PRECIO) );

     END LOOP;
END;
/

16. Modificar el Ejercicio 12, que mostraba el número de productos, de forma que indique “No hay productos” en vez de “Hay 0 productos”

DECLARE
NUMERO_PRODUCTOS INTEGER;
BEGIN

        SELECT COUNT(*) INTO  NUMERO_PRODUCTOS FROM  PRODUCTO;

         IF  NUMERO_PRODUCTOS = 0 THEN
                 DBMS_OUTPUT.PUT_LINE(' No hay Productos ');
        ELSE
                DBMS_OUTPUT.PUT_LINE(' Hay  ' || TO_CHAR(NUMERO_PRODUCTOS) ||  ' Productos ');
        END IF;

END;
/

17. Modificar de nuevo el bloque PL/SQL anterior, de forma que se indique que no hay produtos, que hay pocos (si hay entre 1 y 3)
o el número exacto de productos existentes (si hay más de 3).

DECLARE
NUMERO_PRODUCTOS INTEGER;
BEGIN

        SELECT COUNT(*) INTO  NUMERO_PRODUCTOS FROM  PRODUCTO;

      IF NUMERO_PRODUCTOS = 0 THEN
                 DBMS_OUTPUT.PUT_LINE(' No hay Productos ');

        ELSIF NUMERO_PRODUCTOS > 0 AND NUMERO_PRODUCTOS <=3 THEN
                  DBMS_OUTPUT.PUT_LINE(' Hay Pocos Productos ');

        ELSIF NUMERO_PRODUCTOS > 3 THEN
                     DBMS_OUTPUT.PUT_LINE(' Hay  ' || TO_CHAR(NUMERO_PRODUCTOS) ||  ' Productos ');

        END IF;

END;
/

18. Usar un bucle simple (LOOP ... END LOOP) para mostrar los números pares entre 1 y 10.
 Pueden usar la función MOD(dividendo,divisor) para averiguar si el número es par o impar

DECLARE
CONTADOR_PARES NUMBER:=0;
BEGIN

     LOOP
         CONTADOR_PARES:=CONTADOR_PARES +1 ;
    EXIT WHEN CONTADOR_PARES  > 10;
     IF MOD(CONTADOR_PARES,2)=0 THEN
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(CONTADOR_PARES));

    END IF;
   END LOOP;

END;
/

19. Imprimir de nuevo los números pares entre 1 y 10, pero ahora usando un bucle WHILE

DECLARE
CONTADOR_PARES NUMBER:=1;
BEGIN

    WHILE (CONTADOR_PARES <=10)
    LOOP

         IF MOD(CONTADOR_PARES,2)=0 THEN
             DBMS_OUTPUT.PUT_LINE(TO_CHAR(CONTADOR_PARES));
    END IF;

    CONTADOR_PARES:= CONTADOR_PARES +1 ;

    END LOOP;

 END;
/

20. Imprimir de nuevo los números pares entre 1 y 10, pero ahora usando un bucle FOR

BEGIN

      FOR  CONTADOR_PARES IN 1..10 LOOP

           IF MOD( CONTADOR_PARES,2)=0 THEN
                  DBMS_OUTPUT.PUT_LINE(TO_CHAR(CONTADOR_PARES));

          END IF;

      END LOOP;
END;
/


21. Utilizar un CURSOR y un bucle LOOP simple para recuperar y mostrar los datos de todos Los productos.
Indicar al final cuantos productos hay

DECLARE
CURSOR C_REGISTROS IS SELECT * FROM PRODUCTO;
CODIGO INTEGER;
PRODUCTO VARCHAR2(100) ;
PRECIO NUMBER(8,2);
BEGIN
    OPEN C_REGISTROS;
    LOOP
      FETCH C_REGISTROS  INTO CODIGO,PRODUCTO,PRECIO;
      EXIT WHEN C_REGISTROS%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(CODIGO) || ' NOMBRE : ' || TO_CHAR(PRODUCTO) || ' PRECIO : '  || TO_CHAR(PRECIO));
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(' CANTIDAD DE PRODUCTOS : ' || C_REGISTROS %ROWCOUNT);
    CLOSE C_REGISTROS ;

END;
/

22. Mostrar los datos de todos los productos, pero ahora usando un bucle WHILE para recorrer el CURSOR, e indicar
también el número de productos encontrado

DECLARE
CURSOR  C_REGISTROS IS SELECT * FROM PRODUCTO;
CODIGO INTEGER;
PRODUCTO VARCHAR2(100) ;
PRECIO NUMBER(8,2);
BEGIN

    OPEN  C_REGISTROS;
    FETCH  C_REGISTROS INTO CODIGO,PRODUCTO,PRECIO;
    WHILE  C_REGISTROS%FOUND
    LOOP
          DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(CODIGO) || ' NOMBRE : ' || TO_CHAR(PRODUCTO) || ' PRECIO : '  || TO_CHAR(PRECIO));
             FETCH C_REGISTROS INTO CODIGO,PRODUCTO,PRECIO;
    END LOOP;
     DBMS_OUTPUT.PUT_LINE(' CANTIDAD DE PRODUCTOS : ' ||  C_REGISTROS%ROWCOUNT);
    CLOSE  C_REGISTROS;

END;
/

23. Recuperar e imprimir de nuevo los datos de todos los productos, y cuantos hay, pero ahora usa un bucle FOR

DECLARE
CONTADOR_PRODUCTOS  INTEGER:=0;
CURSOR C_REGISTROS IS SELECT * FROM PRODUCTO;
BEGIN

      FOR  i IN C_REGISTROS LOOP

            DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(i.CODPROD) || ' NOMBRE : ' || TO_CHAR(i.NOMPROD)
           || ' PRECIO : '  || TO_CHAR(i.PRECIO));

            CONTADOR_PRODUCTOS:= CONTADOR_PRODUCTOS+1;

      END LOOP;
      DBMS_OUTPUT.PUT_LINE(' CANTIDAD DE PRODUCTOS : ' || TO_CHAR(CONTADOR_PRODUCTOS));

   END;
/

24. Modificar el Ejercicio 23, de forma que se utilice la consulta directamente en el bucle FOR en vez de declarar explícitamente el CURSOR.

DECLARE
CONTADOR_PRODUCTOS  INTEGER:=0;
BEGIN
      FOR  i IN  (SELECT * FROM PRODUCTO)  LOOP

                DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(i.CODPROD) || ' NOMBRE : ' || TO_CHAR(i.NOMPROD)
               || ' PRECIO : '  || TO_CHAR(i.PRECIO));

                CONTADOR_PRODUCTOS:= CONTADOR_PRODUCTOS+1;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(' CANTIDAD DE PRODUCTOS : ' || TO_CHAR(CONTADOR_PRODUCTOS));


END;
/

25. Modificar ahora el Ejercicio 24, de forma que se recuperen solo los códigos de los productos.
Ya no es necesario indicar cuantos productos hay.

BEGIN

      FOR  i IN  (SELECT CODPROD FROM PRODUCTO)  LOOP
           DBMS_OUTPUT.PUT_LINE(' CODIGO : ' || TO_CHAR(i.CODPROD));

      END LOOP;


END;
/

26. Recuperar ahora el doble del precio de los productos, usando el bucle FOR con la consulta directamente

BEGIN

      FOR  i IN  (SELECT PRECIO * 2 AS DOBLE  FROM PRODUCTO)  LOOP
           DBMS_OUTPUT.PUT_LINE(' PRECIO :' || TO_CHAR(i.DOBLE));

      END LOOP;

END;
/

27. Escribir un bloque PL/SQL que modifique el producto 3, poniendo Monitor TFT 21'' como nombre del producto, y 497 como precio.
Si no existe el producto con código 3, el bloque PL/SQL escrito debe insertar los valores indicados.
El uso del CURSOR implícito SQL podría ayudar a resolver este ejercicio.

DECLARE
CODIGO INTEGER:=3;
NOMBRE VARCHAR2(100):='Monitor TFT 21''';
PRECIO_PRODUCTO NUMBER(8,2):=497;
EXISTENCIA INTEGER;
BEGIN

      SELECT  COUNT(*) INTO EXISTENCIA FROM PRODUCTO WHERE CODPROD=CODIGO;

        IF EXISTENCIA=0  THEN
                INSERT INTO PRODUCTO VALUES(CODIGO,NOMBRE,PRECIO_PRODUCTO);
                  DBMS_OUTPUT.PUT_LINE(TO_CHAR(CODIGO) ||'   ' || TO_CHAR(NOMBRE) ||'  ' || '   '  ||TO_CHAR(PRECIO_PRODUCTO) || '  REGISTRO INSERTADO ');
        ELSE
                UPDATE PRODUCTO SET NOMPROD=NOMBRE,PRECIO=PRECIO_PRODUCTO WHERE CODPROD=CODIGO;
                 DBMS_OUTPUT.PUT_LINE(TO_CHAR(CODIGO) ||'   ' || TO_CHAR(NOMBRE) ||'  ' || '   '  ||TO_CHAR(PRECIO_PRODUCTO) || '  REGISTRO ACTUALIZADO ');
        END IF;

END;
/

28. En un bloque PL/SQL, intentar crear directamente una tabla T con un campo numérico C (fallara)

BEGIN
CREATE TABLE T(
C NUMBER
);

END;
/

29. Crear la tabla usando SQL dinámico y EXECUTE IMMEDIATE dentro de un bloque PL/SQL.
Comprobar que efectivamente la tabla fue creada, con una consulta simple sobre el catálogo de Oracle.
Finalmente, eliminar (DROP) la tabla.

DECLARE
TABLA VARCHAR2(100);
BEGIN

       EXECUTE IMMEDIATE  'CREATE TABLE T(C NUMBER)';

       SELECT TABLE_NAME INTO TABLA  FROM USER_TABLES WHERE TABLE_NAME='T';
       DBMS_OUTPUT.PUT_LINE(' TABLA : ' || TABLA || ' CREADA ' );


       EXECUTE IMMEDIATE  'DROP TABLE T';

       DBMS_OUTPUT.PUT_LINE(' TABLA : ' || TABLA || ' ELIMINADA ' );

END;
/

30. De nuevo en un bloque PL/SQL, crear una tabla T2 con un campo S alfanumérico, e insertar una fila en esa tabla.

DECLARE
TABLA  VARCHAR2(100);
BEGIN

       EXECUTE IMMEDIATE  'CREATE TABLE T2(S VARCHAR2(100))';

       SELECT TABLE_NAME INTO TABLA  FROM USER_TABLES WHERE TABLE_NAME='T2';
       DBMS_OUTPUT.PUT_LINE(' TABLA : ' || TABLA || ' CREADA ' );

      EXECUTE IMMEDIATE  'INSERT INTO T2 VALUES(''GFX1821'')';
      DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' FILA INSERTADA ');


END;
/

31. Recuperar e imprimir de nuevo los datos de todos los productos, ahora usando SQL dinámico.

DECLARE
TYPE C_REFERIDO IS REF CURSOR;
C_REGISTROS C_REFERIDO;
PRODUCTOS  PRODUCTO%ROWTYPE;
CONSULTA_DINAMICA VARCHAR2(254);
BEGIN

  CONSULTA_DINAMICA:='SELECT * FROM PRODUCTO';

  OPEN C_REGISTROS  FOR CONSULTA_DINAMICA;
  LOOP
     FETCH C_REGISTROS  INTO  PRODUCTOS;
      EXIT WHEN C_REGISTROS%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(' CODIGO :' || TO_CHAR(PRODUCTOS.CODPROD) || ' NOMBRE : ' || TO_CHAR( PRODUCTOS.NOMPROD)
      || ' PRECIO : '   || TO_CHAR(PRODUCTOS.PRECIO));

   END LOOP;

             CLOSE C_REGISTROS;
END;
/


32. Alterar la tabla de productos, añadiendo una restricción c_precio_positivo que compruebe que el precio de un producto es mayor que cero.

ALTER TABLE PRODUCTO
ADD CONSTRAINT c_precio_positivo CHECK(PRECIO > 0);


33. Averiguar como podría controlar el error de clave primaria (o restricción única) duplicada.

El error se  puede controlar con la excepción:

 DUP_VAL_ON_INDEX


 34. Crear un bloque PL/SQL en el que se inserta un producto con código 3, ya existente. Haz el control de excepciones
de forma que un error de clave primaria duplicada se ignore (no se inserta el producto pero el bloque PL/SQL se ejecuta correctamente).
Si intentamos insertar un producto con precio negativo, debe aparecer un error.

DECLARE
CODIGO INTEGER:=3;
PRODUCTO VARCHAR2(100):='MINI LAPTOP ACER 1510';
PRECIO NUMBER(8,2):=350;
BEGIN

    IF PRECIO > 0 THEN
          INSERT INTO PRODUCTO VALUES(CODIGO,PRODUCTO,PRECIO);

   ELSE
           DBMS_OUTPUT.PUT_LINE(  ' ERROR : PRECIO  NEGATIVO ');

   END IF;

      EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
          DBMS_OUTPUT.PUT_LINE(  ' CLAVE PRIMARIA DUPLICADA ');


END;
/

 35. Eliminar la restricción c_precio_positivo creada en el Ejercicio 32.

ALTER TABLE PRODUCTO
DROP CONSTRAINT  c_precio_positivo;


36. Escribe un bloque PL/SQL donde usas un registro para almacenar los datos de un producto.
El bloque insertará ese producto, pero si el precio no es positivo generar ´a una excepción de tipo precio_prod_invalido,
que deberá declarar previamente, en vez de realizar la inserción

DECLARE
REGISTROS PRODUCTO%ROWTYPE;
precio_prod_invalido EXCEPTION;
BEGIN
               REGISTROS.CODPROD:=6;
               REGISTROS.NOMPROD:= 'IPAD 3';
               REGISTROS.PRECIO :=1000;

               IF  REGISTROS.PRECIO > 0 THEN
                       INSERT INTO PRODUCTO VALUES REGISTROS;

              ELSE

                        RAISE precio_prod_invalido;

              END IF;

              EXCEPTION
                  WHEN   precio_prod_invalido  THEN
                  DBMS_OUTPUT.PUT_LINE(  ' ERROR : PRECIO NEGATIVO ');
END;
/

37. Modificar el Ejercicio 36, de forma que capture la excepción elevada

DECLARE
REGISTROS PRODUCTO%ROWTYPE;
precio_prod_invalido EXCEPTION;
BEGIN

               REGISTROS.CODPROD:=7;
               REGISTROS.NOMPROD:= 'IPHONE 5';
               REGISTROS.PRECIO :=700;

               IF   REGISTROS.PRECIO > 0 THEN
                       INSERT INTO PRODUCTO VALUES REGISTROS;

              ELSE

                        RAISE precio_prod_invalido;

              END IF;

              EXCEPTION
                 WHEN   precio_prod_invalido  THEN
                 RAISE_APPLICATION_ERROR(-20001,' ERROR : PRECIO NEGATIVO ');
END;
/

38. Modificar de nuevo el Ejercicio 36, de forma que ahora captures la excepción elevada y produces otra,
 con SQLCODE y mensaje de error especifico, usando RAISE_APPLICATION_ERROR

 DECLARE
REGISTROS PRODUCTO%ROWTYPE;
precio_prod_invalido EXCEPTION;
NUM_ERROR NUMBER;
BEGIN

               REGISTROS.CODPROD:=8;
               REGISTROS.NOMPROD:= 'MOTHERBOARD BIOSTAR A15';
               REGISTROS.PRECIO :=65;

               IF REGISTROS.PRECIO > 0 THEN
                      INSERT INTO PRODUCTO VALUES REGISTROS;

              ELSE
                        RAISE precio_prod_invalido;

              END IF;

              NUM_ERROR := SQLCODE;

              EXCEPTION
                 WHEN   precio_prod_invalido  THEN
                 NUM_ERROR := SQLCODE;
                 DBMS_OUTPUT.PUT_LINE(' PRECIO INVALIDO :'||TO_CHAR( NUM_ERROR));
                 RAISE_APPLICATION_ERROR(-20001,' ERROR : PRECIO NEGATIVO ');

END;
/

39. Crear un procedimiento InsertaProd para insertar un producto. Usar tres parámetros,
uno para cada atributo (código, nombre, precio).

CREATE OR REPLACE  PROCEDURE  InsertaProd (CODIGO INTEGER, NOMBRE VARCHAR2, PRECIO NUMBER)
IS
BEGIN

    INSERT INTO PRODUCTO VALUES(CODIGO,NOMBRE,PRECIO);


END;
/

40. Revisar si hubo errores de compilación en la creación del procedimiento, y obtener la información sobre el procedimiento
usando las tablas del catálogo USER_PROCEDURES y USER_OBJECTS (o su SINónimo OBJ).


SHOW ERRORS;

Resultado :
No hay Errores

OBTENIENDO INFORMACION  DESDE USER_PROCEDURES

SELECT * FROM USER_PROCEDURES   WHERE   OBJECT_NAME='INSERTAPROD' ;


OBTENIENDO INFORMACION DESDE USER_OBJECTS

SELECT * FROM USER_OBJECTS  WHERE OBJECT_NAME='INSERTAPROD' ;


41. Examinar la estructura de la tabla USER_SOURCE y usarla para obtener el código fuente PL/SQL del procedimiento InsertaProd

Examinando Estructura de la tabla USER_SOURCE :

DESCRIBE USER_SOURCE;
SELECT * FROM USER_SOURCE;

Obteniendo el Codigo Fuente del Procedimiento InsertProd :

SELECT LINE, TEXT  FROM USER_SOURCE WHERE  NAME='INSERTAPROD';


42. Ejecutar el procedimiento, insertando un producto mediante CALL y otro usando EXEC

CALL  InsertaProd(10,'TORRE 100 DVD ORACLE',20);

EXEC InsertaProd(11,'MOUSE INALAMBRICO MICROSOFT',25);


43. Averiguar que ocurre si creamos un bloque PL/SQL en el que insertamos varios productos llamando al procedimiento InsertaProd,
pero una de las llamadas al procedimiento falla. Por ejemplo, trata de insertar la misma fila 2 veces.


Informe de errores despues de ejecutar el bloque PL/SQL:

ORA-00001: restricción única (OWN_GUIA5.PK_CODIGO_PROD) violada
ORA-06512: en "OWN_GUIA5.INSERTAPROD"
00001. 00000 -  "unique constraint (%s.%s) violated"

El bloque PL/SQL se ejecuta con errores y ninguna de las llamadas al procedimiento InsertaProd se realiza
correctamente porque existen 2 filas que tienen el mismo valor en el campo CODPROD y este campo  contiene una restricción
que no permite valores duplicados porque es una clave primaria.
Las llamadas al procedimiento InsertaProd se ejecutaran exitosamente si los valores que contengan en el campo CODPROD
no coincidan con un existente.


44. Intentar crear otro procedimiento InsertaProd que inserte un producto tomando como único parámetro un registro de tipo producto.

CREATE OR REPLACE PROCEDURE  InsertaProd(REGISTRO PRODUCTO%ROWTYPE)
IS

BEGIN
          INSERT INTO PRODUCTO VALUES REGISTRO;

END;
/

45. Crea un procedimiento ListaProds que toma como parámetro un valor numérico y lista todos los productos
con precio mayor o igual a ese valor

CREATE OR REPLACE PROCEDURE ListaProds(VALOR NUMBER)
IS
CURSOR  C_REGISTROS IS SELECT  NOMPROD FROM PRODUCTO WHERE PRECIO >=VALOR;
BEGIN

      FOR i IN  C_REGISTROS LOOP
           DBMS_OUTPUT.PUT_LINE( TO_CHAR(i.NOMPROD) );

      END LOOP;

END;
/

46. Crear una función triple que devuelva el triple del número que toma como argumento

CREATE OR REPLACE FUNCTION TRIPLE(NUMERO NUMBER)
RETURN NUMBER
IS
BEGIN

            RETURN (NUMERO * 3);
END;
/

47. Crear una función PVP que toma como argumento un precio (de coste) del producto y devuelve el precio de venta al público,
que resulta de aplicarle a ese precio de coste un margen comercial del 20%.

CREATE OR REPLACE FUNCTION PVP(PRECIO_COSTE NUMBER)
RETURN NUMBER
IS
MARGEN_COMERCIAL NUMBER;
P_VENTA_PUBLICO NUMBER;
BEGIN
          MARGEN_COMERCIAL := (PRECIO_COSTE * 0.20);
          P_VENTA_PUBLICO:= PRECIO_COSTE + MARGEN_COMERCIAL;

          RETURN P_VENTA_PUBLICO;

END;
/

48. Crea una vista VPRODUCTO que obtenga todos los datos de los productos más el precio de venta al público.

CREATE VIEW VPRODUCTO AS
SELECT CODPROD, NOMPROD, PRECIO, PVP(PRECIO) AS P_VENTA_PUBLICO FROM PRODUCTO;


49. Examinar los datos de la vista VPRODUCTO. Modificar la función PVP para que aplique un margen del 22%,
y vuelve a examinar los datos de la vista.

Examinando datos de la vista :

SELECT * FROM VPRODUCTO;  /* MUESTRA PRECIO DE VENTA AL PUBLICO CON MARGEN DEL 20% */


Modificando la funcion PVP para aplicar margen del 22% :

CREATE OR REPLACE FUNCTION PVP(PRECIO_COSTE NUMBER)
RETURN NUMBER
IS
MARGEN_COMERCIAL NUMBER;
P_VENTA_PUBLICO NUMBER;
BEGIN
          MARGEN_COMERCIAL := (PRECIO_COSTE * 0.22);
          P_VENTA_PUBLICO:= PRECIO_COSTE + MARGEN_COMERCIAL;

          RETURN P_VENTA_PUBLICO;

END;
/

Examinando datos de la vista :

SELECT * FROM VPRODUCTO;  /* MUESTRA PRECIO DE VENTA AL PUBLICO CON MARGEN DEL 22% */


50. Crear un paquete (PACKAGE) gestprod que usará para gestionar los productos. Debe incluir las signaturas de:

- Dos procedimientos InsertaProd, uno que tome 3 parámetros (código, nombre y precio) y otro que tome
solo un parámetro: un registro de tipo producto.

- Una función BorraProd que borre un producto. Devolverá un valor booleano, indicando si borro el producto o no (porque no existía)

CREATE OR REPLACE PACKAGE gestprod AS

PROCEDURE  InsertaProd (CODIGO INTEGER, NOMBRE VARCHAR2, PRECIO NUMBER);
PROCEDURE  InsertaProd(REGISTRO PRODUCTO%ROWTYPE);
FUNCTION     BorraProd(CODIGO INTEGER) RETURN BOOLEAN;

END  gestprod;
/

51. Crea el cuerpo del paquete (PACKAGE BODY) para el paquete gestprod.

CREATE OR REPLACE PACKAGE BODY  gestprod AS

PROCEDURE  InsertaProd (CODIGO INTEGER, NOMBRE VARCHAR2, PRECIO NUMBER)
IS
BEGIN

    INSERT INTO PRODUCTO VALUES(CODIGO,NOMBRE,PRECIO);

END InsertaProd;

PROCEDURE  InsertaProd(REGISTRO PRODUCTO%ROWTYPE)
IS

BEGIN
          INSERT INTO PRODUCTO VALUES REGISTRO;

END InsertaProd;

FUNCTION BorraProd(CODIGO INTEGER)
RETURN BOOLEAN
IS
EXISTE INTEGER;
ELIMINADO BOOLEAN;
BEGIN

          SELECT COUNT(*) INTO EXISTE FROM PRODUCTO WHERE CODPROD=CODIGO;

          IF EXISTE = 0 THEN
                   ELIMINADO:= FALSE;

          ELSE
                  DELETE FROM PRODUCTO WHERE CODPROD=CODIGO;
                  ELIMINADO:= TRUE;

          END IF;

          RETURN ELIMINADO;

END BorraProd;

END  gestprod;
/

52. Crear un TRIGGER que fuerce a que los precios de los productos sean positivos. Controla solo las modificaciones (UPDATE) de datos.

CREATE OR REPLACE TRIGGER PRECIO_POSITIVO_UPDATE
BEFORE  UPDATE  ON PRODUCTO
FOR EACH ROW
DECLARE
PRECIO_POSITIVO NUMBER(8,2);
BEGIN
         PRECIO_POSITIVO:=ABS(:NEW.PRECIO);
        :NEW.PRECIO:=PRECIO_POSITIVO;
END ;
/


53. Crear otro TRIGGER que fuerce a que los precios de los productos sean positivos, pero en este caso controla solo las inserciones

CREATE OR REPLACE TRIGGER PRECIO_POSITIVO_INSERT
BEFORE  INSERT  ON PRODUCTO
FOR EACH ROW
DECLARE
PRECIO_POSITIVO NUMBER(8,2);
BEGIN
         PRECIO_POSITIVO:=ABS(:NEW.PRECIO);
        :NEW.PRECIO:=PRECIO_POSITIVO;
END ;
/


54. Repitir el Ejercicio 32, añadiendo de nuevo la restricción CHECK que comprueba que los precios de los productos son positivos.
Ahora compruebe que se activa antes, la restricción o el TRIGGER

ALTER TABLE PRODUCTO
ADD CONSTRAINT c_precio_positivo CHECK(PRECIO > 0);

Se activa primero el TRIGGER  porque tiene el modificador BEFORE y antes de hacer
la inserción ejecuta lo que esta dentro del cuerpo del TRIGGER por lo cual el precio del producto
se fuerza a que siempre sea  positivo usando la funcion ABS.

55. Comprobar que orden sigue Oracle en la activación de 2 triggers que comparten tabla, evento, granularidad y tiempo de activación

Para comprobar el orden activación de triggers  se creo el TRIGGER PRECIO_INCREMENTO,
el cual incrementa el precio del producto en un 5% :

CREATE OR REPLACE TRIGGER PRECIO_INCREMENTO
BEFORE  INSERT ON PRODUCTO
FOR EACH ROW
DECLARE
P_INCREMENTO NUMBER(8,2);
RESULTADO NUMBER(8,2);
INCREMENTO NUMBER(8,2):=0.05;
BEGIN

        P_INCREMENTO:= :NEW.PRECIO * INCREMENTO;
        RESULTADO:= :NEW.PRECIO + P_INCREMENTO;
        :NEW.PRECIO:= RESULTADO;

END ;
/

Se inserto 1 registro en la tabla producto en el cual el precio del producto es negativo

CALL gestprod.InsertaProd(20,'DVD-ROM',-60.55);

El orden de activación de TRIGGER se realizo de la siguiente manera:
Se activo el primer TRIGGER que se creo para la tabla producto que comparte el evento INSERT  (TRIGGER PRECIO_POSITIVO_INSERT) este
cambio el precio del producto a positivo, luego se ejecuto el segundo TRIGGER que creo para la tabla PRODUCTO que comparte el mismo evento
el cual es el TRIGGER PRECIO_INCREMENTO.



56. Cree las tablas para almacenar facturas y líneas de factura

 Tabla Facturas :

CREATE TABLE  FACTURAS(
NUMERO INTEGER CONSTRAINT PK_NUMERO_FACTURA PRIMARY KEY,
FECHA DATE DEFAULT(SYSDATE),
TOTAL NUMBER(8,2)
);

Tabla Lineas :

CREATE TABLE LINEAS(
NUMFAC INTEGER NOT NULL CONSTRAINT FK_NUMERO_FACTURA
REFERENCES FACTURAS(NUMERO),
NUMLI INTEGER NOT NULL,
CODPROD INTEGER NOT NULL  CONSTRAINT FK_CODIGO_PROD
REFERENCES PRODUCTO(CODPROD),
CANTIDAD INTEGER NOT NULL,
PRECIO NUMBER(8,2),
SUBTOTAL NUMBER(8,2)
);

57. Asegurar de que cuando se añade una factura el total sea 0.

CREATE OR REPLACE TRIGGER TF_TOTAL_CERO
BEFORE  INSERT ON FACTURAS
FOR EACH ROW
BEGIN
        :NEW.TOTAL:=0;
END;
/

58. Cuando se añade una línea de factura, debe especificarse (entre otras cosas) el código de producto y la cantidad. Crea un TRIGGER t_subtotal_ins
para que tanto el precio como el subtotal de esa línea se cubran automáticamente

CREATE OR REPLACE TRIGGER t_subtotal_ins
BEFORE  INSERT ON LINEAS
FOR EACH ROW
DECLARE
PRECIO_PRODUCTO  NUMBER(8,2);
SUBTOTAL NUMBER(8,2);
BEGIN

    /* PVP : FUNCION   QUE CALCULA EL PRECIO DE VENTA AL PUBLICO */
     SELECT PVP(PRECIO) INTO PRECIO_PRODUCTO  FROM PRODUCTO WHERE CODPROD=:NEW.CODPROD;

     :NEW.PRECIO:=PRECIO_PRODUCTO;

     SUBTOTAL:= (:NEW.CANTIDAD * PRECIO_PRODUCTO);

     :NEW.SUBTOTAL:=SUBTOTAL;

END ;
/

59. Crea otro TRIGGER t_subtotal_upd, similar al realizado para el Ejercicio 58, para realizar el mismo cometido
cuando se modifica una línea de factura.

CREATE OR REPLACE TRIGGER t_subtotal_upd
BEFORE  UPDATE  ON LINEAS
FOR EACH ROW
DECLARE
PRECIO_PRODUCTO  NUMBER(8,2);
SUBTOTAL NUMBER(8,2);
BEGIN

     /* PVP : FUNCION   QUE CALCULA EL PRECIO DE VENTA AL PUBLICO */
     SELECT PVP(PRECIO) INTO PRECIO_PRODUCTO  FROM PRODUCTO WHERE CODPROD=:NEW.CODPROD;

     :NEW.PRECIO:=PRECIO_PRODUCTO;

     SUBTOTAL:= (:NEW.CANTIDAD * PRECIO_PRODUCTO);

     :NEW.SUBTOTAL:=SUBTOTAL;

END ;
/

60. Combina los triggers de los ejercicios 58 y 59 en un ´unico TRIGGER t_subtotal. Decidir si se debe eliminar o deshabilitar
los triggers t_subtotal_ins y t_subtotal_upd una vez se haya creado.


CREATE OR REPLACE TRIGGER t_subtotal
BEFORE  INSERT OR  UPDATE  ON LINEAS
FOR EACH ROW
DECLARE
PRECIO_PRODUCTO  NUMBER(8,2);
SUBTOTAL NUMBER(8,2);
BEGIN

     /* PVP : FUNCION   QUE CALCULA EL PRECIO DE VENTA AL PUBLICO */
     SELECT PVP(PRECIO) INTO PRECIO_PRODUCTO  FROM PRODUCTO WHERE CODPROD=:NEW.CODPROD;

     :NEW.PRECIO:=PRECIO_PRODUCTO;

     SUBTOTAL:= (:NEW.CANTIDAD * PRECIO_PRODUCTO);

     :NEW.SUBTOTAL:=SUBTOTAL;

END;
/

Se eliminaran los triggers  t_subtotal_ins y t_subtotal_upd  no son necesarios
el TRIGGER  t_subtotal se encargara de obtener el precio del producto y calcular el subtotal
y se disparará cuando se realiza una inserción ó actualización.

DROP TRIGGER  t_subtotal_ins;
DROP TRIGGER t_subtotal_upd;


61. Crear el(los) TRIGGER(s) necesarios para mantener correctamente actualizado el total de la factura.

CREATE OR REPLACE TRIGGER TOTAL_FACTURA
BEFORE  INSERT OR UPDATE OR DELETE  ON LINEAS
FOR EACH ROW
DECLARE
PRECIO_PRODUCTO  NUMBER(8,2);
SUBTOTAL NUMBER(8,2);
TOTAL_ACTUAL NUMBER(8,2);
TOTAL_FACTURA NUMBER(8,2);
TOTAL_ANTERIOR NUMBER(8,2);
BEGIN


     IF INSERTING THEN

            SELECT PVP(PRECIO) INTO PRECIO_PRODUCTO  FROM PRODUCTO WHERE CODPROD=:NEW.CODPROD;
            SUBTOTAL:= (:NEW.CANTIDAD * PRECIO_PRODUCTO);

            SELECT TOTAL INTO TOTAL_ACTUAL FROM   FACTURAS WHERE NUMERO=:NEW.NUMFAC;
            TOTAL_FACTURA:= (SUBTOTAL + TOTAL_ACTUAL);

            UPDATE FACTURAS SET TOTAL=TOTAL_FACTURA  WHERE NUMERO=:NEW.NUMFAC;

    ELSIF UPDATING THEN

             SELECT PVP(PRECIO) INTO PRECIO_PRODUCTO  FROM PRODUCTO WHERE CODPROD=:NEW.CODPROD;
            SUBTOTAL:= (:NEW.CANTIDAD * PRECIO_PRODUCTO);

            SELECT TOTAL INTO TOTAL_ACTUAL FROM   FACTURAS WHERE NUMERO=:NEW.NUMFAC;
            TOTAL_ANTERIOR := (TOTAL_ACTUAL - :OLD.SUBTOTAL);

            TOTAL_FACTURA:= (TOTAL_ANTERIOR + SUBTOTAL);
            UPDATE FACTURAS SET TOTAL=TOTAL_FACTURA  WHERE NUMERO=:NEW.NUMFAC;

    ELSIF DELETING THEN

            SELECT TOTAL INTO TOTAL_ACTUAL FROM   FACTURAS WHERE NUMERO=:OLD.NUMFAC;
            TOTAL_FACTURA := (TOTAL_ACTUAL - :OLD.SUBTOTAL);

            UPDATE FACTURAS SET TOTAL=TOTAL_FACTURA  WHERE NUMERO=:OLD.NUMFAC;

    END IF;

END ;
/