oracle 中ceiling,Oracle常用函数（1）

CEIL

语法ceil::=CEIL(n)

功能

返回大于或等于n的最小的整数。

举例

下面例子中，将返回大于或等于15.7的最小的整数：

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling

----------

16

语法floor::=FLOOR(n)

功能

返回小于或等于n的最大的整数。

举例

下面的例子中，将返回小于或等于15.7的最大整数：

SELECT FLOOR(15.7) "Floor" FROM DUAL;

Floor

----------

15

GREATEST

语法greatest::= GREATEST( expr [, expr]... )

功能

返回输入的exprs中的最大值。第一个expr之后的所有的exprs隐含地被转换成第一个expr的数据类型。采用非填充方式(nonpadded)进行比较。字符串之间的比较基于数据库字符集中该字符的值(ASCII码)。依次比较时，如果一个字符串中含有比另一个字符串中ASCII码大的字符，则这个字符串较大。当返回值是字符类型数据时，返回值的数据类型总是VARCHAR2.

举例

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')

"Greatest" FROM DUAL;

Greatest

--------

HARRY

LEAST

语法least::= LEAST( expr [, expr]... )

功能

与GREATEST函数正好相反，返回exprs中的最小值。其余要求与GREATEST函数相同。

举例

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"

FROM DUAL;

LEAST

------

HAROLD

POWER

语法power::= POWER( m , n )

功能

返回m的n次乘积。基数m和指数n可以是任意数值，但当m是负数时，n必须是整数。

举例

下面的例子中，计算3的平方：

SELECT POWER(3,2) "Raised" FROM DUAL;

Raised

----------

9

LENGTH

语法length::={ LENGTH | LENGTHB | LENGTHC | LENGTH2 | LENGTH4 } ( char )

功能

返回字符串的长度。LENGTH函数使用输入的字符集来计算长度；LENGTHB使用字节计算；LENGTHC使用Unicode计算；LENGTH2使用UCS2 codepoints计算；LENGTH4使用UCS4 codepoints计算。

字符串char可以是下列类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB。返回值是NUMBER类型。若char是CHAR类型，则返回值也会计算char中包含的空格；如果char是NULL，则该函数返回NULL。

举例

下面例子中，使用单字节数据库字符集(single-byte database character set)计算LENGTH：

SELECT LENGTH('CANDIDE') "Length in characters"

FROM DUAL;

Length in characters

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

7

下面的例子中，使用双字节数据库字符集(double-byte database character set)：

SELECT LENGTHB ('CANDIDE') "Length in bytes"

FROM DUAL;

Length in bytes

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

14

而我们使用的数据库，一般都是中文字符集(ZHS16GBK或ZHS16GB2312),在这种字符集中，一个汉字占用2个字节：

SQL> select length('田') from dual;

LENGTH('田')

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

1

SQL> select lengthb('田') from dual;

LENGTHB('田')

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

2

INSTR

语法instr::={ INSTR | INSTRB | INSTRC | INSTR2 | INSTR4 } ( string , substring [, position [, occurrence]] )

功能

从字符串string中查询子串substring。该函数根据查询规则返回一个整数，代表查询到的位置。INSTR返回的字符串以输入的字符集为准；INSTRB使用字节(bytes)计算；INSTRC使用Unicode字符集；INSTR2使用UCS2 codepoints。INSTR4使用UCS4 codepoints。

position是一个非零的整数，指明Oracle从string中查询的开始位置。如果position是负数，Oracle将从字符串string的末尾开始，倒着查询并返回相应位置的整数。

occurrence是一个正整数，指明从string查询第几次出现的substring。它的值必须是正数。

string和substring可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或者NCLOB。返回值类型为NUMBER。

position和occurrence的默认值都是1，表示Oracle将从string中的第一个位置开始查询第一次出现的substring位置。返回值是string的第一个位置的相对位置，而与position无关。如果查询失败(从string的第position个字符开始查询第occurrence次出现的substring，没查询到)，则返回值为0。

举例

下面的例子中，将从字符串'CORPORATE FLOOR'的第3个位置开始，查询第2次出现字符串'OR'的位置：

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)

"Instring" FROM DUAL;

Instring

----------

14

在下面这个例子中，你也会看到从字符串'CORPORATE FLOOR'中查询子串'OR'，不过是倒着查。从字符串'CORPORATE FLOOR'的倒数第3个位置开始查询第2次出现子串'OR'的位置：

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)

"Reversed Instring"

FROM DUAL;

Reversed Instring

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

2

下面的这个例子假定使用的是双字节数据库字符集(double-byte database character set)：

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"

FROM DUAL;

Instring in bytes

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

27

语法

substr::={ SUBSTR | SUBSTRB | SUBSTRC | SUBSTR2 | SUBSTR4 } ( string , position [, substring_length] )

功能

返回从position开始，长度为substring_length的字符串string的一部分。SUBSTR使用输入字符集的字符来计算长度；SUBSTRB使用字节计算长度；SUBSTRC使用Unicode；SUBSTR2使用UCS2 codepoints；SUBSTR4使用UCS4 codepoints。

如果position是0,那么它被看做1来计算。

如果position是正数，Oracle从string的开始位置计算。

如果position是负数, Oracle从string的末尾位置计算。

如果省略substring_length, Oracle返回从position开始直到string末尾的字符串。如果substring_length小于1,将返回NULL。

如果position的绝对值大于string的长度，则返回NULL。

string可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或者NCLOB.返回值数据类型与string相同。

举例

下面的几个例子中，将返回字符串"ABCDEFG"的几个子串：

SELECT SUBSTR('ABCDEFG',3,4) "Substring"

FROM DUAL;

Substring

---------

CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"

FROM DUAL;

Substring

---------

CDEF

假设在一个双字节数据库字符集(double-byte database character set)中：

SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"

FROM DUAL;

Substring with bytes

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

CD

REPLACE

语法

replace::=REPLACE( char , search_string [, replacement_string] )

功能

将char中存在的search_string全部替换为repalcement_string，然后返回替换后的新字符串。如果省略replacement_string或replacement_string是NULL，则char中的所有search_string被删除；若search_string是NULL，则返回char。

char, search_string和replacement_string可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB.返回值为VARCHAR2类型，并且与char有相同的字符集。

REPLACE函数的功能由TRANSLATE函数提供。TRANSLATE提供了单字符、一个对一个的替换功能，而REPLACE可以使用一个子串来替换另一个字符串中的子串，或者删除字符串中的指定子串。

举例

下面的例子中，将使用"BL"替换字符串中的"J"：

SELECT REPLACE('JACK and JUE','J','BL') "Changes"

FROM DUAL;

Changes

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

BLACK and BLUE

语法

upper::=UPPER( char )

功能

返回char的大写字符串。char可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB.返回值类型与char相同。

举例

下面的例子中，将返回一个字符串的大写形式：

SELECT UPPER('Large') "Uppercase"FROM DUAL;Upper-----LARGE

LOWER

语法

lower::=LOWER( char )

功能

返回char的小写字符串。char可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB.返回值类型与char相同。

举例

下面的例子中，将返回一个字符串的小写形式：

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"FROM DUAL;Lowercase--------------------mr. scott mcmillan

INITCAP

语法initcap::= INITCAP( char )

功能

返回字符串类型，字符串的第一个字符大写，其余字符小写。使用空格(或非字母及数字的字符)区分不同的字符串。

该字符串可以是下列类型：CHAR, VARCHAR2, NCHAR,或NVARCHAR2。返回值类型与输入值类型相同。

举例

SELECT INITCAP('the soap') "Capitals" FROM DUAL;

Capitals

---------

The Soap

LPAD

语法lpad::= LPAD( char1 , n [, char2] )

功能

使用字符串char2中的字符左填充字符串char1，使char1达到指定长度n。若不指定char2，则默认使用空格填充。若字符串char1的长度大于n，则该函数将截短char1，使之达到长度n。

char1和char2可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,以及NCLOB。返回字符串将是VARCHAR2类型，并且与char1的字符集相同。

参数n是返回值的总长度，就像在你的终端屏幕上看到的那样。在很多字符集中，它也是返回值中字符的个数。不过，在一些多字节(multibyte)字符集中，终端屏幕上显示的字符串总长度会与该字符串中的字符个数不同。

举例

下面的例子中将使用字符“*”和“.”来左填充一个字符串：

SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL;

LPAD example

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

*.*.*.*.*Page 1

RPAD

语法

rpad::=RPAD( char1 , n [, char2] )

功能

与LPAD函数类似，不过是在字符串char1的右侧进行填充。

举例

下面的例子中，使用字符串“ab”右填充一个名字，使它达到12个字符长度：

SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL;

RPAD example

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

MORRISONabab

语法

ltrim::=LTRIM( char [, set] )

功能

从char的左侧开始扫描，删除在set中存在的所有字符，直到出现第一个不在set中存在的字符。返回删除后的新字符串。若char中的所有字符在set中都存在，则返回NULL。set的默认值是空格字符。

char和set可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB。返回值是VARCHAR2类型，并且与char的字符集相同。

举例

下面的例子中，将从一个字符串中删除最左边的“x”和“y”：

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"

FROM DUAL;

LTRIM example

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

XxyLAST WORD

语法

rtrim::=RTRIM( char [, set ] )

功能

与LTRIM相同，只是从char右侧开始删除set中存在的字符，直到第一个不在set中存在的字符为止。

举例

下面的例子中，将从一个字符串中删除最右边的“x”和“y”：

SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"

FROM DUAL;

RTRIM examp

-----------

BROWNINGyxX

语法trim::=TRIM( [{ { LEADING | TRAILING | BOTH } [trim_character]) | trim_character }FROM ] trim_source )

功能

可以从开头或结尾(或同时)整理字符串。

如果指定LEADING，Oracle将从trim_source的第一个字符开始向后扫描，删除所有与trim_character相等的字符。

如果指定TRAILING，Oracle将从trim_source的最后一个字符开始向前扫描，删除所有与trim_character相等的字符。

如果指定BOTH(或不指定这三个)，Oracle将从trim_source的开头和结尾同时删除所有与trim_character相等的字符。

如果不指定trim_character，它的默认值是空格字符。

如果只指定trim_source，Oracle将删除trim_source头尾的空格。

返回值为VARCHAR2类型，其最大长度为trim_source的长度。

如果trim_source或trim_character为NULL，返回值为NULL。

trim_character和trim_source可以是下列数据类型：CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,或NCLOB.返回值类型为VARCHAR2，并且与trim_source的字符集相同。

举例

下面的这个例子中，将删除一个数字中头尾的0：

SELECT TRIM (0 FROM 0009872348900) "TRIM Example"

FROM DUAL;

TRIM Example

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

98723489

语法

to_number::=TO_NUMBER( char [, fmt [, 'nlsparam']] )

功能

将包含数值信息的字符串char按照指定格式fmt转换成数值类型的数据。char可以是下列数据类型：CHAR, VARCHAR2, NCHAR,或NVARCHAR2。

举例

下面的小例子中，将一个字符串按照指定格式转换成了数值：

UPDATE employees SET salary = salary +

TO_NUMBER('100.00', '9G999D99')

WHERE last_name = 'Perkins';

参数 'nlsparam'的功能与TO_CHAR(number)中相同。

SELECT TO_NUMBER('-AusDollars100','L9G999D99',

' NLS_NUMERIC_CHARACTERS = '',.''

NLS_CURRENCY= ''AusDollars''

') "Amount"

FROM DUAL;

Amount

----------

-100

TO_DATE

语法

to_date::=TO_DATE( char [, fmt [, 'nlsparam']] )

功能

将以下类型的数据转换成日期类型：CHAR, VARCHAR2, NCHAR,或NVARCHAR2。 参数fmt指定了char的日期格式。如果省略fmt，则char必须和默认的日期格式相同。如果fmt是'J'，for Julian, char必须是整数(integer)。

注意：

该函数并不能转换成其他的日期类型。如果要转换成其他的日期类型需要使用如下函数： TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL,或 "TO_YMINTERVAL".

默认的日期格式由初始化参数NLS_TERRITORY隐含指定，你也可以通过显式地设置参数NLS_DATE_FORMAT来指定。

参数 'nlsparam'的功能和函数TO_CHAR(datetime)中的功能相同。

char参数不能是日期类型(DATE)。返回值的前两位数字可能与最初的char不同(由fmt或默认的日期格式决定)。

举例

下面的例子中，将一个字符串转换成日期类型数据：

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

TO_DATE('

---------

15-JAN-89

如果设置参数NLS_TERRITORY为'AMERICA'，返回值将使用默认的日期格式。不同的NLS_TERRITORY值指定的默认日期格式不同：

ALTER SESSION SET NLS_TERRITORY = 'KOREAN';

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

TO_DATE(

--------

89/01/15