Oracle（四）约束与级联操作

最新推荐文章于 2025-06-29 21:05:28 发布

Nancy1709

最新推荐文章于 2025-06-29 21:05:28 发布

阅读量761

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/Nancy1709/article/details/88392519

数据库专栏收录该内容

4 篇文章

订阅专栏

本文介绍了Oracle数据库中的约束，包括非空约束、唯一约束、主键约束和检查约束，强调了外键约束在数据一致性中的作用，以及级联操作在主表和从表之间的应用。此外，还提到了索引和复合索引对查询速度的影响，以及SQL优化的策略，如避免全表扫描和合理使用索引。最后，简述了PL/SQL的基础操作和程序控制结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

一、约束

（一）约束的概念
约束：非空约束、唯一约束、主键约束，这些约束有自己的名称，是系统自动给定默认名称，我们也可以自己创建约束指定名称，需要使用关键字CONSTRAINT来创建

（二）检查约束
检查约束我们只是作为语法提出，在开发中不会去使用，因为很耗费性能，如果真有这样的需求是交给程序去实现

（三）DEMO
1.默认的约束名称

--删除数据表
DROP TABLE teacher PURGE;
--创建数据表
CREATE TABLE teacher(
  tno NUMBER(10),
  tname VARCHAR2(20),
  CONSTRAINT pk_tno PRIMARY KEY(tno)
);
--插入数据
INSERT INTO teacher VALUES(1001,'X老师',100,SYSDATE);
INSERT INTO teacher VALUES(1002,'Y老师',100,SYSDATE);
INSERT INTO teacher VALUES(1003,'Z老师',100,SYSDATE);

2.指定自定义的约束名称

DROP TABLE teacher PURGE;
CREATE TABLE teacher(
	tno NUMBER(4),
	tname VARCHAR2(10),
	--主键约束
	CONSTRAINT pk_tno PRIMARY KEY(tno),
	--唯一约束
	CONSTRAINT uk_tname UNIQUE(tname)
);
--插入数据
INSERT INTO teacher VALUES(1001,'X老师');
INSERT INTO teacher VALUES(1002,'Y老师');
INSERT INTO teacher VALUES(1003,'Z老师');

3.检查约束

DROP TABLE teacher PURGE;
CREATE TABLE teacher(
  tno VARCHAR2(10),
  tname VARCHAR2(20),
  tage NUMBER(3),
  CONSTRAINT pk_tno PRIMARY KEY(tno),
  CONSTRAINT ck_tage CHECK (tage BETWEEN 0 AND 250)
);
INSERT INTO teacher VALUES('t001','tom1',18);		--已创建1行
INSERT INTO teacher VALUES('t002','tom2',180);		--已创建1行
INSERT INTO teacher VALUES('t003','tom3',280);		--行出现错误，违反检查约束条件（ck_tage）

二、外键约束

（一）概念
外键约束：就是一张表中的某个字段的数据来源依赖于另外一张中的某个字段的数据
例如：
其实在之前emp数据表中的数据都有自己的部门编号（deptno），只有这个数据在dept数据表中存在才是合理的，比如说向emp表中增加了一条数据给出部门编号是100，但是在dept表中没有编号为100的部门，此时数据就是不合理的，就需要使用外键约束来实现问题的解决。

（二）注意
1.使用了外键约束之后，表之间就存在一定的依赖关系，依赖的数据表（引用其他表中数据的表）叫做子表或者从表，被依赖的数据表叫做父表或者主表
2.删除表的顺序：子父表关系的表，先删子表，后删除父表。除非创建外键约束删除功能
3.创建表的顺序：子父表关系的表，先建父表，再建主表
4.外键约束的表，当前子表插入的数据必须在父表中有记录

（三）DEMO

DROP TABLE sc PURGE;
DROP TABLE course PURGE;
DROP TABLE teacher PURGE;
DROP TABLE student PURGE;

CREATE TABLE student(
  sno VARCHAR2(10),
  sname VARCHAR2(20),
  sage NUMBER(2),
  ssex VARCHAR2(5),
  CONSTRAINT pk_sno PRIMARY KEY(sno)
);

CREATE TABLE teacher(
  tno VARCHAR2(10),
  tname VARCHAR2(20),
  CONSTRAINT pk_tno PRIMARY KEY(tno)
);

CREATE TABLE course(
  cno VARCHAR2(10),
  cname VARCHAR2(20),
  tno VARCHAR2(20),
  CONSTRAINT pk_cno PRIMARY KEY(cno)
);

CREATE TABLE sc(
  sno VARCHAR2(10),
  cno VARCHAR2(10),
  score NUMBER(4,2),
  --外键约束
  CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),
  CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno)
);
--在sc数据表插入数据

三、级联操作

级联操作是存在于主表（父表）和从表（子表）关系之中，当操作了主表中的数据则要求子表中的数据也随之更新或者删除，这样的操作叫做级联操作，级联操作分为级联删除、级联更新、级联查询（使用程序去实现）
（一）级联删除
1.删除主表中的数据
方案一：先删除子表中引用的数据，再删除主表数据
方案二：使用级联删除
2.级联删除的要求：在创建外键的时候要指定使用级联删除的操作（创建表的时候创建外键）

CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE CASCADE

（二）级联更新
1.更新主表中的数据
方案一：先更新子表中的数据，再更新主表中的数据。需要先取消子表对主表数据的引用
方案二：使用级联更新操作
2.级联更新的要求：在创建外键约束的时候指定（创建表的时候创建外键）

CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno) ON UPDATE CASCADE

3.很遗憾，Oracle不支持级联更新，MySQL支持。如果要在Oracle中使用级联更新需要使用触发器实现。

四、索引和复合索引

（一）索引的概念
索引是提高查询速度的一种手段，索引有很多中，有一百多种，目前只需要掌握索引的概念，创建索引，使用索引，删除索引即可

--切换成超级管理员，方可观察查询的sql语句执行计划过程
CONN SYS/CHANGE_ON_INSTALL AS SYSDBA;
--打开追踪器，查询可观察过程
SET AUTOTRACE ON;
--全表扫描 TABLE ACCESS FULL
SELECT * FROM emp WHERE sal>1000;
--索引树的方法查询（二叉树）
--首先创建索引
--CREATE INDEX 索引名 ON 数据表（字段名）
CREATE INDEX emp_sal_index ON SCOTT.emp(sal);
--查询显示是索引范围扫描INDEX RANGE SCAN
SELECT * FROM emp WHERE sal>1000;
--删除索引
DROP INDEX emp_sal_index;

（二）复合索引
复合索引：就是一个索引在多个字段上创建
索引的最左原则：当使用符合索引的时候如果只要其中一个字段，那么必须使用第一个字段（在创建索引时候的字段顺序）索引才会生效，这叫做索引的最左原则。

--创建复合索引
CREATE INDEX emp_job_sal_index ON SCOTT.emp(job,sal);
--分别用job,sal,job与sal三种方式过滤查询数据
--job过滤查询，索引范围扫描
--sal过滤查询，全表扫描（索引的最左原则）
--job AND sal过滤查询，索引范围扫描+全表扫描
--job OR sal过滤查询，全表扫描（建议将OR换成UNION ALL后，索引范围扫描+全表扫描）

（三）注意事项
1.索引不能随便使用，否则就是滥用，如果一张数据表中的数据更新频率太高，会极其耗费性能，因为更新数据之后需要重新创建索引。
2.思考题：如果老板要求一张数据表按照某个字段查询的速度很高，但是该数据表的数据经常改变，请问你作为一个架构师怎么解决这一问题？
方案一：提高硬件性能
方案二：可以设计两张数据表，一张用于数据的查询，一张用于数据的查询，一张用于数据的更新，等到夜间人少的时候做一次数据的汇总

五、sql的优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在进行条件判断的字段上创建索引
2.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
3.应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
4.应尽量避免在where子句中使用or来间接条件，如果一个字段有索引，另一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，而是使用union all实现
5.not in也要慎用，否则会导致全表扫描，很多时候用NOT EXISTS代替NOT IN是一个好的选择（如何使用NOT EXISTS注意一下啊）
6.主键默认索引，我们没有在empno字段上创建索引，但是使用的是索引扫描，原因是empno是表的主键，主键字段数据库会默认使用索引
7.模糊查询使用了“%”也将导致全表扫描（MySQL的时候会）
8.应尽量避免在where子句中对字段进行表达式计算操作，这将导致引擎放弃使用索引而进行全表扫描
9.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引（最左原则），否则该索引不会被使用，并且应尽可能的让字段顺序与索引顺序一致
10.update语句，如果只更改一两个字段，不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
11.对于多张大数据量（这里几百条就算大了）的表连接，可以考虑使用程序去实现，不要做连接查询，就是尽量避开多表查询
12.索引并不是越多越好，索引固然可以提高想要的查询（select）效率，但同时也降低了插入（insert）和修改（update）的效率，因为插入和修改时可能会重建索引，所以怎么创建所以需要慎重考虑，视具体情况而定。
13.尽量使用数字型字段，若只含数据信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每个字符，而对于数字型而言只需要比较一次就够了
14.任何地方都不要使用SELECT * FROM T ，用具体的字段列表代替星号，不要返回用不到的任何字段
15.学会使用慢查询来进行数据库的优化

六、pl/sql

（一）概念
PL/SQL也是一种程序语言，叫做过程化SQL语言，PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL只有Oracle数据库有。

（二）pl/sql的基本操作
1.BEGIN后面没有分号，END;之后要有/
2.赋值使用 := ，字符串的连接使用的是 || ，不是+
3.声明（全局变量）：DECLARE关键字
4.如果声明了变量但是没有赋值，默认就是空
5.打印功能：开启打印功能 SET SERVEROUTPUT ON;
打印子句 DBMS_OUTPUT.put_line();
6.接收接盘输入的值 v_empno:=&empno;

（三）pl/sql的基本操作DEMO
1.默认格式：第一个pl/sql程序

BEGIN
  NULL;
END;
/

2.输出HELLO WORLD

--开启打印功能
SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.put_line('HELLO WORLD');
END;
/

3.定义变量 + 为变量赋值 + 为变量设置默认值

--定义一个全局变量
DECLARE
  v_num NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('变量的值为：'||v_num);
END;
/

--为变量赋值
DECLARE
  v_num NUMBER;
BEGIN
  v_num:=30;
  DBMS_OUTPUT.put_line('变量的值为：'||v_num);
END;
/

--设置变量默认值
DECLARE
  v_num NUMBER:=100;
BEGIN
  DBMS_OUTPUT.put_line('变量默认值为：'||v_num);
END;
/

4.键盘输入数据：根据雇员编号查询出雇员名字

DECLARE
  v_empno NUMBER;
  v_ename VARCHAR2(100);
BEGIN
  DBMS_OUTPUT.put_line('请输入编号');
  v_empno:=&empno;
  SELECT ename INTO v_ename FROM emp
    WHERE empno=v_empno;
END;
/

（四）pl/sql中的程序控制
1.定义常量：CONSTANT关键字

--定义常量,声明的同时必须赋值，且不可以二次赋值
DECLARE
  v_empno CONSTANT NUMBER:=7788;
BEGIN
  DBMS_OUTPUT.put_line('常量是：'||v_empno);
END;
/

2.使用%TYPE定义变量的类型

DECLARE
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
BEGIN
  DBMS_OUTPUT.put_line('请输入编号：');
  v_empno:=&empno;
  SELECT ename INTO v_ename FROM emp 
    WHERE empno=v_empno;
  DBMS_OUTPUT.put_line('编号是：'||v_empno||',姓名是：'||v_ename);
END;
/

3.使用%ROWTYPE声明变量（牢记正确格式）

DECLARE
  v_empno emp.empno%TYPE;
  v_result emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('请输入编号！');
  v_empno:=&empno;
  SELECT * INTO v_result FROM emp  
    WHERE v_empno=empno;
  DBMS_OUTPUT.put_line('编号是：'||v_empno||'，姓名是：'||v_result.ename||'，工作是：'||v_result.job||',薪资是'||v_result.sal);
END;
/

4.IF使用（记得SELECT语句完成时记得加分号）

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM emp;
  IF v_count>10 THEN
    DBMS_OUTPUT.put_line('数值大于10');
  END IF;
END;
/

5.IF ELSE

DECLARE 
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM dept;
  IF v_count>10 THEN
    DBMS_OUTPUT.put_line('数值大于10');
  ELSE
    DBMS_OUTPUT.put_line('数值小于等于10');
  END IF;
END;
/

6.多if结构

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM dept;
  IF v_count>10 THEN
    DBMS_OUTPUT.put_line('数值大于10');
  ELSIF v_count<10 THEN
    DBMS_OUTPUT.put_line('数值小于10');
  ELSE
    DBMS_OUTPUT.put_line('数值等于10');
  END IF;
END;
/