Clase III - Modulo 2

CONECTARSE A NORHWIND
/* CREAR UNA SECUENCIA SQ_REGION */
CREATE SEQUENCE SQ_REGION START WITH 5 INCREMENT BY 1;


/* CREAR UN TRIGGER QUE UTILICE LA SECUENCIA E INCREMENTE EL CODIGO DE LA REGION */

CREATE TRIGGER REGION_SEQUENCIAS BEFORE INSERT ON REGION
FOR EACH ROW
BEGIN
      :NEW.REGIONID:=SQ_REGION.NEXTVAL;

END;
/


/* CREAR UN PROCEDIMIENTO ALMACENADO QUE INSERTE UNA REGION EN LA TABLA REGION Y
RECIBA COMO PARAMETROS SOLO NOMBREREGION (DEBE DE IR VALIDADO EN NO REPETIR REGIONES) */

CREATE OR REPLACE PROCEDURE INSERT_R(REGION VARCHAR2)
IS
EXISTE NUMBER;
BEGIN
    SELECT COUNT(*) INTO EXISTE  FROM REGION WHERE REGIONDESCRIPTION=(UPPER(REGION));

    IF EXISTE=1 THEN
        DBMS_OUTPUT.PUT_LINE(' REGION : ' || REGION || ' EXISTENTE ');


     ELSE
        INSERT INTO REGION(REGIONDESCRIPTION)VALUES(REGION);
    END IF;

END;
/
SELECT COUNT(*) AS EXISTE  FROM REGION WHERE REGIONDESCRIPTION='Centro America';


CREAR UN PROCEDIMIENTO ALMACENADO PARA ACTUALIZAR LOS DATOS DE UNA REGION
                   */
CREATE OR REPLACE PROCEDURE UPDATE_R(IDREG NUMBER, REGION VARCHAR2)
IS
BEGIN
    UPDATE REGION SET REGIONDESCRIPTION=REGION WHERE REGIONID=IDREG;
END;
/


/* CREAR UN PROCEDIMIENTO ALMACENADO PARA ELIMINAR UNA REGION  */
CREATE OR REPLACE PROCEDURE  ELIMINAR_R(IDREG NUMBER)
IS
BEGIN
    DELETE FROM REGION WHERE REGIONID=IDREG;

END;
/



/* CREAR UN PROCEDIMIENTO ALMACENADO PARA MOSTRAR LAS ORDENES EMITIDAS POR REGION */
POR REGION */


SELECT a.orderid FROM orders a inner join
territories b ON a.territoryid=b.territoryid
inner join region c ON b.regionid=a.territoryid
inner join employees  s ON s.employeeid=a.employeeid;



CREATE OR REPLACE FUNCTION Extraer(Cadena VARCHAR2)
RETURN VARCHAR2 IS
resp VARCHAR2(100);
BEGIN
    resp:=SUBSTR(Cadena, -1,INSTR(Cadena,';'));
    RETURN resp;

END;
/

CREATE OR REPLACE FUNCTION  Actualizar(Cadena VARCHAR2) RETURN
VARCHAR2 IS
resp VARCHAR2(100);
BEGIN
    resp:=SUBSTR(Cadena, INSTR(Cadena, ';') + 1,
    LENGTH(Cadena));
    RETURN resp;

END;
/



SELECT extraer('34;6.46;100.65;') FROM dual;

/*  crear un sp que inserte utilizando las funciones extraer y actualizar    */

CREATE OR REPLACE PROCEDURE add_detalle(Productos VARCHAR2, Precios VARCHAR2,
Cantidades VARCHAR2)
IS
Producto NUMBER;
Cantidad NUMBER;
Precio   NUMBER(8,2);
BEGIN
        FOR i IN 1..LENGTH(Productos) LOOP

                Producto:=TO_NUMBER(Extraer(Productos));
            Producto:=Actualizar(Productos);

            Cantidad:=TO_NUMBER(Extraer(Cantidades));
            Cantidad:=Actualizar(Cantidades);

            Precio:=TO_NUMBER(Extraer(Precios));
            Precio:=Actualizar(Precios);

            INSERT INTO Detalle(CodProducto,Cant,Prec)VALUES
            (Producto,Cantidad,Precio);

        END LOOP;
END;
/


call add_detalle(8;7;8,7;6;7,10;5.70;100);


CREATE OR REPLACE PACKAGE Utilitarios AS
    PROCEDURE INSERT_R(nombre VARCHAR2);
    PROCEDURE UPDATE_R(id NUMBER, nombre VARCHAR2);
    FUNCTION Extraer(cadena VARCHAR2) RETURN VARCHAR2;
    FUNCTION Actualizar(cadena VARCHAR2) RETURN VARCHAR2;


END Utilitarios;
/

CREATE OR REPLACE PACKAGE BODY Utilitarios
AS
    PROCEDURE INSERT_R(nombre VARCHAR2) IS
    PROCEDURE UPDATE_R(id NUMBER, nombre VARCHAR2);
    FUNCTION Extraer(cadena VARCHAR2);
    FUNCTION Actualizar(cadena VARCHAR2);


END Utilitarios;
/


invocar con paquetes

SELECT Utilitarios.extraer('15.2;63.2) from duaL;