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);

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;

Answer1:

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; /

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Fetch subquery value from cursor to parameter in PL/SQL