首页 > Linux操作系统 > Linux操作系统 > Multi-table insert using pipelined function

Multi-table insert using pipelined function

原创 Linux操作系统 作者:chncaesar 时间:2013-09-16 09:50:53 0 删除 编辑
Thanks to Adrian Billington's work. This article is an extraction of:

one source, two targets
Consider an example of loading customers and addresses from a single file delivery. Let's imagine that a single customer record has up to three addresses stored in his or her history. This means that as many as four records are generated for each customer. For example:

----------- ---------- ---------- ------------------------------ -------
       1060 Kelley          60455 7310 Breathing Street          Y
       1060 Kelley         119885 7310 Breathing Street          N
     103317 Anderson        65045 57 Aguadilla Drive             Y
     103317 Anderson        65518 117 North Union Avenue         N
     103317 Anderson        61112 27 South Las Vegas Boulevard   N
I have removed most of the detail, but this example shows that Kelley has two addresses in the system and Anderson has three. My loading scenario is that I need to add a single record per customer to the customers table, and all of the address records need to be inserted into the addresses table.
Using object-relational features
It is the most elegant solution to this requirement. I first need to create four types to describe my data:
An object “supertype” to head the type hierarchy. This will contain only the attributes that the subtypes need to inherit. In my case, this will be just the customer_id.
A collection type of this supertype. I will use this as the return type for my pipelined function.
A customer object “subtype” with the remaining attributes required for the customers table load.
An address object “subtype” with the remaining attributes required for the addresses table load.
I've picked a small number of attributes for demonstration purposes. My types look like this:
/* File on web: multitype_setup.sql */
-- Supertype...
( customer_id NUMBER

-- Collection of supertype...
CREATE TYPE customer_ntt AS TABLE OF customer_ot;

-- Customer detail subtype...
CREATE TYPE customer_detail_ot UNDER customer_ot
( first_name VARCHAR2(20)
, last_name  VARCHAR2(60)
, birth_date DATE

-- Address detail subtype...
CREATE TYPE address_detail_ot UNDER customer_ot
( address_id     NUMBER
, primary        VARCHAR2(1)
, street_address VARCHAR2(40)
, postal_code    VARCHAR2(10)

If you have never worked with object types, I suggest that you review the contents of Chapter 26. Briefly, however, Oracle's support for substitutability means that I can create rows of either customer_detail_ot or address_detail_ot, and use them wherever the customer_ot supertype is expected. So if I create a pipelined function to pipe a collection of the supertype, this means that I can also pipe rows of either of the subtypes. This is but one example of how an object-oriented type hierarchy can offer a simple and elegant solution.

a multitype pipelined function
Let's take a look at the pipelined function body, and then I'll explain the key concepts.
/* File on web: multitype_setup.sql */
  1     FUNCTION customer_transform_multi(
  2              p_source     IN customer_staging_rct,
  3              p_limit_size IN PLS_INTEGER DEFAULT customer_pkg.c_default_limit
  4              )
  5        RETURN customer_ntt
  6        PIPELINED
  7        PARALLEL_ENABLE (PARTITION p_source BY HASH(customer_id))
  8        ORDER p_source BY (customer_id, address_id) IS
 10        aa_source     customer_staging_aat;
 11        v_customer_id customer_staging.customer_id%TYPE := -1; /* Needs a non-null default */
 13     BEGIN
 14        LOOP
 15           FETCH p_source BULK COLLECT INTO aa_source LIMIT p_limit_size;
 16           EXIT WHEN aa_source.COUNT = 0;
 18           FOR i IN 1 .. aa_source.COUNT LOOP
 20              /* Only pipe the first instance of the customer details... */
 21              IF aa_source(i).customer_id != v_customer_id THEN
 22                 PIPE ROW ( customer_detail_ot( aa_source(i).customer_id,
 23                                                aa_source(i).first_name,
 24                                                aa_source(i).last_name,
 25                                                aa_source(i).birth_date ));
 26              END IF;
 28              PIPE ROW( address_detail_ot( aa_source(i).customer_id,
 29                                           aa_source(i).address_id,
 30                                           aa_source(i).primary,
 31                                           aa_source(i).street_address,
 32                                           aa_source(i).postal_code ));
 34              /* Save customer ID for "control break" logic... */
 35              v_customer_id := aa_source(i).customer_id;
 37           END LOOP;
 38        END LOOP;
 39        CLOSE p_source;
 40        RETURN;
 41     END customer_transform_multi;
This function is parallel-enabled, and it processes the source data in arrays for maximum performance. The main concepts specific to multityping are:
Line(s) Description
5 My function's return is a collection of the customer supertype. This allows me to pipe subtypes instead.
7-8 I have data dependencies so have used hash partitioning with ordered streaming. I need to process each customer's records together, because I will need to pick off the customer attributes from the first record only, and then allow all addresses through.
21-26 If this is the first source record for a particular customer, pipe out a row of CUSTOMER_DETAIL_OT. Only one customer details record will be piped per customer.
28-32 For every source record, pick out the address information and pipe out a row of ADDRESS_DETAIL_OT.
querying a multitype pipelined function
I now have a single function generating rows of two different types and structures. Using SQL*Plus, let's query a few rows from this function.
/* File on web: multitype_query.sql */
  2  FROM   TABLE(
  3            customer_pkg.customer_transform_multi(
  4               CURSOR( SELECT * FROM customer_staging ) ) ) nt
  5  WHERE  ROWNUM <= 5;

That's a surprise - where's my data? Even though I used SELECT *, I have only the CUSTOMER_ID column in my results. The reason for this is simple: my function is defined to return a collection of the customer_ot supertype, which has only one attribute. So unless I code explicitly for the range of subtypes being returned from my function, the database will not expose any of their attributes. In fact, if I reference any of the subtypes’ attributes using the above query format, the database will raise an ORA-00904: invalid identifier exception.
Fortunately, Oracle supplies two ways to access instances of object types: the VALUE function and the OBJECT_VALUE pseudo-column. Let's see what they do (they are interchangeable):
/* File on web: multitype_query.sql */
SQL> SELECT VALUE(nt) AS object_instance --could use “nt.OBJECT_VALUE” instead
  2  FROM   TABLE(
  3            customer_pkg.customer_transform_multi(
  4               CURSOR( SELECT * FROM customer_staging ) ) ) nt
  5  WHERE  ROWNUM <= 5;

CUSTOMER_DETAIL_OT(1, 'Abigail', 'Kessel', '31/03/1949')
ADDRESS_DETAIL_OT(1, 12135, 'N', '37 North Coshocton Street', '78247')
ADDRESS_DETAIL_OT(1, 12136, 'N', '47 East Sagadahoc Road', '90285')
ADDRESS_DETAIL_OT(1, 12156, 'Y', '7 South 3rd Circle', '30828')
CUSTOMER_DETAIL_OT(2, 'Anne', 'KOCH', '23/09/1949')
This is more promising. I now have the data as it is returned from the pipelined function, so I'm going to do two things with it. First I will determine the type of each record using the IS OF condition; this will be useful to me later on. Second, I will use the TREAT function to downcast each record to its underlying subtype (until I do this, the database thinks that my data is of the supertype and so will not allow me access to any of the attributes). The query now looks something like this:
/* File on web: multitype_query.sql */
  2            WHEN VALUE(nt) IS OF TYPE (customer_detail_ot)
  3            THEN 'C'
  4            ELSE 'A'
  5         END                                    AS record_type
  6  ,      TREAT(VALUE(nt) AS customer_detail_ot) AS cust_rec
  7  ,      TREAT(VALUE(nt) AS address_detail_ot)  AS addr_rec
  8  FROM   TABLE(
  9            customer_pkg.customer_transform_multi(
 10               CURSOR( SELECT * FROM customer_staging ) ) ) nt
 11  WHERE  ROWNUM <= 5;

RECORD_TYPE CUST_REC                       ADDR_REC
----------- ------------------------------ ------------------------------
C           CUSTOMER_DETAIL_OT(1, 'Abigail
            ', 'Kessel', '31/03/1949')

A                                          ADDRESS_DETAIL_OT(1, 12135, 'N
                                           ', '37 North Coshocton Street'
                                           , '78247')

A                                          ADDRESS_DETAIL_OT(1, 12136, 'N
                                           ', '47 East Sagadahoc Road', '

A                                          ADDRESS_DETAIL_OT(1, 12156, 'Y
                                           ', '7 South 3rd Circle', '3082

C           CUSTOMER_DETAIL_OT(2, 'Anne',
            'KOCH', '23/09/1949')
I now have my data in the correct subtype format, which means that I can access the underlying attributes. I do this by wrapping the previous query in an in-line view and accessing the attributes using dot notation, as follows.
/* File on web: multitype_query.sql */
SELECT ilv.record_type
,      NVL(ilv.cust_rec.customer_id,
           ilv.addr_rec.customer_id) AS customer_id
,      ilv.cust_rec.first_name       AS first_name
,      ilv.cust_rec.last_name        AS last_name
,      ilv.addr_rec.postal_code      AS postal_code
       SELECT CASE...
       FROM   TABLE(
                     CURSOR( SELECT * FROM customer_staging ) ) ) nt
       ) ilv;
loading multiple tables from a multi-type pipelined function
I've removed some lines from the example above, but you should recognize the pattern. I now have all the elements needed for a multitable insert into my customers and addresses tables. Here's the loading code:
/* File on web: multitype_setup.sql */
         WHEN record_type = 'C'
            INTO customers
            VALUES (customer_id, first_name, last_name, birth_date)
         WHEN record_type = 'A'
            INTO addresses
            VALUES (address_id, customer_id, primary, street_address, postal_code)
      SELECT ilv.record_type
      ,      NVL(ilv.cust_rec.customer_id,
                 ilv.addr_rec.customer_id) AS customer_id
      ,      ilv.cust_rec.first_name       AS first_name
      ,      ilv.cust_rec.last_name        AS last_name
      ,      ilv.cust_rec.birth_date       AS birth_date
      ,      ilv.addr_rec.address_id       AS address_id
      ,      ilv.addr_rec.primary          AS primary
      ,      ilv.addr_rec.street_address   AS street_address
      ,      ilv.addr_rec.postal_code      AS postal_code
      FROM (
            SELECT CASE
                      WHEN VALUE(nt) IS OF TYPE (customer_detail_ot)
                      THEN 'C'
                      ELSE 'A'
                   END                                    AS record_type
            ,      TREAT(VALUE(nt) AS customer_detail_ot) AS cust_rec
            ,      TREAT(VALUE(nt) AS address_detail_ot)  AS addr_rec
            FROM   TABLE(
                         CURSOR( SELECT * FROM customer_staging ))) nt
           ) ilv;
With this INSERT FIRST statement, I have a complex load that uses a range of object-relational features in a way that enables me to retain set-based principles. This approach might also work for you.

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量