oracle函数总结

1、rpad函数

rpad函数从右边对字符串使用指定的字符进行填充 　　
rpad(string,padded_length,[pad_string]) 　　
string 表示：被填充的字符串 　　
padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符; 　　
pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。 　　
例如： 　　
rpad('tech', 7); 将返回'tech ' 　　
rpad('tech', 2); 将返回'te' 　　
rpad('tech', 8, '0'); 将返回'tech0000' 　　
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net' 　　
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'

SQL> select rpad('aa',5)||decode('bbb',null,' ',rpad('bbb',8))||rpad('cccc',12) from dual;
 
RPAD('AA',5)||DECODE('BBB',NUL
------------------------------
aa   bbb     cccc

 

 

2、decode函数

在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
需要注意的是，这里的if、then及else 都可以是函数或计算表达式。
含义解释： 
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

 

3、to_number,to_char函数

to_number(文字,’格式’)
to_char(数字, ‘格式’)
to_char()这次只介绍to_char(数字, ‘格式’)，明天介绍日期转化的。

主要格式如下：
9 表示有效位数
0 数字前用0补足位数
B 用空格补足位数
$ 在数字前加美元号
L 在数字前加本地货币符号
. 小数点位置
, 分格符位置
MI 负数时在最后边加负号
PR 负数用<>括起来
S 在前面加符号
EEEE 科学计数法
V 把原数字乘以10的N次方(N为V后面的位数)
to_number函数如下：

Converts a string to the NUMBER data type	TO_NUMBER(<value>[, <format>, <NLS parameter>]) RETURN NUMBER
	CREATE TABLE test ( testcol VARCHAR2(10)); INSERT INTO test VALUES ('12345.67'); SELECT TO_BINARY_DOUBLE(testcol) BIN_DOUBLE, TO_BINARY_FLOAT(testcol) BIN_FLOAT, TO_NUMBER(testcol) NMBR FROM test;
Converts a HEX number to FLOAT	TO_NUMBER(<value>, <format>);
	SELECT TO_NUMBER('0A', 'XX') FROM dual;
Converts a HEX number to DECIMAL	TO_NUMBER(<binary_float | binary_double | number>, '<hex mask>') RETURN <binary_float | binary_double | number>;
	SELECT TO_NUMBER(100000,'XXXXXXXX') FROM dual;

在垃圾短信项目中，用到了to_number方法，表中starttime字段存的是某一时间到1994年1月1日 00:00:00的秒数，字段类型为RAW，由于项目的原因，要涉及到改字段的时间比较。考虑了半天，将某一时间到1994年1月1日 00:00:00的秒数传入当做参数，然后将starttime转换成16进制，再通过to_number转换成数字，然后再比较。SQL语句如下：

select * from black_user_info where TO_NUMBER(rawtohex(starttime),'XXXXXXXX') >496022400;

但是经过测试发现 select * from black_user_info where TO_NUMBER(starttime,'XXXXXXXX') >496022400   好像也有同样的效果。raw类型和16进制有很密切的关系，因为在数据库里面，数字的raw类型就是按照16进制保存的，具体的关系就不是很清楚了，目前基本上一个ORACLE盲。

1. 9， 0 ，B 测试

SELECT

    to_char(123.45, '0009.9'),

    to_char(123.45, '9999.9'),

    to_char(123.45, 'B999.9')

FROM dual;

结果为

TO_CHAR(123.45,'0009.9') TO_CHAR(123.45,'9999.9') TO_CHAR(123.45,'B999.9')

------------------------ ------------------------ ------------------------

 0123.5                    123.5                   123.5

2. $， L 测试

SELECT

    to_char(123.45, '$9999.9'),

    to_char(123.45, 'L9999.9')

FROM dual;

结果为

TO_CHAR(123.45,'$9999.9') TO_CHAR(123.45,'L9999.9')

------------------------- -------------------------

  $123.5                           ￥123.5

3. .，, 测试

SELECT

    to_char(123.45, '9999.99'),

    to_char(1233123.45, '9,999,999.99')

FROM dual;

结果为

TO_CHAR(123.45,'9999.99') TO_CHAR(1233123.45,'9,999,999.

------------------------- ------------------------------

  123.45                   1,233,123.45

4. MI，PR 测试

SELECT

    to_char(-123.45, '9999.99MI'),

    to_char(-123.45, '9999.99PR')

FROM dual;

结果为

TO_CHAR(-123.45,'9999.99MI') TO_CHAR(-123.45,'9999.99PR')

---------------------------- ----------------------------

 123.45-                      <123.45>

5. S，EEEE，V 测试

SELECT

    to_char(123.45, 'S9999.99'),

    to_char(12345, '99.99EEEE'),

    to_char(123, '9999V99')

FROM dual;

结果为

TO_CHAR(123.45,'S9999.99') TO_CHAR(12345,'99.99EEEE') TO_CHAR(123,'9999V99')

-------------------------- -------------------------- ----------------------

 +123.45                     1.23E+04                   12300