PL/SQL table (Oracle memory table)-table () function usage

PL/SQL table (Oracle memory table) --- table () function usage = ================================ Author: zhouwf0726 (http://zhouwf0726.itpub.net)
Posted:
Category: Oracle Development
Source: http://zhouwf0726.itpub.net/post/9689/195237
--------------------------------------------------------------- PL/SQL table --- table () function usage

/*

PL/SQL table --- table () function usage:
Using the table () function, we can replace the result set returned by PL/SQL.

Oracle memory tables are frequently used for queries and reports. They are dozens of times faster than physical tables.

Simple Example:

1. Table () combined with Arrays:

*/

Create or replace type t_test as object (
Id integer,
RQ date,
MC varchar2 (60)
);

Create or replace type t_test_table as table of t_test;

Create or replace function f_test_array (N in number default null) return t_test_table
As
V_test t_test_table: = t_test_table ();
Begin
For I in 1 .. nvl (n, 100) loop
V_test.extend ();
V_test (v_test.count): = t_test (I, sysdate, 'mc '| I );
End loop;
Return v_test;
End f_test_array;
/

Select * from table (f_test_array (10 ));

Select * from the (select f_test_array (10) from dual );

/*

2. Table () combined with the pipelined function:

*/

Create or replace function f_test_pipe (N in number default null) return t_test_table pipelined
As
V_test t_test_table: = t_test_table ();
Begin
For I in 1 .. nvl (n, 100) loop
Pipe row (t_test (I, sysdate, 'mc '| I ));
End loop;
Return;
End f_test_pipe;
/

Select * from table (f_test_pipe (20 ));

Select * from the (select f_test_pipe (20) from dual );

/*

3. Table () combined with system package:

*/

Create Table Test (ID varchar2 (20 ));
Insert into test values ('1 ');
Commit;
Explain plan for select * from test;
Select * from table (dbms_xplan.display );

 

Blogger Note: memory table features are available from 9i.