Oracle PL/SQL Programming: A Developer's Workbook

Expert

14-23.	Suppose that five years ago, a developer wrote a packaged procedure with the following header: PACKAGE calc IS PROCEDURE totals ( department_id_in IN department.department_id%TYPE); END calc; There are many calls to this procedure (stored in the server database) in a variety of production applications written in Oracle Forms. Client-side Oracle Forms PL/SQL code does not recognize default values in parameters lists of stored code. A new requirement has now been requested by users to be able to calculate totals per department and also for sections within departments. How would you change the implementation of the calc package to satisfy these requirements?
14-24.	Improve the following procedure: PROCEDURE calc_percentages (total_in IN NUMBER) IS BEGIN food_sales_stg := TO_CHAR ((sales.food_sales / total_in) * 100, '$999,999'); service_sales_stg := TO_CHAR ((sales.service_sales / total_in) * 100, '$999,999'); toy_sales_stg := TO_CHAR ((sales.toy_sales / total_in) * 100, '$999,999'); END;
14-25.	Improve the structure, performance, and functionality of the following function: FUNCTION company_name ( company_id_in IN company.company_id%TYPE, industry_type_out OUT VARCHAR2 ) RETURN VARCHAR2 IS cname company.company_id%TYPE; found_it EXCEPTION; BEGIN SELECT name, industry_type INTO cname, industry_type_out FROM company WHERE company_id = company_id_in; RAISE found_it; EXCEPTION WHEN found_it THEN RETURN cname; END;
14-26.	The remainder of this chapter is a case study of a function published ...