Foros del Web - Procedimiento Almacenado

Foros del Web (http://www.forosdelweb.com/)

- Oracle (http://www.forosdelweb.com/f100/)

- - Procedimiento Almacenado (http://www.forosdelweb.com/f100/procedimiento-almacenado-547132/)

sigfrid

10/01/2008 15:21

Procedimiento Almacenado

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

10/01/2008 16:30

Re: Procedimiento Almacenado

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/

Cita:

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

10/01/2008 16:53

Re: Procedimiento Almacenado

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

10/01/2008 17:25

Re: Procedimiento Almacenado

Hola,

Cita:

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

11/01/2008 10:51

Re: Procedimiento Almacenado

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

sigfrid

11/01/2008 16:26

Re: Procedimiento Almacenado

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

11/01/2008 17:02

Re: Procedimiento Almacenado

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

11/01/2008 19:17

Re: Procedimiento Almacenado

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

La zona horaria es GMT -6. Ahora son las 01:54.