Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

A Simple Example

Earlier, I mentioned the idea of querying a table function with a SELECT statement. Here is an example.

    SELECT *
      FROM TABLE(company_balance_sheet);

At first glance, this might look like just another query, but note that company_balance_sheet is a function. Let’s suppose here that it is a function that can peruse millions of lengthy accounting records from potential acquisitions to see how they would affect a parent company’s bottom line. The large amount of data and stringent accounting rules may seem best suited to a program all their own, but what if the results must be available via a simple query from a web page? Table functions to the rescue...

Here is an example of a table function embedded in PL/SQL. Notice that here it is used just like any other cursor. However, this function might be scanning mountains of detailed sales transactions to summarize them in real time by region to enable managers to make decisions toward make-or-break sales quotas.

    DECLARE
      CURSOR curs_get_western_sales IS
      SELECT *
        FROM TABLE(total_sales_by_region)
       WHERE region = 'Western';
      v_western_sales NUMBER;
    BEGIN
      OPEN curs_get_western_sales;
      FETCH curs_western_sales INTO v_western_sales;
      CLOSE curs_get_western_sales;
    END;

This example shows how a table function can accept parameters that may be used to control business processing. In the next example, the function examines reams of intricate test samples looking for anomalies before presenting results that must be accurate for real-time ...

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 PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page