Oracle详解笔记

一、Oracle介绍

ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。比如SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。

二、Oracle安装

配置监听(对应2.14.2操作) (创建非CDB数据库)

cd /u01/app/oracle/etc/ #oracle用户执行

lsnrctl start #启动监听

lsnrctl stop

lsnrctl status

netstat -an |grep 1521

启停数据库

cd /u01/app/oracle/etc/ #oracle用户执行

sqlplus / as sysdba

startup;

shut immediate;

[DN_Centos7_Oracle12c 静默安装](…/DN_Centos7_Oracle12c 静默安装.pdf)

三、Oracle体系结构

3.1.数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

3.2.实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

3.3.用户

用户是在实例下建立的。不同实例可以建相同名字的用户。

3.4.表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数 据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

3.5.数据文件

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

四、创建表空间

表空间：ORACLE 数据库的逻辑单元。 数据库—表空间 一个表空间可以与多个数据

文件（物理结构）关联

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立

多个表。

CREATE TABLESPACE <tablespace_name> 
DATAFILE '<datafile_location>' 
SIZE <initial_size> 
[AUTOEXTEND ON [NEXT <next_size>]]
[MAXSIZE <max_size>];

• <tablespace_name>是您要创建的表空间的名称。
• <datafile_location>是表空间数据文件的路径和名称。
• <initial_size>是表空间的初始大小，可以使用单位M（兆字节）或G（千兆字节）来指定。
• AUTOEXTEND ON表示表空间可以自动扩展。
• <next_size>是每次自动扩展时增加的大小。
• MAXSIZE <max_size>是表空间的最大大小。

例如，创建名为users_tbs的表空间，数据文件位于/u01/oracle/users_tbs.dbf，初始大小为100M，自动扩展每次增加10M，最大大小为1G：

CREATE TABLESPACE users_tbs
DATAFILE '/u01/oracle/users_tbs.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M
MAXSIZE 1G;

五、用户

5.1.创建用户

create user lyadmin identified by 123456
default tablespace LY

identified by 后边是用户的密码，default tablespace 后边是表空间名称

oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

5.2.用户赋权限

Oracle 中已存在三个重要的角色：connect 角色，resource 角色，dba 角色。

CONNECT 角色： --是授予最终用户的典型权利，最基本的

ALTER SESSION --修改会话 

CREATE CLUSTER --建立聚簇 

CREATE DATABASE LINK --建立数据库链接 

​ CREATE SEQUENCE --建立序列

​ CREATE SESSION --建立会话

​ CREATE SYNONYM --建立同义词

​ CREATE VIEW --建立视图

RESOURCE 角色： --是授予开发人员的

​ CREATE CLUSTER --建立聚簇

CREATE PROCEDURE --建立过程 

CREATE SEQUENCE --建立序列 

CREATE TABLE --建表 

​ CREATE TRIGGER --建立触发器

CREATE TYPE --建立类型 

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统

权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

grant dba to itcastuser

进入 system 用户下给用户赋予 dba 权限，否则无法正常登陆

5.3.Oracle体系结构操作示例

查看当前用户下有哪些表空间

select * from dba_tablespaces;

SELECT * FROM USER_TABLESPACES;

SELECT USERNAME, DEFAULT_TABLESPACE FROM USER_USERS;

查看表空间对应的数据文件

select * from dba_data_files;

SELECT * FROM DBA_TEMP_FILES;

SELECT * FROM V$DATAFILE;

select * from v$tempfile;

select * from database_properties

查看表空间的使用情况

SELECT f.tablespace_name AS tablespace_name

, round(d.sumbytes / 1024 / 1024 / 1024, 2) AS "总大小(G)"

, round(f.sumbytes / 1024 / 1024 / 1024, 2) AS "空闲大小(G)"

, round((d.sumbytes - f.sumbytes) / 1024 / 1024 / 1024, 2) AS "使用大小(G)"

, round((d.sumbytes - f.sumbytes) * 100 / d.sumbytes, 2) AS "利用率"

FROM (

SELECT tablespace_name, SUM(bytes) AS sumbytes

FROM dba_free_space

GROUP BY tablespace_name

) f, (

SELECT tablespace_name, SUM(bytes) AS sumbytes

FROM dba_data_files

-- WHERE tablespace_name IN ('SYSTEM')

GROUP BY tablespace_name

) d

WHERE f.tablespace_name = d.tablespace_name

ORDER BY 利用率 DESC, d.tablespace_name;

创建表空间

--create tablespace ly datafile '/u01/app/oracle/oradata/ly/ly01.dbf' size 200m autoextend on next 10m;

create tablespace CDC datafile '/u01/app/oracle/oradata/cdc/CDC01.dbf' size 10G;

alter tablespace CDC add datafile '/u01/app/oracle/oradata/cdc/CDC02.dbf' SIZE 100m;

alter tablespace CDC add datafile '/u01/app/oracle/oradata/cdc/CDC03.dbf' SIZE 100m;

create tablespace LY datafile '/u01/app/oracle/oradata/ly/ly01.dbf' size 200m

删除表空间及其内容

DROP TABLESPACE ly INCLUDING CONTENTS AND DATAFILES;

--删除表空间

drop tablespace ly;

drop tablespace ly including contents and datafiles;

创建用户指定表空间

create user cdcadmin identified by Cdc2020zstit default tablespace CDC;

修改用户的默认表空间

ALTER USER lyadmin DEFAULT TABLESPACE LY;

修改用户的临时表空间

ALTER USER lyadmin TEMPORARY TABLESPACE LY;

grant create session to cdcadmin; #用户就能成功登录进去

grant create table to cdcadmin; #赋予用户创建表的权限

grant create view to cdcadmin; #赋予用户创建视图的权限

grant connect, resource to cdcadmin; #授权

grant unlimited tablespace to cdcadmin; #赋予相应的权限

查看哪些用户被授予了DBA权限

select * from dba_role_privs where granted_role='DBA';

查询指定用户的所有表名

SELECT * FROM all_tables WHERE OWNER = 'CDCADMIN';--用户名要大写

SELECT * FROM all_tables WHERE OWNER = 'LYADMIN' ORDER BY TABLE_NAME;--用户名要大写

SELECT distinct tablespace_name FROM all_tables WHERE OWNER = 'LYADMIN' ORDER BY tablespace_name;--用户名要大写

SELECT * FROM all_tables WHERE OWNER = 'SYS' ORDER BY TABLE_NAME;--用户名要大写

SELECT distinct tablespace_name FROM all_tables WHERE OWNER = 'SYS' ORDER BY tablespace_name;--用户名要大写

查询当前用户的所有表名

SELECT * FROM user_tables;

查询所有用户

select * from dba_users order by username

SELECT * FROM ALL_USERS WHERE USERNAME = 'LYADMIN';

查看当前用户的会话信息

SELECT sid AS session_id, serial# FROM v$session WHERE username = 'LYADMIN';

SELECT * FROM v$session

终止会话

ALTER SYSTEM KILL SESSION '128, 15922' IMMEDIATE;

ALTER SYSTEM KILL SESSION '253, 40216' IMMEDIATE;

ALTER SYSTEM KILL SESSION '363, 24177' IMMEDIATE;

检查表空间是否被其他用户使用

SELECT * FROM dba_users WHERE default_tablespace = 'ly';

删除用户及其相关对象（包括表、索引等）

DROP USER lyadmin CASCADE;

给用户授权

--oracle数据库中常用角色

connect--连接角色，基本角色

resource--开发者角色

dba--超级管理员角色

--给lyadmin用户授予dba角色

grant dba to lyadmin;

---切换到lyadmin用户下

---创建一个person表

create table person(

       pid number(20),

       pname varchar2(10)

);

---修改表结构

---添加一列

alter table person add (gender number(1));

---修改列类型

alter table person modify gender char(1);

---修改列名称

alter table person rename column gender to sex;

---删除一列

alter table person drop column sex;

---查询表中记录

select * from person;

----添加一条记录

insert into person (pid, pname) values (1, '小明');

commit;

----修改一条记录

update person set pname = '小马' where pid = 1;

commit;

----三个删除

--删除表中全部记录

delete from person;

--删除表结构

drop table person;

--先删除表，再次创建表。效果等同于删除表中全部记录。

--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。

--索引可以提供查询效率，但是会影响增删改效率。

truncate table person;

六、Oracle数据类型

No	数据类型	描述
1	Varchar， varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是 n
		NUMBER(m,n):表示一个小数，总长度是 m，小数是 n，整数是 m-n
3	DATA	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存 4G
5	BLOB	大对象，表示二进制数据，可存 4G

七、表的管理

7.1.建表

语法： 

Create table 表名（ 

字段 1 数据类型 [default 默认值], 

字段 2 数据类型 [default 默认值], 

... 

字段 n 数据类型 [default 默认值] 

）; 

范例：创建 person 表

create table person( 

 pid number(10), 

 name varchar2(10), 

 gender number(1) default 1, 

 birthday date 

); 

insert into person(pid, name, gender, birthday) 

values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd')); 

7.2.表删除

语法：DROP TABLE 表名

7.3.表的修改

在 sql 中使用 alter 可以修改表

• 添加语法：ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)
• 修改语法：ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)
• 修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

范例：在 person 表中增加列 address

alter table person add(address varchar2(10)); 

范例：把 person 表的 address 列的长度修改成 20 长度

alter table person modify(address varchar2(20)); 

7.4.数据库表数据的更新

7.4.1.INSERT（增加）

标准写法：

INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...) 

简单写法（不建议）

INSERT INTO 表名 VALUES(值 1，值 2，…)

注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null

insert into person values(2,'李四',1,null,'北京育新'); 

7.4.2.UPDATE（修改）

全部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，…

局部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，…WHERE 修改条件；

全部更新

局部更新

7.4.3.DELETE（删除）

语法 : DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据

因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数

据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据

库。如果事务提交后则不可以再回滚。

提交：commit

回滚：rollback

7.5.序列

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能，则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

语法：CREATE SEQUENCE 序列名 

[INCREMENT BY n]  

[START WITH n]  

[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]  

[{CYCLE|NOCYCLE}]  

[{CACHE n|NOCACHE}]; 

• 序列不真的属于任何一张表，但是可以逻辑和表做绑定。

• 序列：默认从1开始，依次递增，主要用来给主键赋值使用。

• dual：虚表，只是为了补全语法，没有任何意义。

create sequence s_person;

select s_person.nextval from dual;

----添加一条记录

insert into person (pid, pname) values (s_person.nextval, '小明');

commit;

select * from person;

范例:创建一个 seqpersonid 的序列,验证自动增长的操作

CREATE SEQUENCE seqpersonid; 

序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：

nextval :取得序列的下一个内容

currval :取得序列的当前内容

select seqpersonid.nextval from dual; 

select seqpersonid.currval from dual; 

在插入数据时需要自增的主键中可以这样使用

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。

序列的管理一般使用工具来管理。

八、Scott用户下的表结构

scott用户，密码123456。

• 解锁scott用户

alter user scott account unlock;

• 解锁scott用户的密码【此句也可以用来重置密码】

alter user scott identified by tiger;

九、单行函数

作用于一行，返回一个值。

9.1.字符函数

接收字符输入返回字符或者数值，dual 是伪表

• 把小写的字符转换成大小的字符

  select upper('yes') from dual;--YES
  

• 把大写字符变成小写字符

  select lower('YES') from dual;--yes
  

首字母大写函数

字符串链接函数

字符串截取函数

字符串替换函数

获取字符串长度函数

9.2.数值函数

四舍五入函数

ROUND() ：默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数。

select round(56.16, -2) from dual;---100 四舍五入，后面的参数表示保留的位数

数值截取函数

select trunc(56.16, -1) from dual;---50 直接截取，不在看后面位数的数字是否大于5.

取余函数

select mod(10, 3) from dual;---1 求余数

日期函数

Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律

日期 – 数字 = 日期

日期 + 数字 = 日期

日期 – 日期 = 数字

• 查询出emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e;

• 算出明天此刻

select sysdate+1 from dual;

• 查询出emp表中所有员工入职距离现在几月。

select months_between(sysdate,e.hiredate) from emp e;

• 查询出emp表中所有员工入职距离现在几年。

select months_between(sysdate,e.hiredate)/12 from emp e;

• 查询出emp表中所有员工入职距离现在几周。

select round((sysdate-e.hiredate)/7) from emp e;

转换函数

TO_CHAR

字符串转换函数

年：y, 年是四位使用 yyyy

月：m, 月是两位使用 mm

日：d, 日是两位使用 dd

• 日期转字符串

select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;

fm 去掉前导零

TO_DATE

日期转换函数

• 字符串转日期

select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

通用函数

空值处理 nvl

• 算出emp表中所有员工的年薪----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。

select e.sal*12+nvl(e.comm, 0) from emp e;

条件表达式case when

• 条件表达式的通用写法，mysql和oracle通用

CASE expr WHEN comparison_expr1 THEN return_expr1 

 [WHEN comparison_expr2 THEN return_expr2 

 WHEN comparison_exprn THEN return_exprn  

 ELSE else_expr] 

END

• 给emp表中员工起中文名

select e.ename,case e.ename when 'SMITH' then '曹贼' when 'ALLEN' then '大耳贼' when 'WARD' then '诸葛小儿' else '无名' end from emp e;

• 判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，其余显示低收入

select e.sal,case when e.sal>3000 then '高收入' when e.sal>1500 then '中等收入' else '低收入' end from emp e;----oracle中除了起别名，都用单引号。

Decode 函数

该函数类似 if…else if…esle

语法：DECODE(col/expression, [search1,result1],[search2, result2]…[default])

Col/expression:列名或表达式

Search1，search2…:用于比较的条件

Result1, result2…:返回值

如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

• oracle专用条件表达式

select e.ename,decode(e.ename,'SMITH',  '曹贼','ALLEN','大耳贼','WARD',  '诸葛小儿','无名') "中文名" from emp e;

十、多行函数【聚合函数】

作用于多行，返回一个值。

统计记录数

select count(1) from emp;

不建议使用 count(*)，可以使用一个具体的列以免影响性能。

求和函数

select sum(sal) from emp;—工资总和

最大值查询

select max(sal) from emp;—最大工资

最小值查询

select min(sal) from emp;—最低工资

查询平均值

select avg(sal) from emp;—平均工资

十一、分组查询

分组统计需要使用 GROUP BY 来分组

语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列

名 1 ASC|DESC，列名 2…ASC|DESC

• 查询出每个部门的平均工资

• 分组查询中，出现在group by后面的原始列，才能出现在select后面

• 没有出现在group by后面的列，想在select后面，必须加上聚合函数。

• 聚合函数有一个特性，可以把多行记录变成一个值。

select e.deptno, avg(e.sal)--, e.ename
from emp e
group by e.deptno;

• 查询出平均工资高于2000的部门信息

select e.deptno, avg(e.sal) asalfrom emp egroup by e.deptno having avg(e.sal)>2000;

• 所有条件都不能使用别名来判断。
• 比如下面的条件语句也不能使用别名当条件

select ename, sal s from emp where sal>1500;

• 查询出每个部门工资高于800的员工的平均工资

select e.deptno, avg(e.sal) asalfrom emp ewhere e.sal>800 group by e.deptno;

• where是过滤分组前的数据，having是过滤分组后的数据。
• 表现形式：where必须在group by之前，having是在group by之后。
• 查询出每个部门工资高于800的员工的平均工资，然后再查询出平均工资高于2000的部门

select e.deptno, avg(e.sal) asalfrom emp ewhere e.sal>800group by e.deptnohaving avg(e.sal)>2000;

1. 如果使用分组函数，SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其他字段。
2. 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值

十二、多表查询

笛卡尔积

使用一张以上的表做查询就是多表查询

语法： SELECT {DISTINCT} *|列名… FROM 表名 别名，表名 1 别名

{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…}

• 查询员工表和部门表

select *from emp e, dept d;

等值连接

select * from emp e, dept dwhere e.deptno=d.deptno;

内连接

select *from emp e inner join dept don e.deptno = d.deptno;

外连接

—查询出所有部门，以及部门下的员工信息。

select * from emp e right join dept d on e.deptno=d.deptno;

—查询所有员工信息，以及员工所属部门

select *from emp e left join dept d on e.deptno=d.deptno;

oracle中专用外连接

用（+）来实现， 这个 + 号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

select *from emp e, dept dwhere e.deptno(+) = d.deptno;

自连接

自连接其实就是站在不同的角度把一张表看成多张表。

• 查询出员工姓名，员工领导姓名

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

• 查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称

select e1.ename, d1.dname, e2.ename, d2.dname from emp e1, emp e2, dept d1, dept d2 where e1.mgr = e2.empno and e1.deptno=d1.deptno and e2.deptno=d2.deptno; 

十三、子查询

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。 子查询返回一个值

Sql的任何位置都可以加入子查询。

子查询在操作中有三类：

单列子查询：返回的结果是一列的一个内容

单行子查询：返回多个列，有可能是一个完整的记录

多行子查询：返回多条记录

• 查询出工资和SCOTT一样的员工信息

  select * from emp where sal in(select sal from emp where ename = 'SCOTT')---子查询返回一个集合
  

• 查询出工资和10号部门任意员工一样的员工信息

  select * from emp where sal in(select sal from emp where deptno = 10);--子查询返回一张表
  

• 查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称

1. 先查询出每个部门最低工资select deptno, min(sal) msalfrom emp group by deptno;

2. 三表联查，得到最终结果。

   select t.deptno, t.msal, e.ename, d.dnamefrom (select deptno, min(sal) msal      from emp       group by deptno) t, emp e, dept dwhere t.deptno = e.deptnoand t.msal = e.saland e.deptno = d.deptno;
   

十四、分页查询rownum

当我们做select操作的时候，每查询出一行记录，就会在该行上加上一个行号，行号从1开始，依次递增，不能跳着走。

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

rownum不支持大于号，只支持小于号

• 排序操作会影响rownum的顺序

select rownum, e.* from emp e order by e.sal desc

• 如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。

select rownum, t.* from(select rownum, e.* from emp e order by e.sal desc) t;

----emp表工资倒叙排列后，每页五条记录，查询第二页。----rownum行号不能写上大于一个正数。

第一种写法
select * from
(
	select rownum rn, tt.* from
		(select * from emp order by sal desc) tt 
	where rownum<11
) where rn>5;
第二种写法：
select * 
from (select rownum r ,emp.* from emp) b
where b.r >5 and b.r <11

十五、视图

15.1.视图的概念

视图就是提供一个查询的窗口，所有数据来自于原表。

视图就是封装了一条复杂查询的语句。

15.2.视图的语法

语法 1.：CREATE VIEW 视图名称 AS 子查询

语法 2：CREATE OR REPLACE VIEW 视图名称 AS 子查询（如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖）

语法 3：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

1.创建视图必须有dba权限

2.修改视图的话视图所查询的表的字段值被修改了。所以一般不会去修改视图。

---查询语句创建表

create table emp as select * from scott.emp;--LYADMIN用户

select * from emp;--LYADMIN用户

---创建视图

create view v_emp as select ename, job from emp;--LYADMIN用户

---查询视图

select * from v_emp;--LYADMIN用户

---修改视图[不推荐]

update v_emp set job='CLERK' where ename='ALLEN';--LYADMIN用户

commit;--LYADMIN用户

---创建只读视图

create view v_emp1 as select ename, job from emp with read only;--LYADMIN用户

15.3.视图的作用

第一：视图可以屏蔽掉一些敏感字段。

第二：保证总部和分部数据及时统一。

十六、索引

16.1.索引的概念

索引就是在表的列上构建一个二叉树—达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

索引是用于加速数据存取的数据对象。

合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

16.2.单列索引

单列索引是基于单个列所建立的索引 。创建单列索引：

CREATE index 索引名 on 表名(列名)

• 单列索引触发规则，条件必须是索引列中的原始值。
• 单行函数，模糊查询，都会影响索引的触发。

create index idx_ename on emp(ename);

select * from emp where ename='SCOTT'

16.3.复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,比如：

Create index emp_idx1 on emp(ename,job); 

Create index emp_idx1 on emp(job,ename);

• 复合索引中第一列为优先检索列
• 如果要触发复合索引，必须包含有优先检索列中的原始值。

select * from emp where ename='SCOTT' and job='xx';---触发复合索引

select * from emp where ename='SCOTT' or job='xx';---不触发索引

select * from emp where ename='SCOTT';---触发单列索引。

16.4.索引的使用原则

在大表上建立索引才有意义

在 where 子句后面或者是连接条件上的字段建立索引

表中数据修改频率高时不建议建立索引

十七、pl/sql编程语言

17.1.pl/sql概念

pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。

• pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
• pl/sql编程语言主要用来编写存储过程和存储函数等。

PL/SQL（Procedure Language/SQL）PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

17.2.pl/sql程序语法

declare 

 说明部分 （变量说明，游标申明，例外说明 〕 

begin 

 语句序列 （DML 语句〕…  

exception 

 例外处理语句  

End;

声明方法

赋值操作可以使用:=也可以使用into查询语句赋值

17.3.pl/sql常量和变量定义

在程序的声明阶段可以来定义常量和变量。

• 变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number，boolean, long

定义语法：varl char(15);

Psal number(9,2);

说明变量名、数据类型和长度后用分号结束说明语句。

常量定义：married constant boolean:=true

• 引用变量

Myname emp.ename%type;

引用型变量，即 my_name 的类型与 emp 表中 ename 列的类型一样

在 sql中使用 into 来赋值

declare 

	emprec emp.ename%type; 

begin 

	select t.ename into emprec from emp t where t.empno = 7369; 

 	dbms_output.put_line(emprec); 

end; 

• 记录型变量

Emprec emp%rowtype

记录变量分量的引用

emp_rec.ename:=‘ADAMS’;

declare 

 p emp%rowtype; 

begin 

 select * into p from emp t where t.empno = 7369; 

 dbms_output.put_line(p.ename || ' ' || p.sal); 

end;

declare

    i number(2) := 10;

    s varchar2(10) := '小明';

    ena emp.ename%type;---引用型变量

    emprow emp%rowtype;---记录型变量

begin

    dbms_output.put_line(i);

    dbms_output.put_line(s);

    select ename into ena from emp where empno = 7788;

    dbms_output.put_line(ena);

    select * into emprow from emp where empno = 7788;

    dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);

end;

17.4.pl/sql中的if判断

语法 1： 

IF 条件 THEN 语句 1; 

	语句 2; 

 END IF; 

语法 2： 

IF 条件 THEN 语句序列 1；  

	ELSE 语句序列 2； 

	END IF； 

语法 3： 

IF 条件 THEN 语句; 

	ELSIF 语句 THEN 语句;

	ELSE 语句; 

	END IF; 

范例 1：如果从控制台输入 1 则输出我是 1
declare
 pnum number := #
begin
 if pnum = 1 then
 dbms_output.put_line('我是1');
 end if;
end;
范例 2：如果从控制台输入 1 则输出我是 1 否则输出我不是 1
declare
 mynum number := #
begin
 if mynum = 1 then
 dbms_output.put_line('我是1');
 else
 dbms_output.put_line('我不是1');
 end if;
end;
范例 3:判断人的不同年龄段 18 岁以下是未成年人，18 岁以上 40 以下是成年人，40 以上是老年人
declare
 mynum number := #
begin
 if mynum < 18 then
 dbms_output.put_line('未成年人');
 elsif mynum >= 18 and mynum < 40 then
 dbms_output.put_line('中年人');
 elsif mynum >= 40 then
 dbms_output.put_line('老年人');
 end if;
end;

17.5.pl/sql中的loop循环

语法 1： 

WHILE total <= 25000 LOOP

.. . 

total : = total + salary; 

END LOOP; 

语法 2： 

Loop 

EXIT [when 条件]; 

…… 

End loop 

语法 3： 

FOR I IN 1 . . 3 LOOP 

语句序列 ; 

END LOOP ; 

范例:使用语法 1 输出 1 到 10 的数字
declare
 step number := 1;
begin
 while step <= 10 loop
 dbms_output.put_line(step);
 step := step + 1;
 end loop;
end;
范例:使用语法 2 输出 1 到 10 的数字
declare
 step number := 1;
begin
 loop
 exit when step > 10;
 dbms_output.put_line(step);
 step := step + 1;
 end loop;
end;
范例:使用语法 3 输出 1 到 10 的数字
declare
 step number := 1;
begin
 for step in 1 .. 10 loop
 dbms_output.put_line(step);
 end loop;
end;

17.6.pl/sql_游标 Cursor

游标：可以存放多个对象，多行记录。

语法： 

CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;

游标的使用步骤：

 打开游标： open c1; (打开游标执行查询)

 取一行游标的值：fetch c1 into pjob; (取一行到变量中)

 关闭游标： close c1;(关闭游标释放资源)

 游标的结束方式 exit when c1%notfound

cursor c1 is select ename from emp;

注意： 上面的 pjob 必须与 emp 表中的 job 列类型一致： 定义：pjob emp.empjob%type;

范例 1：使用游标方式输出 emp 表中的员工编号和姓名
declare
 cursor pc is
 select * from emp;
 pemp emp%rowtype;
begin
 open pc;
 loop
 fetch pc
 into pemp;
 exit when pc%notfound;
 dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
 end loop;
 close pc;
end;
范例 2：按员工的工种涨工资,总裁 1000 元，经理涨 800 元其，他人员涨 400 元。
备份出一张新表为 myemp;create table myemp as select * from emp;
declare
 cursor pc is
 select * from myemp;
 addsal myemp.sal%type;
 pemp myemp%rowtype;
begin
 open pc;
 loop
 fetch pc
 into pemp;
 exit when pc%notfound;
 if pemp.job = 'PRESIDENT' then
 addsal := 1000;
 elsif pemp.job = 'MANAGER' then
 addsal := 800;
 else
 addsal := 400;
 end if;
 update myemp t set t.sal = t.sal + addsal where t.empno = 
pemp.empno;
 end loop;
 close pc;
end;
范例 3：写一段 PL/SQL 程序，为部门号为 10 的员工涨工资。
declare
 cursor pc(dno myemp.deptno%type) is
 select empno from myemp where deptno = dno;
 pno myemp.empno%type;
begin
 open pc(20);
 loop
 fetch pc
 into pno;
 exit when pc%notfound;
 update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
 end loop;
 close pc;
end;

十八、存储过程

18.1. 存储过程概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经

编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来

执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存

储过程。

18.2.存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

AS  

begin 

 PLSQL 子程序体； 

End; 

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

is 

begin 

 PLSQL 子程序体； 

End 过程名;

范例：创建一个输出 helloword 的存储过程
create or replace procedure helloworld is
begin
 dbms_output.put_line('helloworld');
end helloworld;
调用存储过程
在 plsql 中调用存储过程
begin
 -- Call the procedure 
 helloworld;
end;
范例 2：给指定的员工涨 100 工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程
create or replace procedure addSal1(eno in number) is
 pemp myemp%rowtype;
begin
 select * into pemp from myemp where empno = eno;
 update myemp set sal = sal + 100 where empno = eno;
 dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || 
(pemp.sal + 100));
end addSal1;
调用
begin
 -- Call the procedure
 addsal1(eno => 7902); 
 commit;
end;

存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
----给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is

begin
update emp set sal=sal+100 where empno = eno;
commit;
end;

select * from emp where empno = 7369;
----测试p1
declare

begin
p1(7369);
end;

十九、存储函数

19.1.存储函数语法

create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is 
 结果变量 数据类型; 
begin 

 return(结果变量); 
end 函数名;

• 存储过程和存储函数的参数都不能带长度
• 存储函数的返回值类型不能带长度

--通过f_yearsal存储函数实现计算指定员工的年薪
create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);     
begin
  select sal*12+nvl(comm, 0) into s from emp where empno = eno;
  return s;
end;
--测试f_yearsal
----存储函数在调用的时候，返回值需要接收。
declare
  s number(10); 
begin
  s := f_yearsal(7902);
  dbms_output.put_line(s);
end;

19.2.out类型参数的使用

--使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
   s number(10);
   c emp.comm%type;
begin
   select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
   yearsal := s+c;
end;
---测试p_yearsal
declare
  yearsal number(10);
begin
  p_yearsal(7902, yearsal);
  dbms_output.put_line(yearsal);
end;

19.3.in和out类型参数的区别

1.IN(默认参数模式)：表示当存储过程别调用时，实参值被传递给形参；形参起变量作用，只能读该参数，而不能修改该参数。IN模式参数可以是变量或表达式。
2.OUT：表示当存储过程被调用时，实参值被忽略；形参起未初始化的PL/SQL变量的作用，形参的初始值为NULL，可以进行读/写操作，在存储过程调用结束后，形参值被给实参。OUT模式参数只能是变量，不能是常量或表达式。
3.IN OUT表示当存储过程被调用时，形参值被传递给形参。形参起已初始化的PL/SQL变量的作用，可读可写。IN OUT 模式参数只能是变量，不能是常量或表达式。
4.使用OUT、IN OUT模式参数时只有当程序正常结束时形参值才会传递给实参。

凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。

19.4.存储过程和存储函数的区别

• 语法区别：关键字不一样，存储函数比存储过程多了两个return。

• 本质区别：存储函数有返回值，而存储过程没有返回值。但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实现返回多个值。

  • 如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
  • 即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
  • 而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。
  • 我们可以使用存储函数有返回值的特性，来自定义函数。
  • 而存储过程不能用来自定义函数。

--范例：使用存储函数来查询指定员工的年薪
create or replace function empincome(eno in emp.empno%type) return number is
 psal emp.sal%type;
 pcomm emp.comm%type;
begin
 select t.sal into psal from emp t where t.empno = eno;
 return psal * 12 + nvl(pcomm, 0);
end;
--使用存储过程来替换上面的例子
create or replace procedure empincomep(eno in emp.empno%type, income out number) is
 psal emp.sal%type;
 pcomm emp.comm%type;
begin
 select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
 income := psal*12+nvl(pcomm,0);
end empincomep;
--调用：
declare
 income number;
begin
 empincomep(7369, income);
 dbms_output.put_line(income);
end;

19.5.调用存储函数方法

1.在SQL查询中直接调用：

SELECT empincome(7902) FROM dual;

2.在PL/SQL代码块中调用：

DECLARE
  result_variable_name number;
BEGIN
  result_variable_name := empincome(7900);
  -- 使用result_variable_name
	dbms_output.put_line(result_variable_name);
END;

3.在SQL*Plus或SQLcl命令行工具中使用VARIABLE声明局部变量，并调用函数为其赋值：

VARIABLE result_variable_name number;
EXEC :result_variable_name := empincome(7900);
print result_variable_name;

19.6.案例

查询出员工姓名，员工所在部门名称。

----案例准备工作：把scott用户下的dept表复制到当前用户下。

create table dept as select * from scott.dept;

----使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  return dna;
end;
---使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno) from emp e;

二十、触发器

21.1.触发器概念

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

—触发器，就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，自动触发，无需调用。

21.2.触发器作用

• 数据确认

• 实施复杂的安全性检查

• 做审计，跟踪表上所做的数据操作等

• 数据的备份和同步

21.3.触发器的类型

• 语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行，不包含有for each row的触发器。
• 行级触发器：触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态。包含有for each row的就是行级触发器。

-----------加for each row是为了使用:old或者:new对象或者一行记录。

21.4.触发器的语法

CREATE [or REPLACE] TRIGGER 触发器名
 {BEFORE | AFTER}
 {DELETE | INSERT | UPDATE [OF 列名]}
 ON 表名
 [FOR EACH ROW [WHEN(条件) ] ]
begin
 PLSQL 块
End 触发器名

---语句级触发器
----插入一条记录，输出一个新员工入职
create or replace trigger t1
  after insert on person
declare
begin
  dbms_output.put_line('一个新员工入职');
end;
---触发t1
insert into person values (1, '小红');
commit;
select * from person;

---行级别触发器
---不能给员工降薪
---raise_application_error(-20001~-20999之间, '错误提示信息');
create or replace trigger t2
  before update on emp for each row
declare
begin
  if :old.sal>:new.sal then
     raise_application_error(-20001, '不能给员工降薪');
  end if;
end;
----触发t2
select * from emp where empno = 7902;
update emp set sal=sal-1 where empno = 7902;
commit;

在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

21.5.触发器案例

触发器实现主键自增。【行级触发器】

--分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create or replace trigger auid
  before insert on person for each row
declare
begin
  select s_person.nextval into :new.pid from dual;
end;
--查询person表数据
select * from person;
---使用auid实现主键自增
insert into person (pname) values ('a');
commit;
insert into person values (1, 'b');
commit;

二十一、Java 程序调用存储过程

21.1.java 连接 oracle 的 jar 包

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>jdbc_oracle</artifactId>
    <version>1.0-SNAPSHOT</version>


    <dependencies>
        <dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>orai18n</artifactId>
            <version>12.1.0.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

21.2.数据库连接字符串

String driver="oracle.jdbc.OracleDriver";
String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";
String username="scott";
String password="tiger";

21.3.实现过程的调用

过程定义

--统计年薪的过程
create or replace procedure proc_countyearsal(eno in number,esal
out number)
as
begin
 select sal*12+nvl(comm,0) into esal from emp where empno=eno;
end;
--调用
declare
 esal number;
begin
 proc_countyearsal(7839,esal);
 dbms_output.put_line(esal);
end;

过程调用

package com.itheima.oracle;

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.*;

public class OracleDemo {

    @Test
    public void javaCallOracle() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.53.155:1521:cdc", "lyadmin", "123456");
        //得到预编译的Statement对象
        PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
        //给参数赋值
        pstm.setObject(1, 7902);
        //执行数据库查询操作
        ResultSet rs = pstm.executeQuery();
        //输出结果
        while(rs.next()){
            System.out.println("ename: "+rs.getString("ename"));
        }
        //释放资源
        rs.close();
        pstm.close();
        connection.close();
    }

    /**
     * java调用存储过程
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
     *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallProcedure() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.53.155:1521:cdc", "lyadmin", "123456");
        //得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{call proc_countyearsal(?, ?)}");
        //给参数赋值
        pstm.setObject(1, 7902);
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
        //执行数据库查询操作
        pstm.execute();
        //输出结果[第二个参数]
        System.out.println(pstm.getObject(2));
        //释放资源
        pstm.close();
        connection.close();
    }


    /**
     * java调用存储函数
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
     *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallFunction() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.53.155:1521:cdc", "lyadmin", "123456");
        //得到预编译的Statement对象
        CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
        //给参数赋值
        pstm.setObject(2, 7902);
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        //执行数据库查询操作
        pstm.execute();
        //输出结果[第一个参数]
        System.out.println(pstm.getObject(1));
        //释放资源
        pstm.close();
        connection.close();
    }
}