oracle初级锦集

这篇具有很好参考价值的文章主要介绍了oracle初级锦集。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://blog.csdn.net/m0_69908381/article/details/131040299
出自【进步*于辰的博客】

1、其他知识点链接

细节、经验；
索引；

2、伪列

2.1 rownum

参考笔记一，P18.3/4、P19.5。

2.1.1 概述

rownum是虚拟字段，不真实存储，在返回结果集时生成，“宏观”作用类似序号。
在每次查询时，从1开始给结果集编号。常与<、<=连用。而与>、>=连用时，由于rownum是在查询记录时逐个生成，并迭代。因此，若rownum的判断在第1行记录时就不满足，则无法返回记录，即未迭代，rownum始终为1，最终结果集中无任何记录，与between...and...连用时同理。

$\color{red}{注意：}$
无论任何SQL语句，执行顺序都是：from → where → group by → having → select → order by。

由于rownum是伴随select生成，故与order by连用时，会导致rownum混乱。故通常是多层嵌套，先进行排序，再使用rownum进行筛选。（如下）

$\color{green}{扩展：}$
在Oracle中，常言的top-n查询其实是在rownum编号后，使用rownum进行判断，从而获取前n条记录的查询方法。（如下）

2.1.2 示例

数据表：emp(no, ..., sal)。
需求：查询工资6 ~ 10名的员工的所有信息。

写法一：

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1) e2
where rn between 6 and 10

前2层仅对结果集进行一次排序，不做筛选，在第3层时，才进行筛选。rn是rownum的别名，由于rn属于第2层的结果集，非rownum，故已固定，因此可以直接使用rn between 6 and 10筛选出第6 ~ 10名。

写法二。

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1
	  where rownum <= 10) e2
where rn > 5

在第2层时就进行结果集筛选，rownum初始为1，满足rownum <= 10，则返回记录，同时rownum迭代，如此反复直到条件不满足，这样就查询出前10条记录（工资最高的前10名员工）。此时rownum固定，别名是rn，第3层可以直接使用rn筛选出第6 ~ 10名。

$\color{red}{补充说明：}$
两种写法在第1层时，都得到根据工资降序排序后的所有员工信息。
写法二较写法一，效率高很多。
因为写法一是在第3层才进行结果集筛选，由于条件是rn between 6 and 10，使用的是rn，rn属于结果集，已固定。因此在筛选时，会遍历根据工资降序排序后的所有员工信息。
而写法二，在第2层时，是通过rownum <= 10进行筛选，由于rownum的生成机制，第2层仅遍历前10条记录（工资最高的前10名员工）。因此，第3层仅遍历10条员工信息。

2.2 nextval、currval

参考笔记一，P23.18。

2.2.1 序列

这两个伪列基于 $\color{green}{序列}$ ，我暂未对序列的相关理论进行整理，大家可以查阅这篇博文《Oracle数据库序列》（转发）。

“序列”是一种按照一定规则自动增加或减少数字的数据库对象，主要用于主键（新增时填充主键）。创建示例：

create sequence swq_emp_empId
increment by 1
start with 1000
nocycle
cache 20

seq_emp是序列名；increment是递增值，默认值为1；start是初始值；nocycle表示不循环；cache 20表示进行缓存，缓存大小为20。

大家也可以使用图形化界面操作：
oracle初级锦集

2.2.2 概述

nextval是序列的下一个值，currval是序列的当前值。

使用位置：

select子句中，不包括子查询的select子句；
insert 语句的select子句或values子句中；
update 语句的set子句中。

不能使用位置：

包含distinct、group by、having或order by的视图SQL语句的select子句中；
select、update、delete的子查询中；
包含default的create table、alter table语句中。

操作，

--修改序列--
alter sequence 序列名 ...;// 后面格式与创建语句相同
// 注：后面语句中没有start with，并且修改的值不能少于当前值

2.2.3 示例

insert into emps values(swq_emp_empId.nextval, '张三', 7500.00, 10);

update emps
set sal = 10000.00
where emp_id = swq_emp_empId.currval;

必须先获取nextval，才能使用currval。

4、约束

参考笔记一，P22.18~22。

“约束”是一种对数据表字段存储数据的限制，分为主键（primary key）、唯一键（unique）、外键（foreign key）、check约束、not null约束。

4.1 操作命令

--添加约束--
alter table 表名 add constraint 约束名 约束类型(字段);

--删除约束--
alter table 表名 drop constraint 约束名;

--启用/禁用约束--
alter table 表名 enable/disable constraint 约束名;

注：

添加外键：alter table 表名 add constraint 约束名约束类型(字段1) reference 主表名(字段2)；
添加not null约束：alter table 表名 modify(字段 constraint 约束名 not null)。因为not null约束仅能定义于字段，即列约束，故不能使用...add constraint...命令添加；

4.2 数据字典

--当前用户旗下所有数据表的所有约束--
user_constraints
--当前用户旗下所有数据表的所有约束所属的字段名--
user_cons_columns

4.3 外键约束

外键会将两个数据表进行关联，进而对子表相应字段的数据进行限制。这里就有个问题：若主表记录被删除，那么子表中相应数据该何去何从？

从上述可知创建外键的命令格式，若在其后增加一条子句，可以决定这些数据的去向，有以下4种子句：

on delete cascade：表示子表中相应数据将连同删除；
on delete set null：表示子表中相应数据将被置空（null）；
Restrict：表示若子表中存在相应数据，则提示主表记录不能删除；
on delete no action：表示子表不受影响。

4.4 check 约束

此约束用于限制数据范围（多用于数值字段），其内不允许使用伪列，如：rownum。

可以调用sysdate、uid()、user()、userenv()。

5、数据类型

5.1 char族

Oracle中char族数据类型与MySQL中相同，大家可查阅博文《MySQL知识点锦集》的第3项，在此不作赘述。

5.2 number(a, b)

参考笔记一，P20.13。

此数据类型为数字，可存储小数。其中，a 是数字位数（包括小数），b 是精确小数位数。

规则：

若b > 0，表示精确b位小数，并四舍五入；
若b < 0，表示精确到小数点左b位，并四舍五入，故只能存储整数。如：number(5, 3)，可存储五位数整数，个位和十位都为0；
若b = 0，则只能存储整数；
若a < b，则只能存储-1 ~ 0或0 ~ 1的小数，且小数点右b - a位及其后都必须是0（前后四舍五入都要满足），即精确b位小数。

6、内置函数

6.1 单行函数

参考笔记一，P15.3、P39.1。

摘要	参数说明	说明
`substr(s, a, b)`	a-开始索引，可为负值；b-截取长度	截取。截取方向始终向右
`round(a, b)`	b-精确位数	四舍五入。若`b < 0`，则向左精确，故`round(a)`等价于`round(a, 0)`
`upper(s)`		转大写
`lower(s)`		转小写
`initcap(s)`		首字母大写
`length(s)`		返回长度
`concat(a, b)`		拼接，类似`\|\|`
`trunc(s)`		截取，类似`round(a)`
`sysdate`		获取系统时间
`instr(s1, s2[, a][, b])`	s2-查找字符，a-开始索引，b-第几个	查找。`instr(s1, s2)`等价于`instr(s1, s2, 1, 1)`
`lpad/rpad(s1, n, s2)`		左 / 右填充。表示将`s1`用`s2`向左 / 向右填充成长度为`n`的字符串
`replace(s1, s2, s3)`		替换。表示将`s1`中的`s2`用`s3`替换
`trim(s)`		去除前后空格
`trim(leading/trailing/both/无 a from b)`		去除 b 中开头 / 结尾 / 开头和结尾 / 开头以及结尾的 a
`mod(a, b)`		等同于`a%b`，余数符号跟 a

6.1.1 lpad/rpad(s1, n, s2)

示例。

select lpad(rpad('csdn', 7, '#'), 10, '*') result from dual

结果：
oracle初级锦集
若n < s1.length，则无论lpad/rpad()，结果都只显示s1的前n个字符（从左往右）。

6.1.2 instr(s1, s2[, a][, b])

示例。

select instr(s1, s2, -2, 3) from dual;

表示在s1中，从倒数第2个开始，向左查找第3个s2的位置。注意：无论a的正负，返回的都是绝对位置。

6.2 非空判断函数

参考笔记一，P18.1。

摘要	参数说明	返回值类型/返回值	说明
`nvl(a, b)`			若`a`为`null`，返回 b，否则返回 a
`nvl2(a, b, c)`			若`a`不为`null`，返回 b，否则返回 c
`nullif(a, b)`			比较 a、b，若`a = b`，返回`null`，否则返回 a
`case xx when w1 then c1 when w2 then c2 else c3 end`			若`xx = w1`或`w1`为`true`，返回 c1；若`xx = w2`或`w2`为`true`，返回 c2；否则返回 c3
`decode(xx, w1, c1, w2, c2, c3)`			作用同`case()`

6.3 日期函数

参考笔记一，P15.5。

摘要	参数说明	返回值类型/返回值	说明
`months_between(d1, d2)`			返回`d1`与`d2`相差的自然月数
`add_months(d, n)`			增加月数
`next_day(d, '星期一')`			返回 d 后的第1个星期一
`last_day(d)`			返回 d 当月的最后1天
`round(d, 'dd')`			以`day`四舍五入。`'dd'`是格式码，其他格式码：`'CC'`→ 世纪，`'YY'`→ 年，`'mm'`→ 月，`'hh24'`→ 小时，`'mi'`→ 分钟，`'ss'`→ 秒。其中，`round(d, 'dd')`等价于`round(d)`
`trunc(d, 'dd')`			同`round()`。`trunc(d, 'dd')`等价于`trunc(d)`
`extract(day from d)`			获取 d 的天数。`day`是标识符，表示“天”。其他标识符：`'year'`→ 年，`'month'`→ 月，

7、视图（view）

推荐一篇博文《Oracle视图详解》（转发）。
参考笔记一，P22.24、P23.15~17。

7.1 概述

view可认为是一种绑定了SQL语句的原表“副本”，故当查询视图时，都会重新执行一次SQL语句查询原表，且修改视图记录等同于修改原表记录。

不过，若视图记录是由原表记录经处理后生成，即SQL语句中包含“数据处理”子句或条件时，不允许变动视图记录。“数据处理”子句或条件有：

group函数；
group by；
distinct；
使用表达式定义的字段；
伪列rownum；
原表在视图中未选择（未select）的字段的所有数据为非空且无默认值。

当然，并非只要SQL语句中包含了这6个子句或条件，就无法对视图进行全部DML。规则如下：

删除视图记录。要求SQL语句中不能包含前3个子句或条件。
修改视图记录。要求SQL语句中不能包含前5个子句或条件。
新增视图记录。要求SQL语句中不能包含所有子句或条件。

我们也可以干脆在SQL语句末添加with read only子句禁用视图DML。

7.2 数据字典

--当前用户旗下所有视图--
user_views
--显示视图中哪些字段允许DML--
user_updatable_columns

7.3 一个问题

假若SQL语句是：

create view v_emp_1(id, name, sal)
as
select emp_id, emp_name, sal
from emps
where dept_no = 10;

这里dept_no是外键，该视图记录为员工表中隶属部门10的所有员工信息。

现在，我把10改成20。前言道，查询视图是对原表的再次查询，这样修改后，该视图记录就变为部门20的所有员工信息。可是，view是原表的“副本”，既然创建视图时部门编号为10，说明当时我设定该视图的作用是用于快速查询部门10的所有员工信息，那么又怎能允许后续随意修改查询条件（dept_no = 10）。

因此，可以在SQL语句末添加with check option constraint 约束名子句，设置一个约束限制这种修改。