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 ddl
drop table temp;
create table temp(
  tid varchar2(10),
  tname varchar2(20)
)
begin
execute immediate 'drop table temp';
end;
select * from temp;

----non query binding variable
begin
execute 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 mode
begin
  for i in 1..10000
  loop
    execute immediate 'insert into tb values (' || i || ')';
  end loop;
  rollback;
end;
 
----binding variable mode
begin
for i in 1..10000
loop
  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 cursor
declare
  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 cursor
declare
  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)valu