ora.orcl.db    ora....se.type ONLINE    ONLINE    station90   
[root@station90 ~]# su - oracle
[oracle@station90 ~]$ . oraenv
ORACLE_SID = [orcl] ? 
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 20 19:20:34 2016

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup 
ORACLE instance started.

Total System Global Area 6680915968 bytes
Fixed Size		    2213936 bytes
Variable Size		 3623880656 bytes
Database Buffers	 3019898880 bytes
Redo Buffers		   34922496 bytes
Database mounted.
Database opened.
SQL> alter system register;

System altered.

SQL> !date
2016年 04月 20日 星期三 19:28:50 CST

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 ~]$ cal 
      四月 2016     
日 一 二 三 四 五 六
                1  2
 3  4  5  6  7  8  9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

[oracle@station90 ~]$ cal  2016
                               2016                               

        一月                   二月                   三月        
日 一 二 三 四 五 六   日 一 二 三 四 五 六   日 一 二 三 四 五 六
                1  2       1  2  3  4  5  6          1  2  3  4  5
 3  4  5  6  7  8  9    7  8  9 10 11 12 13    6  7  8  9 10 11 12
10 11 12 13 14 15 16   14 15 16 17 18 19 20   13 14 15 16 17 18 19
17 18 19 20 21 22 23   21 22 23 24 25 26 27   20 21 22 23 24 25 26
24 25 26 27 28 29 30   28 29                  27 28 29 30 31
31
        四月                   五月                   六月        
日 一 二 三 四 五 六   日 一 二 三 四 五 六   日 一 二 三 四 五 六
                1  2    1  2  3  4  5  6  7             1  2  3  4
 3  4  5  6  7  8  9    8  9 10 11 12 13 14    5  6  7  8  9 10 11
10 11 12 13 14 15 16   15 16 17 18 19 20 21   12 13 14 15 16 17 18
17 18 19 20 21 22 23   22 23 24 25 26 27 28   19 20 21 22 23 24 25
24 25 26 27 28 29 30   29 30 31               26 27 28 29 30

        七月                   八月                   九月        
日 一 二 三 四 五 六   日 一 二 三 四 五 六   日 一 二 三 四 五 六
                1  2       1  2  3  4  5  6                1  2  3
 3  4  5  6  7  8  9    7  8  9 10 11 12 13    4  5  6  7  8  9 10
10 11 12 13 14 15 16   14 15 16 17 18 19 20   11 12 13 14 15 16 17
17 18 19 20 21 22 23   21 22 23 24 25 26 27   18 19 20 21 22 23 24
24 25 26 27 28 29 30   28 29 30 31            25 26 27 28 29 30
31
        十月                  十一月                 十二月       
日 一 二 三 四 五 六   日 一 二 三 四 五 六   日 一 二 三 四 五 六
                   1          1  2  3  4  5                1  2  3
 2  3  4  5  6  7  8    6  7  8  9 10 11 12    4  5  6  7  8  9 10
 9 10 11 12 13 14 15   13 14 15 16 17 18 19   11 12 13 14 15 16 17
16 17 18 19 20 21 22   20 21 22 23 24 25 26   18 19 20 21 22 23 24
23 24 25 26 27 28 29   27 28 29 30            25 26 27 28 29 30 31
30 31

[oracle@station90 ~]$ cal
      四月 2016     
日 一 二 三 四 五 六
                1  2
 3  4  5  6  7  8  9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 20 19:30:45 2016

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

SQL> conn hr/oracle_4U
Connected.
SQL>  select  next_day(sysdate,4) from dual;

NEXT_DAY(SYSDATE,4
------------------
27-APR-16

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

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

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_language			     string	 AMERICAN
SQL> select to_char(salary,'$999,999,999.00')  from employees ;
select to_char(salary,'$999,999,999.00')  from employees
                                               *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select to_char(salary,'$999,999,999.00')  from hr.employees ;

TO_CHAR(SALARY,'
----------------
       $2,600.00
       $2,600.00
       $4,400.00
      $13,000.00
       $6,000.00
       $6,500.00
      $10,000.00
      $12,008.00
       $8,300.00
       $8,000.00
      $17,000.00

TO_CHAR(SALARY,'
----------------
      $17,000.00
       $9,000.00
       $6,000.00
       $4,800.00
       $4,800.00
       $4,200.00
      $12,008.00
       $9,000.00
       $8,200.00
       $7,700.00
       $7,800.00

TO_CHAR(SALARY,'
----------------
       $6,900.00
      $11,000.00
       $3,100.00
       $2,900.00
       $2,800.00
       $2,600.00
       $2,500.00
       $8,000.00
       $8,200.00
       $7,900.00
       $6,500.00

TO_CHAR(SALARY,'
----------------
       $5,800.00
       $3,200.00
       $2,700.00
       $2,400.00
       $2,200.00
       $3,300.00
       $2,800.00
       $2,500.00
       $2,100.00
       $3,300.00
       $2,900.00

TO_CHAR(SALARY,'
----------------
       $2,400.00
       $2,200.00
       $3,600.00
       $3,200.00
       $2,700.00
       $2,500.00
       $3,500.00
       $3,100.00
       $2,600.00
       $2,500.00
      $14,000.00

TO_CHAR(SALARY,'
----------------
      $13,500.00
      $12,000.00
      $11,000.00
      $10,500.00
      $10,000.00
       $9,500.00
       $9,000.00
       $8,000.00
       $7,500.00
       $7,000.00
      $10,000.00

TO_CHAR(SALARY,'
----------------
       $9,500.00
       $9,000.00
       $8,000.00
       $7,500.00
       $7,000.00
      $10,500.00
       $9,500.00
       $7,200.00
       $6,800.00
       $6,400.00
       $6,200.00

TO_CHAR(SALARY,'
----------------
      $11,500.00
      $10,000.00
       $9,600.00
       $7,400.00
       $7,300.00
       $6,100.00
      $11,000.00
       $8,800.00
       $8,600.00
       $8,400.00
       $7,000.00

TO_CHAR(SALARY,'
----------------
       $6,200.00
       $3,200.00
       $3,100.00
       $2,500.00
       $2,800.00
       $4,200.00
       $4,100.00
       $3,400.00
       $3,000.00
       $3,800.00
       $3,600.00

TO_CHAR(SALARY,'
----------------
       $2,900.00
       $2,500.00
       $4,000.00
       $3,900.00
       $3,200.00
       $2,800.00
       $3,100.00
       $3,000.00

107 rows selected.

SQL> alter session set nls_territory='china';

Session altered.

SQL> select to_char(salary,'$999,999,999.00')  from hr.employees ;

TO_CHAR(SALARY,'
----------------
       $2,600.00
       $2,600.00
       $4,400.00
      $13,000.00
       $6,000.00
       $6,500.00
      $10,000.00
      $12,008.00
       $8,300.00
       $8,000.00
      $17,000.00

TO_CHAR(SALARY,'
----------------
      $17,000.00
       $9,000.00
       $6,000.00
       $4,800.00
       $4,800.00
       $4,200.00
      $12,008.00
       $9,000.00
       $8,200.00
       $7,700.00
       $7,800.00

TO_CHAR(SALARY,'
----------------
       $6,900.00
      $11,000.00
       $3,100.00
       $2,900.00
       $2,800.00
       $2,600.00
       $2,500.00
       $8,000.00
       $8,200.00
       $7,900.00
       $6,500.00

TO_CHAR(SALARY,'
----------------
       $5,800.00
       $3,200.00
       $2,700.00
       $2,400.00
       $2,200.00
       $3,300.00
       $2,800.00
       $2,500.00
       $2,100.00
       $3,300.00
       $2,900.00

TO_CHAR(SALARY,'
----------------
       $2,400.00
       $2,200.00
       $3,600.00
       $3,200.00
       $2,700.00
       $2,500.00
       $3,500.00
       $3,100.00
       $2,600.00
       $2,500.00
      $14,000.00

TO_CHAR(SALARY,'
----------------
      $13,500.00
      $12,000.00
      $11,000.00
      $10,500.00
      $10,000.00
       $9,500.00
       $9,000.00
       $8,000.00
       $7,500.00
       $7,000.00
      $10,000.00

TO_CHAR(SALARY,'
----------------
       $9,500.00
       $9,000.00
       $8,000.00
       $7,500.00
       $7,000.00
      $10,500.00
       $9,500.00
       $7,200.00
       $6,800.00
       $6,400.00
       $6,200.00

TO_CHAR(SALARY,'
----------------
      $11,500.00
      $10,000.00
       $9,600.00
       $7,400.00
       $7,300.00
       $6,100.00
      $11,000.00
       $8,800.00
       $8,600.00
       $8,400.00
       $7,000.00

TO_CHAR(SALARY,'
----------------
       $6,200.00
       $3,200.00
       $3,100.00
       $2,500.00
       $2,800.00
       $4,200.00
       $4,100.00
       $3,400.00
       $3,000.00
       $3,800.00
       $3,600.00

TO_CHAR(SALARY,'
----------------
       $2,900.00
       $2,500.00
       $4,000.00
       $3,900.00
       $3,200.00
       $2,800.00
       $3,100.00
       $3,000.00

107 rows selected.

SQL> select to_char(salary,'L999,999,999.00')  from hr.employees ;

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥2,600.00
              ￥2,600.00
              ￥4,400.00
             ￥13,000.00
              ￥6,000.00
              ￥6,500.00
             ￥10,000.00
             ￥12,008.00
              ￥8,300.00
              ￥8,000.00
             ￥17,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             ￥17,000.00
              ￥9,000.00
              ￥6,000.00
              ￥4,800.00
              ￥4,800.00
              ￥4,200.00
             ￥12,008.00
              ￥9,000.00
              ￥8,200.00
              ￥7,700.00
              ￥7,800.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥6,900.00
             ￥11,000.00
              ￥3,100.00
              ￥2,900.00
              ￥2,800.00
              ￥2,600.00
              ￥2,500.00
              ￥8,000.00
              ￥8,200.00
              ￥7,900.00
              ￥6,500.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥5,800.00
              ￥3,200.00
              ￥2,700.00
              ￥2,400.00
              ￥2,200.00
              ￥3,300.00
              ￥2,800.00
              ￥2,500.00
              ￥2,100.00
              ￥3,300.00
              ￥2,900.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥2,400.00
              ￥2,200.00
              ￥3,600.00
              ￥3,200.00
              ￥2,700.00
              ￥2,500.00
              ￥3,500.00
              ￥3,100.00
              ￥2,600.00
              ￥2,500.00
             ￥14,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             ￥13,500.00
             ￥12,000.00
             ￥11,000.00
             ￥10,500.00
             ￥10,000.00
              ￥9,500.00
              ￥9,000.00
              ￥8,000.00
              ￥7,500.00
              ￥7,000.00
             ￥10,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥9,500.00
              ￥9,000.00
              ￥8,000.00
              ￥7,500.00
              ￥7,000.00
             ￥10,500.00
              ￥9,500.00
              ￥7,200.00
              ￥6,800.00
              ￥6,400.00
              ￥6,200.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             ￥11,500.00
             ￥10,000.00
              ￥9,600.00
              ￥7,400.00
              ￥7,300.00
              ￥6,100.00
             ￥11,000.00
              ￥8,800.00
              ￥8,600.00
              ￥8,400.00
              ￥7,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥6,200.00
              ￥3,200.00
              ￥3,100.00
              ￥2,500.00
              ￥2,800.00
              ￥4,200.00
              ￥4,100.00
              ￥3,400.00
              ￥3,000.00
              ￥3,800.00
              ￥3,600.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              ￥2,900.00
              ￥2,500.00
              ￥4,000.00
              ￥3,900.00
              ￥3,200.00
              ￥2,800.00
              ￥3,100.00
              ￥3,000.00

107 rows selected.

SQL> alter session set nls_territory='america';

Session altered.

SQL> select to_char(salary,'L999,999,999.00')  from hr.employees ;

TO_CHAR(SALARY,'L999,999,
-------------------------
		$2,600.00
		$2,600.00
		$4,400.00
	       $13,000.00
		$6,000.00
		$6,500.00
	       $10,000.00
	       $12,008.00
		$8,300.00
		$8,000.00
	       $17,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
	       $17,000.00
		$9,000.00
		$6,000.00
		$4,800.00
		$4,800.00
		$4,200.00
	       $12,008.00
		$9,000.00
		$8,200.00
		$7,700.00
		$7,800.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$6,900.00
	       $11,000.00
		$3,100.00
		$2,900.00
		$2,800.00
		$2,600.00
		$2,500.00
		$8,000.00
		$8,200.00
		$7,900.00
		$6,500.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$5,800.00
		$3,200.00
		$2,700.00
		$2,400.00
		$2,200.00
		$3,300.00
		$2,800.00
		$2,500.00
		$2,100.00
		$3,300.00
		$2,900.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$2,400.00
		$2,200.00
		$3,600.00
		$3,200.00
		$2,700.00
		$2,500.00
		$3,500.00
		$3,100.00
		$2,600.00
		$2,500.00
	       $14,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
	       $13,500.00
	       $12,000.00
	       $11,000.00
	       $10,500.00
	       $10,000.00
		$9,500.00
		$9,000.00
		$8,000.00
		$7,500.00
		$7,000.00
	       $10,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$9,500.00
		$9,000.00
		$8,000.00
		$7,500.00
		$7,000.00
	       $10,500.00
		$9,500.00
		$7,200.00
		$6,800.00
		$6,400.00
		$6,200.00

TO_CHAR(SALARY,'L999,999,
-------------------------
	       $11,500.00
	       $10,000.00
		$9,600.00
		$7,400.00
		$7,300.00
		$6,100.00
	       $11,000.00
		$8,800.00
		$8,600.00
		$8,400.00
		$7,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$6,200.00
		$3,200.00
		$3,100.00
		$2,500.00
		$2,800.00
		$4,200.00
		$4,100.00
		$3,400.00
		$3,000.00
		$3,800.00
		$3,600.00

TO_CHAR(SALARY,'L999,999,
-------------------------
		$2,900.00
		$2,500.00
		$4,000.00
		$3,900.00
		$3,200.00
		$2,800.00
		$3,100.00
		$3,000.00

107 rows selected.

SQL> alter session set nls_territory='france';

Session altered.

SQL> select to_char(salary,'L999,999,999.00')  from hr.employees ;

TO_CHAR(SALARY,'L999,999,
-------------------------
              €2,600.00
              €2,600.00
              €4,400.00
             €13,000.00
              €6,000.00
              €6,500.00
             €10,000.00
             €12,008.00
              €8,300.00
              €8,000.00
             €17,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             €17,000.00
              €9,000.00
              €6,000.00
              €4,800.00
              €4,800.00
              €4,200.00
             €12,008.00
              €9,000.00
              €8,200.00
              €7,700.00
              €7,800.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €6,900.00
             €11,000.00
              €3,100.00
              €2,900.00
              €2,800.00
              €2,600.00
              €2,500.00
              €8,000.00
              €8,200.00
              €7,900.00
              €6,500.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €5,800.00
              €3,200.00
              €2,700.00
              €2,400.00
              €2,200.00
              €3,300.00
              €2,800.00
              €2,500.00
              €2,100.00
              €3,300.00
              €2,900.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €2,400.00
              €2,200.00
              €3,600.00
              €3,200.00
              €2,700.00
              €2,500.00
              €3,500.00
              €3,100.00
              €2,600.00
              €2,500.00
             €14,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             €13,500.00
             €12,000.00
             €11,000.00
             €10,500.00
             €10,000.00
              €9,500.00
              €9,000.00
              €8,000.00
              €7,500.00
              €7,000.00
             €10,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €9,500.00
              €9,000.00
              €8,000.00
              €7,500.00
              €7,000.00
             €10,500.00
              €9,500.00
              €7,200.00
              €6,800.00
              €6,400.00
              €6,200.00

TO_CHAR(SALARY,'L999,999,
-------------------------
             €11,500.00
             €10,000.00
              €9,600.00
              €7,400.00
              €7,300.00
              €6,100.00
             €11,000.00
              €8,800.00
              €8,600.00
              €8,400.00
              €7,000.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €6,200.00
              €3,200.00
              €3,100.00
              €2,500.00
              €2,800.00
              €4,200.00
              €4,100.00
              €3,400.00
              €3,000.00
              €3,800.00
              €3,600.00

TO_CHAR(SALARY,'L999,999,
-------------------------
              €2,900.00
              €2,500.00
              €4,000.00
              €3,900.00
              €3,200.00
              €2,800.00
              €3,100.00
              €3,000.00

107 rows selected.

SQL> alter session set nls_territory='australia';

Session altered.

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

NEXT_DAY(SYSDATE,4
------------------
21/APR/16

SQL> alter session set nls_territory='china';

Session altered.

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

NEXT_DAY(SYSDATE,4
------------------
27-APR-16

SQL> alter session set nls_territory='canada';

Session altered.

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

NEXT_DAY
--------
16-04-27

SQL> select  last_day(sysdate ) from dual;

LAST_DAY
--------
16-04-30

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

ROUND(SY
--------
16-01-01

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

TRUNC(SY
--------
16-01-01

SQL> select  trunc (to_date('2016-06-30','YYYY-MM-DD'),'YEAR') from dual; 

TRUNC(TO
--------
16-01-01

SQL> select  trunc (to_date('2016-07-01','YYYY-MM-DD'),'YEAR') from dual; 

TRUNC(TO
--------
16-01-01

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

ROUND(TO
--------
17-01-01

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

ROUND(TO
--------
16-01-01

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

ROUND(SY
--------
16-05-01

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

TRUNC(SY
--------
16-04-01

SQL> select  trunc(to_date('2016-02-15','YYYY-MM-DD'),'MONTH') from dual; 

TRUNC(TO
--------
16-02-01

SQL> select  trunc(to_date('2016-02-16','YYYY-MM-DD'),'MONTH') from dual; 

TRUNC(TO
--------
16-02-01

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

ROUND(TO
--------
16-03-01

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

ROUND(TO
--------
16-02-01

SQL> 


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



[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 20 19:50:25 2016

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

SQL> conn hr/oracle_4U
Connected.
SQL>  select  to_char(sysdate, 'Month') from dual;

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

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

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

SQL> alter session set nls_language='korean';

Session altered.

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

TO_CHAR(
--------
4월

SQL> alter session set nls_language='italian';

Session altered.

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

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

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

Session altered.

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

TO_CHAR(
--------
4月

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

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

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

TO_CHAR(SY
----------
2016-04-20

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

TO_CHAR(SY
----------
2016-4-20

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

TO
--
20

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

TO_
---
111

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

T
-
4

SQL> alter session set nls_territory='australia';

Session altered.

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

T
-
3

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

TO_
---
3rd

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

TO_CHAR(SYSD
------------
星期三

SQL> alter session set nls_language='american';

Session altered.

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

TO_CHAR(SYSDATE,'DAY')
------------------------------------
WEDNESDAY

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

TO_CHAR(SYSDATE,'DAY')
------------------------------------
wednesday

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

TO_CHAR(SYSD
------------
wed

SQL>  select  to_char(sysdate, 'dy HH24:Mi:SS AM') from dual;

TO_CHAR(SYSDATE,'DYHH24:
------------------------
wed 19:58:22 PM

SQL>  select  to_char(sysdate, 'dy "THIS IS" HH24:Mi:SS AM') from dual;

TO_CHAR(SYSDATE,'DY"THISIS"HH24:
--------------------------------
wed THIS IS 19:59:56 PM

SQL> select  to_char(123456.789,'L999,999,999,999,999.000' ) from dual;

TO_CHAR(123456.789,'L999,999,999,9
----------------------------------
		      $123,456.789

SQL> alter session set nls_territory='china' ;

Session altered.

SQL> select  to_char(123456.789,'L999,999,999,999,999.000' ) from dual;

TO_CHAR(123456.789,'L999,999,999,9
----------------------------------
                    ￥123,456.789

SQL> select  to_char(123456.789,'L999.000' ) from dual;

TO_CHAR(123456.789
------------------
##################

SQL> select  to_char(123456.789,'L000,999,999.000' ) from dual;

TO_CHAR(123456.789,'L000,9
--------------------------
        ￥000,123,456.789

SQL> select  to_number('￥000,123,456.789','L000,999,999.000') from dual;

TO_NUMBER('￥000,123,456.789','L000,999,999.000')
-------------------------------------------------
				       123456.789

SQL> select nvl(commission_pct, 0 ) from employees where department_id=10;

NVL(COMMISSION_PCT,0)
---------------------
		    0

SQL> select nvl(commission_pct, 0 ), commission_pct  from employees where department_id=10;

NVL(COMMISSION_PCT,0) COMMISSION_PCT
--------------------- --------------
		    0

SQL> select nvl(manager_id, 'No Manager' ), manager_id  from employees where employee_id=100;
select nvl(manager_id, 'No Manager' ), manager_id  from employees where employee_id=100
                       *
ERROR at line 1:
ORA-01722: invalid number


SQL> 
SQL> select nvl(manager_id, 'NoManager' ), manager_id  from employees where employee_id=100;
select nvl(manager_id, 'NoManager' ), manager_id  from employees where employee_id=100
                       *
ERROR at line 1:
ORA-01722: invalid number


SQL> select nvl(manager_id,  ), manager_id  from employees where employee_id=100;

SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table t05104_a( a varchar2(20))   ;

Table created.

SQL> insert into t05104_a  values ('A') ;

1 row created.

SQL> insert into t05104_a  values ('') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select  * from t05104_a ;

A
--------------------
A


SQL> select  nvl(a,'No value')  from t05104_a ;

NVL(A,'NOVALUE')
--------------------
A
No value

SQL> select nvl2(manager_id,'HAS MANAGER','WITHOUT MANAGER'  ), manager_id  from employees where employee_id=100;

NVL2(MANAGER_ID MANAGER_ID
--------------- ----------
WITHOUT MANAGER

SQL> select nvl2(manager_id,'HAS MANAGER','WITHOUT MANAGER'  ), manager_id  from employees where employee_id=200;

NVL2(MANAGER_ID MANAGER_ID
--------------- ----------
HAS MANAGER	       101

SQL> select  nullif('A' ,'A' ) from dual;

N
-


SQL> select  nullif('A' ,'B' ) from dual;

N
-
A

SQL> select  nullif(1 ,1 ) from dual;

NULLIF(1,1)
-----------


SQL> select  nullif(2 ,1 ) from dual;

NULLIF(2,1)
-----------
	  2

SQL> select  nullif(sysdate ,sysdate ) from dual;

NULLIF(SYSDATE,SYS
------------------


SQL> select  nullif(systimestamp,systimestamp ) from dual;

NULLIF(SYSTIMESTAMP,SYSTIMESTAMP)
---------------------------------------------------------------------------


SQL> select  nullif(systimestamp,systimestamp ) from dual;

NULLIF(SYSTIMESTAMP,SYSTIMESTAMP)
---------------------------------------------------------------------------


SQL> select  nullif(systimestamp,systimestamp ) from dual;

NULLIF(SYSTIMESTAMP,SYSTIMESTAMP)
---------------------------------------------------------------------------


SQL> show user
USER is "HR"
SQL> create table t05104_b( a  varchar2(20), b varchar2(20) , c varchar2(20)) ;

Table created.

SQL> insert into t05104_b  values ('A',null,null) ;

1 row created.

SQL> insert into t05104_b  values (null,'B',null) ;

1 row created.

SQL> insert into t05104_b  values (null,null,'C') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select  a from t05104_b;

A
--------------------
A



SQL> select  coalesce( a,b,c)  from t05104_b;

COALESCE(A,B,C)
--------------------
A
B
C

SQL> 





































