A table function is defined as a function that can produce a set of rows as output. Additionally, table functions can take a set of rows as input. Prior to Oracle9i, PL/SQL functions: Show
Now, functions are not limited in these ways. Table functions extend database functionality by allowing:
Table functions can be defined in PL/SQL using a native PL/SQL interface, or in Java or C using the Oracle Data Cartridge Interface (ODCI). See Also:
Figure 17-3 illustrates a typical aggregation where you input a set of rows and output a set of rows, in that case, after performing a The pseudocode for this operation would be similar to: INSERT INTO Out SELECT * FROM ("Table Function"(SELECT * FROM In)); The table function takes the result of the Additionally, a table function can fan out data within the scope of an atomic transaction. This can be used for many occasions like an efficient logging mechanism or a fan out for other independent transformations. In such a scenario, a single staging table is needed. The pseudocode for this would be similar to: INSERT INTO target SELECT * FROM (tf2(SELECT * FROM (tf1(SELECT * FROM source)))); This inserts into INSERT INTO target SELECT * FROM tf3(SELT * FROM stage_table1); See Also:
Objects to Create Before Running Table Function Examples The following examples demonstrate the fundamentals of table functions, without the usage of complex business rules implemented inside those functions. They are chosen for demonstration purposes only, and are all implemented in PL/SQL. Table functions return sets of records and can take cursors
as input. Besides the CREATE TYPE product_t AS OBJECT ( prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50) , prod_category_desc VARCHAR2(2000) , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) , prod_list_price NUMBER(8,2) , prod_min_price NUMBER(8,2) ); / CREATE TYPE product_t_table AS TABLE OF product_t; / COMMIT; CREATE OR REPLACE PACKAGE cursor_PKG AS TYPE product_t_rec IS RECORD ( prod_id NUMBER(6) , prod_name VARCHAR2(50) , prod_desc VARCHAR2(4000) , prod_subcategory VARCHAR2(50) , prod_subcategory_desc VARCHAR2(2000) , prod_category VARCHAR2(50) , prod_category_desc VARCHAR2(2000) , prod_weight_class NUMBER(2) , prod_unit_of_measure VARCHAR2(20) , prod_pack_size VARCHAR2(30) , supplier_id NUMBER(6) , prod_status VARCHAR2(20) , prod_list_price NUMBER(8,2) , prod_min_price NUMBER(8,2)); TYPE product_t_rectab IS TABLE OF product_t_rec; TYPE strong_refcur_t IS REF CURSOR RETURN product_t_rec; TYPE refcur_t IS REF CURSOR; END; / REM artificial help table, used later CREATE TABLE obsolete_products_errors (prod_id NUMBER, msg VARCHAR2(2000)); Example 17-6 Table Functions Example: Basic Example This example demonstrates a simple filtering; it shows all obsolete products except the CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t) RETURN product_t_table IS prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; objset product_t_table := product_t_table(); i NUMBER := 0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched -- Category Electronics is not meant to be obsolete and will be suppressed IF prod_status='obsolete' AND prod_category != 'Electronics' THEN -- append to collection i:=i+1; objset.extend; objset(i):=product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price); END IF; END LOOP; CLOSE cur; RETURN objset; END; / You can use the table function in a SQL statement to show the results. Here you use additional SQL functionality for the output: SELECT DISTINCT UPPER(prod_category), prod_status FROM TABLE(obsolete_products( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products))); Example 17-7 Table Functions Example: Filtering Using REF CURSOR This example implements the same filtering as Example 17-6. The main differences between the two are:
CREATE OR REPLACE FUNCTION obsolete_products_pipe(cur cursor_pkg.strong_refcur_t) RETURN product_t_table PIPELINED PARALLEL_ENABLE (PARTITION cur BY ANY) IS prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched IF prod_status='obsolete' AND prod_category !='Electronics' THEN PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)); END IF; END LOOP; CLOSE cur; RETURN; END; / You can use the table function as follows: SELECT DISTINCT prod_category, DECODE(prod_status,'obsolete','NO LONGER AVAILABLE','N/A') FROM TABLE(obsolete_products_pipe( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products))); You now change the degree of parallelism for the input table products and issue the same statement again: ALTER TABLE products PARALLEL 4; The session statistics show that the statement has been parallelized: SELECT * FROM V$PQ_SESSTAT WHERE statistic='Queries Parallelized'; STATISTIC LAST_QUERY SESSION_TOTAL -------------------- ---------- ------------- Queries Parallelized 1 3 1 row selected. Example 17-8 Table Functions Example: Fanning Out Results into Persistent Tables Table functions are also capable to fanout results into persistent table structures. In this example, the function filters returns all obsolete products except a those of a specific
CREATE OR REPLACE FUNCTION obsolete_products_dml(cur cursor_pkg.strong_refcur_t, prod_cat varchar2 DEFAULT 'Electronics') RETURN product_t_table PIPELINED PARALLEL_ENABLE (PARTITION cur BY ANY) IS PRAGMA AUTONOMOUS_TRANSACTION; prod_id NUMBER(6); prod_name VARCHAR2(50); prod_desc VARCHAR2(4000); prod_subcategory VARCHAR2(50); prod_subcategory_desc VARCHAR2(2000); prod_category VARCHAR2(50); prod_category_desc VARCHAR2(2000); prod_weight_class NUMBER(2); prod_unit_of_measure VARCHAR2(20); prod_pack_size VARCHAR2(30); supplier_id NUMBER(6); prod_status VARCHAR2(20); prod_list_price NUMBER(8,2); prod_min_price NUMBER(8,2); sales NUMBER:=0; BEGIN LOOP -- Fetch from cursor variable FETCH cur INTO prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price; EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched IF prod_status='obsolete' THEN IF prod_category=prod_cat THEN INSERT INTO obsolete_products_errors VALUES (prod_id, 'correction: category '||UPPER(prod_cat)||' still available'); COMMIT; ELSE PIPE ROW (product_t( prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)); END IF; END IF; END LOOP; CLOSE cur; RETURN; END; / The following query shows all obsolete product groups except the SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products))); As you can see, there are some products of the SELECT DISTINCT msg FROM obsolete_products_errors; Taking advantage of the second input variable, you can specify a different product group than Electronics to be considered: SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml( CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products),'Photo')); Because table functions can be used like a normal table, they can be nested, as shown in the following: SELECT DISTINCT prod_category, prod_status FROM TABLE(obsolete_products_dml(CURSOR(SELECT * FROM TABLE(obsolete_products_pipe(CURSOR(SELECT prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price FROM products)))))); The biggest advantage of Oracle Database's ETL is its toolkit functionality, where you can combine any of the latter discussed functionality to improve and speed up your ETL processing. For example, you can take an external table as input, join it with an existing table and use it as input for a parallelized table function to process complex business
logic. This table function can be used as input source for a How does ETL help transfer data in and out of the data warehouse?A typical ETL process collects and refines different types of data, then delivers the data to a data lake or data warehouse such as Redshift, Azure, or BigQuery. ETL tools also makes it possible to migrate data between a variety of sources, destinations, and analysis tools.
Where extraction transformation and preparation of loading takes place?Extraction, transformation, and loading (ETL) processes are responsible for the operations taking place in the background of a data warehouse architecture.
How does ETL help transfer data in and out of the data warehouse quizlet?How does ETL help transfer data in and out of the data warehouse? ETL is a process that extracts information from internal and external databases, transforms the information using a common set of enterprise definitions, and loads the information into a data warehouse.
What does the typical Extract Transform Load ETL )The typical extract, transform, load (ETL)-based data warehouse uses staging, data integration, and access layers to house its key functions. The staging layer or staging database stores raw data extracted from each of the disparate source data systems.
|