Oracle SQL
Oracle SQL
文中所有代码均在 Oracle 11gR2 数据库中运行通过。使用的表为 Oracle 的 ”Sample Schemas“ 的 "HR" 模式中的表。
SELECT
语法
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
包含 SELECT 子句和 FROM 子句：
- SELECT 子句列出要选择的列，其中 * 表示选择表中的所有列，DISTINCT 关键字表示去掉指定列重复的行，多列之间使用逗号隔开，也可以使用表达式，可以为列或者表达式指定一个别名。
- FROM 子句指明这些列来自于哪张表。
例子：使用 * 选择所有表中列
select * from departments;
例子：使用列名字选择指定的列
select department_id,location_id from departments;
规范
- SQL 语句不区分大小写。
- SQL 语句可以写在一行，也可以跨行。
- 关键字不能缩写及跨行。
- 子句一般单独一行。
- 可以使用缩进提高可读性。
- 在 SQL*PLUS 中使用分号作为语句的结束。
算术表达式
例子：使用 +
select last_name,salary,salary+300 from employees;
例子：使用 * 和 +
select last_name,salary,12*salary+100 from employees;
例子：使用括号改变运算优先级
select last_name,salary,12*(salary+100) from employees;
NULL
NULL 表示一个未知的值，既不是零也不是空格。
例子：查看人员表中的提成，其中有的人的提成为 NULL
select last_name,job_id,salary,commission_pct from employees;
NULL 具有传染性，也就是说 NULL 和任何值进行运算都为 NULL。
例子：使用包含 NULL 的提成字段进行算术运算，结果也是 NULL
select last_name,12*salary*commission_pct from employees;
列别名
- 前面的 SQL 语句一个列的名字为 12*SALARY*COMMISSION_PCT，很长，可以使用一个有意义的简短别名来替代他，更易读。
- 可以直接在列名字后面加上别名，也可以使用 AS 关键字。
例子：使用 AS 关键字加列别名以及直接加上列别名
select last_name as name,commission_pct comm from employees;
别名和列名一样，默认都是以大写显示，如果别名包含空格，特殊字符或者不想使用大写显示，就需要加上双引号。
例子：加上双引号的别名
select last_name "Name",salary*12 "Annual Salary" from employees;
连接操作符
连接操作符使用两个竖杠表示，可以将列或者字符串与其他列连接起来，可以把多个字段连接成一个字段来显示。
例子：2 个字段连接成 1 个字段显示
select last_name||job_id as "Employees" from employees;
例子：使用单引号将字符串引起来，每一行都会显示一次这个字符串
select last_name||' is a '||job_id as "Employee Details" from employees;
例子：把人员表某些数据转换成 insert 语句，拷贝到其他数据库去执行，满足临时少量数据迁移的需求
select 'insert into employee(employee_id,last_name) values(' || employee_id ||
',''' || last_name || ''');' as ttt
from employees;
如果要显示字符串里面的单引号，那就使用两个单引号，也可以使用 q 操作符，同时还需要加上分隔符号，可以是方框，问号，小括号，但是必须要配对。
例子：使用 q 操作符显示字符串里面的单引号
select department_name || q'[,it's assigned Manager Id: ]' || manager_id AS "Department and Manager"
from departments;
DISTINCT
使用 SELECT 语句查询的结果默认显示所有的行，包括重复的行，可以使用 DISTINCT 关键字去重。
例子：对比不使用与使用 DISTINCT 的结果
select department_id from employees;
select distinct department_id from employees;
例子：DISTINCT 后面跟多个字段，表示多个字段联合起来唯一
select distinct department_id,manager_id from employees;
WHERE
语法
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
WHERE 子句必须在 FROM 子句之后。
例子：只查询部门编号为 90 的人员信息
select employee_id,last_name,job_id,department_id from employees where department_id=90;
如果要限制字符类型，日期类型，必须要用单引号引起来，而且单引号里面的字符区分大小写，日期要按照指定的格式书写，默认的的格式是 DD-MON-RR 格式。
例子：查询名字为 Whalen 的人员信息
select last_name,job_id,department_id from employees where last_name='Whalen';
例子：查询入职日期为 23-MAY-06 的人员信息
select last_name,hire_date from employees where hire_date='23-MAY-06';
这里实际上做了一个隐式转换，将字符串转换成日期类型，格式不一致的话，就会报错。
例子：如果格式修改为中国习惯的 YYYY-MM-DD，则会报错
select last_name,hire_date from employees where hire_date='2006-05-23';
比较条件
| Operator | Meaning |
|---|---|
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> | Not equal to |
| BETWEEN...AND... | Between two values (inclusive) |
| IN(set) | Match any of a list of values |
| LIKE | Match a character pattern |
| IS NULL | Is a null value |
例子：查找薪水小于等于 3000 的人员
select last_name,salary from employees where salary<=3000;
例子：查找薪水在 2500 到 3000 之间，包含 2500 和 3000 的人员
select last_name,salary from employees where salary between 2500 and 3500;
例子：查找被管理 ID 为 100、101 及 102 管理者管理的人员信息
select employee_id,last_name,salary,manager_id
from employees
where manager_id in (100,101,201);
使用 LIKE 进行通配符搜索，Oracle 中的通配符有 2 种：
- %：百分号表示 0 或者多个字符
- _：下划线表示一个字符
例子：查找员工的姓中第一个字母为 S 的员工信息
select first_name from employees where first_name like 'S%';
例子：查找员工的名字中第二个字母为 o 的员工信息
select last_name from employees where last_name like '_o%';
如果要搜索包含 % 或者 _ 的字段值，则需要使用 ESCAPE 关键字进行转义
例子：查询职位中包含_的人员信息
select employee_id,job_id from employees where job_id like 'SH\_%' escape '\';
测试是否为 NULL 必须使用 IS NULL 或者 IS NOT NULL，不能使用等号
例子：查找管理 ID 为空的人员信息
select last_name,manager_id from employees where manager_id is null;
逻辑条件
| Operator | Meaning |
|---|---|
| AND | Returns TRUE if both component conditions are true |
| OR | Returns TRUE if either component condition is true |
| NOT | Returns TRUE if the following condition is false |
例子：查找薪水大于等于 10000 而且职位包含 MAN 的人员信息
select employee_id,last_name,job_id,salary
from employees
where salary>=10000 and job_id like '%MAN%';
例子：查找薪水大于等于 10000 或者职位包含 MAN 的人员信息
select employee_id,last_name,job_id,salary
from employees
where salary>=10000 or job_id like '%MAN%';
例子：查找职位不是 'IT_PROG', 'ST_CLERK', 'SA_REP' 这三个的人员信息
select last_name,job_id
from employees
where job_id not in ('IT_PROG','ST_CLERK','SA_REP');
运算优先级
Oracle 中的运算优先级如下表：
| Operator | Meaning |
|---|---|
| 1 | Arithmetic operators |
| 2 | Concatenation operator |
| 3 | Comparison conditions |
| 4 | IS [NOT] NULL, LIKE, [NOT] IN |
| 5 | [NOT] BETWEEN |
| 6 | Not equal to |
| 7 | NOT logical condition |
| 8 | AND logical condition |
| 9 | OR logical condition |
可以使用括号来改变优先级。
例子：查找薪水大于 15000 而且职位为 'AD_PRES' 的人员信息以及职位为 'SA_REP' 的人员信息
select last_name,job_id,salary
from employees
where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;
例子：查找薪水大于 15000 而且职位为 'SA_REP' 或 'AD_PRES' 的人员信息
select last_name,job_id,salary
from employees
where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000;
ORDER BY
语法
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];
SELECT 语句的结果默认是没有排序的，我们可以使用 ORDER BY 子句对指定字段进行排序，ORDER BY 子句位于 SELECT 语句的最后，使用如下 2 个关键字进行排序
- ASC：升序，默认
- DESC：降序
例子：查找人员信息，按照入职时间升序排序
select last_name,job_id,department_id,hire_date from employees order by hire_date;
例子：查找人员信息，按照入职时间降序排序
select last_name,job_id,department_id,hire_date from employees order by hire_date desc;
ORDER BY 除了可以指定字段进行排序，还可以使用字段的别名以及字段的位置。
例子：使用字段的别名对人员年薪进行排序
select employee_id,last_name,salary*12 annsal from employees order by annsal;
例子：使用字段的位置对人员年薪进行排序
select employee_id,last_name,salary*12 annsal from employees order by 3;
ORDER BY 可以使用多个字段进行排序，但是 ASC，DESC 关键字只对其前面的一个字段有效，如果字段后面没有加上关键字，默认是 ASC 升序。
例子：使用部门编号进行升序排列，部门编号相同的再使用薪水进行降序排列来显示人员信息
select last_name,department_id,salary from employees order by department_id,salary desc;
Single-Row Functions
根据函数每次处理的对象是一行还是多行，分为单行函数和多行函数，如果每次处理一行，是单行函数，如果每次处理是多行，就是多行函数，本章主要讲单行函数，包括字符函数，数字函数及日期函数等。
字符函数
字符函数分两类：
- 大小写转换函数
- 字符操作函数
大小写转换函数
| Function | Result |
|---|---|
| LOWER('SQL Course') | sql course |
| UPPER('SQL Course') | SQL COURSE |
| INITCAP('SQL Course') | Sql Course |
例子：查询名字为 Higgins 的人员信息
select employee_id,last_name,department_id from employees where last_name='higgins';
由于单引号内是区分大小写的，故与 Higgins 不匹配，没有结果。
使用 LOWER 函数进行转换就可以匹配了。
select employee_id,last_name,department_id
from employees
where lower(last_name)='higgins';
字符操作函数
| Function | Result |
|---|---|
| CONCAT('Hello', 'World') | HelloWorld |
| SUBSTR('HelloWorld',1,5) | Hello |
| LENGTH('HelloWorld') | 10 |
| INSTR('HelloWorld', 'W') | 6 |
| LPAD(salary,10,'*') | *****24000 |
| RPAD(salary, 10, '*') | 24000***** |
| REPLACE('JACK and JUE','J','BL') | BLACK and BLUE |
| TRIM('H' FROM 'HelloWorld') | elloWorld |
例子：从人员表中查找职位第 4 个字母到最后为 'REP' 的人员，将他们的姓名显示在一列中，还要显示名字的长度以及名字中字母 'a' 的位置
select employee_id,
concat(first_name, last_name) name,
job_id,
length(last_name),
instr(last_name, 'a') "Contains 'a'?"
from employees
where substr(job_id, 4) = 'REP';
数字函数
| Function | Result |
|---|---|
| ROUND(45.926, 2) | 45.93 |
| TRUNC(45.926, 2) | 45.92 |
| MOD(1600, 300) | 100 |
例子：使用 ROUND 函数，四舍五入
select round(45.923,2),round(45.923,0),round(45.923,-1) from dual;
这里引入了一个 DUAL 表，他是一个单行单列的虚拟表，基本上 Oracle 引入 DUAL 为的就是符合语法，主要用来选择系统变量或求一个表达式的值。
例子：使用 TRUNC 函数，去掉指定的位数
select trunc(45.923,2),trunc(45.923),trunc(45.923,-1) from dual;
例子：使用 MOD 函数，求模
select last_name,salary,mod(salary,5000) from employees where job_id='SA_REP';
日期函数
Oracle 数据库默认存储世纪、年月日、时分秒，默认的日期格式是 DD-MON-RR，RR 表示的是世纪年，可以在 21 世纪存储 20 世纪的日期，在 20 世纪存储 21 世纪的日期。
SYSDATE
例子：取服务器时间
select sysdate from dual;
算术运算
- 一个日期增加一个数字或者减去一个数字，结果为一个日期
- 两个日期相减结果为之间的天数
- 通过除以 24 的方式为一天加上小时，如果是分钟，就再除以 60
例子：查询员工至今入职多少周
select last_name,(sysdate-hire_date)/7 as weeks from employees where department_id=90;
日期函数
| Function | Result |
|---|---|
| MONTHS_BETWEEN | Number of months between two dates |
| ADD_MONTHS | Add calendar months to date |
| NEXT_DAY | Next day of the date specified |
| LAST_DAY | Last day of the month |
| ROUND | Round date |
| TRUNC | Truncate date |
| Function | Result |
|---|---|
| MONTHS_BETWEEN('01-SEP-95','11-JAN-94') | 19.6774194 |
| ADD_MONTHS('11-JAN-94',6) | '11-JUL-94' |
| NEXT_DAY('01-SEP-95','FRIDAY') | '08-SEP-95' |
| LAST_DAY('01-FEB-95') | '28-FEB-95' |
例子：查看下一个星期天是几号
select next_day(sysdate,1) from dual;
select next_day(sysdate,'sunday') from dual;
假设：SYSDATE = '25-JUL-03':
| Function | Result |
|---|---|
| ROUND(SYSDATE,'MONTH') | 1-Aug-03 |
| ROUND(SYSDATE ,'YEAR') | 1-Jan-04 |
| TRUNC(SYSDATE ,'MONTH') | 1-Jul-03 |
| TRUNC(SYSDATE ,'YEAR') | 1-Jan-03 |
例子：对 15 号这一天使用 ROUND 函数
select round(to_date('2014-04-15 23:59:59','yyyy-mm-dd hh24:mi:ss'),'month') from dual;
转换函数
使用 TO_CHAR 函数将数字或者日期转换成字符，使用 TO_NUMBER 函数将字符转换成数字，使用 TO_DATE 函数将字符转换成日期。
TO_CHAR
- 使用 TO_CHAR 函数将日期转换成字符
TO_CHAR(date, 'format_model')
格式串规则：
- 格式串需要使用单引号引起来
- 格式串大小写敏感
- 可以使用任何有效的日期格式元素
- 可以使用 fm 消除前面的空格和前导 0
- 格式串和前面的日期用逗号分割
日期元素格式：
| Element | Result |
|---|---|
| YYYY | Full year in numbers |
| YEAR | Year spelled out (in English) |
| MM | Two-digit value for month |
| MONTH | Full name of the month |
| MON | Three-letter abbreviation of the month |
| DY | Three-letter abbreviation of the day of the week |
| DAY | Full name of the day of the week |
| DD | Numeric day of the month |
例子：将员工的入职时间格式化显示
select last_name,to_char(hire_date,'fmDD Month YYYY') as hiredate from employees;
- 使用 TO_CHAR 函数将数字转换成字符
TO_CHAR(number, 'format_model')
数字格式元素：
| Element | Result |
|---|---|
| 9 | Represents a number |
| 0 | Forces a zero to be displayed |
| $ | Places a floating dollar sign |
| L | Uses the floating local currency symbol |
| . | Prints a decimal point |
| , | Prints a comma as thousands indicator |
例子：将薪水转换成字符显示，单位是美元
select to_char(salary,'$99,999.00') salary from employees where last_name='Ernst';
TO_NUMBER
TO_NUMBER(char[, 'format_model'])
例子：将 16 进制 '1e' 转换成 10 进制
select to_number('1e','xx') from dual;
TO_DATE
TO_DATE(char[, 'format_model'])
例子：将字符串转换成日期
select to_date('2014-04-27','yyyy-mm-dd') from dual;
RR 日期格式
使用 RR 日期格式就是使用两位数字来表示世纪年，可以在 21 世纪存储 20 世纪的日期，在 20 世纪存储 21 世纪的日期。
例子：使用 RR 格式，获取入职时间小于 2006 年的人员
select last_name,to_char(hire_date,'DD-Mon-YYYY')
from employees
where hire_date<to_date('01-Jan-06','DD-Mon-RR');
例子：对比 YY 和 RR 格式
select to_char(to_date('27-apr-88','dd-mon-yy'),'yyyy-mm-dd') from dual;
select to_char(to_date('27-apr-88','dd-mon-rr'),'yyyy-mm-dd') from dual;
函数嵌套
单行函数可以嵌套层数不限，从最里面的函数计算起。
例子：取名字前 8 个字符并与 '_US' 拼接，大写显示
select last_name,upper(concat(substr(last_name,1,8),'_US'))
from employees
where department_id=60;
NULL 函数
- NVL (expr1, expr2)：如果第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
- NVL2 (expr1, expr2, expr3)：如果第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值。
- NULLIF (expr1, expr2)：如果 exp1 和 exp2 相等则返回空 (NULL)，否则返回第一个值。
- COALESCE (expr1, expr2, ..., exprn)：如果第一个参数为空，则看第二个参数是否是空，不为空则显示第二个参数，如果第二个参数是空再看第三个参数是否为空，不为空则显示第三个参数，依次类推。
以上函数参数的数据类型必须匹配。
例子：使用 NVL 函数将提成为空的转换成提成为 0
select last_name,
salary,
nvl(commission_pct, 0),
(salary * 12) + (salary * 12 * nvl(commission_pct, 0)) as sal
from employees;
例子：使用 NVL2 函数，如果提成为 NULL，显示 SAL，如果提成不为 NULL，则显示 SAL+COMM
select last_name,salary,commission_pct,nvl2(commission_pct,'SAL+COMM','SAL') income
from employees
where department_id in (50,80);
例子：使用 NULLIF 函数，如果姓和名的长度相等，则返回 NULL，不相等，则返回姓的长度
select first_name,
length(first_name) "expr1",
last_name,
length(last_name) "expr2",
nullif(length(first_name), length(last_name)) result
from employees;
例子：使用 COALESCE 函数，如果 MANAGER_ID 为 NULL，则显示 COMMISSION_PCT 的值，如果 COMMISSION_PCT 也为 NULL，则显示-1
select last_name, coalesce(manager_id, commission_pct, -1) comm
from employees
order by commission_pct;
条件表达式
Oracle 有 2 种方式实现条件表达式
- CASE 语句
- DECODE 函数
CASE
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
注意：
- 寻找 WHEN 的优先级：从上到下
- 再多的 WHEN，也只有一个出口，即其中有一个满足了 expr 就马上退出 CASE
- 不能把 return_expr 和 else_expr 指定为 NULL
例子：根据不同的职位加不同比例的薪水
select last_name,
job_id,
salary,
case job_id
when 'IT_PROG' then
1.10 * salary
when 'ST_CLERK' then
1.15 * salary
when 'SA_REP' then
1.20 * salary
else
salary
end "revised_salary"
from employees;
DECODE
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, 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) as revised_salary
from employees;
例子：显示部门编号为 80 的人员的税率
select last_name,
salary,
decode(trunc(salary / 2000, 0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) as tax_rate
from employees
where department_id = 80;
Group Functions
组函数
语法:
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
常用组函数：
| Function | Description |
|---|---|
| AVG([DISTINCT|ALL]n) | Average value of n, ignoring null values |
| COUNT({*|[DISTINCT|ALL]}expr) | Number of rows, where expr evaluates to something other than null(count all selected rows using *, including duplicates and rows with nulls) |
| MAX([DISTINCT|ALL]expr) | Maximum value of expr, ignoring null values |
| MIN([DISTINCT|ALL]expr) | Minimum value of expr, ignoring null values |
| SUM([DISTINCT|ALL]n) | Sum values of n, ignoring null values |
- AVG 和 SUM 只能用于数字类型。
- MIN 和 MAX 可以用于数字类型，字符类型及日期类型。
例子：查询 JOB_ID 包含 'REP' 的员工的平均薪水，最大薪水，最小薪水及总和
select avg(salary),max(salary),min(salary),sum(salary)
from employees
where job_id like '%REP%';
例子：查询最早入职和最晚入职的日期
select min(hire_date),max(hire_date) from employees;
COUNT(*) 返回表的总行数，包含重复的。
例子：查询在部门 50 的人数
select count(*) from employees where department_id=50;
也可以使用非空的字符来表示，不能使用 NULL。
select count(1) from employees where department_id=50;
COUNT(expr) 返回不包含 NULL 的行数。
例子：查询在部门 80 中有提成的人数
select count(commission_pct) from employees where department_id=80;
COUNT(DISTINCT expr) 返回不重复及非空的行数。
例子：查询人员表中的部门数量
select count(distinct department_id) from employees;
例子：查询有提成人员的平均提成（组函数是默认忽略 NULL 的）
select avg(commission_pct) from employees;
例子：查询公司所有人员的平均提成
select avg(nvl(commission_pct,0)) from employees;
GROUP BY
语法：
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
SELECT 子句中不在组函数当中的字段必须也要出现在 GROUP BY 子句。
例子：查询每个部门的平均薪水
select department_id,avg(salary) from employees group by department_id;
例子：查询每个部门下每种职位的总薪水
select department_id dept_id,job_id,sum(salary)
from employees
group by department_id,job_id;
HAVING
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
例子：查询每个部门的最大薪水，并显示大于 10000 的
select department_id,max(salary)
from employees
group by department_id
having max(salary)>10000;
例子：查询除了职位包含 'REP' 的每种职位的合计薪水，并显示合计薪水大于 13000 的，按照合计薪水从低到高排序
select job_id,sum(salary)
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary)>13000
order by sum(salary);
JOIN
语法
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];
自然连接
- 两个表有相同列名才能创建自然连接，且不用添加连接条件
- 把相同列名值相等的记录连接起来，有多列相同的话，都会被连接起来
- 列名相同，但是类型不同，连接时会报错
例子：查询部门编号、部门名称、部门所在位置及城市
select department_id,department_name,location_id,city
from departments natural join locations;
USING 子句
- 当 2 个表有多列可以进行连接匹配的时候，可以使用 USING 子句指定某一列进行匹配，可以用于列名相同但类型不同的列
- 在 USING 子句中指定的公共列，在整个语句中都不能使用表名或者表别名前缀进行限定，但是可以使用列别名
- 没有在 USING 子句中指定，但是两个表都有这个列，就必须要加上表名限定，同时最好使用列别名以便对结果进行区分
- 如果列只出现在一个表中，表名限定可加可不加，但是如果加上，可以减少解析，提高性能
- 使用 USING 子句创建的连接为内连接，不是自然连接，不能和自然连接同时使用
例子：查询人员的编号、姓名、位置编号及部门编号，仅使用部门编号进行连接（不使用 MANAGER_ID 进行连接）
select employees.employee_id,employees.last_name,departments.location_id,department_id
from employees join departments using (department_id);
例子：使用表别名简化书写，提高性能
select e.employee_id,e.last_name,d.location_id,department_id
from employees e join departments d using(department_id);
ON 子句
- 用于进行不同列名的连接，即使类型不同，也可以使用
- 平时用得更多，也更容易理解
- ON 子句中的列，在整个语句中必须使用表名或者表别名前缀进行限定
- 后面可以使用 AND 或者 WHERE 进行条件限定
例子：使用 ON 子句修改上面的例子
select e.employee_id,e.last_name,d.location_id,e.department_id,d.department_id
from employees e join departments d on(e.department_id=d.department_id);
自连接就是将一张表当成多张表进行连接，以人员表为例，每一条记录里面有员工编号及对应的经理编号，如果想要查询员工名字及对应的经理名字，那么就要使用自连接，先将人员表作为工作人员表，取出员工的名字及其经理的编号（外键），再将人员表作为经理人员表，与其员工编号（主键）进行关联，就可以找到经理名字了。
例子：查询员工名字及其经理的名字
select e.last_name emp, e.manager_id, m.employee_id, m.last_name mgr
from employees e
join employees m
on (e.manager_id = m.employee_id);
例子：查询部门编号为 50 的人员信息和部门信息
select e.employee_id, e.last_name, e.department_id, d.department_id
from employees e
join departments d
on (e.department_id = d.department_id)
and d.department_id = 50;
例子：使用 WHERE 改写上面语句
select e.employee_id, e.last_name, e.department_id, d.department_id
from employees e
join departments d
on (e.department_id = d.department_id)
where d.department_id = 50;
例子：查询人员编号，所在城市及部门名称
select employee_id, city, department_name
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
select employee_id, city, department_name
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
不等连接
例子：查询人员的薪水等级
create table job_grades(
grade_level char(1),
lowest_sal number(10),
highest_sal number(10));
insert into job_grades values('A',1000,2999);
insert into job_grades values('B',3000,5999);
insert into job_grades values('C',6000,9999);
insert into job_grades values('D',10000,14999);
insert into job_grades values('E',15000,24999);
insert into job_grades values('F',25000,40000);
commit;
select * from job_grades;
select e.last_name, e.salary, j.grade_level
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
外连接
- 在 SQL99 标准里面，两张表进行连接，只返回两个字段相匹配的记录，叫 INNER JOIN
- 两张表进行连接，除了 INNER JOIN 的结果，不匹配的也要取出来，根据方向，如果以左边为主，叫左连接，如果以右边为主，叫右连接，如果左边右边都要取出来，我们叫全外连接
左连接
例子：查询所有人员的部门编号及部门名称，即使该人员不属于任何部门
select e.last_name, e.department_id, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
右连接
例子：查询所有部门的部门编号、部门名称及对应的人员，即使该部门没有任何人员
select e.last_name, e.department_id, d.department_name
from employees e
right join departments d
on e.department_id = d.department_id;
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
全外连接
例子：查询所有人员对应的部门以及所有部门对应的人员
select e.last_name, e.department_id, d.department_name
from employees e
full join departments d
on e.department_id = d.department_id;
交叉连接
例子：交叉连接
select last_name,department_name from employees cross join departments;
select employee_id,last_name,department_name from employees,departments;
Subqueries
语法
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
- 主查询执行前，子查询先执行，而且只执行一次
- 主查询使用子查询的结果
例子：查询薪水比 'Abel' 高的人员的名字和薪水
select last_name, salary
from employees
where salary > (select salary from employees where last_name = 'Abel');
使用子查询的注意事项：
- 子查询需要使用括号括起来
- 子查询一般放在比较条件的右边
- 只有在进行 Top-N 分析的时候，子查询才可以使用 ORDER BY 进行排序，其他情况都不行
- 对单行子查询使用单行操作符，对多行子查询使用多行操作符
例子：查找薪水最大的 10 名员工，在进行 Top-N 分析的时候，子查询才可以使用 ORDER BY 进行排序
select *
from (select last_name, salary from employees order by 2 desc)
where rownum < 11;
单行子查询
单行子查询，只返回一行结果，单行子查询比较操作符：
| Operator | Meaning |
|---|---|
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> | Not equal to |
例子：查找 JOB_ID 与 EMPLOYEE_ID 为 141 号员工相同，薪水比 143 号员工大的人员
select last_name, job_id, salary
from employees
where job_id = (select job_id from employees where employee_id = 141)
and salary > (select salary from employees where employee_id = 143);
在子查询里面使用组函数。
例子：查找最低薪水员工的信息
select last_name, job_id, salary
from employees
where salary = (select min(salary) from employees);
在 HAVING 子句使用子查询。
例子：查找部门最小薪水比 50 部门最小薪水大的部门
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (select min(salary)
from employees
where department_id = 50);
多行子查询
如果子查询结果返回多行，则需要使用多行操作符：
| Operator | Meaning |
|---|---|
| IN | Equal to any member in the list |
| ANY | Compare value to each value returned by the subquery |
| ALL | Compare value to every value returned by the subquery |
在多行子查询中使用 ANY 操作符。
例子：查询比职位为 'IT_PROG' 的任何一位员工的薪水低的员工信息（实际上就是比职位为 'IT_PROG' 的最大薪水的员工薪水小，小于最大的，如果是大于 ANY，就是大于最小的）
select employee_id, last_name, job_id, salary
from employees
where salary < any (select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
在多行子查询中使用 ALL 操作符。
例子：查询比职位为 'IT_PROG' 的所有员工的薪水低的员工信息（实际上就是比职位为 'IT_PROG' 的最小薪水的员工薪水小，小于最小的，如果是大于 ALL，就是大于最大的）
select employee_id, last_name, job_id, salary
from employees
where salary < all (select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
在多行子查询中使用 IN 或者 NOT IN 操作符。
例子：查找是管理人员的员工姓名和不是管理人员的员工名字
select emp.last_name
from employees emp
where emp.employee_id in (select mgr.manager_id from employees mgr);
select emp.last_name
from employees emp
where emp.employee_id not in (select mgr.manager_id from employees mgr);
这里返回 0 行因为如果使用 IN 只需要和子查询返回结果的任何一个匹配都可以，而使用 NOT IN 则需要和子查询返回结果的所有进行比较，包括 NULL，而与 NULL 进行计算，都为 NULL，故结果为 0 行，可以先在子查询中排除 NULL。
select emp.last_name
from employees emp
where emp.employee_id not in
(select mgr.manager_id
from employees mgr
where mgr.manager_id is not null);
多列子查询
有 2 种多列比较：
- 非成对比较
- 成对比较，没有交叉
例子：成对比较子查询，查找与 FIRST_NAME 为 'John' 的人员在同一部门且属于同一上司的员工信息
select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in
(select manager_id, department_id
from employees
where first_name = 'John')
and first_name <> 'John';
例子：非成对比较子查询，查询与 FIRST_NAME 为 'John' 的人员同一个部门的同事以及与 FIRST_NAME 为 'John' 的人员同一个上司的同事
select employee_id, manager_id, department_id
from employees
where manager_id in
(select manager_id from employees where first_name = 'John')
and department_id in
(select department_id from employees where first_name = 'John')
and first_name <> 'John';
标量子查询
只返回一个记录一个字段的子查询。
Scalar Subquery 用于如下情况：
- DECOADE 和 CASE 的条件和表达式部分
- 除了 GROUP BY 子句的 SELECT 语句
- UPDATE 语句的 SET 和 WHERE 部分
例子：标量子查询，当部门编号等于位置编号为 1800 的部门编号，则显示位置为 'Canada'，否则显示 'USA'
select employee_id,
last_name,
(case
when department_id =
(select department_id from departments where location_id = 1800) then
'Canada'
else
'USA'
end) location
from employees;
关联子查询
在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。
语法：
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 = outer.expr2);
处理过程如下：
- 外查询取出一行
- 从取出行中存储子查询需要的字段
- 根据存储的字段执行子查询
- 在外查询 WHERE 语句中对子查询执行结果进行确认
- 重复执行以上过程
例子：查询比部门平均薪水高的人员信息
select last_name, salary, department_id
from employees e
where salary > (select avg(salary)
from employees
where department_id = e.department_id);
等价改写：
select last_name, salary, e.department_id, t.avg_sal
from employees e,
(select department_id, avg(salary) avg_sal
from employees
group by department_id) t
where e.department_id = t.department_id
and e.salary > t.avg_sal
例子：查询职位变动过至少 2 次的人
select e.employee_id, last_name, e.job_id
from employees e
where 2 <=
(select count(*) from job_history where employee_id = e.employee_id);
等价改写：
select empid, name, jobid, count(*)
from (select t1.employee_id empid, t1.last_name name, t1.job_id jobid
from employees t1, job_history t2
where t1.employee_id = t2.employee_id)
group by empid, name, jobid
having count(*) >= 2;
EXISTS
比较子查询的结果是否有记录存在，如果有记录，则子查询返回 TRUE 且结束搜索，如果没有记录，则子查询返回 FALSE 且继续搜索。
例子：查询至少有一个下属的领导信息
select employee_id, last_name, job_id, department_id
from employees e
where exists (select 'X' from employees where manager_id = e.employee_id);
其中 'X' 是一个占位符，没有任何意义，使用数字也可以。
也可以使用 IN
select employee_id, last_name, job_id, department_id
from employees e
where employee_id in
(select manager_id from employees where manager_id = e.employee_id);
例子：查询没有人员的部门
select department_id, department_name
from departments d
where not exists
(select 1 from employees where department_id = d.department_id);
select department_id, department_name
from departments d
where department_id not in
(select department_id from employees where department_id is not null);
select department_id, department_name
from departments d
where department_id not in
(select department_id
from employees
where department_id = d.department_id);
WITH
- 存储在用户的临时表空间
- 可以提高性能
- 只能用于 SELECT STATEMENTS
- 可以包含多个查询，用逗号分隔
- 先定义，后使用，对其后面的的查询块及主查询可见
例子：使用 WITH 语句，查询每个部门的名称及其薪水总计，同时这些部门的薪水总计大于公司所有部门的平均薪水
with
dept_costs as
(select d.department_name, sum(e.salary) as dept_total
from employees e
join departments d
on e.department_id = d.department_id
group by d.department_name),
avg_cost as
(select sum(dept_total) / count(*) as dept_avg from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name;
Set Operators
概述
| Operator | Returns |
|---|---|
| UNION | Rows from both queries after eliminating duplications |
| UNION ALL | Rows from both queries, including all duplications |
| INTERSECT | Rows that are common to both queries |
| MINUS | Rows in the first query that are not present in the second query |
注意：
- 每个集合 SELECT 子句的字段，个数和类型必须匹配，不匹配时可以使用 NULL 补齐
- 集合的默认执行顺序是从上到下，可以使用括号去改变
- ORDER BY 子句只能放在集合的最后，排序的字段或者字段别名只能来自于第一个 SELECT 子句，或者直接使用位置进行排序
- 除了 UNION ALL，其他集合操作符都会对结果进行去重和升序排序
- 最终结果的字段来自于第一个查询
UNION
两个查询的结果加起来并去掉重复的记录。
例子：查询员工在公司担任过的所有职位
select employee_id,job_id from employees
union
select employee_id,job_id from job_history;
两个表总共 117 行，这里返回 115 行，说明有部分是重复的，也就是有的员工同一个职位做过 2 次，被去掉了，同时结果默认对第一列升序排序。
UNION ALL
两个查询的结果加起来并保留重复的记录。
例子：查询员工在公司呆过的所有部门
select employee_id,job_id,department_id from employees
union all
select employee_id,job_id,department_id from job_history
order by employee_id;
UNION ALL 默认不会对结果进行排序，所有如果明确两个查询结果没有重复的，可以使用 UNION ALL，避免排序操作。
INTERSECT
两个查询的结果取相同的部分，会去重及排序。
例子：查询当前职位和以前从事过的职位相同的员工编号及职位
select employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;
MINUS
从一个查询的结果里面除去另一个查询的结果，会去重及排序。
例子：查询没有改变过职位的人员
select employee_id from employees
minus
select employee_id from job_history;
Manipulating Data
DML 语句用于以下情况：
- 为表增加新行
- 修改表的记录
- 删除表的记录
INSERT
语法：
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
- INSERT INTO VALUES 语句一次只能插入一行
- 可以在表后面列出需要插入的字段，也可以不列出，使用表定义的字段顺序来插入
- 插入的值如果是字符和日期，需要使用单引号引起来
- 如果字段可以为 NULL，插入的时候表后面不列出字段，就需要显式使用 NULL 占位，插入的时候列出了非空的字段，那么隐式忽略可以为 NULL 的字段
例子：向 DEPT 表插入一条数据（与 DEPARTMENTS 表一样）
create table dept as select * from departments;
insert into dept(department_id,department_name,manager_id,location_id)
values(70,'Public Relations',100,1700);
例子：只向表的某些字段插入值，其余为 NULL
insert into dept
values(100,'Finance',null,null);
例子：向人员表 EMP 插入一行数据，入职时间为当前时间（EMP 与 EMPLOYEES 一样）
create table emp as select * from employees;
insert into emp(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)
values(113,'Louis','Popp','LPOPP','555,124,4567',sysdate,'AC_ACCOUNT',6900,null,205,100);
例子：向人员表 EMP 插入一行数据，入职时间为指定的时间，使用了转换函数 TO_DATE，避免语言环境的原因导致数据插不进去
insert into emp
values(114,'Den','Raphealy','DRAPHEAL','515.127.4561',to_date('FEB 3,1999','MON DD,YYYY'),'AC_ACCOUNT',11000,null,100,30);
例子：使用子查询一次插入多行数据
create table sales_reps(id,name,salary,commission_pct) as
select employee_id,last_name,salary,commission_pct from employees where 1=0;
insert into sales_reps(id,name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees where job_id like '%REP%';
UPDATE
语法：
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
特别注意：不加 WHERE 条件表示更新表里面所有的记录。
例子：将人员表里面人员编号为 113 的人员的部门编号修改为 70
update emp set department_id=70 where employee_id=113;
例子：将人员表里面所有人员的部门编号修改为 110（没有加 WHERE 条件）
update emp set department_id=110;
使用子查询的结果去更新记录。
例子：将人员表里面人员编号为 114 的人员的职位和薪水修改为与人员编号为 205 的一样
update emp set
job_id=(select job_id from emp where employee_id=205),
salary=(select salary from emp where employee_id=205)
where employee_id=114;
还可以使用下面这种，少查询一次，提高性能
update emp set
(job_id,salary)=(select job_id,salary from emp where employee_id=205)
where employee_id=114;
使用其他表的查询结果去更新记录。
例子：将 EMP 表中职位与 EMPLOYEES 表员工编号为 200 的职位一样的人员的部门编号修改为与 EMPLOYEES 表员工编号为 100 的部门编号一样
update emp set
department_id=(select department_id from employees where employee_id=100)
where job_id=(select job_id from employees where employee_id=200);
使用关联子查询用一张表的数据对另一张表进行关联更新。
语法：
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);
例子：使用部门表的部门名字更新人员表的部门名字
create table empl6 as select * from employees;
alter table empl6 add(department_name varchar2(25));
update empl6 e
set department_name =
(select department_name
from departments
where department_id = e.department_id);
DELETE
语法：
DELETE [FROM] table
[WHERE condition];
特别注意：不加 WHERE 条件表示删除表里面所有的记录。
例子：删除 DEPT表中部门名字为 'Finance' 的记录
delete from dept where department_name='Finance';
例子：删除 EMP 表中所有记录
delete from emp;
使用其他表的查询结果去删除记录。
例子：在 EMP 表中删除部门名称包含 'Public' 的部门的所有人员信息
delete from emp
where department_id in
(select department_id from dept where department_name like '%Public%');
使用关联子查询用一张表的数据对另一张表进行关联删除。
语法：
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
例子：删除表 EMPL6 里面更换过工作的人员
delete from empl6 e
where employee_id in (select employee_id
from job_history
where employee_id = e.employee_id);
TRUNCATE
语法：
TRUNCATE TABLE table_name;
- 删除表中所有的记录，保留表结构
- 是 DDL 语句，不能回滚
- 表中有参照完整性约束不能执行 TRUNCATE
例子：删除 EMP 表中所有的记录
truncate table emp;
事务
事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
事务包含：
- 多个 DML 语句
- 一个 DDL 语句
- 一个 DCL 语句
事务开始于第一条 DML 语句的执行。
事务结束于下面的事件：
- 发出 COMMIT 或者 ROLLBACK 语句
- 执行 DDL 或者 DCL 语句
- 正常退出 SQL Developer 或者 SQL*Plus（使用 EXIT 退出），事务自动提交
- 系统崩溃或者异常退出 SQL Developer 或者 SQL*Plus（关闭窗口），事务自动回滚
使用 COMMIT 和 ROLLBACK 语句的作用：
- 确保数据的读一致性，提交了其他会话才能看到
- 在数据永久改变前预览数据的的改变
- 分组逻辑相关的操作
例子：使用 DDL 隐式提交
在会话窗口 1 执行删除
SQL> select count(*) from dept;
SQL> delete from dept;
SQL> select count(*) from dept;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
在会话窗口 1 执行 DDL
SQL> create table loc as select * from locations;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
例子：使用 DCL 隐式提交
在会话窗口 1 执行插入
SQL> insert into dept select * from departments;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
在会话窗口 1 执行 DCL
SQL> grant select on dept to hr;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
例子：EXIT 退出 SQL*Plus 隐式提交
在会话窗口 1 执行删除
SQL> delete from dept;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
在会话窗口 1 使用 EXIT 退出 SQL*Plus
SQL> exit
在会话窗口 2 进行查询
SQL> select count(*) from dept;
例子：关闭终端窗口隐式回滚
在会话窗口 1 执行插入
SQL> insert into dept select * from departments;
在会话窗口 2 进行查询
SQL> select count(*) from dept;
关闭会话窗口 1
在会话窗口 2 进行查询
SQL> select count(*) from dept;
在 COMMIT 或者 ROLLBACK 之前数据的状态：
- 可以恢复到数据先前的状态
- 当前用户可以查看 DML 的结果
- 其他用户看不到 DML 的结果
- 影响的行被锁住，其他用户不能修改被锁住的行
例子：行锁
在会话窗口 1 执行更新
SQL> insert into dept select * from departments;
SQL> commit;
SQL> update dept set manager_id=200 where department_id=100;
在会话窗口 2 更新同一条记录，挂起
SQL> update dept set manager_id=20 where department_id=100;
在会话窗口 1 提交
SQL> commit;
会话窗口 2 完成更新。
在 COMMIT 之后数据的状态：
- 数据改变永久生效
- 数据先前状态永久丢失
- 所有用户都可以看到结果了
- 行锁释放，其他用户可以对该行进行操作
在 ROLLBACK 之后数据的状态：
- 放弃对数据的修改
- 恢复到数据先前的状态
- 锁定的行被释放
语句级回滚
- 如果一个事务中某一个 DML 语句执行失败，则该语句将回滚，该语句之前的操作将保留，必须显式使用 COMMIT 或者 ROLLBACK 结束该事务
- 即使 DDL 语句执行失败，也会 COMMIT
读一致性保证了数据在所有的时候是一个一致的状态，就是说，我们只要没有提交，其他会话看到的始终是修改以前的结果
读一致性确保数据：
- 写不影响读
- 读不影响写
- 写需等待写
执行 DML 操作时，自动进行读一致性操作，Oracle 将要修改的数据复制到 Undo Segment，COMMIT 前，其余 Session 看到的数据是 Undo Segment 中的数据，只有当前 Session 看到的数据是已经修改过的数据，COMMIT 后，所有的 Session 都可以看到修改后的数据，被以前数据占用的 Undo Segment 将会释放重用，如执行回滚，Undo Segment 中的数据将会回写。
Table
创建表
语法：
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
需要指定表名，列名，列数据类型及长度。
表名和列名命名规则：
- 字母开头
- 不超过 30 个字符
- 只能包含大小写字母，0-9，_，$，#
- 同一用户下对象名称不能重复
- 不能使用保留字
创建表的时候可以为字段指定默认值，具体用法如下：
- 字符，表达式或者 SQL 函数是合法的默认值
- 其他字段名称或者伪列是非法的默认值
- 默认值的类型要与字段的类型一致
- 插入的时候 DEFAULT 表示使用默认值
例子：创建一个入职时间表，其中入职时间字段使用系统日期为默认值
create table hire_dates(
id number(8),
hire_date date default sysdate);
例子：创建一个 DEPT 表，其中创建时间字段使用系统日期为默认值
create table dept(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
create_date date default sysdate);
使用 DESC 查看表的结构
SQL> desc dept;
数据类型
| Data Type | Description |
|---|---|
| VARCHAR2(size) | Variable-length character data |
| CHAR(size) | Fixed-length character data |
| NUMBER(p,s) | Variable-length numeric data |
| DATE | Date and time values |
| LONG | Variable-length character data (up to 2 GB) |
| CLOB | Character data (up to 4 GB) |
| RAW and LONG RAW | Raw binary data |
| BLOB | Binary data (up to 4 GB) |
| BFILE | Binary data stored in an external file (up to 4 GB) |
| ROWID | A base-64 number system representing the unique address of a row in its table |
- 最常用的是 VARCHAR2，NUMBER，DATE 这三种类型
- CHAR 为定长字符类型，最大长度 2000 字节，VARCHAR2 为变长字符类型，最大长度 4000 字节，PL/SQL 中的 CHAR 和 VARCHAR2 最大长度为 32767
- NUMBER(p,s)是数字类型，p 表示所有有效数字的位数，默认 38 位，s 表示小数点以后的位数
- LONG 是可变长字符类型，RAW 是固定长度的二进制类型，LONG RAW 是可变长度的二进制类型，这些都是较老的数据类型，逐渐被 CLOB，BLOB 取代
- 使用子查询创建表时 LONG 类型字段不会被复制，LONG 类型字段不能用于 GROUP BY 或者 ORDER BY 子句，一个表只能有一个 LONG 类型字段，不能在 LONG 类型字段上创建约束
- CLOB 是字符大对象类型，可以存储论文、文字
- BLOB 是二进制大对象类型，可以存储图片、音乐
- BFILE 是存储在数据库外的二进制数据类型，可以存储视频
- ROWID 是行的地址
约束
约束是表级上的一个规则，阻止对表依赖性的破坏，约束类型：
- 非空（NOT NULL）约束：顾名思义，所约束的列不能为 NULL 值。否则就会报错
- 唯一（UNIQUE）约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违法约束条件
- 主键（PRIMARY KEY）约束：唯一的标识表中的每一行，不能重复，不能为空。 创建主键或唯一约束后，Oracle 会自动创建一个与约束同名的索引（UNIQUENES 为 UNIQUE 唯一索引）。需要注意的是：每个表只能有且有一个主键约束
- 外键（FOREIGN KEY）约束：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性。外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另外一方面它会增加表插入、更新等 SQL 性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束
- 条件（CHECK）约束：表中每行都要满足该约束条件。条件约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件约束
约束使用指导：
- 可以命名一个约束，也可以不指定，使用 Oracle 自动产生的以 'sys_c' 开头的名字
- 既可以在创建表的时候创建约束，也可以在创建表之后再创建约束
- 既可以在表级创建约束，也可以在列级创建约束。
- 复合约束（包含多列）需在表级创建
- 可以通过数据字典查看约束
定义约束语法：
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
列级约束：
column [CONSTRAINT constraint_name] constraint_type,
表级约束：
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
例子：创建列级约束
create table emp1(
employee_id number(6) constraint emp1_id_pk primary key,
first_name varchar2(20));
例子：创建表级约束
create table emp2(
employee_id number(6),
first_name varchar2(20),
job_id varchar2(10) not null,
constraint emp2_id_pk primary key(employee_id));
非空约束
- 字段的值不能为 NULL
- 只能在列级创建，不能在表级创建
唯一约束
- 字段的值不能相同
- 但是可以为 NULL
- 可以创建在列级，也可以创建在表级
- 唯一约束隐式创建唯一索引
例子：创建唯一约束
create table emp4(
employee_id number(6) unique,
email varchar2(25),
constraint emp4_email_uk unique(email));
创建了 2 个唯一约束，一个列级，没有指定名字，一个表级，指定了名字。
主键约束
- 字段的值不能为 NULL，也不能相同
- 一张表只能有一个主键约束
- 主键约束隐式创建唯一索引
外键约束
列级创建不需要 FOREIGN KEY 关键字，表级创建必须指定，使用 REFERENCES 关键字指定父表及参考列，如果父表有主键，则可以不指定参考列，默认为主键，如果父表没有主键，则必须指定参考列
子表外键的值必须存在于父表中或者为 NULL
父表被参考列须是父表的主键或唯一键
为子表的列增加外键约束后，默认情况下不允许删除或更新父表相关列值，以确保参照完整性
ON DELETE CASCADE 允许删除父表相关记录，同时子表对应记录也将被删除
ON DELETE SET NULL 允许删除父表相关记录，同时子表对应记录被置为 NULL
例子：创建外键
create table emp5(
employee_id number(8) primary key,
manager_id number(8) constraint emp5_emp5_fk references emp5(employee_id),
department_id number(4),
constraint emp5_dept_fk foreign key(department_id) references departments(department_id));
创建了 2 个外键，一个在列级创建，参考自己的主键，一个在表级创建，参考 DEPARTMENTS 表的主键，由于都有主键，都可以不指定父表的参考列。
例子：参照完整性
先创建 2 个表，EMP6 的 DEPTID 列参考 DEPT6 的 DEPTID 列
create table dept6(
deptid number constraint dept6_id_pk primary key,
deptname varchar2(20));
create table emp6(
empid number,
last_name varchar2(20),
deptid number,
constraint emp6_id_pk primary key(empid),
constraint emp6_dept6_fk foreign key(deptid) references dept6);
现在 2 个表没有数据，先向表 EMP6 插入数据
SQL> insert into emp6 values(100,'aaa',10);
insert into emp6 values(100,'aaa',10)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP6_DEPT6_FK) violated - parent key not
found
报错，提示违法约束完整性，没有找到父键，这里插入的 DEPTID 的值为 10，但是在表 dept6 里面没有 DEPTID 为 10 的记录，向往表 EMP6 里面插入一条记录
SQL> insert into dept6 values(10,'sales');
再次执行刚才插入表 EMP6 的语句就可以了
SQL> insert into emp6 values(100,'aaa',10);
删除父表 DEPT6 的这条记录
SQL> delete from dept6 where deptid=10;
delete from dept6 where deptid=10
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP6_DEPT6_FK) violated - child record
found
报错，提示违法约束完整性，在子表找到相应的记录。
先删除约束再重新创建，增加 ON DELETE CASCADE 关键字
SQL> alter table emp6 drop constraint emp6_dept6_fk;
SQL> alter table emp6 modify(deptid constraint emp6_dept6_fk references dept6 on delete cascade);
再次来删除父表 DEPT6 的这条记录就可以了，同时子表的这条记录也会被删除，先看一下删除之前表的记录
SQL> select * from emp6;
SQL> select * from dept6;
执行删除
SQL> delete from dept6 where deptid=10;
再看一下这 2 张表的记录，EMP6 的记录也被级联删除了
SQL> select * from emp6;
SQL> select * from dept6;
CHECK 约束
- 定义一个字段必须满足的条件
- 不允许使用如下伪列：CURRVAL,NEXTVAL,LEVEL,ROWNUM
- 不允许使用如下函数：SYSDATE,UID,USER,USERENV
- 不允许参照其他行的值
- 可以在表级也可以在字段级定义
例子：创建一个 CHECK 约束，薪水必须大于 0
create table emp7(
employee_id number,
salary number constraint emp7_salary_ck check(salary>0));
CTAS
- 可以使用 AS 关键字加子查询创建表并插入数据
- 表的字段要和子查询的字段进行匹配，个数相等，类型相同
- 表后面定义了字段名字，那么子查询里面的表达式就不要定义别名，如果表后面没有定义，那么子查询里面的表达式就必须定义别名
- 列数据类型和非空约束将会继承到新表，其他约束不继承
例子：使用子查询创建表
create table dept80
as
select employee_id,last_name,salary*12 annsal,hire_date
from employees
where department_id=80;
删除表
所有数据和表结构都被删除
所有的索引和约束都被删除
例子：删除 DEPT80 表
SQL> drop table dept80;
Other Schema Objects
视图
视图就是一个子查询。
作用：
- 屏蔽对敏感信息的访问
- 简化复杂的查询
- 根据不同的用户需求创建不同的视图
简单视图和复杂视图：
- 简单视图是指视图数据来源于一张表，不包含函数及分组，可以进行 DML 操作，对简单视图进行操作，实际上就是对基表进行操作，构成视图的表叫基表
- 复杂视图是指视图来源于一张表或者多张表，可能包含分组和函数，不一定能进行 DML 操作
| Feature | Simple Views | Complex Views |
|---|---|---|
| Number of tables | One | One or more |
| Contain functions | No | Yes |
| Contain groups of data | No | Yes |
| DML operations through a view | Yes | Not always |
创建视图
语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
例子：使用人员表里面 80 部门的人员信息创建一个视图
create view empvu80
as
select employee_id,last_name,salary from employees where department_id=80;
查询视图结构
SQL> desc empvu80;
例子：在子查询中使用列别名创建视图
create view salvu50
as
select employee_id id_number,last_name name,salary*12 ann_salary
from employees where department_id=50;
与使用子查询创建表类似，如果子查询里面有表达式或者函数，必须要用别名，不然要报错，或者在视图后面带上字段。
例子：修改前面创建的视图 EMPVU80
create or replace view empvu80(id_number,name,sal,department_id)
as
select employee_id,first_name||' '||last_name,salary,department_id
from employees where department_id=80;
例子：创建一个包含组函数，数据来自于 EMPLOYEES 表和 DEPARTMENTS 表的复杂视图，显示每个部门的部门名称，最小薪水，最大薪水和平均薪水
create or replace view dept_sum_vu(name,minsal,maxsal,avgsal)
as
select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
from employees e join departments d
on e.department_id=d.department_id
group by d.department_name;
DML 操作规则
- 在简单视图上面可以进行 DML 操作
- 视图如果包含组函数，GROUP BY 子句，DISTINCT 关键字，ROWNUM 伪列，不能删除视图里面的记录
- 视图如果包含组函数，GROUP BY 子句，DISTINCT 关键字，ROWNUM 伪列，表达式定义的列，不能修改视图里面的数据
- 视图如果包含组函数，GROUP BY 子句，DISTINCT 关键字，ROWNUM 伪列，表达式定义的列以及存在于基表（没有定义默认值）但是不在视图中的非空列，不能通过视图增加数据
例子：通过简单视图删除数据
SQL> delete from empvu80 where id_number=177;
SQL> rollback;
例子：通过包含组函数的复杂视图删除数据会报错
SQL> delete from dept_sum_vu where name='IT';
delete from dept_sum_vu where name='IT'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
例子：通过包含表达式的视图修改相关数据会报错
SQL> update empvu80 set name='aaa' where id_number=179;
update empvu80 set name='aaa' where id_number=179
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
例子：创建的视图不包含基表的非空字段，且非空字段没有默认值，则通过视图进行插入数据会报错
先创建一个基表，包含非空字段
SQL> create table emp8 as
select employee_id,last_name,salary,job_id from employees where 1=0;
SQL> desc emp;
再创建一个视图，不包含所有非空字段
SQL> create view v_emp as select employee_id,salary from emp8;
SQL> desc v_emp;
插入数据报错
SQL> insert into v_emp values(100,1000);
insert into v_emp values(100,1000)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMP8"."LAST_NAME")
修改表定义，为非空字段增加默认值
SQL> alter table emp8 modify(last_name default 'Tom',job_id default 'IT');
再次插入数据成功
SQL> insert into v_emp values(100,1000);
WITH CHECK OPTION
使用 WITH CHECK OPTION 语句增加约束，可以确保用户的 DML 操作只能在视图的范围内进行（就是不能超出 WHERE 条件）。不然，用户修改了数据导致该数据不满足创建视图子查询的 WHERE 条件，则下次查询就看不到了。
例子：不使用 WITH CHECK OPTION 语句和使用 WITH CHECK OPTION 语句的区别
先创建一个视图不使用 WITH CHECK OPTION 语句
SQL> create or replace view empvu20 as
select employee_id,last_name,salary,department_id from employees where department_id=20;
SQL> select * from empvu20;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
201 Hartstein 13000 20
202 Fay 6000 20
对其中一条记录进行修改，将部门编号修改为 10
SQL> update empvu20 set department_id=10 where employee_id=202;
再次查询，刚才修改的记录看不到了
SQL> select * from empvu20;
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
201 Hartstein 13000 20
为了避免这种情况，使用 WITH CHECK OPTION 语句创建视图
SQL> create or replace view empvu20 as
select employee_id,last_name,salary,department_id from employees where department_id=20
with check option constraint empvu20_ck;
再来进行修改就会报错
SQL> update empvu20 set department_id=10 where employee_id=201;
update empvu20 set department_id=10 where employee_id=201
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
WITH READ ONLY
使用 WITH READ ONLY 语句将视图设置为只读，执行 DML 操作会报错
例子：创建一个只读的视图
SQL> create or replace view empvu10(employee_number,employee_name,job_title) as
select employee_id,last_name,job_id from employees
where department_id=10
with read only;
执行删除操作报错
SQL> delete from empvu10;
delete from empvu10
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
删除视图
语法：
DROP VIEW view;
删除视图不会删除基表的数据。
例子：删除视图
SQL> drop view empvu80;
序列
- 自动产生唯一数字
- 是一个共享的对象
- 可以用于生成主键的值
- 使用缓存提高效率
创建序列
语法：
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
[{ORDER | NOORDER}];
例子：创建一个序列
SQL> create sequence seq_test;
例子：创建一个序列，增长 10，从 120 开始，最大达到 9999，没有 CACHE，没有循环
SQL> create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle;
使用序列
使用序列需要使用 NEXTVAL 和 CURRVAL 伪列
- NEXTVAL 返回下一个可用的序列值
- CURRVAL 返回序列的当前值
- 在当前会话中，如果要使用 CURRVAL ，需要先使用 NEXTVAL
可以在以下情况使用 NEXTVAL 和 currval
- 在 SELECT STATEMENT 的 SELECT LIST 中（不能在子查询的 SELECT LIST）
- 在 INSERT STATEMENT 的子查询 SELECT LIST 中
- 在 INSERT STATEMENT 的 VALUE 语句中
- 在 UPDATE STATEMENT 的 SET 语句中
不可以在以下情况使用 NEXTVAL 和 CURRVAL
- 视图的 SELECT LIST
- 带有 DISTINCT 关键字的 SELECT STATEMENT
- 带有 GROUP BY、HAVING、ORDER BY 语句的 SELECT STATEMENT
- 在 SELECT、DELETE、UPDATE 的子查询
- 在 CREATE TABLE 或者 ALTER TABLE 语句的 DEFAULT EXPRESSION 中
例子：序列的使用
先使用 CURRVAL 报错
SQL> select dept_deptid_seq.currval from dual;
select dept_deptid_seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence DEPT_DEPTID_SEQ.CURRVAL is not yet defined in this session
先使用 NEXTVAL ，获取下一个可用的序列值
SQL> select dept_deptid_seq.nextval from dual;
NEXTVAL
----------
120
再使用 CURRVAL ，取上一次 NEXTVAL 取的值
SQL> select dept_deptid_seq.currval from dual;
CURRVAL
----------
120
例子：使用序列生成的值作为表的主键
SQL> insert into departments(department_id,department_name,location_id)
values(dept_deptid_seq.nextval,'Support',2500);
SQL> select dept_deptid_seq.currval from dual;
修改序列
可以修改序列的增长值，最大值，最小值，循环选项，缓存选项，但是不能修改起始值，如果不设置最小值，默认为 1。
修改注意事项：
- 要有修改的权限
- 只影响后续的序列
- 修改起始值只能删除重建
例子：修改序列
SQL> alter sequence dept_deptid_seq
increment by 20
maxvalue 300
nocache
nocycle;
在使用 Data Pump 进行数据迁移时，源库由于先导出序列，再导出数据，故某些使用序列的表有可能由于正在进行的业务导致导出的数据比先导出的序列的当前值大。当将数据导入到目标库后，对这些表进行插入就会出现序列的取值已经存在与当前表中，报主键冲突的错误。
在 Oracle 19c 之前，不能通过修改序列的起始值来解决这个问题，只能删除再重建序列。
获取删除所有序列的脚本：
SELECT 'DROP SEQUENCE ' || SEQUENCE_NAME || ';' AS drop_sequence
FROM USER_SEQUENCES;
获取创建序列的脚本，将起始值设置为当前值 + 1000：
SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' START WITH ' ||
(LAST_NUMBER + 1000) || ' INCREMENT BY ' || INCREMENT_BY ||
' MAXVALUE ' || MAX_VALUE || ' MINVALUE ' || MIN_VALUE || ' ' || CASE
WHEN CYCLE_FLAG = 'Y' THEN
'CYCLE'
ELSE
'NOCYCLE'
END || ' CACHE ' || CACHE_SIZE || ';' AS create_sequence
FROM USER_SEQUENCES;
删除序列
例子：删除序列
SQL> drop sequence dept_deptid_seq;
索引
- 用于提高查询的速度
- 快速定位数据，减少物理 I/O
创建索引
创建索引的 2 种方式
- 自动创建，当创建主键约束或者唯一约束的时候自动创建一个唯一索引
- 手工创建，创建非唯一索引提高访问速度
语法：
CREATE INDEX index
ON table (column[, column]...);
例子：在人员表的 LAST_NAME 字段创建一个索引
SQL> create index emp_last_name_idx on employees(last_name);
注意：
默认创建索引时会阻塞对表的 DML 操作，可以加上
ONLINE关键字以便在创建索引期间允许对表的 DML 操作。
| Create an index when: |
|---|
| A column contains a wide range of values |
| A column contains a large number of null values |
| One or more columns are frequently used together in a WHERE clause or a join condition |
| The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table |
| Do not create an index when: |
|---|
| The columns are not often used as a condition in the query |
| The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table |
| The table is updated frequently |
| The indexed columns are referenced as part of an expression |
删除索引
索引不能修改，只能删除重建，删除索引需要是索引的所有者或者有 DROP ANY INDEX 权限。
语法：
DROP INDEX index;
例子：删除索引
SQL> drop index emp_last_name_idx;
同义词
同义词是数据库模式对象的一个别名，经常用于简化对象访问。
同义词的分类
- 公用同义词：所有的用户都可以使用公用同义词
- 私有同义词：由创建他的用户所有
同义词作用
- 简化对其他用户对象的访问
- 如果创建的表的名字很长，可以为这个表创建一个同义词来简化引用
语法：
CREATE [PUBLIC] SYNONYM synonym
FOR object;
例子：创建及删除一个同义词
SQL> create synonym d_sum for dept_sum_vu;
SQL> drop synonym d_sum;
Data Dictionary Views
数据字典包含基表和用户可以访问的视图。
数据字典视图的命名规则及分类：
| View Prefix | Purpose |
|---|---|
| USER | User’s view (what is in your schema; what you own) |
| ALL | Expanded user’s view (what you can access) |
| DBA | Database administrator’s view (what is in everyone’s schemas) |
| V$ | Performance-related data |
DICT
使用数据字典视图从 DICTIONARY 视图开始，这个视图里面包含了所有的数据字典表和视图的名字和描述。通过他来查询其他的数据字典视图，是查询数据字典的入口。
SQL> desc dictionary
DICT 为 DICTIONARY 的同义词，实际当中用 DICT 更多一些。
SQL> select * from dictionary where table_name='DICT';
例子：查询内存相关的数据字典视图
SQL> select * from dict where table_name like '%MEMORY%';
OBJECT
- 使用 USER_OBJECTS 视图可以查看你拥有的所有对象，列出在你模式下的对象名字，状态等信息
- 使用 ALL_OBJECTS 视图可以查看你可以访问的所有对象
例子：通过 USER_OBJECTS 视图查看用户当前所有的对象信息
select object_name, object_type, created, status
from user_objects
order by object_type;
例子：查看当前用户下所有无效的对象
select object_name, object_type, status
from user_objects
where status <> 'VALID';
TABLE
例子：通过 USER_TABLES 视图查看用户当前所有表信息
SQL> select table_name from user_tables;
例子：使用 USER_TAB_COLUMNS 查看表字段的信息
select column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from user_tab_columns
where table_name = 'EMPLOYEES';
例子：查看哪些表有 'department_id' 字段
SQL> select table_name from user_tab_columns where column_name='DEPARTMENT_ID';
CONSTRAINT
USER_CONSTRAINTS 视图描述了表的约束定义
USER_CONS_COLUMNS 视图描述了约束对应的列
例子：查看表 employees 的约束信息
select constraint_name,
constraint_type,
search_condition,
r_constraint_name,
delete_rule,
status
from user_constraints
where table_name = 'EMPLOYEES';
例子：查看表 employees 中约束对应的列
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMPLOYEES';
VIEW
例子：查看当前用户视图信息
SQL> select * from user_views;
例子：查看视图的创建语句
SQL> set long 2000
SQL> select text from user_views where view_name='EMP_DETAILS_VIEW';
SEQUENCE
例子：查看当前用户序列信息
SQL> select * from user_sequences;
SYNONYM
例子：查看当前用户同义词信息
select * from user_synonyms;
COMMENT
可以为表和列增加注释，可以通过如下数据字典视图查询注释
- ALL_TAB_COMMENTS
- ALL_COL_COMMENTS
- USER_TAB_COMMENTS
- USER_COL_COMMENTS
例子：为表 EMPLOYEES 增加注释，并查询注释信息
SQL> comment on table employees is 'Employee Information';
SQL> select table_name,comments from user_tab_comments where table_name='EMPLOYEES';
Control User Access
访问 Oracle 数据库，除了需要用户名和密码外，还需要相应的权限。
权限分类：
- 系统权限，是指对数据库进行操作的权限，比如创建连接，表，数据库，表空间等
- 对象权限，是指对数据库对象内容进行操作的权限，比如增加记录，删除记录等
模式（Schema）是用户（User）下面对象（Object）的集合。
系统权限
Oracle 11g 有超过 200 个系统权限，系统权限一般由 DBA 进行授予。
例子：通过 DBA_SYS_PRIVS 视图查看当前数据库的系统权限
SQL> select count(distinct privilege) from dba_sys_privs;
SQL> select distinct privilege from dba_sys_privs order by 1;
创建用户
语法
CREATE USER user
IDENTIFIED BY password;
例子：创建一个用户名为 USER1，密码为 'user1' 的用户
SQL> create user user1 identified by user1;
例子：修改用户 USER1 的默认表空间为 USERS，配额为 10M，临时表空间为 TEMP
SQL> alter user user1 identified by user1 default tablespace users temporary tablespace temp quota 10m on users;
授予用户系统权限
创建用户后，没有赋予权限是不能进行登录的。
例子：使用刚刚创建的用户连接数据库报错，没有 CREATE SESSION 的权限
SQL> conn user1/user1
ERROR:
ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
语法
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
系统开发人员需要的权限如下：
- 创建会话
- 创建表
- 创建序列
- 创建视图
- 创建过程
例子：授予系统权限给用户并登录
SQL> conn / as sysdba
SQL> grant create session,create table,create sequence,create view to user1;
SQL> conn user1/user1
Connected.
例子：通过 SESSION_PRIVS 视图查看该用户的系统权限
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
创建和授予权限给角色
角色是一系列权限的集合，是为了简化用户权限的分配和管理，可以将角色授予给用户和其他角色。
对于新建用户，一般授予 CONNECT，RESOURCE 这两个系统内置角色。
例子：查看当前用户关于角色的权限
SQL> conn / as sysdba
SQL> select * from session_privs where privilege like '%ROLE%';
PRIVILEGE
----------------------------------------
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
例子：创建角色，给角色授予权限，将角色授予用户
SQL> create role manager;
Role created.
SQL> grant create session,create table,create sequence,create view to manager;
Grant succeeded.
SQL> create user user2 identified by user2;
User created.
SQL> grant manager,create synonym to user2;
Grant succeeded.
SQL> conn user2/user2;
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
MANAGER
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
修改密码
例子：使用 ALTER USER 修改密码
SQL> alter user user2 identified by user2;
User altered.
例子：使用 password 命令修改自己的密码
SQL> password user2
Changing password for user2
Old password:
New password:
Retype new password:
Password changed
例子：DBA 使用 password 命令修改其他用户的密码
SQL> conn / as sysdba
Connected.
SQL> password user2
Changing password for user2
New password:
Retype new password:
Password changed
对象权限
- 不同的对象有不同的对象权限
- 用户有其用户模式下所有对象的对象权限
- 对象权限可以转授权给其他用户或者角色
- 系统权限和对象权限不能在一条 GRANT 语句里面
| Object Privilege | Table | View | Sequence | Procedure |
|---|---|---|---|---|
| ALTER | √ | √ | ||
| DELETE | √ | √ | ||
| EXECUTE | √ | |||
| INDEX | √ | |||
| INSERT | √ | √ | ||
| REFERENCES | √ | |||
| SELECT | √ | √ | √ | |
| UPDATE | √ | √ |
语法
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
这里一次只能针对一个对象进行授权。
例子：将 EMPLOYEES 表的查询权限授予 USER1，USER2
SQL> grant select on employees to user1,user2;
例子：将更新 DEPARTMENTS 表 DEPARTMENT_NAME，LOCATION_ID 列的权限授予 USER1，MANAGER
SQL> grant update(department_name,location_id) on departments to user1,manager;
例子：将 DEPARTMENTS 表 SELECT，INSERT 权限授予 USER1，且 USER1 可以转授权
SQL> grant select,insert on departments to user1 with grant option;
例子：将 DEPARTMENTS 表的查询权限授予 PUBLIC，所有用户都可以访问
SQL> grant select on hr.departments to public;
权限相关数据字典视图
| Data Dictionary View | Description |
|---|---|
| ROLE_SYS_PRIVS | System privileges granted to roles |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| USER_ROLE_PRIVS | Roles accessible by the user |
| USER_TAB_PRIVS_MADE | Object privileges granted on the user’s objects |
| USER_TAB_PRIVS_RECD | Object privileges granted to the user |
| USER_COL_PRIVS_MADE | Object privileges granted on the columns of the user’s objects |
| USER_COL_PRIVS_RECD | Object privileges granted to the user on specific columns |
| USER_SYS_PRIVS | System privileges granted to the user |
例子：查看赋予角色的系统权限
SQL> select * from role_sys_privs;
例子：查看授予角色的表权限
SQL> select * from role_tab_privs;
例子：查看用户拥有的角色
SQL> select * from user_role_privs;
例子：查看授予给其他用户的对象权限
SQL> select * from user_tab_privs_made;
例子：查看当前用户获取的其他用户授予的对象权限
SQL> select * from user_tab_privs_recd;
例子：查询授予给其他用户的及当前用户获取的对象权限（USER_TAB_PRIVS_MADE 加上 USER_TAB_PRIVS_RECD）
SQL> select * from user_tab_privs;
例子：查看授予给其他用户的表列的对象权限
SQL> select * from user_col_privs_made;
例子：查看当前用户获取的其他用户授予的表列的对象权限
SQL> select * from user_col_privs_recd;
例子：查询授予给其他用户的及当前用户获取的表列的对象权限（USER_COL_PRIVS_MADE 加上 USER_COL_PRIVS_RECD）
SQL> select * from user_col_privs;
例子：查看授予给用户的系统权限（不包含角色里面的系统权限）
SQL> select * from user_sys_privs;
例子：查看当前用户所有的系统权限（ROLE_SYS_PRIVS 加上 USER_SYS_PRIVS）
SQL> select * from session_privs;
回收权限
- 对象权限级联回收
- 系统权限不能级联回收
语法
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
例子：用户 HR 回收授予给用户 USER1 的对 DEPARTMENTS 的 SELECT 和 INSERT 对象权限，并级联回收 USER2 的对 DEPARTMENTS 的 SELECT 和 INSERT 对象权限
SQL> conn hr/hr
Connected.
SQL> select * from user_tab_privs_made where grantee='USER1' and table_name='DEPARTMENTS';
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- -------------------- -------------------- -------------------- --- ---
USER1 DEPARTMENTS HR INSERT YES NO
USER1 DEPARTMENTS HR SELECT YES NO
SQL> conn user1/user1
Connected.
SQL> grant select,insert on hr.departments to user2;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- -------------------- -------------------- -------------------- --- ---
USER1 HR DEPARTMENTS HR INSERT YES NO
USER1 HR DEPARTMENTS HR SELECT YES NO
USER2 HR DEPARTMENTS USER1 INSERT NO NO
USER2 HR DEPARTMENTS USER1 SELECT NO NO
SQL> revoke select,insert on departments from user1;
Revoke succeeded.
SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';
no rows selected
从这儿可以看到用户 HR 将表 DEPARTMENTS 的 INSERT 和 SELECT 权限授予给 USER1，然后 USER1 又将这两个权限授予给了 USER2。用户 HR 回收授予给 USER1 的权限，同时也将 USER2 的权限也回收了。说明对象权限可以级联回收。
例子：系统权限不可以级联回收
SQL> conn / as sysdba
Connected.
SQL> create user user3 identified by user3;
User created.
SQL> create user user4 identified by user4;
User created.
SQL> grant connect to user3;
Grant succeeded.
SQL> grant connect to user4;
Grant succeeded.
SQL> conn user3/user3;
Connected.
SQL> select * from session_privs;
PRIVILEGE
--------------------
CREATE SESSION
SQL> conn / as sysdba
Connected.
SQL> grant create table to user3 with admin option;
Grant succeeded.
SQL> conn user3/user3;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER3 CREATE TABLE YES
SQL> grant create table to user4;
Grant succeeded.
SQL> conn user4/user4;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER4 CREATE TABLE NO
SQL> conn / as sysdba
Connected.
SQL> revoke create table from user3;
Revoke succeeded.
SQL> conn user4/user4;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER4 CREATE TABLE NO
Manage Schema Objects
ALTER TABLE
使用 ALTER TABLE 语句可以：
- 增加列
- 修改列
- 删除列
- 停用列
- 重命名列
- 管理约束
增加列
- 不能为新增加的列指定位置，只能位于最后
- 如果其他列有数据，强制增加非空列必须指定默认值
- 如果其他列没有数据（空表），可以增加不指定默认值的非空列
- 对于大表，如果增加列时指定了默认值，一定要加上
NOT NULL，此时只会更新数据字典，不会去更新物理行，操作会在很短时间内完成，不会影响生产。
语法
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
例子：为表 DEPT80 增加一个字段 JOB_ID
SQL> create table dept80(employee_id,last_name,annsal,hire_date) as
2 select employee_id,last_name,salary*12,hire_date
3 from employees where department_id=80;
Table created.
SQL> alter table dept80 add(job_id varchar2(9));
Table altered.
例子：为表 DEPT80 增加一个非空字段 FIRST_NAME 报错
SQL> alter table dept80 add(first_name varchar2(20) not null);
alter table dept80 add(first_name varchar2(20) not null)
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
例子：为表 DEPT80 增加一个非空字段 FIRST_NAME，默认值为 'aa'
SQL> alter table dept80 add(first_name varchar2(20) default 'aa' not null);
Table altered.
例子：对大表增加有默认值的列，可以看到加上 NOT NULL 后性能大大提升（从 12C 起支持不加）
SQL> set timing on
SQL> select count(*) from emp8;
COUNT(*)
----------
7012352
Elapsed: 00:00:01.03
SQL> alter table emp8 add (create_time date default sysdate);
Table altered.
Elapsed: 00:03:24.71
SQL> alter table emp8 add (modify_time date default sysdate not null);
Table altered.
Elapsed: 00:00:00.20
修改列
- 可以对列的数据类型，长度和默认值进行修改
- 对已有数据的列进行类型修改，必须要可以转换才行
- 对列长度的修改不能小于已有内容的长度
- 针对默认值的修改只影响后续插入的值
语法
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
例子：修改表 DEPT80 中 LAST_NAME 的列长度为 30
SQL> alter table dept80 modify(last_name varchar2(30));
Table altered.
例子：修改表 DEPT80 中 LAST_NAME 的类型为 DATE 报错，类型为 CHAR 可以
SQL> alter table dept80 modify(last_name date);
alter table dept80 modify(last_name date)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> alter table dept80 modify(last_name char(30));
Table altered.
例子：修改表 DEPT80 中 FIRST_NAME 的长度为 1 报错
SQL> alter table dept80 modify(first_name varchar2(1));
alter table dept80 modify(first_name varchar2(1))
*
ERROR at line 1:
ORA-01401: inserted value too large for column
删除列
- DROP 的列可以包含数据，也可以不包含数据
- 最后一列不能被 DROP
- DROP 的列不能恢复
- DROP 父键列需要使用 CASCADE CONSTRAINTS
- 包含大量数据的列，建议使用 UNUSED
- 分区表的分区键列和索引组织表的主键列不能被 DROP
语法
ALTER TABLE table
DROP (column);
例子：删除表 DEPT80 的一个字段
SQL> alter table dept80 drop column job_id;
Table altered.
例子：删除表 DEPT80 的多个字段
SQL> alter table dept80 drop(first_name,last_name);
Table altered.
例子：创建一个表 DEPT3，为列 DEPARTMENT_ID 增加主键，然后删除列 DEPARTMENT_ID
SQL> create table dept3 as select * from departments;
Table created.
SQL> alter table dept3 add constraint dept_dt_pk primary key(department_id);
Table altered.
SQL> alter table dept3 drop(department_id);
Table altered.
停用列
Oracle 删除字段后会回收空间，但是如果表很大，那么删除回收就很慢，可以用 SET UNUSED 这个选项，不马上回收空间，只是标记这个字段不可用了，达到快速屏蔽掉某个字段的目的，后续在业务比较闲的时候可以使用 DROP UNUSED COLUMNS 去回收空间。
在将字段 SET UNUSED 后，SELECT 查询和 DESC 都将看不到该字段，不会进入回收站，可以重新增加一个名称和类型一样的字段，通过 USER_UNUSED_COL_TAB 数据字典视图查询 SET UNUSED 信息。
SET UNUSED 后该字段的索引、约束都会被立即删除，含该字段的视图，状态变为不可用，必须修改这个视图后才可使用。
语法
ALTER TABLE <table_name>
SET UNUSED(<column_name>);
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;
ALTER TABLE <table_name>
DROP UNUSED COLUMNS;
例子：将表 DEPT80 中的字段 ANNSAL 设置为不可用，通过数据字典视图查询信息并使用 DROP UNUSED COLUMNS 删除
SQL> desc dept80;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
ANNSAL NUMBER
HIRE_DATE NOT NULL DATE
SQL> alter table dept80 set unused(annsal);
Table altered.
SQL> desc dept80;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
HIRE_DATE NOT NULL DATE
SQL> select table_name,column_name from user_tab_columns where table_name='DEPT80';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DEPT80 EMPLOYEE_ID
DEPT80 HIRE_DATE
SQL> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
DEPT80 1
SQL> alter table dept80 drop unused columns;
Table altered.
SQL> select * from user_unused_col_tabs;
no rows selected
重命名列
例子：重命名表 DEPT80 的列 EMPLOYEE_ID 为 EMPID
SQL> alter table dept80 rename column employee_id to empid;
Table altered.
管理约束
使用 ALTER TABLE 语句可以：
- 增加或者删除约束
- 启用或者禁用约束
- 使用 MODIFY 子句在字段级别增加 NOT NULL 约束
增加约束
语法
ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type (<column_name>);
例子：为表 EMP2 在列级增加主键约束
SQL> create table emp2 as select * from employees;
Table created.
SQL> alter table emp2 modify employee_id primary key;
Table altered.
例子：为表 DEPT4 在列级增加主键约束并指定约束名字
SQL> create table dept4 as select * from departments;
Table created.
SQL> alter table dept4 modify(constraint dept_dt_pk primary key(department_id));
Table altered.
SQL> alter table dept4 drop constraint dept_dt_pk;
Table altered.
SQL> alter table dept4 modify(department_id constraint dept_dt_pk primary key);
Table altered.
例子：为表 EMP2 在表级增加外键约束
SQL> alter table emp2 add constraint emp_mgr_fk
2 foreign key(manager_id) references emp2(employee_id);
Table altered.
例子：为表 EMP2 增加非空约束
SQL> alter table emp2 add constraint emp_salary_nn not null(salary);
alter table emp2 add constraint emp_salary_nn not null(salary)
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> alter table emp2 modify(salary constraint emp_salary_nn not null);
Table altered.
非空约束只能在列级增加。
删除约束
- 删除一个有外键引用的字段，需要加上 CASCADE CONSTRAINTS，才能删掉，对应的外键约束都会被删掉，但是子表的数据不会受到影响，要注意和前面删除记录用的 ON DELETE CASCADE 区分开来
- 如果是多字段的联合约束，在删除一个字段的时候，加了 CASCADE CONSTRAINTS，也会把多字段约束删掉
- 删除字段加 CASCADE CONSTRAINTS，只删除约束加 CASCADE
例子：删除约束
SQL> alter table emp2 drop constraint emp_mgr_fk;
Table altered.
例子：级联删除主键外键约束
SQL> drop table dept2;
Table dropped.
SQL> create table dept2 as select * from departments;
Table created.
SQL> alter table dept2 add constraint dept_dt_pk primary key(department_id);
Table altered.
SQL> alter table emp2 add constraint emp2_dt_fk foreign key(department_id) references dept2(department_id);
Table altered.
SQL> alter table dept2 drop primary key;
alter table dept2 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> alter table dept2 drop primary key cascade;
Table altered.
SQL> drop table dept2;
Table dropped.
例子：使用 CASCADE CONSTRAINTS 选项可以删除主键列及其外键约束
SQL> create table dept2 as select * from departments;
Table created.
在表级增加主键约束
SQL> alter table dept2 add constraint dept_dt_pk primary key(department_id);
Table altered.
在表emp2上面增加参考表dept2列department_id的外键
SQL> alter table emp2 add constraint emp_dt_fk foreign key(department_id) references dept2(department_id);
Table altered.
直接删除表dept2的父键列department_id报错
SQL> alter table dept2 drop(department_id);
alter table dept2 drop(department_id)
*
ERROR at line 1:
ORA-12992: cannot drop parent key column
加上cascade constraint就可以删除了
SQL> alter table dept2 drop(department_id) cascade constraint;
Table altered.
这两个表中列department_id上面的约束都被删掉了
SQL> select constraint_name,table_name,column_name from user_cons_columns where (table_name='EMP2' or table_name='DEPT2') and column_name='DEPARTMENT_ID';
no rows selected
停用和启用约束
停用约束：
- 可以在 CREATE TABLE 或者 ALTER TABLE 中使用 DISABLE 语句停用约束
- 使用 CASCADE 关键字停用依赖的约束
- 停用 UNIQUE 或者 PRIMARY KEY 约束会移除 UNIQUE INDEX
启用约束：
- 可以在 CREATE TABLE 或者 ALTER TABLE 中使用 ENABLE 语句启用约束
- 启用 UNIQUE 或者 PRIMARY KEY 约束会自动创建 UNIQUE INDEX
- 启用使用 CASCADE 选项停用的主键约束，不会同时启用依赖该主键约束的外键约束
- 启用 UNIQUE 或者 PRIMARY KEY 约束需要有对这个表创建 INDEX 的权限
例子：停用约束和启用约束
SQL> alter table emp2 add constraint emp2_dept2_fk foreign key(department_id) references dept2(department_id);
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
EMP2_DEPT2_FK EMP2 ENABLED
SQL> alter table emp2 disable constraint emp2_dept2_fk;
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
EMP2_DEPT2_FK EMP2 DISABLED
SQL> alter table emp2 enable constraint emp2_dept2_fk;
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraint_name='EMP2_DEPT2_FK';
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
EMP2_DEPT2_FK EMP2 ENABLED
例子：当父键使用 CASCADE 进行 DISABLE 后，子健也会被 DISABLE，但是当父键重新 ENABLE 后，子健不会自动 ENABLE，只能手动 ENABLE。同时在启用唯一键或者主键的时候，会自动创建唯一索引，禁用的时候，会自动删除唯一索引
SQL> select constraint_name,table_name,status from user_constraints where constraint_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
DEPT2_ID_PK DEPT2 ENABLED
EMP2_DEPT2_FK EMP2 ENABLED
SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';
INDEX_NAME UNIQUENES
------------------------------ ---------
DEPT2_ID_PK UNIQUE
SQL> alter table dept2 disable constraint dept2_id_pk;
alter table dept2 disable constraint dept2_id_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (HR.DEPT2_ID_PK) - dependencies exist
SQL> alter table dept2 disable constraint dept2_id_pk cascade;
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
DEPT2_ID_PK DEPT2 DISABLED
EMP2_DEPT2_FK EMP2 DISABLED
SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';
no rows selected
SQL> alter table dept2 enable constraint dept2_id_pk cascade;
alter table dept2 enable constraint dept2_id_pk cascade
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> alter table dept2 enable constraint dept2_id_pk;
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
DEPT2_ID_PK DEPT2 ENABLED
EMP2_DEPT2_FK EMP2 DISABLED
SQL> select index_name,uniqueness from user_indexes where table_name='DEPT2';
INDEX_NAME UNIQUENES
------------------------------ ---------
DEPT2_ID_PK UNIQUE
SQL> alter table emp2 enable constraint emp2_dept2_fk;
Table altered.
SQL> select constraint_name,table_name,status from user_constraints where constraInt_name like '%DEPT%' and (table_name='EMP2' or table_name='DEPT2');
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
DEPT2_ID_PK DEPT2 ENABLED
EMP2_DEPT2_FK EMP2 ENABLED
延迟约束
Deferrable 表示该约束是可延迟验证的。它有两个选项：
- INITIALLY IMMEDIATE（默认）：立即验证，执行完一个 SQL 后就进行验证；
- INITIALLY DEFERRED：延迟验证，当事务提交时或调用 SET CONSTRAINT [ALL | ] IMMEDIATE 语句时才验证。这两个区别是：INITIALLY DEFERRED，事务提交时验证不通过，则立即回滚事务；SET CONSTRAINT IMMEDIATE 时只验证，不回滚事务。
其中：
- INITIALLY IMMEDIATE 为默认值
- 如果没有指定 DEFERRABLE，则语句执行时会立即检查约束
- 如果创建的主键或者唯一键是延迟约束，那么自动生成的索引是非唯一索引
例子：创建延迟约束并验证
SQL> create table emp_new_sal(
2 salary number constraint sal_ck check(salary>1000) deferrable initially immediate,
3 bonus number constraint bonus_ck check(bonus>0) deferrable initially deferred);
Table created.
SQL> insert into emp_new_sal values(90,5);
insert into emp_new_sal values(90,5)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SAL_CK) violated
SQL> insert into emp_new_sal values(1100,-1);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.BONUS_CK) violated
SQL> set constraint sal_ck deferred;
Constraint set.
SQL> insert into emp_new_sal values(90,5);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.SAL_CK) violated
SQL> set constraint bonus_ck immediate;
Constraint set.
SQL> insert into emp_new_sal values(1100,-1);
insert into emp_new_sal values(1100,-1)
*
ERROR at line 1:
ORA-02290: check constraint (HR.BONUS_CK) violated
也可以使用下面的语句进行设置
SQL> alter session set constraints=immediate;
Session altered.
没有使用deferrable设置的约束不能重新设置为deferred
SQL> set constraint dept2_id_pk deferred;
set constraint dept2_id_pk deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
如果创建的主键或者唯一键是延迟约束，那么自动生成的索引是非唯一索引
SQL> alter table dept3 add constraint dept3_id_pk primary key(department_id) deferrable initially deferred;
Table altered.
SQL> select index_name,uniqueness from user_indexes where table_name='DEPT3';
INDEX_NAME UNIQUENES
------------------------------ ---------
DEPT3_ID_PK NONUNIQUE
而且禁用约束后，不会去删除这个索引
SQL> alter table dept3 disable constraint dept3_id_pk;
Table altered.
SQL> select index_name,uniqueness from user_indexes where table_name='DEPT3';
INDEX_NAME UNIQUENES
------------------------------ ---------
DEPT3_ID_PK NONUNIQUE
重命名约束
例子：重命名表 DEPT3 的约束 DEPT3_ID_PK 为 PK_DEPT3_ID
SQL> alter table dept3 rename constraint dept3_id_pk to pk_dept3_id;
Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='DEPT3';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT3_ID DEPT3
DROP TABLE
- 直接使用 DROP 删除表，只是将表放入了回收站，硬盘空间没有回收，在回收站进行了删除后，才回收硬盘空间
- 使用 PURGE 选项删除表，表示彻底删除，同时回收硬盘空间
- SYSTEM 表空间的表删除后不会进入回收站
例子：删除表 EMP2，进入回收站
SQL> drop table emp2;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16
例子：彻底删除表 DEPT80
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16
SQL> drop table dept80 purge;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP2 BIN$I7afhEQkJmvgU4rmqMCi5Q==$0 TABLE 2015-11-04:19:52:16
例子：清空回收站
SQL> purge recyclebin;
Recyclebin purged.
FLASHBACK TABLE
- 删除后进入回收站的表可以通过闪回恢复
- 闪回恢复后，依赖表的对象，比如约束、索引等，名字依然使用回收站里面的名字，而不是原来的名字
- 外键的约束不随表恢复
语法
FLASHBACK TABLE [schema.]table[,[ schema.]table ]...
TO { TIMESTAMP | SCN } expr
[ { ENABLE | DISABLE } TRIGGERS ];
例子：删除表后再恢复
SQL> drop table dept2;
Table dropped.
SQL> select original_name,operation,droptime from recyclebin;
ORIGINAL_NAME OPERATION DROPTIME
-------------------------------- --------- -------------------
DEPT2_NAME_IDX DROP 2015-11-04:20:03:02
DEPT2_ID_PK DROP 2015-11-04:20:03:02
DEPT2 DROP 2015-11-04:20:03:02
SQL> flashback table dept2 to before drop;
Flashback complete.
SQL> select original_name,operation,droptime from recyclebin;
no rows selected
SQL> select index_name,table_name from user_indexes where table_name='DEPT2';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
BIN$I7bK2SrbJu3gU4rmqMBihg==$0 DEPT2
BIN$I7bK2SraJu3gU4rmqMBihg==$0 DEPT2
EXTERNAL TABLE
- 主要用于外部文件的导入
- 通过目录对象指定外部表的路径，故需要先创建目录对象并赋予权限
- 外部表不占用数据库的空间
- 外部表不能创建索引，不支持 DML 操作
语法
CREATE TABLE <table_name>( <col_name> <datatype>, … )
ORGANIZATION EXTERNAL
(TYPE <access_driver_type>
DEFAULT DIRECTORY <directory_name>
ACCESS PARAMETERS
(… )
LOCATION ('<location_specifier>') ) REJECT LIMIT [0 | <number> | UNLIMITED];
例子：创建目录对象及外部表
先创建目录对象
SQL> conn / as sysdba
Connected.
SQL> !pwd
/home/oracle
SQL> !ls
database sql.txt
SQL> !mkdir emp_dir
SQL> !ls
database emp_dir sql.txt
SQL> create or replace directory emp_dir as '/home/oracle/emp_dir';
Directory created.
SQL> grant read,write on directory emp_dir to hr;
Grant succeeded.
SQL> select * from all_directories where directory_name like '%EMP%';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS EMP_DIR /home/oracle/emp_dir
在目录对象指定的目录下面创建一个文本文件
SQL> !vi emp_dir/emp.dat
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
最后创建外部表
SQL> conn hr/hr
Connected.
SQL> create table old_emp(
2 fname char(25),
3 lname char(25))
4 organization external(
5 type oracle_loader
6 default directory emp_dir
7 access parameters(
8 records delimited by newline
9 nobadfile
10 nologfile
11 fields terminated by ' '
12 (fname position(1:20) char,lname position(22:41) char))
13 location('emp.dat'))
14 parallel 5
15 reject limit 200;
Table created.
查询外部表
SQL> select * from old_emp;
FNAME LNAME
------------------------- -------------------------
Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
10 rows selected.
在外部表上面创建索引报错
SQL> create index old_emp_idx on old_emp(lname);
create index old_emp_idx on old_emp(lname)
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
在外部表上面执行DML操作报错
SQL> delete from old_emp;
delete from old_emp
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
通过外部表创建表
SQL> create table new_emp1 as select * from old_emp;
Table created.
通过外部表创建视图
SQL> create table old_empvu as select * from old_emp;
Table created.
Manipulating Large Data Sets
使用子查询
- 使用子查询插入数据
- 使用子查询查询数据
- 使用子查询更新数据
- 使用子查询删除数据
例子：使用子查询从其他表复制行进行插入，不需要使用 VALUES 关键字，字段要匹配
SQL> insert into sales_reps(id,name,salary,commission_pct)
2 select employee_id,last_name,salary,commission_pct
3 from employees where job_id like '%REP%';
33 rows created.
例子：将数据插入到一个子查询，实际上使用了子查询的表和字段，只插入了一行
SQL> create table emp13 as
2 select employee_id,last_name,email,hire_date,job_id,salary,department_id
3 from employees;
Table created.
SQL> insert into(
2 select employee_id,last_name,email,hire_date,job_id,salary,department_id
3 from emp13 where department_id=50)
4 values(99999,'Taylor','DTAYLOR',to_date('07-JUN-99','DD-MON-RR'),'ST_CLREK',5000,50);
1 row created.
例子：使用子查询作为数据来源，查询薪水比部门平均薪水大的员工
SQL> select a.last_name,a.salary,a.department_id,b.salavg
2 from employees a join (
3 select department_id,avg(salary) salavg from employees group by department_id) b
4 on a.department_id=b.department_id and a.salary>b.salavg;
LAST_NAME SALARY DEPARTMENT_ID SALAVG
------------------------- ---------- ------------- ----------
Fay 6000 10 5200
例子：使用其他表子查询的结果更新表的字段，更新表 EMP13 中字段 JOB_ID 等于表 EMPLOYEES 的 EMPLOYEE_ID 为 205 的 JOB_ID，字段 SALARY 等于表 EMPLOYEES 的 EMPLOYEE_ID 为 168 的 SALARY
SQL> update emp13 set
2 job_id=(select job_id from employees where employee_id=205),
3 salary=(select salary from employees where employee_id=168)
4 where employee_id=114;
1 row updated.
例子：使用其他表子查询的结果更新表的字段，更新表 EMP13 中字段 JOB_ID 等于表 EMPLOYEES 的 EMPLOYEE_ID 为 200 的 JOB_ID 的记录，将字段 DEPARTMENT_ID 等于表 EMPLOYEES 的 EMPLOYEE_ID 为 100 的 DEPARTMENT_ID
SQL> update emp13 set
2 department_id=(select department_id from employees where employee_id=100)
3 where job_id=(select job_id from employees where employee_id=200);
1 row updated.
例子：使用其他表子查询的结果删除表的记录，删除表 EMP13 中 DEPARTMENT_ID 等于表 DEPARTMENTS 中 DEPARTMENT_NAME 包含 PUBLIC 字符的 DEPARTMENT_ID 的记录
SQL> delete from emp13
2 where department_id=(select department_id from departments where department_name like '%Public%');
1 row deleted.
例子：通过使用 WITH CHECK OPTION 限定插入的值需要满足 WHERE 条件，与前面的视图一样
SQL> insert into(select employee_id,last_name,email,hire_date,job_id,salary from emp13 where department_id=50 with check option)
2 values(99998,'Smith','JSMITH',to_date('07-JUN-99','DD-MON-RR'),'ST-CLERK',5000);
insert into(select employee_id,last_name,email,hire_date,job_id,salary from emp13 where department_id=50 with check option)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
显式使用 Default
可以在插入和更新的时候使用 DEFAULT，如果没有定义 DEFAULT，那么就是空
例子：插入的时候使用 DEFAULT
SQL> create table deptm3 as select department_id,department_name,manager_id from departments;
Table created.
查看字段的默认值定义
SQL> select table_name,column_name,data_default from user_tab_columns where table_name='DEPTM3';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------ --------------------
DEPTM3 DEPARTMENT_ID
DEPTM3 DEPARTMENT_NAME
DEPTM3 MANAGER_ID
SQL> insert into deptm3(department_id,department_name,manager_id) values(300,'Engineering',default);
1 row created.
例子：更新的时候使用 DEFAULT
SQL> update deptm3 set manager_id=default where department_id=10;
1 row updated.
多表插入
- 一条 DML 语句向多表插入数据，减少查询次数，提高性能
- 常用于数据仓库对源数据进行抽取
MULTITABLE INSERT 语句类型：
- UNCONDITIONAL INSERT ALL：无条件全部插入，子查询返回的每一行都插入目标表
- CONDITIONAL INSERT ALL：有条件全部插入，子查询返回的每一行在满足特定条件下插入目标表，所有条件都要进行比较
- PIVOTING INSERT：UNCONDITIONAL INSERT ALL 的一种特殊情况，旋转插入，用于将非关系数据转换成关系数据
- CONDITIONAL INSERT FIRST：有条件插入第一个满足条件的，子查询返回的每一行依次比较条件，插入第一次满足条件的
语法
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery);
conditional_insert_clause：
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
多表插入的限制：
- 不能在视图或者物化视图上执行多表插入，只能在表上面执行多表插入语句
- 不能对远程表执行多表插入
- 多表插入时，最多只能插入 999 列
例子：无条件全部插入，将表 EMPLOYEES 中 EMPLOYEE_ID 大于 200 的记录分别插入到表 SAL_HISTORY 和表 MGR_HISTORY
先创建2个空表
SQL> create table sal_history as
2 select employee_id empid,hire_date hiredate,salary sal from employees where 1=0;
Table created.
SQL> create table mgr_history as
2 select employee_id empid,manager_id mgr,salary sal from employees where 1=0;
Table created.
再进行插入
SQL> insert all
2 into sal_history values(empid,hiredate,sal)
3 into mgr_history values(empid,mgr,sal)
4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
5 from employees where employee_id>200;
14 rows created.
例子：有条件全部插入，将表 EMPLOYEES 中 EMPLOYEE_ID 大于 200 的记录中 SALARY 大于 10000 的记录插入到表 SAL_HISTORY，MANAGER_ID 大于 200 的记录插入到表 MGR_HISTORY
先回滚
SQL> rollback;
Rollback complete.
SQL> insert all
2 when sal>10000 then into sal_history values(empid,hiredate,sal)
3 when mgr>200 then into mgr_history values(empid,mgr,sal)
4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
5 from employees where employee_id>200;
5 rows created.
例子：有条件插入第一个满足条件的，先获取人员表中每个部门的薪水总和及最大入职时间，如果薪水总和大于 25000，则插入表 SPECIAL_SAL，如果最大入职时间包含字符 00，则插入表 HIREDATE_HISTORY_00，如果最大入职时间包含字符 99，则插入表 HIREDATE_HISTORY_99，如果以上条件都不满足，则插入表 HIREDATE_HISTORY
先回滚
SQL> rollback;
Rollback complete.
创建需要的表
SQL> create table special_sal as
2 select department_id deptid,salary sal from employees where 1=0;
Table created.
SQL> create table hiredate_history_00 as
2 select department_id deptid,hire_date hiredate from employees where 1=0;
Table created.
SQL> create table hiredate_history_99 as
2 select department_id deptid,hire_date hiredate from employees where 1=0;
Table created.
SQL> create table hiredate_history as
2 select * from hiredate_history_99;
Table created.
再进行插入
SQL> insert first
2 when sal>25000 then into special_sal values(deptid,sal)
3 when hiredate like ('%00%') then into hiredate_history_00 values(deptid,hiredate)
4 when hiredate like ('%99%') then into hiredate_history_99 values(deptid,hiredate)
5 else into hiredate_history values(deptid,hiredate)
6 select department_id deptid,sum(salary) sal,max(hire_date) hiredate
7 from employees group by department_id;
12 rows created.
例子：旋转插入，将非关系数据表转换为关系表
SQL> create table sales_source_data(
2 employee_id number(6),
3 week_id number(2),
4 sales_mon number(8,2),
5 sales_tue number(8,2),
6 sales_wed number(8,2),
7 sales_thur number(8,2),
8 sales_fri number(8,2));
Table created.
SQL> insert into sales_source_data values(178,6,1750,2200,1500,1500,3000);
1 row created.
SQL> select * from sales_source_data;
EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI
----------- ---------- ---------- ---------- ---------- ---------- ----------
178 6 1750 2200 1500 1500 3000
SQL> create table sales_info(
2 employee_id number(6),
3 week number(2),
4 sales number(8,2));
Table created.
SQL> insert all
2 into sales_info values(employee_id,week_id,sales_mon)
3 into sales_info values(employee_id,week_id,sales_tue)
4 into sales_info values(employee_id,week_id,sales_wed)
5 into sales_info values(employee_id,week_id,sales_thur)
6 into sales_info values(employee_id,week_id,sales_fri)
7 select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri from sales_source_data;
5 rows created.
MERGE
MERGE 语句是 Oracle9i 新增的语法，用来合并 UPDATE 和 INSERT 语句。 通过 MERGE 语句，根据一张表或多表联合查询的连接条件对另外一张表进行查询，连接条件匹配上的进行 UPDATE，无法匹配的执行 INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于 INSERT ＋ UPDATE。通过 MERGE 能够在一个 SQL 语句中对一个表同时执行 INSERT 和 UPDATE 操作，经常用于将生产表的数据更新到历史表。
语法
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
例子：比较表 employees 和表 emp13，如果 employee_id 相等，则更新表 emp13 的记录等于表 employees 的对应记录，如果不相等，则将表 employees 表中的记录插入到表 emp13 中
SQL> create table emp13 as
2 select employee_id,last_name,salary,department_id from employees where 1=0;
Table created.
SQL> select * from emp13;
no rows selected
SQL> merge into emp13 n
2 using employees e on(n.employee_id=e.employee_id)
3 when matched then
4 update set
5 n.last_name=e.last_name,
6 n.salary=e.salary,
7 n.department_id=e.department_id
8 when not matched then
9 insert values(e.employee_id,e.last_name,e.salary,e.department_id);
108 rows merged.
删除表emp13一部分记录，修改emp13一部分记录
SQL> delete from emp13 where rownum<50;
49 rows deleted.
SQL> update emp13 set last_name='abc',salary=0;
59 rows updated.
再次执行刚才的merge语句，又和刚才一样了。
SQL> merge into emp13 n
2 using employees e on(n.employee_id=e.employee_id)
3 when matched then
4 update set
5 n.last_name=e.last_name,
6 n.salary=e.salary,
7 n.department_id=e.department_id
8 when not matched then
9 insert values(e.employee_id,e.last_name,e.salary,e.department_id);
108 rows merged.
还可以在匹配的时候添加条件
SQL> merge into emp13 n
2 using employees e on(n.employee_id=e.employee_id)
3 when matched then
4 update set
5 n.last_name=e.last_name,
6 n.salary=e.salary,
7 n.department_id=e.department_id
8 where e.department_id=50
9 when not matched then
10 insert values(e.employee_id,e.last_name,e.salary,e.department_id)
11 where e.department_id=50;
46 rows merged.
还可以在匹配的时候删除记录
SQL> create table orders_master(
2 order_id number,
3 order_total number);
Table created.
SQL> create table monthly_orders(
2 order_id number,
3 order_total number);
Table created.
SQL> insert into orders_master values(1,1000);
1 row created.
SQL> insert into orders_master values(2,2000);
1 row created.
SQL> insert into orders_master values(3,3000);
1 row created.
SQL> insert into orders_master values(4,null);
1 row created.
SQL> insert into monthly_orders values(2,2500);
1 row created.
SQL> insert into monthly_orders values(3,null);
1 row created.
SQL> select * from orders_master;
ORDER_ID ORDER_TOTAL
---------- -----------
1 1000
2 2000
3 3000
4
SQL> select * from monthly_orders;
ORDER_ID ORDER_TOTAL
---------- -----------
2 2500
3
SQL> merge into orders_master o
2 using monthly_orders m on(o.order_id=m.order_id)
3 when matched then
4 update set o.order_total=m.order_total
5 delete where(m.order_total is null)
6 when not matched then
7 insert values(m.order_id,m.order_total);
2 rows merged.
SQL> select * from orders_master;
ORDER_ID ORDER_TOTAL
---------- -----------
1 1000
2 2500
4
更新的时候不能更新用于连接条件的列
SQL> merge into emp13 n
2 using employees e on(n.employee_id=e.employee_id)
3 when matched then
4 update set
5 n.last_name=e.last_name,
6 n.salary=e.salary,
7 n.department_id=e.department_id,
8 n.employee_id=e.employee_id
9 when not matched then
10 insert values(e.employee_id,e.last_name,e.salary,e.department_id);
using employees e on(n.employee_id=e.employee_id)
*
ERROR at line 2:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "N"."EMPLOYEE_ID"
Flashback Version Query
闪回版本查询用于追踪数据变化，只返回已经提交的事务
例子：查询人员薪水的修改记录
SQL> create table employees3 as select * from employees;
Table created.
SQL> select salary from employees3 where employee_id=107;
SALARY
----------
4200
SQL> update employees3 set salary=salary*1.30 where employee_id=107;
1 row updated.
SQL> commit;
Commit complete.
SQL> select salary from employees3 versions between scn minvalue and maxvalue where employee_id=107;
SALARY
----------
5460
4200
SQL> select versions_starttime "start_date",versions_endtime "end_date",versions_operation,salary
2 from employees3 versions between scn minvalue and maxvalue where last_name='Lorentz';
start_date end_date V SALARY
------------------------------ ------------------------------ - ----------
06-NOV-15 08.18.52 AM U 5460
06-NOV-15 08.18.52 AM 4200
Time Zones
时区
时区是地理上的概念，把全球分成 24 个时区，每一个小时一个时区，定义了某一时刻不同地点的时间。
例子：查看操作系统的时区
[root@oracletest ~]# date -R
Sat, 07 Nov 2015 11:08:16 +0800
如何计算区时
计算的区时=已知区时-（已知区时的时区-要计算区时的时区），（注：东时区为正，西时区为负）。
下面举例加以说明：
例 1：已知东京（东九区）时间为 5 月 1 日 12:00，求北京（东八区）的区时？
北京时间=12:00-（9-8）=11:00（即北京时间为 5 月 1 日 11:00）。
例 2：已知北京时间为 5 月 1 日 12:00，求伦敦（中时区）的区时？
伦敦时间=12:00-（8-0)=4:00（即伦敦时间为 5 月 1 日 4:00）。
例 3：已知北京时间为 5 月 1 日 12:00，求纽约（西五区）的区时。
纽约时间=12:00-[8-(-5)]=-1:00+24:00-1 天=23:00（即纽约时间为 4 月 30 日的 23:00）。（注：当算出的区时为负数时，应加上 24:00，日期减一天，即从 5 月 1 日变为 4 月 30 日）。
例 4：已知纽约时间为 5 月 1 日 12:00，求东京的区时？
东京时间=12:00-[(-5)-9]=26:00-24:00+1 天=2:00）即东京时间为 5 月 2 日 2:00）。（注：当算出的区时大于或等于 24:00 时，应减去 24:00，日期加一天，即从 5 月 1 日变为 5 月 2 日）。
TIME_ZONE 会话参数
Oracle 的时区可以分为两种，一种是数据库的时区，一种是 Session 时区。数据库的时区在创建数据库时可以通过在 CREATE DATABASE 语句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 来指定。创建之后，可以通过 ALTER DATABASE 来修改。Database Time Zone 只和 TIMESTAMP WITH LOCAL TIME ZONE 数据类型相关！其实数据库 Timezone 只是一个计算的标尺，TIMESTAMP WITH LOCAL TIME ZONE 数据类型从客户端传入数据库后，转为数据库时区存入数据库。在需要进行相关计算的时候，Oracle 先把时间转换为标准时间 (UTC)，完成计算后再把结果转换为数据库时区的时间保存到数据库。关于 TIMESTAMP WITH LOCAL TIME ZONE 数据类型的详细信息，请参考随后相关部分。
Session 的时区是根据客户端的时区来决定的，连接以后也可以通过 ALTER SESSION SET TIME_ZONE 来改变，改变的值可以设置为：
- 一个绝对的偏移值
- 数据库时区
- 操作系统的时区
- 时区的命名区域
例子：使用 SESSIONTIMEZONE 函数查看当前 Session 时区
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
例子：使用 DBTIMEZONE 函数查看数据库的时区
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
例子：修改当前 Session 的时区为-5：00 时区
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00
例子：修改当前 Session 的时区为数据库的时区
SQL> alter session set time_zone=dbtimezone;
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
例子：修改当前 Session 的时区为本地操作系统的时区
SQL> alter session set time_zone=local;
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
例子：修改当前 Session 的时区为某一个时区命名区域，通过 V$TIMEZONE_NAMES 查询时区命名区域
SQL> select tzname from v$timezone_names where lower(tzname) like '%york%';
TZNAME
--------------------
America/New_York
SQL> alter session set time_zone='America/New_York';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
America/New_York
CURRENT_DATE 函数
CURRENT_DATE 函数返回当前会话下面不同时区对应的日期和时间（数据类型为 DATE）
SQL> select current_date from dual;
CURRENT_DATE
------------
06-NOV-15
如果要使返回的信息包含时间，需要修改nls_date_format参数
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select current_date from dual;
CURRENT_DATE
-----------------------
06-nov-2015 23:04:29
例子：修改时区后查看 CURRENT_DATE 的值
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE
-------------------- ----------------------- -----------------------
-05:00 07-nov-2015 13:46:20 07-nov-2015 00:46:20
SQL> alter session set time_zone='+08:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE
-------------------- ----------------------- -----------------------
+08:00 07-nov-2015 13:47:10 07-nov-2015 13:47:10
CURRENT_TIMESTAMP 函数
返回当前会话下面不同时区对应的日期、时间（包含微秒及上下午）及时区（格式固定，不能修改，数据类型为 TIMESTAMP WITH TIME ZONE）
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_timestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP
-------------------- ----------------------- ----------------------------------------
-05:00 07-nov-2015 13:49:41 07-NOV-15 12.49.41.656386 AM -05:00
SQL> alter session set time_zone='+08:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_timestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP
-------------------- ----------------------- ----------------------------------------
+08:00 07-nov-2015 13:52:00 07-NOV-15 01.52.00.084663 PM +08:00
LOCALTIMESTAMP 函数
返回当前会话下面不同时区对应的日期、时间（包含微秒及上下午，不带时区）（数据类型为 TIMESTAMP）
SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
-------------------- ----------------------- ----------------------- ---------------------------------------- ------------------------------
+08:00 07-nov-2015 13:59:24 07-nov-2015 13:59:24 07-NOV-15 01.59.24.639690 PM +08:00 07-NOV-15 01.59.24.639690 PM
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
-------------------- ----------------------- ----------------------- ---------------------------------------- ------------------------------
-05:00 07-nov-2015 13:59:47 07-nov-2015 00:59:47 07-NOV-15 12.59.47.116923 AM -05:00 07-NOV-15 12.59.47.116923 AM
TIMESTAMP 数据类型
- 是 DATE 数据类型的扩展
- 可以存储微秒
- 三种类型：TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE
| Data Type | Fields |
|---|---|
| TIMESTAMP | Year, Month, Day, Hour, Minute, Second with fractional seconds |
| TIMESTAMP WITH TIME ZONE | Same as the TIMESTAMP data type; also includes:TIMEZONE_HOUR, and TIMEZONE_MINUTE or TIMEZONE_REGION |
| TIMESTAMP WITH LOCAL TIME ZONE | Same as the TIMESTAMP data type; also includes a time zone offset in its value |
当你不需要保存时区/地区信息的时候，选择使用 TIMESTAMP 数据类型，因为它一般需要 7-11bytes 的存储空间，可以节省空间。
当你需要保存时区/地区信息的时候，请选择使用 TIMESTAMP WITH TIME ZONE 数据类型。比如一个跨国银行业务应用系统，需要精确纪录每一笔交易的时间和地点(时区)，在这种情况下就需要纪录时区相关信息。因为需要纪录时区相关信息，所以需要多一些的存储空间，一般需要 13bytes。
当你并不关心操作发生的具体地点，而只是关心操作是在你当前时区的几点发生的时候，选择使用 TIMESTAMP WITH LOCAL TIME ZONE。比如一个全球统一的 change control system。用户可能只关心某某操作是在我的时间几点发生的(比如中国用户看到的是北京时间 8:00am，而伦敦的用户看到的是 0:00am)。记住，此类行不保存时区/地区信息，因此如果需要保存相关信息的要慎重！
TIMESTAMP
TIMESTAMP 除了年月日时分秒外，还包含微秒，默认精度是 6 位，最高可以到 9 位。
例子：比较 DATE 数据类型和 TIMESTAMP 数据类型的不同
SQL> drop table emp5;
Table dropped.
SQL> create table emp5 as select * from employees;
Table created.
SQL> select hire_date from emp5 where employee_id=100;
HIRE_DATE
------------
17-JUN-03
SQL> alter table emp5 modify hire_date timestamp;
Table altered.
SQL> select hire_date from emp5 where employee_id=100;
HIRE_DATE
---------------------------------------------------------------------------
17-JUN-03 12.00.00.000000 AM
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE 除了包含 TIMESTAMP 的信息外，还带有时区。
例子：创建一个表 WEB_ORDERS，存储来自全球的订单信息，包含一个 TIMESTAMP WITH TIME ZONE 类型字段，并插入数据
SQL> create table web_orders(
2 ord_id number primary key,
3 order_date timestamp with time zone);
Table created.
SQL> desc web_orders;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORD_ID NOT NULL NUMBER
ORDER_DATE TIMESTAMP(6) WITH TIME ZONE
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- ----------------------------------------
+08:00 07-NOV-15 10.15.54.762046 PM +08:00
SQL> insert into web_orders values(1,current_timestamp);
1 row created.
SQL> select * from web_orders;
ORD_ID ORDER_DATE
---------- ----------------------------------------
1 07-NOV-15 10.16.46.396682 PM +08:00
SQL> commit;
Commit complete.
另外开一个窗口，模拟来自另外一个地方的订单
SQL> conn hr/hr
Connected.
修改时区
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
07-NOV-15 09.22.49.860132 AM -05:00
插入数据
SQL> insert into web_orders values(2,current_timestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from web_orders;
ORD_ID ORDER_DATE
---------- ----------------------------------------
1 07-NOV-15 10.16.46.396682 PM +08:00
2 07-NOV-15 09.23.46.179299 AM -05:00
可以看到时区信息及对应的日期时间保存到记录里面了，在任何客户端查询都不会根据客户端的时区而变化。
TIMESTAMP WITH LOCAL TIME ZONE
不存储时区信息，时间根据客户端的时区变化而变化。
例子：创建一个表，存储全球的快递投递时间信息，包含一个 TIMESTAMP WITH LOCAL TIME ZONE 类型字段，并插入数据
SQL> create table shipping(delivery_time timestamp with local time zone);
Table created.
SQL> desc shipping;
Name Null? Type
----------------------------------------- -------- ----------------------------
DELIVERY_TIME TIMESTAMP(6) WITH LOCAL TIME
ZONE
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
----------------------------------------
07-NOV-15 10.42.50.916509 PM +08:00
SQL> insert into shipping values(current_timestamp);
SQL> select * from shipping;
DELIVERY_TIME
---------------------------------------------------------------------------
07-NOV-15 10.43.13.949702 PM
SQL> alter session set time_zone='+06:00';
Session altered.
SQL> select * from shipping;
DELIVERY_TIME
---------------------------------------------------------------------------
07-NOV-15 08.43.13.949702 PM
INTERVAL 数据类型
存储两个日期时间的间隔，有以下两类
| Data Type | Description |
|---|---|
| INTERVAL YEAR TO MONTH | Stored as an interval of years and months |
| INTERVAL DAY TO SECOND | Stored as an interval of days, hours, minutes, and seconds |
INTERVAL 类型的值域
| INTERVAL Field | Valid Values for Interval |
|---|---|
| YEAR | Any positive or negative integer |
| MONTH | 00 to 11 |
| DAY | Any positive or negative integer |
| HOUR | 00 to 23 |
| MINUTE | 00 to 59 |
| SECOND | 00 to 59.9(N) where 9(N) is precision |
INTERVAL YEAR TO MONTH
YEAR 后面可以带精度，默认是 2 位。
语法
INTERVAL YEAR [(year_precision)] TO MONTH
示例
'312-2' assigned to INTERVAL YEAR(3) TO MONTH
Indicates an interval of 312 years and 2 months
'312-0' assigned to INTERVAL YEAR(3) TO MONTH
Indicates 312 years and 0 months
'0-3' assigned to INTERVAL YEAR TO MONTH
Indicates an interval of 3 months
例子：创建一个表，保存产品的保质期
SQL> create table warranty(
2 prod_id number,
3 warranty_time interval year(3) to month);
Table created.
SQL> desc warranty;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NUMBER
WARRANTY_TIME INTERVAL YEAR(3) TO MONTH
SQL> insert into warranty values(123,interval '8' month);
1 row created.
SQL> insert into warranty values(155,interval '200' year(3));
1 row created.
SQL> insert into warranty values(678,'200-11');
1 row created.
SQL> select * from warranty;
PROD_ID WARRANTY_TIME
---------- --------------------
123 +000-08
155 +200-00
678 +200-11
INTERVAL DAY TO SECOND
DAY 后面可以带精度，默认是 2 位。
语法
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
示例
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
Indicates 4 days, 5 hours, 12 minutes, 10 seconds,
and 222 thousandths of a second.
INTERVAL '4 5:12' DAY TO MINUTE
Indicates 4 days, 5 hours and 12 minutes.
INTERVAL '400 5' DAY(3) TO HOUR
Indicates 400 days 5 hours.
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
indicates 11 hours, 12 minutes, and 10.2222222 seconds.
例子 ：创建一个表，保存实验的间隔时间
SQL> create table lab(
2 exp_id number,
3 test_time interval day(2) to second);
Table created.
SQL> desc lab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EXP_ID NUMBER
TEST_TIME INTERVAL DAY(2) TO SECOND(6)
SQL> insert into lab values(100012,'90 00:00:00');
1 row created.
SQL> insert into lab values(56098,interval '6 03:30:16' day to second);
1 row created.
SQL> select * from lab;
EXP_ID TEST_TIME
---------- ------------------------------
100012 +90 00:00:00.000000
56098 +06 03:30:16.000000
间隔类型单独没有什么用处，一般是与日期时间进行运算
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select sysdate ,sysdate+test_time from lab;
SYSDATE SYSDATE+TEST_TIME
----------------------- -----------------------
08-NOV-2015 20:06:19 06-FEB-2016 20:06:19
08-NOV-2015 20:06:19 14-NOV-2015 23:36:35
EXTRACT 函数
从时间日期或者间隔值中抽取特定的时间日期值。
例子：查询当前的年份
SQL> select sysdate,extract(year from sysdate) from dual;
SYSDATE EXTRACT(YEARFROMSYSDATE)
----------------------- ------------------------
08-NOV-2015 20:16:05 2015
也可以使用to_char函数
SQL> select sysdate,to_char(sysdate,'yyyy') from dual;
SYSDATE TO_C
----------------------- ----
08-NOV-2015 20:16:45 2015
例子：查询人员入职的月份
SQL> select last_name,hire_date,extract(month from hire_date) from employees where manager_id=100;
LAST_NAME HIRE_DATE EXTRACT(MONTHFROMHIRE_DATE)
------------------------- ----------------------- ---------------------------
Hartstein 17-FEB-2004 00:00:00 2
也可以使用to_char函数
SQL> select last_name,hire_date,to_char(hire_date,'mm') from employees where manager_id=100;
LAST_NAME HIRE_DATE TO
------------------------- ----------------------- --
Hartstein 17-FEB-2004 00:00:00 02
TZ_OFFSET 函数
例子：使用该函数将时区区域命名转换成时区值
SQL> select tz_offset('US/Eastern') from dual;
TZ_OFFS
-------
-05:00
SQL> select tz_offset('Canada/Yukon') from dual;
TZ_OFFS
-------
-08:00
SQL> select tz_offset('Europe/London') from dual;
TZ_OFFS
-------
+00:00
前面讲了通过v$timezone_names数据字典视图查询有哪些时区命名区域
SQL> select * from v$timezone_names where tzname like '%Chongqing%';
TZNAME TZABBREV
-------------------- --------------------
Asia/Chongqing LMT
SQL> select tz_offset('Asia/Chongqing') from dual;
TZ_OFFS
-------
+08:00
FROM_TZ 函数
例子：将 TIMESTAMP 转换成 TIMESTAMP WITH TIME ZONE，这个函数用得很少
SQL> select from_tz(timestamp '2000-03-28 08:00:00','3:00') from dual;
FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')
---------------------------------------------------------------------------
28-MAR-00 08.00.00.000000000 AM +03:00
SQL> select from_tz(timestamp '2000-03-28 08:00:00','Australia/North') from dual;
FROM_TZ(TIMESTAMP'2000-03-2808:00:00','AUSTRALIA/NORTH')
---------------------------------------------------------------------------
28-MAR-00 08.00.00.000000000 AM AUSTRALIA/NORTH
TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 函数
使用 TO_TIMESTAMP 函数将字符串转换成 TIMESTAMP 类型，使用 TO_TIMESTAMP_TZ 函数将字符串转换成 TIMESTAMP WITH TIME ZONE 类型，带时区。
例子：将字符串转换成 TIMESTAMP 类型
SQL> select to_timestamp('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;
TO_TIMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')
---------------------------------------------------------------------------
01-DEC-00 11.00.00.000000000 AM
例子：将字符串转换成 TIMESTAMP WITH TIME ZONE 类型
SQL> select to_timestamp_tz('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
TO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
---------------------------------------------------------------------------
01-DEC-99 11.00.00.000000000 AM -08:00
TO_YMINTERVAL 函数
将字符串转换成年月间隔类型。
例子：将入职时间加上 1 年 2 个月
SQL> select hire_date,hire_date+to_yminterval('01-02') as hire_date_yminterval from employees where department_id=20;
HIRE_DATE HIRE_DATE_YMINTERVAL
----------------------- -----------------------
17-FEB-2004 00:00:00 17-APR-2005 00:00:00
TO_DSINTERVAL 函数
将字符串转换成天秒间隔类型。
例子 ：将入职时间加上 100 天 10 小时
SQL> select last_name,to_char(hire_date,'mm-dd-yy hh:mi:ss') hire_date,to_char(hire_date+to_dsinterval('100 10:00:00'),'mm-dd-yy hh:mi:ss') hiredate2 from employees;
LAST_NAME HIRE_DATE HIREDATE2
------------------------- ----------------- -----------------
OConnell 06-21-07 12:00:00 09-29-07 10:00:00
Hierarchical Retrieval
概念
人员表里面有主键 EMPLOYEE_ID 和外键 MANAGER_ID，这种单个表中的行之间的层次关系可以构成一个树（Tree），因而在行之间就存在父-子关系。通过树结构中的父-子关系，可以控制层次关系的开始点和方向（自顶向下/自底向上），分层查询就是用来解决这种父子关系问题的。
语法
SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
START WITH 子句指定条件以及起始点。
CONNECT BY PRIOR 指定层次的方向，如果 PRIOR 后面的字段为父键，就是从上至下，如果 PRIOR 后面的字段为子健，就是从下至上。
例子：从人员编号 101 开始，查找上级
SQL> select employee_id,last_name,job_id,manager_id from employees
2 start with employee_id=101
3 connect by prior manager_id=employee_id;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES
例子：从人员名字 King 开始，查找下级
SQL> select last_name||' reports to '||prior last_name "Walk Top Down" from employees
2 start with last_name='King'
3 connect by prior employee_id=manager_id;
Walk Top Down
--------------------------------------------------------------
King reports to
King reports to
Kochhar reports to King
格式化
可以使用 LEVEL 伪列来表示分层的级别，同时使用 lpad 函数格式化输出。
例子：左填充来格式化分层查询的输出
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart
2 from employees
3 start with first_name='Steven' and last_name='King'
4 connect by prior employee_id=manager_id;
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
......
107 rows selected.
修剪分支
通过增加限定条件，对分层查询的分支进行修剪，有 2 种修剪方式：
- 使用 WHERE子句删除节点，但是保留该节点的下级
- 在 CONNECT BY 后面增加条件，删除整个分支
例子：对上面的分层查询结果删除 'Higgins' 这个人员，但保留他的下级
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 where last_name<>'Higgins'
3 start with first_name='Steven' and last_name='King'
4 connect by prior employee_id=manager_id;
ORG_CHART
--------------------------------------------------------------------------------
King
106 rows selected.
例子：对上面的分层查询结果删除 'Higgins' 这个分支，包括 'Higgins' 这个节点
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 start with first_name='Steven' and last_name='King'
3 connect by prior employee_id=manager_id and last_name<>'Higgins';
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
105 rows selected.
例子：只显示第一个和第二个节点
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 start with first_name='Steven' and last_name='King'
3 connect by prior employee_id=manager_id and level<3;
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
__De Haan
Regular Expression
正则表达式是 oracle 从 10g 开始推出的技术，Oracle SQL 和 PL/SQL 都支持，通过函数的方式实现正则表达式的功能，使用模式串来进行搜索，修改，进行处理。
| Symbol | Description |
|---|---|
* | Matches zero or more occurrences |
| | Alteration operator for specifying alternative matches |
^/$ | Matches the start-of-line/end-of-line |
[ ] | Bracket expression for a matching list matching any one of the expressions represented in the list |
{m} | Matches exactly m times |
{m,n} | Matches at least m times but no more than n times |
[: :] | Specifies a character class and matches any character in that class |
\ | Can have 4 different meanings: 1. Stand for itself. 2. Quote the next character. 3. Introduce an operator. 4. Do nothing. |
+ | Matches one or more occurrences |
? | Matches zero or one occurrence |
. | Matches any character in the supported character set, except NULL |
() | Grouping expression, treated as a single subexpression |
[==] | Specifies equivalence classes |
\n | Back-reference expression |
[..] | Specifies one collation element, such as a multicharacter element |
正则表达式函数
| Function Name | Description |
|---|---|
| REGEXP_LIKE | Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching |
| REGEXP_REPLACE | Searches for a regular expression pattern and replaces it with a replacement string |
| REGEXP_INSTR | Searches for a given string for a regular expression pattern and returns the position where the match is found |
| REGEXP_SUBSTR | Searches for a regular expression pattern within a given string and returns the matched substring |
| REGEXP_COUNT | Returns the number of times a patten match is found in an input string |
REGEXP_LIKE
使用正则表达式进行匹配。
语法
REGEXP_LIKE(srcstr, pattern [,match_option])
例子：查找人员姓以 'S' 开头，以 'n' 结尾，中间包含 'teve' 或者 'tephe' 的人员
SQL> select first_name,last_name from employees
2 where regexp_like(first_name,'^Ste(v|ph)en$');
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
Steven Markle
Stephen Stiles
REGEXP_REPLACE
使用正则表达式进行匹配替换。
语法
REGEXP_REPLACE(srcstr, pattern [,replacestr [, position
[, occurrence [, match_option]]]])
例子：为国家表里面的国家名字字段的每个字母后面增加一个空格
SQL> select regexp_replace(country_name,'(.)','\1 ') "regexp_replace" from countries;
regexp_replace
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
例子：将人员表里面的电话号码格式的第二个点改为横杠，前三位用括号括起来
SQL> SELECT phone_number,REGEXP_REPLACE(phone_number,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') "PHONE NUMBER" FROM employees;
PHONE_NUMBER PHONE NUMBER
-------------------- --------------------
650.507.9833 (650) 507-9833
REGEXP_INSTR
使用正则表达式返回匹配的位置。
语法
REGEXP_INSTR (srcstr, pattern [, position [, occurrence
[, return_option [, match_option]]]])
例子：查询位置表里面街道地址字段第一个非字母的位置大于 1 的街道地址以及第一个非字母的位置
SQL> select street_address,regexp_instr(street_address,'[^[:alpha:]]') loc from locations
2 where regexp_instr(street_address,'[^[:alpha:]]')>1;
STREET_ADDRESS LOC
---------------------------------------- ----------
Magdalen Centre, The Oxford Science Park 9
Schwanthalerstr. 7031 16
Rua Frei Caneca 1360 4
Murtenstrasse 921 14
Pieter Breughelstraat 837 7
Mariano Escobedo 9991 8
6 rows selected.
例子：使用分组表达式
SQL> select regexp_instr('0123456789','(123)(4(56)(78))',1,1,0,'i',1) "Position" from dual;
Position
----------
2
其中：
第一个参数 0123456789：为源字符串
第二个参数（123）（4（56）（78））：需要匹配的子表达式，共四个，第一个为 123，第二个为 45678，第三个为 56，第四个为 78
第三个参数 1：开始搜索的位置，从第一个源字符串的第一个字符开始
第四个参数 1：出现的次数，第一次出现，第一个找到的匹配项
第五个参数 0：匹配字符串第一个字符的位置（如果是 1，表示匹配字符串后面一个字符的位置）
第六个参数 i：忽略大小写（如果是 c，表示不忽略大小写）
第七个参数 1：指定要匹配的是第几个子表达式，这里是第一个
REGEXP_SUBSTR
使用正则表达式返回匹配的字符串。
语法
REGEXP_SUBSTR (srcstr, pattern [, position
[, occurrence [, match_option]]])
例子：查询位置表里面地址的街道名字
SQL> select regexp_substr(street_address,' [^ ]+ ') "Road" from locations;
Road
--------------------------------------------------------------------------------
Via
Calle
例子：查找指定字符串里面匹配的子字符串
SQL> select regexp_substr('acgctgcactgca','acg(.*)gca',1,1,'i',1) "Value" from dual;
Value
-------
ctgcact
其中：
第一个参数 acgctgcactgca：为源字符串
第二个参数 acg(.*)gca：需要匹配的子表达式
第三个参数 1：开始搜索的位置，从第一个源字符串的第一个字符开始
第四个参数 1：出现的次数，第一次出现，第一个找到的匹配项
第五个参数 i：忽略大小写（如果是 c，表示不忽略大小写）
第六个参数 1：指定要匹配的是第几个子表达式，这里是第一个
REGEXP_COUNT
使用正则表达式返回匹配的次数。
语法
REGEXP_COUNT (srcstr, pattern [, position
[, occurrence [, match_option]]])
例子：计算匹配源字符串的次数
SQL> select regexp_count('123123123123','123',2,'i') as count from dual;
COUNT
----------
3
其中：
第一个参数 123123123123：为源字符串
第二个参数 123：需要匹配的子表达式
第三个参数 2：开始搜索的位置，从第一个源字符串的第二个字符开始
第四个参数 i：忽略大小写（如果是 c，表示不忽略大小写）
正则表达式定义约束
在定义约束的时候，可以使用正则表达式进行匹配。
SQL> create table emp8 as select email from employees;
Table created.
SQL> alter table emp8 modify(email constraint ck_email_emp8 check(regexp_like(email,'@')) novalidate);
Table altered.
novalidate表示不对现有的数据进行约束检查
SQL> insert into emp8 values('ChrisP2creme.com');
insert into emp8 values('ChrisP2creme.com')
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_EMAIL_EMP8) violated
Analytic Functions
概念
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the
analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.Analytic functions are the last set of operations performed in a query except for the final
ORDERBYclause. All joins and allWHERE,GROUPBY, andHAVINGclauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list orORDERBYclause.Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
- 对一组数据进行计算，返回多行
- 不需要进行多表联合，提高性能
- 在 JOIN，WHERE，GROUP BY，HAVING 子句之后进行处理
- 只能位于 SELECT 或者 ORDER BY 子句
语法
analytic_function
analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
arguments
Analytic functions take 0 to 3 arguments. The arguments can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that data type. The return type is also that data type, unless otherwise noted for an individual function.
- 常用 analytic_function
- AVG,MAX,MIN,SUM,COUNT
- DENSE_RANK,RANK,ROW_NUMBER, CUME_DIST
- LAG,LEAD
- FIRST,LAST
- NTILE
- FIRST_VALUE/LAST_VALUE
- LISTAGG
- RATIO_TO_REPORT
- arguments 个数：0~3
- arguments 类型：数字类型或可以隐式转为为数字类型的非数字类型
analytic_clause
Use
OVERanalytic_clauseto indicate that the function operates on a query result set. This clause is computed after theFROM,WHERE,GROUPBY, andHAVINGclauses. You can specify analytic functions with this clause in the select list orORDERBYclause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.Notes on the analytic_clause:
The following notes apply to the
analytic_clause:
- You cannot nest analytic functions by specifying any analytic function in any part of the
analytic_clause. However, you can specify an analytic function in a subquery and compute another analytic function over it.- You can specify
OVERanalytic_clausewith user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.
- 在 FROM,WHERE,GROUP BY 和 HAVING 子句之后进行计算
- 在 SELECT 和 ORDER BY 子句指定带 analytic_clause 的分析函数
query_partition_clause
Use the
PARTITIONBYclause to partition the query result set into groups based on one or morevalue_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.To use the
query_partition_clausein an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in themodel_column_clauses) or a partitioned outer join (in theouter_join_clause), use the lower branch of the syntax (with parentheses).You can specify multiple analytic functions in the same query, each with the same or different
PARTITIONBYkeys.If the objects being queried have the parallel attribute, and if you specify an analytic function with the
query_partition_clause, then the function computations are parallelized as well.Valid values of
value_exprare constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
- 根据 expr 对查询结果进行分组
- 忽略该语句则查询结果为一个分组
- 分析函数使用上面的分支，不带括号
- expr 可以是常量，字段，非分析函数，函数表达式
order_by_clause
Use the
order_by_clauseto specify how data is ordered within a partition. For all analytic functions you can order the values in a partition on multiple keys, each defined by avalue_exprand each qualified by an ordering sequence.Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the
order_by_clauseresults in identical values for multiple rows, the function behaves as follows:
CUME_DIST,DENSE_RANK,NTILE,PERCENT_RANK, andRANKreturn the same result for each of the rows.ROW_NUMBERassigns each row a distinct value even if there is a tie based on theorder_by_clause. The value is based on the order in which the row is processed, which may be nondeterministic if theORDERBYdoes not guarantee a total ordering.- For all other analytic functions, the result depends on the window specification. If you specify a logical window with the
RANGEkeyword, then the function returns the same result for each of the rows. If you specify a physical window with theROWSkeyword, then the result is nondeterministic.Restrictions on the ORDER BY Clause
The following restrictions apply to the
ORDERBYclause:
When used in an analytic function, the
order_by_clausemust take an expression (expr). TheSIBLINGSkeyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise thisorder_by_clauseis the same as that used to order the overall query or subquery.An analytic function that uses the
RANGEkeyword can use multiple sort keys in itsORDERBYclause if it specifies any of the following windows:
RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW. The short form of this isRANGEUNBOUNDEDPRECEDING.RANGEBETWEENCURRENTROWANDUNBOUNDEDFOLLOWINGRANGEBETWEENCURRENTROWANDCURRENTROWRANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWINGWindow boundaries other than these four can have only one sort key in the
ORDERBYclause of the analytic function. This restriction does not apply to window boundaries specified by theROWkeyword.ASC | DESC Specify the ordering sequence (ascending or descending).
ASCis the default.NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLSLASTis the default for ascending order, andNULLSFIRSTis the default for descending order.Analytic functions always operate on rows in the order specified in the
order_by_clauseof the function. However, theorder_by_clauseof the function does not guarantee the order of the result. Use theorder_by_clauseof the query to guarantee the final result ordering.
- 指定分区中数据的排序方式
- 当排序结果有相同值时：
- DENSE_RANK, RANK 返回相同值
- ROW_NUMBER 返回不同值，根据处理行的顺序排序
- 限制
- 在分析函数中只能使用 expr；position 和 c_alias 无效
- 在分析函数中使用 RANGE 关键字且使用以下窗口就可以使用多个排序键
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(RANGE UNBOUNDED PRECEDING)
- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- RANGE BETWEEN CURRENT ROW AND CURRENT ROW
- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
windowing_clause
Some analytic functions allow the
windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow thewindowing_clauseare followed by an asterisk (*).ROWS | RANGE These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
ROWSspecifies the window in physical units (rows).RANGEspecifies the window as a logical offset.You cannot specify this clause unless you have specified the
order_by_clause. Some window boundaries defined by theRANGEclause let you specify only one expression in theorder_by_clause. Refer to "Restrictions on the ORDER BY Clause".The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the
order_by_clauseto achieve this unique ordering.BETWEEN ... AND Use the
BETWEEN...ANDclause to specify a start point and end point for the window. The first expression (beforeAND) defines the start point and the second expression (afterAND) defines the end point.If you omit
BETWEENand specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.UNBOUNDED PRECEDING Specify
UNBOUNDEDPRECEDINGto indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.UNBOUNDED FOLLOWING Specify
UNBOUNDEDFOLLOWINGto indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.CURRENT ROW As a start point,
CURRENTROWspecifies that the window begins at the current row or value (depending on whether you have specifiedROWorRANGE, respectively). In this case the end point cannot bevalue_exprPRECEDING.As an end point,
CURRENTROWspecifies that the window ends at the current row or value (depending on whether you have specifiedROWorRANGE, respectively). In this case the start point cannot bevalue_exprFOLLOWING.value_expr PRECEDING or value_expr FOLLOWING
For
RANGEorROW:
- If
value_exprFOLLOWINGis the start point, then the end point must bevalue_exprFOLLOWING.- If
value_exprPRECEDINGis the end point, then the start point must bevalue_exprPRECEDING.If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
See Also:
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals
If you specified
ROWS:
value_expris a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.- If
value_expris part of the start point, then it must evaluate to a row before the end point.If you specified
RANGE:
value_expris a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Refer to "Literals" for information on interval literals.- You can specify only one expression in the
order_by_clause.- If
value_exprevaluates to a numeric value, then theORDERBYexprmust be a numeric orDATEdata type.- If
value_exprevaluates to an interval value, then theORDERBYexprmust be aDATEdata type.If you omit the
windowing_clauseentirely, then the default isRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW.
支持 windowing_clause 的常用分析函数有：AVG,MAX,MIN,SUM,COUNT
ROWS | RANGE
- 为每行定义一个窗口用于计算函数结果
- ROWS：以行指定窗口
- RANGE：以逻辑偏移量指定窗口
BETWEEN ... AND
- 指定窗口的起始点和结束点
- 省略 BETWEEN，则指定的点为起始点，结束点默认为当前行(current row)
只有指定了 order_by_clause 才能使用 windowing_clause
如果省略了 windowing_clause，则默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
UNBOUNDED PRECEDING：从分区的第一行开始，起始点
UNBOUNDED FOLLOWING：到分区的最后一行结束，结束点
CURRENT ROW
- 作为起始点时，CURRENT ROW 指定窗口开始于当前行或者某个值（取决于使用 ROW 还是 RANGE），这时结束点不能是 value_expr PRECEDING
- 作为结束点时，CURRENT ROW 指定窗口结束于当前行或者某个值（取决于使用 ROW 还是 RANGE），这时开始点不能是 value_expr FOLLOWING
value_expr PRECEDING or value_expr FOLLOWING
- 对于 RANGE 或者 ROW
- 如果起始点是 value_expr FOLLOWING，则结束点必须是 value_expr FOLLOWING
- 如果结束点是 value_expr PRECEDING，则起始点必须是 value_expr PRECEDING
- 如果指定了 ROWS
- value_expr 是一个物理偏移量。必须是常量或表达式, 并且必须计算为正数数值
- 如果 value_expr 是起始点的一部分，则必须位于结束点之前的行
- 如果指定了 RANGE
- value_expr 是一个逻辑偏移量。必须是一个常量或表达式, 计算结果为正值数值或间隔文本
- 在 order_by_clause 只能使用一个排序键
- 如果 value_expr 为数值，则 ORDER BY expr 必须为数字或日期类型
- 如果 value_expr 为间隔值，则 ORDER BY expr 必须为日期类型
- 对于 RANGE 或者 ROW
Reporting
Calculating shares, for example, market share. Works with these functions:
SUM,AVG,MIN,MAX,COUNT(with/withoutDISTINCT),VARIANCE,STDDEV,RATIO_TO_REPORT, and new statistical functions. Note that theDISTINCTkeyword may be used in those reporting functions that supportDISTINCTin aggregate mode.
语法：
{SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE ... }
([ALL | DISTINCT] {value expression1 [,...] | *})
OVER ([PARTITION BY value expression2[,...]])
例子：查询人员信息以及公司平均薪水，最小薪水，最大薪水，薪水总计以及人数
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 avg(salary) over() avg_sal,
6 max(salary) over() max_sal,
7 min(salary) over() min_sal,
8 sum(salary) over() sum_sal,
9 count(salary) over() count_sal
10 from employees
11 where department_id <= 40
12 order by department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY AVG_SAL MAX_SAL MIN_SAL SUM_SAL COUNT_SAL
----------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
200 Whalen 10 4400 5480 13000 2500 54800 10
201 Hartstein 20 13000 5480 13000 2500 54800 10
202 Fay 20 6000 5480 13000 2500 54800 10
114 Raphaely 30 11000 5480 13000 2500 54800 10
115 Khoo 30 3100 5480 13000 2500 54800 10
116 Baida 30 2900 5480 13000 2500 54800 10
117 Tobias 30 2800 5480 13000 2500 54800 10
118 Himuro 30 2600 5480 13000 2500 54800 10
119 Colmenares 30 2500 5480 13000 2500 54800 10
203 Mavris 40 6500 5480 13000 2500 54800 10
10 rows selected.
例子：查询人员信息以及各部门平均薪水，最小薪水，最大薪水，薪水总计以及人数
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 avg(salary) over(partition by department_id) avg_sal,
6 max(salary) over(partition by department_id) max_sal,
7 min(salary) over(partition by department_id) min_sal,
8 sum(salary) over(partition by department_id) sum_sal,
9 count(salary) over(partition by department_id) count_sal
10 from employees
11 where department_id <= 40
12 order by department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY AVG_SAL MAX_SAL MIN_SAL SUM_SAL COUNT_SAL
----------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
200 Whalen 10 4400 4400 4400 4400 4400 1
201 Hartstein 20 13000 9500 13000 6000 19000 2
202 Fay 20 6000 9500 13000 6000 19000 2
114 Raphaely 30 11000 4150 11000 2500 24900 6
115 Khoo 30 3100 4150 11000 2500 24900 6
116 Baida 30 2900 4150 11000 2500 24900 6
117 Tobias 30 2800 4150 11000 2500 24900 6
118 Himuro 30 2600 4150 11000 2500 24900 6
119 Colmenares 30 2500 4150 11000 2500 24900 6
203 Mavris 40 6500 6500 6500 6500 6500 1
10 rows selected.
例子：查询部门最高薪水的员工信息，不使用分析函数
SQL> set autotrace on
SQL> select employee_id, last_name, e1.department_id, job_id, salary
2 from employees e1
3 where e1.salary = (select max(salary)
4 from employees e2
5 where e1.department_id = e2.department_id)
6 order by department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY
----------- ---------- ------------- ---------- ----------
200 Whalen 10 AD_ASST 4400
201 Hartstein 20 MK_MAN 13000
114 Raphaely 30 PU_MAN 11000
203 Mavris 40 HR_REP 6500
121 Fripp 50 ST_MAN 8200
103 Hunold 60 IT_PROG 9000
204 Baer 70 PR_REP 10000
145 Russell 80 SA_MAN 14000
100 King 90 AD_PRES 24000
108 Greenberg 100 FI_MGR 12008
205 Higgins 110 AC_MGR 12008
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 298340369
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 44 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 44 | 5 (20)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 44 | 5 (20)| 00:00:01 |
| 4 | VIEW | VW_SQ_1 | 1 | 16 | 4 (25)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 7 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 28 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(MAX("SALARY")>0)
8 - access("E1"."DEPARTMENT_ID"="ITEM_1")
9 - filter("E1"."SALARY"="MAX(SALARY)")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
1178 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed
例子：查询部门最高薪水的员工信息，使用分析函数
SQL> select emp.*
2 from (select employee_id,
3 last_name,
4 department_id,
5 job_id,
6 salary,
7 max(salary) over(partition by department_id) max_sal
8 from employees
9 order by department_id) emp
10 where salary = max_sal
11 order by department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY MAX_SAL
----------- ---------- ------------- ---------- ---------- ----------
200 Whalen 10 AD_ASST 4400 4400
201 Hartstein 20 MK_MAN 13000 13000
114 Raphaely 30 PU_MAN 11000 11000
203 Mavris 40 HR_REP 6500 6500
121 Fripp 50 ST_MAN 8200 8200
103 Hunold 60 IT_PROG 9000 9000
204 Baer 70 PR_REP 10000 10000
145 Russell 80 SA_MAN 14000 14000
100 King 90 AD_PRES 24000 24000
108 Greenberg 100 FI_MGR 12008 12008
205 Higgins 110 AC_MGR 12008 12008
178 Grant SA_REP 7000 7000
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 720055818
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 6848 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 107 | 6848 | 3 (0)| 00:00:01 |
| 2 | WINDOW SORT | | 107 | 2996 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2996 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALARY"="MAX_SAL")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1312 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
对比执行计划，使用分析函数后性能有提高。
例子：查询人员信息以及各部门各职位薪水总计和各部门薪水总计
SQL> select employee_id,
2 last_name,
3 department_id,
4 job_id,
5 salary,
6 sum(salary) over(partition by department_id, job_id) job_sal1,
7 sum(salary) over(partition by department_id) dept_sal2
8 from employees
9 where department_id <= 40
10 order by department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY JOB_SAL1 DEPT_SAL2
----------- ---------- ------------- ---------- ---------- ---------- ----------
200 Whalen 10 AD_ASST 4400 4400 4400
201 Hartstein 20 MK_MAN 13000 13000 19000
202 Fay 20 MK_REP 6000 6000 19000
115 Khoo 30 PU_CLERK 3100 13900 24900
119 Colmenares 30 PU_CLERK 2500 13900 24900
116 Baida 30 PU_CLERK 2900 13900 24900
117 Tobias 30 PU_CLERK 2800 13900 24900
118 Himuro 30 PU_CLERK 2600 13900 24900
114 Raphaely 30 PU_MAN 11000 11000 24900
203 Mavris 40 HR_REP 6500 6500 6500
10 rows selected.
例子：查询各部门各职位薪水总计以及各部门薪水总计
SQL> select department_id,
2 job_id,
3 sum(salary) job_sal1,
4 sum(sum(salary)) over(partition by department_id) dept_sal2
5 from employees
6 group by department_id, job_id
7 order by department_id;
DEPARTMENT_ID JOB_ID JOB_SAL1 DEPT_SAL2
------------- ---------- ---------- ----------
10 AD_ASST 4400 4400
20 MK_MAN 13000 19000
20 MK_REP 6000 19000
30 PU_CLERK 13900 24900
30 PU_MAN 11000 24900
40 HR_REP 6500 6500
50 SH_CLERK 64300 156400
50 ST_CLERK 55700 156400
50 ST_MAN 36400 156400
60 IT_PROG 28800 28800
70 PR_REP 10000 10000
80 SA_MAN 61000 304500
80 SA_REP 243500 304500
90 AD_PRES 24000 58000
90 AD_VP 34000 58000
100 FI_ACCOUNT 39600 51608
100 FI_MGR 12008 51608
110 AC_ACCOUNT 8300 20308
110 AC_MGR 12008 20308
SA_REP 7000 7000
20 rows selected.
例子：查询各职位薪水总计占所在部门薪水总计超过 50%的职位
SQL> select emp.*, 100 * round(job_sal1 / dept_sal2, 2) || '%' Percent
2 from (select department_id,
3 job_id,
4 sum(salary) job_sal1,
5 sum(sum(salary)) over(partition by department_id) dept_sal2
6 from employees
7 group by department_id, job_id) emp
8 where job_sal1 > dept_sal2 * 0.5;
DEPARTMENT_ID JOB_ID JOB_SAL1 DEPT_SAL2 PERCENT
------------- ---------- ---------- ---------- -----------------------------------------
10 AD_ASST 4400 4400 100%
20 MK_MAN 13000 19000 68%
30 PU_CLERK 13900 24900 56%
40 HR_REP 6500 6500 100%
60 IT_PROG 28800 28800 100%
70 PR_REP 10000 10000 100%
80 SA_REP 243500 304500 80%
90 AD_VP 34000 58000 59%
100 FI_ACCOUNT 39600 51608 77%
110 AC_MGR 12008 20308 59%
SA_REP 7000 7000 100%
11 rows selected.
SQL> select emp.*
2 from (select department_id,
3 job_id,
4 sum(salary) job_sal1,
5 sum(sum(salary)) over(partition by department_id) dept_sal2,
6 ratio_to_report(sum(salary)) over(partition by department_id) job_to_dept_sal3
7 from employees
8 group by department_id, job_id) emp
9 where job_to_dept_sal3 > 0.5;
DEPARTMENT_ID JOB_ID JOB_SAL1 DEPT_SAL2 JOB_TO_DEPT_SAL3
------------- ---------- ---------- ---------- ----------------
10 AD_ASST 4400 4400 1
20 MK_MAN 13000 19000 .684210526
30 PU_CLERK 13900 24900 .558232932
40 HR_REP 6500 6500 1
60 IT_PROG 28800 28800 1
70 PR_REP 10000 10000 1
80 SA_REP 243500 304500 .799671593
90 AD_VP 34000 58000 .586206897
100 FI_ACCOUNT 39600 51608 .767322896
110 AC_MGR 12008 20308 .591294071
SA_REP 7000 7000 1
11 rows selected.
例子：查询每个人的薪水占部门薪水合计及公司薪水总计的百分比
SQL> select employee_id,
2 last_name,
3 department_id,
4 hire_date,
5 salary,
6 ratio_to_report(salary) over(partition by department_id) as pct1,
7 ratio_to_report(salary) over() as pct2
8 from employees
9 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID HIRE_DATE SALARY PCT1 PCT2
----------- ---------- ------------- ------------------ ---------- ---------- ----------
200 Whalen 10 17-SEP-03 4400 1 .080291971
201 Hartstein 20 17-FEB-04 13000 .684210526 .237226277
202 Fay 20 17-AUG-05 6000 .315789474 .109489051
114 Raphaely 30 07-DEC-02 11000 .441767068 .200729927
115 Khoo 30 18-MAY-03 3100 .124497992 .056569343
116 Baida 30 24-DEC-05 2900 .116465863 .052919708
117 Tobias 30 24-JUL-05 2800 .112449799 .051094891
118 Himuro 30 15-NOV-06 2600 .104417671 .047445255
119 Colmenares 30 10-AUG-07 2500 .100401606 .045620438
203 Mavris 40 07-JUN-02 6500 1 .118613139
10 rows selected.
Windowing
Calculating cumulative and moving aggregates. Works with these functions:
SUM,AVG,MIN,MAX,COUNT,VARIANCE,STDDEV,FIRST_VALUE,LAST_VALUE, and new statistical functions. Note that theDISTINCTkeyword is not supported in windowing functions except forMAXandMIN.
Cumulative Aggregate Function
例子：查询按部门的薪水合计及公司薪水总计
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) dept_sal_cum1,
6 sum(salary) over(order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400 54800
201 Hartstein 20 13000 19000 54800
202 Fay 20 6000 19000 54800
114 Raphaely 30 11000 24900 54800
115 Khoo 30 3100 24900 54800
116 Baida 30 2900 24900 54800
117 Tobias 30 2800 24900 54800
118 Himuro 30 2600 24900 54800
119 Colmenares 30 2500 24900 54800
203 Mavris 40 6500 6500 54800
10 rows selected.
与下面的SQL等价
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id) dept_sal_cum1,
6 sum(salary) over() dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400 54800
201 Hartstein 20 13000 19000 54800
202 Fay 20 6000 19000 54800
114 Raphaely 30 11000 24900 54800
115 Khoo 30 3100 24900 54800
116 Baida 30 2900 24900 54800
117 Tobias 30 2800 24900 54800
118 Himuro 30 2600 24900 54800
119 Colmenares 30 2500 24900 54800
203 Mavris 40 6500 6500 54800
10 rows selected.
例子：查询按部门的薪水累计及不按部门的薪水累计
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) dept_sal_cum1,
6 sum(salary) over(order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400 4400
201 Hartstein 20 13000 13000 17400
202 Fay 20 6000 19000 23400
114 Raphaely 30 11000 11000 34400
115 Khoo 30 3100 14100 37500
116 Baida 30 2900 17000 40400
117 Tobias 30 2800 19800 43200
118 Himuro 30 2600 22400 45800
119 Colmenares 30 2500 24900 48300
203 Mavris 40 6500 6500 54800
10 rows selected.
与下面的SQL等价
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id order by department_id ROWS UNBOUNDED PRECEDING) dept_sal_cum1,
6 sum(salary) over(order by department_id ROWS UNBOUNDED PRECEDING) dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400 4400
201 Hartstein 20 13000 13000 17400
202 Fay 20 6000 19000 23400
114 Raphaely 30 11000 11000 34400
115 Khoo 30 3100 14100 37500
116 Baida 30 2900 17000 40400
117 Tobias 30 2800 19800 43200
118 Himuro 30 2600 22400 45800
119 Colmenares 30 2500 24900 48300
203 Mavris 40 6500 6500 54800
10 rows selected.
例子：查询按部门分区从分区第一行到本行前一行的累计和到本行后一行的累计
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) dept_sal_cum1,
6 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400
201 Hartstein 20 13000 19000
202 Fay 20 6000 13000 19000
114 Raphaely 30 11000 14100
115 Khoo 30 3100 11000 17000
116 Baida 30 2900 14100 19800
117 Tobias 30 2800 17000 22400
118 Himuro 30 2600 19800 24900
119 Colmenares 30 2500 22400 24900
203 Mavris 40 6500 6500
10 rows selected.
Moving Aggregate Function
例子：查询按部门分区从分区前一行到本行的累计以及从本行到后一行的累计
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) dept_sal_cum1,
6 sum(salary) over(partition by department_id order by department_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) dept_sal_cum2
7 from employees
8 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
----------- ---------- ------------- ---------- ------------- -------------
200 Whalen 10 4400 4400 4400
201 Hartstein 20 13000 13000 19000
202 Fay 20 6000 19000 6000
114 Raphaely 30 11000 11000 14100
115 Khoo 30 3100 14100 6000
116 Baida 30 2900 6000 5700
117 Tobias 30 2800 5700 5400
118 Himuro 30 2600 5400 5100
119 Colmenares 30 2500 5100 2500
203 Mavris 40 6500 6500 6500
10 rows selected.
Centered Aggregate
例子：查询按照入职日期分组的薪水合计，以及入职日期相邻 1 天的人员的平均薪水
SQL> select hire_date,
2 sum(salary) as sum_sal1,
3 avg(sum(salary)) over(order by hire_date range between interval '1' day preceding and interval '1' day following) as centered_1_day_avg
4 from employees
5 where department_id <= 40
6 group by hire_date;
HIRE_DATE SUM_SAL1 CENTERED_1_DAY_AVG
------------------ ---------- ------------------
07-JUN-02 6500 6500
07-DEC-02 11000 11000
18-MAY-03 3100 3100
17-SEP-03 4400 4400
17-FEB-04 13000 13000
24-JUL-05 2800 2800
17-AUG-05 6000 6000
24-DEC-05 2900 2900
15-NOV-06 2600 2600
10-AUG-07 2500 2500
10 rows selected.
Ranking
Calculating ranks, percentiles, and n-tiles of the values in a result set.
- RANK()：返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
- DENSE_RANK()：返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
- ROW_NUMBER()：返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
- CUME_DIST()：计算指定值所处的位置，计算方法为相对位置/总行数，返回值(0,1]。
- PERCENT_RANK()：与 CUME_DIST() 类似，计算方法为 (相对位置-1)/(总行数-1)，因此第一行的结果为 0，返回值[0,1]。
- NTILE()：一个分区划分为一定数量的 Buckets，每个 Buckets 中的记录数量是相等的（差额不超过 1），为每个 Bucket 赋值一个 Bucket Number。
例子：查询按部门的薪水从低到高排名人员信息
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 RANK() over(partition by department_id order by salary) rank,
6 DENSE_RANK() over(partition by department_id order by salary) dense_rank,
7 ROW_NUMBER() over(partition by department_id order by salary) row_number
8 from employees
9 where department_id = 50 and salary <= 2500.00;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY RANK DENSE_RANK ROW_NUMBER
----------- ---------- ------------- ---------- ---------- ---------- ----------
132 Olson 50 2100 1 1 1
128 Markle 50 2200 2 2 2
136 Philtanker 50 2200 2 2 3
127 Landry 50 2400 4 3 4
135 Gee 50 2400 4 3 5
131 Marlow 50 2500 6 4 6
140 Patel 50 2500 6 4 7
144 Vargas 50 2500 6 4 8
182 Sullivan 50 2500 6 4 9
191 Perkins 50 2500 6 4 10
10 rows selected.
例子：查询每个部门的薪水排名前三名人员信息
SQL> select e.*
2 from (select employee_id,
3 last_name,
4 department_id,
5 salary,
6 DENSE_RANK() over(partition by department_id order by salary desc) dense_rank
7 from employees) e
8 where dense_rank <= 3
9 and department_id <= 50;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY DENSE_RANK
----------- ---------- ------------- ---------- ----------
200 Whalen 10 4400 1
201 Hartstein 20 13000 1
202 Fay 20 6000 2
114 Raphaely 30 11000 1
115 Khoo 30 3100 2
116 Baida 30 2900 3
203 Mavris 40 6500 1
121 Fripp 50 8200 1
120 Weiss 50 8000 2
122 Kaufling 50 7900 3
10 rows selected.
例子：计算每个人在本部门按照薪水排列中的相对位置
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 cume_dist() over(partition by department_id order by salary) as cume_dist
6 from employees
7 where department_id <= 40;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY CUME_DIST
----------- ---------- ------------- ---------- ----------
200 Whalen 10 4400 1
202 Fay 20 6000 .5
201 Hartstein 20 13000 1
119 Colmenares 30 2500 .166666667
118 Himuro 30 2600 .333333333
117 Tobias 30 2800 .5
116 Baida 30 2900 .666666667
115 Khoo 30 3100 .833333333
114 Raphaely 30 11000 1
203 Mavris 40 6500 1
10 rows selected.
例子：计算每个人在本部门按照薪水排列中的相对位置
SQL> select department_id,
2 last_name,
3 salary,
4 percent_rank() over(partition by department_id order by salary) as percent_rank
5 from employees
6 where department_id <= 40;
DEPARTMENT_ID LAST_NAME SALARY PERCENT_RANK
------------- ---------- ---------- ------------
10 Whalen 4400 0
20 Fay 6000 0
20 Hartstein 13000 1
30 Colmenares 2500 0
30 Himuro 2600 .2
30 Tobias 2800 .4
30 Baida 2900 .6
30 Khoo 3100 .8
30 Raphaely 11000 1
40 Mavris 6500 0
10 rows selected.
例子：查询人员信息及其对应的薪水等级，将薪水分为 5 个等级
SQL> select employee_id,
2 last_name,
3 salary,
4 ntile(5) over(order by salary desc) as quartile
5 from employees
6 where department_id <= 40;
EMPLOYEE_ID LAST_NAME SALARY QUARTILE
----------- ---------- ---------- ----------
201 Hartstein 13000 1
114 Raphaely 11000 1
203 Mavris 6500 2
202 Fay 6000 2
200 Whalen 4400 3
115 Khoo 3100 3
116 Baida 2900 4
117 Tobias 2800 4
118 Himuro 2600 5
119 Colmenares 2500 5
10 rows selected.
LAG/LEAD
Finding a value in a row a specified number of rows from a current row.
语法：
{LAG | LEAD} ( value_expr [, offset] [, default] ) [RESPECT NULLS|IGNORE NULLS]
OVER ( [query_partition_clause] order_by_clause )
offsetis an optional parameter and defaults to 1.defaultis an optional parameter and is the value returned ifoffsetfalls outside the bounds of the table or partition. WhenIGNORENULLSis specified, the value returned will be from a row at a specified lag or lead offset after ignoring rows with NULLs.
- LAG 和 LEAD 函数可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干 offset 的某个行的某个列(不用结果集的自关联）
- LAG，LEAD 分别是向前，向后
- LAG 和 LEAD 有三个参数，第一个参数是列名，第二个参数是偏移的 Offset，第三个参数是 超出记录窗口时的默认值）
- LAG(expression<,offset><,default>)函数可以访问组内当前行之前的行，而 LEAD(expression<,offset><,default>)函数则正相反，可以访问组内当前行之后的行。其中，Offset 是正整数，默认为 1。因组内第一个条记录没有之前的行，最后一行没有之后的行，default 就是用于处理这样的信息，默认为空。注意：这 2 个函数必须指定 ORDER BY 字句。
例子：查询人员薪水及其前面入职人员的薪水和后面入职人员的薪水
SQL> SELECT hire_date,
2 last_name,
3 salary,
4 LAG(salary, 1, 0) OVER(ORDER BY hire_date) AS prev_sal,
5 LEAD(salary, 1, 0) OVER(ORDER BY hire_date) AS next_sal
6 FROM employees
7 WHERE job_id = 'PU_CLERK'
8 ORDER BY hire_date;
HIRE_DATE LAST_NAME SALARY PREV_SAL NEXT_SAL
------------------ ---------- ---------- ---------- ----------
18-MAY-03 Khoo 3100 0 2800
24-JUL-05 Tobias 2800 3100 2900
24-DEC-05 Baida 2900 2800 2600
15-NOV-06 Himuro 2600 2900 2500
10-AUG-07 Colmenares 2500 2600 0
FIRST/LAST
First or last value in an ordered group.
语法：
aggregate_function KEEP ( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]
- FIRST 和 LAST 函数有 OVER 子句就是分析函数，没有就是聚合函数。
- 函数的参数必须是数字类型（或者其他类型可转为数字类型），返回相同类型。
- aggregate_function 可以是 MIN,MAX,SUM,AVG,COUNT,VARIANCE,STDDEV。
例子：查询人员信息及其所在部门的最低和最高薪水
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 min(salary) keep(dense_rank first order by salary) over(partition by department_id) "worst",
6 max(salary) keep(dense_rank last order by salary) over(partition by department_id) "best"
7 from employees
8 where department_id <= 40
9 order by department_id, salary, last_name;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY worst best
----------- ---------- ------------- ---------- ---------- ----------
200 Whalen 10 4400 4400 4400
202 Fay 20 6000 6000 13000
201 Hartstein 20 13000 6000 13000
119 Colmenares 30 2500 2500 11000
118 Himuro 30 2600 2500 11000
117 Tobias 30 2800 2500 11000
116 Baida 30 2900 2500 11000
115 Khoo 30 3100 2500 11000
114 Raphaely 30 11000 2500 11000
203 Mavris 40 6500 6500 6500
10 rows selected.
FIRST_VALUE/LAST_VALUE
The
FIRST_VALUEandLAST_VALUEfunctions allow you to select the first and last rows from a window. These rows are especially valuable because they are often used as the baselines in calculations.
语法：
FIRST_VALUE|LAST_VALUE ( <expr> ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause )
例子：查询人员信息及其所在部门最低薪水和最高薪水人员姓名
SQL> select employee_id,
2 last_name,
3 department_id,
4 salary,
5 first_value(last_name) over(partition by department_id order by salary) as worst,
6 last_value(last_name) over(partition by department_id order by salary rows between unbounded preceding and unbounded following) as best
7 from employees
8 where department_id <= 40
9 order by department_id, salary;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID SALARY WORST BEST
----------- ---------- ------------- ---------- ------------------------- -------------------------
200 Whalen 10 4400 Whalen Whalen
202 Fay 20 6000 Fay Hartstein
201 Hartstein 20 13000 Fay Hartstein
119 Colmenares 30 2500 Colmenares Raphaely
118 Himuro 30 2600 Colmenares Raphaely
117 Tobias 30 2800 Colmenares Raphaely
116 Baida 30 2900 Colmenares Raphaely
115 Khoo 30 3100 Colmenares Raphaely
114 Raphaely 30 11000 Colmenares Raphaely
203 Mavris 40 6500 Mavris Mavris
10 rows selected.
LISTAGG
orders data within each group based on the
ORDERBYclause and then concatenates the values of the measure column.
语法：
LISTAGG (<expr> [, <delimiter>) WITHIN GROUP (ORDER BY <oby_expression_list>)
例子：查询每个部门所有人员姓名并按照薪水从低到高排序，排序结果拼接起来
SQL> select department_id,
2 listagg(last_name, ',') within group(order by salary) name
3 from employees
4 where department_id <= 40
5 group by department_id;
DEPARTMENT_ID NAME
------------- --------------------------------------------------
10 Whalen
20 Fay,Hartstein
30 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
40 Mavris
SQL> select department_id,
2 last_name,
3 salary,
4 listagg(last_name, ',') within group(order by salary) over(partition by department_id) name
5 from employees
6 where department_id <= 40;
DEPARTMENT_ID LAST_NAME SALARY NAME
------------- ---------- ---------- --------------------------------------------------
10 Whalen 4400 Whalen
20 Fay 6000 Fay,Hartstein
20 Hartstein 13000 Fay,Hartstein
30 Colmenares 2500 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
30 Himuro 2600 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
30 Tobias 2800 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
30 Baida 2900 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
30 Khoo 3100 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
30 Raphaely 11000 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
40 Mavris 6500 Mavris
10 rows selected.
Hypothetical Rank
The rank or percentile that a row would have if inserted into a specified data set.
语法：
[RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST]( constant expression [, ...] )
WITHIN GROUP ( ORDER BY order by expression [ASC|DESC] [NULLS FIRST|NULLS LAST][, ...] )
例子：假如 50 部门新来一位工资 4000 的员工，计算该员工在 50 部门薪水的位置
SQL> select rank(50, 4000) within group(order by department_id, salary) rank,
2 dense_rank(50, 4000) within group(order by department_id, salary) dense_rank,
3 percent_rank(50, 4000) within group(order by department_id, salary) percent_rank,
4 cume_dist(50, 4000) within group(order by department_id, salary) cume_dist
5 from employees
6 where department_id = 50;
RANK DENSE_RANK PERCENT_RANK CUME_DIST
---------- ---------- ------------ ----------
38 18 .822222222 .847826087