|
|
课程第11次（2016-04-20星期三）：单行函数，转换函数（单行函数的一种），组函数和分组
【上完1Z0-051的第3章】：单行函数
【上完1Z0-051的第4章】：转换函数（单行函数的一种）
【上完1Z0-051的第5章】：组函数和分组
【1Z0-051】：共6章
【1Z0-052】：共3章
2016-04-20.txt：
[code]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,
-------------------------
|
|
发表于 2016-4-21 09:17:42
