Oracle+functions+detail

系统函数2.13.7Match、MatchW

http://www.cnblogs.com/ciyan/p/5119022.html

= =

[|oracle函数详解]
> **1. 提高开发人员产能** — 开发人员可以通过更清晰、更简洁的 SQL 代码执行复杂分析. 现在可以使用一条 SQL 语句表示复杂任务，编写和维护速度更快、效率更高.
 * ====Oracle 数据库中 SQL 分析的主要优势 ==== ||
 * Oracle 数据库中分析功能和特性提供以下主要优势：

> **2. 提高查询速度** — 数据库中分析支持的处理优化可大幅提高查询性能. 以前需要自联接或复杂过程处理的操作现在可以用原生 SQL 执行.

> **3. 改善可管理性** — 应用程序共享一个公共的关系环境，而非数据结构不兼容的计算引擎组合，因此可以轻松访问所有数据类型和资源整合视图

> **4. 学习负担极小** — SQL 分析函数需要学习的新关键字极少，因为其语法利用了众所周知的已有关键字.

> **5. 基于行业标准的语法** — Oracle 的特性符合 ANSI SQL 标准，受到大量独立软件供应商的支持. || > || ** 排名
 * ==== 概述 ==== ||
 * Oracle Database 中内嵌的数据库中分析函数特性可用于回应各种业务问题. 开发人员和业务用户可以访问各种分析特性，并将其结果与其他 SQL 查询和分析管道结合，获得更深入的洞察. Oracle 数据库包括以下特性：
 * 窗口化
 * 报表聚合
 * LAG/LEAD
 * FIRST/LAST
 * 逆百分位数 || ** <span style="background-color: rgba(255,255,255,0);">假设性排名和分布
 * <span style="background-color: rgba(255,255,255,0);">模式匹配
 * <span style="background-color: rgba(255,255,255,0);">建模
 * <span style="background-color: rgba(255,255,255,0);">高级聚合
 * <span style="background-color: rgba(255,255,255,0);">用户定义的函数 ||

<span style="background-color: rgba(255,255,255,0);">ABS <span style="background-color: rgba(255,255,255,0);">语法: <span style="background-color: rgba(255,255,255,0);">ABS ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的绝对值. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n为数值数据类型，或者可以隐式转化为数值类型的其他数据类型. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECTABS(-12345.6789) result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">12345.6789

<span style="background-color: rgba(255,255,255,0);">ACOS <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ACOS ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的反余弦值. n的值必须在-1到1之间；返回值的范围从0到pi. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ACOS(-1)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">3.14159265

<span style="background-color: rgba(255,255,255,0);">ADD_MONTHS <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ADD_MONTHS ( date,integer ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回date加上integer个月后的日期. 一个月是由会话(session)参数NLS_CALENDAR来定义的. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">date是一个日期时间值或可以隐式转化为DATE的其他值. integer是一个整数或其他可以转化为整数的值. 返回值永远是DATE数据类型. 如果date是一个月的最后一天，或者得到结果的月份的天数比date所在月份的天数时，返回结果是结果月份的最后一天. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECTTO_CHAR(ADD_MONTHS(TO_DATE('**2011-01-31**','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2011-02-28

<span style="background-color: rgba(255,255,255,0);">SELECTTO_CHAR(ADD_MONTHS(TO_DATE('**2011-01-30**','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2011-02-28

<span style="background-color: rgba(255,255,255,0);">SELECTTO_CHAR(ADD_MONTHS(TO_DATE('**2011-01-27**','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2011-02-27

<span style="background-color: rgba(255,255,255,0);">SELECTTO_CHAR(ADD_MONTHS(TO_DATE('2011-01-31','YYYY-MM-DD'),**1.9**),'YYYY-MM-DD') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2011-02-28

<span style="background-color: rgba(255,255,255,0);">ASCII <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ASCII ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回char的**第一个字符**在数据库字符集中的十进制表示. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，或者能转化成它们的其他数据类型. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ASCII('甲骨文') result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 42450

<span style="background-color: rgba(255,255,255,0);">SELECTASCII('Oracle') result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 79

<span style="background-color: rgba(255,255,255,0);">ASCIISTR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ASCIISTR ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回字符串char在数据库字符集中的ASCII版本. 非ASCII字符将会转化成\XXXX的格式，其中XXXX是UTF-16编码单元. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ASCIISTR('甲骨文') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">\7532\9AA8\6587

<span style="background-color: rgba(255,255,255,0);">SELECTASCIISTR('Oracle') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">Oracle

<span style="background-color: rgba(255,255,255,0);">ASIN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ASIN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的反正弦值. <span style="background-color: rgba(255,255,255,0);">n必须在**-1到1**之间. 返回值的范围在**-pi/2到pi/2**. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ASIN(1)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">1.57079633

<span style="background-color: rgba(255,255,255,0);">ATAN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ATAN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的反正切值. n的取值**没有范围**；而返回值的范围在**-pi/2到pi/2**之间. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ATAN(1)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">.785398163

<span style="background-color: rgba(255,255,255,0);">ATAN2 <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ATAN2 ( n1, n2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n1与n2的反正切值. 等同于ATAN(n1/n2). <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 如果n是BINARY_FLOAT，则返回值的类型是BINARY_DOUBLE，否则返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT ATAN2(1,0)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">1.57079633

<span style="background-color: rgba(255,255,255,0);">SELECT ATAN2(-.1,0)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">-1.5707963

<span style="background-color: rgba(255,255,255,0);">AVG <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">AVG ( [DISTINCT|ALL]expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回expr的平均值. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr可以为任意数值类型或可以转化为数值类型的其他类型. 返回值的数据类型与expr的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT AVG(DISTINCTsal) result FROM scott.emp; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2064.58333

<span style="background-color: rgba(255,255,255,0);">SELECT AVG(ALL sal)result FROM scott.emp; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2073.21429

<span style="background-color: rgba(255,255,255,0);">BFILENAME <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">BFILENAME ('directory', 'filename' ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个关于directory和filename的BFILE定位器. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">directory是一个DIRECTORY对象. filename是服务器文件系统的一个文件名. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">作为SYSDBA <span style="background-color: rgba(255,255,255,0);">CREATE DIRECTORYMEDIA_DIR AS '/home/oracle'; <span style="background-color: rgba(255,255,255,0);">GRANT READ ONDIRECTORY MEDIA_DIR TO TEST; <span style="background-color: rgba(255,255,255,0);">作为TEST <span style="background-color: rgba(255,255,255,0);">CREATE TABLEtest_bfile (id NUMBER, bfiles BFILE); <span style="background-color: rgba(255,255,255,0);">INSERT INTOtest_bfile VALUES (1, BFILENAME('MEDIA_DIR', 'uname.txt')); <span style="background-color: rgba(255,255,255,0);">COMMIT;

<span style="background-color: rgba(255,255,255,0);">BIN_TO_NUM <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">BIN_TO_NUM ( expr [,expr, ...] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字节向量转化成等值的NUMBER. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">每个expr不是0就是1. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECTBIN_TO_NUM(1,1,0,0) result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 12

<span style="background-color: rgba(255,255,255,0);">BITAND <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">BITAND ( expr1,expr2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回expr1与expr2进行位与运算的结果. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr1和expr2都是NUMBER，结果也是NUMBER. 如果其中一个参数为NULL，结果也为NULL. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT BITAND( <span style="background-color: rgba(255,255,255,0);"> BIN_TO_NUM(1,1,0,0), <span style="background-color: rgba(255,255,255,0);"> BIN_TO_NUM(1,0,1,1)) result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 8

<span style="background-color: rgba(255,255,255,0);">CARDINALITY <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CARDINALITY (nested_table ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回内嵌表中元素的个数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">返回值的类型是NUMBER. 如果内嵌表是空的，或者是一个null集合，则返回NULL. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">CREATE OR REPLACETYPE test_nestedtable AS TABLE OF VARCHAR2(20); <span style="background-color: rgba(255,255,255,0);">/ <span style="background-color: rgba(255,255,255,0);">CREATE TABLE test_nt(id NUMBER, nt test_nestedtable) NESTED TABLE nt STORE AS nt_tab; <span style="background-color: rgba(255,255,255,0);">INSERT INTO test_nt VALUES(1, test_nestedtable('hello', 'nested', 'table')); <span style="background-color: rgba(255,255,255,0);">COMMIT; <span style="background-color: rgba(255,255,255,0);">SELECT id, CARDINALITY(nt) FROM test_nt; <span style="background-color: rgba(255,255,255,0);"> ID CARDINALITY(NT) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);"> 1 3

<span style="background-color: rgba(255,255,255,0);">CAST <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CAST ( { expr | MULTISET ( subquery ) } AS type_name ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将一种内置的数据类型或集合类型转化成另一种内置的数据类型或集合类型. CAST不支持LONG，LONG RAW. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">数据类型可以为BINARY_FLOAT，BINARY_DOUBLE，CHAR，VARCHAR2，NUMBER，DATETIME，INTERVAL，RAW，ROWID，UROWID，NCHAR，NVARCHAR2. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT CAST(TO_DATE('2011/10/07','YYYY/MM/DD')AS TIMESTAMP WITH LOCAL TIME ZONE) result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">07-OCT-1112.00.00.000000 AM +08:00

<span style="background-color: rgba(255,255,255,0);">CREATE OR REPLACETYPE cust_address_typ <span style="background-color: rgba(255,255,255,0);"> AS OBJECT <span style="background-color: rgba(255,255,255,0);"> ( street_address VARCHAR2(40) <span style="background-color: rgba(255,255,255,0);">, postal_code VARCHAR2(10) <span style="background-color: rgba(255,255,255,0);"> , city VARCHAR2(30) <span style="background-color: rgba(255,255,255,0);"> , state_province VARCHAR2(10) <span style="background-color: rgba(255,255,255,0);"> , country_id CHAR(2) <span style="background-color: rgba(255,255,255,0);"> ); <span style="background-color: rgba(255,255,255,0);">/

<span style="background-color: rgba(255,255,255,0);">CREATE TYPE address_book_tAS TABLE OF cust_address_typ; <span style="background-color: rgba(255,255,255,0);">/

<span style="background-color: rgba(255,255,255,0);">CREATE TABLEcust_address ( <span style="background-color: rgba(255,255,255,0);"> custno NUMBER, <span style="background-color: rgba(255,255,255,0);"> street_address VARCHAR2(40), <span style="background-color: rgba(255,255,255,0);"> postal_code VARCHAR2(10), <span style="background-color: rgba(255,255,255,0);"> city VARCHAR2(30), <span style="background-color: rgba(255,255,255,0);"> state_province VARCHAR2(10), <span style="background-color: rgba(255,255,255,0);"> country_id CHAR(2));

<span style="background-color: rgba(255,255,255,0);">CREATE TABLEcust_short (custno NUMBER, name VARCHAR2(31));

<span style="background-color: rgba(255,255,255,0);">INSERT INTOcust_short VALUES (1, 'TEST');

<span style="background-color: rgba(255,255,255,0);">INSERT INTOcust_address VALUES (1, 'O STREET', '9900', 'ORACLE', 'DATABASE', 'CN');

<span style="background-color: rgba(255,255,255,0);">COMMIT;

<span style="background-color: rgba(255,255,255,0);">SELECT s.custno,s.name, <span style="background-color: rgba(255,255,255,0);"> CAST(MULTISET(SELECT ca.street_address,ca.postal_code, ca.city, ca.state_province, ca.country_id <span style="background-color: rgba(255,255,255,0);"> FROM cust_address ca <span style="background-color: rgba(255,255,255,0);"> WHERE s.custno = ca.custno) <span style="background-color: rgba(255,255,255,0);"> AS address_book_t) <span style="background-color: rgba(255,255,255,0);">FROM cust_short s <span style="background-color: rgba(255,255,255,0);">ORDER BY s.custno; <span style="background-color: rgba(255,255,255,0);">CUSTNO NAME RESULT(STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)

<span style="background-color: rgba(255,255,255,0);">-- --- <span style="background-color: rgba(255,255,255,0);"> 1 TEST ADDRESS_BOOK_T(CUST_ADDRESS_TYP(O STREET,9900,ORACLE,DATABASE,CN))

<span style="background-color: rgba(255,255,255,0);">CEIL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CEIL ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回大于或等于n的最小整数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT CEIL(1234.5)result FROM dual; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1235

<span style="background-color: rgba(255,255,255,0);">CHARTOROWID <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CHARTOROWID ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将CHAR，VARCHAR2，NCHAR或NVARCHAR2的数据类型转换成ROWID数据类型. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">select rowid, id fromtest_nt <span style="background-color: rgba(255,255,255,0);">wherechartorowid('AAAVLjAAEAAAADmAAA') = rowid; <span style="background-color: rgba(255,255,255,0);">ROWID ID <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">AAAVLjAAEAAAADmAAA 1

<span style="background-color: rgba(255,255,255,0);">CHR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CHR ( n [ USINGNCHAR_CS ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回拥有二进制编码n的字符，如果使用NCHAR_CS，就使用数据库字符集. 对于单字节字符集，如果n大于256，那么会先进行n mod 256取余数的操作. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n是一个NUMBER类型的值，或者可以转化成NUMBER的其他类型的值. 返回值是一个字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">select CHR(42450)from dual; <span style="background-color: rgba(255,255,255,0);">CHR(42450) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">甲

<span style="background-color: rgba(255,255,255,0);">COALESCE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">COALESCE ( { expr [,expr ] ... } ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回第一个非NULL的值，至少有两个expr. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">**expr的数据类型必须一致**. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">select COALESCE (null, 123, '123') from dual; <span style="background-color: rgba(255,255,255,0);">select COALESCE (null, 123, '123') from dual <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">Error at line 1 <span style="background-color: rgba(255,255,255,0);">ORA-00932:inconsistent datatypes: expected NUMBER got CHAR

<span style="background-color: rgba(255,255,255,0);">select COALESCE (null, 123, 456) from dual; <span style="background-color: rgba(255,255,255,0);">COALESCE(NULL,123,456) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 123

<span style="background-color: rgba(255,255,255,0);">COMPOSE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">COMPOSE ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将参数的字符串转化成相同字符集下的Unicode字符串. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char可以为CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT COMPOSE( 'a'|| UNISTR('\0308') ) FROM DUAL; <span style="background-color: rgba(255,255,255,0);">COMPOSE('A'||UNISTR('\0308')) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">ä

<span style="background-color: rgba(255,255,255,0);">CONCAT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CONCAT ( char1,char2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回值是char1连接char2，**效果与连接符||一样**. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char1和char2可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">select **CONCAT**('Hello ', 'Oracle') result fromdual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">Hello Oracle

<span style="background-color: rgba(255,255,255,0);">CONVERT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CONVERT ( char,dest_char_set [, source_char_set ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char从字符集source_char_set转成dest_char_set. CHAR和VARCHAR2的返回值是VARCHAR2；NCHAR和NVARCHAR2的返回值是NVARCHAR2；CLOB的返回值是CLOB；NCLOB的返回值是NCLOB. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB和NCLOB. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">select CONVERT('甲骨文', 'US7ASCII', 'ZHT16MSWIN950') result from dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--
 * <span style="background-color: rgba(255,255,255,0);">??? **

<span style="background-color: rgba(255,255,255,0);">CORR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CORR ( expr1, expr2) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一组数值对的相对系数. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">with tmp as <span style="background-color: rgba(255,255,255,0);">(select 'a' cd, 95n1, 86 n2 from dual <span style="background-color: rgba(255,255,255,0);">union all <span style="background-color: rgba(255,255,255,0);">select 'a' cd, 100n1, 95 n2 from dual <span style="background-color: rgba(255,255,255,0);">union all <span style="background-color: rgba(255,255,255,0);">select 'a' cd, 58n1, 65 n2 from dual) <span style="background-color: rgba(255,255,255,0);">select CORR(n1,n2)result from tmp group by cd; <span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">.982482592

<span style="background-color: rgba(255,255,255,0);">COS <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">COS ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的余弦值. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以转化成数值的其他类型. 如果n是BINARY_FLOAT，那么返回值是BINARY_DOUBLE；否则返回值类型与参数类型一样. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT COS(180* 3.14159265359 / 180) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> -1

<span style="background-color: rgba(255,255,255,0);">COUNT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">COUNT ( { * } | { [DISTINCT | ALL ] expr } ) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">返回查询结果的行数. 如果指定了expr，将返回expr不为NULL的行数. DISTINCT会排除重复的行；ALL包含重复的行. 指定‘*’时，会返回所有的行，包括重复的和NULL的行. COUNT永远不会返回NULL. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTCOUNT(*) FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);"> COUNT(*) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 14

<span style="background-color: rgba(255,255,255,0);">CURRENT_DATE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CURRENT_DATE <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回会话时区的当前日期时间（使用Gregorian日历）. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> ALTERSESSION SET **NLS_DATE_FORMAT** ='YYYY/MM/DD HH24:MI:SS';

<span style="background-color: rgba(255,255,255,0);">Session altered.

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SESSIONTIMEZONE CURRENT_DATE <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">+08:00 2011/10/10 15:14:18

<span style="background-color: rgba(255,255,255,0);">SQL> ALTERSESSION SET TIME_ZONE = '0:0';

<span style="background-color: rgba(255,255,255,0);">Session altered.

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SESSIONTIMEZONE CURRENT_DATE <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">+00:00 2011/10/10 07:14:43

<span style="background-color: rgba(255,255,255,0);">CURRENT_TIMESTAMP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">CURRENT_TIMESTAMP (precision ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回会话时区的当前日期和时间，包括小数秒. 返回值类型是TIMESTAMP WITH TIME ZONE. 如果没有指定precision，默认为6. LOCALTIMESTAMP和CURRENT_TIMESTAMP之间的区别是LOCALTIMESTAMP返回一个TIMESTAMP类型的值，而CURRENT_TIMESTAMP返回一个TIMESTAMP WITH TIME ZONE类型的值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(SESSIONTIMEZONE) result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> 2 UNION <span style="background-color: rgba(255,255,255,0);"> 3 SELECT TO_CHAR(CURRENT_TIMESTAMP) FROM DUAL <span style="background-color: rgba(255,255,255,0);"> 4 UNION <span style="background-color: rgba(255,255,255,0);"> 5 SELECTTO_CHAR(LOCALTIMESTAMP) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">+08:00 <span style="background-color: rgba(255,255,255,0);">10-OCT-1104.07.39.860798 PM <span style="background-color: rgba(255,255,255,0);">10-OCT-1104.07.39.860798 PM +08:00

<span style="background-color: rgba(255,255,255,0);">DBTIMEZONE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">DBTIMEZONE <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回数据库的时区，格式为‘[+|-]TZH:TZM’或者时区名. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT DBTIMEZONEFROM DUAL;

<span style="background-color: rgba(255,255,255,0);">DBTIME <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">+00:00

<span style="background-color: rgba(255,255,255,0);">DECODE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">DECODE ( expr, {search, result [, ...] } [, default] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">DECODE比较每个expr的值，如果expr等于其中一个search，那么返回相应的result. 如果没有匹配的值，返回default. 如果没有default，返回NULL. **在DECODE中，Oracle认为两个NULL是匹配的**，所以如果expr是NULL，就会返回第一个为NULL的search的相应的result. DECODE函数中，包括expr，search，result和default，一共可以有**255**个组件. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参数必须是数值或字符类型. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTENAME, DECODE(SIGN(SAL-4000),1,'RICH',0,'MIDDLE','LOW') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMSCOTT.EMP;

<span style="background-color: rgba(255,255,255,0);">ENAME RESULT <span style="background-color: rgba(255,255,255,0);">-- -- <span style="background-color: rgba(255,255,255,0);">SMITH LOW <span style="background-color: rgba(255,255,255,0);">ALLEN LOW <span style="background-color: rgba(255,255,255,0);">WARD LOW <span style="background-color: rgba(255,255,255,0);">JONES LOW <span style="background-color: rgba(255,255,255,0);">MARTIN LOW <span style="background-color: rgba(255,255,255,0);">BLAKE LOW <span style="background-color: rgba(255,255,255,0);">CLARK LOW <span style="background-color: rgba(255,255,255,0);">SCOTT LOW <span style="background-color: rgba(255,255,255,0);">KING RICH <span style="background-color: rgba(255,255,255,0);">TURNER LOW <span style="background-color: rgba(255,255,255,0);">ADAMS LOW

<span style="background-color: rgba(255,255,255,0);">ENAME RESULT <span style="background-color: rgba(255,255,255,0);">-- -- <span style="background-color: rgba(255,255,255,0);">JAMES LOW <span style="background-color: rgba(255,255,255,0);">FORD LOW <span style="background-color: rgba(255,255,255,0);">MILLER LOW

<span style="background-color: rgba(255,255,255,0);">DEREF <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">DEREF ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回参数expr的对象引用，**expr必须返回一个对象的REF**. 如果在查询中没有使用这个函数，Oracle会返回该REF的对象ID. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> CREATE TYPEtest_ref_typ_new AS OBJECT <span style="background-color: rgba(255,255,255,0);"> 2 (idNUMBER, name VARCHAR2(20)); <span style="background-color: rgba(255,255,255,0);"> 3 /

<span style="background-color: rgba(255,255,255,0);">Type created.

<span style="background-color: rgba(255,255,255,0);">SQL> CREATE TABLEtest_ref OF test_ref_typ_new;

<span style="background-color: rgba(255,255,255,0);">Table created. <span style="background-color: rgba(255,255,255,0);">SQL> CREATE TABLEtest (test_id NUMBER, tr REF test_ref_typ_new SCOPE IS test_ref);

<span style="background-color: rgba(255,255,255,0);">Table created.

<span style="background-color: rgba(255,255,255,0);">SQL> INSERT INTOtest_ref VALUES (1, 'TEST');

<span style="background-color: rgba(255,255,255,0);">1 row created.

<span style="background-color: rgba(255,255,255,0);">SQL> COMMIT;

<span style="background-color: rgba(255,255,255,0);">Commit complete.

<span style="background-color: rgba(255,255,255,0);">SQL> INSERT INTOtest select 101, REF(a) FROM test_ref a;

<span style="background-color: rgba(255,255,255,0);">1 row created. <span style="background-color: rgba(255,255,255,0);">SQL> commit;

<span style="background-color: rgba(255,255,255,0);">Commit complete.

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT trFROM test;

<span style="background-color: rgba(255,255,255,0);">TR <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">0000220208AEEEFD47B1A256F7E040E10A4A0B0F89AEEEFD47B1A156F7E040E10A4A0B0F89

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTDEREF(tr) FROM test;

<span style="background-color: rgba(255,255,255,0);">DEREF(TR)(ID, NAME) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">TEST_REF_TYP_NEW(1,'TEST')

<span style="background-color: rgba(255,255,255,0);">DUMP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">DUMP ( expr [,return_fmt [, start_position [, length ] ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回expr的数据类型编码，字节长度和内部表示. 它总是以数据库字符集来返回结果的. return_fmt有以下类型 <span style="background-color: rgba(255,255,255,0);">8表示8进制 <span style="background-color: rgba(255,255,255,0);">10表示10进制 <span style="background-color: rgba(255,255,255,0);">16表示16进制 <span style="background-color: rgba(255,255,255,0);">17表示如果能解析成可打印的字符则返回字符，否则返回16进制码. <span style="background-color: rgba(255,255,255,0);">默认地，返回值不包含字符集的信息. 要显示字符集信息，在上面代码的基础上加上1000. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">start_position表示开始位置；length表示要解析的长度. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTDUMP('abc', **1016**) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3 **CharacterSet=ZHT16MSWIN950**: 61,62,63

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTDUMP('abc', 17) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3: a,b,c

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTDUMP('abc', 8, 2, 1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3: 142

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT DUMP('abc',9,2,1)result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3: 98

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTDUMP('abc',10,2,1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3: 98

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTDUMP('abc',16,2,1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">Typ=96 Len=3: 62

<span style="background-color: rgba(255,255,255,0);">EXP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">EXP ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回自然对数e的n次方. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXP(1) result1, EXP(2) result2 FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT1 RESULT2 <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">2.71828183 7.3890561

<span style="background-color: rgba(255,255,255,0);">EXTRACT (datetime) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">EXTRACT ( { YEAR |MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE |TIMEZONE_REGION | TIMEZONE_ABBR } FROM expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回日期或间隔的特定部分的值. 如果是YEAR或MONTH，expr必须是DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL YEAR TO MONTH；如果是DAY，expr必须是DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND；如果是HOUR，MINUTE或SECOND，expr必须是TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND；如果是TIMEZONE_HOUR，TIMEZONE_MINUTE，TIMEZONE_ABBR，TIMEZONE_REGION或TIMEZONE_OFFSET，expr必须是TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(YEAR FROM SYSDATE) result FROM DUAL;
 * <span style="background-color: rgba(255,255,255,0);">EXTRACT处理DATE时，使用ANSI DATE，不包含HOUR，MINUTE，SECOND. **

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 2011

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(MONTH FROM SYSDATE) result FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 10

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(DAY FROM SYSDATE) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(HOUR FROM SYSTIMESTAMP) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 4

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT EXTRACT(TIMEZONE_HOURFROM SYSTIMESTAMP) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 8

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(TIMEZONE_OFFSET FROM SYSTIMESTAMP) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">EXTRACT(TIMEZONE_OFFSETFROMSYSTIMESTAMP) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">+00000000008:00:00.000000000

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 0

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(YEAR FROM INTERVAL '50' MONTH) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 4

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTEXTRACT(SECOND FROM INTERVAL '12 12:34:56' DAY TO SECOND) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 56

<span style="background-color: rgba(255,255,255,0);">FLOOR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">FLOOR ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回小于或等于n的最大整数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n可以为任意数值类型或可以隐式转化成数值的其他类型. 返回值的类型与参数的数据类型相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTFLOOR(1234.5) result FROM dual;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1234

<span style="background-color: rgba(255,255,255,0);">FROM_TZ <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">FROM_TZ (timestamp_value, time_zone_value ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将一个TIMESTAMP值和一个时区转化为一个TIMESTAMP WITH TIME ZONE值. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">time_zone_value是一个格式为‘TZH:TZM’的字符串或能返回一个TZR的字符串. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTFROM_TZ(TIMESTAMP '2011-10-11 12:22:00', '02:00') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">11-OCT-1112.22.00.000000000 PM +02:00

<span style="background-color: rgba(255,255,255,0);">GREATEST <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">GREATEST ( { expr [,expr ... ] } ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个或多个表达式中的最大值. 返回值的类型由第一个expr决定. 在比较前，将其余的expr转化成第一个expr的类型再进行比较. 默认地，比较是使用nonpadded语义的、二进制的. 可以通过NLS_COMP和NLS_SORT参数来改变默认行为. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTGREATEST('HARRY', 'HARRIOT', 'HAROLD') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESUL <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">HARRY

<span style="background-color: rgba(255,255,255,0);">GROUPING <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">GROUPING ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">GROUPING可以将超聚合行从常规分组行中区分出来. GROUP BY的扩展ROLLUP和CUBE会产生超聚合行，使用GROUPING可以将其区分出来. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr必须是GROUP BY中的表达. 如果expr在行中的值为NULL，则返回1，否则返回0. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT <span style="background-color: rgba(255,255,255,0);"> 2 DECODE(GROUPING(d.dname), 1, 'ALL DEPARTMENTS', d.dname) department, <span style="background-color: rgba(255,255,255,0);"> 3 DECODE(GROUPING(e.job), 1, 'ALL JOBS', e.job) job, <span style="background-color: rgba(255,255,255,0);"> 4 COUNT(*) "TOTAL EMPLOYEES", <span style="background-color: rgba(255,255,255,0);"> 5 AVG(sal) * 12 "AVARAGE SALARY" <span style="background-color: rgba(255,255,255,0);"> 6 FROMscott.emp e, scott.dept d <span style="background-color: rgba(255,255,255,0);"> 7 WHERE e.deptno=d.deptno <span style="background-color: rgba(255,255,255,0);"> 8 GROUP BY ROLLUP (d.dname, e.job) <span style="background-color: rgba(255,255,255,0);"> 9 ORDER BY d.dname, e.job;

<span style="background-color: rgba(255,255,255,0);">DEPARTMENT JOB TOTAL EMPLOYEES AVARAGE SALARY <span style="background-color: rgba(255,255,255,0);"> --- -- <span style="background-color: rgba(255,255,255,0);">ACCOUNTING CLERK 1 15600 <span style="background-color: rgba(255,255,255,0);">ACCOUNTING MANAGER 1 29400 <span style="background-color: rgba(255,255,255,0);">ACCOUNTING PRESIDENT 1 60000 <span style="background-color: rgba(255,255,255,0);">ACCOUNTING ALL JOBS 3 35000 <span style="background-color: rgba(255,255,255,0);">RESEARCH ANALYST 2 36000 <span style="background-color: rgba(255,255,255,0);">RESEARCH CLERK 2 11400 <span style="background-color: rgba(255,255,255,0);">RESEARCH MANAGER 1 35700 <span style="background-color: rgba(255,255,255,0);">RESEARCH ALL JOBS 5 26100 <span style="background-color: rgba(255,255,255,0);">SALES CLERK 1 11400 <span style="background-color: rgba(255,255,255,0);">SALES MANAGER 1 34200 <span style="background-color: rgba(255,255,255,0);">SALES SALESMAN 4 16800 <span style="background-color: rgba(255,255,255,0);">SALES ALL JOBS 6 18800 <span style="background-color: rgba(255,255,255,0);">ALL DEPARTMENTS ALLJOBS 14 24878.5714

<span style="background-color: rgba(255,255,255,0);">GROUPING_ID <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">GROUPING_ID ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一行中相关GROUPING字节向量的数值. GROUPING_ID只有在GROUP BY ROLLUP或CUBE中，并且和GROUPING函数一起使用. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTdeptno, job, SUM(sal) "Total Salary", <span style="background-color: rgba(255,255,255,0);"> 2 GROUPING(deptno) gd, <span style="background-color: rgba(255,255,255,0);"> 3 GROUPING(job) gj, <span style="background-color: rgba(255,255,255,0);"> 4 GROUPING_ID(deptno, job) gdj, <span style="background-color: rgba(255,255,255,0);"> 5 GROUPING_ID(job, deptno) gjd <span style="background-color: rgba(255,255,255,0);"> 6 FROMscott.emp <span style="background-color: rgba(255,255,255,0);"> 7 GROUP BY CUBE(deptno,job) <span style="background-color: rgba(255,255,255,0);"> 8 ORDER BY deptno,job,"Total Salary",gd;

<span style="background-color: rgba(255,255,255,0);"> DEPTNO JOB Total Salary GD GJGDJ GJD <span style="background-color: rgba(255,255,255,0);">-- - --- --- --- --- <span style="background-color: rgba(255,255,255,0);"> 10 CLERK 1300 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 10 MANAGER 2450 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 10 PRESIDENT 5000 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 10 8750 0 1 1 2 <span style="background-color: rgba(255,255,255,0);"> 20 ANALYST 6000 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 20 CLERK 1900 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 20 MANAGER 2975 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 20 10875 0 1 1 2 <span style="background-color: rgba(255,255,255,0);"> 30 CLERK 950 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 30 MANAGER 2850 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 30 SALESMAN 5600 0 0 0 0 <span style="background-color: rgba(255,255,255,0);"> 30 9400 0 1 1 2 <span style="background-color: rgba(255,255,255,0);"> ANALYST 6000 1 0 2 1 <span style="background-color: rgba(255,255,255,0);"> CLERK 4150 1 0 2 1 <span style="background-color: rgba(255,255,255,0);"> MANAGER 8275 1 0 2 1 <span style="background-color: rgba(255,255,255,0);"> PRESIDENT 5000 1 0 2 1 <span style="background-color: rgba(255,255,255,0);"> SALESMAN 5600 1 0 2 1 <span style="background-color: rgba(255,255,255,0);"> 29025 1 1 3 3

<span style="background-color: rgba(255,255,255,0);">HEXTORAW <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">HEXTORAW ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将包含16进制数字的char转化为RAW值. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char可以是CHAR，VARCHAR2，NCHAR或NVARCHAR2. 不直接支持CLOB，但传进来的CLOB会先进行隐式转化. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTUTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('4F5241434C45')) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">INITCAP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">INITCAP ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char转化成每个单词首字母大写，其他字母小写的形式. 单词是由空格或其他非字母数字字符来间隔. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">char可以为CHAR，VARCHAR2，NCHAR或NVARCHAR2，返回值类型与char相同. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTINITCAP('ORACLE DATABASE') reslt FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESLT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">Oracle Database

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT INITCAP('Oracledatabase') reslt FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESLT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">Oracle Database

<span style="background-color: rgba(255,255,255,0);">INSTR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">INSTR ( string,substring [, position [, occurrence ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">在string中，从position开始搜索substring，返回第occurrence次匹配的位置. 如果没找到，返回0. position和occurrence的默认值是1. 当position为负数时，表示从string的后面开始向前搜索. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('12345678901234567890', '0') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 10

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('12345678901234567890', '0', 11) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 20

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT INSTR('12345678901234567890','0', 2, 2) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 20

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('12345678901234567890', '0', 11, 2) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 0

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('12345678901234567890', '0', -1) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 20

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('12345678901234567890', '0', -1, 2) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 10

<span style="background-color: rgba(255,255,255,0);">LAST_DAY <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LAST_DAY ( date ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回date所在月份的最后一天. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT LAST_DAY(SYSDATE)result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">31-OCT-11

<span style="background-color: rgba(255,255,255,0);">LEAST <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LEAST ( { expr [,expr ... ] } ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个或多个表达式中的最小值. 返回值的类型由第一个expr决定. 在比较前，将其余的expr转化成第一个expr的类型再进行比较. 默认地，比较是使用nonpadded语义的、二进制的. 可以通过NLS_COMP和NLS_SORT参数来改变默认行为. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT LEAST('HARRY','HARRIOT', 'HAROLD') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">HAROLD

<span style="background-color: rgba(255,255,255,0);">LENGTH <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LENGTH ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回字符串char的长度. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLENGTH('ORACLE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 6

<span style="background-color: rgba(255,255,255,0);">LN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的自然对数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n必须大于0. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLN(10) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">2.30258509

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT LN(0)result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT LN(0) resultFROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01428: argument'0' is out of range

<span style="background-color: rgba(255,255,255,0);">LOCALTIMESTAMP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LOCALTIMESTAMP ( timestamp_pecision) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以会话时区返回当前日期时间. 它与CURRENT_TIMESTAMP的区别是，LOCALTIMESTAMP返回类型为TIMESTAMP，CURRENT_TIEMSTAMP返回类型为TIMESTAMP WITH TIME ZONE. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">timestamp_pecision指定小数秒，默认为6. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLOCALTIMESTAMP FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">LOCALTIMESTAMP <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">12-OCT-1102.37.38.969074 PM

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLOCALTIMESTAMP(0) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">LOCALTIMESTAMP(0) <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">12-OCT-11 02.38.03PM

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLOCALTIMESTAMP(9) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">LOCALTIMESTAMP(9) <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">12-OCT-1102.38.18.770422000 PM

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLOCALTIMESTAMP(10) FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECTLOCALTIMESTAMP(10) FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-30088: datetime/intervalprecision is out of range

<span style="background-color: rgba(255,255,255,0);">LOG <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LOG ( n1, n2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回以n1为底的n2的对数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">底n1为任意正数，除了0和1. n2可以为任意正数. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLOG(10,100) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 2

<span style="background-color: rgba(255,255,255,0);">LOWER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LOWER ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回字符串的小写形式. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT LOWER('ORACLE')result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">oracle

<span style="background-color: rgba(255,255,255,0);">LPAD <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LPAD ( expr1, n [,expr2 ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回长度为n的expr1字符串. 如果expr1的长度小于n，则用expr2左填充expr1到长度n为止；expr2默认为一个空格符. 如果expr1的长度大于n，截取expr1前n个字符返回. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLPAD('ORACLE',10) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLPAD('ORACLE',10,'-') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLPAD('ORACLE',4) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESU <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">ORAC

<span style="background-color: rgba(255,255,255,0);">LTRIM <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">LTRIM ( char [, set] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">删除char从左边开始的包含在set中的字符，直到遇到第一个不再set中的字符. set默认是空格符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLTRIM('<===>ORACLE<===>', '<>=') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">ORACLE<===>

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTLTRIM(' ORACLE ') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">MAX <span style="background-color: rgba(255,255,255,0);">语法 <span style="background-color: rgba(255,255,255,0);">MAX ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">聚合函数，返回所有行中expr的最大值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTMAX(sal) FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);"> MAX(SAL) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 5000

<span style="background-color: rgba(255,255,255,0);">MEDIAN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">MEDIAN ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">聚合函数，返回所有行中expr的中位数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTMEDIAN(sal) FROM scott.emp;
 * <span style="background-color: rgba(255,255,255,0);">expr只能是数值类型或日期时间类型. **

<span style="background-color: rgba(255,255,255,0);">MEDIAN(SAL) <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> 1550

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTMEDIAN(ename) FROM scott.emp; <span style="background-color: rgba(255,255,255,0);">SELECT MEDIAN(ename)FROM scott.emp <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-30495: Theargument should be of numeric or date/datetime type.

<span style="background-color: rgba(255,255,255,0);">MIN <span style="background-color: rgba(255,255,255,0);">语法 <span style="background-color: rgba(255,255,255,0);">MIN ( expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">聚合函数，返回所有行中expr的最小值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT MIN(sal)FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);"> MIN(SAL) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 800

<span style="background-color: rgba(255,255,255,0);">MOD <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">MOD ( n2, n1 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n2除以n1后的余数. 如果n1为0，则返回n2. <span style="background-color: rgba(255,255,255,0);">注意：MOD的计算方式不用于**数学上的经典模式n2 - n1 *FLOOR(n2/n1)**. 网上找到的**MOD的计算方式为：DECODE(SIGN(n2), -1,'-') || MOD(ABS(n2), ABS(n1))**，余数的符号由被除数n2决定. 另外，Oracle另一个关于余数的函数**REMAINDER的计算方式为n2 - n1 * ROUND(n2/n1)**. 所以要注意使用. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTMOD(11,4), MOD(11,-4), <span style="background-color: rgba(255,255,255,0);"> 2 MOD(-11,4),MOD(-11,-4) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> MOD(11,4) MOD(11,-4) MOD(-11,4) MOD(-11,-4) <span style="background-color: rgba(255,255,255,0);"> -- --- <span style="background-color: rgba(255,255,255,0);"> 3 3 -3 -3

<span style="background-color: rgba(255,255,255,0);">MONTHS_BETWEEN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">MONTHS_BETWEEN ( date1,date2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回date1和date2之间的月份数. 如果date1比date2晚，结果为正数；如果date1比date2早，结果为负数. 如果date1和date2是该月中的相同日期，或者是该月中的最后一天，那么结果是整数，否则结果具有小数部分. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTMONTHS_BETWEEN( <span style="background-color: rgba(255,255,255,0);"> 2 TO_DATE('20111001','YYYYMMDD'), <span style="background-color: rgba(255,255,255,0);"> 3 TO_DATE('20111015','YYYYMMDD')) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> -.4516129

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTMONTHS_BETWEEN( <span style="background-color: rgba(255,255,255,0);"> 2 TO_DATE('20111003','YYYYMMDD'), <span style="background-color: rgba(255,255,255,0);"> 3 TO_DATE('20111103','YYYYMMDD')) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> -1

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTMONTHS_BETWEEN( <span style="background-color: rgba(255,255,255,0);"> 2 TO_DATE('20110331','YYYYMMDD'), <span style="background-color: rgba(255,255,255,0);"> 3 TO_DATE('20110228','YYYYMMDD')) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1

<span style="background-color: rgba(255,255,255,0);">NANVL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NANVL ( n2, n1 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">该函数只对BINARY_FLOAT和BINARY_DOUBLE起作用. 如果n2为NaN（不是数值），返回n1；否则返回n2. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">CREATE TABLEfloat_point_demo <span style="background-color: rgba(255,255,255,0);"> (dec_num NUMBER(10,2), bin_doubleBINARY_DOUBLE, bin_float BINARY_FLOAT); <span style="background-color: rgba(255,255,255,0);">INSERT INTOfloat_point_demo <span style="background-color: rgba(255,255,255,0);"> VALUES (0,'NaN','NaN'); <span style="background-color: rgba(255,255,255,0);">SQL> SELECT * <span style="background-color: rgba(255,255,255,0);"> 2 FROM float_point_demo;

<span style="background-color: rgba(255,255,255,0);"> DEC_NUM BIN_DOUBLE BIN_FLOAT <span style="background-color: rgba(255,255,255,0);"> -- <span style="background-color: rgba(255,255,255,0);"> 0 Nan Nan <span style="background-color: rgba(255,255,255,0);">SQL> SELECTbin_float, NANVL(bin_float,0) <span style="background-color: rgba(255,255,255,0);"> 2 FROMfloat_point_demo;

<span style="background-color: rgba(255,255,255,0);"> BIN_FLOAT NANVL(BIN_FLOAT,0) <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> Nan 0

<span style="background-color: rgba(255,255,255,0);">NEXT_DAY <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NEXT_DAY ( date,char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回date之后的第一个由char表示的星期几. 返回值类型是DATE. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTNEXT_DAY(SYSDATE, 'SUNDAY') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">16-OCT-11

<span style="background-color: rgba(255,255,255,0);">NULLIF <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NULLIF ( expr1,expr2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">比较expr1和expr2，如果相等，返回NULL，否则返回expr1. expr1不能是NULL的字面量. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTNULLIF(1, 4/2) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNVL(NULLIF('ORACLE', 'ORA' || 'CLE'), 'They are the same.') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">They are the same.

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNULLIF(NULL, 'ORACLE') result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT NULLIF(NULL,'ORACLE') result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-00932:inconsistent datatypes: expected - got CHAR

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNULLIF(NULL, 100) result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT NULLIF(NULL,100) result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-00932:inconsistent datatypes: expected - got CHAR

<span style="background-color: rgba(255,255,255,0);">NUMTODSINTERVAL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NUMTODSINTERVAL ( n,'interval_unit' ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将数值n转化为INTERVAL DAY TO SECOND类型. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">interval_unit必须为DAY，HOUR，MINUTE或SECOND. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTODSINTERVAL(100, 'DAY') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">+00000010000:00:00.000000000

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTODSINTERVAL(123.45, 'hour') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">+000000005 03:27:00.000000000

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTODSINTERVAL(12345.6789, 'minute') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">+00000000813:45:40.734000000

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTODSINTERVAL(12345.6789, 'SECOND') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">+00000000003:25:45.678900000

<span style="background-color: rgba(255,255,255,0);">NUMTOYMINTERVAL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NUMTOYMINTERVAL ( n,'interval_unit' ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将数值n转化为INTERVAL YEAR TO MONTH类型. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">interval_unit必须为YEAR或MONTH. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTOYMINTERVAL(12345.6789, 'YEAR') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">+000012345-08

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNUMTOYMINTERVAL(12345.6789, 'MONTH') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">+000001028-10

<span style="background-color: rgba(255,255,255,0);">NVL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NVL ( expr1, expr2 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">如果expr1为NULL，返回expr2，否则返回expr1. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">如果expr1和expr2的数据类型不同时，Oracle将进行隐式转化. 如果不能转化，Oracle会返回一个错误. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTNVL(NULL, 'IS NULL') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">IS NULL

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTNVL(123, 'ORACLE') result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT NVL(123,'ORACLE') result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01722: invalidnumber

<span style="background-color: rgba(255,255,255,0);">NVL2 <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">NVL2 ( expr1, expr2,expr3 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">如果expr1**不为**null，则返回expr2，否则返回expr3. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr1可以为任意数据类型；而expr2和expr3，除了LONG，可以为任意数据类型. 如果expr2和expr3的数据类型不同，Oracle则会隐式地将其中一种专为另一种数据类型. **如果它们不能转化，会报错. **如select nvl2(null, 123, 'abc') from dual;会报错ORA-01722 invalid number，因为abc不能转化为数值. 如果expr2是字符或数值类型，则根据以下规则进行隐式转化：1. 如果expr2是字符类型，Oracle将expr3转化成expr2的数据类型，除非expr3是null. 2. 如果expr2是数值类型，Oracle会根据数值优先级来进行转化. 其他情况根据隐式类型转化表进行转化. <span style="background-color: rgba(255,255,255,0);">注：数值优先级： BINARY_DOUBLE > BINARY_FLOAT > NUMBER <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SELECT NVL2(NULL,'NOT NULL', 'NULL') result FROM dual; <span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">NULL

<span style="background-color: rgba(255,255,255,0);">POWER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">POWER ( n2, n1 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n2的n1次方. n2和n1可以是任意数值，**但当n2为负数时，n1必须是整数. ** <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTPOWER(2, 32) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">4294967296

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTPOWER(10, 2.5) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">316.227766

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTPOWER(-10, -2) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> .01

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT POWER(-10,3.3) result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT POWER(-10,3.3) result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01428: argument'-10' is out of range

<span style="background-color: rgba(255,255,255,0);">RAWTOHEX <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">RAWTOHEX ( raw ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将raw转化成16进制表示的字符串. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTRAWTOHEX(HEXTORAW('3D3E3F')) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">3D3E3F

<span style="background-color: rgba(255,255,255,0);">REGEXP_COUNT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REGEXP_COUNT (source_char, pattern [, position [, match_param]] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以正则表达式搜索并返回模式pattern在source_char中出现的次数. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">pattern最多可以有512字节，如果其数据类型与source_char不同，Oracle会将pattern转化为source_char的数据类型. REGEXP_COUNT会忽略pattern中的子表达式的括号，如pattern'(123(45))'等于'12345'. position表示从source_char的第几位开始搜索，默认为1. match_param可以改变匹配行为，如i表示不区分大小写；c表示区分大小写；n表示'.'可以匹配换行符；m可以将source_char作为多行处理；x表示忽略空格符. 如果指定了矛盾的两个值，如'ic'，Oracle会取后一个值. 如果指定其他不在此范围的值，Oracle会报错. 如果忽略match_param，默认的大小写模式由NLS_SORT参数决定；'.'不能匹配换行符；source_char是单行模式. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[AEIOU]') result FROM DUAL

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 7

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'ic') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 0

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'icu') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL; <span style="background-color: rgba(255,255,255,0);">SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'icu') result <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01760: illegalargument for function

<span style="background-color: rgba(255,255,255,0);">REGEXP_INSTR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_opt [, match_param [,subexpr ] ] ] ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以正则表达式搜索，返回匹配pattern的位置，可能是匹配子字符串的开始或结束位置，由参数return_option决定. 如果没有匹配，返回0. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">相同的参数可以参考REGEXP_COUNT. occurrence表示搜索匹配的第几次，如果大于1，则从第一次匹配后的第一字符开始搜索第二次匹配. 这个行为与INSTR不同，INSTR是从第一次匹配的第二个字符开始搜索第二次匹配. return_option默认为0，表示返回匹配的第一个字符的位置，即匹配的开头；如果为1，则返回匹配之后的位置，即匹配的结尾. subexpr的范围是从0到9，表示匹配的子模式，如'0123(((abc)(de)f)ghi)45(678)'中，有5个子模式，"abcdefghi"，"abcdef"，"abc"，"de"，"678". 如果subexpr为0，则返回匹配整个模式的位置，如果大于0，则返回匹配整个模式中相应的子模式的位置. 如果subexpr为NULL，则返回NULL. 默认值为0. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_INSTR('BOBOB','BOB',1,2) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">REGEXP_INSTR('BOBOB','BOB',1,2) <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> 0

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTINSTR('BOBOB','BOB',1,2) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">INSTR('BOBOB','BOB',1,2) <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> 3

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_INSTR('0123456789','123',1,1,0) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 2

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_INSTR('0123456789','123',1,1,1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 5

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_INSTR('0123456789','digit:+(678)',1,1,0,'i',1) from dual;

<span style="background-color: rgba(255,255,255,0);">REGEXP_INSTR('0123456789','DIGIT:+(678)',1,1,0,'I',1) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 7

<span style="background-color: rgba(255,255,255,0);">REGEXP_LIKE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REGEXP_LIKE (source_char, pattern [, match_param] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以正则表达式搜索模式pattern，找到则返回true，否则返回false. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参考REGEXP_COUNT和REGEXP_INSTR. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT'FOUND' result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> 2 WHERE REGEXP_LIKE('0123456789','digit:+(678)');

<span style="background-color: rgba(255,255,255,0);">RESUL <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">FOUND

<span style="background-color: rgba(255,255,255,0);">REGEXP_REPLACE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REGEXP_REPLACE (source_char, pattern [, replace_string [, position [, occurrence [, match_param] ] ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以正则表达式搜索并替换pattern为replace_string. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">replace_string可以包含子模式的引用\n，n从1到9. 如果没有提供replace_string，则删除source_char中匹配到的pattern. 其他参数参考REGEXP_COUNT和REGEXP_INSTR. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_REPLACE('0123456789','678') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">0123459

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_REPLACE('999-888-1234', '(digit:{3})-(digit:{3})-(digit:{4})', <span style="background-color: rgba(255,255,255,0);"> 2 '(\3)\1 \2') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">(1234)999 888

<span style="background-color: rgba(255,255,255,0);">REGEXP_SUBSTR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REGEXP_SUBSTR (source_char, pattern [, position [, occurrence [, match_param [, subexpr ] ] ]] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">以正则表达式搜索并截取字符串，匹配原理与REGEXP_INSTR相似. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参数REGEXP_INSTR. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREGEXP_SUBSTR('http://www.oracle.com/database', <span style="background-color: rgba(255,255,255,0);"> 2 'http://((alnum:+\.?){3,4})/?', 1,1,'i',1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">www.oracle.com

<span style="background-color: rgba(255,255,255,0);">REMAINDER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REMAINDER ( n2, n1 ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n2除以n1后的余数，计算原理可以参考MOD. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">如果n1=0或者n2=无线，那么当参数是NUMBER时，Oracle返回一个错误，当参数是BINARY_FLOAT或BINARY_DOUBLE时，返回NaN. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREMAINDER(11,4),REMAINDER(11,-4), <span style="background-color: rgba(255,255,255,0);"> 2 REMAINDER(-11,4),REMAINDER(-11,-4) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">REMAINDER(11,4) REMAINDER(11,-4)REMAINDER(-11,4) REMAINDER(-11,-4) <span style="background-color: rgba(255,255,255,0);">--- - <span style="background-color: rgba(255,255,255,0);"> -1 -1 1 1

<span style="background-color: rgba(255,255,255,0);">REPLACE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">REPLACE ( char,search_string [, replacement_string ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char中的所有search_string替换成replacement_string. 如果忽略replacement_string或为NULL，那将删除char中的所有search_string. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTREPLACE('HELLO ORACLE', 'O', '0') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">HELL0 0RACLE

<span style="background-color: rgba(255,255,255,0);">ROUND(date) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ROUND ( date [, fmt] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回关于格式fmt的完整日期. fmt默认为DAY. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(TO_DATE('20110531091011','YYYYMMDDHH24MISS')) <span style="background-color: rgba(255,255,255,0);"> 2 result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">31-MAY-11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(TO_DATE('20110531091011','YYYYMMDDHH24MISS'), 'MONTH') <span style="background-color: rgba(255,255,255,0);"> 2 result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JUN-11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(TO_DATE('20110531091011','YYYYMMDDHH24MISS'), 'yyyy') <span style="background-color: rgba(255,255,255,0);"> 2 result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JAN-11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(TO_DATE('20110601091011','YYYYMMDDHH24MISS'), 'yyyy') <span style="background-color: rgba(255,255,255,0);"> 2 result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JAN-11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(TO_DATE('20110701091011','YYYYMMDDHH24MISS'), 'yyyy') <span style="background-color: rgba(255,255,255,0);"> 2 result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JAN-12

<span style="background-color: rgba(255,255,255,0);">ROUND(NUMBER) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ROUND ( n [, integer] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回四舍五入后的n. 计算原理是如果n为0，总是返回0；如果n为正数，则返回FLOOR(n * POWER(10, integer) + 0.5) *POWER(10, -integer)；如果n为负数，则返回-ROUND(-n, integer). <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">n的默认值为0，当n大于0时，表示小数点右边进行四舍五入；当n小于0时，表示小数点左边进行四舍五入. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(0, -1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 0

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(1234.5678, 2) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1234.57

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTROUND(9876.54321, -2) reulst FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> REULST <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 9900

<span style="background-color: rgba(255,255,255,0);">ROWIDTOCHAR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">ROWIDTOCHAR ( rowid) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将rowid转化为VARCHAR2数据类型，长度为18字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTROWIDTOCHAR(rowid),deptno FROM SCOTT.DEPT;

<span style="background-color: rgba(255,255,255,0);">ROWIDTOCHAR(ROWID) DEPTNO <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">AAASHhAAEAAAACHAAA 10 <span style="background-color: rgba(255,255,255,0);">AAASHhAAEAAAACHAAB 20 <span style="background-color: rgba(255,255,255,0);">AAASHhAAEAAAACHAAC 30 <span style="background-color: rgba(255,255,255,0);">AAASHhAAEAAAACHAAD 40

<span style="background-color: rgba(255,255,255,0);">RPAD <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">RPAD ( expr1, n [,expr2 ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回长度为n的expr1字符串. 如果expr1的长度小于n，则用expr2右填充expr1到长度n为止；expr2默认为一个空格符. 如果expr1的长度大于n，截取expr1前n个字符返回. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTRPAD('ORACLE',10) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTRPAD('ORACLE',10,'-') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTRPAD('ORACLE',4) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESU <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">ORAC

<span style="background-color: rgba(255,255,255,0);">RTRIM <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">RTRIM ( char [, set] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">删除char从右边开始的包含在set中的字符，直到遇到第一个不再set中的字符. set默认是空格符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTRTRIM('<===>ORACLE<===>', '<>=') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"><===>ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTRTRIM(' ORACLE ') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);"> ORACLE

<span style="background-color: rgba(255,255,255,0);">SESSIONTIMEZONE <span style="background-color: rgba(255,255,255,0);">语法 <span style="background-color: rgba(255,255,255,0);">SESSIONTIMEZONE <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回当前会话的时区. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSESSIONTIMEZONE FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SESSIONTIMEZONE <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">+08:00

<span style="background-color: rgba(255,255,255,0);">SIGN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SIGN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的符号. 对于NUMBER，当n<0时，返回-1；当n=0时，返回0；当n>0时，返回1. 对于BINARY_FLOAT或BINARY_DOUBLE，但n<0时，返回-1；当n>=0或n=NaN时，返回1. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSIGN(-10), SIGN(0), SIGN(10) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> SIGN(-10) SIGN(0) SIGN(10) <span style="background-color: rgba(255,255,255,0);"> -- <span style="background-color: rgba(255,255,255,0);"> -1 0 1

<span style="background-color: rgba(255,255,255,0);">SIN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SIN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的正弦值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSIN(30 * 3.14159265359/180) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> .5

<span style="background-color: rgba(255,255,255,0);">SOUNDEX <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SOUNDEX ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回char的语音表示字符. 规则是保留第一个字符，删除剩余的aehiouwy中的字符；映射bfpv=1，cgjkqsxz=2，dt=3，l=4，mn=5，r=6；相邻的语音字符相同，则删除重复的一个；最后返回4位语音字符，不足4位，用0右填充. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSOUNDEX('ORACLE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESU <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">O624

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSOUNDEX('Bad') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESU <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">B300

<span style="background-color: rgba(255,255,255,0);">SQRT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SQRT ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的平方根. 如果n为NUMBER，那么n不能为负数. 如果n为BINARY_FLOAT或BINARY_DOUBLE，那么n>=0，返回正数；n=-0，返回-0；n<0，返回NaN. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSQRT(100) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> SQRT(100) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 10

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSQRT(-100) FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT SQRT(-100)FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01428: argument'-100' is out of range

<span style="background-color: rgba(255,255,255,0);">STDDEV <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">STDDEV ( [ DISTINCT| ALL ] expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回expr的标准差. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSTDDEV(sal) FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);">STDDEV(SAL) <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> 1182.50322

<span style="background-color: rgba(255,255,255,0);">SUBSTR <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SUBSTR ( char,position [, substring_length ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">从char中position开始截取长度为substring_length的字符串. 如果position为0，则改成1；如果position为正数，则从char的左边开始数；如果position为负数，则从char的右边开始数. 如果substring_length忽略，则截取到char的最后. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSUBSTR('ORACLE',4,5) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RES <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">CLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSUBSTR('ORACLE',-4,2) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RE <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">AC

<span style="background-color: rgba(255,255,255,0);">SUM <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SUM ( [ DISTINCT |ALL ] expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">计算expr的总和. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSUM(sal) result FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 29025

<span style="background-color: rgba(255,255,255,0);">SYS_CONNECT_BY_PATH <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYS_CONNECT_BY_PATH( column, char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">只在分级查询中有效，返回column从根到节点的路径，以char分隔. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTLPAD(' ',2*LEVEL-1)||SYS_CONNECT_BY_PATH(ename,'/') path <span style="background-color: rgba(255,255,255,0);"> 2 FROMscott.emp START WITH ename='JONES' <span style="background-color: rgba(255,255,255,0);"> 3 CONNECT BY PRIOR empno=mgr;

<span style="background-color: rgba(255,255,255,0);">PATH <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> /JONES <span style="background-color: rgba(255,255,255,0);"> /JONES/SCOTT <span style="background-color: rgba(255,255,255,0);"> /JONES/SCOTT/ADAMS <span style="background-color: rgba(255,255,255,0);"> /JONES/FORD <span style="background-color: rgba(255,255,255,0);"> /JONES/FORD/SMITH

<span style="background-color: rgba(255,255,255,0);">SYS_CONTEXT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYS_CONTEXT ('namespace', 'parameter' [, length ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回当前上下文的namespace中，parameter的值. Oracle提供了一个内置的namespace是USERENV. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">length默认为256，可选范围是1至4000字节. 如果不在此范围，Oracle会忽略它并使用默认值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYS_CONTEXT('USERENV', 'SESSION_USER') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">TEST

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYS_CONTEXT('USERENV', 'SESSION_USER', 2) result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">TE

<span style="background-color: rgba(255,255,255,0);">SYS_EXTRACT_UTC <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYS_EXTRACT_UTC (datetime_with_timezone ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回datetime_with_timezone的协调世界时. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SYS_EXTRACT_UTC(SYSTIMESTAMP) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">14-OCT-1108.41.51.754413 AM

<span style="background-color: rgba(255,255,255,0);">SYS_GUID <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYS_GUID **** <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个GUID. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYS_GUID FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT SYS_GUID FROMDUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-00904:"SYS_GUID": invalid identifier

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYS_GUID FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SYS_GUID <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">AF3F48998E6789F8E040E10A4A0B31DF

<span style="background-color: rgba(255,255,255,0);">SYSDATE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYSDATE <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回数据库系统的当前日期时间. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SYSDATE <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">14-OCT-11

<span style="background-color: rgba(255,255,255,0);">SYSTIMESTAMP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">SYSTIMESTAMP <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回数据库系统的当前日期时间，包括小数秒和时区信息. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSTIMESTAMP FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">SYSTIMESTAMP <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">14-OCT-1104.50.52.557445 PM +08:00

<span style="background-color: rgba(255,255,255,0);">TAN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TAN ( n ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回n的正切值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTAN(135 * 3.14159265359/180) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> -1

<span style="background-color: rgba(255,255,255,0);">TIMESTAMP_TO_SCN <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TIMESTAMP_TO_SCN (timestamp ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">根据timestamp返回一个最接近的SCN. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTIMESTAMP_TO_SCN(SYSTIMESTAMP) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 14384740

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE (expr [, fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个双精度浮点数. 从字符串转化成BINARY_DOUBLE是不精确的；从BINARY_FLOAT转化成BINARY_DOUBLE是精确的. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr可以是字符串或数值型，如NUMBER，BINARY_FLOAT，BINARY_DOUBLE. 如果expr是BINARY_DOUBLE，返回expr. 只有expr是字符串时，fmt和nlsparam才可用. 'INF'（不分大小写）转化成正无限；'-INF'（不分大小写）转化成负无限；'NaN'转化成NaN（不是数值）. 在expr中不能使用浮点数格式化元素（F，f，D，d）. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_DOUBLE('INF') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE('INF') <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> Inf

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_DOUBLE('-inf') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE('-INF') <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> -Inf

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_DOUBLE('nan') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE('NAN') <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> Nan

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_DOUBLE(123.4567) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE(123.4567) <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1.235E+002

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_DOUBLE('123,456.78','999,999.99') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_DOUBLE('123,456.78','999,999.99') <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> 1.235E+005

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT (expr [, fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回一个单精度浮点数. 从字符串转化成BINARY_FLOAT是不精确的；如果BINARY_DOUBLE的精度超过BINARY_FLOAT，则从BINARY_DOUBLE转化成BINARY_FLOAT是不精确的. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">expr可以是字符串或数值型，如NUMBER，BINARY_FLOAT，BINARY_DOUBLE. 如果expr是BINARY_FLOAT，返回expr. 只有expr是字符串时，fmt和nlsparam才可用. 'INF'（不分大小写）转化成正无限；'-INF'（不分大小写）转化成负无限；'NaN'转化成NaN（不是数值）. 在expr中不能使用浮点数格式化元素（F，f，D，d）. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_FLOAT('INF') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT('INF') <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> Inf

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_FLOAT('-inf') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT('-INF') <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);"> -Inf

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_FLOAT('nan') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT('NAN') <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> Nan

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_FLOAT(123.4567) FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT(123.4567) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);"> 1.235E+002

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BINARY_FLOAT('123,456.78','999,999.99') FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">TO_BINARY_FLOAT('123,456.78','999,999.99') <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 1.235E+005

<span style="background-color: rgba(255,255,255,0);">TO_BLOB <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_BLOB ( raw_value ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将LONG RAW和RAW转化成BLOB. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> CREATE TABLEraw_table (raw_column RAW(2000));

<span style="background-color: rgba(255,255,255,0);">Table created.

<span style="background-color: rgba(255,255,255,0);">SQL> INSERT INTOraw_table VALUES (HEXTORAW('1234567890ABCDEF'));

<span style="background-color: rgba(255,255,255,0);">1 row created.

<span style="background-color: rgba(255,255,255,0);">SQL> COMMIT;

<span style="background-color: rgba(255,255,255,0);">Commit complete.

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_BLOB(raw_column) blob FROM raw_table;

<span style="background-color: rgba(255,255,255,0);">BLOB <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">1234567890ABCDEF

<span style="background-color: rgba(255,255,255,0);">TO_CHAR(CHARACTER) <span style="background-color: rgba(255,255,255,0);">语法: <span style="background-color: rgba(255,255,255,0);">TO_CHAR ( { nchar |clob | nclob } ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将NCHAR，NVARCHAR2，CLOB或NCLOB转化成数据库字符集，返回值总是VARCHAR2类型. 当LOB的长度超过VARCHAR2的最大长度时，Oracle会返回一个错误. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR('ORACLE DATABASE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">ORACLE DATABASE

<span style="background-color: rgba(255,255,255,0);">TO_CHAR(DATETIME) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_CHAR ( { datetime| interval } [, fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND，INTERVAL YEAR TO MONTH转化成格式为fmt的VARCHAR2类型. 如果忽略fmt，将以下面的规则进行处理：DATE转化成默认的日期格式；TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE转化成默认的时间戳格式；TIMESTAMP WITH TIME ZONE转化成默认的时间戳加时区的格式；INTERVAL值转化成数字代表的格式. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">nlsparam可以指定会话的语言，如NLS_DATE_LANGUAGE=language. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(SYSDATE) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">17-OCT-11

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(SYSTIMESTAMP) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">17-OCT-1110.46.31.729122 AM +08:00

<span style="background-color: rgba(255,255,255,0);">TO_CHAR(NUMBER) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_CHAR ( n [, fmt[, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将数值n转化成格式是fmt的VARCHAR2. 如果n是负数，负号会放在格式化的字符串之前，如TO_CHAR(-1, '$9')返回-$1，而不是$-1. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">nlsparam可以指定小数点、千分位、货币符号（Local和International）. 如NLS_NUMERIC_CHARACTERS='dg'，NLS_CURRENCY='text'，NLS_ISO_CURRENCY=territory. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(-10000,'99G999D99') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">-10,000.00

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(-10000,'L99G999D99') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> -$10,000.00

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT TO_CHAR(-10000,'L99G999D99MI')result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);"> $10,000.00-

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CHAR(-10000,'L99G999D99MI', <span style="background-color: rgba(255,255,255,0);"> 2 'NLS_NUMERIC_CHARACTERS=,. NLS_CURRENCY=AusDollars ') result <span style="background-color: rgba(255,255,255,0);"> 3 FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">AusDollars10.000,00-

<span style="background-color: rgba(255,255,255,0);">TO_CLOB <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_CLOB ( {lob_column | char } ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将LOB字段中或字符串中的NCLOB值转化为CLOB值. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_CLOB('ORACLE DATABASE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">ORACLE DATABASE

<span style="background-color: rgba(255,255,255,0);">TO_DATE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_DATE ( char [,fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字符串char转化成DATE. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">fmt指定了char的时间格式. 如果忽略fmt，就使用默认的时间格式. 默认的时间格式有NLS_TERRITORY隐式决定，或由NLS_DATE_FORMAT显示决定. 如果fmt是J，代码是Julian，那么char必须是整数. nlsparam可以指定字符串的语言，如NLS_DATE_LANGUAGE=language. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_DATE('20110101 23:59:59', 'YYYYMMDD HH24:MI:SS') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JAN-11

<span style="background-color: rgba(255,255,255,0);">TO_DSINTERVAL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_DSINTERVAL ( '{[+|-] days hours : minutes : seconds [, frac_secs ] | [-] P [ days D ] [ T [hours H ] [ minutes M ] [ seconds [, frac_secs ] S ] }' <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字符串转化成INTERVAL DAY TO SECOND. TO_DSINTERVAL可以接受两种类型的格式，SQL标准和ISO标准. 对于SQL标准，days范围是0～999999999；hours范围是0～23；minutes和seconds是0～59；frac_secs是.0～.999999999. 对于ISO标准，days、hours、minutes和seconds的范围是0～999999999；frac_secs是.0～.999999999；格式中不能有空格符；如果指定了T，必须至少指定hours、minutes或seonds中的一个. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE + TO_DSINTERVAL('100') result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT SYSDATE +TO_DSINTERVAL('100') result FROM DUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-01867: theinterval is invalid

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE + TO_DSINTERVAL('100 00: 00: 00') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">25-JAN-12

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE + TO_DSINTERVAL('P100DT50H') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">27-JAN-12

<span style="background-color: rgba(255,255,255,0);">TO_MULTI_BYTE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_MULTI_BYTE ( char) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char中的单字节转化为对应的多字节字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_MULTI_BYTE('ORACLE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">ＯＲＡＣＬＥ

<span style="background-color: rgba(255,255,255,0);">TO_NUMBER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_NUMBER ( expr [,fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将expr转化成NUMBER类型. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参考TO_CHAR(number). <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT TO_NUMBER('-RMB1,000','L9G999D99', <span style="background-color: rgba(255,255,255,0);"> 2 'NLS_NUMERIC_CHARACTERS=., NLS_CURRENCY=RMB') result <span style="background-color: rgba(255,255,255,0);"> 3 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> -1000

<span style="background-color: rgba(255,255,255,0);">TO_SINGLE_BYTE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_SINGLE_BYTE (char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char中多字节字符转成相应的单字节字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT TO_SINGLE_BYTE(TO_MULTI_BYTE('ORACLE'))result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">TO_TIMESTAMP <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_TIMESTAMP ( char[, fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字符串char转换成TIMESTAMP数据类型. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参考TO_CHAR(DATE)函数. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_TIMESTAMP('20110101 12:34:56.7890', <span style="background-color: rgba(255,255,255,0);"> 2 'YYYYMMDD HH24:MI:SS.FF') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">01-JAN-1112.34.56.789000000 PM

<span style="background-color: rgba(255,255,255,0);">TO_TIMESTAMP_TZ <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_TIMESTAMP_TZ (char [, fmt [, 'nlsparam' ] ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字符串char转化成TIMESTAMP WITH TIME ZONE类型. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">参考TO_CHAR(DATE)函数. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTO_TIMESTAMP_TZ('20110101 12:34:56.7890 +08:00', <span style="background-color: rgba(255,255,255,0);"> 2 'YYYYMMDD HH24:MI:SS.FF TZH:TZM') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-JAN-11 12.34.56.789000000PM +08:00

<span style="background-color: rgba(255,255,255,0);">TO_YMINTERVAL <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TO_YMINTERVAL ( '{[+|-] years - months | [-]P [ years Y ] [ months M ] }' ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将字符串转化成INTERVAL YEAR TO MONTH. 参数有两种，一种是SQL标准，另一种是ISO标准. ISO标准需要可以指定days、hours、minutes和seconds，但不起作用. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT SYSDATE+TO_YMINTERVAL('1-1')result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">17-NOV-12

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE+TO_YMINTERVAL('P1Y1M1D') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">17-NOV-12

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTSYSDATE+TO_YMINTERVAL('P1Y1M20D') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">17-NOV-12

<span style="background-color: rgba(255,255,255,0);">TRANSLATE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TRANSLATE ( expr,from_string, to_string ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将expr中from_string中的字符替换成to_string中的相应字符. 当from_string包含的字符多于to_string，没有对应的字符就删除其在expr中的字符. 如果一个字符在from_string中出现多次，只替换成第一个相应的字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTRANSLATE('ORACLE DATABASE', 'OLE', '01') result <span style="background-color: rgba(255,255,255,0);"> 2 FROMDUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);">0RAC1 DATABAS

<span style="background-color: rgba(255,255,255,0);">TRIM <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TRIM ( [ { { LEADING| TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">删除trim_source中开头或结尾的trim_character中的字符. 如果指定了LEADING，表示从开头删除；如果指定了TRAILING，表示从结尾删除；如果指定了BOTH，表示从两头删除. 如果没有指定trim_source，默认是空格符. 如果只指定了trim_source，表示删除两头的空格符. trim_character只能是一个字符. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT TRIM( '-' FROM '--ORACLE') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">SQL> SELECTTRIM(' OE' FROM ' ORACLE DATABASE ')result FROM DUAL; <span style="background-color: rgba(255,255,255,0);">SELECT TRIM(' OE'FROM ' ORACLE DATABASE ') result FROMDUAL <span style="background-color: rgba(255,255,255,0);"> * <span style="background-color: rgba(255,255,255,0);">ERROR at line 1: <span style="background-color: rgba(255,255,255,0);">ORA-30001: trim setshould have only one character

<span style="background-color: rgba(255,255,255,0);">TRUNC(DATE) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TRUNC ( date [, fmt] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">截取掉有fmt指定的日期部分. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTRUNC(SYSDATE, 'MM') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">01-OCT-11

<span style="background-color: rgba(255,255,255,0);">TRUNC(NUMBER) <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">TRUNC ( n1 [, n2 ] ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将n1截取到小数点的n2位置. 如果n2>0，从小数点右边截取；如果n1<0，从小数点左边截取. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTTRUNC(15.79,1) result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 15.7

<span style="background-color: rgba(255,255,255,0);">SQL> SELECT TRUNC(15.79,-1)result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 10

<span style="background-color: rgba(255,255,255,0);">UID <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">UID <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回当前用户的话会ID. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT UIDFROM DUAL;

<span style="background-color: rgba(255,255,255,0);"> UID <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);"> 101

<span style="background-color: rgba(255,255,255,0);">UPPER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">UPPER ( char ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">将char中字符串转化为大写字母. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTUPPER('oracle') result FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">RESULT <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">ORACLE

<span style="background-color: rgba(255,255,255,0);">USER <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">USER <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回当前用户的名称. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECT USERFROM DUAL;

<span style="background-color: rgba(255,255,255,0);">USER <span style="background-color: rgba(255,255,255,0);">--- <span style="background-color: rgba(255,255,255,0);">TEST

<span style="background-color: rgba(255,255,255,0);">USERENV <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">USERENV ('parameter' ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回关于当前会话的信息. <span style="background-color: rgba(255,255,255,0);">参数： <span style="background-color: rgba(255,255,255,0);">parameter可以是CLIENT_INFO，ENTRYID，ISDBA，LANG，LANGUAGE，SESSIONID，SID，TERMINAL. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTUSERENV('LANGUAGE') language FROM DUAL;

<span style="background-color: rgba(255,255,255,0);">LANGUAGE <span style="background-color: rgba(255,255,255,0);">-- <span style="background-color: rgba(255,255,255,0);">AMERICAN_AMERICA.ZHT16MSWIN950

<span style="background-color: rgba(255,255,255,0);">VARIANCE <span style="background-color: rgba(255,255,255,0);">语法： <span style="background-color: rgba(255,255,255,0);">VARIANCE ( [DISTINCT | ALL ] expr ) <span style="background-color: rgba(255,255,255,0);">用法： <span style="background-color: rgba(255,255,255,0);">返回expr表达式的方差. <span style="background-color: rgba(255,255,255,0);">例子： <span style="background-color: rgba(255,255,255,0);">SQL> SELECTVARIANCE(sal) FROM scott.emp;

<span style="background-color: rgba(255,255,255,0);">VARIANCE(SAL) <span style="background-color: rgba(255,255,255,0);">- <span style="background-color: rgba(255,255,255,0);"> 1398313.87

<span style="background-color: rgba(255,255,255,0);">好文要顶 关注我 收藏该文 <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0); color: #000000;">[|辞久] <span style="background-color: rgba(255,255,255,0); color: #000000;">[|关注 - 6] <span style="background-color: rgba(255,255,255,0); color: #000000;">[|粉丝 - 11]

<span class="diggnum" style="background-color: rgba(255,255,255,0);">0 <span class="burynum" style="background-color: rgba(255,255,255,0);">0

<span style="background-color: rgba(255,255,255,0);">posted on 2016-01-10 19:58 [|辞久] 阅读(353) 评论(...) [|编辑] [|收藏] <span style="background-color: rgba(255,255,255,0);">努力加载评论中... <span style="background-color: rgba(255,255,255,0);"> <span style="background-color: rgba(255,255,255,0);">刷新评论[|刷新页面][|返回顶部]