Oracle PL/SQL for DBAs

Using Table Functions

In this section, I’ll show the use of table functions in a real-world example based on a large cable company’s attempt to track repeat orders. Simply put, a repeat order occurs when a technician is dispatched to a location more than once within a 30-day period to perform the same type of work or to repair the original work. A repeat visit doesn’t always have a negative connotation—it could result from an installation occurring within 30 days of a pre-sales call.

Locations are identified by preassigned ID values. These can map to anything from a specific neighborhood cable outlet to a house or a large shopping mall. The type of work is identified using order type IDs. For example, type 1 might be “Cable Installation,” and type 2 might be “Cable Upgrade.”

The company is divided into several regions, each with its own set of location codes, order types, and repeat-order criteria. These criteria define sets of two order types that must occur at the same location within 30 days of each other to be considered a repeat order. The two order types may or may not be the same; for example, a “Cable Installation” followed by a subsequent “Cable Repair” might be deemed a repeat order in the same way that two “Cable Installations” would.

The criteria are held in this table:

    SQL> DESC repeat_order_criteria
     Name                                      Null?    Type
     ----------------------------------------- -------- ------
     REGION_ID                                          NUMBER
     START_DATE                                         DATE
     FIRST_TYPE_ID                                      NUMBER
     REPEAT_TYPE_ID                                     NUMBER

The table holds each region’s ...