oracle 的to_char、to_number、to_date用法

oracle 的to_char、to_number、to_date用法

 (2010-11-04 17:05:54)

转载▼

标签：  杂谈

关键字: oracle 的to_char、to_number、to_date用法

TO_CHAR 是把日期或数字转换为字符串
TO_DATE 是把字符串转换为数据库中得日期类型转换函数
TO_NUMBER 将字符转化为数字

 TO_CHAR
使用TO_CHAR函数处理数字
TO_CHAR(number, '格式')
TO_CHAR(salary,’$99,999.99’);
使用TO_CHAR函数处理日期
TO_CHAR(date,’格式’);

 TO_NUMBER
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char[, '格式'])

 TO_DATE
使用TO_DATE函数将字符转换为日期
TO_DATE(char[, '格式'])

 数字格式格式
9 代表一个数字
0 强制显示0
$ 放置一个$符
L 放置一个浮动本地货币符
. 显示小数点
, 显示千位指示符

 日期格式
格式控制 描述
YYYY、YYY、YY 分别代表4位、3位、2位的数字年
YEAR 年的拼写
MM 数字月
MONTH 月的全拼
MON 月的缩写
DD 数字日
DAY 星期的全拼
DY 星期的缩写
AM 表示上午或者下午
HH24、HH12 12小时制或24小时制
MI 分钟
SS 秒钟
SP 数字的拼写
TH 数字的序数词

“特殊字符” 假如特殊字符
HH24:MI:SS AM 15:43:20 PM

日期例子：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL

日期说明：
当省略HH、MI和SS对应的输入参数时，Oracle使用0作为DEFAULT值。如果输入的日期数据忽略时间部分，Oracle会将时、分、秒部分都置为0，也就是说会取整到日。

同样，忽略了DD参数，Oracle会采用1作为日的默认值，也就是说会取整到月。

但是，不要被这种“惯性”所迷惑，如果忽略MM参数，Oracle并不会取整到年，取整到当前月。

注意：
1.在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份

 

下面是时间经典的SQL查询：


select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss:pm:dy') from dual;

                         年  月 日 24制小时 分 秒 上/下午 星期中文;


--获取11月天数--select to_char(last_day(to_date('2010-11-1','YYYY-MM-DD')),'DD') from dual;
--获取12月天数--select to_char(last_day(to_date('2010-12-1','YYYY-MM-DD')),'DD') from dual;


显示上个礼拜一到礼拜日
SELECT to_char(SYSDATE,'yyyymmdd')-to_number(to_char(SYSDATE,'d')-1)-6,to_char(SYSDATE,'yyyymmdd')-to_number(to_char(SYSDATE,'d')-1) from dual 


1.显示本月第一天

SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),
120)+'01',120)

2.显示本月最后一天

select dateadd(day,-1,convert(datetime,convert
(varchar(8),dateadd(month,1,getdate()),120)+'01',120))
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))


3.上个月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))

4.本月的第一个星期一i

select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart(
day,getdate()),getdate())),0)


5.本年的第一天

SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)

6.本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))

7.去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))


8.本季度的第一天

SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0)


9.本周的星期一

SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)


10.查询本月的记录

select * from tableName where DATEPART(mm, theDate)
=DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) 
= DATEPART(yy, GETDATE())

11.查询本周的记录

select * from tableName where DATEPART(wk, theDate) = DATEPART
(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

12查询本季的记录 注：其中:GETDATE()是获得系统时间的函数。

select * from tableName where DATEPART(qq, theDate) = DATEPART
(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

13.获取当月总天数:

select DATEDIFF(dd,getdate(),DATEADD
(mm, 1, getdate()))

select datediff(day,
dateadd(mm, datediff(mm,'',getdate()), ''),
dateadd(mm, datediff(mm,'',getdate()), '1900-02-01'))

14.获取当前为星期几

DATENAME(weekday, getdate())

提高ORACLE的速度（1） (2010-11-26 10:26:25)转载▼ 标签： it 提高ORACLE的执行速度需要从软件、硬件等方面考虑，软件主要指SQL语句本身、数据库部署与配置等等，硬件主要指机器的硬件配置，主要指标是CPU、内存等等。真正能够显著提高数据库执行速度的还是SQL语句本身，索引就是首先考虑的对象。 1、 WHERE条件的拼写规则 1.1、 在WHERE条件中字段放在逻辑运算符的左边 WHERE条件有一个或多个逻辑运算，一个逻辑运算一般包括三块：字段、运算符（例如）、值。其中，运算符例如=、>，还有数据库支持的其他运算符，例如IN、LIKE、EXISTS等等；值包括一般是常量，字段或字段组合，也可能是个数据库支持语句。 例如 WHERE comm_workdate>=’ 20100101’ 中，comm_workdate是字段，>=是逻辑运算符，20100101是个常量。 在WHERE条件中，字段要放在逻辑运算符的左边。如果这个条件写成 WHERE ’20100101’<=comm_workdate 那就大错特错了，尽管逻辑上没错。 1.2、 在WHERE条件中运算放在逻辑运算符右边 运算符就是加、减、乘、除之类的。例如 WHERE amount1>=amount2*5+100 中，amount1、amount2是字段，>=是个运算符，5、100是个常量。 从业务逻辑上讲，这个条件也可以写成 WHERE amount2<= (amount1-100)/5 以上条件的两个写法都没错，究竟使用哪个表达式可以结合索引。参考下文。 但如果这个条件写成 WHERE (amount1-100)/5>=amount2 那就大错特错了，尽管逻辑上没错。 1.3、 在WHERE条件中数据集少的逻辑计算先写 当WHERE条件有两个或两个以上的逻辑运算时，数据集少的需要先写。简单地说，像使用了=的逻辑运算符的，应该先写，对于>、<、LIKE、EXISTS、IN、IS NULL、NOT等等其他运算符的需要后写。 例如 WHERE amount1=100 AND login_date LIKE ‘201001%’ 如果写成 WHERE login_date LIKE ‘201001%’ AND amount1=100 那就大错特错了，尽管逻辑上没错。 1.4、 在WHERE条件中尽量避免非表达式 非表达式例如!=、NOT IN、NOT LIKE、NOT EXISTS等等，我们应尽量避免使用，而用等价的表达式。 例如 SELECT t1.fld1, …, t1.fld11 FROM my_table1 t1 WHERE fundid NOT IN (‘000001’,’000002’) 不管是否存在以fundid为第一个字段的索引，我们要尽量采用下列的等价表达式，尽管这样的语句看起来可能显得啰嗦： SELECT t1.fld1, …, t1.fld11 FROM my_table1 t1 WHERE fundid IN (‘000003’,’000004’,…) 1.5、 多表关联时表关联关系一定要先写 多表关联时，表关联关系一定要先写，这是结构化查询语言的笛卡儿积理论决定的。例如 SELECT t1.fld1, t2.fld2, t2.fld10, t2.fld11 FROM my_table1 t1, my_table2 t2 WHERE t1.id=t2.id AND t1.fld3=:var1 AND t2.fld15>=:var2 这里，数据来自my_table1、my_table2两个表，两个表的关联条件是：my_table1的字段id与my_table2的字段相等，另外的单表的附加条件。 这一点新手可能会犯错。例如把WHERE条件写成 WHERE t1.fld3=:var1 AND t2.fld15>=:var2 AND t1.id=t2.id 这就是一种错误，尽管业务逻辑上没错。 1.6、 简单原则 SQL语句执行前数据库要对语句进行解析，解析成它认为的简单语句。 喜爱写复杂的SQL并不是个好习惯，过分复杂的SQL数据库解析得往往不尽如人意。事实上，将复杂的SQL拆开，有时候会极大地提高效率，因为能获得很好的优化。 2、 单表操作的WHERE条件和索引 根据索引组织WHERE条件和根据WHERE条件建立索引是同一个意义，有时候我们需要根据条件来建索引，有时我们需要根据索引组织条件，其意义是一样的：提高速度。表的记录越多，索引的效果越明细，大于10万条记录的表操作的SQL语句，在追求逻辑准确的同时必须考虑SQL的执行速度。 去了解索引的机制、存储方式，你就会知道为什么索引会提高速度，但这些这里不打算讲。 同样的数据库环境，同样的SQL语句，如果建立了相应的索引，速度可以提高10倍甚至几十、几百、几千倍。有的同志说，我已经建立了索引，怎么速度还是没提高？其实，他不懂得数据库的内部潜规则 也不是说索引越多就越好。建立索引需要消耗更多的磁盘空间，即所谓“以空间换时间”理念。特别是大量从来或者几乎不用的索引，对系统只有损害。数据库在进行增、删、改记录的同时，其内部的索引也同时要计算，索引越多计算的工作量就越大，所以索引太多对增、删、改会是致命的打击。OLTP系统每表超过5个索引即会降低性能，而且在一个SQL中，ORACLE从不能使用超过5个索引。 所以，索引不可不建，也不可滥建，要综合考虑。还要考虑那些SQL语句操作的频率。 那么怎么建立索引才能有效提高速度呢？ WHERE条件使用索引，是基于下列的潜规则：WHERE子句中的第一个字段，必须是索引的第一个字段。 这个索引可以是单列索引，也可以是复合索引。所谓单列索引就是一个字段建立的索引，复合索引就是多个字段建立的索引。 注意：WHERE条件中第一个字段不要使用IS NULL、IS NOT NULL、!=等逻辑运算。 2.1、 只有一个字段的WHERE条件 例如经常有这样的操作： SELECT fld1, fld2, …,fldn FROM dapehost.all_xxxx WHERE comm_workdate>=’20100101’ 数据库解析器查找以comm_workdate建立的单列索引，这样的速度最快。如果没有，数据库会使用第一个字段为comm_workdate的复合索引，速度也较快，例如idx(comm_workdate,comm_organ)。其他索引对于本例没意义。 如果没有以comm_workdate建立的单列索引而有多个第一个字段为comm_workdate的复合索引，数据库会根据其分析器分析的结果使用某个它认为最优的索引，如果人工指定，可以使用下面的语法： SELECT fld1, fld2…, fldn FROM dapehost.all_xxxx t WHERE comm_workdate>=’20100101’ DELETE FROM dapehost.all_xxxx t WHERE comm_workdate>=’20100101’ 注意，注释符必须跟在SELECT之后，且注释中的“+”要紧跟着注释起始符“/*”，否则hint就被认为是一般注释。 2.2、 两个字段的WHERE条件 例如经常有这样的操作： UPDATE dapehost.all_xxxx SET fld=var WHERE comm_workdate=’20100101’ AND fundid=’000001’ 数据库解析器首先查找以comm_workdate,fundid开头的索引，这样的索引速度最快。如果没有这样的索引，数据库会查找第一个字段为comm_workdate的索引，速度也较快，例如idx(comm_workdate,comm_organ)等等。其他索引对于本例没意义。 2.3、 多个字段的WHERE条件 上文说过，不是说索引越多越好，也就不必为每种条件的字段顺序建立一个索引，多个字段的情况下，可以以全部字段也可以WHERE条件的前面部分字段建立索引。 UPDATE dapehost.all_xxxx SET fld=var WHERE comm_workdate=’20100101’ AND fundid=’000001’ AND currtype=’001’ 数据库解析器首先查找以comm_workdate,fundid,currtype开头的索引，如果没有这样的索引，数据库会查找以comm_workdate,fundid开头的索引，还没有的话，就找以comm_workdate开头的索引。 2.4、 为了使用索引WHERE条件可能需要多写字段 举个例子，现在有个数据表，它只有一天的记录，记录可能有百万条以上，它有索引idx(comm_workdate,comm_organ)，但没有索引idx(comm_organ)。这时WHERE条件写成 WHERE comm_organ=’12345678’ … 不好，因为没有可用的索引，需要写成： WHERE comm_workdate=’xxxxxxxx’ AND comm_organ=’12345678’ … 这样才能使用到索引idx(comm_workdate,comm_organ)。 2.5、 函数索引 ORACLE9i开始支持函数索引，例如 SELECT fld1, fld2, …,fldn FROM dapehost.all_xxxx WHERE TO_CHAR(create_date,’YYYYMMDD’)=’20100101’ 其中create_date是一个DATE型字段。那么，为了提高速度，可以考虑建立索引idx(TO_CHAR(create_date,’YYYYMMDD’))，因为这个索引字段使用了函数TO_CHAR，这样的索引就是函数索引。 3、 多表操作的where条件和索引 前面讲过多表关联时表关联关系一定要先写，这是结构化查询语言的原理要求的，还不是说索引。如果还建立了相应索引，速度会更快。例如 SELECT t1.fld1, t2.fld2, t2.fld10, t2.fld11 FROM my_table1 t1, my_table2 t2 WHERE t1.id=t2.id AND t1.fld3=:var1 AND t2.fld15>=:var2 根据结构化查询语言的笛卡儿积理论，需要建立下面一些索引： my_table1表：以id开头的索引1。 my_table2表：以id开头的索引1。 另外，为了进一步提高速度，可以再考虑建立其他索引： my_table1表：以fld3开头的索引2。 my_table2表：以fld5开头的索引2。 例如 SELECT t1.fld1, t2.fld2, t2.fld10, t2.fld11 FROM my_table1 t1, my_table2 t2 WHERE t1.id=t2.id AND t1.id2=t2.id3 AND t1.fld3=:var1 AND t2.fld15>=:var2 需要建立下面一些索引： my_table1表：以id、id2开头的索引1。 my_table2表：以id、id3开头的索引1。 4、 根据GROUP BY建立索引 如果查询语句有分组，对分组字段建立索引，可以提高SQL的速度。例如 SELECT fld1, fld2, COUNT(*) FROM dapehost.all_xxxx WHERE comm_workdate>=’20100101’ GROUP BY fld1, fld2 可以建立索引idx(fld1, fld2)。 5、 总结 本文讲了WHERE条件的拼写规则和如何建立索引来提高SQL速度，其实这方面的内容很多，这里也只讲了大概，甚至个别地方也可能说错了。大家可以在工作中要善于总结、查阅资料，不断充实自己。