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

Source: Internet
Author: User
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.