相关整理Oracle

1、 函数

日期时间相关：

    To_Date(时间字段,时间格式) 字符串转换为日期、时间格式

    TO_DATE('2004-8-4','YYYY-MM-DD’);

    insert into ins_date values(to_date('2004-08-10 15:33:12','yyyy-mm-dd hh24:mi:ss'));

  insert into tdate(col2) values(to_date('2004-8-10 下午 03:33:12',15:17:26   2 'yyyy-mm-dd am hh:mi:ss'));

  Trunc(Sysdate)

(1).TRUNC(for dates)

TRUNC函数为指定元素而截去的日期值。

其具体的语法格式如：TRUNC（date[,fmt]）

date一个日期值

fmt日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去


select trunc(sysdate)-to_date('2004-11-4','yyyy-mm-dd') from dual;

select TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss') from dual;

  --1999-12-23 8:40:44

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss')) from dual;

  --1999-12-23

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'yyyy') from dual;

  --1999-01-01

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'MM') from dual;

  --1999-12-01

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'dd') from dual;

  --1999-12-23

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'hh') from dual;

  --1999-12-23 8:00:00

select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'mi') from dual;

  --1999-12-23 8:40:00
(2).TRUNC(for number)

TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。

其具体的语法格式如下

TRUNC（number[,decimals]）

number待做截取处理的数值

decimals指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

下面是该函数的使用情况：

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
SYSDATE 取得数据库服务器当前日期、时间
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

NEXT_DAY

LAST_DAY

ADD_MONTHS

MONTHS_BETWEEN

SELECT LAST_DAY(SYSDATE) FROM DUAL
日期的各部分的常用的的写法

取时间点的年份的写法：

SELECT TO_CHAR(SYSDATE，'YYYY') FROM DUAL；

取时间点的月份的写法：

SELECT TO_CHAR(SYSDATE，'MM') FROM DUAL；

取时间点的日的写法：

SELECT TO_CHAR(SYSDATE，'DD') FROM DUAL；

取时间点的时的写法：

SELECT TO_CHAR(SYSDATE，'HH24') FROM DUAL；

取时间点的分的写法：

SELECT TO_CHAR(SYSDATE，'MI') FROM DUAL；

取时间点的秒的写法：

SELECT TO_CHAR(SYSDATE，'SS') FROM DUAL；

取时间点的日期的写法：

SELECT TRUNC(SYSDATE) FROM DUAL；

取时间点的时间的写法：

SELECT TO_CHAR(SYSDATE，'HH24：MI：SS') FROM DUAL；

日期，时间形态变为字符形态：

SELECT TO_CHAR(SYSDATE) FROM DUAL；

将字符串转换成日期或时间形态：

SELECT TO_DATE('2003/08/01') FROM DUAL；

返回参数的星期几的写法：

SELECT TO_CHAR(SYSDATE，'D') FROM DUAL；

返回参数一年中的第几天的写法：

SELECT TO_CHAR(SYSDATE，'DDD') FROM DUAL；

返回午夜和参数中指定的时间值之间的秒数的写法：

SELECT TO_CHAR(SYSDATE，'SSSSS') FROM DUAL；

返回参数中一年的第几周的写法：

SELECT TO_CHAR(SYSDATE，'WW') FROM DUAL；

数子相关：

To_Number()

+、-、*、/

  ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND

  SIN、SINH、SQRT、TAN、TRUNC

AVG、COUNT、MAX、MIN、SUM

GREATEST　返回参数的最大值

LEAST　返回参数的最小值

SELECT GREATEST(3, 4, 5)*4 FROM DUAL;

SELECT POWER(2,3) FROM DUAL;
字符串相关：

To_Char()

To_Char(日期字段,'yyyymmdd')

|| 连接两个字符串

SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE;

TRIM/LTRIM/RTRIM 截断串左（右）指定字符串（包括空串）

ASCII(CHAR)　得到字符串的第一个字符的ASCII值

SELECT ASCII('ABCDE') FROM DUAL；

结果： 65

CHR(N)　得到数值N指定的字符

SELECT CHR(68) FROM DUAL；

结果： D

CONCAT(CHAR1，CHAR2)　如何连接两个字符串

SELECT CONCAT('ABC'，'DEFGH') FROM DUAL；

结果： 'ABCDEFGH'

DECODE(CHAR，N1，CHAR1，N2，CHAR2...)　如何将列中的数值代替为字符串

SELECT DECODE(DAY，1，'SUN'，2，'MON') FROM DUAL；

INITCAP(CHAR) 将字符串CHAR的第一个字符为大写，其余为小写

SELECT INITCAP('ABCDE') FROM DUAL；

LENGTH(CHAR) 取一字符串CHAR的长度。

SELECT LENGTH('ABCDE') FROM DUAL；

LOWER(CHAR) 将字符串CHAR全部变为小写。

SELECT LOWER('ABCDE') FROM DUAL；

LPAD(CHAR1，N，CHAR2) 用字符串CHAR2包括的字符左填CHAR1，使其长度为N

SELECT LPAD('ABCDEFG'，10'123') FROM DUAL；

结果： '123ABCDEFG'

LTRIM(CHAR，SET) 从字符串CHAR的左边移去字符串SET中的字符，直到第一个不是SET中的字符为止。

SELECT ('CDEFG'，'CD') FROM DUAL；

结果： 'EFG'

NLS_INITCAP(CHAR) 取字符CHAR的第一个字符大写，其余字符为小写。

SELECT NLS_INITCAP('ABCDE') FROM DUAL；

NLS_LOWER(CHAR) 将字符串CHAR包括的字符全部小写。

SELECT NLS_LOWER('AAAA') FROM DUAL；

NLS_UPPER(CHAR) 将字符串CHAR包括的字符全部大写。

SELECT NLS_UPPER('AAAA') FROM DUAL；

REPLACE(CHAR1，CHAR2，CHAR3) 用字符串CHAR3代替每一个列值为CHAR2的列，其结果放在CHAR1中

SELECT REPLACE(EMP_NO，'123'，'456') FROM DUAL；

RPAD(CHAR1，N，CHAR2) 用字符串CHAR2右填字符串CHAR1，使其长度为N

SELECT RPAD('234'，8，'0') FROM DUAL；

RTRIM(CHAR，SET) 移去字符串CHAR右边的字符串SET中的字符，直到最后一个不是SET中的字符为止

SELECT RTRIM('ABCDE'，'DE') FROM DUAL；

SUBSTR(CHAR，M，N) 得到字符串CHAR从M处开始的N个字符. 双字节字符，一个汉字为一个字符的

SELECT SUBSTR('ABCDE'，2，3) FROM DUAL；

TRANSLATE(CHAR1，CHAR2，CHAR3) 将CHAR1中的CHAR2的部分用CHAR3代替。

SELECT TRANSLATE('ABDCDEFGdH','DE','MN') FROM DUAL; 结果： ABMCMNFGdH

SELECT translate('12a3.456','0123456789.',' ') FROM DUAL; 结果：a

UPPER(CHAR) 将字符串CHAR全部为大写。

TO_MULTI_BYTE(CHAR) 将半角转换为全角。

select 'a',to_multi_byte('a') from dual;   结果：a ａ

系统函数：

DECODE函数

格式：DECODE(value, if1, then1, if2, then2...,else)

例子：

  假设表ROYAL_DTABLE中有如下数据：

RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD

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

1         royal     1

2         bill       2

3         joy       1



请观察如下SQL语句输出结果。

SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;

DC

---------

royaltest

billgates

joy

2、 语法

having子句的用法

having子句对group by子句所确定的行组进行控制，having子句条件中只允许涉及常量,聚组函数或group by 子句中的列。

外部联接"+"的用法

外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的

行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in

运算,大大提高运行速度.例如,下面这条命令执行起来很慢

用外联接提高表连接的查询速度

在作表连接(常用于视图)时，常使用以下方法来查询数据:

SELECT PAY_NO, PROJECT_NAME

FROM A

WHERE A.PAY_NO NOT IN (SELECT PAY_

NO FROM B WHERE VALUE >=120000);
---- 但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOT IN要进 行一条一条的比较，共需要10000*10000

次比较后，才能得到结果。该用外联接后，可以缩短到1分左右的时间:

SELECT PAY_NO,PROJECT_NAME

FROM A,B

WHERE A.PAY_NO=B.PAY_NO(+)

AND B.PAY_NO IS NULL

AND B.VALUE >=12000;

3、 SQL语句

Select 检索相关：

设置日期语言

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

也可以这样

TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

可查看

select * from nls_session_parameters

select * from V$NLS_PARAMETERS

处理不同时区

select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate from dual;

时间为null的用法

select id, active_date from table1

UNION

select 1, TO_DATE(null) from dual;

注意：要用TO_DATE(null)

求某天是星期几

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;

显示：星期一

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;

显示：monday

查询两个日期间的天数

select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

查询某天的数据

select * from table_name where trunc(日期字段)＝to_date('2003-05-02'，'yyyy-mm-dd')；

查询某时间段的数据

select * from table_name where 日期字段>=To_Date('2004-09-01','YYYY-MM-DD hh24:mi:ss') AND 日期字段<=

To_Date('2004-09-30','YYYY-MM-DD hh24:mi:ss')；

查询某月中所有周五的具体日期

select to_char(t.d,'YY-MM-DD') from (

select trunc(sysdate, 'MM')+rownum-1 as d

from dba_objects

where rownum < 32) t

where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期

and trim(to_char(t.d, 'Day')) = '星期五'

--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。

日期时间间隔操作

当前时间减去7分钟的时间

select sysdate,sysdate - interval '7' MINUTE from dual

当前时间减去7小时的时间

select sysdate - interval '7' hour from dual

当前时间减去7天的时间

select sysdate - interval '7' day from dual

当前时间减去7月的时间

select sysdate,sysdate - interval '7' month from dual

当前时间减去7年的时间

select sysdate,sysdate - interval '7' year   from dual

时间间隔乘以一个数字

select sysdate,sysdate - 8 *interval '2' hour   from dual

日期到字符操作

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

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual

select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual

参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)

字符到日期操作

select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual

具体用法和上面的to_char差不多。

毫秒级的数据类型

--返回当前时间 年月日小时分秒毫秒

select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;

--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)

select to_char(current_timestamp(9),'MI:SSxFF') from dual;

获取当前年龄、天数

SELECT TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(birthday,'YYYY') Age FROM 表名；

SELECT TO_CHAR(sysdate,'yyyyMMdd')-TO_CHAR(birthday,'yyyyMMdd') Days FROM 表名；

查找出前N条记录

SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno；

查找表中[N，M]条记录

取得某列中第N大的行

select column_name from

(select table_name.*,dense_rank() over (order by column desc) rank from table_name)

where rank = &N；

假如要返回前5条记录：

select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)

假如要返回第5-9条记录：

select * from tablename

where …

and rownum<10

minus

select * from tablename

where …

and rownum<5

order by name

选出结果后用name排序显示结果。(先选再排序)

注意：只能用以上符号(<、<=、!=)。

select * from tablename where rownum != 10;返回的是前９条记录。

不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件 不成立，查不到记录.

另外，这个方法更快：

select * from (

select rownum r,a from yourtable

where rownum <= 20

order by name )

where r > 10

这样取出第11-20条记录!(先选再排序再选)

要先排序再选则须用select嵌套：内层排序外层选。

rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum 是在 查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:

1： 假如 判定条件是常量，则：

只能 rownum = 1, <= 大于1 的自然数， = 大于1 的数是没有结果的，大于一个数也是没有结果的

即 当出现一个 rownum 不满足条件的时候则 查询结束 　　this is stop key!

2: 当判定值不是常量的时候

若条件是 = var , 则只有当 var 为1 的时候才满足条件，这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数

据进行判定

选出一行后才能去选rownum=2的行……

查找一列中第N大的值

select * from (select t.*，dense_rank() over (order by sal) rank from employee) where rank = N；

查找、删除重复记录

法一: 用Group by语句此查找很快的

select count(num), max(name) from student --查找表中num列重复的，列出重复的记录数，并列出他的name属性

group by num

having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次

delete from student(上面Select的)

这样的话就把所有重复的都删除了。-----慎重

法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:

---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录

SELECT * FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相当于First,Second

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

---- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录

DELETE FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

两个结果集互加的函数

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW；

SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW

SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW；

两个结果集互减的函数

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW；

把select出来的结果导到一个文本文件中

SQL>SPOOL C：/ABCD.TXT；

SQL>select * from table；

SQL >spool off；

中文排序

Oracle9i之前，中文是按照二进制编码进行排序的

在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值

SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序

SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序

SCHINESE_PINYIN_M 按照拼音排序