Untitled

-- 5
create or replace procedure creditreport
as
    avgCredit NUMBER(8,2); 
    maxCredit NUMBER(8,2);
    minCredit NUMBER(8,2);
    
    avg_count NUMBER(10,0); 
    high_count NUMBER(10,0);
    low_count NUMBER(10,0);
begin
    SELECT AVG(credit_limit) INTO avgCredit FROM customers;
    SELECT MAX(credit_limit) INTO maxCredit FROM customers;
    SELECT MIN(credit_limit) INTO minCredit FROM customers;
    
    SELECT COUNT(*) INTO avg_count 
    FROM customers 
    WHERE credit_limit >= (avgCredit + minCredit) / 2 
    AND credit_limit <= (avgCredit + maxCredit) / 2;
    
    SELECT COUNT(*) 
    INTO high_count 
    FROM customers 
    WHERE credit_limit < (avgCredit + maxCredit) / 2;
    
    SELECT COUNT(*) 
    INTO low_count 
    FROM customers 
    WHERE credit_limit < (avgCredit + minCredit) / 2;
    
    DBMS_OUTPUT.PUT_LINE ('The number of customers with average credit limit: ' || avg_count);
    DBMS_OUTPUT.PUT_LINE ('The number of customers with high credit limit: ' || high_count);
    DBMS_OUTPUT.PUT_LINE ('The number of customers with low credit limit: ' || low_count);
exception
when others
    then
        DBMS_OUTPUT.PUT_LINE ('Error getting credit limit data!');
end;
/
begin
  creditreport ();
end;
/