--【create type】*****************************************************************--①简单 type _object--------------------------------------create or replace type test_type01 as object(       a varchar2(20),       b varchar2(20),       c number(10));       --②简单 type _varray--------------------------------------create or replace type test_type02 as varray(10) of test_type01;--③简单 type _table--------------------------------------create or replace type test_type03 as table of test_type02;--④创建带映射函数的【object】类型--create type as object(member) --------------------------------------create or replace type test_type01 as object (       a varchar2(10),       b varchar2(10),       member function aaa(a in varchar2,b in varchar2) return varchar2       );create or replace type body test_type01 as       member function aaa(a in varchar2,b in varchar2) return varchar2 is       begin           return a||b;       end;end;   --用法一：create table of typecreate table test_type_table01 of test_type01;insert into test_type_table01 values(‘a‘,‘a‘);insert into test_type_table01 select test_type01(‘1‘,‘1‘) from dual; commit;select * from test_type_table01;select ee.a,ee.aaa(‘dd‘,‘ee‘) from test_type_table01 ee;--用法二：create table(typename.type)create table test_type_table02(       a number(10),       test_t test_type01       );insert into test_type_table02 values(1,test_type01(‘a‘,‘a‘));insert into test_type_table02 select 2,test_type01(‘b‘,‘b‘) from dual;commit;select * from test_type_table02;select rr.a,rr.test_t,rr.test_t.a,rr.test_t.aaa(‘dd‘,‘ee‘) from test_type_table02 rr;--⑤创建【table】类型--create type as table ----------------------------------------用法一：create or replace type test_type02 as object (       a varchar2(10),       b varchar2(10)       ) not final; create or replace type test_type03 as table of test_type02;       create table test_type_table03(       id number(10),       test_ty test_type03       )nested table test_ty store as test_ty_table;--test_ty_table表示嵌套表【名称可随意取？】；insert into test_type_table03 values(1,test_type03(test_type02(‘a‘,‘a‘)));insert into test_type_table03 select 2,test_type03(test_type02(‘b‘,‘b‘),test_type02(‘c‘,‘c‘)) from dual;commit;select * from test_type_table03;select aa.id,aa.test_ty from test_type_table03 aa;select * from table(select aa.test_ty from test_type_table03 aa where aa.id=2);/*       --无效sqlselect * from table(select aa.test_ty from test_type_table03 aa where aa.id in(1,2));--ORA-01427: 单行子查询返回多个行select * from test_ty_table;--ORA-22812: 无法参考嵌套表列的存储表truncate table test_type_table03;--truncate 无效，因为其中有集合*/--用法二：create or replace type test_type04 as table of varchar2(10);--varchar2要限定字符数，否则会报错create or replace type test_type05 as object(       a number(10),       test_ test_type04       );create table test_type_table02 of test_type05        nested table test_ store as test_ty_table02;--test_为【table】类型的变量              /*--在用法二下这种定义表的语句不被允许,是因为test_type05为object？create table test_type_table04(       id number(10),       test_t test_type05       )nested table test_ store as test_ty_table02;*/insert into test_type_table02 values(test_type05(1,test_type04(‘a‘)));insert into test_type_table02 select test_type05(2,test_type04(‘b‘,‘c‘)) from dual;commit;drop table test_type_table02;drop type test_type04;drop type test_type05;select * from test_type_table02;select * from table(select tt.test_ from test_type_table02 tt where tt.a=2);select aa.a,aa.test_ from test_type_table02 aa;--⑥创建【varray】类型--create type as varray --------------------------------------create or replace type test_type07 as varray(4) of varchar2(10);create table test_type_table03(       id number(10),       test_ test_type07       );insert into test_type_table03 values(1,test_type07(‘a‘));insert into test_type_table03        select 2,test_type07(‘b‘,‘c‘) from dual       union all       select 3,test_type07(‘d‘,‘e‘,‘f‘) from dual;commit;select * from test_type_table03;select kk.* from table(select tt.test_ from test_type_table03 tt where id=3) kk;--⑦创建混合自定义类型create or replace type test_type08 as varray(4) of varchar2(10);create or replace type test_type09 as table of varchar2(10);--varchar2要限定字符数，否则会报错create or replace type test_type10 as object(       a number(10),       test_ test_type08,       test_1 test_type09       );create table test_type_table05 of test_type10       nested table test_1 store as test_ty_table05;insert into test_type_table05 values(1,test_type08(‘a‘),test_type09(‘a‘));insert into test_type_table05 select 2,test_type08(‘b‘,‘c‘),test_type09(‘b‘) from dual;--注意以下两种写法，效果相同insert into test_type_table05 select test_type10(3,test_type08(‘b‘,‘c‘,‘d‘,‘e‘),test_type09(‘c‘,‘d‘)) from dual;insert into test_type_table05 select 4,test_type08(‘b‘,‘c‘,‘d‘,‘e‘),test_type09(‘c‘,‘d‘) from dual;select * from test_type_table05;select * from table(select tt.test_ from test_type_table05 tt where tt.a=3);select * from table(select tt.test_1 from test_type_table05 tt where tt.a=3);--【type】*****************************************************************--①简单 type _object--------------------------------------declare type test_type_ is record(       a emp.empno%type,       b emp.ename%type,       c emp.job%type);       test_ test_type_;begin       test_.a:=123;       test_.b:=‘abc‘;       test_.c:=‘def‘;       dbms_output.put_line(test_.a||test_.b||test_.c);       select empno,ename,job into test_ from emp where empno=‘7369‘;       dbms_output.put_line(test_.a||test_.b||test_.c);end;--②简单 type _varray--------------------------------------declare      type test_type_02 is varray(5) of varchar2(10);      test_ test_type_02;begin      test_:=test_type_02(‘a‘,‘b‘,‘c‘,‘d‘);      dbms_output.put_line(test_(1)||test_(2)||test_(3)||test_(4));end;--③简单 type _table--------------------------------------declare      type test_type_03 is table of emp%rowtype;      test_ test_type_03;begin      select empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into test_ from emp where empno=‘7369‘;      for i in test_.first..test_.last loop          dbms_output.put_line(test_(i).empno||test_(i).ename||test_(i).job);      end loop;end;--【type ref cursor】*****************************************************************--①强类型和弱类型ref游标--------------------------------------declare   --弱类型ref游标  type test_type is ref cursor;  --强类型ref游标（有return返回值）  type test_type_ is ref cursor return emp%rowtype;  test_ test_type;  test__ test_type_;  emp_ emp%rowtype;  emp__ emp%rowtype;begin  open test_ for select * from emp;  loop       fetch test_ into emp_;       dbms_output.put_line(emp_.ename);       exit when test_%notfound;  end loop;  close test_;  dbms_output.put_line(‘##############################‘);  open test__ for select * from emp;  loop       fetch test__ into emp__;       dbms_output.put_line(emp__.ename);       exit when test__%notfound;  end loop;  close test__;  end;--②将ref游标作为返回值----------------------------------------创建函数（返回游标）create or replace function test_func_ref return sys_refcursor as       test_ref sys_refcursor;begin       open test_ref for select * from emp;       return test_ref;end;       --调用函数declare a sys_refcursor;        b emp%rowtype;begin        a:=test_func_ref;        loop          fetch a into b;          exit when a%notfound;          dbms_output.put_line(b.empno);        end loop;end;   --sys_refcursor是9i后系统定义的一个refcursor，用于过程中返回结果集