Oracle

2022-03-03 本文已影响0人请叫我平爷

Oracle

基础

创建表空间

```
create tablespace waterboss
datafile ' waterboss.dbf'
s ize 100 m
autoextend on
next 10 m
```

解释：

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

创建用户

create user wateruser
identified by itcast
default tablespace waterboss

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

用户赋权

-- 给用户 wateruser 赋予 DBA 权限后即可登陆
grant dba to wateruser

创建表

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

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

修改表

增加字段

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

修改字段

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

修改字段名语法：

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

删除字段名

ALTER TABLE 表名称 DROP COLUMN 列名

删除多个字段名

ALTER TABLE 表名称 DROP (列名 1, 列名 2...)

删除表

DROP TABLE 表名称

数据增删改查

插入数据

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

修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2,...WHERE 修改条件;

删除数据

DELETE FROM 表名 WHERE 删除条件;

注意：
INSERT INTO、UPDATE、DELETE都需要操作后，再执行事务

3.1、删除表
TRUNCATE TABLE 表名称;

TRUNCATE、DELETE实现数据删除的区别

delete删除的数据可以rollback
delete删除可能产生碎片，并且不释放空间
truncate 是先摧毁表结构，再重构表结构

数据的导出与导入

1.1 整库导出

exp system/itcast full=y

exp system/itcast file=文件名 full=y

1.2 整库导入

imp system/itcast full=y

imp system/itcast full=y file=water.dmp

full=y 就是整库导出，默认文件名EXPDAT.DMP

2.1 按用户导出

exp system/itcast owner=wateruser file=wateruser.dmp

2.2 按用户导入

imp system/itcast file=wateruser.dmp fromuser=wateruser

3.1 按表导出，多表用，分割即可

exp wateruser/itcast file=a.dmp tables=t_account,a_area

3.2 按表导入

imp wateruser/itcast file=a.dmp tables=t_account,a_area

DDL、DML

DDL 创建表、修改表、删除表
DML insert、update、delete

单表查询

select * from user where id='30408'
select * from user where name like '%刘%'
select * from user where name like '%刘%' and age=20
select * from user where name like '%刘%' and (age=20 or age=19)
select * from user where (age=20 or age=19)

范围查询

select * from user where age>=18 and age<=25
select * from user where age between 18 and 25

空值查询

select * from user where address is null
select * from user where address is not null

去除重复记录

select distinct address from user

排序

select * from user order by age
select * from user order by age desc

伪列ROWID

select rowid,t.* from user t
select rowid,t.* from user t where rowid = 'AAAM1uAAGAAAAD8AAC'

ROWNUM，分页用到

select rownum,t.* from user t

聚合函数

--求和sum
select sum(score) from user where age>20
--求平均avg
select avg(score) from user where age>20
--求最大值max
select max(score) from user where ager>20
--求最小值min
select min(score) from user where age>20
--统计个数count
select count(1) from user where age>20
--分组聚合group by 
select age,sum(score) from user group by age
--分组后条件查询having
select age,sum(score) from user group by age having sum(score)>20

连接查询

左连接

select t1.*,t2.* 
from table t1
left join table t2 on t2.t1_id=t1.id

select t1.*,t2.* 
from table t1,table t2
where t1.id=t2.t1_id(+)

子查询

select * from table t1 where t1.id=(select t1_id from table t2 where t2.id=1)

IN 等于列表中的任何一个
ANY 和子查询返回的任意一个值比较
ALL 和子查询返回的所有值比较

分页查询

1-10

select rownum,t.* from user t where rownum<10

11-20

select * from
(select rownum,t.* from user t where rownum<=20)
where r>10

排序后分页

11-20

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

函数

字符函数

函数名	备注	使用	结果
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串，与		相同	select concat('ABC','D') from dual;select 'ABC'	'D' from dual;	ABCDABCD
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度	select length('ABCD') from dual;	4
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串	select substr('ABCD',2,2) from dual;	BC
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)	求模，取余	select mod(10,3) from dual	1
POWER(value,exponent)	value 的exponent 次幂
ROUND(value,precision)	按precision 精度4 舍5 入	select round(100.567) from dualselect round(100.567,2) from dual	101100.57
SIGN(value)	value 为正返回1;为负返回-1;为0 返回0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value 的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,precision)	按照precision 截取value	select trunc(100.567) from dualselect trunc(100.567,2) from dual	100100.56
VSIZE(value)	返回value 在ORACLE 的存储空间大小

日期函数

函数名	备注	使用	结果
ADD_MONTHS	在日期date 上增加count 个月 select add_months(sysdate,2) from dual 2020/7/8 11:16:07
GREATEST(date1,date2,...)	从日期列表中选出最晚的日期
LAST_DAY(date) 返回日期date	所在月的最后一天 select last_day(sysdate) from dual 2020/5/31 11:16:46
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.( 午夜，一天的开始).	select TRUNC(sysdate) from dual	2020/5/8

转换函数

函数名	备注	使用	结果
CHARTOROWID	将字符转换到rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到raw 类型
RAWTOHEX	转换raw 到十六进制
ROWIDTOCHAR	转换ROWID 到字符
TO_CHAR	转换日期格式到字符串	select TO_CHAR(1024) from dualselect TO_CHAR(sysdate,'yyyy-mm-dd') from dual	10242020-05-08
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

其他函数

函数名	备注	使用
NVL(检测的值，如果为null 的值)		select NVL(NULL,0) from dual 0
NVL2(检测的值，如果不为null 的值，如果为null 的值)
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
RANK	相同的值排名相同，排名跳	select rank() over(order by usenum desc ),usenum from T_ACCOUNT
DENSE_RANK	相同的值排名相同，排名连续	select dense_rank() over(order by usenum desc ),usenumfrom T_ACCOUNT
ROW_NUMBER	返回连续的排名，无论值是否相等用row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:	select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

集合运算

函数名	备注	使用
UNION ALL(并集)	返回各个查询的所有记录，包括重复记录。	select * from t_owners where id<=7union allselect * from t_owners where id>=5
UNION(并集)	返回各个查询的所有记录，不包括重复记录。	select * from t_owners where id<=7unionselect * from t_owners where id>=5
INTERSECT(交集)	返回两个查询共有的记录。
MINUS(差集)	返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

视图

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

简化数据操作：视图可以简化用户处理数据的方式。
重于特定数据：不必要的数据或敏感数据可以不出现在视图中。
视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口

创建视图

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 操作。

--没有约束
create or replace 
view view_owners1 
as select * from T_OWNERS where ownertypeid=1

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

--满足视图定义的约束areaid= 2
create or replace 
view view_address2 
as select * from T_ADDRESS where areaid= 2
with check option

这个时候不能插入或者更新areaid不为2的数据

--只读，不能插入或更新
create or replace 
view view_owners1 
as select * from T_OWNERS where ownertypeid= 1
with read only

删除视图

DROP VIEW view_name

键保留表

键保留表:键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。

物化视图

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

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

手动刷新实物视图

--手动刷新实物视图
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

--插入一条记录
insert into t_address values 8 宏福苑小区 1 1

--手动刷新
--PL/SQL执行
begin
DBMS_MVIEW.refresh( refresh('MV_ADDRESS','C',)
end

--手动刷新方式二
--注意：此语句需要在命令窗口中执行。
EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');

自动刷新物化视图

--自动刷新物化视图
create materialized view mv_address 2
refre sh
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

不生成数据的物化视图
--不生成数据的物化视图
create materialized view mv_address3
build deferred
refresh
on commit
as
sele ct ad.id,ad.name adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;

--生成数据

begin
DBMS_MVIEW.refresh( refresh('MV_ ADDRESS3',,'
end

创建增量刷新的物化视图
- 如果创建增量刷新的物化视图，必须首先创建物化视图日志

create materialized view log on t_address with rowid
create materialized view log on t_area with rowid

创建物化视图

create materialized view mv_address4
refresh fast
as
selec t ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name
adname,ar.name ar_name
from t_address ad,t_area ar
where ad.areaid=ar.id;

注意：创建增量刷新的物化视图，必须：
1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志
  当我们向地址表插入数据后，物化视图日志的内容：
SNAPTIME$$:用于表示刷新时间。
DMLTYOE$$:用于表示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}];//分配并存入到内存中

--开始值不能小于最小值
--当序列值为 300 （最大值）的时候再次提取值，系统会报异常信息
create sequence seq_test1
increment by 10
start with 10
maxvalue 300
minvalue 5

--有最大值的循环序列，必须指定最大值
--当序列当前值为300 （最大值），再次提取序列的值为5
create sequence seq_test2
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle

--带缓存的序列
--缓存数量必须为501
create sequence seq_test3
increment by 10
start with 10
maxvalue 500
minvalue 9
cycl e
cache 50

修改序列，ALTER SEQUENCE 语句修改序列，无法更改序列的START WITH参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列

DROP SEQUENCE 序列名称

同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

创建同义词

create [public] SYNONYM synooym for object;

synooym 表示要创建的同义词名称
object 表示表、视图、序列等我们要创建同义词的对象名称

--私有同义词
create synonym OW NERS for T_OWNERS;
--使用同义词
select * from OWNERS ;

--共有同义词
create public synonym OWNERS 2 for T_OWNERS;
--以另外的用户登陆，也可以使用公有同义词：
select * from OWNERS 2

索引

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

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

普通索引

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

create index index_owners_name on T_OWNERS(name)

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

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

create unique index index_owners_watermeter on T_OWNERS(watermeter);

复合索引：我们经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。那如何建立索引呢？我们可以建立复合索引，也就是基于两个以上的列建立一个索引。

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

create index owners_index_ah on T_OWNERS(addressid,housenumber);

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

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

位图索引：使用场景：位图索引适合创建在低基数列上
- 位图索引不直接存储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;

变量

--表明变量的语法：
变量名 类型 (长度)

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

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(10,2); --上月字数
    v_num1 number(10,2); --本月字数
    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 ='2020' and month ='05' and owneruuid= 1
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;

属性类型

%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
...
end;

%ROWTYPE 记录型
- 标识某个表的行记录类型

declare
    v_price number 10 2 单价
    v_account T_ACCOUNT%ROWTYPE 记录型
    v_usenum2 number 10 2 使用吨数
    v_money number 10 2 水费金额
begin
...
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 ='2020' and month ='05';
--字数换算为吨数
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 
    业务逻辑 
else if 条件 then 
    业务逻辑 
else 
    业务逻辑 
end if;

循环

loop

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

while

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

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 中的结果集。

cursor 游标名称 is SQL 语句

使用游标语法

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

declare
    v_pricetable T_PRI CETABLE% 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

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

存储函数

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 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

存储函数中有返回值，且必须返回。而存储过程没有返回值，可以通过传出参数返回多个值。
存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

创建存储过程

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

过程参数的三种模式：
- IN 传入参数（默认）
- OUT 传出参数，主要用于返回程序运行结果
- IN OUT 传入传出参数
创建不带传出参数的存储过程

--创建不带传出参数的存储过程
--增加业主信息 序列
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_housenumber,v_watermeter, sysdate ,v_type);
    commit;
end;


--调用
call pro_owners_add('赵伟', 1 ,'999.3','132.7' 1)

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

增加业主信息存储过程
create or replace procedure pro_owners_add
(
    v_name varchar2
    v_addressid number
    v_housenumber varchar2 ,
    v_waterm eter 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_watermeter, sysdate ,v_type);
    commit;
end

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

触发器

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

数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步
分类：
- 前置触发器（BEFORE）
- 后置触发器（AFTER）
创建触发器

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

FOR EACH ROW 作用是标注此触发器是行级触发器 语句级触发器

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

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

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

创建业主名称修改日志表用于记录业主更改前后的名称

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

事务

事务是指作为单个逻辑工作单元执行的一组相关操作，这些操作要么全部完成，或者全部不完成。

好处：保证数据的安全有效。

四个特点

原子性：事务中所有数据的修改，要么全部执行，要么全部不执行
一致性：事务完成时，所有的数据都保持一致状态。所有数据的修改，必须在所有相关的表中得到反映
隔离性：事务应该在另一个事务对数据进行修改前或者修改后进行访问
持久性：保证事务时对数据库的修改时持久有效的，即使发生系统故障，也不应该丢失。

问题

脏读：A对T1进行更新，B读取到这条数据，A回滚，B读取的数据无效
不可重复读：B读取一行数据，A修改了，B刷新查询，发现两次数据不一致
幻读：B读取数据，A插入数据，B刷新，发现多了A的数据，新记录就是幻读

隔离级别

NO_TRANSACTION 不支持事务
READ_UNCONMMITED 允许脏读、不可重复读、幻读
READ_CONMMITED 允许不可重复读、幻读，不允许脏读
REPEATABLE 允许幻读、不允许脏读、不可重复读
SERIALIZABLE 不允许脏读、不可重复读、幻读

Oracle支持SQL92标准的READ_COMMITED、SERIALIZABLE，自身特有的READ_ONLY

锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

DML锁：数据锁，用于保护数据的完整性
TM锁：表级锁。
SS、SX、S、X等多种功能模式
TX锁：事务锁或行级锁。
X锁，排他锁。
DDL锁：字典锁，保护数据库对象的结构，表、索引等结构定义
内部锁和闩：保护数据库的内部结构

行级排他锁：RX锁，A使用DML对T表1行进行操作，B可以对T表除1以外的数据进行DML，或者对T表进行RX锁定，不允许对T表1行添加X锁
行级共享锁：RS锁，A使用select...from for update，不允许其他事务对相同的表添加X锁，允许DML锁定T表的其他数据行
共享锁：S锁，lock table in share mode 添加S锁，不允许任何用户更新表，但是允许select...from for update添加RS锁
排他锁：X锁，lock table in exclusive mode 添加X锁，其他用户不能对表进行DML和DDL操作，只能查询
共享行级排他锁：SRX锁，比RS锁和S锁的级别要高，不能对相同的表进行DML操作，也不能添加S锁

Oracle

Oracle

基础

创建表空间

创建用户

用户赋权

创建表

修改表

删除表

数据增删改查

TRUNCATE、DELETE实现数据删除的区别

数据的导出与导入

DDL、DML

单表查询

范围查询

空值查询

去除重复记录

排序

伪列ROWID

ROWNUM，分页用到

聚合函数

连接查询

分页查询

函数

视图

键保留表

物化视图

序列

同义词

索引

PL/SQL

变量

属性类型

异常

条件判断

循环

游标

存储函数

存储过程

触发器

事务

四个特点

问题

隔离级别

锁

猜你喜欢

热点阅读