Oracle procedure return query rows

Question

Oracle procedure return query rows

Navigation

#1 by (2 votes)

2

I have the following table:

CREATE TABLE CARRO (
    CD_CARRO INT
    ,DONO    VARCHAR(20) 
    ,MARCA   VARCHAR2(20)
);

The inserts

INSERT INTO CARRO VALUES (1,'VICENTE','FERRARI');
INSERT INTO CARRO VALUES (2,'CARLOS','FUSCA');
INSERT INTO CARRO VALUES (3,'MAIZA','DOBLO');
INSERT INTO CARRO VALUES (4,'FERNANDA','FUSCA');
INSERT INTO CARRO VALUES (5,'BRUNA','FERRARI');

I have a query:

SELECT * FROM carro WHERE marca = 'volks';

I would like, from a procedure, to return the above query

CREATE PROCEDURE prc_carros (dsmarca VARCHAR2) IS
BEGIN
   SELECT * FROM carro WHERE marca = dsmarca;
END;

I would like to return in procedure oracle the result as if it were a normal table

oracle procedure select

asked by anonymous 29.03.2017 / 15:26

1 answer

How do I return the value of a query in nodejs How do I check with nodejs, if a table is unregistered?

score 2 · Accepted Answer

In Oracle you can not return a select directly through a procedure , what you can do is return a cursor in procedure or create a function and use it as if it were a table.

Using Function:

First you need to create your own return type with all the columns you want to return

CREATE OR REPLACE TYPE OUTPUT_CARRO_TYPE
IS OBJECT (
    MARCA VARCHAR(20),
    DONO VARCHAR2(20)
);

CREATE OR REPLACE TYPE OUTPUT_CARRO
AS TABLE OF OUTPUT_CARRO_TYPE

Then you can create your Function using this type of return you just created.

CREATE OR REPLACE FUNCTION fn_carros (dsmarca VARCHAR2)
RETURN OUTPUT_CARRO PIPELINED
IS            
BEGIN
    FOR RECORD_OUTPUT IN (
        SELECT * FROM carro WHERE marca = dsmarca
    )
    LOOP
        PIPE ROW (OUTPUT_CARRO_TYPE(RECORD_OUTPUT.MARCA, RECORD_OUTPUT.DONO));
    END LOOP;                   
END;

You can use Function as follows:

SELECT * FROM TABLE(fn_carros('BMW'));

Using Procedure and cursor

First you create your procedure with a variable out that will be your cursor.

create procedure prc_carros (dsmarca VARCHAR2, prc out sys_refcursor)
is
begin
    open prc for SELECT * FROM carro WHERE marca = dsmarca;
end;

To check your result for example you can use the following command:

var rc refcursor;
execute myproc('BMW', :rc);

print rc;