SQL> -- 查询系统当前时间
SQL> select sysdate from dual;
SYSDATE
--------------
15-3月 -16
SQL> alter session set NLS_DATE_PARAMETER=‘yyyy-mm-dd‘;
alter session set NLS_DATE_PARAMETER=‘yyyy-mm-dd‘
*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
SQL> select * from v$nls_parameters;
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_CHARACTERSET
AL32UTF8
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
已选择19行。
SQL> set linesize 200;
SQL> /
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_CHARACTERSET
AL32UTF8
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
已选择19行。
SQL> col parameter for a50;
SQL> /
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择19行。
SQL> alter session set NLS_DATE_FORMAT=‘yyyy-mm-dd‘;
会话已更改。
SQL> select sysdate from dual;
SYSDATE
----------
2016-03-15
SQL> -- 改变时间的显示的另一种方式
SQL> select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYY-MM-DDHH24:MI:SS‘
--------------------------------------
2016-03-15 16:39:38
SQL> desc emp;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> -- oracle中如果你的字符串的格式满足日期的格式系统会根据你的需求帮你转换
SQL> -- 输出昨天 ，今天 和明天
SQL> select sysdate + 1 明天,sysdate 今天, sysdate - 1 昨天 from dual;
明天 今天 昨天
---------- ---------- ----------
2016-03-16 2016-03-15 2016-03-14
SQL> -- 明天减去今天等于多少
SQL> select (sysdate + 1) - (sysdate) from dual;
(SYSDATE+1)-(SYSDATE)
---------------------
1
SQL> -- 明天加今天等于多少
SQL> select (sydate + 1) + sysdate from dual;
select (sydate + 1) + sysdate from dual
*
第 1 行出现错误:
ORA-00904: "SYDATE": 标识符无效
SQL> -- 日期不能去做加法，只能加几天，可以做减法，减去一个日期或者几天
SQL> -- 根据入司时间计算一下emp表里面的所有员工的工龄
SQL> select (sysdate - hiredate) 天,(sysdate -hiredate)/7 周, (sysdate - hiredate)/30 月 from emp;
天 周 月
---------- ---------- ----------
12872.7023 1838.95747 429.090077
12807.7023 1829.67176 426.92341
12805.7023 1829.38604 426.856743
12766.7023 1823.81461 425.556743
12587.7023 1798.24319 419.590077
12737.7023 1819.67176 424.590077
12698.7023 1814.10033 423.290077
10558.7023 1508.38604 351.956743
12537.7023 1791.10033 417.92341
12607.7023 1801.10033 420.256743
10524.7023 1503.5289 350.82341
天 周 月
---------- ---------- ----------
12521.7023 1788.81461 417.390077
12521.7023 1788.81461 417.390077
12470.7023 1781.5289 415.690077
已选择14行。
SQL> select (sysdate - hiredate)/30 粗略, months_between(sysdate,hiredate) 精确 from emp;
粗略 精确
---------- ----------
429.090142 422.958202
426.923475 420.861427
426.856808 420.796911
425.556808 419.442073
419.590142 413.603363
424.590142 418.474331
423.290142 417.216266
351.956808 346.893685
417.923475 411.958202
420.256808 414.248524
350.823475 345.764653
粗略 精确
---------- ----------
417.390142 411.409814
417.390142 411.409814
415.690142 409.764653
已选择14行。
SQL> -- 在一个日期上加上几个月
SQL> select add_months(sysdate,5) from dual;
ADD_MONTHS
----------
2016-08-15
SQL> -- 查看当前月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
2016-03-31
SQL> -- 下一个日期 比如下一个星期六
SQL> select next_day(sysdate,‘星期二‘) from dual;
NEXT_DAY(S
----------
2016-03-22
SQL> -- 日期的四舍五入
SQL> select round(sysdate,‘year‘) from dual;
ROUND(SYSD
----------
2016-01-01
SQL> select round(sysdate,‘month‘) from dual;
ROUND(SYSD
----------
2016-03-01
SQL> select round(sysdate,‘day‘) from dual;
ROUND(SYSD
----------
2016-03-13
SQL> select sysdate from dual;
SYSDATE
----------
2016-03-15
SQL> select trunc(sysdate,‘day‘) from dual;
TRUNC(SYSD
----------
2016-03-13
SQL> -- 今天是星期二 天气真的很好
SQL> select to_char(sysdate,‘"今天是"yyyy-mm-dd day "天气真的很好") from dual;
ERROR:
ORA-01756: 引号内的字符串没有正确结束
SQL> select to_char(sysdate,‘"今天是"yyyy-mm-dd day "天气真的很好"‘) from dual;
TO_CHAR(SYSDATE,‘"今天是"YYYY-MM-DDDAY"天气真的很好"‘)
------------------------------------------------------------------------------------------------------
今天是2016-03-15 星期二 天气真的很好
SQL> -- 双引号的作用除了作为别名以外还可以作为日期格式里面的常亮
SQL> --常量
SQL> -- 将整数转换为一个字符串
SQL> select to_char(1524251,L999,999) from dual;
select to_char(1524251,L999,999) from dual
*
第 1 行出现错误:
ORA-00904: "L999": 标识符无效
SQL> select to_char(1524251,‘L999,999‘) from dual;
TO_CHAR(1524251,‘L999,999‘)
------------------------------------
##################
SQL> select to_char(1524251,‘L9,999,999‘) from dual;
TO_CHAR(1524251,‘L9,999,999‘)
----------------------------------------
￥1,524,251
SQL> -- L 表示本地货比
SQL> select to_number(‘12345‘,‘99,99‘) from dual;
select to_number(‘12345‘,‘99,99‘) from dual
*
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select to_number(‘12345‘,‘99,999‘) from dual
2 ;
select to_number(‘12345‘,‘99,999‘) from dual
*
第 1 行出现错误:
ORA-01722: 无效数字
SQL> select to_number(‘123‘) from dual;
TO_NUMBER(‘123‘)
----------------
123
SQL> -- 通用函数
SQL> -- nvl2 是nvl的一个加强版
SQL> -- nvl2(a,b,c) 如果a==null ,返回c否则返回b
SQL> select sal,nvl2(comm,comm,sal) from emp;
SAL NVL2(COMM,COMM,SAL)
---------- -------------------
800 800
1600 300
1250 500
2975 2975
1250 1400
2850 2850
2450 2450
3000 3000
5000 5000
1500 0
1100 1100
SAL NVL2(COMM,COMM,SAL)
---------- -------------------
950 950
3000 3000
1300 1300
已选择14行。
SQL> select sal,nvl2(comm,comm,sal), comm from emp;
SAL NVL2(COMM,COMM,SAL) COMM
---------- ------------------- ----------
800 800
1600 300 300
1250 500 500
2975 2975
1250 1400 1400
2850 2850
2450 2450
3000 3000
5000 5000
1500 0 0
1100 1100
SAL NVL2(COMM,COMM,SAL) COMM
---------- ------------------- ----------
950 950
3000 3000
1300 1300
已选择14行。
SQL> -- nullif(a,b) 如果a == b 返回空,如果不等于返回a
SQL> select nullif(‘abc‘,‘abc‘) from dual;
NULLIF
------
SQL> select nullif(‘abc‘,‘abcc‘) from dual;
NULLIF
------
abc
SQL> -- coalesce(a,b,c....) 从最左边开始查找，查找到第一个不为空的返回
SQL> select sal, comm, coalesce(sal,comm,empno) from emp;
SAL COMM COALESCE(SAL,COMM,EMPNO)
---------- ---------- ------------------------
800 800
1600 300 1600
1250 500 1250
2975 2975
1250 1400 1250
2850 2850
2450 2450
3000 3000
5000 5000
1500 0 1500
1100 1100
SAL COMM COALESCE(SAL,COMM,EMPNO)
---------- ---------- ------------------------
950 950
3000 3000
1300 1300
已选择14行。
SQL> select sal, comm, coalesce(comm,sal,empno) from emp;
SAL COMM COALESCE(COMM,SAL,EMPNO)
---------- ---------- ------------------------
800 800
1600 300 300
1250 500 500
2975 2975
1250 1400 1400
2850 2850
2450 2450
3000 3000
5000 5000
1500 0 0
1100 1100
SAL COMM COALESCE(COMM,SAL,EMPNO)
---------- ---------- ------------------------
950 950
3000 3000
1300 1300
已选择14行。
SQL> -- case 和decode ，case是sql99标准里面的内容,decode只有在oracle中支持
SQL> select sal 调前,
2 case job
3 when ‘PRESIDENT‘ then sal + 1000;
when ‘PRESIDENT‘ then sal + 1000
*
第 3 行出现错误:
ORA-00905: 缺失关键字
SQL> when ‘MANAGER‘ then sal + 800;
SP2-0734: 未知的命令开头 "when ‘MANA..." - 忽略了剩余的行。
SQL> sal + 400;
SP2-0042: 未知命令 "sal + 400" - 其余行忽略。
SQL> end 涨后 from emp;
SP2-0734: 未知的命令开头 "end 涨后 f..." - 忽略了剩余的行。
SQL>
SQL>
SQL> select sal 调前,case job when ‘PRESIDENT‘ then sal + 1000;when ‘MANAGER‘ then sal + 800;sal + 400;end 涨后 from emp;
select sal 调前,case job when ‘PRESIDENT‘ then sal + 1000;when ‘MANAGER‘ then sal + 800;sal + 400;end 涨后 from emp
*
第 1 行出现错误:
ORA-00911: 无效字符
SQL> select sal, case
2 when job = ‘PRESIDENT‘ then sal + 1000;
when job = ‘PRESIDENT‘ then sal + 1000
*
第 2 行出现错误:
ORA-00905: 缺失关键字
SQL> select sal 调前,case job when ‘PRESIDENT‘ then sal + 1000 when ‘MANAGER‘ then sal + 800 sal + 400 end 涨后 from emp;
select sal 调前,case job when ‘PRESIDENT‘ then sal + 1000 when ‘MANAGER‘ then sal + 800 sal + 400 end 涨后 from emp
*
第 1 行出现错误:
ORA-00905: 缺失关键字
SQL>
SQL> select sal 调前,case when job = ‘PRESIDENT‘ then sal + 1000 when job = ‘MANAGER‘ then sal + 800 sal + 400 end 涨后 from emp;
select sal 调前,case when job = ‘PRESIDENT‘ then sal + 1000 when job = ‘MANAGER‘ then sal + 800 sal + 400 end 涨后 from emp
*
第 1 行出现错误:
ORA-00905: 缺失关键字
SQL> select sal, case
2 when job = ‘MANAGER‘ then sal + 800
3 end from emp;
SAL CASEWHENJOB=‘MANAGER‘THENSAL+800END
---------- -----------------------------------
800
1600
1250
2975 3775
1250
2850 3650
2450 3250
3000
5000
1500
1100
SAL CASEWHENJOB=‘MANAGER‘THENSAL+800END
---------- -----------------------------------
950
3000
1300
已选择14行。
SQL> spool off;
原文：http://www.cnblogs.com/877599949yan/p/5285002.html