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

Q:

14-23.

You can’t simply add a second argument with a default value in this case; the earlier calls to calc.totals in the Oracle Forms applications don’t compile, because they haven’t passed values for all the arguments. You must instead rely on overloading:

PACKAGE calc
IS
   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE);

   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE,
      section_id_in IN INTEGER);
END calc;

Inside an Oracle Forms application, a developer can now call calc.totals in one of two ways, as shown in these examples:

calc.totals (:emp.deptno);
calc.totals (my_dept_var, :sections.section_id);

Existing calls to calc.totals do not need to be changed; new calls to the calc.totals procedure providing a section will unambiguously be routed to the “second” implementation of the totals procedure.

Q:

14-24.

To remove the redundancies, you can use a local procedure (defined in the declaration section) to centralize all common code elements:

PROCEDURE calc_percentages (total_in IN NUMBER)
IS
   FUNCTION formatted_pct  (val_in IN NUMBER)
      RETURN VARCHAR2 IS
   BEGIN
      RETURN TO_CHAR ((val_in/total_in) * 100, '$999,999');
   END;
BEGIN
   food_sales_stg := formatted_pct  (sales.food_sales);
   service_sales_stg := formatted_pct  (sales.service_sales);
   toy_sales_stg := formatted_pct  (sales.toy_sales);
END;

Q:

14-25.

This is a poorly designed function for a number of reasons:

  • The function returns two different pieces of information: the company name as the RETURN value ...

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