Procedure: How to allow multiple input parameters. 1

[hallux]

A stored procedure similar to the code below works fine, now there is a need to allow multiple input parameters for the variable destination_in . This will allow the input of more than one destination. Is that permissible and if so, how is it done?

Thanks, Hallux

CREATE OR REPLACE PROCEDURE expedition(destination_in IN VARCHAR2,
p_start_date_in IN DATE,
p_stop_date_in IN DATE
return_curs IN OUT expedition_pkg.expedition_curs) IS
BEGIN
OPEN return_curs FOR
SELECT destination, lastname, firstname, COUNT(DISTINCT exp_tripnum) AS orders
FROM travel
WHERE destination = destination_in
AND s_date = p_start_date_in
AND e_date = p_stop_date_in
END expedition;

 

[lewisp]

You could create a string containing a comma-delimited list of destinations and use that in your ref cursor.

Or you could create a PL/SQL table and pass that is as a parameter. You would have to define the table spec in a package so that the calling and called procedure can reference the table. You could then use a loop to build the ref cursor statement or to loop through each row in the table and call the SQL.

 

[hallux]

Thanks lewisp.
-Hallux