For details about simple application of Table functions on Oracle, oracletable
Advantages
1. Some complicated public information, but creating a view cannot be implemented. In this case, you can consider using pipeline output.
2. There are many operations involved, and it is more difficult to write a simple SQL statement. It is okay to use the table implementation.
Instance
1. Preliminary work:
Create or replace type ty_row as object
(
Col1 varchar2 (36 ),
Col2 varchar2 (36 ),
Col3 varchar2 (36)
);
Create or replace type ty_table as table of ty_row;
2. Define a function to obtain basic user information:
Create or replace function f_get_user_info (v_user_id in varchar2 default null)
Return ty_table
V_user_list ty_table;
Begin
Select ty_row (t. user_id, nvl (t. emp_name, t. user_name), t. user_name) bulk collect
Into v_user_list
From t_bs_user t
Where t. user_id = v_user_id
Or v_user_id is null;
Return v_user_list;
End f_get_user_info;
3. Easy to use:
Select * from table (f_get_user_info ('1 '))