Oracle/SQL基础

Oracle学习笔记

表结构

image-20210128160037149

01 基本操作

SELECT e.last_name, d.department_name, l.city
   FROM   employees e, departments d, locations l
   WHERE  e.department_id = d.department_id
   AND    d.location_id = l.location_id;
   
   
   
--2.	查询90号部门员工的job_id和90号部门的location_id
select job_id , location_id 
from EMPLOYEES e, DEPARTMENTS d
where e.department_id=d.department_id
and e.department_id=90;

--3.	选择所有有奖金的员工的
--last_name , department_name , location_id , city
select last_name , department_name , l.location_id , city
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.LOCATION_ID
where e.commission_pct is not null;


--4.	选择city在Toronto工作的员工的
--last_name , job_id , department_id , department_name
select last_name,job_id,d.department_id,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
where Lower(l.CITY)='toronto';

--5.	选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

--employees	Emp#	manager	Mgr#
--kochhar	  101	  king	  100


select e1.last_name "employees" ,e1.employee_id "Emp#" , e2.last_name "manager" , e1.manager_id "Mgr#"
from employees e1 left join employees e2
on e1.manager_id=e2.employee_id;

avg()、sum()都只能使用number类型，而max()、min()能使用任何类型

组函数中使用nvl函数，不忽略空值

select avg(nvl(commission_pct,0))
from employees;

使用distinct去重

select count(distinct department_id) from employees;

--求出employees表中各部门的平均工资

select department_id, avg(salary)
from employees
--where department_id in (40,60,80)
group by department_id
order by department_id;

--多层分组
select department_id, job_id,avg(salary)
from employees
--where department_id in (40,60,80)
group by department_id,job_id;
--***查询时在Select中出现不是组函数的列，都应出现在group by中，而在group by中出现的列，不一定要出现在select中

--不能在where子句中使用组函数
--可以在having子句中使用组函数

--求出各部门中平均工资大于6000的部门及其平均工资,以部门号从小到大排序

select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>6000
order by department_id;


--显示各部门平均工资的最大值

SELECT MAX(AVG(salary))
FROM     employees
GROUP BY department_id;




--查询employees表中有多少部门
select count(distinct department_id)
from employees;

--查询‘Toronto’的平均工资

select avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id and lower(l.city)='toronto'

--有员工的城市的平均工资

select city, avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
group by city;

--查询平均工资高于8000的部门id,部门名及它的平均工资

select e.department_id,department_name,avg(salary)
from employees e,departments d
where d.department_id=e.department_id
group by e.department_id,department_name
having avg(salary)>8000
order by department_id;


--查询平均工资高于 6000 的 job_title 有哪些

select job_title,avg(salary)
from employees e join jobs j
on e.job_id=j.job_id
group by job_title
having avg(salary)>6000;

--4.	查询公司员工工资的最大值，最小值，平均值，总和

select max(salary),min(salary),avg(salary),sum(salary)
from employees;

--5.	查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id;

--6.	选择具有各个job_id的员工人数
select job_id,count(*)
from employees
group by job_id;

--7.	查询员工最高工资和最低工资的差距（DIFFERENCE）

select max(salary),min(salary),max(salary)-min(salary) "DIFFERENCE"
from employees;

--8.	查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select e1.employee_id,e1.last_name,min(e2.salary)
from employees e1 join employees e2
on e1.employee_id=e2.manager_id
group by e1.employee_id,e1.last_name
having min(e2.salary)>=6000
order by e1.employee_id;

--9.	查询所有部门的名字，location_id，员工数量和工资平均值
select department_name,location_id,count(*),avg(salary)
from employees e,departments d
where e.department_id(+)=d.department_id
group by department_name,location_id;

--10.	查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式
--total	1995	1996	1997	1998
--20	  3	    4	    6	    7

select count(*) "total",
       count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
       count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
       count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
       count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998');

decode()函数，相当于三目运算符

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

IF 条件=值1 THEN 　　　

RETURN(翻译值1)

ELSIF 条件=值2 THEN 　　　

RETURN(翻译值2) 　　　

......

ELSIF 条件=值n THEN 　　　

RETURN(翻译值n)

ELSE 　　　

RETURN(缺省值)

END IF

02 子查询

单行子查询

  • 只返回一行。

  • 使用单行比较操作符。

操作符 含义
= Equal to
> Greater than
>= Greater than or Equal to
< Less than
<= Less than or equal to
<> Less than or equal to

例题

--返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
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);

--返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);
--查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

多行子查询

  • 返回多行。

  • 使用多行比较操作符。

操作符 含义
IN 等于列表中的任意一个
ANY 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较

例题

--返回其它部门中比job_id为‘IT_PROG’部门任一员工工资低的员工的员工号、姓名、job_id 以及salary

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';

--返回其它部门中比job_id为‘IT_PROG’部门所有员工工资都低的员工的员工号、姓名、job_id 以及salary
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';

练习

--查询工资最低的员工信息: last_name, salary	
select last_name,salary from employees 
where salary=(select min(salary) from employees);

--查询平均工资最低的部门信息
--1 查询各部门的平均工资
--2 查询各部门平均工资最小的是哪一个
--3 哪个部门的工资 = 2的结果
--4 查询此部门的部门信息

select * from departments where department_id=
(select department_id from employees group by department_id having avg(salary)=
(select min(avg(salary)) from employees group by department_id));

--查询平均工资最低的部门信息和该部门的平均工资

select d.*,(select avg(salary) from employees where department_id=d.department_id)
from departments d
where department_id=
(select department_id from employees group by department_id having avg(salary)=
(select min(avg(salary)) from employees group by department_id));

--查询平均工资最高的 job 信息
select * from jobs where job_id in
(select job_id from employees group by job_id
having avg(salary)=(select max(avg(salary)) from employees group by job_id));

--查询平均工资高于公司平均工资的部门有哪些?
select * from departments where department_id in(
select department_id from employees group by department_id having avg(salary)>
(select avg(salary) from employees));

--查询出公司中所有 manager 的详细信息.
select * from employees where employee_id in (
select manager_id from employees
);

--各个部门中 最高工资中最低的那个部门的 最低工资是多少
select min(salary) from employees where department_id=
(select department_id from employees group by department_id having max(salary)=
(select min(max(salary)) from employees group by department_id));

--查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

select last_name,department_id,email,salary from employees where employee_id=
(select manager_id from departments where department_id=
(select department_id from employees group by department_id having avg(salary)=
(select max(avg(salary)) from employees group by department_id)));

--查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
select * from employees where salary=
(select MAX(salary) from employees where to_char(hire_date,'yyyy')='1999') and to_char(hire_date,'yyyy')='1999';

--1.	查询和Zlotkey相同部门的员工姓名和雇用日期

select last_name,to_char(hire_date,'yyyy-mm-dd')
from employees where department_id=(
select department_id from employees where last_name='Zlotkey'
)
and last_name<>'Zlotkey';


--2.	查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id,last_name,salary from employees
where salary>(select avg(salary) from employees);


--3.	查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id,last_name,salary
from employees e1
where salary>(
select avg(salary) from employees e2 group by department_id
having e2.department_id=e1.department_id 
);

--4.	查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id , last_name from employees
where department_id in (select department_id from employees 
where last_name like '%u%')
and last_name not like '%u%';

--5.查询在部门的location_id为1700的部门工作的员工的员工号
--解法1
select employee_id from employees e, departments d
where e.department_id=d.department_id and d.location_id=1700;

--解法2
select employee_id
from employees
where department_id in (
                       select department_id
                       from departments
                       where location_id = 1700
                       )


--6.查询管理者是King的员工姓名和工资

select last_name,salary
from employees 
where manager_id in (select employee_id from employees where last_name='King');

03 数据处理

数据操纵语言（DML）

  • DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行:

    • 向表中插入数据
    • 修改现存数据
    • 删除现存数据

  • 事务是由完成若干项工作的DML语句组成的

Insert语句

  • 使用 INSERT 语句向表中插入数据。
INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);

  • 使用这种语法一次只能向表中插入一条数据。

  • 为每一列添加一个新值。

  • 按列的默认顺序列出各个列的值。

  • 在 INSERT 子句中随意列出列名和他们的值。

  • 字符和日期型数据应包含在单引号中。

INSERT INTO departments(department_id, department_name, 
                        manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);


INSERT INTO employees(employee_id,last_name,email,hire_date,job_id)
VALUES  (300,’Tom’,’tom@126.com’,to_date(‘2012-3-21’,’yyyy-mm-dd’),’SA_RAP’);

向表中插入空值

  • 隐式方式: 在列名表中省略该列的值。
INSERT INTO	departments (department_id, 
                          department_name)
VALUES		(30, 'Purchasing');
  • 显示方式: 在VALUES 子句中指定空值。
INSERT INTO	departments
VALUES		(100, 'Finance', NULL, NULL);

创建脚本

  • 在SQL 语句中使用 & 变量指定列值。

  • & 变量放在VALUES子句中。

INSERT INTO departments 
            (department_id, department_name, location_id)
VALUES      (&department_id, '&department_name',&location);

从其它表中拷贝数据

  • 在 INSERT 语句中加入子查询。
INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

  • 不必书写 VALUES 子句。

  • 子查询中的值列表应与 INSERT 子句中的列名对应

Update语句

  • 使用 UPDATE 语句更新数据。
UPDATE		table
SET		column = value [, column = value, ...]
[WHERE 		condition];

  • 可以一次更新多条数据。

  • 使用 WHERE 子句指定需要更新的数据。

UPDATE employees
SET department_id=70
WHERE employee_id=113;
  • 如果省略 WHERE 子句，则表中的所有数据都将被更新
UPDATE 	copy_emp
SET    	department_id = 110;

在 UPDATE 语句中使用子查询

--题目：更新 114号员工的工作和工资使其与205号员工相同。
UPDATE   employees
SET      job_id  = (SELECT  job_id 
                    FROM    employees 
                    WHERE   employee_id = 205), 
         salary  = (SELECT  salary 
                    FROM    employees 
                    WHERE   employee_id = 205) 
WHERE    employee_id    =  114;

在 UPDATE 中使用子查询，使更新基于另一个表中的数据。

--题目：调整与employee_id 为200的员工job_id相同的员工的department_id为employee_id为100的员工的department_id。
UPDATE  copy_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 employees
SET    department_id = 55
WHERE  department_id = 110;

/*
错误报告 -
SQL 错误: ORA-02291: 违反完整约束条件 (C##JENNER.EMP_DEPT_FK) - 未找到父项关键字
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.
*/

55号部门不存在

Delete语句

使用 DELETE 语句从表中删除数据。

DELETE FROM	  table
[WHERE	  condition];

使用 WHERE 子句删除指定的记录。

DELETE FROM departments
 WHERE  department_name = 'Finance';

如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM  copy_emp;

在 DELETE 中使用子查询

--题目：从emp1表中删除dept1部门名称中含Public字符的部门id
DELETE FROM emp1
WHERE  department_id =
                       (SELECT department_id
                        FROM   dept1
                        WHERE  department_name LIKE '%Public%');

删除中的数据完整性错误

DELETE FROM departments
WHERE       department_id = 60;
--You cannot delete a row that contains a primary key that is used as a foreign key in another table.

数据库事务

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

数据库事务由以下的部分组成:

  • 一个或多个DML语句

  • 一个 DDL(Data Definition Language – 数据定义语言) 语句

  • 一个 DCL(Data Control Language – 数据控制语言) 语句

  • 以第一个 DML 语句的执行作为开始

  • 以下面的其中之一作为结束:

    • COMMIT 或 ROLLBACK 语句
    • DDL 语句（自动提交）
    • 用户会话正常结束
    • 系统异常终止

COMMIT和ROLLBACK语句的优点

使用COMMIT 和 ROLLBACK语句,我们可以:

  • 确保数据完整性。
  • 数据改变被提交之前预览。
  • 将逻辑上相关的操作分组。

控制事务

image-20200727101128963

ROLLBACK默认回滚到上一次COMMIT之后

回滚到保留点

  • 使用 SAVEPOINT 语句在当前事务中创建保存点。
  • 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

事务进程

  • 自动提交在以下情况中执行:
    • DDL语句
    • DCL语句
    • 不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。
  • 会话异常结束或系统异常会导致自动回滚。

提交或回滚前的数据状态

  • 改变前的数据状态是可以恢复的

  • 执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正

  • 其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

  • DML语句所涉及到的行被锁定， 其他用户不能操作。

提交后的数据状态

  • 数据的改变已经被保存到数据库中。

  • 改变前的数据已经丢失。

  • 所有用户可以看到结果。

  • 锁被释放, 其他用户可以操作涉及到的数据。

  • 所有保存点被释放。

提交数据

  • 改变数据
DELETE FROM employees
WHERE  employee_id = 99999;
1 row deleted.

INSERT INTO departments 
VALUES (290, 'Corporate Tax', NULL, 1700);
1 row inserted.
  • 提交改变
COMMIT;
Commit complete.

数据回滚后的状态

使用 ROLLBACK 语句可使数据变化失效:

  • 数据改变被取消。

  • 修改前的数据状态被恢复。

  • 锁被释放。

DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.
语句 功能
INSERT 插入
UPDATE 修正
DELETE 删除
COMMIT 提交
SAVEPOINT 保存点
ROLLBACK 回滚

题目

--更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job
update employee
set salary=(select max(salary) from employees where department_id=(select department_id from employees where employee_id=108))
,set job_id=(select job_id from employees group by job_id having avg(salary)=(select min(avg(salary)) from employees group by job_id))
where employee_id=108;

--删除 108 号员工所在部门中工资最低的那个员工.
--解法1
delete from employees
where employee_id = (select employee_id from employees where department_id = (select department_id from employees where employee_id = 108) and salary = (select min(salary) from employees where department_id = (select department_id from employees where employee_id = 108)));

--或者(解法)
delete from employees e
where salary=(
select min(salary) from employees where department_id = (select department_id from employees where employee_id=108)) and department_id=(select department_id from employees where employee_id=108);

--优化
delete from employees e
where salary=(
select min(salary) from employees where department_id = e.department_id) and department_id=(select department_id from employees where employee_id=108)

04 约束

什么是约束

  • 约束是表级的强制规定

  • 有以下五种约束:

    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK

NOT NULL

create table emp2(
id number(10) constraint emp2_id_nn not null,
name varchar2(20) not null,
salary number(10,2)
);

查看约束

image-20200729084818117

测试

insert into emp2
values(11111,null,2345.22);
--出现错误：ORA-01400: 无法将 NULL 插入 ("C##JENNER"."EMP2"."NAME")

UNIQUE

create table emp3(
--列级约束
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20),
salary NUMBER(10,2),
--表级约束
constraint emp3_email_uk unique(email)
);

image-20200729085954873

测试

insert into emp3
values(1001,'AA','AA@126.com',2000);

insert into emp3
values(1001,'BB','AA1@126.com',2000);

--报错:ORA-00001: 违反唯一约束条件 (C##JENNER.EMP3_ID_UK)

Unique约束下可以插入多个null值

PRIMARY KEY

create table emp4(
--列级约束
id number(10) `constraint emp4_id_pk primary key`,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary NUMBER(10,2),
--表级约束
`constraint emp4_id_pk primary key`
);

image-20200729112243538

测试

insert into emp4 values
(null,'jenner','123@163.com',5432);
--报错：ORA-01400: 无法将 NULL 插入 ("C##JENNER"."EMP4"."ID")

insert into emp3
values(1001,'AA','AA@126.com',2000);

insert into emp3
values(1001,'BB','AA1@126.com',2000);

--报错:ORA-00001: 违反唯一约束条件 (C##JENNER.EMP4_ID_PK)

主键不可为空，不可重复

FOREIGN KEY

create table emp6(
--列级约束
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary NUMBER(10,2),
department_id number(10),
--表级约束
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
);

image-20200729153435033

测试

insert into emp6
values(1002,'AA',null,2000,2000);
--报错ORA-02291: 违反完整约束条件 (C##JENNER.EMP6_DEPT_ID_FK) - 未找到父项关键字

FOREIGN KEY约束关键字

  • FOREIGN KEY: 在表级指定子表中的列
  • REFERENCES: 标示在父表中的列
  • ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
  • ON DELETE SET NULL(级联置空): 子表中相应的列置空

CHECK

create table emp7(
--列级约束
id number(10) constraint emp7_id_pk primary key,
name varchar2(20) constraint emp7_name_nn not null,
email varchar2(20),
salary NUMBER(10,2) CHECK(salary>1500 and salary<30000),
department_id number(10),

--表级约束
constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id)
);

image-20200729155100412

测试

insert into emp7
values(1003,'BB',null,1450,30);
--报错：ORA-02290: 违反检查约束条件 (C##JENNER.SYS_C007400)

添加约束

  • 添加或删除约束,但是不能修改约束

  • 有效化或无效化约束

  • 添加 NOT NULL 约束要使用 MODIFY 语句

--特殊的not null
alter table emp7 modify(salary number(10,2) not null);

--删除约束
alter table emp7 drop constraint emp7_name_nn;

--使约束失效
alter table emp7 disable constraint emp7_dept_id_fk;

--使约束有效
alter table emp7 enable constraint emp7_dept_id_fk;

05 视图

对象 描述
表 基本的数据存储集合，由行和列组成。
视图 从表中抽出的逻辑上相关的数据集合。
序列 提供有规律的数值。
索引 提高查询的效率
同义词 给对象起别名

  • 视图是一种虚表。

  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句。

  • 视图向用户提供基表数据的另一种表现形式

好处

  • 控制数据访问

  • 简化查询

  • 避免重复访问相同的数据

视图创建

create view empview
as 
select employee_id,last_name,salary
from employees
where department_id=80;

更改视图中的数据（增删改），在表中的数据也会更改

--多表连接创建视图
create view empview2
as 
select employee_id id,last_name name,salary,department_name
from employees e,departments d
where e.department_id=d.department_id;

修改视图

使用CREATE OR REPLACE VIEW 子句修改视图

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
   --with read only
   --只读
   ;

简单视图和复杂视图

特性 简单视图 复杂视图
表的数量 一个 一个或多个
函数 没有 有
分组 没有 有
DML操作 可以 有时可以 
--复杂查询
create or replace view empview2
as 
select department_name,avg(salary) avg_sal
from employees e,departments d
where e.department_id=d.department_id
group by department_name;

  • 当视图定义中包含以下元素之一时不能使用update:

    • 组函数
    • GROUP BY子句
    • DISTINCT 关键字
    • ROWNUM 伪列
    • 列的定义为表达式

  • 当视图定义中包含以下元素之一时不能使用delete:

    • 组函数
    • GROUP BY 子句
    • DISTINCT 关键字
    • ROWNUM 伪列

删除视图

drop view empview3;

TOP-N分析

查询最大的几个值的 Top-N 分析：

--找工资前10位员工的id，name，salary
select rownum,employee_id,last_name,salary
from 
(select employee_id,last_name,salary
from employees 
order by salary desc)
where rownum<=10;

对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。

--工资在40-50位之间的员工
select rn,employee_id,last_name,salary
from(
    select rownum rn,employee_id,last_name,salary
    --注意rownum要取别名，和新查找的列名相同
    from (select employee_id,last_name,salary
          from employees 
          order by salary desc)
)
where rn>40 and rn<=50;

练习

--查询员工表中salary 10-20的员工信息
select * from
(select rownum rn,temp.*
from 
(select last_name,salary
from employees e
order by salary desc)
temp)
where rn>10 and rn<21;

--对于数据库中的记录进行分页：每页显示10条记录，查询第5页的信息
select * from (
select rownum rn,employee_id,last_name,salary
from EMPLOYEES
)e
where e.rn<=50 and e.rn>40;

--使用表employees创建视图employee_vu，其中包括姓名(LAST_NAME)，员工号(EMPLOYEE_ID)，部门号(DEPARTMENT_ID).
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees;

06 其他对象

序列

可供多个用户用来产生唯一数值的数据库对象

  • 自动提供唯一的数值
  • 共享对象
  • 主要用于提供主键值
  • 将序列值装入内存可以提高访问效率

创建序列

  1. 创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
  2. 不使用 CYCLE 选项
CREATE SEQUENCE dept_deptid_seq
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;
  • 查询数据字典视图 USER_SEQUENCES 获取序列定义信息
SELECT	sequence_name, min_value, max_value, 
	increment_by, last_number
FROM	user_sequences;

image-20200813145724880

应用

INSERT INTO departments(department_id, 
            department_name, location_id)
VALUES      (dept_deptid_seq.NEXTVAL, 
            'Support', 2500);
  • 序列 DEPT_DEPTID_SEQ 的当前值
SELECT	dept_deptid_seq.CURRVAL
FROM	dual;

使用序列

  • 将序列值装入内存可提高访问效率
  • 序列在下列情况下出现裂缝:
    • 回滚
    • 系统异常
    • 多个表同时使用同一序列
  • 如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值

修改序列

修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

ALTER SEQUENCE dept_deptid_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;

注意

  • 必须是序列的拥有者或对序列有 ALTER 权限
  • 只有将来的序列值会被改变
  • 改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

DROP SEQUENCE dept_deptid_seq;

索引

  • 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中

  • 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度

  • 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引

  • 在删除一个表时,所有基于该表的索引会自动被删除

  • 通过指针加速 Oracle 服务器的查询速度

  • 通过快速定位数据的方法，减少磁盘 I/O

创建

  • 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引

  • 手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

CREATE INDEX 	emp_last_name_idx
ON 		employees(last_name);

何时创建索引？

以下情况可以创建索引:

  • 列中数据值分布范围很广

  • 列经常在 WHERE 子句或连接条件中出现

  • 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

何时不要创建索引？

下列情况不要创建索引:

  • 表很小

  • 列不经常作为连接条件或出现在WHERE子句中

  • 查询的数据大于2%到4%

  • 表经常更新

查询索引

  • 可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
SELECT	ic.index_name, ic.column_name,
	ic.column_position col_pos,ix.uniqueness
FROM	user_indexes ix, user_ind_columns ic
WHERE	ic.index_name = ix.index_name
AND	ic.table_name = 'EMPLOYEES';

同义词

使用同义词访问相同的对象:

  • 方便访问其它用户的对象

  • 缩短对象名字的长度

CREATE SYNONYM  d_sum
FOR  dept_sum_vu;

07 控制用户权限

权限

  • 数据库安全性
    • 系统安全性
    • 数据安全性
  • 系统权限：对于数据库的权限
  • 对象权限：操作数据库对象的权限

创建用户

使用create user 语句创建用户

CREATE USER user              			   
IDENTIFIED BY password;

用户创建后，DBA会赋予用户一些系统权限

grant privilege [, privilege...]		
to user [, user| role, PUBLIC...];
  • 以应用程序开发者为例, 一般具有下列系统权限:
    • CREATE SESSION（创建会话）
    • CREATE TABLE（创建表）
    • CREATE SEQUENCE（创建序列）
    • CREATE VIEW（创建视图）
    • CREATE PROCEDURE（创建过程）

DBA 可以赋予用户特定的权限

GRANT  create session, create table, 
       create sequence, create view
TO     scott;

创建用户表空间

用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表

ALTER USER atguigu01 QUOTA UNLIMITED 
ON users

角色

image-20200820135157906

创建角色及赋予权限

  • 创建角色
create role manager;
  • 为角色赋予权限
GRANT create table, create view 		  
TO manager;
  • 将角色赋予用户
GRANT manager TO DEHAAN, KOCHHAR;

修改密码

  • DBA 可以创建用户和修改密码
  • 用户本人可以使用 ALTER USER 语句修改密码
ALTER USER scott             			  
IDENTIFIED BY lion;

对象权限

  • 不同的对象具有不同的对象权限

  • 对象的拥有者拥有所有权限

  • 对象的拥有者可以向外分配权限

GRANT	object_priv [(columns)]
 ON		object
 TO		{user|role|PUBLIC}
 [WITH GRANT OPTION];

分配对象权限

  • 分配表 EMPLOYEES 的查询权限
GRANT  select
ON     employees
TO     sue, rich;
  • 分配表中各个列的更新权限
GRANT  update 
ON     scott.departments
TO     jenner;

WITH GRANT OPTION和PUBLIC关键字

  • WITH GRANT OPTION 使用户同样具有分配权限的权利
GRANT  select, insert
ON     departments
TO     scott
WITH   GRANT OPTION;
  • 向数据库中所有用户分配权限
GRANT  select
ON	  alice.departments
TO	  PUBLIC;

查询权限分配情况

image-20200820140737718

收回对象权限

  • 使用 REVOKE 语句收回权限

  • 使用 WITH GRANT OPTION 子句所分配的权限同样被收回

REVOKE {privilege [, privilege...]|ALL}
ON	  object
FROM   {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

--举例
REVOKE  select, insert
ON      departments
FROM    scott;

08 SET 运算符

UNION & UNION ALL

UNION 并集操作 $A \cup B$

SELECT employee_id, job_id
FROM   employees
UNION
SELECT employee_id, job_id
FROM   job_history;

UNION ALL 并集，且允许重复 $A \cap B + A \cup B$

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;

INTERSECT

操作符返回两个结果集的交集 $ A \cap B$

SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;

MINUS

返回两个结果集的差集 $A-B$

SELECT employee_id,job_id
FROM   employees
MINUS
SELECT employee_id,job_id
FROM   job_history;

注意事项

  • 在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
  • 括号可以改变执行的顺序
  • ORDER BY 子句:
    • 只能在语句的最后出现
    • 可以使用第一个查询中的列名, 别名或相对位置

特点

  • 除 UNION ALL之外，系统会自动将重复的记录删除

  • 系统将第一个查询的列名显示在输出中

  • 除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

匹配 select 语句

SELECT department_id, TO_NUMBER(null) 
       location, hire_date
FROM   employees
UNION
SELECT department_id, location_id,  TO_DATE(null)
FROM   departments;

SELECT employee_id, job_id,salary
FROM   employees
UNION
SELECT employee_id, job_id,0
FROM   job_history;

使用相对位置排序举例

--1
COLUMN a_dummy NOPRINT;
--2
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I`d like to teach', 1
FROM dual
UNION 
SELECT 'the world to', 2
FROM dual
ORDER BY 2;

image-20200820153150178

练习

--1.查询部门的部门号，其中不包括job_id是”ST_CLERK”的部门号
--法一
select department_id from departments
where department_id not in(
    select distinct department_id
    from employees
    where job_id='ST_CLERK'
)
                            order by department_id;


--法二
select department_id from departments
minus
select department_id from employees
where job_id='ST_CLERK';

--2.查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列
select job_id,department_id,1 a_dummy
from employees
where department_id=10
union
select job_id,department_id,2
from employees
where department_id=50
union
select job_id,department_id,3
from employees
where department_id=20
order by 3

--3.查询所有员工的last_name ,department_id 和department_name

select last_name,e.department_id,d.department_name
from employees e left join DEPARTMENTS d
on e.department_id=d.department_id;

09 高级子查询

成对比较

--查询与141号或者174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id

select employee_id,manager_id,department_id from employees e1
where (manager_id,department_id) in (
    select manager_id,department_id from employees where employee_id in (141,174)
)
and employee_id not in (141,174)

在 FROM 子句中使用子查询

--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
--法一
select last_name,department_id,salary,(select avg(salary) from employees e3 
                                       where e1.department_id = e3.department_id 
                                       group by department_id) avg_salary
from employees e1 where salary>(select avg(salary)
                                from employees e2
                                where e1.department_id=e2.department_id
                                group by department_id);
--法二
select a.last_name,a.department_id,a.salary,b.salavg
from employees a,(select department_id,avg(salary) salavg from employees group by department_id) b
where a.department_id=b.department_id and a.salary>b.salavg;

单列子查询表达式

--例一（case表达式中使用）
--显示员工的employee_id,last_name和location。
--其中，若员工department_id与location_id为1800的department_id相同，则location为’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;

--例二（order by子句中使用）
--查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name
from employees e
order by(select department_name 
         from departments d
         where e.DEPARTMENT_ID = d.DEPARTMENT_ID);

相关子查询

--子查询中使用主查询中的列
SELECT column1, column2, ...
 FROM   table1 outer
 WHERE  column1 operator 
			  (SELECT  colum1, column2
                       FROM    table2
                       WHERE   expr1 = outer.expr2);

--例一
--查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name,salary,department_id
from employees e1
where salary>(select avg(salary)
              from employees e2
              where e1.department_id=e2.department_id);
--若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
select e.employee_id,last_name,e.job_id
from employees e
where 2<=(select count(*)
          from job_history j where e.employee_id=j.employee_id);

EXISTS 操作符

  • EXISTS 操作符检查在子查询中是否存在满足条件的行

  • 如果在子查询中存在满足条件的行：

    • 不在子查询中继续查找
    • 条件返回 TRUE

  • 如果在子查询中不存在满足条件的行：

    • 条件返回 FALSE
    • 继续在子查询中查找
--查询公司管理者的employee_id,last_name,job_id,department_id信息
select employee_id,last_name,job_id,department_id
from employees e1
where exists(
    select * from employees where e1.EMPLOYEE_ID=manager_id);
    

--查询departments表中，不存在于employees表中的部门的department_id和department_name
select department_id,department_name
from departments d
where not exists(select 'X' from employees where department_id=d.department_id);

相关更新&相关删除

--在employee表中增加department_name字段
1)
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
2)
UPDATE employees e
SET    department_name = 
              (SELECT department_name 
	       FROM   departments d
	       WHERE  e.department_id = d.department_id);


--删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees E
WHERE employee_id =  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = E.employee_id);

WITH 子句

  • 使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块

  • WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

  • 使用 WITH 子句可以提高查询效率

--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dept_costs  AS (
    SELECT  d.department_name, SUM(e.salary) AS dept_total
    FROM    employees e, departments d
    WHERE   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;

练习

--查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可
select last_name,department_id,salary from employees
where (salary,department_id) in (select salary,department_id
                                 from employees where commission_pct is not null);

--选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
--法一
select last_name,job_id,salary
from employees
where salary>
(
    select max(salary) from employees where job_id='SA_MAN'
);
--法二
select last_name,job_id,salary
from employees
where salary> 
all
(
    select salary from employees where job_id='SA_MAN'
);

--选择所有没有管理者的员工的last_name
--法一
select last_name
from employees e1
where not exists (
    select 'A'
    from employees e2
    where e1.manager_id = e2.employee_id
);
--法二
select last_name
from employees
where manager_id is null;

10 PL/SQL

pl/sql 的基本结构

DECLARE
/* 声明部分: 在此声明 PL/SQL 用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
 /* 执行部分: 过程及 SQL 语句 , 即程序的主要部分 */
EXCEPTION
 /* 执行异常部分: 错误处理 */
END;
  • 其中，执行部分是必须的。

第一个 pl/sql 程序（hello world）

declare

begin
  dbms_output.put_line('hello, world');
--exception
end;

变量声明

变量声明方式一

declare
	--声明变量
	v_sal number(20,2);
	v_email varchar2(20);
	v_hire_date date;
begin
	--select语句的操作：select ... into ... from ... where ...
	select salary,email,hire_date into v_sal,v_email, v_hire_date from employees where employee_id=100;
	--打印
	dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

变量声明方式二

定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE

declare
	v_sal employees.salary%type;
	v_email employees.email%type;
	v_hire_date employees.hire_date%type;
begin
	select salary,email,hire_date into v_sal,v_email, v_hire_date from employees where employee_id=100;

	dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。

declare
  v_emp employees%rowtype;
    
begin
  select * into v_emp
  from employees where employee_id=100;
  dbms_output.put_line(v_emp.last_name||','||v_emp.department_id||','||v_emp.hire_date);
end;

记录

记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。

TYPE record_type IS RECORD(
 Field1 type1 [NOT NULL] [:= exp1 ],
 Field2 type2 [NOT NULL] [:= exp2 ],
 . . . . . .
 Fieldn typen [NOT NULL] [:= expn ] ) ;

测试

declare
  type test_rec is record(
  l_name employees.last_name%type,
  d_id employees.department_id%type);
  v_emp test_rec;
    
begin
  select last_name,department_id into v_emp
  from employees where employee_id=100;
  dbms_output.put_line(v_emp.l_name||','||v_emp.d_id);
end;

表

PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表

声明嵌套表类型的一般语法如下

TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};
  • 在使用嵌套表之前必须先使用该集合的构造器初始化它. PL/SQL 自动提供一个带有相同名字的构造器作为集合类型.
  • 嵌套表可以有任意数量的行。表的大小在必要时可动态地增加或减少: extend(x) 方法添加 x 个空元素到集合末尾; trim(x)方法为去掉集合末尾的 x 个元素.

测试

declare
  type dep_table_type is table of departments%rowtype;
  my_dep_table dep_table_type := dep_table_type();
    
begin
  my_dep_table.extend(5);
  for i in 1 .. 5 loop
    select * into my_dep_table(i)
    from departments
    where department_id=200+10*i;
    end loop;
   dbms_output.put_line(my_dep_table.count());
   dbms_output.put_line(my_dep_table(1).department_id);
end;

流程控制

选择语句

 --查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000'; 
--若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'
--法一：if else
declare
  v_salary employees.salary%type;
  v_message varchar2(30);
begin
  select salary into v_salary
  from employees
  where employee_id=150;
  if v_salary >=10000 then
    v_message:='salary >= 10000';
  elsif v_salary >= 5000 then
    v_message:='5000<= salary < 10000';
  else
    v_message:='salary < 5000';
  end if;
  dbms_output.put_line(v_message);
end;

--法二 case when
declare
  v_salary employees.salary%type;
  v_message varchar2(30);
begin
  select salary into v_salary
  from employees
  where employee_id=150;
  case trunc(v_salary/5000)
    when 2 then v_message := 'salary >= 10000';
    when 1 then v_message := '5000<= salary < 10000';
    else v_message := 'salary < 5000';
  end case; 
   dbms_output.put_line(v_message);
end;

循环语句

--打印1-100
--法一
declare
       --初始化条件
       v_i number(3) := 1;
begin
       loop
       --循环体
        dbms_output.put_line(v_i);
	--循环条件
        exit when v_i = 100;
	--迭代条件
        v_i := v_i + 1;
       end loop;
end;

--法二
declare
       --初始化条件
       v_i number(3) := 1;
begin
       --循环条件
       while v_i <= 100 loop
	     --循环体
             dbms_output.put_line(v_i);
	     --迭代条件
             v_i := v_i + 1;
       end loop;
end; 

--法三
begin
       for i in 1 .. 100 loop
             dbms_output.put_line(i);
       end loop;
end;

--综合使用 if, while 语句, 打印 1 - 100 之间的所有素数
--(素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, ...).
--法一
declare
  v_flag number(1):=1;
  v_i number(3):=2;
  v_j number(2):=2;
begin

  while (v_i<=100) loop
        while v_j <= sqrt(v_i) loop
              if (mod(v_i,v_j)=0) then v_flag:= 0;
              exit;
	      	  end if;
	      v_j :=v_j +1;
        end loop;

		if(v_flag=1) then dbms_output.put_line(v_i);
		end if;

        v_flag :=1;
        v_j := 2;
        v_i :=v_i +1;
   end loop;
end;

--法二
declare
  --标记值, 若为 1 则是素数, 否则不是
  v_flag number(1) := 0;
begin
   for i in 2 .. 100 loop
       v_flag := 1;     
       for j in 2 .. sqrt(i) loop
           if i mod j = 0 then
              v_flag := 0;	
           end if;        
       end loop;
       if v_flag = 1 then
           dbms_output.put_line(i);
       end if;
   end loop;
end;

--法三
declare
  type is_prime IS VARRAY(105) OF BOOLEAN;
  j int := 2;
  prime_nums is_prime := is_prime();
begin
  prime_nums.extend(105);
  for i in 1 .. 100 loop
    prime_nums(i) := true;
  end loop;
  for i in 2 .. 100 loop
    if prime_nums(i) = true then
      while j * i <=100 loop
        prime_nums(j*i) := false;
        j := j+1;
      end loop;
      j := 2;
    end if;
  end loop;
  for i in 2 .. 100 loop
    if prime_nums(i) = true then
    dbms_output.put_line(i);
    end if;
  end loop;
end;

游标

​ 为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。 游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL 可以控制上下文区和处理语句时上下文区会发生些什么事情。

SQL语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显式的
结果是多行的查询语句 显式的

显式游标

  • 定义游标

就是定义一个游标名，以及与其相对应的 SELECT 语句。 格式： CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement; 游标参数只能为输入参数，其格式为： parameter_name [IN] datatype [{:= | DEFAULT} expression] 在指定数据类型时，不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。

  • 打开游标

就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式：OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示 法。PL/SQL 程序不能用 OPEN 语句重复打开一个游标。

  • 提取游标数据

就是检索结果集合中的数据行，放入指定的输出变量中。 格式： FETCH cursor_name INTO {variable_list | record_variable };

  • 关闭游标

当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源， 并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。

CLOSE cursor_name;

注：定义的游标不能有 INTO 子句。

--查询前 10 名员工的信息
declare
  --定义游标
  cursor c_cursor is select last_name,salary from employees
  where rownum<11
  order by salary;
  v_name employees.last_name%type;
  v_sal  employees.salary%type;
begin
    --打开游标
    open c_cursor;
    --提取游标数据
    fetch c_cursor into v_name, v_sal;
    while c_cursor % found loop
      dbms_output.put_line(v_name || ' : '||v_sal);
      fetch c_cursor into v_name,v_sal;
    end loop;
    --关闭游标
    close c_cursor;
end;

游标属性

  • %FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；

  • %NOTFOUND 布尔型属性，与%FOUND 相反；

  • %ISOPEN 布尔型属性，当游标已打开时返回 TRUE；

  • %ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

游标的FOR循环

PL/SQL 语言提供了游标 FOR 循环语句，自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能； 当进入循环时，游标 FOR 循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标 FOR 循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有 数据行后结束循环，并自动关闭游标。

格式：

FOR index_variable IN cursor_name[value[, value]…] LOOP
 -- 游标数据处理代码 
END LOOP;

其中： index_variable 为游标 FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句 返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据， index_variable 中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择 列表中存在计算列，则必须为这些计算列指定别名后才能通过游标 FOR 循环语句中的索引变量来访问这些 列数据。

--打印50号部门的所有员工的名字以及工资信息
declare 
	cursor c_emp(dep_id number default 50) is
	select last_name, salary sal
	from employees
	where department_id=dep_id;
begin
  for v_emp in c_emp loop
    dbms_output.put_line(v_emp.last_name||', '||v_emp.sal);
  end loop;
end;

隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句， 如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建 的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。对于隐式游标的操作，如定义、 打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、 最新处理的一条 SQL 语句所包含的数据。

格式调用为： SQL%

隐式游标属性

  • SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE；

  • SQL%NOTFOUND 布尔型属性,与%FOUND 相反；

  • SQL%ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；

  • SQL%ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。

--更新指定员工信息，如果该员工没有找到，则打印”查无此人”信息。
declare
	v_name employees.last_name%type;
	v_id employees.employee_id%type := &v_id;
begin
	update employees
	set last_name='xx'
	where employee_id=v_id;
	
	if SQL%NOTFOUND then
		dbms_output.put_line('查无此人');
	end if;
end;

NO_DATA_FOUND 和 %NOTFOUND 的区别

SELECT … INTO 语句触发 NO_DATA_FOUND；

当一个显式游标的 WHERE 子句未找到时触发%NOTFOUND；

当 UPDATE 或 DELETE 语句的 WHERE 子句未找到时触发 SQL%NOTFOUND；

在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND

游标修改和删除操作

游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用 FOR UPDATE 选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。 为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使 ORACLE 锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。

语法：

SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]

如果另一个会话已对活动集中的行加了锁，那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上 NOWAIT 子句时，如果这些行真的被另一个会话锁定， 则 OPEN 立即返回并给出：

ORA-0054 ：resource busy and acquire with nowait specified.

如果使用 FOR UPDATE 声明游标，则可在 DELETE 和 UPDATE 语句中使用 WHERE CURRENT OF cursor_name 子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。

--从 EMPLOYEES 表中查询某部门的员工情况，将其工资最低定为 3000；
declare
	v_dep_id employees.department_id%type := &v_dep_id;
	cursor emp_cursor is
	select last_name, salary
	from employees
	where department_id = v_dep_id;
	for update nowait;
begin
	for emp_rec in emp_cursor loop
		if emp_rec.salary < 3000 then
			update employees set salary = 3000
			where current of emp_cursor;
		end if;
	end loop;
end;

/*	利用游标, 调整公司中员工的工资: 
    
    工资范围       调整基数
    0 - 5000       5%
    5000 - 10000   3%
    10000 - 15000  2%
    15000 -        1%
*/
declare
  cursor emp_sal_cursor is 
  select employee_id,salary
  from employees;
  temp number(4,2);
  --v_empid employees.employee_id%type;
  --v_sal employees.salaey%type;
begin
  for emp_sal in emp_sal_cursor loop
    --dbms_output.put_line('before:'||emp_sal.salary);
    if emp_sal.salary <5000 then
      temp := 0.05;
    elsif emp_sal.salary < 10000 then
      temp:=0.03;
    elsif emp_sal.salary < 15000 then
      temp := 0.02;
    else
      temp := 0.01;
    end if;
    update employees set salary = salary*(1+temp)
    where employee_id=emp_sal.employee_id;
    --dbms_output.put_line('after:'||emp_sal.salary);
  end loop;
end;

--带参数的游标
declare
    --定义游标
    cursor emp_sal_cursor(dept_id number, sal number) is 
           select salary + 1000 sal, employee_id id 
           from employees 
           where department_id = dept_id and salary > sal;
    
    --定义基数变量
    temp number(4, 2);
begin
    --处理游标的循环操作
    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
          --判断员工的工资, 执行 update 操作
          --dbms_output.put_line(c.id || ': ' || c.sal);
          
          if c.sal <= 5000 then
             temp := 0.05;
          elsif c.sal <= 10000 then
             temp := 0.03;   
          elsif c.sal <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
          
          dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
          --update employees set salary = salary * (1 + temp) where employee_id = c.id;
    end loop;
end;
--还可以使用SQL中的 decode 函数

--update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,                                             1, 0.03,                                             2, 0.02,                                                0.01)))
--更新指定员工 salary(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
begin
     update employees set salary = salary + 10 		      where employee_id = 1005;

     if sql%notfound then
        dbms_output.put_line('查无此人!');
     end if;
end;

异常处理

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于 PL/SQL 程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。

有三种类型的异常错误：

  • 预定义 ( Predefined )错误 ：ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 ORACLE 自动将其引发。

  • 非预定义 ( Non-predefined )错误 : 即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将其引发。

  • 用户定义(User_define) 错误 : 程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义， 然后显式地在程序中将其引发。

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

EXCEPTION
 WHEN first_exception THEN <code to handle first exception >
 WHEN second_exception THEN <code to handle second exception >
 WHEN OTHERS THEN <code to handle others exception >
END;

预定义异常

错误号 异常错误信息 说明
ORA-0001 Dup_val_on_index 试图破坏一个唯一性限制
ORA-0051 Timeout-on-resource 在等待资源时发生超时
ORA-0061 Transaction-backed-out 由于发生死锁事务被撤消
ORA-1001 Invalid-CURSOR 试图使用一个无效的游标
ORA-1012 Not-logged-on 没有连接到 ORACLE
ORA-1017 Login-denied 无效的用户名/口令
ORA-1403 No_data_found SELECT INTO 没有找到数据
ORA-1422 Too_many_rows SELECT INTO 返回多行
ORA-1476 Zero-divide 试图被零除
ORA-1722 Invalid-NUMBER 转换一个数字失败
ORA-6500 Storage-error 内存不够引发的内部错误
ORA-6501 Program-error 内部错误
ORA-6502 Value-error 转换或截断错误
ORA-6504 Rowtype-mismatch 宿主游标变量与 PL/SQL 变量有不兼容行类型
ORA-6511 CURSOR-already-OPEN 试图打开一个已存在的游标
ORA-6530 Access-INTO-null 试图为 null 对象的属性赋值
ORA-6531 Collection-is-null 试图将 Exists 以外的集合( collection)方法应用于 一个 null pl/sql 表上或 varray 上
ORA-6532 Subscript-outside-limit 对嵌套或 varray 索引得引用超出声明范围以外
ORA-6533 Subscript-beyond-count 对嵌套或 varray 索引得引用大于集合中元素的个数 
--更新指定员工工资，如工资小于 3000，则加 100；对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理.
declare
	v_empno employees.employee_id%type := &v_empno;
	v_sal employees.salary%type;
begin
	select salary into v_sal
	from employees
	where employee_id= v_empno
	for update;
	if v_sal <=3000 then
		update employees set salary=salary+100
		where employee_id=v_empno;
		dbms_output.put_line('编码为'||v_empno||'员工工资已更新');
	else
		dbms_output.put_line('编码为'||v_empno||'员工工资不需要更新');
	end if;
exception
	when NO_DATA_FOUND then
		dbms_output.put_line('没有编码为'||v_empno||'的员工');
	when TOO_MANY_ROWS then
	 	dbms_output.put_line('程序运行错误，请使用游标');
	when others then
		dbms_output.put_line('其他错误');
end;

非预定义的异常

对于这类异常情况的处理，首先必须对非定义的 ORACLE 错误进行定义。

步骤如下：

  1. 在 PL/SQL 块的定义部分定义异常情况： <异常情况> EXCEPTION;
  2. 将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 PRAGMA EXCEPTION_INIT 语句： PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
  3. 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
--删除指定部门，确保该部门没有员工
declare
	v_deptno departments.department_id%type := &v_deptno;
	deptno_remaining exception;
	
	-- -2292是为违反一致性约束的错误代码
	pragma exception_init(deptno_remaining,-2292);
begin
	delete from departments where department_id=v_deptno;
exception
	when deptno_remaining then
		dbms_output.put_line('违反一致性约束');
	when others then
		dbms_output.put_line(sqlcode|| '--' || sqlerrm);
end;

自定义异常

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使 用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION 块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：

  1. 在 PL/SQL 块的定义部分定义异常情况： <异常情况> EXCEPTION;
  2. RAISE <异常情况>；
  3. 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
declare
	v_empid employees.employee_id%type := &v_empid;
	no_result exception;
begin
	update employees
	set salary=salary+100
	where employee_id=v_empid;
	if sql%notfound then
		raise no_result;
	end if;
exception
	when no_result then
		dbms_output.put_line('更新失败');
	when others then
		dbms_output.put_line('其他异常');
end;

sqlcode & sqlerrm

在 PL/SQL 中使用 SQLCODE, SQLERRM SQLCODE 返回错误代码数字 SQLERRM 返回错误信息.

如:

SQLCODE=-100 --> SQLERRM=’no_data_found ‘

SQLCODE=0 -->SQLERRM=’normal, successfual completion’

--将 ORACLE 错误代码及其信息存入错误代码表
create table errors(errnum number(4),errmsg varchar2(100));

declare
  err_msg varchar2(100);
begin
  for err_num in -100 .. 0 loop
    err_msg := sqlerrm(err_num);
    insert into errors values(err_num,err_msg);
  end loop;
end;

存储函数和过程

ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。

这样就叫存储过程或函数。过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。

过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

内嵌函数

create or replace function func_name(dept_id number, salary number)
return number
is
       --函数使用过程中，需要声明的变量、记录类型、cursor
       
begin
       --函数执行体
exception
       --异常处理
end;
  1. OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突
  2. 函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记。参数之间用逗号隔开，IN 参数标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句。若省略标记, 则参数隐含为 IN。
  3. 因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型。
create or replace function hello_world
                  return varchar2
is
begin
       return 'hello, world';
end;

select hello_world from dual;

--创建一个存储函数，返回当前系统时间
create or replace function get_sysdate
return date
is
	v_date date;
begin
	v_date := sysdate;
	return v_date;
end;
--
select get_sysdate() from dual;

--两数相加
create or replace function add_param(v_num1 number,v_num2 number)
return number
is 
       v_sum number;
begin
       v_sum := v_num1+v_num2;
       return v_sum;
end;
--
select add_param(3,5) from dual;

--获取给定部门的工资总和，部门号为参数，工资总额为返回值
create or replace function get_salary_sum(dept_id number)
return number
is
	v_sumsal number(10) := 0;
	cursor salary_cursor is select salary from employees where department_id=dept_id;
begin
	for c in salary_cursor loop
		v_sumsal := v_sumsal+c.salary;
	end loop;
	return v_sumsal;
end;
  • IN 参数 标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数隐含为 IN。
--定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值.
create or replace function get_salary_sum(dept_id number, total_count out number)
return number
is
	v_sumsal number(10) := 0;
	cursor salary_cursor is select salary from employees where department_id=dept_id;
begin
	total_count := 0;
	for c in salary_cursor loop
		total_count := total_count + 1;
		v_sumsal := v_sumsal+c.salary;
	end loop;
	return v_sumsal;
end;

--输出
declare
	v_sum number(5) := 0;
begin
	dbms_output.put_line(get_salary_sum(80,v_sum));
	dbms_output.put_line(v_sum);
end;

过程

--定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数

create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
       is
       
       cursor sal_cursor is select salary from employees where department_id = dept_id;
begin
       v_sum_sal := 0;
       
       for c in sal_cursor loop
           --dbms_output.put_line(c.salary);
           v_sum_sal := v_sum_sal + c.salary;
       end loop;       

       dbms_output.put_line('sum salary: ' || v_sum_sal);
end;
--[执行]
declare 
     v_sum_sal number(10) := 0;
begin
     sum_sal_procedure(80,v_sum_sal);
end;
--对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间,    为其加薪 %5
                                                       --        [95 , 98)             %3       
                                                       --        [98, ?)               %1
--得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).

create or replace procedure add_sal_procedure(dept_id number, temp out number)

is

       cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
       a number(4, 2) := 0;
begin
       temp := 0;       

       for c in sal_cursor loop
           a := 0;    
       
           if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
              a := 0.05;
           elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
              a := 0.03;
           else
              a := 0.01;
           end if;
           
           temp := temp + c.sal * a;
           update employees set salary = salary * (1 + a) where employee_id = c.id;
       end loop;       
end;

触发器

触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或 直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且， 触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE 事件指的是对数据库的表进行的 INSERT、UPDATE 及 DELETE 操作或对视图进行类似的操作。ORACLE 将触发器的功能扩展到了触发 ORACLE， 如数据库的启动与关闭等。

  • ORACLE 可以在 DML 语句进行触发，可以在 DML 操作前或操作后进行触发，并且可以对每个行或语句 操作上进行触发。

触发器组成:

  • 触发事件：即在何种情况下触发 TRIGGER; 例如：INSERT, UPDATE, DELETE。

  • 触发时间：即该 TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和 该 TRIGGER 的操作顺序。

  • 触发器本身：即该 TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

  • 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次； 行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一 次。

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] table_name
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触 发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。 FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一 个 DML 语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激 活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当 省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器，而 INSTEAD OF 触发器则为行触发 器。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达式，其中必须包含相关名称，而不能包含查 询语句，也不能调用 PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器 中，不能用在 INSTEAD OF 行触发器和其它类型的触发器中。

当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

--语句级触发器: 一个 update/delete/insert 语句只使触发器执行一次
create or replace trigger employees_trigger
after 
update on employees
begin
       dbms_output.put_line('修改了一条记录!');
end;

--行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行
create or replace trigger employees_trigger
after 
update on employees
for each row
begin
       dbms_output.put_line('修改了一条记录!');
end;

--使用 :new, :old 修饰符
create or replace trigger employees_trigger
after 
update on employees
for each row
begin
       dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;

--编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录
--准备工作:
	create table my_emp as select employee_id id, last_name name, salary sal from employees
	create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2
 
create or replace trigger bak_emp_trigger
       before delete on my_emp
       for each row
       
begin
       insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;
posted @ 2021-01-29 08:34  JennerShao  阅读(187)  评论(0)    收藏  举报