过滤和排序数据
- 在查询中过滤行。
- 在查询中对行进行排序。
- 在运行时使用“&”字符替换来限制和排序输出
1.1. 过滤
- 使用WHERE子句，将不满足条件的行过滤掉
- WHERE子句紧随FROM子句
语法：
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
1) 使用WHERE子句
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;
2) WHERE 子句中的字符和日期
- 字符和日期要包含在单引号中。
- 字符大小写敏感，日期格式敏感。
- 默认的日期格式是 DD-MON-RR
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen'
/
SELECT last_name, hire_date
FROM employees
WHERE hire_date = '2007-6-21'
/
注hire_date字段为日期型，'2007-6-21'的格式要与当前会话的nls_date_format的格式相同
格式相同，字符串会做一个隐式的转换
1.2. 比较运算符
=,>,>=,<,<=,<>(!=,^=),BETWEEN...AND...,IN(),LIKE,IS NULL
1) 使用比较运算符
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
2) 使用BETWEEN 操作符
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
注使用 BETWEEN 操作符来显示在一个区间内的值，结果包含边界值
3) 使用IN 操作符
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
4) 使用LIKE 操作符
- % 代表一个或多个字符。
- _ 代表一个字符。
SELECT first_name FROM employees WHERE first_name LIKE 'S%'
/
SELECT last_name FROM employees WHERE last_name LIKE '_o%'
/
- 使用 ESCAPE 标识符
SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '\';
请问：如果字符串中有两个连续的“_”，那么是否需要两次转义？不需要
测试脚本如下：
insert into t_like values(6,'a__d');
commit;
SQL1：select * from t_like where y like 'a\__d%' escape '\';
SQL2：select * from t_like where y like 'a\_\_d%' escape '\';
5) 使用NULL条件
使用IS NULL 操作符判断空值
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
注在检索null时，无法用=或<>来进行判断，只允许用is null和is not null来进行判断
1.3. 逻辑运算符
AND,OR,NOT
1) 使用AND 运算符（要求两边条件都为真）
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000 AND job_id LIKE '%MAN%';
2) 使用OR 运算符（只要任一条件为真即可）
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000 OR job_id LIKE '%MAN%'
3) 使用NOT 操作符
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP')
/
SELECT last_name, manager_id FROM employees WHERE manager_id IS NOT NULL
/
1.4. 表达式中运算符优先级
1. 算数运算符
2. 连接符
3. 比较符
4. IS [NOT] NULL, LIKE, [NOT] IN
5. [NOT] BETWEEN
6. 不等于
7. NOT
8. AND
9. OR
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES'
AND salary > 15000
/
SELECT last_name, job_id, salary
FROM employees
WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES')
AND salary > 15000
/
1.5. 排序
- 使用ORDER BY 子句排序：ASC（升序，默认），DESC（降序）
- ORDER BY 子句在SELECT 语句结尾
1) 默认（升序）排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;
2) 降序排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;
3) 使用别名默认排序
SELECT employee_id, last_name, salary * 12 annsal
FROM employees
ORDER BY annsal;
4) 使用的列的数字位置
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 3;
5) 按多个列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id DESC, salary ASC;
6) 排序字段包含NULL值
SELECT DISTINCT department_id FROM employees ORDER BY 1;
我们会发现，结果中有NULL值，升序排序后，NULL值在最下面（反之在最上面），因为NULL值是未知的，也就是“无穷大”
如果，我们的需求是，非空值升序，空值在显示结果的最上面
SELECT DISTINCT department_id FROM employees ORDER BY 1 ASC NULLS FIRST;
如果，我们的需求是，非空值降序，控制在显示结果的最下面
SELECT DISTINCT department_id FROM employees ORDER BY 1 DESC NULLS LAST;
1.6. 替代变量
- 使用&和&&临时存储值，替代变量
- 使用替代变量，可以替代以下内容
ü WHERE 条件
ü ORDER BY 子句
ü 列表达式
ü 表名
ü 整个 SELECT 语句
1) 数值型变量
HR@ORA11GR2>SELECT employee_id, last_name, salary, department_id
2 FROM employees
3 WHERE employee_id = &v_num;
Enter value for v_num: 101
old 3: WHERE employee_id = &v_num
new 3: WHERE employee_id = 101
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
101 Kochhar 17000 90
HR@ORA11GR2>
2) 字符型变量
HR@ORA11GR2>SELECT last_name, department_id, salary*12
2 FROM employees
3 WHERE job_id = '&job_title' ;
Enter value for job_title: HR_REP
old 3: WHERE job_id = '&job_title'
new 3: WHERE job_id = 'HR_REP'
LAST_NAME DEPARTMENT_ID SALARY*12
------------------------- ------------- ----------
Mavris 40 78000
HR@ORA11GR2>
3) 日期型变量
HR@ORA11GR2>SELECT last_name, department_id, salary FROM employees WHERE hire_date = '&hd';
Enter value for hd: 2008/1/13
old 1: SELECT last_name, department_id, salary FROM employees WHERE hire_date = '&hd'
new 1: SELECT last_name, department_id, salary FROM employees WHERE hire_date = '2008/1/13'
LAST_NAME DEPARTMENT_ID SALARY
------------------------- ------------- ----------
Grant 50 2600
HR@ORA11GR2>
或
HR@ORA11GR2>SELECT last_name, department_id, salary FROM employees WHERE hire_date = to_date('&hd', 'yyyy-mm-dd');
Enter value for hd: 2008/1/13
old 1: SELECT last_name, department_id, salary FROM employees WHERE hire_date = to_date('&hd', 'yyyy-mm-dd')
new 1: SELECT last_name, department_id, salary FROM employees WHERE hire_date = to_date('2008/1/13', 'yyyy-mm-dd')
LAST_NAME DEPARTMENT_ID SALARY
------------------------- ------------- ----------
Grant 50 2600
HR@ORA11GR2>
4) WHERE子句、ORDER BY子句、字段
HR@ORA11GR2>SELECT employee_id, last_name, job_id, &column_name
2 FROM employees
3 WHERE &condition
4 ORDER BY &order_column;
Enter value for column_name: email
old 1: SELECT employee_id, last_name, job_id, &column_name
new 1: SELECT employee_id, last_name, job_id, email
Enter value for condition: manager_id=101
old 3: WHERE &condition
new 3: WHERE manager_id=101
Enter value for order_column: 1
old 4: ORDER BY &order_column
new 4: ORDER BY 1
EMPLOYEE_ID LAST_NAME JOB_ID EMAIL
----------- ---------------------- ---------- -------------------------
108 Greenberg FI_MGR NGREENBE
200 Whalen AD_ASST JWHALEN
203 Mavris HR_REP SMAVRIS
204 Baer PR_REP HBAER
205 Higgins AC_MGR SHIGGINS
HR@ORA11GR2>
5) 使用&&替代变量(重复变量，无需再次录入)
HR@ORA11GR2>SELECT employee_id, last_name, job_id, &&column_name
2 FROM employees
3 WHERE &condition
4 ORDER BY &column_name;
Enter value for column_name: hire_date
old 1: SELECT employee_id, last_name, job_id, &&column_name
new 1: SELECT employee_id, last_name, job_id, hire_date
Enter value for condition: department_id=20
old 3: WHERE &condition
new 3: WHERE department_id=20
old 4: ORDER BY &column_name
new 4: ORDER BY hire_date
EMPLOYEE_ID LAST_NAME JOB_ID HIRE_DATE
----------- ---------------------- ---------- -------------------
201 Hartstein MK_MAN 2004-02-17 00:00:00
202 Fay MK_REP 2005-08-17 00:00:00
HR@ORA11GR2>
1.7. DEFINE命令
- 使用DEFINE命令创建和分配一个变量的值。
- 使用UNDEFINE命令来删除一个变量。
HR@ORA11GR2>DEFINE employee_num = 200
HR@ORA11GR2>SELECT employee_id, last_name, salary, department_id
2 FROM employees
3 WHERE employee_id = &employee_num;
old 3: WHERE employee_id = &employee_num
new 3: WHERE employee_id = 200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
HR@ORA11GR2>DEFINE
DEFINE _DATE = "2013-03-05 16:47:25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORA11GR2" (CHAR)
DEFINE _USER = "HR" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)
DEFINE EMPLOYEE_NUM = "200" (CHAR)
HR@ORA11GR2>
HR@ORA11GR2>UNDEFINE employee_num
HR@ORA11GR2>DEFINE
DEFINE _DATE = "2013-03-05 16:47:46" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORA11GR2" (CHAR)
DEFINE _USER = "HR" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)
HR@ORA11GR2>
1.8. VERIFY命令
使用VERIFY命令来显示替换变量之前和之后的值，默认值为ON，即显示替换变量之前之后的值
HR@ORA11GR2>show VERIFY
verify ON
HR@ORA11GR2>SELECT employee_id, last_name, salary, department_id
2 FROM employees
3 WHERE employee_id = &employee_num;
Enter value for employee_num: 200
old 3: WHERE employee_id = &employee_num
new 3: WHERE employee_id = 200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
HR@ORA11GR2>SET VERIFY OFF
HR@ORA11GR2>show VERIFY
verify OFF
HR@ORA11GR2>
HR@ORA11GR2>SELECT employee_id, last_name, salary, department_id
2 FROM employees
3 WHERE employee_id = &employee_num;
Enter value for employee_num: 200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
HR@ORA11GR2>
单行函数
1.9. 字符型函数
1.9.1. ASCII
1) 返回与指定的字符对应的ASCII码;
SCOTT@ORA11GR2>select ascii('A') A,ascii('a') "a",ascii('0') zero,ascii(' ') space from dual;
A a ZERO SPACE
---------- ---------- ---------- ----------
65 97 48 32
SCOTT@ORA11GR2>
2) 但是求单引号的ASCII码写法很特殊两个单引带表一个单引
SCOTT@ORA11GR2> select ASCII(''') from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SCOTT@ORA11GR2> select ASCII('''') from dual;
ASCII('''')
-----------
39
SCOTT@ORA11GR2>
3) 通过下面的例子我们发现，ASCII只针对第一个字符进行转换
SCOTT@ORA11GR2>select ascii('A') ,ascii('AB') ,ascii('ABC') from dual;
ASCII('A') ASCII('AB') ASCII('ABC')
---------- ----------- ------------
65 65 65
SCOTT@ORA11GR2>
ASCII是“美国信息交换标准码”的英文缩写(American Standard Code for Information Interchange)
1.9.2. CHR
语法：CHR(n [ USING NCHAR_CS ])
返回n的字符值 n是ASCII码数
SCOTT@ORA11GR2> select chr(39) from dual;
C
-
'
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select 'I''m sorry' as sorry1,'I'||chr(39)||'m sorry' as sorry2 from dual;
SORRY1 SORRY2
--------- ---------
I'm sorry I'm sorry
SCOTT@ORA11GR2>
注：
回车：chr(13)
换行：chr(10)
1.9.3. ASCIISTR&UNISTR
语法：
ASCIISTR(char)
UNISTR(char)
在任何字符集和数据库字符集中返回一个ASCII字符串。非ASCII字符转换为\ xxxx，其中xxxx表示一个UTF-16代码单元的形式。
SQL> select ASCIISTR('你好hello') from dual;
ASCIISTR('你好HELLO')
---------------------
\4F60\597Dhello
SQL> select UNISTR('\4F60\597Dhello') from dual;
UNISTR('\4F60\597DHELLO')
-------------------------
你好hello
SQL>
例：显示表t字段name中，没有中文字符的记录
SQL> with t as (
select 'abc' as name from dual
union all
select 'abc你' from dual
union all
select '哈哈' from dual
union all
select '你hao' from dual)
select * from t where asciistr(name) not like '%\%';
NAME
------------
abc
SQL>
1.9.4. CONCAT
语法：CONCAT(char, dest_char_set[, source_char_set ])
连接两个字符串;
SCOTT@ORA11GR2> select concat('Hello'||chr(32),'OCM')||'!' as "HELLO OCM!" from dual;等同于select concat('Hello',' OCM!') AS "HELLO OCM!" from dual;
HELLO OCM!
----------
Hello OCM!
SCOTT@ORA11GR2>
1.9.5. INITCAP
语法：INITCAP(char)
返回字符串并将字符串的第一个字母变为大写;
SCOTT@ORA11GR2> select initcap('james') as james from dual;
JAMES
-----
James
SCOTT@ORA11GR2>
1.9.6. INSTR
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
语法：INSTR(C1,C2,I,J)
ü C1:被搜索的字符串
ü C2:希望搜索的字符串
ü I:搜索的开始位置，默认为1
ü J:出现的位置，默认为1
SCOTT@ORA11GR2> select instr('a2a4a6a8','a') as "instr" from dual;
instr
----------
1
SCOTT@ORA11GR2> select instr('a2a4a6a8','a',2,1) as "instr" from dual;
instr
----------
3
SCOTT@ORA11GR2> select instr('a2a4a6a8','a',1,2) as "instr" from dual;
instr
----------
3
SCOTT@ORA11GR2> select instr('a2a4a6a8','a',-1,1) as "instr" from dual;
instr
----------
7
SCOTT@ORA11GR2>
1.9.7. SUBSTR
语法：substr(string,start,count)l
示例：SUBSTR('HelloWorld',1,5) 输出hello
示例：select substr(first_name,2) from employees;表示从输入字符第二位之前的字符截断
取子字符串,从start开始,取count个
1) 字符串'a2a4a6a8'，从后面数第一个'a'开始，截取之后的所有字符串
SCOTT@ORA11GR2>select substr('a2a4a6a8',instr('a2a4a6a8','a',-1)) from dual;
SU
--
a8
SCOTT@ORA11GR2>
2) 从第二个a开始到第三个a之前这段儿字符串
SCOTT@ORA11GR2>SELECT substr('a2a4a6a8', instr('a2a4a6a8', 'a', 2, 1), instr('a2a4a6a8', 'a', -1, 1) - instr('a2a4a6a8', 'a', 2, 1)) FROM dual;
SUBS
----
a4a6
SCOTT@ORA11GR2>
1.9.8. VSIZE
语法：VSIZE(expr)
VSIZE返回expr的内部表示的字节数。如果expr是空的，那么这个函数返回null，此功能不支持直接CLOB数据
SCOTT@ORA11GR2> select vsize('丽') from dual;
VSIZE('丽')
-----------
2
SCOTT@ORA11GR2>
1.9.9. LOWER
语法：LOWER(char)
返回字符串,并将所有的字符小写
SCOTT@ORA11GR2> select 'OcP'as "OcP",lower('OcP') as "ocp" from dual;
OcP ocp
--- ---
OcP ocp
SCOTT@ORA11GR2>
1.9.10. UPPER
语法：UPPER(char)
返回字符串,并将所有的字符大写
SCOTT@ORA11GR2> select 'OcP'as "OcP",upper('OcP') as "OCP" from dual;
OcP OCP
--- ---
OcP OCP
SCOTT@ORA11GR2>
1.9.11. RPAD&LPAD
语法：LPAD(expr1, n [, expr2 ])在列的左边粘贴字符
语法：RPAD(expr1, n [, expr2 ])在列的右边粘贴字符
LPAD(salary,10,'*')；输出：*****24000
RPAD(salary, 10, '*') ；输出：24000*****
1) 用字符'0'使字符串在列的左侧补足5位
SCOTT@ORA11GR2> select lpad(rownum,5,'0') as left from dual connect by rownum<=5;
LEFT
--------------------
00001
00002
00003
00004
00005
SCOTT@ORA11GR2>
2) 用字符'0'使字符串在列的右侧补足5位
SCOTT@ORA11GR2> select rpad(rownum,5,'0') as right from dual connect by rownum<=5;
RIGHT
--------------------
10000
20000
30000
40000
50000
SCOTT@ORA11GR2>
3) 用字符'0'使字符串在列的左侧补足5位，然后再在右侧补字符'0'，使其到10位
SCOTT@ORA11GR2> select lpad(rpad(rownum,5,'0'),10,'0') as lr from dual connect by rownum<=5;
LR
----------------------------------------
0000010000
0000020000
0000030000
0000040000
0000050000
SCOTT@ORA11GR2>
1.9.12. LTRIM & RTRIM
语法：LTRIM(char [, set ])删除左边出现的字符串
语法：RTRIM(char [, set ])删除右边出现的字符串
1) 构造str字符串
SCOTT@ORA11GR2>with t as (select lpad(rpad(rownum,5,'0'),10,'0') as str from dual connect by rownum<=5)
select str from t;
STR
----------------------------------------
0000010000
0000020000
0000030000
0000040000
0000050000
SCOTT@ORA11GR2>
2) 删除字符串str左侧的'0'
SCOTT@ORA11GR2>with t as (select lpad(rpad(rownum,5,'0'),10,'0') as str from dual connect by rownum<=5)
select ltrim(str,'0') as "ltrim" from t;
ltrim
----------------------------------------
10000
20000
30000
40000
50000
SCOTT@ORA11GR2>
3) 删除字符串str右侧的'0'
SCOTT@ORA11GR2>with t as (select lpad(rpad(rownum,5,'0'),10,'0') as str from dual connect by rownum<=5)
select rtrim(str,'0') as "rtrim" from t;
rtrim
----------------------------------------
000001
000002
000003
000004
000005
SCOTT@ORA11GR2>
1.9.13. REPLACE
语法：
REPLACE('string','s1','s2')
ü string 希望被替换的字符或变量
ü s1 被替换的字符串
ü s2 要替换的字符串
REPLACE示例：('JACK and JUE','J','BL') 输出：BLACK AND BLUE.
1) 将字符串的'0'都替换为null
SCOTT@ORA11GR2>select replace('0000010000','0') from dual;
R
-
1
SCOTT@ORA11GR2>
注：如果replace的第二个参数没有指定，那么默认为null，当然，我们也可以采用如下方式达到这个效果
SCOTT@ORA11GR2>select replace('0000010000','0',null) from dual;
R
-
1
SCOTT@ORA11GR2>select replace('0000010000','0','') from dual;
R
-
1
SCOTT@ORA11GR2>
2) 将字符串的'0'都替换为'1'
SCOTT@ORA11GR2>select replace('0000010000','0','1') from dual;
REPLACE('0
----------
1111111111
SCOTT@ORA11GR2>
3) 将字符串的'0'都替换为'dt'
SCOTT@ORA11GR2>select replace('0000010000','0','dt') from dual;
REPLACE('0000010000
-------------------
dtdtdtdtdt1dtdtdtdt
SCOTT@ORA11GR2>
1.9.14. TRANSLATE
语法：TRANSLATE(expr, from_string, to_string)
返回将出现在from_string中的每个字符替换为to_string中的相应字符以后的字符串。若from_string比to_string字符串长，那么在from_string中比to_string中多出的字符将会被删除。三个参数中有一个是空，返回值也将是空值。
SCOTT@ORA11GR2> select translate('abclloc','abc','he') from dual;
TRANS
-----
hello
SCOTT@ORA11GR2>
分析：
1) 将abc替换为he
2) a对应h，b对应e，c没有对应值
3) 替换，a都替换为h，b都替换为e，c由于没有对应值，故全部删掉
例1：删除字符串中所有数字
SCOTT@ORA11GR2> select translate('123abc','a0123456789','a') from dual;
TRA
---
abc
SCOTT@ORA11GR2>
SCOTT@ORA11GR2> with t as(
select '123abc456' as str from dual union all
select 'eess5533' from dual union all
select '8hello world8' from dual union all
select '#$%123abc' from dual)
select translate(str,'#0123456789','#') from t;
TRANSLATE(STR,'#0123456789','#')
----------------------------------------------------
abc
eess
hello world
#$%abc
SCOTT@ORA11GR2>
例2：统计逗号的个数
SCOTT@ORA11GR2>select length(translate('a,b,c,d',',abcd',',')) from dual;
LENGTH(TRANSLATE('A,B,C,D',',ABCD',','))
----------------------------------------
3
SCOTT@ORA11GR2>
1.9.15. SOUNDEX
语法：SOUNDEX(char)
返回一个与给定的字符串读音几乎相同的字符串
SCOTT@ORA11GR2> with t as (
select 'sun' as val from dual union all
select 'son' from dual union all
select 'hello' from dual)
select * from t where soundex(val)=soundex('san');
VAL
-----
sun
son
SCOTT@ORA11GR2>
1.9.16. TRIM
语法：
TRIM([ { { LEADING | TRAILING | BOTH }
[ trim_character ]
| trim_character}FROM]trim_source)
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
BOTH 前后都剪
1) 默认（等同于BOTH）
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select '|'||name||'|', '|'||trim(name)||'|' from t;
'|'||NA '|'||
------- -----
| OCP | |OCP|
SCOTT@ORA11GR2>
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select '|'||name||'|', '|'||trim(BOTH from name)||'|' from t;
'|'||NA '|'||
------- -----
| OCP | |OCP|
SCOTT@ORA11GR2>
2) 剪掉前面的空格
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select '|'||name||'|', '|'||trim(LEADING from name)||'|' from t;
'|'||NA '|'||T
------- ------
| OCP | |OCP |
SCOTT@ORA11GR2>
3) 剪掉后面的空格
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select '|'||name||'|', '|'||trim(TRAILING from name)||'|' from t;
'|'||NA '|'||T
------- ------
| OCP | | OCP|
SCOTT@ORA11GR2>
4) 剪掉两边的指定字符“P”
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select trim(upper('p') from trim(name))||'M' from t;
TRI
---
OCM
SCOTT@ORA11GR2>
5) 剪掉后面的指定字符“P”
SCOTT@ORA11GR2> with t as
(SELECT chr(32)||'OCP'||chr(32) as name FROM dual)
select '|'||trim(TRAILING upper('p') from trim(name))||'M|' from t;
'|'||
-----
|OCM|
SCOTT@ORA11GR2>
1.9.17. ABS
语法：ABS(n)
返回指定值的绝对值
SCOTT@ORA11GR2> select abs(-95) from dual;
ABS(-95)
----------
95
SCOTT@ORA11GR2>
1.9.18. 半角/全角转换
语法：TO_MULTI_BYTE(char)半角转全角
语法：TO_SINGLE_BYTE(char)全角转半角
SCOTT@ORA11GR2> select TO_MULTI_BYTE('a'),TO_SINGLE_BYTE('ａ') from dual;
TO_MULTI_BYTE('A') TO_SINGLE_BYTE('Ａ')
------------------ --------------------
ａ a
SCOTT@ORA11GR2>
1.9.19. LEAST&GREATEST
语法：LEAST(expr [, expr ]...)
expr为字符型，返回一组字符串的最小值
语法：GREATEST(expr [, expr ]...)
expr为字符型，返回一组字符串的最大值
SCOTT@ORA11GR2> select least('aa','ab','ac') as s,greatest('aa','ab','ac') as b from dual;
S B
-- --
aa ac
SCOTT@ORA11GR2>
巧用LEAST和GREATEST去除重复
SCOTT@ORA11GR2> with t as(
select 'zhangfei' as name1,'zhangyide' as name2 from dual
union all select 'liubei','liuxuande' from dual
union all select 'guanyu','guanyuchang' from dual
union all select 'liubei','liuxuande' from dual
union all select 'guanyu','guanyuchang' from dual
union all select 'zhangyide','zhangfei' from dual
union all select 'guanyuchang','guanyu' from dual)
select * from t;
NAME1 NAME2
----------- -----------
zhangfei zhangyide
liubei liuxuande
guanyu guanyuchang
liubei liuxuande
guanyu guanyuchang
zhangyide zhangfei
guanyuchang guanyu
7 rows selected.
SCOTT@ORA11GR2>
SCOTT@ORA11GR2> with t as(
select 'zhangfei' as name1,'zhangyide' as name2 from dual
union all select 'liubei','liuxuande' from dual
union all select 'guanyu','guanyuchang' from dual
union all select 'liubei','liuxuande' from dual
union all select 'guanyu','guanyuchang' from dual
union all select 'zhangyide','zhangfei' from dual
union all select 'guanyuchang','guanyu' from dual)
select distinct least(name1,name2) as name1,greatest(name1,name2) as name2 from t;
NAME1 NAME2
----------- -----------
liubei liuxuande
guanyu guanyuchang
zhangfei zhangyide
SCOTT@ORA11GR2>
1.10. 数值型函数
1.10.1. CEIL
语法：ceil(n)
取大于等于数值n的最小整数
SCOTT@ORA11GR2> select ceil(9.9),ceil(9),ceil(9.1) from dual;
CEIL(9.9) CEIL(9) CEIL(9.1)
---------- ---------- ----------
10 9 10
SCOTT@ORA11GR2>
1.10.2. FLOOR
语法：floor(n)
取小于等于数值n的最大整数
SCOTT@ORA11GR2> select floor(9.9),floor(9),floor(9.1) from dual;
FLOOR(9.9) FLOOR(9) FLOOR(9.1)
---------- ---------- ----------
9 9 9
SCOTT@ORA11GR2>
1.10.3. TRUNC
语法：trunc(x [,y])
1) 其中y表示小数点后面保留的位数，如果没有指定y，则对x在0位小数进行截断，如：
SCOTT@ORA11GR2> select trunc(3.1415) from dual;
TRUNC(3.1415)
-------------
3
SCOTT@ORA11GR2>
2) 如果指定y为正数, 则对x在y位小数进行截断，如下：
SCOTT@ORA11GR2> select trunc(9.99,1) from dual;
TRUNC(9.99,1)
-------------
9.9
SCOTT@ORA11GR2>
3) 如果y是负数，则对x在小数点左边的第|y|位处进行截断，如：
SCOTT@ORA11GR2> select trunc(9.99,-1) from dual;
TRUNC(9.99,-1)
--------------
0
SCOTT@ORA11GR2> select trunc(129.99,-1) from dual;
TRUNC(129.99,-1)
----------------
120
SCOTT@ORA11GR2> select trunc(129.99,-2) from dual;
TRUNC(129.99,-2)
----------------
100
SCOTT@ORA11GR2>
1.10.4. ROUND
语法：round(x [,y])
用法同trunc，区别在于round四舍五入，如果没有指定y，则对x在0位小数的后以为进行四舍五入；
测试效果如下：
SCOTT@ORA11GR2> select round(3.1415) from dual;
ROUND(3.1415)
-------------
3
SCOTT@ORA11GR2> select round(3.1415,2) from dual;
ROUND(3.1415,2)
---------------
3.14
SCOTT@ORA11GR2> select round(9.99,1) from dual;
ROUND(9.99,1)
-------------
10
SCOTT@ORA11GR2> select round(9.99,-1) from dual;
ROUND(9.99,-1)
--------------
10
SCOTT@ORA11GR2> select round(129.99,-1) from dual;
ROUND(129.99,-1)
----------------
130
SCOTT@ORA11GR2> select round(129.99,-2) from dual;
ROUND(129.99,-2)
----------------
100
SCOTT@ORA11GR2>
1.10.5. MOD
语法: MOD(n1,n2)
返回n1除以n2的余数.如果n2是0,则返回n1
SCOTT@ORA11GR2> select mod(10,3),mod(3,3),mod(2,3),mod(9,0) from dual;
MOD(10,3) MOD(3,3) MOD(2,3) MOD(9,0)
---------- ---------- ---------- ----------
1 0 2 9
SCOTT@ORA11GR2>
1.10.6. POWER
语法：POWER(x,y)
返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数。
SCOTT@ORA11GR2> SELECT trunc(power(10, 1 / 2), 2), trunc(power(10, -1 / 2), 2), power(-2, 2) FROM dual;
TRUNC(POWER(10,1/2),2) TRUNC(POWER(10,-1/2),2) POWER(-2,2)
---------------------- ----------------------- -----------
3.16 .31 4
SCOTT@ORA11GR2>
小缺陷：Y为小数会报错（注：-8的1/3次方是有意义的）
SCOTT@ORA11GR2> select power(-8,1/3) from dual;
select power(-8,1/3) from dual
*
ERROR at line 1:
ORA-01428: argument '-8' is out of range
SCOTT@ORA11GR2>
1.10.7. SIGN
语法：SIGN(n)
sign函数就是实现如下功能：
CREATE OR REPLACE FUNCTION my_sign(p_val IN NUMBER) RETURN NUMBER IS
v_return NUMBER;
BEGIN
IF p_val > 0 THEN
v_return := 1;
ELSIF p_val < 0 THEN
v_return := -1;
ELSE with
v_return := 0;
END IF;
RETURN v_return;
END;
/
1) 测试自定义的my_sign函数：
SCOTT@ORA11GR2> select my_sign(100),my_sign(0),my_sign(-1),my_sign(5-6) from dual;
MY_SIGN(100) MY_SIGN(0) MY_SIGN(-1) MY_SIGN(5-6)
------------ ---------- ----------- ------------
1 0 -1 -1
SCOTT@ORA11GR2>
2) 测试sign函数：
SCOTT@ORA11GR2> select sign(100),sign(0),sign(-1),sign(5-6) from dual;
SIGN(100) SIGN(0) SIGN(-1) SIGN(5-6)
---------- ---------- ---------- ----------
1 0 -1 -1
SCOTT@ORA11GR2>
1.11. 日期型函数
1.11.1. SYSDATE
1) 用来得到系统的当前日期，不过，这种格式不使我们常用的“年-月-日”
SCOTT@ORA11GR2> select sysdate from dual;
SYSDATE
---------
09-JUN-12
SCOTT@ORA11GR2>
- 我们可以在当前会话修改日期格式（默认的日期格式是 DD-MON-RR）
SCOTT@ORA11GR2> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SCOTT@ORA11GR2>
2) 再次查看即为我们习惯的格式
SCOTT@ORA11GR2> select sysdate from dual;
SYSDATE
-------------------
2012-06-06 15:48:50
SCOTT@ORA11GR2>
3) 当然也可以通过修改参数达到效果（注：参数nls_date_format为静态参数，修改时必须加上scope=spfile子句，重启数据库参数生效）
SCOTT@ORA11GR2> show parameter nls_date_format
NAME TYPE VALUE
--------------------- ----------- ------------------------------
nls_date_format string
SCOTT@ORA11GR2> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.
SCOTT@ORA11GR2> startup force;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SCOTT@ORA11GR2>
SCOTT@ORA11GR2> select sysdate from dual;
SYSDATE
-------------------
2012-06-09 17:15:51
SCOTT@ORA11GR2> show parameter nls_date_format
NAME TYPE VALUE
-------------------------- ------- ------------------------------
nls_date_format string yyyy-mm-dd hh24:mi:ss
SCOTT@ORA11GR2>
1.11.2. SYSTIMESTAMP
带有时区及毫秒的日期格式
SCOTT@ORA11GR2> select systimestamp from dual;
SYSTIMESTAMP
------------------------------------
09-JUN-12 10.20.23.211333 PM +08:00
SCOTT@ORA11GR2>
1.11.3. ADD_MONTHS
语法：ADD_MONTHS(date, integer)
用于从一个日期值增加或减少一些月份
1) 返回系统时间一个月后的日期
SYS@ORA11GR2>select sysdate,add_months(sysdate,1) as "months+1" from dual;
SYSDATE months+1
------------------- -------------------
2012-06-06 14:31:47 2012-07-06 14:31:47
SYS@ORA11GR2>
2) 返回系统时间一年后的日期
SYS@ORA11GR2>select sysdate,add_months(sysdate,12) as "months+12" from dual;
SYSDATE months+12
------------------- -------------------
2012-06-06 14:33:16 2013-06-06 14:33:16
SYS@ORA11GR2>
3) 返回系统时间一年前的日期
SYS@ORA11GR2>select sysdate,add_months(sysdate,-12) as "months-12" from dual;
SYSDATE months-12
------------------- -------------------
2012-06-06 14:33:45 2011-06-06 14:33:45
SYS@ORA11GR2>
1.11.4. LAST_DAY
语法：LAST_DAY(date)
返回包含了日期参数的月份的最后一天的日期
SYS@ORA11GR2> select sysdate, last_day(sysdate) "last" from dual;
SYSDATE last
------------------- -------------------
2012-06-06 18:08:21 2012-06-30 18:08:21
SYS@ORA11GR2>
1.11.5. DBTIMEZONE
返回数据库的时区
SYS@ORA11GR2> select dbtimezone from dual;
DBTIME
------
+08:00
SYS@ORA11GR2>
CREATE DATABASE
db01
...
SET TIME_ZONE='+08:00';
或
alter database set time_zone='+08:00';
时区分东西，东时区都带+号标识，西时区用-号。在一个特定的时间点，时区号越大（考虑正负号）的地方，时间越晚，比如北京在+08:00区, St. Louis在-06:00区，北京已经是傍晚了，St. Louis还在凌晨，它们之前相差即+08:00-(-06:00)=14（受夏时制的影响，可能会有一个小时的误差）。
1.11.6. SESSIONTIMEZONE
返回当前会话的时区
SYS@ORA11GR2> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------
+08:00
SYS@ORA11GR2>
1.11.7. CURRENT_DATE
1) 返回当前会话时区中的当前日期
SYS@ORA11GR2> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SYS@ORA11GR2> col sessiontimezone for a10
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_date from dual;
DBTIME SESSIONTIM CURRENT_DATE
------ ---------- -------------------
+08:00 +08:00 2012-06-06 18:00:46
SYS@ORA11GR2>
2) 将当前会话改为西六区
SYS@ORA11GR2> alter session set time_zone = '-6:0';
Session altered.
SYS@ORA11GR2>
3) 我们发现，CURRENT_DATE时间为会话所在时区的时间
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_date from dual;
DBTIME SESSIONTIM CURRENT_DATE
------ ---------- -------------------
+08:00 -06:00 2012-06-06 04:00:58
SYS@ORA11GR2>
1.11.8. CURRENT_TIMESTAMP
语法：CURRENT_TIMESTAMP [ (precision) ]
Precision：毫秒的精确度，默认为6位毫秒
1) 以timestamp with time zone数据类型返回当前会话时区中的当前日期
SYS@ORA11GR2> col SESSIONTIMEZONE for a15
SYS@ORA11GR2> col CURRENT_TIMESTAMP for a40
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_timestamp from dual;
DBTIME SESSIONTIMEZONE CURRENT_TIMESTAMP
------ --------------- ----------------------------------------
+08:00 +08:00 06-JUN-12 06.14.15.872734 PM +08:00
SYS@ORA11GR2>
2) 将当前会话改为西六区
SYS@ORA11GR2> alter session set time_zone = '-6:0';
Session altered.
SYS@ORA11GR2>
3) 返回为当前会话所在时区的timestamp with time zone时间
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_timestamp from dual;
DBTIME SESSIONTIMEZONE CURRENT_TIMESTAMP
------ --------------- ----------------------------------------
+08:00 -06:00 06-JUN-12 04.14.37.777012 AM -06:00
SYS@ORA11GR2>
4) 我们也可以控制毫秒的位数
SYS@ORA11GR2> select CURRENT_TIMESTAMP(3) from dual;
CURRENT_TIMESTAMP(3)
---------------------------------------------------------------
06-JUN-12 04.22.15.438 AM -06:00
SYS@ORA11GR2> select CURRENT_TIMESTAMP(2) from dual;
CURRENT_TIMESTAMP(2)
---------------------------------------------------------------
06-JUN-12 04.22.22.32 AM -06:00
SYS@ORA11GR2>
5) 巧妙显示为标准日期型
SYS@ORA11GR2> col CURRENT_TIMESTAMP for a35
SYS@ORA11GR2> select current_timestamp,current_timestamp+0 from dual;
CURRENT_TIMESTAMP CURRENT_TIMESTAMP+0
----------------------------------- -------------------
09-JUN-12 07.10.02.835011 PM +08:00 2012-06-09 19:10:02
SYS@ORA11GR2>
1.11.9. LOCALTIMESTAMP
语法：LOCALTIMESTAMP [ (timestamp_precision) ]
1) 返回会话中的日期和时间
SYS@ORA11GR2> col CURRENT_TIMESTAMP for a38
SYS@ORA11GR2> col LOCALTIMESTAMP for a30
SYS@ORA11GR2> select current_timestamp, localtimestamp from dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP
----------------------------------- ------------------------------
06-JUN-12 07.02.07.314525 PM +08:00 06-JUN-12 07.02.07.314525 PM
SYS@ORA11GR2>
2) 将会话改为西六区
SYS@ORA11GR2> alter session set time_zone = '-6:0';
Session altered.
SYS@ORA11GR2>
3) 此时返回的是西六区的会话时间
SYS@ORA11GR2>select current_timestamp, localtimestamp from dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP
----------------------------------- ------------------------------
06-JUN-12 05.02.23.734885 AM -06:00 06-JUN-12 05.02.23.734885 AM
SYS@ORA11GR2>
4) 此函数也可以控制毫秒的位数
SYS@ORA11GR2> select current_timestamp, localtimestamp(3) as "localtimestamp" from dual;
CURRENT_TIMESTAMP localtimestamp
----------------------------------- ------------------------------
06-JUN-12 05.03.03.079213 AM -06:00 06-JUN-12 05.03.03.079 AM
SYS@ORA11GR2>
1.11.10. EXTRACT
语法：
EXTRACT( { { YEAR| MONTH| DAY| HOUR| MINUTE| SECOND}
| { TIMEZONE_HOUR| TIMEZONE_MINUTE}
| { TIMEZONE_REGION| TIMEZONE_ABBR}}
FROM { datetime_value_expression | interval_value_expression}
)
提取日期或时间指定的信息值
1) 返回指定时间的年份
SYS@ORA11GR2> select extract(year from date '2009-5-11') as year from dual;
YEAR
----------
2009
SYS@ORA11GR2>
2) 返回指定时间的月份
SYS@ORA11GR2> select extract(month from date '2009-5-11') as mon from dual;
MON
----------
5
SYS@ORA11GR2>
3) 返回指定时间的日
SYS@ORA11GR2> select extract(day from to_date('2009-5-11','yyyy-mm-dd')) as day from dual;
DAY
----------
11
SYS@ORA11GR2>
4) 返回指定时间的小时
SYS@ORA11GR2>select extract(hour from TIMESTAMP '2009-5-11 08:09:10') as hour from dual;
HOUR
----------
8
SYS@ORA11GR2>
5) 返回指定时间的分钟
SYS@ORA11GR2> select extract(minute from TIMESTAMP '2009-5-11 08:09:10') as minute from dual;
MINUTE
----------
9
SYS@ORA11GR2>
6) 返回指定时间的秒
SYS@ORA11GR2> select extract(second from TIMESTAMP '2009-5-11 08:09:10') as second from dual;
SECOND
----------
10
SYS@ORA11GR2>
1.11.11. MONTHS_BETWEEN
语法：MONTHS_BETWEEN(date1, date2)
判断两个日期之间的月份数量
SYS@ORA11GR2>select months_between(to_date('2012-06-06', 'yyyy-mm-dd'), to_date('2009-05-11', 'yyyy-mm-dd')) "months" from dual;
months
----------
36.8387097
SYS@ORA11GR2>
问：下面SQL是否可以执行，如果不能执行，为什么？如果可以执行，请问什么情况下可以执行？为什么？
select months_between('20110701','20110501') as mb from dual;
可以，因为当前的系统时间格式为：YYYY-MM-DD格式。
1.11.12. NEXT_DAY
语法：NEXT_DAY(date, char)
给定一个日期值，返回由第二个参数指出的日子第一次出现的日期值。
英文字符集：
select NEXT_DAY(trunc(sysdate),'Monday') from dual
中文字符集：
select NEXT_DAY(trunc(sysdate),'星期一') from dual
1) 格式化日期格式
SYS@ORA11GR2>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@ORA11GR2>
2) 2009-05-11之后的第一个星期二
SYS@ORA11GR2>select next_day(to_date('2009-05-11','yyyy-mm-dd'),'tuesday') as "next day" from dual;
next day
-------------------
2009-05-12 00:00:00
SYS@ORA11GR2>
3) 2009-05-11之后的第一个星期五
SYS@ORA11GR2>select next_day('2009-05-11',6) "next day" from dual;
next day
-------------------
2009-05-15 00:00:00
SYS@ORA11GR2>
注：
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1：周日； 2：周一； 3：周二； 4：周三；
5：周四； 6：周五； 7：周六；
1.11.13. TRUNC
语法：trunc(date,fmt)
按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
1) 格式化时间
SYS@ORA11GR2>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@ORA11GR2>
2) trunc函数处理日期,没有fmt参数，默认截取到日
SYS@ORA11GR2>select trunc(sysdate) from dual;
TRUNC(SYSDATE)
-------------------
2012-11-21 00:00:00
SYS@ORA11GR2>
3) 时间截取到小时，截取到分钟
SYS@ORA11GR2>select trunc(sysdate,'hh') "hh",trunc(sysdate,'mi') "hh-mi" from dual;
hh hh-mi
------------------- -------------------
2012-11-21 15:00:00 2012-11-21 15:11:00
SYS@ORA11GR2>
4) 返回本周周一
SYS@ORA11GR2>select trunc(sysdate,'iw') from dual;
TRUNC(SYSDATE,'IW')
-------------------
2012-11-19 00:00:00
SYS@ORA11GR2>
5) 例：返回近10年的周一和周日
select trunc(sysdate - 3650, 'iw') + 7 * rownum monday, trunc(sysdate - 3650, 'iw') +7 * rownum + 6 sunday from dual connect by rownum <= 500;
……请自行测试……
6) 返回指定日期当周的周日
SYS@ORA11GR2>select trunc(to_date('2008.08.08','yyyy.mm.dd'),'d') from dual;
TRUNC(TO_DATE('2008
-------------------
2008-08-03 00:00:00
SYS@ORA11GR2>
7) 返回指定日期当周的周一
SYS@ORA11GR2>select trunc(to_date('2008.08.08','yyyy.mm.dd'),'d')+1 from dual;
TRUNC(TO_DATE('2008
-------------------
2008-08-04 00:00:00
SYS@ORA1
8) 返回指定日期所在月份的第一天
SYS@ORA11GR2>select trunc(to_date('2008.08.08','yyyy.mm.dd'),'mm') from dual;
TRUNC(TO_DATE('2008
-------------------
2008-08-01 00:00:00
SYS@ORA11GR2>
9) 返回指定日期所在月份的上个月的最后一天
SYS@ORA11GR2>select trunc(to_date('2008.08.08','yyyy.mm.dd'),'mm')-1 from dual;
TRUNC(TO_DATE('2008
-------------------
2008-07-31 00:00:00
SYS@ORA11GR2>
10) 返回指定日期所在年份的第一天
SYS@ORA11GR2>select trunc(to_date('2008.08.08','yyyy.mm.dd'),'y') from dual;
TRUNC(TO_DATE('2008
-------------------
2008-01-01 00:00:00
SYS@ORA11GR2>
注：返回指定日期所在年份的前一年的最后一天，只需在这个结果集上-1即可。
1.11.14. ROUND(补)日期四舍五入
月份四舍五入：15号舍去，16号进一；
SYS@ORA11GR2>select round(dd,'MONTH'),
2 round(dd,'YEAR'),
3 trunc(dd,'MONTH'),
4 trunc(dd,'YEAR') from
5 (select to_date('2008-08-15','yyyy-mm-dd') as dd from dual);
ROUND(DD,'MONTH') ROUND(DD,'YEAR') TRUNC(DD,'MONTH') TRUNC(DD,'YEAR')
------------------- ------------------- ------------------- -------------------
2008-08-01 00:00:00 2009-01-01 00:00:00 2008-08-01 00:00:00 2008-01-01 00:00:00
SYS@ORA11GR2>select round(dd,'MONTH'),
2 round(dd,'YEAR'),
3 trunc(dd,'MONTH'),
4 trunc(dd,'YEAR') from
5 (select to_date('2008-08-16','yyyy-mm-dd') as dd from dual);
ROUND(DD,'MONTH') ROUND(DD,'YEAR') TRUNC(DD,'MONTH') TRUNC(DD,'YEAR')
------------------- ------------------- ------------------- -------------------
2008-09-01 00:00:00 2009-01-01 00:00:00 2008-08-01 00:00:00 2008-01-01 00:00:00
SYS@ORA11GR2>
1.11.15. 日期计算
1) 计算两个时间相差的月数（trunc不加参数默认是截取到0位）；
SYS@ORA11GR2> SELECT trunc(months_between(DATE '2003-7-11', DATE '1999-9-1')) MONTHS from dual;
MONTHS
----------
46
SYS@ORA11GR2>
2) 计算两个时间相差的 天数、小时数、分钟数、秒数
SYS@ORA11GR2>select (to_date('20120511', 'yyyymmdd') - to_date('20120401', 'yyyymmdd')) * decode(upper('mi'), 'ss', 24 * 60 * 60, 'mi', 24 * 60, 'hh', 24, 1) as val from dual;
VAL
----------
40
SYS@ORA11GR2>
注：这是一个通用的方法，只需要替换upper中的值就可以计算相差的天数、小时、分钟、秒。
hh：计算返回相差的小时数
mi：计算返回相差的分钟数
ss：计算返回相差的描述
不等于上述三个字符的任意字符都返回相差的天数
3) 给出两个时间，显示这两个时间之间的连续时间
要求：返回2010年12月25日到2011年1月5日之间的连续日期（不包含这两个日期）
SYS@ORA11GR2>select to_date('20101225','yyyymmdd')+rownum from dual connect by rownum <to_date('20110105','yyyymmdd')-to_date('20101225','yyyymmdd');
TO_DATE('20101225',
-------------------
2010-12-26 00:00:00
2010-12-27 00:00:00
2010-12-28 00:00:00
2010-12-29 00:00:00
2010-12-30 00:00:00
2010-12-31 00:00:00
2011-01-01 00:00:00
2011-01-02 00:00:00
2011-01-03 00:00:00
2011-01-04 00:00:00
10 rows selected.
SYS@ORA11GR2>
请问：如果要求包含2010年12月25日和2011年1月5日这两个日期，那么这个SQL语句该如何修改？
4) 指定时间加1天
SYS@ORA11GR2> alter session set nls_date_format='yyyy-mm-dd';
Session altered.
SYS@ORA11GR2>
SYS@ORA11GR2> with t as (select to_date('20120601','yyyymmdd') as dd from dual)
select dd,dd+1 from t;
DD DD+1
---------- ----------
2012-06-01 2012-06-02
SYS@ORA11GR2>
5) 指定时间加1小时（注：一天有24小时，加1/24即为加1小时）
SYS@ORA11GR2> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@ORA11GR2> with t as (select to_date('20120601','yyyymmdd') as dd from dual)
select dd,dd+1/24 from t;
DD DD+1/24
------------------- -------------------
2012-06-01 00:00:00 2012-06-01 01:00:00
SYS@ORA11GR2>
6) 指定时间加5分钟,（注：一天有24小时，一小时有60分钟，那么一天即为24*60=1440分钟，加5/1440即为加5分钟）
SYS@ORA11GR2> with t as (select to_date('20120601','yyyymmdd') as dd from dual)
select dd,dd+5/(24*60) from t;
DD DD+5/(24*60)
------------------- -------------------
2012-06-01 00:00:00 2012-06-01 00:05:00
SYS@ORA11GR2>
请问：下面sql的结果与上面的结果相同吗？
with t as (select to_date('20120601','yyyymmdd') as dd from dual)
select dd,dd+5/24*60 from t;
不一样，因为*和/的优先顺序不同了；
1.12. 隐式转换
1.12.1. 隐式数据类型转换
|
源数据类型 |
目标数据类型 |
|
VARCHAR2 or CHAR |
NUMBER |
|
VARCHAR2 or CHAR |
DATE |
|
NUMBER |
VARCHAR2 |
|
DATE |
VARCHAR2 |
1) 字符型隐式转换为数值型
SCOTT@ORA11GR2>create table t1(x number(10));
Table created.
SCOTT@ORA11GR2>insert into t1 values('1');
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t1;
X
----------
1
SCOTT@ORA11GR2>
2) 字符型隐式转换为日期型
SCOTT@ORA11GR2>create table t2(x date);
Table created.
SCOTT@ORA11GR2>insert into t2 values('2012-12-12');
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t2;
X
-------------------
2012-12-12 00:00:00
SCOTT@ORA11GR2>
3) 数值型隐式转换为字符型
SCOTT@ORA11GR2>create table t3(x varchar2(10));
Table created.
SCOTT@ORA11GR2>insert into t3 values(1);
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t3;
X
----------
1
SCOTT@ORA11GR2>
4) 日期型隐式转换为字符型
SCOTT@ORA11GR2>create table t4(x varchar2(20));
Table created.
SCOTT@ORA11GR2>insert into t4 values(to_date('20121212','yyyymmdd'));
1 row created.
SCOTT@ORA11GR2>commit;
Commit complete.
SCOTT@ORA11GR2>select * from t4;
X
--------------------
2012-12-12 00:00:00
SCOTT@ORA11GR2>
1.12.2. 表达式中数据类型自动转换
|
源数据类型 |
目标数据类型 |
|
VARCHAR2 or CHAR |
NUMBER |
|
VARCHAR2 or CHAR |
DATE |
1) 数值型隐式转换为字符型
SCOTT@ORA11GR2>desc t3
Name Null? Type
----------------------------- -------- ----------------------------
X VARCHAR2(10)
SCOTT@ORA11GR2>select * from t3;
X
----------
1
SCOTT@ORA11GR2>select * from t3 where x=1;
X
----------
1
SCOTT@ORA11GR2>
2) 日期型隐式转换为字符型
SCOTT@ORA11GR2>desc t4
Name Null? Type
----------------------------- -------- ----------------------------
X VARCHAR2(20)
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select * from t4;
X
--------------------
2012-12-12 00:00:00
SCOTT@ORA11GR2>select * from t4 where x<=to_date('2012-12-31','yyyy-mm-dd');
X
--------------------
2012-12-12 00:00:00
SCOTT@ORA11GR2>
备注：虽然我们在测试的时候已经成功的进行了隐式转换，并且成功的将数据写入到表中或者在表达式中进行了隐式转换，不过，都不直观，我们通过查看执行计划，可以清晰的看到隐式转换，如下：
SYSTEM@ORA11GR2>set autot trace exp
SYSTEM@ORA11GR2>select * from scott.t3 where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=1)
Note
-----
- dynamic sampling used for this statement (level=2)
SYSTEM@ORA11GR2>
1.13. 显示转换（补）
1.13.1. TO_CHAR
TO_CHAR日期语法：
TO_CHAR(date, 'format_model')
格式要求：
- 必须包含在单引号中而且大小写敏感。
- 可以包含任意的有效的日期格式。
- 可以使用fm去掉多余的空格或者前导零。
- 与日期值用逗号隔开。
TO_CHAR数值语法：
TO_CHAR(number, 'format_model')
|
9 |
代表一位数字 |
|
0 |
强制显示为零 |
|
$ |
放置一个浮动的美元符号 |
|
L |
采用本地货币符号 |
|
. |
小数点 |
|
, |
千位符 |
1) fm的使用
--去掉空格
SYS@ORA11GR2>select replace(to_char(123.456,'99999.999'),' ','*') as num from dual;
NUM
----------
***123.456
SYS@ORA11GR2>select replace(to_char(123.456,'fm99999.999'),' ','*') as num from dual;
NUM
----------
123.456
SYS@ORA11GR2>
--去掉前导0
SYS@ORA11GR2>select to_char(sysdate,'yyyy-mm-dd') as dd from dual;
DD
----------
2012-09-07
SYS@ORA11GR2>select to_char(sysdate,'fmyyyy-mm-dd') as dd from dual;
DD
----------
2012-9-7
SYS@ORA11GR2>
2) 日期格式转换
--scc或cc显示世纪
SYS@ORA11GR2>select to_char(sysdate,'scc yyyy-mm-dd hh24:mi:ss') sys_date from dual;
SYS_DATE
-----------------------
21 2012-09-08 00:44:53
SYS@ORA11GR2>
--yyyy显示年
SYS@ORA11GR2>col yyyy for a10
SYS@ORA11GR2>select to_char(sysdate,'yyyy') as yyyy from dual;
YYYY
----------
2012
SYS@ORA11GR2>select to_char(sysdate,'y,yyy') as yyyy from dual;
YYYY
-----
2,012
SYS@ORA11GR2>select to_char(sysdate,'yyy') as yyy from dual;
YYY
---
012
SYS@ORA11GR2>select to_char(sysdate,'yy') as yy from dual;
YY
--
12
SYS@ORA11GR2>select to_char(sysdate,'y') as y from dual;
Y
-
2
SYS@ORA11GR2>
--ISO标准的年IYYY,IYY,IY,I,年的4、3、2、1位值
SYS@ORA11GR2>select to_char(sysdate,'iyyy') as iyyy from dual;
IYYY
----
2012
SYS@ORA11GR2>select to_char(sysdate,'iyy') as iyy from dual;
IYY
---
012
SYS@ORA11GR2>select to_char(sysdate,'iy') as iy from dual;
IY
--
12
SYS@ORA11GR2>select to_char(sysdate,'i') as i from dual;
I
-
2
SYS@ORA11GR2>
--year和syear，显示英文拼写的年
SYS@ORA11GR2>select to_char(sysdate,'year') as year from dual;
YEAR
------------------------------------------
twenty twelve
SYS@ORA11GR2>select to_char(sysdate,'syear') as syear from dual;
SYEAR
-------------------------------------------
twenty twelve
SYS@ORA11GR2>
--Q，当前时间为一年的第几季度
SYS@ORA11GR2>select to_char(sysdate,'q') as q,to_char(sysdate,'yyyy-mm-dd') as sdate from dual;
Q SDATE
- ----------
3 2012-09-08
SYS@ORA11GR2>
--mm，显示两位的月份
SYS@ORA11GR2>select to_char(sysdate,'mm') as mm from dual;
MM
--
09
SYS@ORA11GR2>
--month，完整的月份名称
9位字符长度（不足9位填充空格）月的名字
SYS@ORA11GR2>select to_char(sysdate,'month') as month from dual;
MONTH
------------------------------------
september
SYS@ORA11GR2>
SYS@ORA11GR2>select to_char(sysdate-30,'month') from dual;
TO_CHAR(SYSDATE-30,'MONTH')
------------------------------------
august
SYS@ORA11GR2>select length(to_char(sysdate-30,'month')) as len from dual;
LEN
----------
9
SYS@ORA11GR2>
--mon，3位缩写字母的月的名字
SYS@ORA11GR2>select to_char(sysdate,'mon') as mon from dual;
MON
------------
sep
SYS@ORA11GR2>
--ww：一年中的第几周；w：一个月中的第几周
SYS@ORA11GR2>select to_char(sysdate,'ww') as y,to_char(sysdate,'w') as m from dual;
y m
-- -
36 2
SYS@ORA11GR2>
--ddd：一年中的第几天；dd：一个月中的第几天；d：一周中的第几天
SYS@ORA11GR2>select to_char(sysdate,'ddd') as y,to_char(sysdate,'dd') as m,to_char(sysdate,'d') as w from dual;
Y M W
--- -- -
252 08 7
SYS@ORA11GR2>
注：一周中的第几天，按照如下方式排列
1：周日；
2：周一；
3：周二；
4：周三；
5：周四；
6：周五；
7：周六；
--day，输出星期几；9位字符长度（不足9位填充空格）天的名字
SYS@ORA11GR2>select to_char(sysdate,'day') as day from dual;
DAY
------------------------------------
saturday
SYS@ORA11GR2>
--dy，3位字母缩写天的名字
SYS@ORA11GR2>select to_char(sysdate,'dy') as dy from dual;
DY
------------
sat
SYS@ORA11GR2>
--AM或PM，显示上下午标识
SYS@ORA11GR2>
with t as (select to_date('20120908 100000','yyyymmdd hh24miss') as t1,to_date('20120908 180000','yyyymmdd hh24miss') as t2 from dual)
select to_char(t1,'yyyy-mm-dd hh24:mi:ss am') as t1am,to_char(t1,'yyyy-mm-dd hh24:mi:ss pm') as t1pm, to_char(t2,'yyyy-mm-dd hh24:mi:ss am') as t2am,to_char(t2,'yyyy-mm-dd hh24:mi:ss pm') as t2pm from t;
SYS@ORA11GR2>
T1AM T1PM T2AM T2PM
---------------------- ---------------------- ---------------------- ----------------------
2012-09-08 10:00:00 am 2012-09-08 10:00:00 am 2012-09-08 18:00:00 pm 2012-09-08 18:00:00 pm
--hh（天的小时）,hh12（1-12）,hh24（0-23）
SYS@ORA11GR2>with t as (select to_date('20120908 22:22:22','yyyymmdd hh24:mi:ss') as dt from dual) select to_char(dt,'yyyy-mm-dd hh:mi:ss') as hh,to_char(dt,'yyyy-mm-dd hh12:mi:ss') as hh12,to_char(dt,'yyyy-mm-dd hh24:mi:ss') as hh24 from t;
HH HH12 HH24
------------------- ------------------- -------------------
2012-09-08 10:22:22 2012-09-08 10:22:22 2012-09-08 22:22:22
SYS@ORA11GR2>
--ddth，显示序数，如：
SYS@ORA11GR2>select to_char(sysdate,'ddth') as ddth from dual;
DDTH
----
08th
SYS@ORA11GR2>
--ddsp，显示英文日期
SYS@ORA11GR2>select to_char(sysdate,'ddsp') as ddth from dual;
DDTH
------------
eight
--ddspth或ddthsp
SYS@ORA11GR2>select to_char(sysdate,'ddspth') as ddspth,to_char(sysdate,'ddthsp') as ddthsp from dual;
DDSPTH DDTHSP
-------------- --------------
eighth eighth
SYS@ORA11GR2>
3) 数字格式转换
--9:表示一个数字，9的个数决定显示的宽度，如小数点后显示的小于实际数字，那么实际数字按照显示的位数四舍五入
SYS@ORA11GR2>select to_char('123','999999') as val from dual;
VAL
-------
123
SYS@ORA11GR2>
--0:显示前导0，数字的实际位数与要求不符，没有要求的“宽”，那么以0补齐
SYS@ORA11GR2>select to_char('123','0999999') as val from dual;
VAL
--------
0000123
SYS@ORA11GR2>
3）$：显示美元符号
SYS@ORA11GR2>select to_char('123','$999999') as val from dual;
VAL
--------
$123
SYS@ORA11GR2>
4）L:浮动本地货币符号（这个与字符集有关系，如果是中文字符集，那么会显示￥）
SYS@ORA11GR2>select to_char('123','L999999') as val from dual;
VAL
-----------------
$123
SYS@ORA11GR2>
5）.：小数点位置指定,即指定了小数的位置，没有补0.(有的话，四舍五入)
SYS@ORA11GR2>select to_char('123','9999.99') as val from dual;
VAL
--------
123.00
SYS@ORA11GR2>
6）,：显示逗号位置
SYS@ORA11GR2>select to_char('12345','999,999') as val from dual;
VAL
--------
12,345
SYS@ORA11GR2>
7）mi:负号显示在右侧
SYS@ORA11GR2>select to_char('-12345','999999mi') as val from dual;
VAL
-------
12345-
SYS@ORA11GR2>
8）pr：负数变成<>
SYS@ORA11GR2>select to_char('-12345','999999pr') as val from dual;
VAL
--------
<12345>
SYS@ORA11GR2>
9）eeee：科学技术法
SYS@ORA11GR2>select to_char('12345','999999eeee') as val from dual;
VAL
-------
1E+04
SYS@ORA11GR2>
10）v：输入值乘以10的n次方，n次方的n即为v后面9的个数
SYS@ORA11GR2>select to_char('123','99999v99') as val1,123*100 as val2 from dual;
VAL1 VAL2
-------- ----------
12300 12300
SYS@ORA11GR2>
1.13.2. TO_DATE
语法：TO_DATE(char[, 'format_model'])
select
to_date('2011-08-01', 'yyyy-mm-dd') as d1,
to_date('2011.08.01', 'yyyy.mm.dd') as d2,
to_date('2011/08/01', 'yyyy/mm/dd') as d3,
to_date('20110801', 'yyyymmdd') as d4,
to_date('20110801', 'yyyy-mm-dd') as d5,
to_date('8/1', 'MM/DD') as d6,
date '2011-08-01' as d7
from dual;
SELECT to_date('2011-08-01', 'yyyy.mm.dd') FROM dual;
SELECT to_date('2011-08-01', 'yyyymmdd') FROM dual;
SELECT to_date('2011-08-01', 'yyyy/mm.dd') FROM dual;
SELECT to_date('2011.08.01', 'yyyy/mm\dd') FROM dual;
SELECT to_date('20110801', 'yyyy/mm\dd') FROM dual;
1.13.3. TO_NUMBER
语法：TO_NUMBER(char[, 'format_model'])
1.14. 单值通用函数
1.14.1. NVL
语法：NVL(expr1, expr2)
expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
SYS@ORA11GR2> select nvl(null,0),nvl(2,0) from dual;
NVL(NULL,0) NVL(2,0)
----------- ----------
0 2
SYS@ORA11GR2>
1.14.2. NVL2
语法：NVL2(expr1, expr2, expr3)
expr1为NULL，返回expr3；不为NULL，返回expr2。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
SYS@ORA11GR2> select nvl2(null,1,2),nvl2(3,1,2) from dual;
NVL2(NULL,1,2) NVL2(3,1,2)
-------------- -----------
2 1
SYS@ORA11GR2>
下面的例子我们可以看出，expr3参数有个隐式转换为expr2参数类型的动作
SYS@ORA11GR2> select nvl2(null,1,2),nvl2(3,1,'A') from dual;
select nvl2(null,1,2),nvl2(3,1,'A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SCOTT@ORA11GR2>col n1 for 99999
SCOTT@ORA11GR2>col n2 for a5
SCOTT@ORA11GR2>select nvl2(null,1,2) as n1,nvl2(3,'b',1) as n2 from dual;
N1 N2
------ -----
2 b
SCOTT@ORA11GR2>
1.14.3. NULLIF
语法：NULLIF(expr1, expr2)
expr1, expr2相等则返回NULL，不等则返回expr1
SYS@ORA11GR2> select nullif(1,1),nullif(2,1) from dual;
NULLIF(1,1) NULLIF(2,1)
----------- -----------
2
SYS@ORA11GR2>
1.14.4. LNNVL
LNNVL <==> IS NULL OR IS NOT TRUE，即与所筛选条件相反的选项，包括null值。
create table t_null(id number(5) primary key,name varchar2(10),age number(3));
insert into t_null values(1,'zhangsan',20);
insert into t_null values(2,'lisi',21);
insert into t_null values(3,'wangwu',null);
insert into t_null values(4,'zhaoliu',19);
commit;
查询age小于等于20或者age为空的所有人
SCOTT@ORCL>select * from t_null;
ID NAME AGE
---------- ---------- ----------
1 zhangsan 20
2 lisi 21
3 wangwu
4 zhaoliu 19
SCOTT@ORCL>
SCOTT@ORCL>SELECT * FROM t_null WHERE age <= 20 or age is null;
ID NAME AGE
---------- ---------- ----------
1 zhangsan 20
3 wangwu
4 zhaoliu 19
SCOTT@ORCL>
SCOTT@ORCL>SELECT * FROM t_null WHERE lnnvl(age > 20);
ID NAME AGE
---------- ---------- ----------
1 zhangsan 20
3 wangwu
4 zhaoliu 19
SCOTT@ORCL>
1.14.5. COALESCE
语法：COALESCE(expr1 , expr2 ... exprN)
返回expr1,expr2….,exprN中,第一个非null值
SCOTT@ORCL> select COALESCE(null,1,null,2) from dual;
COALESCE(NULL,1,NULL,2)
-----------------------
1
SCOTT@ORCL>
1.15. 条件表达式
1.15.1. CASE WHEN END
语法：
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select 1 as sno,60 as score from dual union all
select 2,70 from dual union all
select 3,59 from dual)
select sno,score,case when score>=60 then 'Congratulations' else 'Thank you' end as ocm from t;
SNO SCORE OCM
---------- ---------- ---------------
1 60 Congratulations
2 70 Congratulations
3 59 Thank you
SCOTT@ORCL>
例：薪水小于等于1000的增加40%，薪水大于1000小于等于2000的增加30%，大于2000小于等于3000的增加20%，3000以上的增加10%
SCOTT@ORCL> SELECT ename, sal, CASE
WHEN sal <= 1000 THEN
sal + sal * 0.4
WHEN sal > 1000 AND sal <= 2000 THEN
sal + sal * 0.3
WHEN sal > 2000 AND sal <= 3000 THEN
sal + sal * 0.2
ELSE
sal + sal * 0.1
END new_sal
FROM emp
ORDER BY sal;
ENAME SAL NEW_SAL
---------- ---------- ----------
SMITH 800 1120
JAMES 950 1330
ADAMS 1100 1430
WARD 1250 1625
MARTIN 1250 1625
MILLER 1300 1690
TURNER 1500 1950
ALLEN 1600 2080
CLARK 2450 2940
BLAKE 2850 3420
JONES 2975 3570
SCOTT 3000 3600
FORD 3000 3600
KING 5000 5500
14 rows selected.
示例hr用于下：
SQL> select first_name,salary,
2 case when salary<=1000 then salary*1.4
3 when salary >1000 and salary <=2000 then salary*1.3
4 when salary >2000 and salary <=3000 then salary*1.2
5 else salary*1.1
6 end as jiaxin from employees;
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Donald 2600 3120
Douglas 2600 3120
Jennifer 4400 4840
Michael 13000 14300
Pat 6000 6600
Susan 6500 7150
Hermann 10000 11000
Shelley 12008 13208.8
William 8300 9130
Steven 24000 26400
Neena 17000 18700
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Lex 17000 18700
Alexander 9000 9900
Bruce 6000 6600
David 4800 5280
Valli 4800 5280
Diana 4200 4620
Nancy 12008 13208.8
Daniel 9000 9900
John 8200 9020
Ismael 7700 8470
Jose Manuel 7800 8580
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Luis 6900 7590
Den 11000 12100
Alexander 3100 3410
Shelli 2900 3480
Sigal 2800 3360
Guy 2600 3120
Karen 2500 3000
Matthew 8000 8800
Adam 8200 9020
Payam 7900 8690
Shanta 6500 7150
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Kevin 5800 6380
Julia 3200 3520
Irene 2700 3240
James 2400 2880
Steven 2200 2640
Laura 3300 3630
Mozhe 2800 3360
James 2500 3000
TJ 2100 2520
Jason 3300 3630
Michael 2900 3480
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Ki 2400 2880
Hazel 2200 2640
Renske 3600 3960
Stephen 3200 3520
John 2700 3240
Joshua 2500 3000
Trenna 3500 3850
Curtis 3100 3410
Randall 2600 3120
Peter 2500 3000
John 14000 15400
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Karen 13500 14850
Alberto 12000 13200
Gerald 11000 12100
Eleni 10500 11550
Peter 10000 11000
David 9500 10450
Peter 9000 9900
Christopher 8000 8800
Nanette 7500 8250
Oliver 7000 7700
Janette 10000 11000
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Patrick 9500 10450
Allan 9000 9900
Lindsey 8000 8800
Louise 7500 8250
Sarath 7000 7700
Clara 10500 11550
Danielle 9500 10450
Mattea 7200 7920
David 6800 7480
Sundar 6400 7040
Amit 6200 6820
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Lisa 11500 12650
Harrison 10000 11000
Tayler 9600 10560
William 7400 8140
Elizabeth 7300 8030
Sundita 6100 6710
Ellen 11000 12100
Alyssa 8800 9680
Jonathon 8600 9460
Jack 8400 9240
Kimberely 7000 7700
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Charles 6200 6820
Winston 3200 3520
Jean 3100 3410
Martha 2500 3000
Girard 2800 3360
Nandita 4200 4620
Alexis 4100 4510
Julia 3400 3740
Anthony 3000 3600
Kelly 3800 4180
Jennifer 3600 3960
FIRST_NAME SALARY JIAXIN
-------------------- ---------- ----------
Timothy 2900 3480
Randall 2500 3000
Sarah 4000 4400
Britney 3900 4290
Samuel 3200 3520
Vance 2800 3360
Alana 3100 3410
Kevin 3000 3600
107 rows selected.
1.15.2. DECODE(如果，那么)
语法：
DECODE(expr, search, result[, search, result ]...[, default ])
示例：
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
输出为：
select decode(rownum,1,2,mod(rownum,2)) as gender from dual connect by rownum<10)
select gender as sex,decode(gender,0,'M',1,'F','U') as gender from t;
SEX GENDER
---------- ------
2 U
0 M
1 F
0 M
1 F
0 M
1 F
0 M
1 F
9 rows selected.
SYS@ORA11GR2>
例1：巧用decode解决条件判断问题（注：因为decode只能用于判断相等的值，无法判断不等条件，此例是利用sign这个函数来迂回解决判断问题）
select 1 as sno,60 as score from dual union all
select 2,70 from dual union all
select 3,59 from dual)
select sno,score,decode(sign(score-60),-1,'Thank you','Congratulations') as ocm from t;
SNO SCORE OCM
---------- ---------- ---------------
1 60 Congratulations
2 70 Congratulations
3 59 Thank you
SYS@ORA11GR2>
例2：利用decode统计数量
SYS@ORA11GR2> with t as(
select mod(rownum,2) as type,mod(rownum,3) as state from dual connect by rownum <=33)
select type,count(*) as num,
sum(decode(state,0,1,0)) as s1,
sum(decode(state,1,1,0)) as s2,
sum(decode(state,2,1,0)) as s3
from t group by type;
TYPE NUM S1 S2 S3
---------- ---------- ---------- ---------- ----------
1 17 6 6 5
0 16 5 5 6
SYS@ORA11GR2>
1.16. 其它
1.16.1. USERENV
语法：USERENV('parameter')
1) ISDBA 查看当前用户是否是DBA如果是则返回true
SYS@ORA11GR2> select userenv('isdba') from dual;
USEREN
------
TRUE
SYS@ORA11GR2>
2) SESSION 返回会话标志
SYS@ORA11GR2> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
4294967295
SYS@ORA11GR2>
3) ENTRYID 返回会话人口标志
SYS@ORA11GR2> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
SYS@ORA11GR2>
4) INSTANCE 返回当前INSTANCE的标志
SYS@ORA11GR2> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
SYS@ORA11GR2>
5) LANGUAGE 返回当前环境变量
SYS@ORA11GR2> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII
SYS@ORA11GR2>
6) LANG 返回当前环境的语言的缩写
SYS@ORA11GR2> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
US
SYS@ORA11GR2>
7) TERMINAL 返回用户的终端或机器的标志
SYS@ORA11GR2> select userenv('terminal') from dual;
USERENV('TERMINAL')
------------------------------
XXF-PC
SYS@ORA11GR2>