Oracle Learning (ii) Use of table functions

Original address:

http://blog.csdn.net/lne818/article/details/3042250

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 with the table.

The

Oracle Memory table is used more often when querying and reporting, and it is dozens of times times faster than a physical table.

Simple example:

1, table () Associative arrays:

*/

Create or replace type T_test as Object (
ID integer,
RQ date,
MC varchar2
);

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_tabl E: = 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);

SELECT * from the (select F_test_array (Ten) from dual);

/*

2, table () combined with 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 (a) from dual);

/*

3, table () combined with the 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);

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 with the table.

Oracle memory tables are used more often in queries and reports, which are dozens of times times faster than physical tables.

Simple example:

1. Table () Associative array:

*/

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 (Ten) from dual);

/*

2, table () combined with 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 (a) from dual);

/*

3, table () combined with the 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);

Oracle Learning (ii) Use of the table function