Entradas

Mostrando entradas de noviembre 9, 2016

CURSORES EXPLÍCITOS Y PROCEDURES EN ORACLE DATABASE 11G

/*CURSORES EXPLICITOS*/
--habilitar salida archivo de comandos
set serveroutput on;

declare cursor c1
is
    select country_id, country_name from countries;
    v_id countries.country_id%type;
    v_nom countries.country_name%type;
begin
  open c1;
  dbms_output.put_line('ID' || ' ' || 'Nombre');
  loop
    FETCH c1 into v_id, v_nom;
    exit when c1%notfound;
    dbms_output.put_line(v_id || ' ' || v_nom);  
  end loop;
  close c1;
end;

/*PROCEDURE CON CURSOR*/
create or replace procedure usp_buscar_region_x_pais(reg_id in number)
is
begin
  declare cursor c2
  is
    select country_id, country_name from countries
    where region_id = reg_id;
    v_id countries.country_id%type;
    v_nom countries.country_name%type;
  begin
    open c2;
    loop
      fetch c2 into v_id, v_nom;
      exit when c2%notfound;
      dbms_output.put_line(v_id || ' ' || v_nom);
    end loop;
    close c2;
  end;
end;
--EJECUTAR PROCEDURE
begin
  usp_buscar_region_x_…