Return multiple rows from a stored procedure

Question

How do I create a stored procedure which can return multiple rows using SQL Developer BTW.?

Right now stored procedure returns the value for 1 row in 4 diff variables (there are 4 cols)

How I would go about making it so that it could return more than 1 row, for example if i were to query in my date it could return all the relevant data for that date instead of only 1.

create or replace PROCEDURE P2
(
    ts IN TIMESTAMP,
    u_id OUT VARCHAR2,
    u_email OUT VARCHAR2,
    cmnt OUT VARCHAR2
)
AS
BEGIN
    SELECT U_ID , U_EML, C_TX INTO u_id, u_email, cmnt 
    FROM U_CM 
    WHERE U_CM_TS =  ts;
END;

ts is the input timestamp

if i put in more a timestamp that has multiple rows associated with it i get an error?

How do i change the design so I can be successful in doing what i want? I am new to this so I dont know where to start

Answer 1

Use ref cursor:

create or replace PROCEDURE P2
(
    ts IN TIMESTAMP,
    p_result OUT sys_refcursor
)
AS
BEGIN
    open p_result for
    SELECT U_ID , U_EML, C_TX 
    FROM U_CM 
    WHERE U_CM_TS =  ts;
END;

Also, try to give a more detailed names to columns and tables for more maintainable code. For example, user_id, user_email instead of u_id, u_eml. What is c_tx? I have no idea. Read about table and column naming conventions.