2021-08-06 数据库课堂笔记Day04

1 PL/SQL

PL/SQL 编程（Procedure language/SQL）

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

1.1 PL/SQL 语法

declare
	程序的声明部分：变量	常量	游标
begin
	语句;
	select 变量1 into 变量2 from 表名 （where） -- 从表中查询变量1的值赋值给变量2
	dbms_output.put_line() -- 输出语句
exception
	异常处理语句;
end;

1.1.1 常量和变量的定义

变量的类型与 Oracle 使用的类型一致

char varchar2 date number boolean long blob clob

定义语法：

• 变量

  • 变量名 数据类型（长度）；

• 常量

  • 常量名 数据类型 := 数值；

• 引用变量

  • 变量名1 表名.变量名2%type；变量1类型与表中变量2的类型一致

• 记录类型变量

  • 变量名 表名%rowtype；

• 键盘输入

  • 变量名 数据类型 :=&变量名；

1.1.2 if分支

语法1：

if 条件 then 
	语句；
end if；

语法2：

if 条件 then 
	语句；
else
	语句；
end if；

语法3：

if 条件 then
	语句；
elsif 条件 then
	语句；
else
	语句；
end if

1.1.3 loop 循环语句

语法1：

while 条件 loop 
	语句
end loop；

语法2：

loop 
	exit[when 条件]
	语句；
end loop；

语法3：

for 起始值 in 范围（起始值……结束值）loop
	语句；
end loop；

2 游标 Cursor

游标可以存储返回的多条数据

语法：

cursor 游标名[参数名 数据类型，参数名 数据类型……] is select 语句

使用步骤：

• 打开游标：open 游标名 （打开游标 执行查询）
• 取一行游标的值：fetch 游标名 into 行变量
  • 游标迭代取值的结束：exit when 游标名%notfound；
• 关闭游标：close 游标名 （释放资源）

3 存储过程（掌握）

存储过程（stored proceduer）是在大型数据库中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，可通过指定的存储过程的名字并给出参数执行它。存储过程时数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程

3.1 创建存储过程

create [or replace] procedure 过程名[(参数名 in/out/inout 数据类型)] IS
begin
	PLSQL程序体；
end 过程名；
或
create [or replace] procedure 过程名[(参数名 in/out/inout 数据类型)] AS
begin
	PLSQL程序体；
end；

4 存储函数

create or replace function 函数名（参数名 in type ,param in type）return 类型 is
结果变量 数据类型;
begin
return 结果变量;
end 函数名;

存储函数和存储过程的区别

函数可以有一个返回值，过程没有返回值

都可以通过 out 指定一个或多个输出参数，利用 out 参数，在过程和函数中实现多个返回值

参数的传递

名称传参：用参数名称指向放入参数 （形参名=>传入参数）

位置传参：在对应的参数位置放入参数 （参数1，参数2，……）

传参方法可以混合，但名称传参要在最后

5 触发器

触发器是一个与表相关联的，存储的PLSQL程序。当特定的数据库操作语句（insert,update,delete）在指定表上发出时，Oracle 会自动执行触发器的定义语句

create or replace trigger 触发器名
    before | after
    delete|insert|update [of 列名] on 表名
    [for each row [when  (条件)]]
begin
	plsql程序
end  触发器名；

触发器用途：

• 数据确认
• 实施复杂的安全性检查
• 做审计，跟踪表所做的操作语句
• 数据的备份和同步

分类

行级触发器：

触发语句作用的每一行记录都被触发，在行级触发器中使用old和new 伪记录变量 识别值的状态

语句级触发器：

在指定的操作语句操作之前或之后执行依次，不管这条语句影响了多少行

伪记录变量 :old 和 :new

insert 插入前字段在 :old中且全为null，插入后的字段在 :new 中

update 更新前的值在 :old 中，更新后的值在 :new 中

delete 删除前的值在 :old 中，删除后的值在 :new 中且全部为 null

6 数据库管理

Oracle 的数据库备份方式：

导入导出（逻辑备份）

热备份、冷备份（物理备份）

6.1 冷备份

在数据库正常关闭的情况下执行的备份，一般是将关键性文件拷贝到另外的存储设备

冷备份是最快最安全的备份

冷备份主要拷贝的文件：

• 数据文件
• 控制文件
• 联机文件 redo log 文件
• init.ora文件

上述文件可以sys身份查看

6.2 热备份

在数据库运行的情况下，采用归档的方式备份数据库的方法

6.3 导入导出

利用 exp/imp 两个操作，用exp命令将数据库中的数据提取到一个文件，之后再用imp命令还原数据

导入导出三种方式：

• 表方式（T）: 将指定的表的数据导出
• 用户方式（U）: 将指定的用户所有的对象及数据导出
• 全库方式（full）: 数据库中的所有对象导出

导出步骤

在备份的盘符建立导出目录

使用cmd命令进入目录

执行导出操作 exp 用户名/密码@orcl file=back.dmp

导出包括：

• 完全增量导出 备份所有的数据
• 增量型增量导出 备份上一次备份后改变的数据
• 积累型增量导出 导出自上次完全导出只有的数据库中变化的数据信息

在导出的时候 可以通过过inctype= complete/incremental/cumulative

导入

imp 用户名/密码  inctype =restore   full=y  file=备份文件路径

7 数据库设计

数据库设计就是指对于一个给定的应用场景，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效的存储数据，满足各种用户的应用需求。

7.1 数据库设计的范式

7.1.1 第一范式（单表）

确保数据表之中的每一个字段都不可再分

7.1.2 第二范式（多对多）

确保表中的每列都和主键相关

数据表中不存在非关键字段对任意候选关键字段的部分函数依赖

7.1.3 第三范式（一对多）

确保每列都和主键列之间相关，而不是间接相关

数据表之中不存在非关键字段对任意关键字段的传递函数依赖

8 数据建模

ER（entry relationship）图建模

ER图分为：实体 属性 关系三个核心部分

实体用矩形体现，属性用椭圆形体现，关系用菱形体现

ER图中的实体的关系

一对一 一对多 多对多 多对一

建模完成后可用软件功能创建数据库