Untitled

set serveroutput on;

-- 1
create or replace procedure capitalize (input IN varchar)

as

begin
    
  DBMS_OUTPUT.PUT_LINE (upper(input) || ' has ' || LENGTH(input) || ' characters.');  

end;

begin
    capitalize ('Te$t');
end;
/
-- 2
create or replace procedure get_work_years (employeeID in number)
as
    emp_year number;
begin
    SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) INTO emp_year 
        FROM employees 
        WHERE employee_id = employeeID;
    DBMS_OUTPUT.PUT_LINE ('The employee with ID '||employeeID|| ' has worked '||emp_year|| ' years.');  
end;
/
begin
    get_work_years (82);
end;

/
-- 3
create or replace procedure find_product (productID in number)
as
    productName VARCHAR2(255 BYTE);
    listPrice NUMBER(9,2);
    categoryName VARCHAR2(255 BYTE);    
begin
    SELECT product_name, list_price, category_name
        INTO productName, listPrice, categoryName
        FROM products p
        JOIN product_categories pc ON p.category_id = pc.category_id
        WHERE product_id = productID;
    DBMS_OUTPUT.PUT_LINE ('Product name: '|| productName);  
    DBMS_OUTPUT.PUT_LINE ('List price: '|| listPrice); 
    DBMS_OUTPUT.PUT_LINE ('Category name: '|| categoryName); 
end;
/
begin
    find_product (132);
end;
/
--4
create or replace procedure newprice (categoryID in number, amount in number)
as
    average NUMBER(9,2);
begin
    SELECT AVG(list_price) INTO average 
        FROM new_products 
        WHERE category_id = categoryID;
    if (average < amount) then
        UPDATE new_products
            SET list_price = list_price * 1.02
            WHERE category_id = categoryID;
        DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' rows are updated.');
    else
        DBMS_OUTPUT.PUT_LINE ('Average of 1 is ' || average || ', which is no lower than ' || amount || '. We won''t update the price!');
    end if;
end;
/
BEGIN
  newprice(2, 10000);
  newprice(1, 500);
END;
/
-- 5
create or replace procedure creditreport
as
    avgCredit NUMBER(8,2); 
    maxCredit NUMBER(8,2);
    minCredit NUMBER(8,2);
    
    avg_count NUMBER(6,0); 
    high_count NUMBER(6,0);
    low_count NUMBER(6,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);
end;
/
BEGIN
  creditreport ();
END;
/