设为首页收藏本站

Botang唐波's Oracle Station

查看: 1498|回复: 2

课程第9次2016-09-19星期一

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2016-9-20 11:44:24 | 显示全部楼层 |阅读模式
课程第9次2016-09-19星期一:
单行函数和转换函数介绍完了。
【1Z0-051】:共12章0 1 2 3 4 5 6 7 8 9 10 11
1Z0-052】:共19章(0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
【1Z0-053】:共21章(0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

2016-09-19a.sql:
  1. [root@station90 ~]# su - oracle
  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 19:34:46 2016

  4. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  5. SQL> conn hr/oracle_4U
  6. Connected.
  7. SQL> select  'a' from dual;

  8. '
  9. -
  10. a

  11. SQL> select  * from dual;

  12. D
  13. -
  14. X

  15. SQL> conn / as sysdba
  16. Connected.
  17. SQL> select  open_mode from v$database;

  18. OPEN_MODE
  19. --------------------
  20. READ WRITE

  21. SQL> conn hr/oracle_4U
  22. Connected.
  23. SQL> select  open_mode from v$database;
  24. select        open_mode from v$database
  25.                        *
  26. ERROR at line 1:
  27. ORA-00942: table or view does not exist


  28. SQL> select  * from dual;

  29. D
  30. -
  31. X

  32. SQL> select  substr('HelloWorld',1,5 ) from dual;

  33. SUBST
  34. -----
  35. Hello

  36. SQL> select  substr('HelloWorld',-1,1 ) from dual;

  37. S
  38. -
  39. d

  40. SQL> select  substr('HelloWorld',-1,5 ) from dual;

  41. S
  42. -
  43. d

  44. SQL> select  substr('HelloWorld',-1,0 ) from dual;

  45. S
  46. -


  47. SQL> select  substr('HelloWorld',1,0 ) from dual;

  48. S
  49. -


  50. SQL> select  substr('HelloWorld',2,1 ) from dual;

  51. S
  52. -
  53. e

  54. SQL> select  substr('HelloWorld',2 ) from dual;

  55. SUBSTR('H
  56. ---------
  57. elloWorld

  58. SQL> select  substr('HelloWorld',2 ) from dual;

  59. SUBSTR('H
  60. ---------
  61. elloWorld

  62. SQL> select  substr('HelloWorld',-4 ) from dual;

  63. SUBS
  64. ----
  65. orld

  66. SQL> create table t05103_zh( a varchar2(4));

  67. Table created.

  68. SQL> insert into t05103_zh values ('中国') ;
  69. insert into t05103_zh values ('中国')
  70.                               *
  71. ERROR at line 1:
  72. ORA-12899: value too large for column "HR"."T05103_ZH"."A" (actual: 6, maximum:
  73. 4)


  74. SQL> alter table t05103_zh  modify ( a  varchar2(6))  ;

  75. Table altered.

  76. SQL> insert into t05103_zh values ('中国') ;

  77. 1 row created.

  78. SQL> commit;

  79. Commit complete.

  80. SQL> select  length(a) from t05103_zh  ;

  81. LENGTH(A)
  82. ----------
  83.          2

  84. SQL> select  length2(a) from t05103_zh  ;

  85. LENGTH2(A)
  86. ----------
  87.          2

  88. SQL> select  lengthb(a) from t05103_zh  ;

  89. LENGTHB(A)
  90. ----------
  91.          6

  92. SQL> select  instr('HelloWorld','W' )  from dual;

  93. INSTR('HELLOWORLD','W')
  94. -----------------------
  95.                       6

  96. SQL> select  instr('HelloWorld','w' )  from dual;

  97. INSTR('HELLOWORLD','W')
  98. -----------------------
  99.                       0

  100. SQL> select  instr('HelloWorld','Wor' )  from dual;

  101. INSTR('HELLOWORLD','WOR')
  102. -------------------------
  103.                         6

  104. SQL> select  instr('HelloWorld','or' )  from dual;

  105. INSTR('HELLOWORLD','OR')
  106. ------------------------
  107.                        7

  108. SQL> select  trim ('H' from 'HelloWorld' )  from dual;

  109. TRIM('H'F
  110. ---------
  111. elloWorld

  112. SQL> select  trim ('d' from 'HelloWorld' )  from dual;

  113. TRIM('D'F
  114. ---------
  115. HelloWorl

  116. SQL> select  trim ('e' from 'HelloWorld' )  from dual;

  117. TRIM('E'FR
  118. ----------
  119. HelloWorld

  120. SQL> select  trim ('W' from 'Hello World' )  from dual;

  121. TRIM('W'FRO
  122. -----------
  123. Hello World

  124. SQL> select  trim ('H' from 'Hello World' )  from dual;

  125. TRIM('H'FR
  126. ----------
  127. ello World

  128. SQL> select  replace ('Hello World','e','' )  from dual;

  129. REPLACE('H
  130. ----------
  131. Hllo World

  132. SQL> select  replace ('Hello World','o','' )  from dual;

  133. REPLACE('
  134. ---------
  135. Hell Wrld

  136. SQL> select  ROUND(45.926, 2)  from dual;

  137. ROUND(45.926,2)
  138. ---------------
  139.           45.93

  140. SQL> select  ROUND(45.926)  from dual;

  141. ROUND(45.926)
  142. -------------
  143.            46

  144. SQL> select  ROUND(45.926,0)  from dual;

  145. ROUND(45.926,0)
  146. ---------------
  147.              46

  148. SQL> select  ROUND(45.926,-1)  from dual;

  149. ROUND(45.926,-1)
  150. ----------------
  151.               50

  152. SQL> select  ROUND(45.926,-2)  from dual;

  153. ROUND(45.926,-2)
  154. ----------------
  155.                0

  156. SQL> select  ROUND(45.926,-1)  from dual;

  157. ROUND(45.926,-1)
  158. ----------------
  159.               50

  160. SQL> select  trunc(45.926,-1)  from dual;

  161. TRUNC(45.926,-1)
  162. ----------------
  163.               40

  164. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual;
  165. select        MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual
  166.         *
  167. ERROR at line 1:
  168. ORA-00909: invalid number of arguments


  169. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

  170. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
  171. --------------------------------------------------------------------------------
  172.                                                                       19.6774194

  173. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-YY'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

  174. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-YY'),TO_DATE('11-JAN-94','DD-MON-RR')
  175. --------------------------------------------------------------------------------
  176.                                                                       1219.67742

  177. SQL> select  MONTHS_BETWEEN(   to_date('11-JAN-94','DD-MON-RR'),   to_date('01-SEP-95','DD-MON-RR') )  from dual;

  178. MONTHS_BETWEEN(TO_DATE('11-JAN-94','DD-MON-RR'),TO_DATE('01-SEP-95','DD-MON-RR')
  179. --------------------------------------------------------------------------------
  180.                                                                       -19.677419

  181. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

  182. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
  183. --------------------------------------------------------------------------------
  184.                                                                       19.6774194

  185. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/30  from dual;

  186. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/30
  187. ----------------------------------------------------------------------
  188.                                                             19.9333333

  189. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/31  from dual;

  190. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/31
  191. ----------------------------------------------------------------------
  192.                                                             19.2903226

  193. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/29  from dual;

  194. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/29
  195. ----------------------------------------------------------------------
  196.                                                             20.6206897

  197. SQL> select  add_months(sysdate , 1 )  from dual;

  198. ADD_MONTHS(SYSDATE
  199. ------------------
  200. 19-OCT-16

  201. SQL> select  add_months(sysdate , 3 )  from dual;

  202. ADD_MONTHS(SYSDATE
  203. ------------------
  204. 19-DEC-16

  205. SQL> select  next_day( sysdate,'Tuesday')  from dual;

  206. NEXT_DAY(SYSDATE,'
  207. ------------------
  208. 20-SEP-16

  209. SQL> select  next_day( sysdate,'Sunday')  from dual;

  210. NEXT_DAY(SYSDATE,'
  211. ------------------
  212. 25-SEP-16

  213. SQL> select  next_day( sysdate,0)  from dual;
  214. select        next_day( sysdate,0)  from dual
  215.                           *
  216. ERROR at line 1:
  217. ORA-01846: not a valid day of the week


  218. SQL> select  next_day( sysdate,'0')  from dual;
  219. select        next_day( sysdate,'0')        from dual
  220.                           *
  221. ERROR at line 1:
  222. ORA-01846: not a valid day of the week


  223. SQL> select  next_day( sysdate,'Sunday')  from dual;

  224. NEXT_DAY(SYSDATE,'
  225. ------------------
  226. 25-SEP-16

  227. SQL> select  next_day( sysdate,'Sunday')  from dual;

  228. NEXT_DAY(SYSDATE,'
  229. ------------------
  230. 25-SEP-16

  231. SQL>
  232. SQL> select  next_day( sysdate,7)  from dual;

  233. NEXT_DAY(SYSDATE,7
  234. ------------------
  235. 24-SEP-16

  236. SQL> select  next_day( sysdate,0)  from dual;
  237. select        next_day( sysdate,0)  from dual
  238.                           *
  239. ERROR at line 1:
  240. ORA-01846: not a valid day of the week


  241. SQL> select  next_day( sysdate,1)  from dual;

  242. NEXT_DAY(SYSDATE,1
  243. ------------------
  244. 25-SEP-16

  245. SQL> conn / as sysdba
  246. Connected.
  247. SQL> show parameter nls_terri

  248. NAME                                     TYPE         VALUE
  249. ------------------------------------ ----------- ------------------------------
  250. nls_territory                             string         AMERICA
  251. SQL> select  next_day( sysdate,1)  from dual;

  252. NEXT_DAY(SYSDATE,1
  253. ------------------
  254. 25-SEP-16

  255. SQL> alter system set nls_territory=china;
  256. alter system set nls_territory=china
  257.                  *
  258. ERROR at line 1:
  259. ORA-02096: specified initialization parameter is not modifiable with this
  260. option


  261. SQL> conn hr/oracle_4U
  262. Connected.
  263. SQL> alter session set nls_territory=china;

  264. Session altered.

  265. SQL> select next_day(sysdate,1) from dual;

  266. NEXT_DAY(SYSDATE,1
  267. ------------------
  268. 25-SEP-16

  269. SQL> alter session set nls_territory=australia;

  270. Session altered.

  271. SQL> select next_day(sysdate,1) from dual;

  272. NEXT_DAY(SYSDATE,1
  273. ------------------
  274. 26/SEP/16

  275. SQL> select next_day(sysdate,'Sunday') from dual;

  276. NEXT_DAY(SYSDATE,'
  277. ------------------
  278. 25/SEP/16

  279. SQL> select round(sysdate,'month') from dual;

  280. ROUND(SYSDATE,'MON
  281. ------------------
  282. 01/OCT/16

  283. SQL> select round(to_Date('2016-02-15','YYYY-MM-DD')     ,'month') from dual;

  284. ROUND(TO_DATE('201
  285. ------------------
  286. 01/FEB/16

  287. SQL> select round(to_Date('2016-02-16','YYYY-MM-DD')     ,'month') from dual;

  288. ROUND(TO_DATE('201
  289. ------------------
  290. 01/MAR/16

  291. SQL> select round(to_Date('2016-07-16','YYYY-MM-DD')     ,'month') from dual;

  292. ROUND(TO_DATE('201
  293. ------------------
  294. 01/AUG/16

  295. SQL> select round(to_Date('2016-07-15','YYYY-MM-DD')     ,'month') from dual;

  296. ROUND(TO_DATE('201
  297. ------------------
  298. 01/JUL/16

  299. SQL> select round(to_Date('2016-07-01','YYYY-MM-DD')     ,'year') from dual;

  300. ROUND(TO_DATE('201
  301. ------------------
  302. 01/JAN/17

  303. SQL> select round(to_Date('2016-06-30','YYYY-MM-DD')     ,'year') from dual;

  304. ROUND(TO_DATE('201
  305. ------------------
  306. 01/JAN/16

  307. SQL> select  last_day(to_Date('2016-02-13','YYYY-MM-DD')) from dual;

  308. LAST_DAY(TO_DATE('
  309. ------------------
  310. 29/FEB/16

  311. SQL> select  last_day(to_Date('2015-02-13','YYYY-MM-DD')) from dual;

  312. LAST_DAY(TO_DATE('
  313. ------------------
  314. 28/FEB/15

  315. SQL>
复制代码

2016-09-19b.sql:
[code][root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 19:34:46 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn hr/oracle_4U
Connected.
SQL> select  'a' from dual;

'
-
a

SQL> select  * from dual;

D
-
X

SQL> conn / as sysdba
Connected.
SQL> select  open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> conn hr/oracle_4U
Connected.
SQL> select  open_mode from v$database;
select        open_mode from v$database
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select  * from dual;

D
-
X

SQL> select  substr('HelloWorld',1,5 ) from dual;

SUBST
-----
Hello

SQL> select  substr('HelloWorld',-1,1 ) from dual;

S
-
d

SQL> select  substr('HelloWorld',-1,5 ) from dual;

S
-
d

SQL> select  substr('HelloWorld',-1,0 ) from dual;

S
-


SQL> select  substr('HelloWorld',1,0 ) from dual;

S
-


SQL> select  substr('HelloWorld',2,1 ) from dual;

S
-
e

SQL> select  substr('HelloWorld',2 ) from dual;

SUBSTR('H
---------
elloWorld

SQL> select  substr('HelloWorld',2 ) from dual;

SUBSTR('H
---------
elloWorld

SQL> select  substr('HelloWorld',-4 ) from dual;

SUBS
----
orld

SQL> create table t05103_zh( a varchar2(4));

Table created.

SQL> insert into t05103_zh values ('中国') ;
insert into t05103_zh values ('中国')
                              *
ERROR at line 1:
ORA-12899: value too large for column "HR"."T05103_ZH"."A" (actual: 6, maximum:
4)


SQL> alter table t05103_zh  modify ( a  varchar2(6))  ;

Table altered.

SQL> insert into t05103_zh values ('中国') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select  length(a) from t05103_zh  ;

LENGTH(A)
----------
         2

SQL> select  length2(a) from t05103_zh  ;

LENGTH2(A)
----------
         2

SQL> select  lengthb(a) from t05103_zh  ;

LENGTHB(A)
----------
         6

SQL> select  instr('HelloWorld','W' )  from dual;

INSTR('HELLOWORLD','W')
-----------------------
                      6

SQL> select  instr('HelloWorld','w' )  from dual;

INSTR('HELLOWORLD','W')
-----------------------
                      0

SQL> select  instr('HelloWorld','Wor' )  from dual;

INSTR('HELLOWORLD','WOR')
-------------------------
                        6

SQL> select  instr('HelloWorld','or' )  from dual;

INSTR('HELLOWORLD','OR')
------------------------
                       7

SQL> select  trim ('H' from 'HelloWorld' )  from dual;

TRIM('H'F
---------
elloWorld

SQL> select  trim ('d' from 'HelloWorld' )  from dual;

TRIM('D'F
---------
HelloWorl

SQL> select  trim ('e' from 'HelloWorld' )  from dual;

TRIM('E'FR
----------
HelloWorld

SQL> select  trim ('W' from 'Hello World' )  from dual;

TRIM('W'FRO
-----------
Hello World

SQL> select  trim ('H' from 'Hello World' )  from dual;

TRIM('H'FR
----------
ello World

SQL> select  replace ('Hello World','e','' )  from dual;

REPLACE('H
----------
Hllo World

SQL> select  replace ('Hello World','o','' )  from dual;

REPLACE('
---------
Hell Wrld

SQL> select  ROUND(45.926, 2)  from dual;

ROUND(45.926,2)
---------------
          45.93

SQL> select  ROUND(45.926)  from dual;

ROUND(45.926)
-------------
           46

SQL> select  ROUND(45.926,0)  from dual;

ROUND(45.926,0)
---------------
             46

SQL> select  ROUND(45.926,-1)  from dual;

ROUND(45.926,-1)
----------------
              50

SQL> select  ROUND(45.926,-2)  from dual;

ROUND(45.926,-2)
----------------
               0

SQL> select  ROUND(45.926,-1)  from dual;

ROUND(45.926,-1)
----------------
              50

SQL> select  trunc(45.926,-1)  from dual;

TRUNC(45.926,-1)
----------------
              40

SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual;
select        MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual
        *
ERROR at line 1:
ORA-00909: invalid number of arguments


SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
--------------------------------------------------------------------------------
                                                                      19.6774194

SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-YY'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-YY'),TO_DATE('11-JAN-94','DD-MON-RR')
--------------------------------------------------------------------------------
                                                                      1219.67742

SQL> select  MONTHS_BETWEEN(   to_date('11-JAN-94','DD-MON-RR'),   to_date('01-SEP-95','DD-MON-RR') )  from dual;

MONTHS_BETWEEN(TO_DATE('11-JAN-94','DD-MON-RR'),TO_DATE('01-SEP-95','DD-MON-RR')
--------------------------------------------------------------------------------
                                                                      -19.677419

SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;

MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
--------------------------------------------------------------------------------
                                                                      19.6774194

SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/30  from dual;

(TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/30
----------------------------------------------------------------------
                                                            19.9333333

SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/31  from dual;

(TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/31
----------------------------------------------------------------------
                                                            19.2903226

SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/29  from dual;

(TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/29
----------------------------------------------------------------------
                                                            20.6206897

SQL> select  add_months(sysdate , 1 )  from dual;

ADD_MONTHS(SYSDATE
------------------
19-OCT-16

SQL> select  add_months(sysdate , 3 )  from dual;

ADD_MONTHS(SYSDATE
------------------
19-DEC-16

SQL> select  next_day( sysdate,'Tuesday')  from dual;

NEXT_DAY(SYSDATE,'
------------------
20-SEP-16

SQL> select  next_day( sysdate,'Sunday')  from dual;

NEXT_DAY(SYSDATE,'
------------------
25-SEP-16

SQL> select  next_day( sysdate,0)  from dual;
select        next_day( sysdate,0)  from dual
                          *
ERROR at line 1:
ORA-01846: not a valid day of the week


SQL> select  next_day( sysdate,'0')  from dual;
select        next_day( sysdate,'0')        from dual
                          *
ERROR at line 1:
ORA-01846: not a valid day of the week


SQL> select  next_day( sysdate,'Sunday')  from dual;

NEXT_DAY(SYSDATE,'
------------------
25-SEP-16

SQL> select  next_day( sysdate,'Sunday')  from dual;

NEXT_DAY(SYSDATE,'
------------------
25-SEP-16

SQL>
SQL> select  next_day( sysdate,7)  from dual;

NEXT_DAY(SYSDATE,7
------------------
24-SEP-16

SQL> select  next_day( sysdate,0)  from dual;
select        next_day( sysdate,0)  from dual
                          *
ERROR at line 1:
ORA-01846: not a valid day of the week


SQL> select  next_day( sysdate,1)  from dual;

NEXT_DAY(SYSDATE,1
------------------
25-SEP-16

SQL> conn / as sysdba
Connected.
SQL> show parameter nls_terri

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
nls_territory                             string         AMERICA
SQL> select  next_day( sysdate,1)  from dual;

NEXT_DAY(SYSDATE,1
------------------
25-SEP-16

SQL> alter system set nls_territory=china;
alter system set nls_territory=china
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL> conn hr/oracle_4U
Connected.
SQL> alter session set nls_territory=china;

Session altered.

SQL> select next_day(sysdate,1) from dual;

NEXT_DAY(SYSDATE,1
------------------
25-SEP-16

SQL> alter session set nls_territory=australia;

Session altered.

SQL> select next_day(sysdate,1) from dual;

NEXT_DAY(SYSDATE,1
------------------
26/SEP/16

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(SYSDATE,'
------------------
25/SEP/16

SQL> select round(sysdate,'month') from dual;

ROUND(SYSDATE,'MON
------------------
01/OCT/16

SQL> select round(to_Date('2016-02-15','YYYY-MM-DD')     ,'month') from dual;

ROUND(TO_DATE('201
------------------
01/FEB/16

SQL> select round(to_Date('2016-02-16','YYYY-MM-DD')     ,'month') from dual;

ROUND(TO_DATE('201
------------------
01/MAR/16

SQL> select round(to_Date('2016-07-16','YYYY-MM-DD')     ,'month') from dual;

ROUND(TO_DATE('201
------------------
01/AUG/16

SQL> select round(to_Date('2016-07-15','YYYY-MM-DD')     ,'month') from dual;

ROUND(TO_DATE('201
------------------
01/JUL/16

SQL> select round(to_Date('2016-07-01','YYYY-MM-DD')     ,'year') from dual;

ROUND(TO_DATE('201
------------------
01/JAN/17

SQL> select round(to_Date('2016-06-30','YYYY-MM-DD')     ,'year') from dual;

ROUND(TO_DATE('201
------------------
01/JAN/16

SQL> select  last_day(to_Date('2016-02-13','YYYY-MM-DD')) from dual;

LAST_DAY(TO_DATE('
------------------
29/FEB/16

SQL> select  last_day(to_Date('2015-02-13','YYYY-MM-DD')) from dual;

LAST_DAY(TO_DATE('
------------------
28/FEB/15

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 20:59:26 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter nls_ter

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
nls_territory                             string         AMERICA
SQL> show parameter nls_langu

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
nls_language                             string         AMERICAN
SQL> select  to_char(sysdate , 'MONTH' )  from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
SEPTEMBER

SQL> select  to_char(sysdate , 'Month' )  from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
September

SQL> conn hr/oracle_4U
Connected.
SQL> alter session set nls_language=korean;

Session altered.

SQL> select  to_char(sysdate , 'MONTH' )  from dual;

TO_CHAR(
--------
9월

SQL> alter session set nls_language=italian;

Session altered.

SQL> select  to_char(sysdate , 'MONTH' )  from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
SETTEMBRE

SQL> alter session set nls_language=fench;
ERROR:
ORA-12705: Impossibile accedere ai file di dati NLS oppure l'ambiente
specificato non è valido


SQL> alter session set nls_language=french;

Session altered.

SQL> select  to_char(sysdate , 'MONTH' )  from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
SEPTEMBRE

SQL> alter session set nls_language=tailanian;
ERROR:
ORA-12705: Impossible d'accéder aux fichiers de données NLS ou l'environnement
indiqué n'est pas valide


SQL> select  to_char(sysdate , 'DY' )  from dual;

TO_CHAR(SYSDATE,
----------------
LUN.

SQL> alter session set nls_language=american;

Session altered.

SQL> select  to_char(sysdate , 'DY' )  from dual;

TO_CHAR(SYSD
------------
MON

SQL> alter session set nls_language='simplified chinese';

Session altered.

SQL> select  to_char(sysdate , 'DY' )  from dual;

TO_CHAR(SYSD
------------
星期一

SQL> alter session set nls_language='traditional chinese';

Session altered.

SQL> select  to_char(sysdate , 'DY' )  from dual;

TO_CHAR(SYSD
------------
星期一

SQL> select  to_char(sysdate , 'YEAR' )  from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWENTY SIXTEEN

SQL> select  to_char(sysdate , 'year' )  from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
twenty sixteen

SQL> select  to_char(sysdate , 'dd' )  from dual;

TO
--
19

SQL> select  to_char(sysdate , 'd' )  from dual;

T
-
2

SQL> alter session set nls_territary=australia;
alter session set nls_territary=australia
*
ERROR at line 1:
ORA-00922: 遺漏選項, 或此選項無效


SQL> alter session set nls_territey=australia;
alter session set nls_territey=australia
*
ERROR at line 1:
ORA-00922: 遺漏選項, 或此選項無效


SQL> alter session set nls_teritey=australia;
alter session set nls_teritey=australia
*
ERROR at line 1:
ORA-00922: 遺漏選項, 或此選項無效


SQL> alter session set nls_territory=australia;

Session altered.

SQL> select  to_char(sysdate , 'd' )  from dual;

T
-
1

SQL> select  to_char(sysdate , 'ddd' )  from dual;

TO_
---
263

SQL> alter session set nls_territory=china;

Session altered.

SQL> select  to_char(sysdate , 'ddd' )  from dual;

TO_
---
263

SQL> select  to_char(     sysdate   , 'YYYY-MM-DD' )  from dual;

TO_CHAR(SY
----------
2016-09-19

SQL> select  to_char(     sysdate   , 'fmYYYY-MM-DD' )  from dual;

TO_CHAR(SY
----------
2016-9-19

SQL> select  to_char(     sysdate   , 'fmYYYY-MM-DD  "is my birthday"   ' )  from dual;

TO_CHAR(SYSDATE,'FMYYYY-MM-DD
-----------------------------
2016-9-19  is my birthday

SQL> select  to_char(     sysdate   , 'fmYYYY-MM-DDspth  "is my birthday"   ' )  from dual;

TO_CHAR(SYSDATE,'FMYYYY-MM-DDSPTH"ISMYBIR
-----------------------------------------
2016-9-NINETEENTH  is my birthday

SQL> select  to_char(     sysdate   , 'fmYYYY-MM-ddspth  "is my birthday"   ' )  from dual;

TO_CHAR(SYSDATE,'FMYYYY-MM-DDSPTH"ISMYBIR
-----------------------------------------
2016-9-nineteenth  is my birthday

SQL> select  salary from employees where employee_id=100;

    SALARY
----------
     24000

SQL> select  to_char(salary,'L999,999,999.99' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L999,999,
-------------------------
             ¥24,000.00

SQL> select  to_char(salary,'L999.99' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L
-----------------
#################

SQL> select  to_char(salary,'L999,999,999.99' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L999,999,
-------------------------
             ¥24,000.00

SQL> select  to_char(salary,'L000,000,000,000,000.99' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L000,000,000,000,
---------------------------------
        ¥000,000,000,024,000.00

SQL> select  to_char(salary,'L000,000,000,000,000.00' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L000,000,000,000,
---------------------------------
        ¥000,000,000,024,000.00

SQL> select  to_char(salary,'L000,000,000,000,000.009' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L000,000,000,000,0
----------------------------------
        ¥000,000,000,024,000.000

SQL> select  to_char(salary,'L000,000,000,000,000.00' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L000,000,000,000,
---------------------------------
        ¥000,000,000,024,000.00

SQL> alter session set nls_territory=france;

Session altered.

SQL> select  to_char(salary,'L000,000,000,000,000.00' ) from employees where employee_id=100;

TO_CHAR(SALARY,'L000,000,000,000,
---------------------------------
        
回复

使用道具 举报

17

主题

32

帖子

140

积分

版主

Rank: 7Rank: 7Rank: 7

积分
140
发表于 2016-9-21 22:07:31 | 显示全部楼层
好像少了复杂简单case
回复 支持 反对

使用道具 举报

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
 楼主| 发表于 2016-9-23 16:14:40 | 显示全部楼层
wuchunqiang 发表于 2016-9-21 22:07
好像少了复杂简单case

查课堂笔记吧,抱歉课后Teacher我也找不到了。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-20 15:45 , Processed in 0.195837 second(s), 23 queries .

快速回复 返回顶部 返回列表