全部博文（136）
分类： Oracle
2008-04-11 16:43:35
TO_CHAR (character)
语法
to_char_char::= TO_CHAR ( nchar | clob | nclob )
功能
将以下类型的数据转换成数据库字符集中的类型：NCHAR, NVARCHAR2, CLOB, 或NCLOB。
举例
这是一个简单的小例子，可以把它和TO_CHAR (number)中的第一个例子比较：
|
SELECT TO_CHAR('01110') FROM DUAL; TO_CH ----- 01110 |
下面这个例子中，将表pm.print_media中的一些CLOB数据转换数据库字符集的类型：
|
SELECT TO_CHAR(ad_sourcetext) FROM print_media WHERE product_id = 2268; TO_CHAR(AD_SOURCETEXT) -------------------------------------------------------------------- ****************************** TIGER2 2268...Standard Hayes Compatible Modem Product ID: 2268 The #1 selling modem in the universe! Tiger2's modem includes call management and Internet voicing. Make real-time full duplex phone calls at the same time you're online. ********************************** |
TO_CHAR (number)
语法
to_char_number::= TO_CHAR ( n [, fmt [, 'nlsparam']] )
功能
将NUMBER类型的n 按照指定的fmt格式转换成VARCHAR2类型。如果省略fmt， 则n 被按照原来的长度转换成VARCHAR2，以保持其精度不变。
参数'nlsparam' 指定了返回值的格式：
该参数格式如下：
'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
字符d代表小数点，字符g代表分隔符，它们必须是不同的单字节字符。记得在单引号内，两个单引号代表一个单引号字符。货币字符长度为10个字符（不足10个字符，则左填充空格）。
如果省略参数 'nlsparam' 或它内部的任何参数，该函数将使用会话中的默认参数值。
举例
下面的例子中，使用隐式转换将一个字符串转换成数字（可以将它与TO_CHAR (character)中的第一个例子进行比较）：
|
SELECT TO_CHAR('01110' + 1) FROM dual; TO_C ---- 1111 |
下面的例子中，你将看到在货币符号的左边填充了空格，以达到10个字符长度：
|
SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL; Amount -------------- $10,000.00- SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ------------------- AusDollars10.000,00- |
TO_CHAR (datetime)
语法
to_char_date::= TO_CHAR ( date [, fmt [, 'nlsparam' ]] )
功能
TO_CHAR (datetime) 将以下日期类型的数据按照指定的fmt格式转换成VARCHAR2类型：DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, 或TIMESTAMP WITH LOCAL TIME ZONE。如果省略格式fmt,将按照以下格式进行转换：
参数 'nlsparams' 指定了返回值显示的语言（年月的名字和缩写）。该参数的格式为：
'NLS_DATE_LANGUAGE = language'
如果省略 'nlsparams', 该函数将按照默认的日期语言返回结果。
举例
下面的例子中，将使用这个表：
|
CREATE TABLE date_tab ( ts_col TIMESTAMP, tsltz_col TIMESTAMP WITH LOCAL TIME ZONE, tstz_col TIMESTAMP WITH TIME ZONE); |
下面的这些例子中，将使用TO_CHAR函数转换不同的时间戳类型。其中TIMESTAMP WITH LOCAL TIME ZONE的结果与会话设定的时区相关。而TIMESTAMP和TIMESTAMP WITH TIME ZONE的结果与会话设定的时区无关：
|
ALTER SESSION SET TIME_ZONE = '-8:00'; INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00', TIMESTAMP'1999-12-01 10:00:00'); INSERT INTO date_tab VALUES ( TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00', TIMESTAMP'1999-12-02 10:00:00 -8:00'); SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM date_tab; TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00 SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM date_tab; SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY --------------- ------------------------------ -08:00 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00.000000 ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'), TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') FROM date_tab; TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI: ------------------------------ ------------------------------------- 01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00 02-DEC-1999 10:00:00.000000 02-DEC-1999 10:00:00.000000 -08:00 SELECT SESSIONTIMEZONE, TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') FROM date_tab; SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY ------------------------- ------------------------------ -05:00 01-DEC-1999 13:00:00.000000 -05:00 02-DEC-1999 13:00:00.000000 |
MONTHS_BETWEEN
语法
months_between::= MONTHS_BETWEEN ( date1 , date2 )
功能
返回日期date1和date2之间的月数。如果date1大于date2，则返回正数；否则返回负数。如果date1和date2的月份具有相同的天数，或都是对应月份的最后一天，则返回值永远是正数。否则Oracle会按照一个月有31天，来计算日期date1和date2之间的差值，计算时还会考虑到二者的时差。
举例
下面的例子中，将返回两个日期之间的差值：
|
SQL> select months_between( 2 to_date('20070430', 'yyyymmdd'), 3 to_date('20070227', 'yyyymmdd') 4 ) months_between 5 from dual; MONTHS_BETWEEN -------------- 2.09677419 |
后面的小数是怎么计算的呢？我们在功能里讲了，Oracle是按照每月31天来计算的。那么，就认为2007年2月有31天咯。那么这两个日期相差3天：
|
SQL> select 3/31 from dual; 3/31 ---------- .096774194 SQL> |
下面的这个例子中，你将看到若两个日期的“日”相同，或者都是对应月份的最后一天，则返回值永远是整数：
|
SQL> select months_between( 2 to_date('20070430', 'yyyymmdd'), 3 to_date('20070330', 'yyyymmdd') 4 ) months_between 5 from dual 6 ; MONTHS_BETWEEN -------------- 1 SQL> ed 已写入文件 afiedt.buf 1 select months_between( 2 to_date('20070430', 'yyyymmdd'), 3 to_date('20070331', 'yyyymmdd') 4 ) months_between 5* from dual 6 / MONTHS_BETWEEN -------------- 1 SQL> |
LAST_DAY
语法
last_day::= LAST_DAY ( date )
功能
返回输入日期中，该月份的最后一天的日期。
举例
下面的语句确定当前月份还剩下多少天：
|
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 30-MAY-01 31-MAY-01 1 |
下面的语句中，给每个雇员从雇佣期开始增加5个月，作为试用期：
|
SELECT last_name, hire_date, TO_CHAR( ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date" FROM employees; LAST_NAME HIRE_DATE Eval Date ------------------------- --------- --------- King 17-JUN-87 30-NOV-87 Kochhar 21-SEP-89 28-FEB-90 De Haan 13-JAN-93 30-JUN-93 Hunold 03-JAN-90 30-JUN-90 Ernst 21-MAY-91 31-OCT-91 Pataballa 05-FEB-98 31-JUL-98 Lorentz 07-FEB-99 31-JUL-99 . . . |
ADD_MONTHS
语法
add_months::= ADD_MONTHS(d,n)
功能
返回比日期d大n个月的日期，参数n可以是任意整数。如果d是该月份的最后一天，或者返回值月份的天数比日期d的天数少，则返回值是应返回月份的最后一天；否则返回值与d的日期中的天（day）是一致的。
举例
下面例子中，将返回hire_date的下个月的日期：
|
SELECT TO_CHAR( ADD_MONTHS(hire_date,1), 'DD-MON-YYYY') "Next month" FROM employees WHERE last_name = 'Baer'; Next Month ----------- 07-JUL-1994 |
NVL
语法
nvl::= NVL ( expr1 , expr2 )
功能
将NULL替换成指定的值。如果expr1是NULL，则返回expr2；否则返回expr1。
参数expr1和expr2可以是任意数据类型。如果它们的数据类型不同，Oracle在比较之间，会将expr2转换成expr1的数据类型。
返回值的数据类型总是与expr1相同。但如果expr1是字符数据，则返回值会是VARCHAR2类型，并且与expr1的字符集相同。
举例
下面例子中，返回雇员的姓名和佣金。如果某雇员没收到佣金，则返回"Not Applicable"：
|
SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') "COMMISSION" FROM employees WHERE last_name LIKE 'B%' ORDER BY last_name; LAST_NAME COMMISSION ------------------------- --------------------- Baer Not Applicable Baida Not Applicable Banda .1 Bates .15 Bernstein .25 Bissot Not Applicable Bloom .2 Bull Not Applicable |
DECODE
语法
decode::= DECODE(expr,search,result[,search,result]...[,default])
功能
将expr与search逐一比较，若二者相等，则返回对应的result；若全部不相等，则返回default，若省略default，则返回null。
如果expr和search中是字符类型的数据，采用非填充方式（nonpadded）进行比较。expr, search, 和 result可以是如下数据类型：CHAR, VARCHAR2, NCHAR, or NVARCHAR2。返回的字符串类型是VARCHAR2,并且与第一个result参数具有相同的字符集。
search, result, 和default 可以是表达式。Oracle在与expr 比较时才会计算search 中表达式的值，而不是在与expr 比较前计算出所有search的值。因此，当一个search 与expr 匹配上之后，下面所有search 的值，Oracle都不会计算。
Oracle在比较之前，会自动将expr和search转换成第一个search的数据类型。Oracle也会自动将返回值转换成和第一个result的相同数据类型。如果第一个result是CHAR类型，或第一个result是null，Oracle会将数据类型转换成VARCHAR2。
在DECODE函数中，Oracle认为两个NULL是相等的。也就是说，当expr是NULL时，Oracle将会返回第一个为NULL的search所对应的result。
DECODE函数的最大参数是255（包括expr, searches, results, 和default）。
举例
在下面的例子中，如果warehouse_id的值是1，则DECODE函数将返回'Southlake'；如果值是2，则返回'San Francisco'；……依次类推。如果warehouse_id的值不是1，2，3，和4，则DECODE函数将返回'Non-domestic'。
|
SELECT product_id, DECODE (warehouse_id, 1, 'Southlake', 2, ' 3, ' 4, ' 'Non-domestic') "Location of inventory" FROM inventories WHERE product_id < 1775; |
下面的这个例子中，存在除数为0的情况，但由于前面匹配上了，所以Oracle不会计算后面的值：
|
SQL> SELECT decode(1, 1, 10, 2 2/0, 20/0, 3 30) 4 from dual; DECODE(1,1,10,2/0,20/0,30) -------------------------- 10 |
而如果前面匹配不上，则就会计算这个值了：
|
SQL> SELECT decode(1, 3, 10, 2 2/0, 20/0, 3 30) 4 from dual; 2/0, 20/0, * ERROR 位于第 2 行: ORA-01476: 除数为 0 SQL> |
下面这个例子中，你将会看到NULL也是可以匹配上的：
|
SQL> SELECT decode(NULL, 3, 10, 2 NULL, 20, 3 30) 4 FROM dual; DECODE(NULL,3,10,NULL,20,30) ---------------------------- 20 |