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 ...