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..

CodePudding user response：

With sample data like below:
Table CUSTOMERS

Table ORDERS

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...