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

botang

课程第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. 
   
4. SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 19 19:34:46 2016
   
5. 
   
6. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
7. 
   
8. SQL> conn hr/oracle_4U
   
9. Connected.
   
10. SQL> select  'a' from dual;
    
11. 
    
12. '
    
13. -
    
14. a
    
15. 
    
16. SQL> select  * from dual;
    
17. 
    
18. D
    
19. -
    
20. X
    
21. 
    
22. SQL> conn / as sysdba
    
23. Connected.
    
24. SQL> select  open_mode from v$database;
    
25. 
    
26. OPEN_MODE
    
27. --------------------
    
28. READ WRITE
    
29. 
    
30. SQL> conn hr/oracle_4U
    
31. Connected.
    
32. SQL> select  open_mode from v$database;
    
33. select        open_mode from v$database
    
34.                        *
    
35. ERROR at line 1:
    
36. ORA-00942: table or view does not exist
    
37. 
    
38. 
    
39. SQL> select  * from dual;
    
40. 
    
41. D
    
42. -
    
43. X
    
44. 
    
45. SQL> select  substr('HelloWorld',1,5 ) from dual;
    
46. 
    
47. SUBST
    
48. -----
    
49. Hello
    
50. 
    
51. SQL> select  substr('HelloWorld',-1,1 ) from dual;
    
52. 
    
53. S
    
54. -
    
55. d
    
56. 
    
57. SQL> select  substr('HelloWorld',-1,5 ) from dual;
    
58. 
    
59. S
    
60. -
    
61. d
    
62. 
    
63. SQL> select  substr('HelloWorld',-1,0 ) from dual;
    
64. 
    
65. S
    
66. -
    
67. 
    
68. 
    
69. SQL> select  substr('HelloWorld',1,0 ) from dual;
    
70. 
    
71. S
    
72. -
    
73. 
    
74. 
    
75. SQL> select  substr('HelloWorld',2,1 ) from dual;
    
76. 
    
77. S
    
78. -
    
79. e
    
80. 
    
81. SQL> select  substr('HelloWorld',2 ) from dual;
    
82. 
    
83. SUBSTR('H
    
84. ---------
    
85. elloWorld
    
86. 
    
87. SQL> select  substr('HelloWorld',2 ) from dual;
    
88. 
    
89. SUBSTR('H
    
90. ---------
    
91. elloWorld
    
92. 
    
93. SQL> select  substr('HelloWorld',-4 ) from dual;
    
94. 
    
95. SUBS
    
96. ----
    
97. orld
    
98. 
    
99. SQL> create table t05103_zh( a varchar2(4));
    
100. 
     
101. Table created.
     
102. 
     
103. SQL> insert into t05103_zh values ('中国') ;
     
104. insert into t05103_zh values ('中国')
     
105.                               *
     
106. ERROR at line 1:
     
107. ORA-12899: value too large for column "HR"."T05103_ZH"."A" (actual: 6, maximum:
     
108. 4)
     
109. 
     
110. 
     
111. SQL> alter table t05103_zh  modify ( a  varchar2(6))  ;
     
112. 
     
113. Table altered.
     
114. 
     
115. SQL> insert into t05103_zh values ('中国') ;
     
116. 
     
117. 1 row created.
     
118. 
     
119. SQL> commit;
     
120. 
     
121. Commit complete.
     
122. 
     
123. SQL> select  length(a) from t05103_zh  ;
     
124. 
     
125. LENGTH(A)
     
126. ----------
     
127.          2
     
128. 
     
129. SQL> select  length2(a) from t05103_zh  ;
     
130. 
     
131. LENGTH2(A)
     
132. ----------
     
133.          2
     
134. 
     
135. SQL> select  lengthb(a) from t05103_zh  ;
     
136. 
     
137. LENGTHB(A)
     
138. ----------
     
139.          6
     
140. 
     
141. SQL> select  instr('HelloWorld','W' )  from dual;
     
142. 
     
143. INSTR('HELLOWORLD','W')
     
144. -----------------------
     
145.                       6
     
146. 
     
147. SQL> select  instr('HelloWorld','w' )  from dual;
     
148. 
     
149. INSTR('HELLOWORLD','W')
     
150. -----------------------
     
151.                       0
     
152. 
     
153. SQL> select  instr('HelloWorld','Wor' )  from dual;
     
154. 
     
155. INSTR('HELLOWORLD','WOR')
     
156. -------------------------
     
157.                         6
     
158. 
     
159. SQL> select  instr('HelloWorld','or' )  from dual;
     
160. 
     
161. INSTR('HELLOWORLD','OR')
     
162. ------------------------
     
163.                        7
     
164. 
     
165. SQL> select  trim ('H' from 'HelloWorld' )  from dual;
     
166. 
     
167. TRIM('H'F
     
168. ---------
     
169. elloWorld
     
170. 
     
171. SQL> select  trim ('d' from 'HelloWorld' )  from dual;
     
172. 
     
173. TRIM('D'F
     
174. ---------
     
175. HelloWorl
     
176. 
     
177. SQL> select  trim ('e' from 'HelloWorld' )  from dual;
     
178. 
     
179. TRIM('E'FR
     
180. ----------
     
181. HelloWorld
     
182. 
     
183. SQL> select  trim ('W' from 'Hello World' )  from dual;
     
184. 
     
185. TRIM('W'FRO
     
186. -----------
     
187. Hello World
     
188. 
     
189. SQL> select  trim ('H' from 'Hello World' )  from dual;
     
190. 
     
191. TRIM('H'FR
     
192. ----------
     
193. ello World
     
194. 
     
195. SQL> select  replace ('Hello World','e','' )  from dual;
     
196. 
     
197. REPLACE('H
     
198. ----------
     
199. Hllo World
     
200. 
     
201. SQL> select  replace ('Hello World','o','' )  from dual;
     
202. 
     
203. REPLACE('
     
204. ---------
     
205. Hell Wrld
     
206. 
     
207. SQL> select  ROUND(45.926, 2)  from dual;
     
208. 
     
209. ROUND(45.926,2)
     
210. ---------------
     
211.           45.93
     
212. 
     
213. SQL> select  ROUND(45.926)  from dual;
     
214. 
     
215. ROUND(45.926)
     
216. -------------
     
217.            46
     
218. 
     
219. SQL> select  ROUND(45.926,0)  from dual;
     
220. 
     
221. ROUND(45.926,0)
     
222. ---------------
     
223.              46
     
224. 
     
225. SQL> select  ROUND(45.926,-1)  from dual;
     
226. 
     
227. ROUND(45.926,-1)
     
228. ----------------
     
229.               50
     
230. 
     
231. SQL> select  ROUND(45.926,-2)  from dual;
     
232. 
     
233. ROUND(45.926,-2)
     
234. ----------------
     
235.                0
     
236. 
     
237. SQL> select  ROUND(45.926,-1)  from dual;
     
238. 
     
239. ROUND(45.926,-1)
     
240. ----------------
     
241.               50
     
242. 
     
243. SQL> select  trunc(45.926,-1)  from dual;
     
244. 
     
245. TRUNC(45.926,-1)
     
246. ----------------
     
247.               40
     
248. 
     
249. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual;
     
250. select        MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR',to_date('11-JAN-94','DD-MON-RR') )  from dual
     
251.         *
     
252. ERROR at line 1:
     
253. ORA-00909: invalid number of arguments
     
254. 
     
255. 
     
256. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;
     
257. 
     
258. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
     
259. --------------------------------------------------------------------------------
     
260.                                                                       19.6774194
     
261. 
     
262. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-YY'),to_date('11-JAN-94','DD-MON-RR') )  from dual;
     
263. 
     
264. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-YY'),TO_DATE('11-JAN-94','DD-MON-RR')
     
265. --------------------------------------------------------------------------------
     
266.                                                                       1219.67742
     
267. 
     
268. SQL> select  MONTHS_BETWEEN(   to_date('11-JAN-94','DD-MON-RR'),   to_date('01-SEP-95','DD-MON-RR') )  from dual;
     
269. 
     
270. MONTHS_BETWEEN(TO_DATE('11-JAN-94','DD-MON-RR'),TO_DATE('01-SEP-95','DD-MON-RR')
     
271. --------------------------------------------------------------------------------
     
272.                                                                       -19.677419
     
273. 
     
274. SQL> select  MONTHS_BETWEEN(to_date('01-SEP-95','DD-MON-RR'),to_date('11-JAN-94','DD-MON-RR') )  from dual;
     
275. 
     
276. MONTHS_BETWEEN(TO_DATE('01-SEP-95','DD-MON-RR'),TO_DATE('11-JAN-94','DD-MON-RR')
     
277. --------------------------------------------------------------------------------
     
278.                                                                       19.6774194
     
279. 
     
280. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/30  from dual;
     
281. 
     
282. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/30
     
283. ----------------------------------------------------------------------
     
284.                                                             19.9333333
     
285. 
     
286. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/31  from dual;
     
287. 
     
288. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/31
     
289. ----------------------------------------------------------------------
     
290.                                                             19.2903226
     
291. 
     
292. SQL> select  (to_date('01-SEP-95','DD-MON-RR')-to_date('11-JAN-94','DD-MON-RR') )/29  from dual;
     
293. 
     
294. (TO_DATE('01-SEP-95','DD-MON-RR')-TO_DATE('11-JAN-94','DD-MON-RR'))/29
     
295. ----------------------------------------------------------------------
     
296.                                                             20.6206897
     
297. 
     
298. SQL> select  add_months(sysdate , 1 )  from dual;
     
299. 
     
300. ADD_MONTHS(SYSDATE
     
301. ------------------
     
302. 19-OCT-16
     
303. 
     
304. SQL> select  add_months(sysdate , 3 )  from dual;
     
305. 
     
306. ADD_MONTHS(SYSDATE
     
307. ------------------
     
308. 19-DEC-16
     
309. 
     
310. SQL> select  next_day( sysdate,'Tuesday')  from dual;
     
311. 
     
312. NEXT_DAY(SYSDATE,'
     
313. ------------------
     
314. 20-SEP-16
     
315. 
     
316. SQL> select  next_day( sysdate,'Sunday')  from dual;
     
317. 
     
318. NEXT_DAY(SYSDATE,'
     
319. ------------------
     
320. 25-SEP-16
     
321. 
     
322. SQL> select  next_day( sysdate,0)  from dual;
     
323. select        next_day( sysdate,0)  from dual
     
324.                           *
     
325. ERROR at line 1:
     
326. ORA-01846: not a valid day of the week
     
327. 
     
328. 
     
329. SQL> select  next_day( sysdate,'0')  from dual;
     
330. select        next_day( sysdate,'0')        from dual
     
331.                           *
     
332. ERROR at line 1:
     
333. ORA-01846: not a valid day of the week
     
334. 
     
335. 
     
336. SQL> select  next_day( sysdate,'Sunday')  from dual;
     
337. 
     
338. NEXT_DAY(SYSDATE,'
     
339. ------------------
     
340. 25-SEP-16
     
341. 
     
342. SQL> select  next_day( sysdate,'Sunday')  from dual;
     
343. 
     
344. NEXT_DAY(SYSDATE,'
     
345. ------------------
     
346. 25-SEP-16
     
347. 
     
348. SQL>
     
349. SQL> select  next_day( sysdate,7)  from dual;
     
350. 
     
351. NEXT_DAY(SYSDATE,7
     
352. ------------------
     
353. 24-SEP-16
     
354. 
     
355. SQL> select  next_day( sysdate,0)  from dual;
     
356. select        next_day( sysdate,0)  from dual
     
357.                           *
     
358. ERROR at line 1:
     
359. ORA-01846: not a valid day of the week
     
360. 
     
361. 
     
362. SQL> select  next_day( sysdate,1)  from dual;
     
363. 
     
364. NEXT_DAY(SYSDATE,1
     
365. ------------------
     
366. 25-SEP-16
     
367. 
     
368. SQL> conn / as sysdba
     
369. Connected.
     
370. SQL> show parameter nls_terri
     
371. 
     
372. NAME                                     TYPE         VALUE
     
373. ------------------------------------ ----------- ------------------------------
     
374. nls_territory                             string         AMERICA
     
375. SQL> select  next_day( sysdate,1)  from dual;
     
376. 
     
377. NEXT_DAY(SYSDATE,1
     
378. ------------------
     
379. 25-SEP-16
     
380. 
     
381. SQL> alter system set nls_territory=china;
     
382. alter system set nls_territory=china
     
383.                  *
     
384. ERROR at line 1:
     
385. ORA-02096: specified initialization parameter is not modifiable with this
     
386. option
     
387. 
     
388. 
     
389. SQL> conn hr/oracle_4U
     
390. Connected.
     
391. SQL> alter session set nls_territory=china;
     
392. 
     
393. Session altered.
     
394. 
     
395. SQL> select next_day(sysdate,1) from dual;
     
396. 
     
397. NEXT_DAY(SYSDATE,1
     
398. ------------------
     
399. 25-SEP-16
     
400. 
     
401. SQL> alter session set nls_territory=australia;
     
402. 
     
403. Session altered.
     
404. 
     
405. SQL> select next_day(sysdate,1) from dual;
     
406. 
     
407. NEXT_DAY(SYSDATE,1
     
408. ------------------
     
409. 26/SEP/16
     
410. 
     
411. SQL> select next_day(sysdate,'Sunday') from dual;
     
412. 
     
413. NEXT_DAY(SYSDATE,'
     
414. ------------------
     
415. 25/SEP/16
     
416. 
     
417. SQL> select round(sysdate,'month') from dual;
     
418. 
     
419. ROUND(SYSDATE,'MON
     
420. ------------------
     
421. 01/OCT/16
     
422. 
     
423. SQL> select round(to_Date('2016-02-15','YYYY-MM-DD')     ,'month') from dual;
     
424. 
     
425. ROUND(TO_DATE('201
     
426. ------------------
     
427. 01/FEB/16
     
428. 
     
429. SQL> select round(to_Date('2016-02-16','YYYY-MM-DD')     ,'month') from dual;
     
430. 
     
431. ROUND(TO_DATE('201
     
432. ------------------
     
433. 01/MAR/16
     
434. 
     
435. SQL> select round(to_Date('2016-07-16','YYYY-MM-DD')     ,'month') from dual;
     
436. 
     
437. ROUND(TO_DATE('201
     
438. ------------------
     
439. 01/AUG/16
     
440. 
     
441. SQL> select round(to_Date('2016-07-15','YYYY-MM-DD')     ,'month') from dual;
     
442. 
     
443. ROUND(TO_DATE('201
     
444. ------------------
     
445. 01/JUL/16
     
446. 
     
447. SQL> select round(to_Date('2016-07-01','YYYY-MM-DD')     ,'year') from dual;
     
448. 
     
449. ROUND(TO_DATE('201
     
450. ------------------
     
451. 01/JAN/17
     
452. 
     
453. SQL> select round(to_Date('2016-06-30','YYYY-MM-DD')     ,'year') from dual;
     
454. 
     
455. ROUND(TO_DATE('201
     
456. ------------------
     
457. 01/JAN/16
     
458. 
     
459. SQL> select  last_day(to_Date('2016-02-13','YYYY-MM-DD')) from dual;
     
460. 
     
461. LAST_DAY(TO_DATE('
     
462. ------------------
     
463. 29/FEB/16
     
464. 
     
465. SQL> select  last_day(to_Date('2015-02-13','YYYY-MM-DD')) from dual;
     
466. 
     
467. LAST_DAY(TO_DATE('
     
468. ------------------
     
469. 28/FEB/15
     
470. 
     
471. 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,
---------------------------------
        

wuchunqiang

好像少了复杂简单case

botang

wuchunqiang 发表于 2016-9-21 22:07
好像少了复杂简单case

查课堂笔记吧，抱歉课后Teacher我也找不到了。