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

?

?

?

?

?

?

?