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; /
Leave a Comment