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

?

?

?

?

?

?

?

?

?

?

?

?