Oracle学习(五)——15天Oracle入门

--集合

--<1>集合.exists(i)是判定集合是否存在第i个元素，存在返回真否则返回假

declare

type mytye is  varray(10) of number;

av mytye:= mytye(4,2,5,4,5,0,4,1,8);

ma  number:=10;

begin

if av.exists(ma) then

dbms_output.put_line(ma);

end if;

end;

 

--<2> limit返回集合的最大个数，索引表和嵌套表返回null值

declare

type mytype is table of number(8) index by binary_integer;

va mytype;

begin

dbms_output.put_line(va.limit);

end;

 

--<3>first 和 last 返回的是第一个或者是最后一个元素的下标。

declare

type mytype is varray(20) of number;

va mytype:=mytype(1,2,3,4,5,6,7,8,9,10);

begin

dbms_output.put_line(va.first);

dbms_output.put_line(va.last);

end; --注意其返回是下标

 

--<4> priop 和 next 返回的是无素的上一个或者下一个元素的下标，其返回的同样是下标

declare

type mytype is varray(20) of varchar2(20);

va mytype;

begin

va:=mytype('a','b','c','d','e','f','g','h','i','j','k');

dbms_output.put_line(va.prior(2));

end;

 

--<5>trim 从尾部删除元素, trim从尾部删除一个, trim(n)从尾部删除n个

 

declare

type mytype is varray(20) of varchar2(20);

va mytype;

begin

va:=mytype('a','b','c','d','e','f','g','h','i','j','k');

va.trim(3);

for i in 1..va.count loop

dbms_output.put_line(va(i));

end loop;

end;

 

--<6> delete 同样是删除尾部元素，delete 删除全部元素，delete(n),删                                                                                          除集合第n个元素

 

 

 

create table test

(  tid number(10),   tname varchar2(10))

 

--在forall语句中使用部分集合元素

declare

  type my is table of number(6) index by binary_integer;

  type my2 is table of varchar2(10) index by binary_integer;

  va1        my;

  va2        my2;

  start_time number(10);

  end_time   number(10);

begin

  for i in 1 .. 50 loop

    va1(i) := i;

    va2(i) := 'name';

  end loop;

  forall i in 40 .. 50

    insert into test values (va1(i), va2(i));

end;

 

select * from test

 

drop table test

 

declare

type my is table of number(6);

va1 my:=my(41,null,43,null,45);

begin  

 forall i in indices of va1 --删去在这个处的索引

  delete from test where tid=va1(i);

  end;

 

 

declare

type my is table of number(10);

va1 my:=my(41,null,43,null,45);

begin  

 forall i in values of va1

  delete from test where tid=va1(i);  --删去在这个处的索引

  end;

 

 

--Bulk collect 子句用于取得批量数据 ,它只适用于selete into语句,fetch into语句 和DML返回子句

declare

type my is table of emp%rowtype index by binary_integer;

va my;

begin

select * bulk collect into va from emp;--返回的是一列数据

for i in 1..va.count loop

dbms_output.put_line(va(i).ename|| va(i).job||va(i).sal);

end loop;

end;

 

create table emp as select * from scott.emp

select * from emp

 

--在DML的返回子句中使用bulk collect子句

declare

type my is table of emp%rowtype ;

va my;

begin

delete from emp where deptno =20

returning empno,ename,job,mgr,hiredate,sal,comm,deptno  bulk collect into va;--return语句后必须要全部列出列名不能用*代替

for i in 1..va.count loop

dbms_output.put_line(va(i).ename||va(i).job);

end loop;

end;

 

select * from emp