如果一个参数在单个参数的函数中具有多个值，如何从pl/sql函数获取数据

本文介绍了如果一个参数在单个参数的函数中具有多个值，如何从pl/sql函数获取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在用pl/sql开发一个过程，该过程从函数中执行的游标获取数据集.例如:

I am developing a procedure in pl/sql that get a data set from a cursor executed in a function. For example:

功能:

f_process_data(id_process IN NUMBER, id_product IN NUMBER)

返回:

v_result_cursor sys_refcursor;

但是问题是在搜索游标时，我需要一次发送多个id_product.像这样:

But the problem is that in the search of the cursor I need to send at time more than one id_product. Something like this:

id_product:1240(香肠) id_product:1260(火腿)

id_product: 1240 (sausages) id_product: 1260 (ham)

¿该如何发送(或获取)该功能中的多个产品?

¿How can I send (or get) more than one product in the function?

我知道循环是可能的，但是怎么办?

I understood that it's possible with a loop, but how?

类似这样的事情??

v_sausage := 1240; v_ham := 1260; LOOP IF v_count = v_sausage OR v_count = v_ham THEN v_result_cursor := f_process_data(1, p_id_product); END IF; FETCH v_result_cursor INTO v_id, v_id_product; EXIT WHEN v_result_cursor%NOTFOUND; END LOOP;

我希望v_id和v_id_product已记录在一个集合中，其中包括id_product 1240和1260的结果.

I expected that v_id and v_id_product has been recorded in a collection including the results from the id_product 1240 and 1260.

但是，如果业务规则发生变化并且不仅是2种产品，那么100或1000种产品又如何呢?

But, if the business rules changes and is not only 2 products, how about 100 or 1000 products?

注意:无法修改该函数中的游标，id_product必须一一发送.

Note: the cursor from the function it's not possible to modify, the id_product must be sent one by one.

CURSOR cu_get_value_products IS SELECT value FROM supplies WHERE id = p_id and id_product = p_id_product;

推荐答案

以下是我想出的一些有限的信息.我仍然不知道您在问题中提到的STATIC光标是在f_process_data()函数中定义的.由于我不知道该函数的完整代码，因此我只写了自己的代码，并将CURSOR声明为SYS_REFCURSOR，因为这是该函数返回的内容.

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 IS TABLE OF 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 FOR SELECT * 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 THEN CLOSE v_result_cursor; END;