Procedure Oracle return query lines

Question

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 an appointment:

SELECT * FROM carro WHERE marca = 'volks';

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

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

I would like you to return the result as if it were a normal table

Answer 1

In the Oracle you can’t return a select directly through a Procedure, what you can do is return a cursor to Procedure or create a Function and use it as 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 feedback that you have 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 the Function as follows:

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

Using Procedure and Cursor

First you create your Procedure with an out variable 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;