Oracle系列之4-Dynamic Cursor

Oracle系列之四----Dynamic 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；

----dynamic cursor---------------------------------------
----sql%rowcount
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');

drop table param;
create table param(
  pname varchar2(20)
)

select * from temp;
select * from param;

begin
  delete from temp where tid = '8';
    dbms_output.put_line('delete lines: ' || sql%rowcount);
  update temp set tname = 'syntax' where tid = '1';
    dbms_output.put_line('update lines: ' || sql%rowcount);
end;

----sql%bulk_rowcount
declare
type tname_type is table of temp.tname%type index by binary_integer;
v_tname tname_type;
begin
  select tname bulk collect into v_tname from temp;
  forall i in 1..v_tname.count
    insert into param values(v_tname(i));
    for j in 1..v_tname.count
    loop
      dbms_output.put_line(j || ' times insert: ' || sql%bulk_rowcount(j) || '---' || v_tname(j));
    end loop;
    commit;
end;

----display cursor
declare
cursor mycur is select * from temp;
rowdata temp%rowtype;
begin
  open mycur;
  loop
    fetch mycur into rowdata;
    exit when mycur%notfound;
    dbms_output.put_line(rowdata.tname);
  end loop;
  close mycur;
end;

----parameter cursor
declare
----note please:parameter type does not need appoint precision
cursor mycur(custage number) is select * from customer where accountid < custage;
rowdata customer%rowtype;
begin
  ----assign value to cursor parameter when open cursor
  open mycur(24);
  loop
    fetch mycur into rowdata;
    exit when mycur%notfound;
    dbms_output.put_line(rpad(rowdata.custname,10) || '    ' || rpad(rowdata.custcareer,15) || '  ' || rpad(rowdata.workunit,6));
  end loop;
  close mycur;
end;

----for cycle cursor
declare
cursor mycur(custage number) is select * from customer where accountid < custage;
begin
  for rowdata in mycur(24)
  loop
    dbms_output.put_line(rpad(rowdata.custname,10) || '    ' || rpad(rowdata.custcareer,15) || '  ' || rpad(rowdata.workunit,6));
  end loop;
end;

----cursor update
declare
/*query and lock result;for update sub sentence:get a row-level exclusive lock when cursor in which row,
  for update of customer.custname(for update of columnname)
*/
cursor mycur(v_custage number) is select * from customer where custage < v_custage for update;
begin
  for i in mycur(24)
  loop
    /*pay attention to application of 'where current of' when modify cursor!
      question:while i want to add update condition,how can i do it? 
      i try it before or after set phrase 'where current of',
      so i think:let update condition before 'for update of columnname',
      is there any better way to ‎improve it?modestly consults the question,thanks!
    */
    update customer set custage = custage + 1 where current of mycur;
    dbms_output.put_line(rpad(i.custname,10) || '    ' || rpad(i.custcareer,15) || '  ' || rpad(i.workunit,6));
  end loop;
end;

declare
cursor mycur(v_custage number) is select * from customer where custage < v_custage and custname = '徐雪花' and custid = 31 for update;
rowdata customer%rowtype;
begin
  open mycur(48);
  loop
    fetch mycur into rowdata;
    exit when mycur%notfound;
    update customer set custname = 'meilin' where current of mycur;
    dbms_output.put_line(rowdata.custname || '--' || rowdata.custage);
  end loop;
  commit;
  close mycur;
end;
select custname,custage from customer where custid = 31;

----dynamic cursor ref
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
----strong type cursor(can not use in dynamic sql expression)
type mycur_type_strong is ref cursor return temp%rowtype;
mycur_strong mycur_type_strong;
----weak type cursor
type mycur_type_weak is ref cursor;
mycur_weak mycur_type_weak;
rowdata customer%rowtype;
begin
  ----open cursor and appoint content of cursor
  open mycur_weak for select * from customer where custid  < 30;
  loop
    fetch mycur_weak into rowdata;
    exit when mycur_weak%notfound;
    dbms_output.put_line(rowdata.custname);
  end loop;
  close mycur_weak;
end;

----procedure 
drop table temp;
create table temp(
  tid number(4),
  tname varchar2(10),
  tage number(3)
);
create or replace procedure insertPro(p_tid in number,p_tname in varchar2,p_tage in number)
is
begin
  insert into temp values (p_tid,p_tname,p_tage);
end insertPro;
call insertPro(4,'hui',18);
select * from temp;

----declare a cursor type in programme package specifications
create or replace package types
as
  type cursorType is ref cursor;
end;

----return resultset by using procedure
create or replace procedure pro_getTemp(tid number,pro_cursor in out types.cursorType)
as
begin
  open pro_cursor for 'select * from temp where tid = ' || tid;
end;

----using cursor as parameter
declare
  type curref is ref cursor;
  v_cur curref;
  rowdata temp%rowtype;
begin
  pro_getTemp(4,v_cur);
  loop
    fetch v_cur into rowdata;
    exit when v_cur%notfound;
    dbms_output.put_line(rowdata.tname || '   ' || rowdata.tage);
  end loop;
  close v_cur;
end;

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

Oracle系列之4-Dynamic Cursor

相关推荐