如何在单条查询/PL/SQL存储过程中关联变量批量更新多行数据

Hey there! Let's walk through fixing your current PL/SQL code and then show you how to do this with a single query (which you asked for) to associate your ID and ProductNo pairs.

First, your original PL/SQL has a few issues that would prevent it from working correctly:

• You're initializing your VARRAYs with comma-separated strings instead of individual elements (so it's treating "1319180455,1319182309" as one single ID value, not two separate ones)
• The total variable isn't defined—you should use the array's count property instead
• Using IN for single values is unnecessary; just use = instead

Fixed PL/SQL Procedure #

DECLARE
  TYPE ordersIDarray IS VARRAY(5) OF VARCHAR2(10);
  TYPE simArray IS VARRAY(5) OF VARCHAR2(20);
  orders ordersIDarray;
  simNo simArray;
BEGIN
  -- Initialize arrays with individual elements (one per row to update)
  orders := ordersIDarray('1319180455', '1319182309');
  simNo := simArray('8948061100060064024', '8948061100060064055');
  
  -- Loop through the array using its count property
  FOR i IN 1 .. orders.count LOOP
    UPDATE item_product 
    SET product_no = simNo(i) 
    WHERE id = orders(i);
  END LOOP;
  COMMIT; -- Don't forget to commit your changes!
END;
/

Single Query Solution (Using MERGE) #

If you want to do this in a single query (associating your ID and ProductNo pairs directly), the MERGE statement is perfect for this. You can convert your variable pairs into a dataset and join it with your target table:

DECLARE
  -- Define a record type to hold ID and ProductNo pairs
  TYPE id_product_rec IS RECORD(
    item_id VARCHAR2(10),
    product_number VARCHAR2(20)
  );
  -- Define a table type for multiple records
  TYPE id_product_tab IS TABLE OF id_product_rec;
  -- Initialize your update list
  update_list id_product_tab;
BEGIN
  update_list := id_product_tab(
    ('1319180455', '8948061100060064024'),
    ('1319182309', '8948061100060064055')
  );
  
  -- Single MERGE statement to update all rows at once
  MERGE INTO item_product tgt
  USING (
    SELECT item_id, product_number 
    FROM TABLE(update_list)
  ) src
  ON (tgt.id = src.item_id)
  WHEN MATCHED THEN
    UPDATE SET tgt.product_no = src.product_number;
    
  COMMIT;
END;
/

This approach has a few key benefits:

• It uses a single SQL operation, which reduces context-switching overhead between PL/SQL and SQL
• It's easier to maintain—just add new pairs to the update_list when you need to update more rows
• You can easily add validation (like filtering out IDs that don't exist in item_product) by modifying the USING clause

If you prefer a pure SQL solution (no PL/SQL), you can also construct the dataset using CONNECT BY:

MERGE INTO item_product tgt
USING (
  SELECT 
    CASE level 
      WHEN 1 THEN '1319180455' 
      WHEN 2 THEN '1319182309' 
    END AS item_id,
    CASE level 
      WHEN 1 THEN '8948061100060064024' 
      WHEN 2 THEN '8948061100060064055' 
    END AS product_number
  FROM dual
  CONNECT BY level <= 2 -- Match this to the number of rows you're updating
) src
ON (tgt.id = src.item_id)
WHEN MATCHED THEN
  UPDATE SET tgt.product_no = src.product_number;
COMMIT;

内容的提问来源于stack exchange，提问作者truegunner