The following example creates a procedure that converts a CLOB value containing XML sales data into a set of rows that are inserted into a sales table.
First the table definitions:
CREATE TABLE sales ( partnum INTEGER, qtysold INTEGER, storecode INTEGER, salesdate DATE) PRIMARY INDEX (partnum, qtysold, storecode, salesdate); CREATE TABLE saleslog ( storecode INTEGER, salesdate DATE, sales CLOB, logdate DATE, logtime TIME) PRIMARY INDEX (storecode, salesdate, logdate, logtime);
The following SQL code defines the procedure:
CREATE PROCEDURE stores_sales_procedure (storecode INTEGER, salesdate DATE, salesclob CLOB) BEGIN INSERT INTO saleslog (:storecode, :salesdate, :salesclob, CURRENT_DATE, CURRENT_TIME); INSERT INTO sales SELECT * FROM TABLE (xmlparser(:salesclob)); END;
The stages in the process followed by this procedure are as follows:
- The sales CLOB is inserted into a log table named saleslog.
- The CLOB is passed by means of the variable salesclob to a user-defined table function named xmlparser (see CREATE FUNCTION (Table Form)).
- The system invokes table function xmlparser on every AMP.
- The table function invokes fnc_loblocal (the definitions for xmlparser and fnc_loblocal are not shown for this example) to detect whether the CLOB is located on the same AMP on which the function is executing.
- The function that has local access to the CLOB strips out the XML sales data and returns standard relational table rows to be inserted into sales.