oracle常用函数总结(2)

month10 number(10,2) --10月销售金额 month11 number(10,2) --11月销售金额 month12 number(10,2) --12月销售金额

结构转化的SQL语句为: create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12) as select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)), sum(decode(substrb(month,5,2),'02',sell,0)), sum(decode(substrb(month,5,2),'03',sell,0)), sum(decode(substrb(month,5,2),'04',sell,0)),

补充1：

有学生成绩表student,现在要用decode函数实现以下几个功能：成绩>85,显示优秀；>70显示良好；>60及格；否则是不及格。 假设student的编号为id,成绩为score,那么：

select id, decode(sign(score-85),1,'优秀',0,'优秀',-1, decode(sign(score-70),1,'良好',0,'良好',-1,

decode(sign(score-60),1,'及格',0,'及格',-1,'不及格'))) from student；

补充2：

Decode函数的语法结构如下:

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n) decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

以下是一个简单测试，用于说明Decode函数的用法:

SQL> create table t as select username,default_tablespace,lock_date from dba_users; Table created.

SQL> select * from t;

USERNAME DEFAULT_TABLESPACE

LOCK_DATE

------------------------------ ------------------------------ --------- SYS SYSTEM SYSTEM SYSTEM OUTLN SYSTEM CSMIG SYSTEM SCOTT SYSTEM EYGLE USERS DBSNMP SYSTEM

WMSYS SYSTEM 20-OCT-04

8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked','locked') status from t; USERNAME STATUS ------------------------------ --------

SYS unlocked SYSTEM unlocked OUTLN unlocked CSMIG unlocked SCOTT unlocked EYGLE unlocked DBSNMP unlocked WMSYS locked 8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked') status from t; USERNAME STATUS ------------------------------ --------