Al igual que ésta semana anotaba cómo hacer consultas con Limit en Sql Server 2005, en éste post le toca el turno a Oracle.
La forma de paginar resultados en Oracle, al igual que se hace con un Limit en mySQL, es la siguiente:
SELECT * FROM ( SELECT TABLA.* , ROWNUM AS FILAS FROM TABLA ) WHERE FILAS BETWEEN 5 AND 10
Ésta consulta devuelve los resultados del 5º encontrado al 10º
Ésta entrada se la dedico a mi ex-compañero y Red Hat Trainer de Ecuador, Washington Luis Villamar Lavayen “Luisao”, que fue quién me chivó ésta consulta hace algo así como un año… cuando trabajabamos juntos ;)
gracias por la ayuda, esto me sirvio mucho, una pregunta, como recupero un set de datos desde un procedimiento almacenado en oracle 10g??
o sea
create procedure xx(param numeric, param2 varchar2)
is
begin
select * from tabla where
campo1=param and
campo2=param2
end xx;
/
en t-sql retorna un set de datos, como lo hago en oracle?
Hola Danilo,
Ya siento no poder responderte ya que actualmente no programo contra bases Oracle y no creo poder darte una respuesta lo suficientemente precisa, que no puedas encontrar tú mismo en google.
Ésta entrada la postee porque en su día me hizo falta y un gran amigo me pasó el truco. Si en un futuro lo vuelvo a necesitar, aquí la tengo yo o cualquiera que lea éste blog.
Un saludo
Danilo, para que puedas recuperar los datos que te devuelve una consulta en oracle es necesario que incluyas en tu store procedure un parametro in out que deberá ser un ref cursor. Este parametro es quien recuperara los datos que filtraste en el sql. Te dejo este ejemplo que me sirvió de mucho: http://support.microsoft.com/kb/322160/es
No es indispensable pasarle el type al parametro de entrada, pero si es muy indispensable incluir el parametro de salida tipo cursor.
Me parece además que este ejemplo es para Oracle 9i. En 10g tendrás que utilizar OracleDbType.RefCursor.
Suerte amigo!
Este método es práctico pero poco efectivo cuando se trata de consultas que utilizan varias tablas (join).
Paginar de esta forma implica que la consulta deba ser ejecutada una y otra vez en su totalidad, creando el Oracle las tablas transitorias necesarias para resolverla, ordenarla, etc. y luego recien tomar la cantidad de filas solicitadas. Esto resulta en una degradación de la base con el consiguiente perjuicio para el resto de las personas que utilizan la misma base.
Algo más efectivo sería utilizar una tabla transitoria para luego solicitar las filas del cursor ya resuelto, o bien, si el lenguaje lo permite, serializar el cursor a XML o JSON y trabajarlo desde el disco.
Esto último tiene la dificultad que estarian utilizando un cursor estático. Pero en el anterior, si la tabla es muy dinámica, nada asegura que las páginas vistas tengan las mismas filas cuando se las vuelva a solicitar, pues si se borran filas, las filas de páginas posteriores estarán en las anteriores.
Danilo: Lo que necesitas en Oracle PL/SQL es muy similar a lo que pusiste, de hecho solo un cambio de sintaxis debiera bastar:
create procedure xx(param in number, param2 in varchar2)
as
begin
select * from tabla where
campo1=param and
campo2=param2
end xx;
/
Ten ojo que esto solo te sirve en la consola SQL o en tu programa de consultas para Oracle como el Oracle SQL Developer o el TOAD For Oracle 9.0 (Muy buen programa! Funciona con varias versiones de oracle tambien!), eso
si es que solo te interesa verlos a ti.
Como ha dicho Hubert, si vas a utilizar ese procedure en algun programa, necesitaras un parametro que te devuelva los datos si es que necesitas manipularlos o recibirlos para consulta, en general solo basta con un cursor…
Estimado Asier, estaba probando tu sugerencia, pero encuentro que si quiero emitir los registros ordenados de una manera determinada, la salida restringida a las filas no da el resultado correcto.
Si pido todas las filas en un orden y luego verifico si al solicitar las filas 5 a 10 son las correspondientes al listado general, esto no pasa.
Desde ya muchas gracias por tu tiempo.
Marcelo (desde Arg)
Pingback: Paginar resultados en SQL Server 2000, al estilo Limit de mySQL
Para mi no funciono como esperaba pero si quieren una explicación de un experto de Oracle sigan esta liga
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Esto debe resolver el problema de ordenamiento:
select * from ( select a.*, rownum rnum from (
SELECT campo1, campo2 FROM tabla1 WHERE campo1 = valor ORDER BY campo2
) a where rownum = firstElement;
Saludos