Cursores Externos (Ref Cursor)

En algunos desarrollos necesitamos que una consulta (Cursor) sea utilizada en más de un procedimiento o función, para esto realizamos un refCursor,  esto es una función la cual retorna una consulta por medio del tipo de variable SYS_REFCURSOR.

Ejemplo: Se necesita retornar los valores que contiene la tabla dummy utilizando un refCursor.

--Creación de la función que contiene el select

create or replace function prueba_ref_cursor return SYS_REFCURSOR is

      cursorRetornado SYS_REFCURSOR;

begin

   open cursorRetornado for --Abre la consulta

   select dummy

   from dual;

   return cursorRetornado; --retorna la consulta

end prueba_ref_cursor;

En este bloque anónimo se realiza el llamado a al refCursor, para esto se utilizan las instrucciones del cursor Fetch, Into y Close, la instrucción Open se ejecuta en el refCursor.

--Llamado de la funcion refCursor para ejecutar el paquete.

declare

  tipo_Cursor  sys_refcursor := null;

  valor_cursor varchar2(100);

begin

  tipo_Cursor := prueba_ref_cursor();--Open cursor

  LOOP --Inicia el llop

   

    FETCH tipo_Cursor INTO valor_cursor; --Pasa los valores a valor_cursor

    EXIT WHEN tipo_Cursor%NOTFOUND; --Indica la salida del loop

   

    DBMS_OUTPUT.PUT_LINE(valor_cursor); --Imprime el valor

 

  END LOOP; --Fin del loop

  close tipo_Cursor; --Se cierra el cursor

end;

Output:

 

Guardar los cambios en la base de datos (Commit).

Commit:

Cuando en Oracle modificamos los datos, realizando un update insert o delete y necesitamos guardar esos cambios para que otros usuarios puedan observarlos, realizamos un commit, esto le indica a la tabla que estos cambios serán permanentes.

Autocommit:

Cuando en Oracle realizamos un cambio a la estructura de un objeto de la base de datos, realizando un alter, create, drop etc.. No será necesario hacer un commit ya que estos procesos automáticamente realizan un commit, es decir si realizo un insert y luego creo una tabla sin necesidad de hacer commit se guardara el insert realizado porque el create automáticamente hace commit.

Ejemplo:

1-      Se solicita crear la tabla persona

2-      Inserte 3 registros en la tabla persona, no hacer commit

3-      Agregue una columna a la tabla

4-      Consulte los 3 registros ingresados en otra sesión.

1.

Create table persona(

  Nombre varchar2(200),

  Apellido varchar2(200)

);

2.

Insert into persona values ('Carlos','Gonzalez');

Insert into persona values('Karina','Mina');

Insert into persona values('Angela','Peña');

3.

Alter table persona add fecha_nacimiento date;

4.

Unir dos o más consultas Select (UNION)

Cuando tenemos una consulta que nos genera unos registros dependiendo las condiciones y el origen de los datos (Tablas) y necesitamos unir esos registros con otros registros, que se generan de una consulta parecida a la cual se le ha cambiado alguna condición o el origen de los datos (Tablas), utilizamos la función UNION  la cual permite unir consultas.

Para usar la función UNION se deben tener la misma cantidad de columnas y el mismo tipo de dato.

Sentencia Base:

select 'A' Salida

  from dual

union

select 'E' Salida

  from dual;

Output:

 

Nota: Si los registros están duplicados los quita.

Por ejemplo tenemos la tabla datos la cual contiene 3 columnas con los siguientes datos.

  

Se tienen datos repetidos.

Realizamos dos consultas una que nos traiga los registros cuyo PAIS sea Colombia, y el otro que traiga todos los países diferentes a Colombia.

Consulta 1: El país Colombia

Consulta 2: Los países diferentes a Colombia.

Unir los registros de las dos consultas utilizando la función UNION.

select *

  from datos

 where pais = 'Colombia'

 union

select *

  from datos

 where pais != 'Colombia' ;

Output:

 

La función UNION removió de la consulta los valores duplicados que pertenecían al país Colombia