Procedimiento Almacenado

[sigfrid]

Hola que tal, migre algunas tablas del conocido northwind hacia una instancia de oracle para empezar a hacer algunas pruebas.
Estoy creando algunos procedimeintos almacenados que me devuelva un conjunto de filas...
Inicialmente lo hice de la siguiente manera:

Código:

create or replace procedure up_MiProcedure 
is
BEGIN
  SELECT CUSTOMERS.CUSTOMERID, CUSTOMERS.COMPANYNAME

  FROM CUSTOMERS;
end up_MiProcedure; 

pero arrojó un error : se esperaba una clausula INTO en una sentencia SELECT
... bueno luego de inventigar un poco mas llegue a hacerlo de la siguiente manera :

Código:

create or replace procedure up_MiProcedure 
is
  pcustomerid CUSTOMERS.CUSTOMERID%TYPE ; 
  pcompanyname CUSTOMERS.COMPANYNAME%TYPE; 
BEGIN
  SELECT CUSTOMERS.CUSTOMERID, CUSTOMERS.COMPANYNAME
  INTO pcustomerid , pcompanyname
  FROM CUSTOMERS;
end up_MiProcedure;

bueno, no arrojo error pero al ejecutar me da el siguiente mensaje : la recuperación exacta devuelve un numero mayor de filas que el solicitado.

ahora alli va mi pregunta... a que se debe esto...
como y cual es la manera de realizar procedimientos almacenados que devuelvan un conjunto de filas con o sin parametros...

Atentamente.
Gracias
Luis A.

[matanga]

Hola,

En este link se habla un poco sobre la forma de devolver un conjunto de resultados en un procedimiento o funcion.

http://www.forosdelweb.com/f100/inse...do-spl-538309/

bueno, no arrojo error pero al ejecutar me da el siguiente mensaje : la recuperación exacta devuelve un numero mayor de filas que el solicitado.

Este error se debe a que un SELECT INTO tiene que devolver una sola fila, se generara un error si devuelve mas de una o ninguna, la idea de esto es asignar el valor de una fila a una o varias variables. A diferencia de SQL Server, en Oracle, los procedimientos no tienen retorno de un conjunto de resultados, se tiene que hacer a traves del tipo de datos sys_refcursor.

Saludos

[sigfrid]

Ante todo gracias por responder.
Bueno quiza me haya confundido, puesto que me inicie con Ms Sql Server y estoy iniciandome en Oracle.

Lo que estoy tratando de obtener es un conjunto de resultados, bueno entonces me dices que la unica manera de obtener tal resultado es mediante el tipo sys_refcursor. Luego para manejar dicho resultado deberé recorrer tal cursor para recien poder visualizarlo...
Gracias
L.A.

[matanga]

Hola,

Luego para manejar dicho resultado deberé recorrer tal cursor para recien poder visualizarlo...

No necesitas recorrer el sys_refcursor para visualizarlo, es decir, si lo que quieres hacer es ver el conjunto de resultados devuelto por el cursor por razones de debug y simplemente para ver que resultados hay, tienes la siguiente forma:

Creo una tabla de ejemplo y le cargo algunos datos.

Código:

SQL> create table t1 ( id number, name varchar2(20));

Table created.

SQL> insert into t1 values (1,'name 1');

1 row created.

SQL> insert into t1 values (2,'name 2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 name 1
         2 name 2

Un procedimiento que hace un select sobre la tabla y devuelve un sys_refcursor con los datos.

Código:

SQL> create or replace procedure p1
  2  (c1 out sys_refcursor)
  3  as
  4  begin
  5  open c1 for select * from t1;
  6  end;
  7  /

Procedure created.

Finalmente, el codigo para ejecutar el procedimiento y visualizar el resultado en SQL*Plus.

Código:

SQL> variable c1 refcursor;
SQL> execute p1(:c1);

PL/SQL procedure successfully completed.

SQL> print c1;

        ID NAME
---------- --------------------
         1 name 1
         2 name 2

Cada herramienta de base de datos que trabaja con Oracle, ya sea TOAD, SQL Navigator, PL/SQL Developer, SQL*Plus, etc, tiene su propia forma de mostrar el resultado de un sys_refcursor sin necesidad de que te tomes el trabajo de recorrerlo.

Saludos

[sigfrid]

ok. Muchas gracias, ya comprendi.
Me sirvio mucho tu repuesta.
Gracias, y hasta una proxima consulta.
Luis. A.

[sigfrid]

bueno otra ves consultando...

Código:

create or replace procedure prueba2
(q out sys_refcursor) is
begin
  open q for
  select c.* from customers c;
end prueba2; 

He ejecutado el procedimiento de la siguiente manera en Toad for Oracle

Codigo A :

Código:

declare 
    micursor sys_refcursor;
begin
  -- Call the procedure
  prueba2(q => micursor);
end; 

Me dio el mensaje : PL/SQL procedure successfully completed.
Pero no puedo encontrar por ningun lado el conjunto de filas como resultado del select.

Por otro lado, con el PL/SQL Developer ejecuto lo suiguiente :
Codigo B:

Código:

begin
  -- Call the procedure
  prueba2(q => :q);
end; 

y si obtengo el conjunto de resultados.

Mi pregunta es por que cuando trato de ejecutar el codigo A , me arroja el error :
numero/nombre variable no valido

Ademas me podrias explicar por que se coloca el => y el =>: y el :

Gracias de antemano.
Saludos

[matanga]

Hola,

De todo lo que preguntas solo puedo responder a poco :(

Código:

SQL>
  1  create or replace procedure p1
  2  (id number, name varchar2)
  3  as
  4  begin
  5  dbms_output.put_line('id='|| to_char(id) ||' name='||name);
  6* end;
SQL> /

Procedure created.

SQL> begin
  2  p1(id=>1,name=>'name 1');
  3  end;
  4  /
id=1 name=name 1

PL/SQL procedure successfully completed.

SQL> begin
  2  p1(name=>'name1',id=>10);
  3  end;
  4  /
id=10 name=name1

PL/SQL procedure successfully completed.

Los parametros de entrada/salida en un procedimiento o funcion pueden ser interpretados en forma posicional o por nombre, si quieres que en la llamada los parametros sean por nombre lo tienes que indicar con el formato NOMBRE_PARAMETRO=>VALOR.

Tambien tiene otro uso, pero no funcional, sino visual, para orientarnos mejor al momento de leer la llamada a un procedimiento, imagina que tienes 10 parametros de entrada/salida, expresandolo como NOMBRE_PARAMETRO=>VALOR es mas ordenado y nos da una mejor interpretacion cuando leemos el codigo.

Por lo demas, se de hecho que el TOAD puede mostrar el contenido del sys_refcursor, pero hace mucho que no lo utilizo y no recuerdo como se hace, pero conociendo a la gente de QUEST, lo mas probable es que tengas que hacer algo asi como:

1. Boton derecho sobre el nombre del procedimiento.
2. En el menu desplegable, tavlez exista una opcion de Ejecutar o Probar.
3. Aparecera algun tipo de asistente que te guia sobre como completar los valores de los parametros.
4. Al finalizar probablemente te muestre una grilla con los valores del sys_refcursor.

Saludos

[sigfrid]

Ok.
Gracias una vez mas, y tratare de investigar mas acerca de la visualización.
Gracias