第六章：数据库编程

type

slug

status

summary

icon

6.1 数据库连接技术（ODBC）

6.1 数据库连接技术（JBDC）

JDBC工作原理：

注意：sql在上下文中是有具体的作用的

6.2 数据库存储过程

6.2.1 存储过程的概念

存储过程（Stored Procedure）是一种数据库的对象；

由一组能完成特定功能的SQL语句集构成；

是把经常会被重复使用的SQL语句逻辑块封装起来，经编译后，存储在数据库服务器端；（所以叫存储过程）

当被再次调用时，而不需要再次编译；

当客户端连接到数据库时，用户通过指定存储过程的名字并给出参数，数据库就可以找到相应的存储过程予以调用。（所以函数调用和存储过程调用很像）

6.2.3 创建存储过程的语法

基本语法：

其中：

procedureName：存储过程名

OR REPLACE ：覆盖同名的存储过程

IN、OUT或INOUT参数模式。IN为输入参数；OUT为输出参数；缺省值是IN

Pname：形式参数的名字

dataType：该存储过程参数的数据类型

实际上就是把创建函数中的FUNCTION关键字替换为了PROCEDURE，并且存储过程是没有返回值的

6.3 数据库触发器

6.3.1 触发器的概念

触发器的概念

触发器是特殊类型的存储过程（是因为触发器也是直接存储在数据库服务器上的，不需要重新编译），主要由操作事件(就是只有数据定义语言能触发。如INSERT、UPDATE、DELETE) 触发而被自动执行。（也是一个数据库对象）
触发器可以实现比约束更复杂的数据完整性，经常用于加强数据的完整性约束和业务规则。
触发器本身是一个特殊的事务单位。

触发器的特点

与表相关联：必须定义在表或视图上。
自动触发：由执行INSERT、DELETE、UPDATE操作时触发
不能直接调用，也不能传递或接受参数（但是有返回值，返回的值是一个记录类型的变量，表示着当前操作事件操作的对象）
是事务的一部分：触发器和触发语句作为可在触发器内回滚的单个事务。

触发器的分类

按触发的语句分为： INSERT触发器、DELETE触发器、UPDATE触发器（都是数据操纵语言，查询的时候不会触发触发器）

按触发器的执行次数可分为：

语句级触发器：由关键字FOR EACH STATEMENT声明，在触发器作用的表上执行一条SQL语句时，该触发器只执行一次，即使是修改了零行数据的SQL，也会导致相应的触发器执行。如果都没有被指定，FOR EACH STATEMENT会是默认值。
行级触发器：由关键字FOR EACH ROW标记的触发器，当触发器作用的表的数据发生变化时，每变化一行就会执行一次触发器。例如，假设学生成绩表有DELETE触发器，当在该表执行DELETE语句删除记录时，如果删除了20条记录，则将导致DELETE触发器被执行20 次。

按触发的时间分为三类

BEFORE触发器：在触发事件之前执行触发器。
AFTER触发器：在触发事件之后执行触发器。
INSTEAD OF触发器：当触发事件发生后，执行触发器中指定的函数，而不是执行产生触发事件的SQL 语句。在表或视图上，对于INSERT、UPDATE 或 DELETE 三种触发事件，每种最多可以定义一个INSTEAD OF 触发器

触发器相关的特殊变量

NEW：数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。
OLD：数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。

也就是说NEW和OLD只对行级触发器有效。这里的记录类型的变量就理解为一个行类型的变量，只不过这个行类型就相当于是使用一个泛型接受的，就使得它能够接收所有关系表的某一个元组

TG_OP：数据类型是text；是值为INSERT、UPDATE、DELETE 的一个字符串（所以判等的时候需要加上引号），它说明触发器是为哪个操作引发。

这些变量实际上是在触发器的存储过程中使用的，所以这三个特殊变量在一般的存储过程中也是可以使用的，只不过在一般的存储过程中使用没有意义

6.3.2 创建触发器的基本语法

基本语法格式

可以发现在定义一个触发器的时候一定要先创建一个触发器函数以便将触发器和对应的函数绑定

其中：

触发器名：指明所定义的触发器名

BEFORE | AFTER | INSTEAD OF：指明触发器被触发的时间

操作事件：INSERT、UPDATE、DELETE等，可以使用OR将操作事件连接起来

ON 表名：指明触发器所依附的表

FOR EACH { ROW | STATEMENT } ：指明触发器被触发的次数

EXECUTE PROCEDURE 函数 ( 参数列表 ) ：指明触发时所执行的函数（是函数是因为定义触发器时要求有返回值，而存储过程不能有返回值）

例如：

接下来在student_score表上创建触发器score_audit_triger，这个是格式：

6.3.3 修改触发器的基本语法

因为触发器也是一个数据库对象，所以对触发器的修改使用的SQL语言也是数据定义语言

语句格式

其中：

name：需要修改的现有触发器的名称。

table_name：该触发器作用的表的名字。

new_name：现有触发器的新名字。

这里需要提供table_name跟前面修改存储过程的名称是一样的，因为只有提供了name和table_name才能唯一确定一个触发器（对存储过程而言就是要提供参数列表）。然后新名字会自动绑定原触发器的表

如将score_audit_trigger触发器改名为score_audit_trig：

6.3.4 删除触发器的基本语法

也是使用DROP

语句格式

其中：

IF EXISTS：如果指定的触发器不存在，那么发出提示而不是抛出错误（跟存储过程是一样的）

name：要删除的触发器名；

table_name：触发器定义所依附的表的名称。

如将上述触发器score_audit_trig删除（没有级联删除的功能，如果要有级联删除的功能的话就要使用CASCADE关键字了）：

6.1.2 数据库PL/pgSQL语言

PL/pgSQL简介：PL/pgSQL（Procedural Language/PostgreSQL）是postgreSQL数据库的面向过程的编程语言。

PL/pgSQL的主要语法特点

PL/pgSQL代码由块组成，每个块包含声明、可执行和异常处理部分。
PL/pgSQL支持多种数据类型，包括PostgreSQL的内置数据类型（如整数、浮点数、字符串等）和用户自定义数据类型。
PL/pgSQL提供了丰富的控制结构，如条件语句（IF、CASE）、循环语句（FOR、WHILE、LOOP）和异常处理（BEGIN EXCEPTION … END）。
PL/pgSQL允许开发者在代码中嵌入SQL语句，以便执行查询、插入、更新和删除等操作。
PL/pgSQL支持创建用户自定义函数和存储过程。
PL/pgSQL允许开发者创建触发器函数，用于在特定事件发生时自动执行。触发器函数可以用于实现数据完整性约束、审计日志等功能。

6.1.3 PL/pgSQL变量与类型声明

PL/pgSQL变量声明

如：

所以不要看上面的变量声明语法那么复杂，其实就是变量名+类型+：=赋值

需要的时候就在变量类型的前后加上CONSTANT和NOT NULL 加上常量与非空约束

PL/pgSQL特殊类型声明

也就是定义行类型变量（就相当于是在定义数据库表中的一条元组的类型，很像一个结构体）
或者是声明一个记录类型变量，又或者是通过一个与定义变量的类型来定义一个新变量的类型
声明行类型语法格式

需要注意的是这里表名的后面是一个百分号加上一个ROWTYPE关键字；

如：这里就是将stuinfor变量的类型设置为student表的行类型（也就是一个stuinfor就代表了student表中的一条元组）

声明记录类型语法格式

例如：

使用已定义变量来定义新变量语法格式

需要注意的是，这里使用的是%加上一个TYPE关键字。从这里就可以看出%在PL/pgSQL中应该是一个运算符，相当于是取出一个对象的某个属性，具体取出哪个属性是靠%后面提供的关键字实现的。所以在这里我就用这种方法来记这个语法格式了，就是对一个关系表对象而言，他有一个对象的属性就是行类型；而对一个变量而言，就有一个对象属性是他自己的类型

如：