CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Geralt
Geralt

Reputation: 180

Oracle PL/SQL - Receive a list of values as IN parameter, use them in the WHERE clause and return the results in a procedure

I need to create an Oracle procedure that receives a list of values, let's say Customer IDs, and uses those IDs in the WHERE clause of a SELECT statetement searching for all those Customer IDs in a table. The resulting columns and rows of this table must be returned by the procedure to the caller. The quantity of Customer IDs is variable.

I imagine I should use cursors for this and the procedure would be declared similarly to this: CREATE PROCEDURE (c_customers IN SYS_REFCURSOR, c_results OUT sys_refcursor) IS BEGIN ... but I don’t know how to manipulate those cursors.

Upvotes: 0

Views: 2103

Answers (2)

hotfix
hotfix

Reputation: 3396

an alternative solution is to use the collections:

First you have to create a global collection as a type

CREATE OR REPLACE TYPE tab_number is TABLE OF NUMBER;

you can then use it as a type of your arguments in your procedure

CREATE PROCEDURE YOUR_PROC_NAME (
    in_id_list IN            tab_number ,
    C_RESULTS     OUT           SYS_REFCURSOR
) IS
BEGIN
    OPEN C_RESULTS FOR 
    SELECT *
      FROM YOUR_TABLE
     WHERE YOUR_COLUMN IN (
              SELECT column_value from table(in_id_list)
       );

END;

Upvotes: 0

Popeye
Popeye

Reputation: 35900

I think you need to take customer ids as string input.

see if the following can help:

CREATE PROCEDURE YOUR_PROC_NAME (
    C_CUSTOMERS   IN            VARCHAR2,
    C_RESULTS     OUT           SYS_REFCURSOR
) IS
BEGIN
    OPEN C_RESULTS FOR 
    SELECT *
      FROM YOUR_TABLE
     WHERE YOUR_COLUMN IN (
              SELECT REGEXP_SUBSTR(C_CUSTOMERS, '[^,]+', 1, LEVEL)
                FROM DUAL
          CONNECT BY REGEXP_SUBSTR('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, LEVEL) IS NOT NULL
       );

END;

Cheers!!

Upvotes: 1

Related Questions