Package y Procedures

Package y Procedures
******************
Este ejemplo es desarrollado con el esquema de HR de la base de datos Oracle Database 11g XE.


/*Paquetes para un Modelo de BD Relacional*/
create or replace package pack_mante_pais
is
  /*procedures*/
  --registrar
  procedure usp_registrar_pais(
  p_id in countries.country_id%type,
  p_nom in countries.country_name%type,
  p_regid in countries.region_id%type );
  --actualizar
  procedure usp_actualizar_pais(
  p_nom in countries.country_name%type,
  p_regid in countries.region_id%type,
  p_id in countries.country_id%type );
  --eliminar
  procedure usp_eliminar_pais(
  p_id in countries.country_id%type );
  --buscar
  procedure usp_buscar_pais(
  p_id in countries.country_id%type );
 
end pack_mante_pais;

/*Cuerpo del Paquete*/
create or replace package body pack_mante_pais
is
  procedure usp_registrar_pais(
    p_id in countries.country_id%type,
    p_nom in countries.country_name%type,
    p_regid in countries.region_id%type )
  is
  begin
    insert into countries
    values(p_id, p_nom, p_regid);
    DBMS_OUTPUT.put_line('Se Registro el Pais');
  end usp_registrar_pais;
  procedure usp_actualizar_pais(
    p_nom in countries.country_name%type,
    p_regid in countries.region_id%type,
    p_id in countries.country_id%type )
  is
  begin
    update countries set COUNTRY_NAME = p_nom,
    REGION_ID = p_regid where COUNTRY_ID = p_id;
    DBMS_OUTPUT.put_line('Se Actualizo el Pais');
  end usp_actualizar_pais;

  procedure usp_eliminar_pais(
    p_id in countries.country_id%type)
  is
  begin
    delete from countries
    where country_id = p_id;
    commit;
    DBMS_OUTPUT.put_line('Se Elimino el Pais');
  exception
    when others then
      rollback;
      DBMS_OUTPUT.put_line('Se cancelo la eliminacion
      del ID: ' || p_id);
  end usp_eliminar_pais;

  procedure usp_buscar_pais(
    p_id in countries.country_id%type )
  is
  v_nom countries.country_name%type;
  v_regid countries.region_id%type;
  begin
    select country_name, region_id into v_nom, v_regid
    from countries
    where COUNTRY_ID = p_id;
    DBMS_OUTPUT.put_line('Nombre: ' || v_nom);
    DBMS_OUTPUT.put_line('Region ID: ' || v_regid);
  end usp_buscar_pais;

end pack_mante_pais;

--EJECUTAR
set SERVEROUTPUT ON;
begin
  PACK_MANTE_PAIS.USP_REGISTRAR_PAIS('CR','Corea del Norte',3);
end;
begin
  PACK_MANTE_PAIS.USP_BUSCAR_PAIS('CR');
end;
begin
  PACK_MANTE_PAIS.USP_ELIMINAR_PAIS('CR');
  PACK_MANTE_PAIS.USP_ELIMINAR_PAIS('CRX');
  PACK_MANTE_PAIS.USP_ELIMINAR_PAIS('CS');
end;

/*Ing. Jhonatan  Abal M.*/