Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page