Oracle系列之二----Exception And Expression
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'declarestr 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.78declarenum number(6,2);begin 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 expressiondeclare num number(5); val varchar2(50);begin 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 expressionselect * 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之类declarenum number(5) := #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 loop----loop:cycle run one time at lease; create table temp( tid number(6) primary key, tname varchar2(10))declarei number(6) := 1;begin loop insert into temp values (i,'value' || i); exit when i = 10; i := i + 1; end loop; dbms_output.put_line('Data insert complete!');end;select * from temp;----whiledrop table temp;create table temp( tid number(6) primary key, tname varchar2(10))declarei number(6) := 1;begin while i <= 10 loop insert into temp values(i,'value' || i); i := i + 1; end loop ----commit;end;select * from temp;----for:cycle time is ?a constantdeclarei number(5);j number(5);begin for i in reverse 1..10 loop for j in 1..i loop dbms_output.put('*'); end loop; dbms_output.put_line(''); end loop;end;----pre-defined exceptionselect * from customer where custname = '彭海燕' and custid = 96declare v_custage char(1);begin select custage into v_custage from customer where custname = '彭海燕'; exception when too_many_rows then dbms_output.put_line('more than one result:' || sqlerrm); when no_data_found then dbms_output.put_line('no user in db:' || sqlerrm); when others then dbms_output.put_line('unknown exception:' || sqlerrm);end;----unpre-defined exception:select * from customer where custname = '彭海燕' and custid = 96;declare e_integrity exception; pragma exception_init(e_integrity,-2291);begin update customer set custage = custage + 2 where custname = '彭海燕' and custid = 96; exception when e_integrity then dbms_output.put_line('no this user:' || sqlerrm); when others then dbms_output.put_line('no this user:' || sqlcode || ' ' || sqlerrm);end;----self-definitiondeclareex exception;begin update customer set custage = custage + 2 where custname = '彭海燕 Exception'; if sql%notfound then raise ex; end if; exception when ex then dbms_output.put_line('no this user:' || sqlerrm); ----throw self-definition raise_application_error(-20001,'this user is not exist in db!'); when others then dbms_output.put_line('more than one result or unknown exception:' || sqlerrm);end;create or replace procedure pro_self_exceptionasex exception;begin update customer set custage = custage + 2 where custname = '彭海燕 Exception'; if sql%notfound then raise ex; end if; exception when ex then dbms_output.put_line('no this user:' || sqlerrm); ----throw self-definition raise_application_error(-20001,'this user is not exist in db!'); when others then dbms_output.put_line('more than one result or unknown exception:' || sqlerrm);end; declare e_integrity exception; pragma exception_init(e_integrity,-20001);begin pro_self_exception; exception when e_integrity then dbms_output.put_line('this user is not exist in db!');end;?
Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868
?
?
?
?
?
?
?
?
?
?
?
?