Oracle常用函数

 

一、row_number() over()

资料甲

1 select column_name１,column_name,data_type,

 2 row_number() over (partition by column_name１ order by column_name) row_num

 3 * from test_col where rownum < 20

partition by相当与Group by，如果有了Partition by则Oralce会先分组，且Row_number是以每一组为单位，且在一组内进行标识顺序的

可以使用partition by 字句,这样row_number就分类做出排名

如果不指定partition就相当于在order by的字句上进行排序了

资料乙

"ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序

而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

 RANK() 类似，不过RANK 排序的时候跟派名次一样，

可以并列2个第一名之后 是第3名LAG 表示 分组排序后 ，

组内后面一条记录减前面一条记录的差，

第一条可返回 NULL BTW: EXPERT ONE ON ONE

上讲的最详细,还有很多相关特性，

文档看起来比较费劲row_number()和rownum差不多

，功能更强一点（可以在各个分组内从1开时排序）rank()是跳跃排序

，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，

有两个第二名时仍然跟着第三名。

相比之下row_number是没有重复值的 lag（arg1,arg2,arg3):

arg1是从其他行返回的表达式

rg2是希望检索的当前行分区的偏移量。

是一个正的偏移量，时一个往回检索以前的行的数目。

 arg3是在arg2表示的数目超出了分组的范围时返回的值。 "

 

二、NVL()

NVL (expr1, expr2)

    如果expr1为NULL，返回expr2

NVL2 (expr1, expr2, expr3)

    如果expr1为非空，则返回expr2，如果expr1为NULL，则返回expr3

NULLIF (expr1, expr2)

    如果expr1=expr2，返回NULL，否则返回expr1

 

三、CASE WHEN THEN

语法：

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

在简单的CASE表达式中，ORACLE会搜索expr 等于comparison_expr的第一对when then，然后返回return_expr.如果没有满足条件的when then 并且存在ELSE子句，那么ORACLE会返回else_expr，否则会返回空值。

您不能为所有这些return_expr和else_expr指定文字的NULL。

 

四、DECODE()

DECODE有什么用途呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

　　decode()函数使用技巧　　

　　·含义解释:

　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

　　该函数的含义如下:

　　IF 条件=值1 THEN

　　RETURN(翻译值1)

　　ELSIF 条件=值2 THEN

　　RETURN(翻译值2)

　　......

　　ELSIF 条件=值n THEN

　　RETURN(翻译值n)

　　ELSE

　　RETURN(缺省值)

　　END IF

　　· 使用方法:

　　1、比较大小

　　select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

　　sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

　　例如:

　　变量1=10，变量2=20

　　则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

　

五、||

Oracle用|| 符号作为连接符，　Oracle查询如下所示：

Select ‘Name’ || ‘Last Name’ From tableName

查询结果中name和last name连接在一起了

 

六、CONCAT()

拼接 CONCAT('Good', 'String')     GoodString

 

七、LENGTH()

求长度   LENGTH('String')   6

 

八、TO_CHAR()

TO_CHAR 将数字或日期转换为字符串
TO_CHAR(date, 'fmt')

格式串包含在''中, 大小写敏感, 可以是任意有效的日期

格式

格式串：

YYYY 表示完整的四位数字年

YEAR 表示英文拼写的年

MM 表示两位数字月

MONTH 表示英文全拼的年

DY 表示三位缩写的星期

DAY 表示英文全拼的星期

 

一些特殊用法：

时间格式

            HH24:MI:SS AM 15:45:32 PM

在格式串中加入字符串

            DD " of " MONTH 12 of OCTOBER

用后缀拼出整个日期

            ddspth fourteenth

 

TO_CHAR(number, 'fmt')

使用此函数将数字转换成数字

    9 表示数字

    0 强制为0

    $ 设置美元符号

    L 使用当前字符集的货币符号

    . 小数点

    , 千位分隔符