oracle中table函数的使用

个人理解，table函数是把数组类型的变量展开显示出来

三种用法:

 1 table()与返回值为数组的类型的普通函数一起使用

 2 table()与返回值为数组类型的管道函数一起使用

3, table()与系统包中的管道函数一起使用 实际也就是2

 

以下代码为例子：

 

[c-sharp] view plain copy print ?

1. --创建类型t_test  
2. SQL> create or replace type t_test as object (  
3.   2  id number,  
4.   3  time date,  
5.   4  data varchar2(60)  
6.   5  );  
7.   6  /  
8.    
9. Type created  
10.    
11. Executed in 0.063 seconds  
12.  --创建以t_test类型的数组t_test_tb  
13. SQL> create or replace type t_test_tb as table of t_test;  
14.   2  /  
15.    
16. Type created  
17.    
18. Executed in 0.031 seconds  
19.    
20. --1 创建普通函数 返回数组类型，用数组的实例存放结果集(t_array),结果集全部处理完成后一起返回  
21. SQL> create or replace function f_test_array(n in number default null) return t_test_tb as  
22.   2    t_array t_test_tb := t_test_tb();  
23.   3  begin  
24.   4    for i in 1..nvl(n,100) loop  
25.   5        t_array.extend();  
26.   6        t_array(t_array.count) := t_test(i,sysdate,'mi'||i);  
27.   7    end loop;  
28.   8    return t_array;  
29.   9  end;  
30.  10  /  
31.    
32. Function created  
33.    
34. Executed in 0.046 seconds  
35.  --直接select是不能显示的  
36. SQL> select f_test_array(10) from dual;  
37.    
38. F_TEST_ARRAY(10)  
39. ----------------  
40. <Object>  
41.    
42. Executed in 0.062 seconds  
43.  --对于返回类型是数组的那么用table()函数或者the  
44. SQL> select * from table(f_test_array(10));  
45.    
46.         ID TIME        DATA  
47. ---------- ----------- ------------------------------------------------------------  
48.          1 2010-8-10 1 mi1  
49.          2 2010-8-10 1 mi2  
50.          3 2010-8-10 1 mi3  
51.          4 2010-8-10 1 mi4  
52.          5 2010-8-10 1 mi5  
53.          6 2010-8-10 1 mi6  
54.          7 2010-8-10 1 mi7  
55.          8 2010-8-10 1 mi8  
56.          9 2010-8-10 1 mi9  
57.         10 2010-8-10 1 mi10  
58.    
59. 10 rows selected  
60.    
61. Executed in 0.187 seconds  
62.    
63. SQL> select * from the(select f_test_array(10) from dual);  
64.    
65.         ID TIME        DATA  
66. ---------- ----------- ------------------------------------------------------------  
67.          1 2010-8-10 1 mi1  
68.          2 2010-8-10 1 mi2  
69.          3 2010-8-10 1 mi3  
70.          4 2010-8-10 1 mi4  
71.          5 2010-8-10 1 mi5  
72.          6 2010-8-10 1 mi6  
73.          7 2010-8-10 1 mi7  
74.          8 2010-8-10 1 mi8  
75.          9 2010-8-10 1 mi9  
76.         10 2010-8-10 1 mi10  
77.    
78. 10 rows selected  
79.    
80. Executed in 0.172 seconds  
81.  --2 用返回类型为数组的管道函数，这里没用用到存放中间结果的变量，每处理完一条记录那么立刻返回结果  
82. SQL> create or replace function f_test_pipe(n in number default null) return t_test_tb pipelined  
83.   2  as  
84.   3  begin  
85.   4         for i in 1..nvl(n,100) loop  
86.   5             pipe row(t_test(i,sysdate,'mi'||i));  
87.   6         end loop;  
88.   7         return;  
89.   8  end;  
90.   9  /  
91.    
92. Function created  
93.    
94. Executed in 0.031 seconds  
95.    
96. SQL> select f_test_pipe(10) from dual;  
97.    
98. F_TEST_PIPE(10)  
99. ---------------  
100. <Object>  
101.    
102. Executed in 0.062 seconds  
103.    
104. SQL> select * from table(f_test_pipe(10));  
105.    
106.         ID TIME        DATA  
107. ---------- ----------- ------------------------------------------------------------  
108.          1 2010-8-10 1 mi1  
109.          2 2010-8-10 1 mi2  
110.          3 2010-8-10 1 mi3  
111.          4 2010-8-10 1 mi4  
112.          5 2010-8-10 1 mi5  
113.          6 2010-8-10 1 mi6  
114.          7 2010-8-10 1 mi7  
115.          8 2010-8-10 1 mi8  
116.          9 2010-8-10 1 mi9  
117.         10 2010-8-10 1 mi10  
118.    
119. 10 rows selected  
120.    
121. Executed in 0.156 seconds  
122.    
123. SQL> select * from the(select f_test_pipe(10) from dual);  
124.    
125.         ID TIME        DATA  
126. ---------- ----------- ------------------------------------------------------------  
127.          1 2010-8-10 1 mi1  
128.          2 2010-8-10 1 mi2  
129.          3 2010-8-10 1 mi3  
130.          4 2010-8-10 1 mi4  
131.          5 2010-8-10 1 mi5  
132.          6 2010-8-10 1 mi6  
133.          7 2010-8-10 1 mi7  
134.          8 2010-8-10 1 mi8  
135.          9 2010-8-10 1 mi9  
136.         10 2010-8-10 1 mi10  
137.    
138. 10 rows selected  
139.    
140. Executed in 0.172 seconds  
141.    
142. SQL> drop table test;  
143.    
144. Table dropped  
145.    
146. Executed in 0.047 seconds  
147.  --3 table调用包中返回为数组类型的函数 dbms_xplan.display 是返回类型为数组的管道函数  
148. SQL> create table test(id number, name varchar2(30));  
149.    
150. Table created  
151.    
152. Executed in 0.047 seconds  
153.    
154. SQL> insert into test values(1,'d');  
155.    
156. 1 row inserted  
157.    
158. Executed in 0 seconds  
159.    
160. SQL> commit;  
161.    
162. Commit complete  
163.    
164. Executed in 0.016 seconds  
165.    
166. SQL> explain plan for select * from test;  
167.    
168. Explained  
169.    
170. Executed in 0 seconds  
171.    
172. SQL> select * from table(dbms_xplan.display);  
173.    
174. PLAN_TABLE_OUTPUT  
175. --------------------------------------------------------------------------------  
176. Plan hash value: 1357081020  
177. --------------------------------------------------------------------------  
178. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
179. --------------------------------------------------------------------------  
180. |   0 | SELECT STATEMENT  |      |     1 |    30 |     3   (0)| 00:00:01 |  
181. |   1 |  TABLE ACCESS FULL| TEST |     1 |    30 |     3   (0)| 00:00:01 |  
182. --------------------------------------------------------------------------  
183. Note  
184. -----  
185.    - dynamic sampling used for this statement  
186.    
187. 12 rows selected  
188.    
189. Executed in 0.187 seconds