oracle savepoint游标,Oracle系列之三-Implicit Cursor

Oracle系列之三----Implicit Cursor

Oracle系列之一----Datatype And Subprogram：http://overshit.iteye.com/admin/blogs/932585；

Oracle系列之二----Exception And Expression：http://overshit.iteye.com/admin/blogs/932605；

Oracle系列之三----Implicit Cursor：http://overshit.iteye.com/admin/blogs/932609；

Oracle系列之四----Dynamic Cursor：http://overshit.iteye.com/admin/blogs/932610；

Oracle系列之五----Cursor And Variable：http://overshit.iteye.com/admin/blogs/932612；

Oracle系列之六----Procedure--Package--Purity：http://overshit.iteye.com/admin/blogs/932615；

Oracle系列之七----Trigger：http://overshit.iteye.com/admin/blogs/932616；

----implicit cursor:sql%notfound returning

----sql%isopen

/*Attribute:sql%found:a value of boolean,if influence records in db,it returns true,or return false;

sql%notfound:against sql%found;

sql%rowcount:how many records are influenced(return an integer);

sql%isopen:a value of boolean,if cursor is open,return true,or return fase,implicit cursor always return fasle;

Method:returning: lay back result of sql expression operation,reduce access db query operation,upgrade db performance;

*/

drop table temp;

create table temp(

tid varchar2(10),

tname varchar2(20)

)

insert into temp values ('1','meilin');

insert into temp values ('2','bing');

insert into temp values ('3','qing');

declare

v_temp temp%rowtype;

begin

update temp set tname = 'syntax' where tid = 1 returning tid,tname into v_temp;

if sql%notfound then

dbms_output.put_line('Update Failure: ' || sql%rowcount);

else

dbms_output.put_line('Update Success: ' || sql%rowcount || ' ' || v_temp.tname);

if sql%isopen then

dbms_output.put_line('The cursor is open!');

else

dbms_output.put_line('The cursor is close!');

end if;

end if;

end;

select * from temp;

----implicit cursor:bulk collect

delete from temp;

insert into temp values ('1','meilin');

insert into temp values ('2','bing');

insert into temp values ('3','qing');

declare

type temp_type is table of temp%rowtype index by binary_integer;

v_temp temp_type;

begin

select * bulk collect into v_temp from temp;

for i in 1.. v_temp.count

loop

dbms_output.put_line('tname:' || v_temp(i).tname);

end loop;

end;

----forall:reduce transfer data times between PL/SQL ‎engine and SQL ‎engine,upgrade db performance;

drop table param;

create table param(

pname varchar2(20)

)

declare

type temp_tname_type is table of temp.tname%type index by binary_integer;

v_tname temp_tname_type;

begin

select custname bulk collect into v_tname from customer where custid = '96';

forall i in 1..v_tname.count

insert into param values (v_tname(i));

dbms_output.put_line(sql%bulk_rowcount(1) || '--' || v_tname(1));

commit;

----rollback;

end;

select * from param;

----transaction

delete from param;

begin

----transaction start

insert into param values ('Java');

savepoint spj;

insert into param values ('Python');

savepoint spp;

insert into param values ('Dotnet');

savepoint spd;

rollback to savepoint spj;

commit;

exception

when others then

rollback;

end;

select * from param;

----lock

update temp set tname = 'lock' where tid = '1';

----transaction isolation(to be continued)

Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868