Untitled

/*
1. Write a stored procedure to calculate the total sales amount for customers with more than four orders and display the results using a cursor.
*/
create or replace procedure calc_sales
as
    o_amount number(10,2);
    c_name customers.name%type;
    
    cursor c_cursor is
        select name, sum(quantity*unit_price) amt
        from orders o
        left join order_items oi on o.order_id = oi.order_id
        left join customers c on o.customer_id = c.customer_id
        where o.customer_id in
            (select customer_id
            from orders
            group by customer_id
            having count(customer_id) > 4)
        group by o.customer_id, name
        order by o.customer_id;
begin
    open c_cursor;
    loop
        fetch c_cursor into c_name, o_amount;
        exit when c_cursor%notfound;
        dbms_output.put_line(c_name || ' ' || o_amount);
    end loop;
    close c_cursor;
end;
/
begin
  calc_sales ();
end;
/

/*
    Write a stored procedure using a cursor to display the warehouse IDs and their corresponding locations (City, State, and country ID) for all warehouses.
*/
create or replace procedure calc_warehouse
as
    w_warehouse_id warehouses.warehouse_id%type;
    l_city locations.city%type;
    l_state locations.state%type;
    l_country_id locations.country_id%type;
    
    cursor w_cursor is
        select warehouse_id, city, state, country_id 
        from warehouses w
        inner join locations lo on w.location_id = lo.location_id;
begin
    open w_cursor;
    loop
        fetch w_cursor into w_warehouse_id, l_city, l_state, l_country_id;
        exit when w_cursor%notfound;
        dbms_output.put_line(w_warehouse_id || ' ' || l_city || ' ' || l_state || ' ' || l_country_id);
    end loop;
    close w_cursor;
end;
/
begin
  calc_warehouse ();
end;
/

/*
    The statement checks if there are no more rows in the cursor's result of our query. 
    When all rows have been fetched, %NOTFOUND becomes true, and the loop exits.
    Without this exit condition, the loop would continue even if there are no more rows causing sql developer to hang or cause an error.
*/