Simple Application example of table function in Oracle

Description

Table functions can accept query statements or cursors as input parameters, and can output multiple rows of data.
The function can be executed in parallel and can continuously output data streams, which are called piped outputs.
Application table functions can process data transformations in stages and eliminate the storage and buffering tables for intermediate results.

Advantage

1. Some complex public information, but the creation of a view is not possible, you can consider the use of pipeline output.
2. Involved in the operation of more, write a simple SQL implementation is more troublesome, with table implementation is OK

Instance

1. Pre-work:

createorreplacetypeasobject(  varchar2(36),  varchar2(36),  varchar2(36));

createorreplacetypeastableof ty_row;

2. Then define a function to get the user's basic information:

createorreplacefunctioninvarchar2defaultnull)  returnas  v_user_list ty_table;begin  selectnvlbulkcollect    into v_user_list    from t_bs_user t   where t.user_id = v_user_id      orisnull;  return v_user_list;end f_get_user_info;

3. It's easy to use:

selectfromtable(f_get_user_info(‘1‘))

Simple Application example of table function in Oracle