Muestra del procedimiento abcc y actividad de segundo parcial

Les dejo la muestra del procedimiento:

Noten que cambié el procedimiento a municipios_abcc, que significa municipios altas, bajas, cambios y consultas.

De lo que expliqué en la mañana, en la opción 4, en lugar de varios IF usé CASE, es lo mismo pero más limpio.

Recuerden que la entrega es para este viernes 7 de octubre y es un procedimiento almacenado por cada tabla catálogo. Además de haberlo compilado en la base de datos, lo guardan en su carpeta personal de DropBox (un archivo de texto por cada procedimiento). Con esto cerramos las actividades del segundo parcial.

En breve subiré el proyecto en NetBeans.



create or replace procedure municipios_abcc
(
  p_op in number  --1 insert, 2 update, 3 delete, 4 consultas
, p_valido out number  --1 sí, 2 no válido
, p_error out varchar2 --mensaje de error cuando no es válida la ejecución
, p_municipio_id in out number  --llave primaria, se genera cuando es insert, no se modifica
, p_nombre in out varchar2  --nombre del municipio
, p_estado_id in out number --llave foránea, viene de la tabla estados
, p_consulta out sys_refcursor --resultado de la consulta para la opcion 4
) as
begin
--limpiando y validando

  p_nombre:= upper(trim(p_nombre));

  if p_op=1 then
    p_municipio_id:= sec_municipios.nextval;

    insert into municipios(municipio_id, nombre, estado_id)values
    (p_municipio_id, p_nombre, p_estado_id);
  end if;

  if p_op=2 then--actualizando
    update municipios set
       nombre=p_nombre ,
       estado_id = p_estado_id
    where municipio_id=p_municipio_id;
  end if;

  if p_op=3 then--borrando
    delete from municipios  where municipio_id = p_municipio_id;
  end if;
 
  if p_op = 4 then --consultado
     case
     when p_municipio_id is not null then --si es consulta por PK
        open p_consulta for
        select m.municipio_id, m.nombre, e.nombre estado_nombre, m.estado_id
        from municipios m, estados e
        where m.estado_id  = e.estado_id
        and   m.municipio_id = p_municipio_id
        order by m.nombre;
     when p_nombre is not null then --si es consulta por nombre, buscamos por patrón
        open p_consulta for
        select m.municipio_id, m.nombre, e.nombre estado_nombre, m.estado_id
        from municipios m, estados e
        where m.estado_id  = e.estado_id
        and   m.nombre like '%'||p_nombre||'%'
        order by m.nombre;
     when p_estado_id is not null then --si es por estado buscamos por esta FK
        open p_consulta for
        select m.municipio_id, m.nombre, e.nombre estado_nombre, m.estado_id
        from municipios m, estados e
        where m.estado_id  = e.estado_id
        and   m.estado_id = p_estado_id
        order by m.nombre;
     else        --si no es ninguna de las anteriores, regresamos toda la tabla  
        open p_consulta for
        select m.municipio_id, m.nombre, e.nombre estado_nombre, m.estado_id
        from municipios m, estados e
        where m.estado_id  = e.estado_id
        order by m.nombre;    
     end case;
   
  end if;
 
  p_valido:=1;

  exception
  when others then
    p_valido:=0;
    p_error :='Ocurrió un error '||sqlcode||' '||sqlerrm;
end municipios_abcc;