一篇文章让你了解Oracle(Oracle知识点总汇)

最新推荐文章于 2024-03-23 19:13:13 发布

小跟班儿oo

最新推荐文章于 2024-03-23 19:13:13 发布

阅读量1.3k

点赞数 1

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_51753851/article/details/131731386

版权

本章介绍Oracle数据库的知识点，本章将会简明扼要的去介绍Oracle数据。对于那些官方介绍说辞将简短介绍，在学习Oracle之前，这里建议大家一定要先学好Myqsl数据，这将对于我们学习Oracle有极大的帮助，对于文章中出现的错误，问题，不懂之处欢迎大家积极的讨论。

DUAL 是一个‘伪表’，可以用来测试函数和表达式

一、ORACLE 体系结构

在我们学习Oracle数据库前，我们一定要了解一些概念，能够更好地帮助我们去了解Oracle数据库
平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统. Oracle 数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system). 它由Oracle 数据库和 Oracle 实例(instance)构成

1.数据库

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

2.实例

一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)
和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例，
在任何时刻一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）

3.数据文件（dbf）

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

4.表空间

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

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件
(datafile)。一个数据文件只能属于一个表空间。
**由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行
管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用
户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！
**

5.用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表, ORACLE
的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户。

在这里插入图片描述

6.Oracle 的(资源限制)概要文件

1.概念

为了控制系统资源的使用, 可以利用资源限制概要文件

资源限制概要文件是 Oracle 安全策略的重要组成部分, 利用资源限制概要文件可以对数据库用户进行基本的资源限制, 而且还可以对用户的口令进行管理

在这里插入图片描述

2.Oracle 数据库的默认概要文件

每个 Oracle 数据库都有一个默认的资源概要文件, 名为 DEFAULT

当创建一个新的数据库用户且不对用户分配一个特定的概要文件时, Oracle 自动给用户分配数据库的 DEFAULT 概要文件. 默认时,数据库 DEFAULT 概要文件的所有资源限制设置为无限制的

2、安装

具体的安装就不说了，可以去查找别的资料，但是有些截图我们要知道。
在这里插入图片描述

这里我们这只远程连接配置，如下选中的文件，然后可以配置对应的

三、Oracle知识点

1、创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

2、创建用户

create user wateruser
identified by itcast
default tablespace waterboss

wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

3、用户权限

grant dba to wateruser

4.创建表

CREATE TABLE 表名称(
 字段名 类型(长度) primary key,
 字段名 类型(长度),
 .......
);

字符型
（1）CHAR : 固定长度的字符类型，最多存储 2000 个字节
（2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
（3）LONG : 大文本类型。最大可以存储 2 个 G
2.数值型
NUMBER : 数值类型
例如：NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99
3.日期型
（1）DATE：日期时间型，精确到秒
（2）TIMESTAMP：精确到秒的小数点后 9 位
4.二进制型（大数据类型）
（1）CLOB : 存储字符,最大可以存 4 个 G
（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个

5.修改表

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)


ALTER TABLE T_OWNERS ADD
(
 REMARK VARCHAR2(20),
 OUTDATE DATE
)

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)

ALTER TABLE T_OWNERS MODIFY
(
REMARK CHAR(20),
OUTDATE TIMESTAMP
)

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE

--删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
--删除多个字段
ALTER TABLE 表名称 DROP (列名 1,列名 2...

--删除字段
ALTER TABLE T_OWNERS DROP COLUMN REMARK


DROP TABLE 表名称

5.数据增删改查

INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...)
insert into T_OWNERS VALUES (1,' 张三丰
',1,'2-2','5678',sysdate,1);

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；
update T_OWNERS set adddate=adddate-3 where id=1;
commit;


DELETE FROM 表名 WHERE 删除条件;
delete from T_OWNERS where id=2;
commit;

TRUNCATE TABLE 表名称

6.数据库连接驱动

1、引入jar包，ojdbc14.jar

public class BaseDao {
//加载驱动
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接
* @return
* @throws SQLException
*/
public static java.sql.Connection getConnection() throws
SQLException{
return java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.80.10:1521:orcl",
 "wateruser", "itcast");
}
/**
* 关闭资源
* @param rs
* @param stmt
* @param conn
*/
public static void closeAll(java.sql.ResultSet rs,
java.sql.Statement stmt,java.sql.Connection conn)
{
//关闭结果集
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭执行对象
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭执行对象
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

JDBC 驱动为：
oracle.jdbc.OracleDriver

连接字符串( 瘦连接 )：
jdbc:oracle:thin:@虚拟机的 IP:1521:orcl

7.数据库导入与导出

整库导出命令
exp system/itcast full=y

指定备份文件的名称
exp system/itcast file=文件名 full=y

整库导入命令
imp system/itcast full=y
如果指定 file 参数，则按照 file 指定的备份文件进行恢复
imp system/itcast full=y file=water.dmp

按用户导出
exp system/itcast owner=wateruser file=wateruser.dmp

按用户导
imp system/itcast file=wateruser.dmp fromuser=wateruser

按表导出
exp wateruser/itcast file=a.dmp tables=t_account,a_area

按表导入
imp wateruser/itcast file=a.dmp tables=t_account,a_area

8.单表查询

单表查询中其中普通的数据查询和mysql一样，这里我们就不重复赘述了，下面要介绍的一点就是基于伪劣的查询。

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就
像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。
接下来学习两个伪列：ROWID 和 ROWNUM。

8.1 ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的
物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的
标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可
以显示行是如何存储的。

select rowID,t.* from T_AREA t

8.2 ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，
第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

select rownum,t.* from T_OWNERTYPE t

9. 聚合统计

求和 su
求平均 avg
求最大值 max
求最小值 min
统计记录个数 count

分组聚合 Group by
select areaid,sum(money) from t_account group by areaid

分组后条件查询 having
select areaid,sum(money) from t_account group by areaid
having sum(money)>169000

10、连接查询

1、多表内连接查询

在这里插入图片描述

2、左外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主
没有账务记录也要列出姓名。
在这里插入图片描述
如果是左外连接，就在右表所在的条件一端填上（+）

3.右外连接查询

在这里插入图片描述

11、子查询

1.where子查询

单值子查询
单行子查询
多行子查询

2.from子查询

from 子句的子查询为多行子查

select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id)
where 业主类型='居民

3.select 子句中的子查

select 子句的子查询必须为单行子查

select id,name,
(select name from t_address where id=addressid) addressname
from t_owners

在这里插入图片描述

12、分页查询

1、简单分页
执行这段代码的时候，我们发现我们能够正常的查询到所有的数据，也包括我们的rownum！

select rownum,t.* from T_ACCOUNT t

在这里插入图片描述
在接下里我们继续看我们的代码，如果我们想根据条件查询我们10到20的数据。我们发现，诶？什么数据也没有，这是为什么呢？我们的数据也没有报错。这是因为们rownum是根据扫描出来的数据而产生的，解释下就是当我们查询到数据后，根据查询到的数据进行一个排序产生的序列号，这里我们测试后发现小于和小于等于都能显示数据，但是等于，大于，大于等于都不能显示出数据。还是回到那句话rownum是随着扫描表而产生的，比如我们还没有扫描到rownum大于10的数据，这时候就显示未空，查不到满足条件的数据。

select rownum,t.* from T_ACCOUNT t
where rownum>10 and rownum<=20

在这里插入图片描述

解决
那么我们怎么解决这个问题呢？就是靠子查询！我们先通过子查询将需要查询的数据查出来，这个时候所有的数据查询出来了rownum也根据扫描到的数据进行了排序。这时候我们就能根据查询的结果进行条件查询了。

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>1

在这里插入图片描述
2.基于排序的分页
我们看下面的代码以及截图的执行结果！我们发现我们的伪劣rownum是乱序的，我们接着看下我们子查询的代码

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20 order
by usenum desc)
where r>10

在这里插入图片描述
这里我们是子查询的代码，你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描
是产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的

select rownum r,t.* from T_ACCOUNT t
where rownum<=20 order by usenum desc

在这里插入图片描述
我们只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后
的结果再产生 R，这样就不会乱

select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10

在这里插入图片描述

13、函数

函数说明
ASCII 返回对应字符的十进制值
CHR 给出十进制返回字符
CONCAT 拼接两个字符串，与 || 相同
INITCAT 将字符串的第一个字母变为大写
INSTR 找出某个字符串的位置
INSTRB 找出某个字符串的位置和字节数
LENGTH 以字符给出字符串的长度
LENGTHB 以字节给出字符串的长度
LOWER 将字符串转换成小写
LPAD 使用指定的字符在字符的左边填充
LTRIM 在左边裁剪掉指定的字符
RPAD 使用指定的字符在字符的右边填充
RTRIM 在右边裁剪掉指定的字符
REPLACE 执行字符串搜索和替换
SUBSTR 取字符串的子串
SUBSTRB 取字符串的子串（以字节）
SOUNDEX 返回一个同音字符串
TRANSLATE 执行字符串搜索和替换
TRIM 裁剪掉前面或后面的字符串
UPPER 将字符串变为大

ABS(value) 绝对值
CEIL(value) 大于或等于 value 的最小整数
COS(value) 余弦
COSH(value) 反余弦
EXP(value) e 的 value 次幂
FLOOR(value) 小于或等于 value 的最大整数
LN(value) value 的自然对数
LOG(value) value 的以 10 为底的对数
MOD(value,divisor) 求模
POWER(value,exponent) value 的 exponent 次幂
ROUND(value,precision) 按 precision 精度 4 舍 5 入
SIGN(value) value 为正返回 1;为负返回-1;为 0 返回 0. SIN(value) 余弦
SINH(value) 反余弦
SQRT(value) value 的平方根
TAN(value) 正切
TANH(value) 反正切
TRUNC(value,按 precision) 按照 precision 截取 value
VSIZE(value) 返回 value 在 ORACLE

ADD_MONTHS 在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .) 从日期列表中选出最晚的日期
LAST_DAY( date ) 返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .) 从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1)
给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’) 给出日期 date 之后下一天的日期，这里的 day 为星期，
如： MONDAY,Tuesday 等。
NEW_TIME(date,’this’,’other’) 给出在 this 时区=Other 时区的日期和时间
ROUND(date,’format’) 未指定 format 时，如果日期中的时间在中午之前，则
将日期中的时间截断为 12 A.M.(午夜，一天的开始),否
则进到第二天。时间截断为 12 A.M.(午夜，一天的开始), 否则进到第二天。
TRUNC(date,’format’) 未指定 format 时，将日期截为 12 A.M.( 午夜，一天的
开始)

CHARTOROWID 将字符转换到 rowid 类型
CONVERT 转换一个字符节到另外一个字符节
HEXTORAW 转换十六进制到 raw 类型
RAWTOHEX 转换 raw 到十六进制
ROWIDTOCHAR 转换 ROWID 到字符
TO_CHAR 转换日期格式到字符串
TO_DATE 按照指定的格式将字符串转换到日期型
TO_MULTIBYTE 把单字节字符转换到多字节
TO_NUMBER 将数字字串转换到数字
TO_SINGLE_BYTE 转换多字节到单字节

14、空值处理

1、空值处理函数 NVL

在这里插入图片描述

2、空值处理函数 NVL2

在这里插入图片描述

3.NULLIF

NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

在这里插入图片描述

3.条件取值 decode

在这里插入图片描述

15、行列转换

在这里插入图片描述

16、分析函数

在这里插入图片描述

1、RANK 相同的值排名相同，排名跳跃

在这里插入图片描述

2、DENSE_RANK 相同的值排名相同，排名连续

在这里插入图片描述

3.ROW_NUMBER 返回连续的排名，无论值是否相等

在这里插入图片描述

17、集合运算

··

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

· 在这里插入图片描述

·UNION(并集)，返回各个查询的所有记录，不包括重复记录。

· 在这里插入图片描述

·INTERSECT(交集)，返回两个查询共有的记录。

· 在这里插入图片描述

·MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

18、连接符

通过||这个连接符，把列与列连接符连接在一起，可以合成列。

SELECT	last_name||job_id AS "Employees"
FROM 	employees;

19、函数

1.字符串转换函数

在这里插入图片描述 initcap作用是首字母大写。

2.日期函数

在这里插入图片描述

3.日期或数字转字符串

年（yyyy）、月（mm）、日（dd）、时（hh、hh24）、分（mi）、秒（ss）。

在这里插入图片描述

4.字符串转日期TO_DATE

在这里插入图片描述

5.字符串转数字

在这里插入图片描述

20.约束

在这里插入图片描述

四、视图

1、介绍

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视
图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图
的结构和数据是对数据表进行查询的结果。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多
个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使
用。

优点
在这里插入图片描述 创建视图语句：

CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH CHECK OPTION ] 
[WITH READ ONLY]

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

删除视图

DROP VIEW view_name

2、简单视图

什么是简单视图？如果视图中的语句只是单表查询，并且没有聚合函数，我们就
称之为简单视图。对于简单视图，我们不仅可以用查询，还可以增删改记录。

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1

视图其实是一个虚拟的表，它
的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了
表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL
语句。

2、带检查约束的视图

我们创建视图的时候后规定了的查询的作用范围，内容区域为id为2的内容。这个时候我们如果进通过视图进行数据的改编，会发生什么呢？我们发现报错了。是因为我们想通过视图修改id为4的值，不在视图的内容区域，所以就会报错。

create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option

update view_address2 set areaid=1 where id=4

在这里插入图片描述

4. 只读视图的创建与使用

如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视
图时指定 WITH READ ONLY 选项，这样创建的视图就是一个只读视图。
需求：将上边的视图修改为只读视图

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only

在这里插入图片描述

5.创建带错误的视图

如果我们创建一个错误的视图，比如表不存在这个时候，我们的程序报错，为了不报错，我们用关键词force
create or replace FORCE view view_TEMP as
select * from T_TEMP

6.复杂视图的创建与使用

所谓复杂视图，就是视图的 SQL 语句中，有聚合函数或多表关联查询。
1、多表联查
我们创建了一个多表联查的视图，我们尝试通过视图去修改数据，我们发现第一个sql可以修改，但是第二条sql执行失败，我们看错误的描述，意思是我们修改的列不属于保留表的列。什么是保留表呢？保留表是连接视图修改限制的一个基本概念，该表的主键列全部显示在视
图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连
接视图中也是键值，那么就称这个表为键保留表
在我们这个例子中，视图中存在两个表，业主表（T_OWNERS）和业主类型表
（T_OWNERTYPE）, 其中 T_OWNERS 表就是键保留表，因为 T_OWNERS 的
主键也是作为视图的主键。键保留表的字段是可以更新的，而非键保留表是不能
更新的。

create or replace view view_owners as
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot 
where o.ownertypeid=ot.id

在这里插入图片描述 2.分组聚合统计查询的例子

7.物化视图

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语
句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次
访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化
视图）。
物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张
表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样
的。

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

BUILD IMMEDIATE 是在创建物化视图的时候就生成数据
BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。
默认为 BUILD IMMEDIATE。
刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种
方式和基表进行同步。
REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。FAST
刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整
个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判
断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE
的方式。FORCE 是默认的方式。
刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要
手动刷新物化视图（默认）。ON COMMIT 指在基表发生 COMMIT 操作时自动
刷新。

1.创建手动刷新的物化视图

create materialized view mv_address 
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

在这里插入图片描述

2.创建自动刷新的物化视图，和上例一样的结果集

创建此物化视图后，当 T_ADDRESS 表发生变化时，MV_ADDRESS2 自动跟着
改变。

create materialized view mv_address2 
refresh 
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

3.创建时不生成数据的物化视图

create materialized view mv_address3
build deferred 
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

在这里插入图片描述

由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须
再次执行 refresh

4.创建增量刷新的物化视图

在这里插入图片描述

SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U 表示 UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）
表示旧值，U 表示 UPDATE 操作。
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。
此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。
插入操作显示为：FE, 删除显示为：OO 更新操作则根据更新字段的位置而显示
不同的值。
当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

begin
DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

五、序列

序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。
可供多个用户用来产生唯一数值的数据库对象

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

create sequence 序列名称

NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值

select 序列名称.nextval from dual
select 序列名称.currval from dual

创建复杂序列

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默
认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

1. 有最大值的非循环序列

 create sequence seq_test1 
increment by 10
start with 10
maxvalue 300
minvalue 5

在这里插入图片描述

2. 有最大值的循环序列

create sequence seq_test2
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle ;

在这里插入图片描述

3. 带缓存的序列

在这里插入图片描述

create sequence seq_test5
increment by 10
start with 10
maxvalue 500
minvalue 9
cycle
cache 50;
把最小值减 1，或把最大值加 1，都可以通过。

4.修改和删除序列

 ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

DROP SEQUENCE 序列名称;

六、同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以
及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全
性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。
同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义
词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊
用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，
只允许特定用户或者有基对象访问权限的用户进行访问

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给
同义词的基对象赋予权限，同义词只是基对象的一个别名。

create [public] SYNONYM synooym for object;

其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要
创建同义词的对象的名称。

案例
为表 T_OWNERS 创建( 私有 )同义词名称为 OWNERS

create synonym OWNERS for T_OWNERS;

select * from OWNERS ;

为表 T_OWNERS 创建( 公有 )同义词名称为 OWNERS2：

create public synonym OWNERS2 for T_OWNERS;
select * from OWNERS2 ;

七、索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次
数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于
下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提
到的伪列（ROWID）

1、普通索引

create index 索引名称 on 表名(列名);

create index index_owners_name on T_OWNERS(name)

2、唯一索引

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可
以创建唯一索引。

create unique index 索引名称 on 表名(列名);

create unique index index_owners_watermeter on
T_OWNERS(watermeter);

3.复合索引

create index 索引名称 on 表名(列名,列名.....);

create index owners_index_ah
on T_OWNERS(addressid,housenumber);

4.反向键索引

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子
树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变
得不规则，从而使索引树能够均匀分布。

在这里插入图片描述

create index 索引名称 on 表名(列名) reverse;

5.位图索引

使用场景：位图索引适合创建在低基数列上
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射
优点：减少响应时间，节省空间占用

create bitmap index 索引名称 on 表名(列名);

create bitmap index index_owners_typeid
on T_OWNERS(ownertypeid)

八、PL/SQL

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

[declare 
 --声明变量
 ]
begin
 --代码逻辑 
[exception
 --异常处理
 ]
end;

一、变量

哈哈哈学习任何语言之前我们都要学习helloworld吧，接下来我们要首先学习一下输出语法

BEGIN

  --打印hello world

  DBMS_OUTPUT.PUT_LINE('hello world');

END;

我们默认情况下，输出选项是默认关闭状态的，所以我们要首先开启一下 set serveroutput on
在这里插入图片描述变量类型
普通数据类型（char,varchar2, date, number, boolean, long）
特殊变量类型（引用型变量、记录型变量）

变量名变量类型（变量长度）例如： v_name varchar2(20);
赋值

直接赋值语句 := 比如: v_name := ‘zhangsan’
语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

声明变量的语法：
变量名类型（长度）；

变量赋值的语法：
变量名:=变量值

--变量的用法--
declare
 v_price number(10,2);--水费单价
 v_usenum number; --水费字数
 v_usenum2 number(10,2);--吨数
 v_money number(10,2);--金额 
begin
 v_price:=2.45;--水费单价
 v_usenum:=8012;--字数
 --字数换算为吨数
 v_usenum2:= round( v_usenum/1000,2);
 --计算金额
 v_money:=round(v_price*v_usenum2,2);
 dbms_output.put_line('单价：'||v_price||'吨
数:'||v_usenum2||'金额：'||v_money); 
end;

Select into 方式赋值

select 列名 into 变量名 from 表名 where 条件

declare
 v_price number(10,2);--单价
 v_usenum number;--水费字数
 v_num0 number;--上月字数
 v_num1 number;--本月字数
 v_usenum2 number(10,2);--使用吨数
 v_money number(10,2);--水费金额
begin
 --对单价进行赋值
 v_price:=3.45;
 --变量赋值
 select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT 
 where year='2012' and month='01' and owneruuid=1;
 
 v_usenum2:= round(v_usenum/1000,2);
 v_money:=v_price*v_usenum2;
 DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
 ||v_usenum2||'金额：'||v_money||'上月字数：'||v_num0||'本月
字数'||v_num1);
end;

二、属性类型

1.%TYPE 引用型

declare
 v_price number(10,2);--单价
 v_usenum T_ACCOUNT.USENUM%TYPE;--水费字数
 v_num0 T_ACCOUNT.NUM0%TYPE;--上月字数
 v_num1 T_ACCOUNT.NUM1%TYPE;--本月字数
 v_usenum2 number(10,2);--使用吨数
 v_money number(10,2);--水费金额
begin
 --对单价进行赋值
 v_price:=3.45;
 --v_usenum:=8090;
 select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT 
 where year='2012' and month='01' and owneruuid=1;
 --使用吨数
 v_usenum2:= round(v_usenum/1000,2);
 --计算金额
 v_money:=v_price*v_usenum2;
 DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
 ||v_usenum2||'金额：'||v_money||'上月字数：'||v_num0||'本月
字数'||v_num1);
end;

2.%ROWTYPE 记录型

作用: 标识某个表的行记录类型

--变量的用法--
declare
 v_price number(10,2);--单价
 v_account T_ACCOUNT%ROWTYPE;--记录型
 v_usenum2 number(10,2);--使用吨数
 v_money number(10,2);--水费金额
begin
 --对单价进行赋值
 v_price:=3.45;
 --赋值
 select * into v_account from T_ACCOUNT 
 where year='2012' and month='01' and owneruuid=1;
 --使用吨数
 v_usenum2:= round(v_account.usenum/1000,2);
 --计算金额
 v_money:=v_price*v_usenum2;
 DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
 ||v_usenum2||'金额：'||v_money||'上月字数：
'||v_account.num0||'本月字数'||v_account.num1);
end;

三、异常

在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式
引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的
异常通过 RAISE 语句显式引发
在这里插入图片描述

exception
 when 异常类型 then
 异常处理逻辑

--变量的用法--
declare
 v_price number(10,2);--水费单价
 v_usenum T_ACCOUNT.USENUM%type; --水费字数
 v_usenum2 number(10,3);--吨数
 v_money number(10,2);--金额 
begin
 v_price:=2.45;--水费单价
 select usenum into v_usenum from T_ACCOUNT where
owneruuid=1 and year='2012' and month='01';
 --字数换算为吨数
 v_usenum2:= round( v_usenum/1000,3);
 --计算金额
 v_money:=round(v_price*v_usenum2,2);
 dbms_output.put_line('单价：'||v_price||'吨
数:'||v_usenum2||'金额：'||v_money);
exception
 when NO_DATA_FOUND then
 dbms_output.put_line('未找到数据，请核实');
 when TOO_MANY_ROWS then
dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;

四、条件判断

if 条件 then
 业务逻辑
end if;

if 条件 then
 业务逻辑
else
 业务逻辑
end if;

if 条件 then
 业务逻辑
elsif 条件 then
 业务逻辑
else
 业务逻辑 
end if;

五、循环

1、无条件循环

loop
 --循环语句
end loop;

declare
v_num number:=1;
begin 
 loop
 dbms_output.put_line(v_num);
 v_num:=v_num+1;
 exit when v_num>100;
 end loop; 
end ;

2、条件循环

while 条件
loop
end loop;

declare
v_num number:=1;
begin
 while v_num<=100
 loop
 dbms_output.put_line(v_num);
 v_num:=v_num+1; 
 end loop; 
end ;

3、for循环

for 变量 in 起始值..终止值
loop
 
end loop;



begin
 for v_num in 1..100
 loop
 dbms_output.put_line(v_num); 
 end loop;
end;

九、游标

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们
可以把游标理解为 PL/SQL 中的结果集。

用于临时存储一个查询返回的多行数据（结果集,类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明—>打开—>读取—>关闭
在这里插入图片描述

cursor 游标名称 is SQL 语句;

open 游标名称
loop
 fetch 游标名称 into 变量 
 exit when 游标名称%notfound
end loop;
close 游标名称

declare
 v_pricetable T_PRICETABLE%rowtype;--价格行对象
 cursor cur_pricetable is select * from T_PRICETABLE where
ownertypeid=1;--定义游标
begin
	open cur_pricetable;--打开游标
 loop
 fetch cur_pricetable into v_pricetable;--提取游标到变量
 exit when cur_pricetable%notfound;--当游标到最后一行下面退
出循环
 dbms_output.put_line( '价格:'
 ||v_pricetable.price ||'吨位：
'||v_pricetable.minnum||'-'||v_pricetable.maxnum ); 
 end loop; 
 close cur_pricetable;--关闭游标
end ;

在这里插入图片描述

1.带参数的游标
我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型，
可能是运行时才可以决定，那如何实现呢？我们接下来学习带参数的游标，修改
上述案例

declare
 v_pricetable T_PRICETABLE%rowtype;--价格行对象
 cursor cur_pricetable(v_ownertypeid number) is select * 
from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游
标
begin
	open cur_pricetable(2);--打开游标
 loop 
 fetch cur_pricetable into v_pricetable;--提取游标到变量
 exit when cur_pricetable%notfound;--当游标到最后一行下面退
出循环
 dbms_output.put_line('价格:'||v_pricetable.price ||'吨
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum ); 
 end loop; 
 close cur_pricetable;--关闭游标
end ;

3.for 循环提取游标值

 我们每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的
退出等等，好麻烦！有没有更简单的写法呢？有！用 for 循环一切都那么简单，
上例的代码可以改造为下列形式

declare
 cursor cur_pricetable(v_ownertypeid number) is select * 
from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游
标
begin
 for v_pricetable in cur_pricetable(3)
loop 
 dbms_output.put_line('价格:'||v_pricetable.price ||'吨
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum ); 
 end loop; 
end ;

十、存储函数语法结构

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。
在函数中我们可以使用 P/SQL 进行逻辑的处理。

CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
 变量声明部分;
BEGIN
 逻辑部分;
 RETURN 结果变量;
[EXCEPTION 
 异常处理部分]
END;

create function fn_getaddress(v_id number) 
return varchar2
is
 v_name varchar2(30);
begin
 select name into v_name from t_address where id=v_id;
 return v_name;
end;

select fn_getaddress(3) from dual

在这里插入图片描述

十一、存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如
下：
1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
传出参数返回多个值。
2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是
被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务
代码。
在这里插入图片描述

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名类型, 参数名类型, 参数名类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;

参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数

1. 创建不带传出参数的存储过程

--增加业主信息序列
create sequence seq_owners start with 11;
--增加业主信息存储过程
create or replace procedure pro_owners_add 
(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2, 
 v_watermeter varchar2,
 v_type number
)
is
 
begin
 insert into T_OWNERS 
values( seq_owners.nextval,v_name,v_addressid,v_housenumb
er,v_watermeter,sysdate,v_type );
 commit;
end;

call pro_owners_add('赵伟',1,'999-3','132-7',1);

/**
* 增加
* @param owners
*/
public static void add(Owners owners){
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call 
pro_owners_add(?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
}

2 创建带传出参数的存储过程

--增加业主信息存储过程
create or replace procedure pro_owners_add 
(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2, 
 v_watermeter varchar2,
 v_type number,
 v_id out number
)
is 
begin
 select seq_owners.nextval into v_id from dual;
 insert into T_OWNERS 
values( v_id,v_name,v_addressid,v_housenumber,v_watermete
r,sysdate,v_type );
 commit;
end;

declare
 v_id number;--定义传出参数的变量
begin
 pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
 DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;

/**
* 增加
* @param owners
*/
public static long add(Owners owners){
long id=0;
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call 
pro_owners_add(?,?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.registerOutParameter(6, OracleTypes.NUMBER);//注
册传出参数类型
stmt.execute();
id=stmt.getLong(6);//提取传出参数
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
return id;
}

十二、触发器

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

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

在这里插入图片描述

1. 前置触发器

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
 :new.usenum:=:new.num1-:new.num0;
end;

2. 后置触发器

--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
 
begin
 insert into t_owners_log 
values(sysdate,:old.id,:old.name,:new.name);
end;

--更新数据
update t_owners set name='杨小花' where id=3;
commit;
--查询日志表
select * from t_owners_log;

小跟班儿oo

关注

1
点赞
踩
30

收藏

觉得还不错? 一键收藏
打赏
0
评论
一篇文章让你了解Oracle(Oracle知识点总汇)

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。优点OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
复制链接

扫一扫