嵌套表的用法 SQL> CREATE OR REPLACE TYPE tnt_names AS TABLE OF VARCHAR2(64);% E/ a, Z C/ W
2 /' u9 W7 f5 Y: d4 J7 p" z
类型已建树。
* G& V; V+ ^' b8 i 已用时刻: 00: 00: 00.04/ e. U4 G: @0 g8 O+ K% F5 M
SQL> create table test_yct( id number(2), names tnt_names) nested table names store as test_yct_names;
& X) d: p) M. J [ 表已建树。2 C/ c# z2 G* p4 {; G1 B) O3 Z
已用时刻: 00: 00: 00.02
0 V# z2 `; @3 I SQL> insert into test_yct values(1, tnt_names('yuechaotian1', 'yuexingtian1', 'jinglitian1'));+ u1 G b0 z0 Q) M8 F
已建树 1 行。
# K v4 g2 [) b, G0 t) A( } 已用时刻: 00: 00: 00.00 N+ W, H" b/ w5 r
SQL> insert into test_yct values(2, tnt_names('yuechaotian2', 'yuexingtian2', 'jinglitian2'));
/ \: p1 J) J9 D# i5 L 已建树 1 行。! ^+ @8 A4 M. W: s, P; l( Y. T
已用时刻: 00: 00: 00.00
1 w6 A/ `- `7 i( h SQL> col names format a60
3 M$ T9 V. ]6 C. n SQL> select * from test_yct;. }! j) J; i! k' W3 ]9 Q
ID NAMES
% L7 I& l2 I+ \) S+ s3 v ---------- ------------------------------------------------------------
. R& X5 v8 W$ \ Q- W" u; }8 S 1 TNT_NAMES('yuechaotian1', 'yuexingtian1', 'jinglitian1')$ w; y/ B- X4 h; ?
2 TNT_NAMES('yuechaotian2', 'yuexingtian2', 'jinglitian2')
^# p7 E. v) A 已用时刻: 00: 00: 00.00
1 M! D5 \, m& }. s2 I SQL> select y.id, c.* from test_yct y, table(y.names) c;+ Q1 b& ~4 n# Q9 T% K8 c
ID COLUMN_VALUE+ e# r+ _: ]* Y/ a' G& p. j2 P$ l
---------- ----------------------------------------------------------------8 X4 a) [! I' b3 @* C
1 yuechaotian1
5 C+ V8 d( {2 @0 f& {% w3 Y" k 1 yuexingtian17 w# Z8 y& v8 T) S7 a" d; Z& c
1 jinglitian1
3 w3 D, D+ l# V5 ?) m 2 yuechaotian2
0 J5 `- {3 d# m, J 2 yuexingtian2
- v5 T6 C( v% U! |3 F; _5 \ 2 jinglitian2
+ S5 d7 ?& e# J! w$ E 已选择6行。- R( y: ^5 V" C, L" l+ J
已用时刻: 00: 00: 00.00 |