Oracle系列之五----Cursor And Variable
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；
?
----non query ddldrop table temp;create table temp( tid varchar2(10), tname varchar2(20))beginexecute immediate 'drop table temp';end;select * from temp;----non query binding variablebeginexecute immediate 'insert into temp values(4,:tname)' using &variable;commit;end;----binding variable:the efficiency of using binding variable mode outclass(much higher than) using character mode;delete from tb;create table tb( tbname varchar2(10))select * from tb;----character modebegin for i in 1..10000 loop execute immediate 'insert into tb values (' || i || ')'; end loop; rollback;end; ----binding variable modebeginfor i in 1..10000loop execute immediate 'insert into tb values(:param)' using i; end loop;end;----non query returning:when input parameter,do not need input '' and tid can not change!drop table temp;create table temp( tid number(3), tname varchar2(20))insert into temp values (1,'meilin');insert into temp values (2,'bing');insert into temp values (3,'qing');declare v_tid number(3); v_tname varchar2(20); strsql varchar2(200);begin strsql := 'update temp set tname =: tname where tid =: tid returning tid,tname into :a,:b'; execute immediate strsql using '&tname',&tid returning into v_tid,v_tname; dbms_output.put_line(v_tid || '---' || v_tname);end;select * from temp;----query ref cursordeclare type curType is ref cursor; v_cur curType; rowdata temp%rowtype; strsql varchar2(200);begin strsql := 'select * from temp where tid>:tid'; open v_cur for strsql using 0; loop fetch v_cur into rowdata; exit when v_cur%notfound; dbms_output.put_line(rowdata.tid || '---' || rowdata.tname); end loop;end;----return implicit cursordeclare type temp_row is table of temp%rowtype index by binary_integer; v_row temp_row; strsql varchar2(200);begin strsql := 'select * from temp where tid > :tid'; execute immediate strsql bulk collect into v_row using 0; for i in 1..v_row.count loop dbms_output.put_line(v_row(i).tid || '---' || v_row(i).tname); end loop;end;?
Oracle中index by binary_integer：如type num is table of number index by binary_integer， 加了index by binary_integer后，num类型的下标就是自增长，num类型插入元素时无需初始化，不需每次extend增加一个空间； 而如果没有有index by binary_integer，则需显示对初始化，且每插入一个元素到num类型的table中都需extend；没加index by binary_integer：declare type num is table of number; n numis := num();begin n.extend; n(1) := 2; n.extend; n(2) := 3; for i in 1..n.count loop dbms_output.put_line(n(i)); end loop;end;输出2和3；加index by binary_integer：declare type num is table of number; n num;begin n(1) = 2; n(2) : 3; for i in 1..n.count loop dbms_output.put_line(n(i)); end loop;end;----------------------------------account.sql----------------------------------drop table account;create table account( id number(3) not null, --账户id name varchar2(50) not null, --账户名 balance number(8,2) not null, --账户余额 btime date default sysdate not null --开户时间)insert into account (id,name,balance,btime)values (1,'张三',2000.23,TO_DATE('12-02-2008','dd-mm-yyyy'));insert into account (id,name,balance,btime)values (2,'李四',530,TO_DATE('10-03-2008','dd-mm-yyyy'));insert into account (id,name,balance,btime)values (3,'王五',1620.2,TO_DATE('20-08-2007','dd-mm-yyyy'));insert into account (id,name,balance,btime)values (4,'小强',910.9,TO_DATE('23-01-2009','dd-mm-yyyy'));insert into account (id,name,balance,btime)values (5,'小周',8700,TO_DATE('10-09-2006','dd-mm-yyyy'));--insert into account (id,name,balance,btime)values (6,'←夕※炎→',8341,default);select distinct * from account;declare--保存开户日期与当前日期的差值v_monthbt number(5,2);type str_type is table of varchar2(50) index by binary_integer;type id_table_type is table of number(3) index by binary_integer;--账户名称数组name_table str_type;--赠送金额数组money_table str_type;--账户id数组id_table id_table_type;--建立注册时间与当前时间差，超过6各月+100；超过12各月+200；超过24各月+500；而未满6各月不加beginfor i in 1..5loop select months_between(sysdate,btime) into v_monthbt from account where id=1; if v_monthbt between 6 and 12 then money_table(i):=100; elsif v_monthbt between 12 and 24 then money_table(i):=200; elsif v_monthbt>=24 then money_table(i):=500; else money_table(i):=0; end if; id_table(i):=i;end loop;forall i in 1..money_table.countupdate account set balance=balance+money_table(i)where id=id_table(i) returning name bulk collect into name_table;for i in 1..name_table.countloopdbms_output.put_line(name_table(i));end loop;commit;end;select * from account;--为另一用户转账100(自己转出)，要考虑到自己余额是否充足和接受账户是否存在的问题declarev_balance account.balance%type;beginupdate account set balance=balance-100where name='&nametest'returning balance into v_balance;if sql%notfound thenraise_application_error(-20001,'&nametest不存在');end if;if v_balance <0 thenraise_application_error(-20001,'账户余额不足');end if;--接收方提交看是否已转账update account set balance =balance+100 where name='&recname';if sql%notfound thenraise_application_error(-20001,'&recname不存在');end if;commit;--其它情况，没成功则回滚!exception when others then rollback; dbms_output.put_line(sqlerrm);end;/select * from account;?
Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868
?
?
?
?
?
?
?
?
?
?
?
?