stored procedure using cursor to return multiple query results

Question

I am trying to return multiple query results.. like this:

(list up every customers and order records per country-user input)
Customer key: 6745, Customer#000006745
Total number of orders made: 33
(and list up every orders:)
order date order key order price
xxx.       xxx.       xxx

the result should look like this, and I was told that I am supposed to use cursor to do this.

create or replace procedure orderbuyer(country varchar2) as
cursor c_sr is
select key, name, count(*) from customer join orders on ...
group by key, name //the customer info
begin
open c_sr 
for c in c_sr ...

end;

My query looks like this, but as I am tasked to return a table of order transactions too, im kinda confused..

d r · Accepted Answer · 2022-11-04 18:35:04Z

With sample data like below:
Table CUSTOMERS

CUST_ID	CUST_NAME
000006745	BuyItAll Ltd
000004325	Father And Sons Co.
000009511	Plan B Inc

Table ORDERS

ORDER_DATE	CUST_ID	ORDER_NO	PROD_ID	QUANTITY	PRICE
26-OCT-22	000006745	6746	145	7	17.42
27-OCT-22	000006745	6747	141	5	20.58
28-OCT-22	000006745	6748	119	9	12.08
29-OCT-22	000006745	6749	170	13	14.55
30-OCT-22	000006745	6750	148	4	19.04
31-OCT-22	000006745	6751	120	10	12.48
01-NOV-22	000006745	6752	184	7	16.84
02-NOV-22	000006745	6753	115	14	15.28
03-NOV-22	000006745	6754	182	1	18.89
01-NOV-22	000004325	4326	174	3	23.51
02-NOV-22	000004325	4327	145	12	20.85
03-NOV-22	000004325	4328	175	6	17.02
28-OCT-22	000009511	9512	134	10	18.41
29-OCT-22	000009511	9513	127	13	17.66
30-OCT-22	000009511	9514	168	9	14.17
31-OCT-22	000009511	9515	178	3	22.76
01-NOV-22	000009511	9516	178	5	22.19
02-NOV-22	000009511	9517	129	9	22.03
03-NOV-22	000009511	9518	164	4	17.66

Here is the code that uses two cursors to get you your report. You should adjust it to your needs, but the construct is as below:

SET SERVEROUTPUT ON 
Declare
      CURSOR cust IS
        SELECT CUST_ID, CUST_NAME 
        FROM customers
        ORDER BY CUST_ID;
    custSet cust%ROWTYPE;
    --
    CURSOR ord IS
        Select c.CUST_ID, c.CUST_NAME, o.ORDER_DATE, o.ORDER_NO, o.QUANTITY, o.PRICE, Count(*) OVER(Partition By o.CUST_ID Order By o.CUST_ID) "CUST_ORDERS"
        From customers c Inner Join orders o ON(o.CUST_ID = c.CUST_ID)
        Where c.CUST_ID = custSet.CUST_ID
        Order By c.CUST_ID;
    ordSet  ord%ROWTYPE;
    --
    i       Number(3) := 0;
Begin
    OPEN cust;
    LOOP
        FETCH cust InTo custSet;
        EXIT WHEN cust%NOTFOUND;
        OPEN ord;
        LOOP
            FETCH ord Into ordSet;
            EXIT WHEN ord%NOTFOUND;
            i := i + 1;
            If i = 1 Then
                DBMS_OUTPUT.PUT_LINE(' ');
                DBMS_OUTPUT.PUT_LINE('Customer Key: ' || To_Number(custSet.CUST_ID) || ',   -   Customer#' || custSet.CUST_ID);
                DBMS_OUTPUT.PUT_LINE('Total number of orders made: ' || ordSet.CUST_ORDERS);
                DBMS_OUTPUT.PUT_LINE(RPAD('Order Date', 15, ' ') ||  '  ' || RPAD('Order ID', 10, ' ') || '  ' || LPAD('Order Price', 15, ' '));
                DBMS_OUTPUT.PUT_LINE(RPAD('-', 15, '-') ||  '  ' || RPAD('-', 10, '-') || '  ' || LPAD('-', 15, '-'));
            End If;
            DBMS_OUTPUT.PUT_LINE(RPAD(ordSet.ORDER_DATE, 15, ' ') ||  '  ' || RPAD(ordSet.ORDER_NO, 10, ' ') || '  ' || LPAD(ordSet.PRICE, 15, ' '));
        END LOOP;
        CLOSE ord;
        i := 0;
        --
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
    CLOSE cust;
End;

... the output is:

/*
anonymous block completed
 
Customer Key: 4325,   -   Customer#000004325
Total number of orders made: 3
Order Date       Order ID        Order Price
---------------  ----------  ---------------
01-NOV-22        4326                  23.51
02-NOV-22        4327                  20.85
03-NOV-22        4328                  17.02

 
Customer Key: 6745,   -   Customer#000006745
Total number of orders made: 9
Order Date       Order ID        Order Price
---------------  ----------  ---------------
26-OCT-22        6746                  17.42
27-OCT-22        6747                  20.58
28-OCT-22        6748                  12.08
29-OCT-22        6749                  14.55
30-OCT-22        6750                  19.04
31-OCT-22        6751                  12.48
01-NOV-22        6752                  16.84
02-NOV-22        6753                  15.28
03-NOV-22        6754                  18.89

 
Customer Key: 9511,   -   Customer#000009511
Total number of orders made: 7
Order Date       Order ID        Order Price
---------------  ----------  ---------------
28-OCT-22        9512                  18.41
29-OCT-22        9513                  17.66
30-OCT-22        9514                  14.17
31-OCT-22        9515                  22.76
01-NOV-22        9516                  22.19
02-NOV-22        9517                  22.03
03-NOV-22        9518                  17.66
*/

Regards...

Collectives™ on Stack Overflow

stored procedure using cursor to return multiple query results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related