orcle 数据库 day0903

ok奥家人们，今天继续学习orcle数据库 

一. 数据完整性

向某张表插入数据、更新、删除数据之前会对数据做校验，目

的就是为了确保数据的正确性、一致性、最大限度 减少重复

的数据、避免脏数据，这就是数据完整性。以下的数据就是脏

数据：

下列数据就是脏数据

• 学生成绩为负数
• 手机号码长度大于11
• 学生学号相同

如何减少数据冗余、避免脏数据？需要我们在创建表的时候为

字段增加约束 ( 规则 ) ，向表插入数据、更新、删除 数据之前对

数据做校验，如果数据满足校验规则提示用户操作成功，否则

提示就用户操作失败。在 Oracle 系统中，约 束的类型包括非

空约束、唯一约束、默认约束、主键约束、外键约束、检查约

束。

1.1 非空约束

插入数据之前会进行验证，确保该列的数据不能为空。非空约

束关键字为 not null

示例：创建 class( 班级 ) 表，为 class_name( 班级名称 ) 字段添加

非空约束

• 非空约束可以在字段名称后面添加。例如 class_name
  varchar(30) not null

SQL> create table class(
2 id number(5),
3 class_name varchar2(20) not null,
4 class_desc varchar2(300)
5 );
Table created
SQL> desc class;
Name Type Nullable Default
Comments
---------- ------------- -------- ------- ------
--
ID NUMBER(5) Y
CLASS_NAME VARCHAR2(20)
CLASS_DESC VARCHAR2(300) Y

• 也可以使用alter table...modify为指定的字段添加非空约束。语法如下：

alter table 表名称 modify 字段名称 数据类型 not null;

示例：为班级表的 “ 班级描述 ” 添加非空约束

SQL> alter table class modify class_desc
varchar2(300) not null;
Table altered
SQL> desc class;
Name Type Nullable Default
Comments
---------- ------------- -------- ------- ------
--
ID NUMBER(5) Y
CLASS_NAME VARCHAR2(20)
CLASS_DESC VARCHAR2(300)

验证非空约束

SQL> insert into class(id,class_name)
values('1','一班');
insert into class(id,class_name) values('1','一
班')
ORA-01400: 无法将 NULL 插入
("ZHANGSAN"."CLASS"."CLASS_DESC")
帮助：https://docs.oracle.com/error-help/db/ora-
01400/
SQL> insert into class(id,class_name,class_desc)
values('1','一班','解放小学');
1 row inserted
SQL> commit;
Commit complete

1.2 主键约束

概念：主键也叫作主要的关键字，用来确保每一行数据在表中

的唯一性。

主键关键字： primary key

主键约束特征：非空并且唯一

创建主键语法：

alter table 表名称 add constraint 约束名称 primary
key (字段名);
或者
alter table 表名称 add primary key (字段名);
第一种创建主键语法，需要手动为主键设置名称
第二种创建主键语法，系统自动为主键分配名称，所以不用写
constraint 约束名称

示例：为班级表的 ID 字段建立主键约束

SQL> alter table class add constraint
pk_class_id primary key(id);
Table altered

验证主键约束

SQL> insert into class(class_name,class_desc)
values('二班','希望小学');
insert into class(class_name,class_desc)
values('二班','希望小学')
ORA-01400: 无法将 NULL 插入
("ZHANGSAN"."CLASS"."ID")

帮助：https://docs.oracle.com/error-help/db/ora-
01400/
SQL> insert into class(id,class_name,class_desc)
values('1','二班','希望小学');
insert into class(id,class_name,class_desc)
values('1','二班','希望小学')
ORA-00001: 违反唯一约束条件 (ZHANGSAN.PK_CLASS_ID)
帮助：https://docs.oracle.com/error-help/db/ora-
00001/
SQL> insert into class(id,class_name,class_desc)
values('2','二班','希望小学');
1 row inserted
SQL> commit;
Commit complete

1.3 外键约束

概念：外键也叫作外部关键字，在多个表之间建立关联，确保

数据一致性、正确性。通常在多的一侧建立外键约 束去关联

一的一侧主键。例如：多个学生对应着一个班级。我们可以为

学生表的所在班级字段建立外键，去关联班 级表的主键。外

键特征如下：

• 外键约束在多张表之间建立关联(联系)，确保数据的正确性、一致性。
• 有外键的表叫做从表，没有外键的表叫做主表。

外键关键字 : foreign key references

语法如下：

alter table 表名称 add constraint 约束名称 foreign
key(字段名称) references 表名称(字段名称);
或者
alter table 表名称 add foreign key(字段名称)
references 表名称(字段名称);
第一种创建外键语法，需要手动为外键设置名称
第二种创建外键语法，系统自动为外键分配名称，所以不用写
constraint 约束名称

示例：创建学生表 ( 学生学号、学生姓名、学生生日、学生成

绩、学生联系方式、所在班级 ) ，为学生表的所在班级字 段添

加外键去关联班级表的主键

SQL> -- 创建学生表
SQL> create table student(
2 id number(10),
3 stu_name varchar2(30) not null, -- 学生姓名
4 stu_birthday date, -- 学生生日
5 stu_score number(5,2), -- 学生成绩
6 stu_mobile char(15), -- 学生联系方式
7 class_id number(5) -- 学生所在班级
8 );
Table created
SQL> desc student;
Name Type Nullable Default
Comments
------------ ------------ -------- ------- -----
---
ID NUMBER(10) Y
STU_NAME VARCHAR2(30)
STU_BIRTHDAY DATE Y
STU_SCORE NUMBER(5,2) Y
STU_MOBILE CHAR(15) Y
CLASS_ID NUMBER(5) Y
SQL> -- 为学生表的id字段建立主键
SQL> alter table student add constraint
pk_student_id primary key(id);
Table altered
SQL> -- 为学生表的class_id建立外键去关联班级表的主键
(id)
SQL> alter table student add constraint
fk_student_classid foreign key(class_id)
references class(id);
Table altered

验证外键约束

SQL> insert into student(id,stu_name,class_id)
values(1,'zs',5);
insert into student(id,stu_name,class_id)
values(1,'zs',5)
ORA-02291: 违反完整约束条件
(ZHANGSAN.FK_STUDENT_CLASSID) - 未找到父项关键字
帮助：https://docs.oracle.com/error-help/db/ora-
02291/
SQL> insert into student(id,stu_name,class_id)
values(1,'zs',1);
1 row inserted
SQL> commit;
Commit complete

1.4 唯一约束

唯一约束强调所在的列允许空值但是值不允许重复。例如班级

表的班级名称不能重复。

唯一约束关键字： unique

唯一约束语法如下：

alter table 表名称 add constraint 约束名称 unique
(字段名);
或者
alter table 表名称 add unique (字段名);

示例：为学生表的手机号码定义唯一约束

SQL> -- 为学生表的手机号码定义唯一约束
SQL> alter table student add constraint
unique_student_mobile unique(stu_mobile);
Table altered

验证唯一约束：

SQL> -- 验证唯一约束
SQL> insert into
student(id,stu_name,stu_mobile,class_id)
values(2,'ls','15518880231',1);
1 row inserted
SQL> insert into
student(id,stu_name,stu_mobile,class_id)
values(3,'ww','15518880231',1);
insert into
student(id,stu_name,stu_mobile,class_id)
values(3,'ww','15518880231',1)
ORA-00001: 违反唯一约束条件
(ZHANGSAN.UNIQUE_STUDENT_MOBILE)
帮助：https://docs.oracle.com/error-help/db/ora-
00001/

唯一约束和主键约束有啥区别？唯一约束可以插入空值，主键

约束不能插入空值

1.5 检查约束

检查约束在插入和更新数据之前对数据的有效性规则进行检

查，这个有效性规则需要我们自己定义。

检查约束关键字： check

创建检查约束语法：

alter table 表名称 add constraint 约束名称 check
(约束条件) ;
或者
alter table 表名称 add check (约束条件) ;

示例：为学生表的 “ 成绩 ” 字段建立检查元素，要求学生成绩必

须在 0~150 之间

SQL> -- 为学生表的“成绩”字段建立检查元素，要求学生成绩
必须在0~150之间
SQL> alter table student add constraint
check_stu_score check(stu_score>=0 and
stu_score<=150);
Table altered

验证检查约束

SQL> -- 验证检查约束
SQL> insert into
student(id,stu_name,stu_mobile,stu_score)
values(4,'jack','15518880233',-1);
insert into
student(id,stu_name,stu_mobile,stu_score)
values(4,'jack','15518880233',-1)
ORA-02290: 违反检查约束条件
(ZHANGSAN.CHECK_STU_SCORE)
帮助：https://docs.oracle.com/error-help/db/ora-
02290/
SQL> insert into
student(id,stu_name,stu_mobile,stu_score)
values(4,'jack','15518880233',1);
1 row inserted
SQL> commit;
Commit complete

二.Oracle常用系统函数

Oracle 提供了大量系统内置函数，可以大大增强 SQL 语言的运

算和判断功能。本次课对 Oracle 中常用的系统函数 进行介

绍。

2.1 字符串函数

字符串函数是专门用于字符处理的函数，处理的对象可以是字

符串，还可以是 varchar2 类型的字段，常用的字 符类函数有

如下几种：

dual是Oracle系统内部提供的一个用于临时数据计算的内存表
select * from emp;
-- 使用ascii(c)函数返回字符串c对应的的ascii码
select ascii('A') from dual;
-- 使用chr(i)函数返回ascii码对应的字符，i表示一个整数
select chr(65) from dual;
-- 使用concat函数将字符串s2连接到s1后面
select concat('hello','world') from dual;
-- 使用concat函数将员工表的“职位”字段连接到“员工姓名”字段后面
select concat(ename,job) from emp;
-- 使用initcap()函数,将字符串‘how are you’ 每个单词首字母大写
select initcap('how are you') from dual;
-- 使用length()函数返回字符串HelloWorld的长度
select length('HelloWorld') from dual;
select length('王') from dual;
-- 使用length()函数返回员工表的“员工姓名”的长度
select ename,length(ename) from emp;
-- 使用length()函数返回员“员工姓名”长度大于5的所有员工信息
select * from emp where length(ename)>5;
-- 返回字符串'ABC'的小写形式
-- 返回字符串'abc'的大写形式
select lower('ABC'),upper('abc') from dual;
select lower(ename) from emp;
-- 使用replace()函数将字符串‘abcdefg'替换成’abcdXfg'
select replace('abcdefg','e','X') from dual;
select ename,replace(ename,'A','h') from emp;数字函数 说明
ceil(n) 参数n向上取整
floor(n) 参数n向下取整
round（n1,n2) 四舍五入，并且保留n2位小数
power(n1,n2) 返回n1的n2次方

select substr('abcdefghijklmn',5,3) from dual;
-- 使用instr()在字符串'Oracle 19c'中，从第三个字符开始查找字符串'c'第二次出现的位置
select instr('Oracle 19c','c',3,2) from dual;

2.2 数字函数

数字函数主要用于执行各种数学计算，所有的数字类函数都有

数字参数并返回数字值。常用的数字函数如下：

数字函数	说明
ceil(n)	参数 n 向上取整
floor(n)	参数 n 向下取整
round （ n1,n2)	四舍五入，并且保留 n2 位小数
power(n1,n2)	返回 n1 的 n2 次方

select * from emp;
update emp set sal=799.64 where empno=7369;
select ceil(sal) from emp;
select floor(sal) from emp;
select round(sal,1) from emp;
select power(3,3) from dual;

2.3 日期时间函数

Oracle 提供了很多用来处理日期和时间的函数，通过这些函数

可以实现计算需要的特定日期和时间。常用的日期 时间函数

如下：

日期时间函数	说明
sysdate	返回系统当前日期
add_months(d,i)	返回日期d加上i 个月之后的结果。其中 i 为任意整数。

select * from emp;
select sysdate from dual;
select hiredate,add_months(hiredate,1) from
emp;

2.4 转换函数

在操作表中的数据时，使用转换函数将某个数据从一种类型转

换为另一种数据类型。常用的转换函数如下：

转换函数	说明
to_char(x[,format])	将参数 x 转换为指定格式的字符 串， format 表示字符串格式
to_date(s, [format[lan]])	将参数 s 转换为 date 类型， format 表示字符串格式， lan 表示所使用 的语言
to_number(s)	返回字符串 s 代表的数字

select * from emp;
-- 使用to_char函数将系统日期时间转换为“yyyy-mm-dd
hh24:mi:ss”格式
select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
-- 向emp表插入数据
insert into emp(empno,ename,hiredate)
values(1000,'zs',to_date('2024/09/03','yyyy-mmdd'));
-- 根据入职日期计算员工的工龄
-- 工龄=系统年份-入职年份
-- 系统年份如何获取？使用to_char(sysdate,'yyyy')将
系统年份
-- 取到，然后使用to_number函数肩将字符串转换为整数
-- 入职年份如何获取?使用to_char(hiredate,'yyyy')将
员工入职
-- 年份取到，然后使用to_number函数肩将字符串转换为整数
select hiredate 入职日
期,to_number(to_char(sysdate,'yyyy'))-
to_number(to_char(hiredate,'yyyy')) 工龄 from
emp;

三.PS/SQL编程

3.1 PL/SQL简介

PL/SQL(Procedural Language/SQL) 是 Oracle 数据库引入的一

种过程化编程语言。 PL/SQL 编程语言在除了能够 编写 SQL 语

句还能执行流程控制语句 ( 顺序、分支、循环 ) 。 PL/SQL 是建立

在 SQL 语句的基础上的。

PL/SQL 程序以块 (Block) 为基本单位，分为三部分：声明部分

(declare 开头 ) 、执行部分 (begin 开头 ) 、异常处理 部分

(exception 开头 ) 。其中执行部分是必须的，其它两个部分是

可选的，语法结构如下：

[declare]
-- 声明部分，可选-- 在这里可以声明PL/SQL程序块用到
的变量、常量、游标等等
begin
-- 执行部分，必须-- 主要的逻辑控制和运算都在这里完
成
[exception]
[when 异常名称 then]
-- 异常部分，可选
-- 纠正BEGIN里面的错误，或者给用户提供一些错误信息提示
end;
声明部分DECLARE用来定义变量、常量
执行部分begin用来执行PL/SQL的逻辑
当PL/SQL的逻辑执行出现了异常，进入EXCEPTION处理异常

示例：编写第一个 PL/SQL 程序，在控制台输出 HelloWorld

begin
dbms_output.put_line('helloworld...');
end;
-- dbms_output.put_line语句用于输出计算结果。

示例：定义一个 PL/SQL 块，当 0 作为除数抛出异常，给出提示

信息除数不能为 0 。

begin
-- 异常情况：0不能作为除数
dbms_output.put_line(1/0);
-- 当begin块发生异常，程序跳转到exception块
-- exception唯一职责就是处理begin块的异常
-- 此时处理异常的方式为：向控制台输出‘除数不能为0。’
exception
-- 抓住上面的异常
-- zero_divide是Oracle定义的异常名称。
when zero_divide then
dbms_output.put_line('除数不能为0');
end;

3.2 数据类型

数据类型本质上是一种描述数据存储的内存结构，它决定变量

中所存储数据的类型。

3.2.1 基本数据类型

常用的基本数据类型包括：数值类型、字符串类型、日期类

型、布尔类型 (Logic)

数值类型

number(p,s) 是常用的数值类型，可以用来存储整数和小数。

p 表示数字中的有效位，取值范围为 [1~38] ，如 果 p 没有指

定， Oracle 将使用 38 作为有效位。 s 表示数字精确到小数点右

边的位数。

例如： number(5,2) 表示总共有 5 个数字，其中小数占据了 2

位，最大值为 999.99

例如： number 等价于 number(38)

字符串类型

字符串类型主要包括 varchar2 、 char 。

varchar2 用于存储可变长度的字符串，语法结构如下：

varchar2(maxlength) 其中 maxlength 表示可存储字符串的最

大长度。定义变量时必须给出，最大值是 32767 字节。

char 用于存储固定长度的字符串 , 默认最大长度为 1 ，语法结构

如下：

char(maxlength)

char 只有 2000 字节，所以如果 PL/SQL 中的 char 类型变量的长

度大于 2000 字节，则不能赋给数据库中的 char

日期类型

日期类型只有一种，即 DATE 类型，用来存储日期和时间。

布尔类型 (Logic)

布尔类型也只有一种，即 BOOLEAN 类型。主要用于程序的流

程控制和业务逻辑判断，变量值可以是 true 、 false 、 null 。

3.2.2 特殊数据类型

为了提高编程效率和解决复杂业务逻辑需求， PL/SQL 提供了 3

种特殊的数据类型。

%TYPE 类型

它可以声明一个与指定字段相同的数据类型，通常跟在指定列

名的后面。语法如下：

变量名 表名称.列名称%type
emp_name emp.ename%type;
表示变量emp_name的数据类型跟emp表的ename字段类型完全相
同

示例：在 scott 模式 ( 用户 ) 下，查询员工编号为 7566 的员工姓

名和它的工资。

目的：使用 %type 定义变量并输出

步骤：
1. 定义PL/SQL结构体 declare...begin...end;
2. 定义%type变量 emp_name和emp_sal它们的数据类型
跟emp表的ename和sal相同
3. 根据员工编号(7566)查询员工的姓名和工资，将查询结
果集赋值给变量emp_name、emp_sal
4. 输出变量emp_name、emp_sal的值
declare
-- emp_name数据类型跟emp表的ename字段数据类型相同
emp_name emp.ename%type;
emp_sal emp.sal%type;
begin
-- 将查询的字段值通过into关键字赋给变量emp_name和
emp_sal
select ename,sal into emp_name,emp_sal
from emp
where empno=7566;
-- 输出变量emp_name、emp_sal的值
-- ||表示字符串的拼接 ，类似Java的+
dbms_output.put_line('姓名是:'||emp_name||',
工资是:'||emp_sal);
end;

ok了家人们 see you later