Taller PL/SQL

Asuma un sistema de información muy simple donde se registran los productos de un almacén.

 CREATE TABLE PRODUCTOS (
   ID SERIAL PRIMARY KEY,
   CODIGO VARCHAR(10),
   NOMBRE VARCHAR(20) NOT NULL,
   CANTIDAD INT NOT NULL);
 INSERT INTO PRODUCTOS (CODIGO,NOMBRE, CANTIDAD) VALUES
  ('1AA1','PRODUCTO 1', 4),
  ('1BB1','PRODUCTO 2', 3),
  ('2AAA','PRODUCTO 3', 6),
  ('BCAA','PRODUCTO 4', 5),
  ('CCA1','PRODUCTO 5', 4),
  ('DDA1','PRODUCTO 6', 3),
  ('BCAA','PRODUCTO 7', 6),
  ('BBDD','PRODUCTO 8', 5),
  ('2AAA','PRODUCTO 9', 5);

En la tabla anterior, los productos se identifican por el ID. Sin embargo, los proveedores del almacén, no hacen uso de dicho ID. En cambio, ellos identifican los productos por medio del CODIGO.

Cuando Los proveedores entregan mercancía al almacén, adjuntan un archivo con los productos que se han entregado. Dicho archivo se sube a la base de datos por medio de la siguiente tabla:

 CREATE TABLE RECEPCION(
  CODIGO VARCHAR(10) NOT NULL,
  CANTIDAD INT);
INSERT INTO RECEPCION (CODIGO,CANTIDAD) VALUES 
 ('1AA1',10), 
 ('2AAA',20),
 ('CCA1',18),
 ('BBDD',6),
 ('CCC1',3);

La idea es desarrollar un procedimiento que actualice la tabla PRODUCTOS con los productos que se han entregado en RECEPCION. Note que algunos CODIGOS se repiten en la tabla PRODUCTOS (e.g. '2AAA') y algunos no existen (e.g. 'CCC1').

Se debe realizar un procedimiento que por cada registro en la tabla RECEPCION, actualice la tabla productos siempre y cuando el CODIGO exista y sea único. Las inconsistencias deben reportarse en la tabla:

 CREATE TABLE INCONSISTENCIAS(
   ID SERIAL PRIMARY KEY,
   CODIGO VARCHAR(10),
   CANTIDAD INT,
   CAUSA CHAR(2));
 ALTER TABLE INCONSISTENCIAS ADD CONSTRAINT INC_CAUSA_CK CHECK (CAUSA IN ('R','NE'));

donde 'R' significa “Código Repetido” y 'NE' significa 'Codigo no Existe'.

CREATE OR REPLACE FUNCTION ACTUALIZAR_PRODUCTOS () RETURNS VOID AS $$
DECLARE
   CUR_RECEPCION CURSOR FOR SELECT CODIGO,CANTIDAD FROM RECEPCION FOR UPDATE;
   RecepVar RECEPCION%ROWTYPE;
   IdProd INT;
   NumProd INT ;
BEGIN
  FOR RecepVar IN CUR_RECEPCION LOOP
    -- Se cuenta el número de productos con el codigo RecepVar.Codigo
    SELECT COUNT(*) INTO NUMPROD FROM PRODUCTOS WHERE CODIGO = RecepVar.CODIGO;
    IF NUMPROD = 1 THEN
      UPDATE PRODUCTOS SET CANTIDAD = CANTIDAD + RECEPVAR.CANTIDAD WHERE CODIGO = RecepVar.CODIGO;
    ELSEIF NUMPROD = 0 THEN
      -- EL PRODUCTO NO EXISTE
      INSERT INTO INCONSISTENCIAS (CODIGO,CANTIDAD,CAUSA) VALUES (RecepVar.Codigo,RecepVar.Cantidad,'NE');
    ELSE
       -- Multiples productos con el mismo código
       INSERT INTO INCONSISTENCIAS (CODIGO,CANTIDAD,CAUSA) VALUES (RecepVar.Codigo,RecepVar.Cantidad,'R');
    
    END IF;
    -- SE ELIMINA EL REGISTRO DE LA TABLA RECEPCION
    DELETE FROM RECEPCION WHERE CURRENT OF CUR_RECEPCION; 
    
  END LOOP;
END;
$$ LANGUAGE plpgsql;
 
materias/4to_taller_pl_pgsql.txt · Última modificación: 2012/05/02 14:50 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki