How To Get Data From A Pl/sql Function If A Parameter Have More Than One Value In Function With Single Parameters

I am developing a procedure in pl/sql that get a data set from a cursor executed in a function. For example: Function: f_process_data(id_process IN NUMBER, id_product IN NUMBER)

Solution 1:

With somewhat limited information the below is what I've come up with. I still have no clue what is happening with your STATIC Cursor which you mentioned in your question is defined in your f_process_data() Function. Since I don't know the full code in this function I simply wrote my own and declared the CURSOR as SYS_REFCURSOR as that is what the function returns.

Please let me know if this works or if I'm missing some important information. I feel like I'm lacking crucial information in order to provide a useful solution to you.

The mock-up table I created called Products contains the following columns and data. See image.

DECLARE/* Store Ref Cursor returned by f_process_data() Function */
  v_result_cursor   SYS_REFCURSOR;

  /* Declare Record so we can store the data FETCHed from the Cursor */
  rec_products      products%ROWTYPE;

  /* Declare a couple Product Variables for Proof of Concept */
  v_sausage         NUMBER;
  v_ham             NUMBER;

  /* Store output */
  n_id              NUMBER;
  v_id_product      VARCHAR2(100);

  /* Declare Type of TABLE NUMBER */
  TYPE  nt_type ISTABLEOF NUMBER;

  /* Create Array/Table/Collection of type nt_type to store product ids */
  nt_product_ids    nt_type;

  /* Returns a Ref Cursor based on the product_id used as Input to this function */FUNCTION f_process_data(p_id_process IN NUMBER, p_id_product IN NUMBER)
  RETURN SYS_REFCURSOR
  AS/* Declare Ref Cursor that will be Returned */
    rc_result_cursor   SYS_REFCURSOR;    

  BEGIN/* Open Ref Cursor based on Product ID parameter */OPEN rc_result_cursor FORSELECT*FROM products WHERE item_id = p_id_product;

    RETURN rc_result_cursor;

  END f_process_data
  ;

BEGIN/* Set Product Variables to IDs */
  v_sausage       :=2002;
  v_ham           :=2009;

  /* Store product ids into a Number Table so we can Loop thru it */
  nt_product_ids  :=  nt_type (v_sausage,v_ham);

  FOR r IN nt_product_ids.FIRST .. nt_product_ids.LAST
  LOOP
    /* Get Ref Cursor using SINGLE Product ID */
    v_result_cursor := f_process_data(1, nt_product_ids(r));

    LOOP

    FETCH v_result_cursor INTO rec_products;

    n_id            :=  rec_products.item_id;
    v_id_product    :=  rec_products.item;

    EXIT WHEN v_result_cursor%NOTFOUND;

    dbms_output.put_line('Product_id: '|| n_id);
    dbms_output.put_line('Product: '|| v_id_product);  

    END LOOP; /* Cursor Loop *//* Close Cursor */CLOSE v_result_cursor;

  END LOOP; /* Product IDs Loop */

EXCEPTION WHEN OTHERS
  THENCLOSE v_result_cursor;

END;

Solution 2:

In order to send more than one product to be processed at a time, create a user defined Table Type in your database called ProductList. You can then send a Product list into a stored procedure as a parameter.