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；

?

先来看个decode：

create table emp(
       empid number(4) primary key not null,
       salary number (8) not null
)
insert into emp values(1,1000);
insert into emp values(2,4000);
select * from emp;
select decode (sign(salary-3000),1,salary*1.05,-1,salary*1.1) from emp

?

decode可以接很多expression：

decode(condition,expressiona，valuea，expressionb，valueb，……expressionn，valuen，default value)
if condition = expressiona then
   return (valuea)
elsif condition = expressionb then
   return (valueb)
……
elsif condition = expressionn then
   return (valuen)
else
   return (default value)
end if;

?

?

下面的case等语句就省略decode写法了

?

----if expression,input:'徐雪花'
declare
  v_custage number(3);
  v_custname varchar2(50);
begin
  v_custname := &custname ;
  select custage into v_custage
  from customer where custname = v_custname and custid = 73;
  if v_custage < 20 then
    update customer set custage = custage + 3 where custname = v_custname;
  elsif v_custage >20 and v_custage < 40 then
    update customer set custage = custage + 1 where custname = v_custname;
  else
    dbms_output.put_line('not in add age area!');
  end if;
exception
  when no_data_found then
    dbms_output.put_line('no customer found!');
end;

----case:单值，等值比较：case后接表达式，when后接具体的值,input:'A'
declare
str varchar2(1);
begin
  str := &str;
  case str
    when 'A' then
      dbms_output.put_line('A:Good Job');
    when 'B' then
      dbms_output.put_line('B:So So');
    when 'C' then
      dbms_output.put_line('C:Come On');
  end case;
end;

----case:范围，条件比较：case后接when语句，when后接条件表达式,input:56.78
declare
num number(6,2);
begin
  num := &num;
  case 
    when num < 60 then
      dbms_output.put_line('Failure');
    when num < 80 then
      dbms_output.put_line('Good');
    when num < 100 then
      dbms_output.put_line('Perfect');
  end case;
  exception
  when case_not_found then
    dbms_output.put_line('no suit case:' || sqlerrm);
end;

----case expression:as part of a sentence
----in assignment expression
declare
  num number(5);
  val varchar2(50);
begin
  num := &num;
  val := case num
    when 1 then 'First'
    when 2 then 'Second'
    when 3 then 'Third'
    else 'No'
    end || ' Group';
  dbms_output.put_line(val);
end;

----in select expression
select * from customer where custname = '彭海燕'
declare
  str varchar2(50);
begin
  select case
    when custage between 1 and 18 then
      'Girl'
    when custage between 24 and 40 then
      'Woman'
    else
      'GrandMother'
    end param into str from customer where custname = '彭海燕' and custid = 96;
  dbms_output.put_line(str);
end;
        
select case
  when custage between 1 and 18 then
    'Girl'
  when custage between 24 and 40 then
    'Woman'
  else
    'GrandMother'
  end Person
  from customer

----goto null:<<lable>>后不能直接跟exception这类关键字类的语句，要用null把标签隔开,类似的关键字还有endloop之类
declare
num number(5) := &num;
begin
  if num < 5 then
    goto labelParam;
  else
    dbms_output.put_line('nothing');
    null;
  end if;
  dbms_output.put_line('welcome to you!');
  <<labelParam>>
    dbms_output.put_line('less than five!');
end;

----loop,while,for:common in cycle expression:they are make up of loop end l