--在表列中使用嵌套表 --必须先使用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 ;
oracle中的复合查询,oracle复合类型---嵌套表模糊查询
最新推荐文章于 2023-04-17 11:02:56 发布