在PL/SQL中将游标中的子查询值获取到参数 [英] Fetch subquery value from cursor to parameter in PL/SQL

问题描述

我在下面的查询中选择子查询的数量:

I have below query to select the count of subquery:

SELECT COUNT(*) FROM (select crs_cust.CUSTOMER_ID, 
subset.NEW_REFERENCE_ID FROM CRS_CUSTOMERS crs_cust INNER JOIN DAY0_SUBSET subset ON crs_cust.CUSTOMER_ID=subset.CURRENT_CUSTOMER_ID);

以上查询是从PL/SQL中的游标执行的，如何从子查询(CUSTOMER_ID和NEW_REFERENCE_ID)中提取列到各自的参数中?

Above query is executed from a cursor in PL/SQL, how can I fetch columns from subquery (CUSTOMER_ID and NEW_REFERENCE_ID) into respective parameter?

预计光标具有多个记录.类似如下:

The cursor is expected to have multiple records.Similiar like the following:

p_Count := SELECT COUNT(*) FROM DAY0_SUBSET;
OPEN c1; 
LOOP
FETCH c1 into p_Current_CustomerId,p_New_Cust_Ref_ID; -->query from cursor's subquery
EXIT WHEN c1%NOTFOUND;
EXIT WHEN (c1%ROWCOUNT <> p_Count);

FOR i IN c1 LOOP
<do manipulation of subquery values>
END LOOP;
END IF;
CLOSE c1;  

推荐答案

子查询的列未投影，因此无法引用它们.如果要在程序中使用CUSTOMER_ID和NEW_REFERENCE_ID，则必须在顶级SELECT子句中选择它们. 解决方案最简单的答案是为您的设备打开一个光标 子查询本身，例如:

The columns of sub queries are not projected so you can't reference them. If you want the CUSTOMER_ID and NEW_REFERENCE_ID in your program you will have to select them in the top level SELECT clause. The easiest answer to your solution is to just open a cursor for your subquery itself, for eample:

BEGIN
  FOR cur IN (SELECT crs_cust.CUSTOMER_ID, subset.NEW_REFERENCE_ID
               FROM CRS_CUSTOMERS crs_cust
                INNER JOIN DAY0_SUBSET subset ON 
                  crs_cust.CUSTOMER_ID=subset.CURRENT_CUSTOMER_ID)
  LOOP
     DBMS_OUTPUT.PUT_LINE(cur.customer_id || ', ' || cur.new_reference_id);
  END LOOP;
END;
/

这篇关于在PL/SQL中将游标中的子查询值获取到参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！