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%rowcountdrop 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_rowcountdeclaretype 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 cursordeclarecursor 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 cursordeclare----note please:parameter type does not need appoint precisioncursor 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 cursordeclarecursor 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 updatedeclare/*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;declarecursor 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 refdrop 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 cursortype 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)isbegin 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 specificationscreate or replace package typesas type cursorType is ref cursor;end;----return resultset by using procedurecreate or replace procedure pro_getTemp(tid number,pro_cursor in out types.cursorType)asbegin open pro_cursor for 'select * from temp where tid = ' || tid;end;----using cursor as parameterdeclare 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
?
?
?
?
?
?
?
?
?
?
?
?