oracle复合类型---嵌套表模糊查询

--在表列中使用嵌套表
--必须先使用CREATE TYPE命令来建立嵌套表类型,当使用嵌套表类型作为表列的数据类型时,必须为嵌套表指定专门的存储表
CREATE TYPE phone_type01 IS TABLE OF VARCHAR2(20);
--
CREATE TABLE employee011(
   id NUMBER(4), 
   name VARCHAR2(10),
   sal NUMBER(6,2),
   phone phone_type01)NESTED TABLE phone STORE AS phone_type011;
--
iNSERT INTO employee011 VALUES(2, 'SCOddTT', 80, phone_type01('12345dhh6','ddd1324564hhh1234'));
/*iNSERT INTO employee011 
VALUES(1, 'SCOTT', 800, phone_type01(TO_DATE('2012-03-05','YYYY-MM-DD'),'中国人的ffffffff的的',to_number('46456')));*/


---
select *  from employee011;
--模糊查询 
select  T.*,TT.*  from employee011 T , table( T.PHONE) TT where TT.COLUMN_VALUE  not  in ('%1324564hhh1234');


select no
from (
select column_value as no  from table(select phone from employee011)
)  where no='12345hh6';


select *from phone_type011
--通过表字段查询
select T.* from table(select phone from employee011 where name='SCOTT') T ;