为什么单靠 SQL 不够#

• SQL 不是通用编程语言
• 它没有通用语言那样完整的表达能力
• 像 打印报表、和用户交互、把结果发给 GUI、走网络请求 这些操作，SQL 本身做不了

所以数据库程序员必须把 SQL 和 宿主语言（Java、C、C++、Python 等）结合起来。

• SQL 擅长描述要什么数据
• 通用语言擅长控制流程、组织交互、处理外部资源

这就是 数据库应用程序 存在的意义。

两条路线：API vs Embedded SQL#

从通用编程语言访问数据库，主要有两种做法：

1. API 方式

程序通过一组库函数去:

• 建立连接
• 发送 SQL
• 逐行取回结果

典型代表：

• JDBC：Java 用
• ODBC：C / C++ / C# 常用

2. Embedded SQL 方式

把 SQL 直接嵌进宿主语言源码里。

• 编译时先由预处理器把嵌入式 SQL 翻译成函数调用

• 运行时再通过底层 API 连库并执行

• API：你自己显式写 调用数据库接口 的代码

• Embedded SQL：你把 SQL 写在程序里，编译器/预处理器帮你翻译

JDBC#

1
Connection conn = DriverManager.getConnection(url, user, passwd);
2
Statement stmt = conn.createStatement();
3
// do actual work
4
stmt.close();
5
conn.close();

1
stmt.executeUpdate(
2
    "insert into instructor values('77987', 'Kim', 'Physics', 98000)"
3
);

1
ResultSet rset = stmt.executeQuery(
2
    "select dept_name, avg(salary) from instructor group by dept_name"
3
);
4
while (rset.next()) {
5
    System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
6
}

1
PreparedStatement pStmt = conn.prepareStatement(
2
    "insert into instructor values(?,?,?,?)"
3
);
4
pStmt.setString(1, "88877");
5
pStmt.setString(2, "Perry");
6
pStmt.setString(3, "Finance");
7
pStmt.setInt(4, 125000);
8
pStmt.executeUpdate();

1
ResultSetMetaData rsmd = rs.getMetaData();
2
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
3
    System.out.println(rsmd.getColumnName(i));
4
    System.out.println(rsmd.getColumnTypeName(i));
5
}

1
DatabaseMetaData dbmd = conn.getMetaData();
2
ResultSet rs = dbmd.getColumns(null, "univdb", "department", "%");

1
conn.setAutoCommit(false);
2
// 多条更新
3
conn.commit();
4
// 或者 conn.rollback();

1
CallableStatement cStmt1 = conn.prepareCall("{? = call some_function(?)}");
2
CallableStatement cStmt2 = conn.prepareCall("{call some_procedure(?,?)}");

1
#sql iterator deptInfoIter (String dept_name, int avgSal);
2
#sql iter = {
3
    select dept_name, avg(salary) as avgSal
4
    from instructor
5
    group by dept_name
6
};

ODBC#

ODBC 的思想#

ODBC 是一种更通用的数据库接口标准。

你可以把它理解成：

• 应用程序面对统一接口
• 不同数据库厂商各自提供 driver
• 应用通过 driver 去和具体 DBMS 通信

应用程序不用为每个数据库重写一套完全不同的接口代码， 而是通过 ODBC 驱动层去适配不同 DBMS。

• Embedded SQL 往往绑定某个特定 DBMS
• 因而可移植性差
• 也不方便同时访问多个数据库并做互操作

于是 ODBC 试图提供一种 像打印机驱动那样 的统一访问模式：

• 应用程序只面对 ODBC API
• 中间由 Driver Manager 负责装载、管理驱动
• 再由具体 ODBC Driver 去和某个 DBMS 通信

从体系结构上看，ODBC 至少包含三层：

1. Application
2. Driver Manager
3. 具体 Driver + Data Source

其中：

• Driver Manager 负责管理应用与驱动之间的通信、装载驱动、配置数据源
• Driver 负责把 ODBC 标准调用翻译成某个数据库能理解的调用
• Data Source Name（DSN） 则把一个具体连接包装成名字，里面通常包含服务器名、驱动、数据库名等信息

所以 ODBC 的真正价值不只是 多一个 API ，而是：

它把 应用如何访问数据库 与 底层到底是哪家数据库 进一步解耦了。

---

1
SQLAllocEnv(&env);
2
SQLAllocConnect(env, &conn);
3
SQLConnect(conn, "db.yale.edu", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS);

1
char *sqlquery =
2
    "select dept_name, sum(salary) from instructor group by dept_name";
3
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);

1
SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1);
2
SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0, &lenOut2);
3
while (SQLFetch(stmt) == SQL_SUCCESS) {
4
    printf("%s %g\n", deptname, salary);
5
}

1
SQLAllocHandle(SQL_HANDLE_ENV,  NULL, &hEnv);
2
SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
3
              (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
4
SQLAllocHandle(SQL_HANDLE_DBC,  hEnv, &hDBC);
5
SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hSTMT);

1
SQLBindCol(hSTMT, 1, SQL_C_CHAR,  deptname, 80, &lenOut1);
2
SQLBindCol(hSTMT, 2, SQL_C_FLOAT, &salary,  0,  &lenOut2);
3

4
while (SQLFetch(hSTMT) == SQL_SUCCESS) {
5
    printf("%s %g\n", deptname, salary);
6
}

1
SQLFreeHandle(SQL_HANDLE_STMT, hSTMT);
2
SQLDisconnect(hDBC);
3
SQLFreeHandle(SQL_HANDLE_DBC, hDBC);
4
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

Embedded SQL#

Embedded SQL 是另一条非常经典的路线：不是在程序里手工调用一堆 API，而是把 SQL 直接嵌入宿主语言源码中。

• SQL 是 computation incomplete（计算不完备）
• SQL 是 resource incomplete（资源不完备）

也就是：

• 它不擅长表达一般性的控制流程与复杂算法
• 它也不擅长直接管理输入输出、文件、界面、系统资源

所以需要 宿主语言 + SQL 联合工作。

宿主语言与宿主变量#

嵌入式 SQL 的核心是：

• 在宿主语言里直接写 SQL
• 用专门语法标出 这里是 SQL

例如：

1
EXEC SQL <embedded SQL statement>;

更准确地说，SQL 标准会把 SQL 嵌入到多种语言里，比如：

• C
• Java
• Cobol

这些语言就叫 host language（宿主语言）。

不同语言的标记方式略有差异：

• C 风格常写成 EXEC SQL ... ;
• Java 的某些嵌入形式会写成 #sql { ... }

SQL 用到宿主语言里的变量时，要在变量名前加冒号 :。

例如：

1
EXEC SQL BEGIN DECLARE SECTION;
2
int credit_amount;
3
EXEC SQL END DECLARE SECTION;

然后在 SQL 中写：

1
where tot_cred > :credit_amount

这里 :credit_amount 不是 SQL 变量，而是宿主语言变量。

嵌入式 SQL 真正难的地方，不只是 语法怎么写 ，而是三类接口问题：

1. 如何标记 SQL 片段的起止
2. 数据库和宿主语言如何通信
3. SQL 类型系统与宿主语言类型系统如何对接

SQLCA 与 SQLDA：程序和数据库怎么交换状态#

两个经典结构：

• SQLCA
• SQLDA

1. SQLCA

SQLCA 可以理解为 SQL Communication Area，它保存最近一次 SQL 执行后的状态信息。

最常见的是看：

• sqlcode

如果执行成功，通常可以继续走；如果失败，就根据 sqlcode 判断错误。

所以像下面这种代码：

1
EXEC SQL INCLUDE SQLCA;
2
...
3
EXEC SQL insert into account
4
    values (:account_no, :branch_name, :balance);
5
if (SQLCA.sqlcode != 0) printf("Error!\n");
6
else printf("Success!\n");

本质上就是：

SQL 执行完以后，程序到 SQLCA 里查看执行状态。

2. SQLDA

SQLDA 可以理解为 SQL Descriptor Area。

它更偏 描述信息 ，主要用于管理：

• 变量地址
• 长度
• 类型
• 指示变量
• 名字等元数据

直觉上可以把它理解成：

当 SQL 的输入输出结构比较动态时，程序需要一个 描述器 来说明这些数据长什么样。

所以：

• SQLCA 更像 状态区
• SQLDA 更像 描述区

Embedded SQL 的基本例子：单条记录的增删改查#

无游标 的基本操作：

1
EXEC SQL BEGIN DECLARE SECTION;
2
char account_no[11];
3
char branch_name[16];
4
int balance;
5
EXEC SQL END DECLARE SECTION;
6

7
EXEC SQL CONNECT TO bank_db USER Adam Using Eve;
8
EXEC SQL insert into account
9
    values (:account_no, :branch_name, :balance);

1
EXEC SQL delete from account
2
where account_number = :account_no;

1
EXEC SQL update account
2
set balance = balance + :balance
3
where account_number = :account_no;

1
EXEC SQL select balance into :balance
2
from account
3
where account_number = :account_no;

指示变量（indicator variable）#

null 怎么传到宿主语言里？

因为像 C 里的 int balance; 本身没有 SQL null 这个概念，所以通常要再配一个 indicator variable：

1
int balance;
2
int mask;
3
EXEC SQL select balance into :balance:mask
4
from account
5
where account_number = :account_no;

这里的含义大致是：

• mask = 0：正常取到值
• mask < 0：该列为 null
• mask > 0：可能发生截断之类情况

所以 indicator variable 的本质作用是：

补上 宿主语言原生类型无法直接表达 SQL null / 截断状态 的那部分语义。

为什么需要 cursor#

如果查询结果可能有多行，就不能只靠简单的 select ... into 一次装完。

这时要用 cursor（游标）。

你可以把它理解成：

结果集上的一个 读头 ，每次取一行。

例如：

1
EXEC SQL
2
declare c cursor for
3
select ID, name
4
from student
5
where tot_cred > :credit_amount;

这个语句只是 定义查询 ，还没真正执行。

open / fetch / close#

游标的生命周期通常是：

1. open c

1
EXEC SQL open c;

此时数据库真正执行查询，并把结果保存在临时结果关系里。

2. fetch c into ...

1
EXEC SQL fetch c into :si, :sn;

每次 fetch：

• 取一行
• 把该行列值放到宿主变量里

多次 fetch 就会依次取后面的行。

3. close c

1
EXEC SQL close c;

临时结果被释放。

当结果取完时，SQLCA 中的 SQLSTATE 会变成 '02000'，表示 no more data。

完整流程：

1
EXEC SQL DECLARE account_cursor CURSOR FOR
2
select account_number, balance
3
from depositor natural join account
4
where depositor.customer_name = :customer_name;
5

6
EXEC SQL open account_cursor;
7
for (;;) {
8
    EXEC SQL fetch account_cursor into :account_no, :balance;
9
    if (SQLCA.sqlcode != 0) break;
10
    printf("%s %d\n", account_no, balance);
11
}
12
EXEC SQL close account_cursor;

这个例子比抽象语法更直观：

• declare cursor 只是定义查询模板
• open 才真正执行
• 每次 fetch 只拿一行
• 用循环不断取，直到 sqlcode 表示无更多数据

因此游标和 JDBC 的 ResultSet 很像，只是写法更 嵌入式 SQL 风格。

for update 与 where current of#

embedded SQL 还支持 边扫描边更新 。

先定义：

1
EXEC SQL
2
declare c cursor for
3
select *
4
from instructor
5
where dept_name = 'Music'
6
for update;

然后每次 fetch 之后，可以写：

1
update instructor
2
set salary = salary + 1000
3
where current of c;

这句话的意思是：

更新 游标当前指向的那一行 。

它很适合写逐行处理逻辑。

example：

1
if (balance < 1000)
2
    EXEC SQL update account
3
    set balance = balance * 1.05
4
    where current of account_cursor;
5
else
6
    EXEC SQL update account
7
    set balance = balance * 1.06
8
    where current of account_cursor;

它体现出 where current of 的最大价值：

• 不需要再写一遍主键条件
• 直接更新 刚刚 fetch 到的当前行 即可

对于逐行扫描 + 条件更新，这种写法非常自然。

Dynamic SQL：运行时再构造 SQL#

静态嵌入式 SQL 的特点是：

• 关系名、属性名、SQL 结构大多写死在程序里
• 编译时就能确定

而动态 SQL 是：

• SQL 语句在运行时构造
• 再由程序提交给数据库执行

这在下面两类场景尤其常见：

1. 表名、列名、条件要由用户输入决定
2. SQL 模板固定，但参数值会反复变化

常见有两种执行方式。

1
sprintf(statement, "delete from %s where %s", table_name, condition);
2
EXEC SQL EXECUTE IMMEDIATE :statement;

1
char *sqlprog =
2
    "update account
3
     set balance = balance * 1.05
4
     where account_number = ?";
5

6
EXEC SQL prepare dynprog from :sqlprog;
7
EXEC SQL execute dynprog using :account;

为什么要把逻辑放进数据库#

到这里，Chapter 5 开始把重点从 程序如何访问数据库 转向 数据库内部自己执行逻辑 。

把业务逻辑放进数据库，有几个典型理由：

• 外部应用不需要了解太多内部表结构细节
• 多个应用可以共享同一套数据库内部逻辑
• 一些约束和操作更靠近数据本身，更容易保持一致

也就是说，函数、过程、触发器，本质上都在回答一个问题：

某些逻辑，是不是应该 挪到数据库内部 来做？

函数 function#

最简单的例子是一个标量函数：给定院系名，返回该院系教师人数。

1
create function dept_count(dept_name varchar(20))
2
returns integer
3
begin
4
    declare d_count integer;
5
    select count(*) into d_count
6
    from instructor
7
    where instructor.dept_name = dept_name;
8
    return d_count;
9
end

然后就可以在 SQL 里直接调用：

1
select dept_name, budget
2
from department
3
where dept_count(dept_name) > 1;

它说明：

• 函数可以把一段重复逻辑封装起来
• 以后别的 SQL 直接调用函数即可
• SQL 变得更像 用已有组件拼装业务语义

表值函数 returns table#

SQL:2003 还支持返回一张关系表的函数。

例如：

1
create function instructors_of(dept_name char(20))
2
returns table (
3
    ID varchar(5),
4
    name varchar(20),
5
    dept_name varchar(20),
6
    salary numeric(8,2)
7
)
8
return table (
9
    select ID, name, dept_name, salary
10
    from instructor
11
    where instructor.dept_name = instructors_of.dept_name
12
)

使用时：

1
select *
2
from table(instructors_of('Music'));

这个思想很重要：

• 普通函数返回一个值
• 表值函数返回 一整张结果表

它相当于 带参数的 view 。

普通 view 没参数，而表值函数可以吃参数，所以更灵活。

存储过程 procedure#

前面的 dept_count 也可以写成过程：

1
create procedure dept_count_proc(
2
    in dept_name varchar(20),
3
    out d_count integer
4
)
5
begin
6
    select count(*) into d_count
7
    from instructor
8
    where instructor.dept_name = dept_count_proc.dept_name;
9
end

调用方式：

1
declare d_count integer;
2
call dept_count_proc('Physics', d_count);

函数和过程的直观区别可以这样理解：

• function：更像 表达式 ，常常直接写进查询里
• procedure：更像 执行一个动作

当然不同 DBMS 的具体限制不完全一样，但这个直觉通常成立。

过程化控制结构#

slides 这里讲的是 SQL 的procedural extension。

也就是说，SQL 不只是查表语言，它还加入了：

• 变量
• 分支
• 循环
• 复合语句

这样数据库内部就可以写更复杂的程序。

PL/SQL ：

PL/SQL = Procedural Language Extension to SQL

它允许在 SQL 的基础上加入更完整的程序设计工具，例如：

• loops
• conditions
• functions

也就是说，前面我们在 Functions and Procedures 里讲的很多想法，在 Oracle 里往往会具体落成 PL/SQL block 的形式。

1
DECLARE      -- 可选：声明变量、类型、游标
2
BEGIN        -- 必选：执行语句
3
EXCEPTION    -- 可选：异常处理
4
END;         -- 必选
5
/

1
declare
2
    birthday DATE;
3
    age NUMBER(2) NOT NULL := 27;
4
    name VARCHAR2(13) := 'Levi';
5
    magic CONSTANT NUMBER := 77;
6
    valid BOOLEAN NOT NULL := TRUE;
7
begin
8
    ...
9
end;
10
/

1
declare
2
    sname Sailors.sname%TYPE;
3
    fav_boat VARCHAR2(30);
4
    my_fav_boat fav_boat%TYPE := 'Pinta';
5
begin
6
    ...
7
end;
8
/

1
declare
2
    reserves_record Reserves%ROWTYPE;
3
begin
4
    reserves_record.sid := 9;
5
    reserves_record.bid := 877;
6
end;
7
/

1
declare
2
    type sailor_record_type is record (
3
        sname  VARCHAR2(10),
4
        sid    VARCHAR2(9),
5
        age    NUMBER(3),
6
        rating NUMBER(3)
7
    );
8
    sailor_record sailor_record_type;
9
begin
10
    sailor_record.sname := 'peter';
11
    sailor_record.age := 45;
12
end;
13
/

1
begin
2
    -- 多条 SQL
3
end

1
declare n integer default 0;
2
while n < 10 do
3
    set n = n + 1;
4
end while;
5

6
repeat
7
    set n = n - 1;
8
until n = 0
9
end repeat;

1
declare n integer default 0;
2
for r as
3
    select budget from department
4
    where dept_name = 'Music'
5
do
6
    set n = n - r.budget;
7
end for;

1
if boolean_expression then
2
    statement
3
elseif boolean_expression then
4
    statement
5
else
6
    statement
7
end if;

1
if rating > 7 then
2
    v_message := 'You are great';
3
elsif rating >= 5 then
4
    v_message := 'Not bad';
5
else
6
    v_message := 'Pretty bad';
7
end if;

1
declare
2
    v_sname  VARCHAR2(10);
3
    v_rating NUMBER(3);
4
begin
5
    select sname, rating
6
    into   v_sname, v_rating
7
    from   Sailors
8
    where  sid = '112';
9
end;
10
/

1
declare
2
    cursor c is select * from sailors;
3
    sailorData sailors%ROWTYPE;
4
begin
5
    open c;
6
    fetch c into sailorData;
7
    close c;
8
end;
9
/

1
begin
2
    update mylog
3
    set logon_num = logon_num + 1
4
    where who = user;
5

6
    if SQL%ROWCOUNT = 0 then
7
        insert into mylog values (user, 1);
8
    end if;
9

10
    commit;
11
end;
12
/

Example：registerStudent#

• 输入学生、课程、班级、学期、年份等信息
• 先查当前已选人数 currEnrol
• 再查教室容量 limit
• 如果人数还没满，就插入 takes
• 如果已经满员，就返回错误信息

设计思路：

选课 不是简单插一条 takes 记录 。 它其实是一个带业务检查的原子过程。

1
create function registerStudent(
2
    in s_id varchar(5),
3
    in s_courseid varchar(8),
4
    in s_secid varchar(8),
5
    in s_semester varchar(6),
6
    in s_year numeric(4,0),
7
    out errorMsg varchar(100)
8
)
9
returns integer
10
begin
11
    declare currEnrol int;
12
    select count(*) into currEnrol
13
    from takes
14
    where course_id = s_courseid
15
      and sec_id = s_secid
16
      and semester = s_semester
17
      and year = s_year;
18

19
    declare limit int;
20
    select capacity into limit
21
    from classroom natural join section
22
    where course_id = s_courseid
23
      and sec_id = s_secid
24
      and semester = s_semester
25
      and year = s_year;
26

27
    if (currEnrol < limit) then
28
    begin
29
        insert into takes values
30
            (s_id, s_courseid, s_secid, s_semester, s_year, null);
31
        return(0);
32
    end;
33
    end if;
34

35
    -- Otherwise, section capacity limit already reached
36
    set errorMsg = 'Enrollment limit reached for course ' || s_courseid
37
                   || ' section ' || s_secid;
38
    return(-1);
39
end;

数据库内部做这件事的好处是：

• 容量检查和插入动作绑定在一起
• 外部应用不必自己重复实现
• 业务约束更靠近数据，更统一

也就是说，存储过程/函数的真正价值，不只是 省几行代码 ，而是把业务规则封装成数据库服务接口。

外部语言函数 / 过程#

SQL:1999 允许函数和过程用其他语言写，比如 C/C++。

声明时大概像：

1
create procedure dept_count_proc(in dept_name varchar(20), out count integer)
2
language C
3
external name '/usr/avi/bin/dept_count_proc';

这说明数据库其实允许这样一种模式：

• SQL 只声明接口
• 真正实现交给外部语言

好处：

• 表达能力更强
• 某些计算更高效

但问题也随之而来。

安全问题与 sandbox#

把外部代码直接丢进数据库系统运行，风险很大：

• 可能误伤数据库内部结构
• 可能越权访问不该看的数据
• 可能把数据库进程本身搞崩

两种缓解思路：

1. sandbox

也就是 沙箱执行 。

• 用更安全的语言，比如 Java
• 限制它随意访问系统或数据库内部内存

2. 单独进程执行

• 外部函数不直接跑在数据库地址空间里
• 而是在另一个进程里执行
• 数据库和它通过进程间通信传参、拿结果

代价就是性能开销更大。

所以这里有一个很典型的权衡：

• 性能
• 安全隔离

通常不能同时取到极致。

什么是 trigger#

trigger 是：

当数据库发生某类修改时，系统自动执行的一段语句。

也就是说，它不是你显式 call 的。

它是 某事件发生时，被动触发 的。

所以 trigger 很适合做：

• 审计日志
• 自动维护派生数据
• 补充完整性检查
• 自动修正输入

ECA 规则#

trigger 常被概括成 ECA：

• E = Event：什么事件触发？insert / delete / update
• C = Condition：满足什么条件才执行？
• A = Action：满足条件后做什么？

所以一个 trigger 设计，本质上要回答三件事：

1. 什么时候触发
2. 触发时检查什么
3. 真正执行什么动作

Example1：大额余额变动记日志#

1
create trigger account_trigger after update of balance on account
2
referencing new row as nrow
3
referencing old row as orow
4
for each row
5
when nrow.balance - orow.balance >= 200000
6
  or orow.balance - nrow.balance >= 50000
7
begin
8
    insert into account_log values(
9
        nrow.account_number,
10
        nrow.balance - orow.balance,
11
        current_time()
12
    );
13
end;

这个 trigger 的意思是：

• 当 account.balance 被更新后
• 如果变动金额太大
• 就往 account_log 写一条日志

这里很值得注意的是：

• old row 表示更新前的元组
• new row 表示更新后的元组

所以 trigger 天然很适合做 比较新旧状态 的工作。

Example2：用 trigger 补足外键约束表达不了的完整性#

一个很有代表性的例子。

time_slot_id 不是 time_slot 的主键，因此没法直接从 section 建一个标准外键过去。

这时就用 trigger 做约束补充。

插入 section 时检查：

1
create trigger timeslot_check1 after insert on section
2
referencing new row as nrow
3
for each row
4
when (nrow.time_slot_id not in (
5
    select time_slot_id from time_slot
6
))
7
begin
8
    rollback;
9
end;

意思是：

• 插入新 section 后
• 如果它引用了一个根本不存在的 time_slot_id
• 整个事务回滚

删除 time_slot 时检查：

1
create trigger timeslot_check2 after delete on time_slot
2
referencing old row as orow
3
for each row
4
when (
5
    orow.time_slot_id not in (select time_slot_id from time_slot)
6
    and orow.time_slot_id in (select time_slot_id from section)
7
)
8
begin
9
    rollback;
10
end;

意思是：

• 某个 time_slot_id 的最后一条记录被删掉了
• 但 section 里还有记录引用它
• 那就不允许删，直接回滚

这说明 trigger 可以补足这样一类问题：

标准外键语法表达不了， 但业务上又必须保证的数据一致性。

Example3：before update 纠正数据#

trigger 不一定只在事后记日志，也可以在事前修正输入。

例如：

1
create trigger setnull_trigger before update of grade on takes
2
referencing new row as nrow
3
for each row
4
when (nrow.grade = ' ')
5
begin atomic
6
    set nrow.grade = null;
7
end;

意思是：

• 更新成绩前
• 如果有人把成绩写成空白字符串
• 系统自动把它改成 null

这是一个典型的数据清洗/标准化用法。

Example4：成绩更新后自动累计学分#

1
create trigger credits_earned after update of grade on takes
2
referencing new row as nrow
3
referencing old row as orow
4
for each row
5
when nrow.grade <> 'F' and nrow.grade is not null
6
     and (orow.grade = 'F' or orow.grade is null)
7
begin atomic
8
    update student
9
    set tot_cred = tot_cred + (
10
        select credits
11
        from course
12
        where course.course_id = nrow.course_id
13
    )
14
    where student.id = nrow.id;
15
end;

它的业务语义是：

• 某学生某门课原来没通过 / 还没成绩
• 现在成绩更新成了 通过
• 那么自动给 student.tot_cred 加上该课程学分

这个例子特别能体现 trigger 的优势：

• takes.grade 改了
• student.tot_cred 自动跟着更新

也就是：把派生数据维护自动化。

for each row vs for each statement#

trigger 有两种重要粒度：

1. for each row

• 每影响一行就执行一次
• 更细
• 适合逐行逻辑

2. for each statement

• 整条 SQL 语句只执行一次
• 适合批量更新后的整体检查
• 通常效率更高

一个 statement-level 的例子：

1
create trigger grade_trigger after update of grade on takes
2
referencing new table as new_table
3
for each statement
4
when exists (
5
    select avg(grade)
6
    from new_table
7
    group by course_id, sec_id, semester, year
8
    having avg(grade) < 60
9
)
10
begin
11
    rollback;
12
end;

这里 new_table 是 transition table，保存这条语句影响到的所有新行。

这个 trigger 的意思是：

• 一次批量更新成绩后
• 如果某个班级的平均分低于 60
• 那么整个操作回滚

这种 整体检查 如果按 for each row 做，反而不自然。

trigger 的风险与替代方案#

trigger 很强，但有明显风险。

风险 1：容易 悄悄执行

比如：

• 从备份恢复数据
• 远程复制更新
• 批量导入数据

这时 trigger 可能被意外触发。

风险 2：可能拖垮关键事务

trigger 里只要有 bug，就可能让本来重要的更新失败。

风险 3：级联触发

一个 trigger 改了表，可能又引发别的 trigger，最后非常难追踪。

所以一些旧时代常拿 trigger 做的事，现在有更好的专门机制：

• 维护汇总数据：现在可用 materialized view
• 复制：现在数据库往往有内建 replication

另外，很多业务逻辑也可以通过封装方法/存储过程来做，而不是直接放 trigger 里。

所以经验上：

• trigger 很适合小而清晰的自动反应逻辑
• 不适合堆太复杂、太隐蔽的业务流程

为什么需要递归查询#

有一类问题，天然带 传递性 或 层级性 ：

• A 的先修课是 B，B 的先修课是 C，那 C 也是 A 的间接先修课
• 员工的经理的经理，仍然是他的上级
• 组织树、类别树、祖先后代关系

这类问题的共同特点是：

不是固定连一次、两次表就结束， 而是要一直 沿关系往上/往下追 。

这时就需要递归查询。

先修课的传递闭包#

1
with recursive rec_prereq(course_id, prereq_id) as (
2
    select course_id, prereq_id
3
    from prereq
4

5
    union
6

7
    select rec_prereq.course_id, prereq.prereq_id
8
    from rec_prereq, prereq
9
    where rec_prereq.prereq_id = prereq.course_id
10
)
11
select *
12
from rec_prereq;

这个查询的含义是：

第一部分（base query）

1
select course_id, prereq_id from prereq

先取出所有 直接先修 关系。

第二部分（recursive step）

1
select rec_prereq.course_id, prereq.prereq_id
2
from rec_prereq, prereq
3
where rec_prereq.prereq_id = prereq.course_id

如果：

• X <- Y
• Y <- Z

那么就推出：

• X <- Z

不断重复，直到不再产生新元组为止。

这个结果就叫 transitive closure（传递闭包）。

为什么非递归 SQL 做不好这类问题#

如果你不用递归，只靠普通 SQL，那么你最多只能写出：

• 连一次 prereq
• 连两次 prereq
• 连三次 prereq

也就是说，连接层数是写死的。

但真实数据库里：

• 先修链可能 2 层
• 可能 5 层
• 也可能 20 层

只要层数超过你事先写死的 join 次数，查询就失效。

所以递归查询的本质价值在于：

不是预先写死 追几层 ， 而是一直推到固定点为止。

递归的迭代版思路#

一个 非递归 SQL + 过程化控制 的版本，即 findAllPrereqs 思想。

1. 建临时表存当前已知先修课集合
2. 再建临时表存 上一轮新增的课程
3. 根据上一轮新增项继续往外扩展
4. 把新找到但之前没见过的课程加入集合
5. 若这一轮没有新课程，停止

这说明：

• 递归 SQL 和 迭代过程 在逻辑上是等价的
• 递归 SQL 更声明式、更紧凑
• 过程化写法更接近算法实现

层级关系：员工-经理传递关系#

1
with recursive empl(employee_name, manager_name) as (
2
    select employee_name, manager_name
3
    from manager
4

5
    union
6

7
    select manager.employee_name, empl.manager_name
8
    from manager, empl
9
    where manager.manager_name = empl.employee_name
10
)
11
select *
12
from empl;

这里求出的就是：

• 员工对直接经理
• 员工对经理的经理
• 员工对更高层经理

本质上仍然是传递闭包。

所以要建立一个统一直觉：

• 先修关系 是有向图边
• 经理关系 也是有向图边
• 递归查询就是在算图上的可达关系

Ranking#

rank() 与 dense_rank()#

如果有关系：

1
student_grades(ID, GPA)

想按照 GPA 排名，可以写：

1
select ID, rank() over (order by GPA desc) as s_rank
2
from student_grades;

如果还想按排名顺序输出：

1
select ID, rank() over (order by GPA desc) as s_rank
2
from student_grades
3
order by s_rank;

这里的关键是：

• over (...) 定义的是 窗口
• 排名函数不会把多行压成一行
• 它是给结果中的每一行额外算一个排名值

rank() 的特点是：

• 并列名次会跳号

例如前两名并列第一，那么下一名是第三。

dense_rank() 则不跳号，下一名是第二。

低效但直观的 基础 SQL 排名写法#

不用窗口函数的排名写法：

1
select ID,
2
       (1 + (select count(*)
3
             from student_grades B
4
             where B.GPA > A.GPA)) as s_rank
5
from student_grades A
6
order by s_rank;

这个思路很直观：

• 一个人前面有多少人比他 GPA 高
• 他的排名就是那个数量加 1

但它很低效，因为每一行都要再做一次子查询统计。

这正好说明窗口函数的价值：

• 表达更自然
• 执行通常更高效

分组内排名 partition by#

排名还可以在分组内部做。

例如 按院系分别排名 ：

1
select ID, dept_name,
2
       rank() over (partition by dept_name order by GPA desc) as dept_rank
3
from dept_grades
4
order by dept_name, dept_rank;

这里：

• partition by dept_name 先把数据按院系分区
• 每个分区内部再按 GPA 排名

这就是很多 Top-N within each group 问题的标准解法。

比单纯 limit n 更强，因为：

• limit n 只能取全局前 n
• partition + rank 可以取 每组前 n

其他排名函数#

• percent_rank()：百分位排名
• cume_dist()：累计分布
• row_number()：顺序编号
• ntile(n)：把排序结果切成 n 个桶

例如：

1
select ID, ntile(4) over (order by GPA desc) as quartile
2
from student_grades;

这就是把学生按 GPA 分成四个分位组。

此外，SQL 还允许显式指定 nulls first 或 nulls last：

1
select ID,
2
       rank() over (order by GPA desc nulls last) as s_rank
3
from student_grades;

这能避免空值在排序中的位置含糊不清。

Windowing#

移动窗口#

窗口函数不只做排名，也能做 滑动聚合 。

经典例子是移动平均。

给定：

1
sales(date, value)

要算 当天、前一天、后一天 三天的总和/平均，可以写：

1
select date,
2
       sum(value) over (
3
           order by date
4
           rows between 1 preceding and 1 following
5
       )
6
from sales;

这个窗口的意思是：

• 以当前行为中心
• 往前取 1 行
• 往后取 1 行

所以它天然适合时间序列平滑。

常见窗口边界#

几类常见窗口框架：

• between rows unbounded preceding and current row
• rows unbounded preceding
• range between 10 preceding and current row
• range interval 10 day preceding

要区分：

• rows：按 物理行数 取窗口
• range：按 排序键值范围 取窗口

比如：

• rows 1 preceding 是前一行
• range 10 preceding 是所有排序值在当前值减 10 到当前值之间的行

分区内累计和#

另一个特别实用的例子是 账户余额流水 。

给定：

1
transaction(account_number, date_time, value)

其中：

• 存款是正数
• 取款是负数

想求 每个账户在每笔交易后的余额 ：

1
select account_number, date_time,
2
       sum(value) over (
3
           partition by account_number
4
           order by date_time
5
           rows unbounded preceding
6
       ) as balance
7
from transaction
8
order by account_number, date_time;

这里的含义是：

• 每个账户单独分区
• 在该账户内部按时间排序
• 从第一笔到当前笔全部累加

这就是 running total（累计和）。

OLAP#

什么是 OLAP#

OLAP = Online Analytical Processing。

核心不是事务处理，而是：

• 交互式分析数据
• 从不同角度汇总数据
• 快速切换观察视角

也就是：

不是 改一条记录 ， 而是 从海量数据里快速看规律 。

维度属性与度量属性#

分析型数据分成两类：

1. 度量属性（measure）

• 用来度量某个值
• 可以聚合
• 例如销量 quantity

2. 维度属性（dimension）

• 用来决定 从哪个角度看
• 例如：item_name、color、clothes_size

简单的理解：

• quantity 回答 有多少
• item_name/color/size 回答 按什么分类看

交叉表 cross-tab / pivot-table#

销售关系：

1
sales(item_name, color, clothes_size, quantity)

交叉表的特征是：

• 一个维度做行头
• 一个维度做列头
• 其他维度作为过滤条件或更上层标题
• 表格单元格里放聚合结果

例如 按商品和颜色看销量汇总 的表，就是一个典型 cross-tab / pivot-table。

Data Cube#

交叉表其实只是多维数据立方体的一个二维视图。

Data Cube 可以理解为：

• 对多个维度同时做聚合
• 得到多种不同粒度的汇总结果

2D 的交叉表只是 cube 的一个切面。

Hierarchy、roll up、drill down、slice、dice、pivot#

分析型查询里常见的几个词

Hierarchy（层级）

例如时间维度可以按：

• hour
• date
• month
• quarter
• year

地区维度可以按：

• city
• state
• country
• region

Roll up

• 从细粒度汇总到粗粒度
• 例如日 -> 月 -> 年

Drill down

• 从粗粒度展开到细粒度
• 年 -> 月 -> 日

Slice / Dice

• slice：固定某个维度值，切出一个切片
• dice：固定多个维度值，切出一个子块

Pivot

• 改变交叉表的观察维度
• 例如原来按 商品 × 颜色 ，改成 商品 × 尺码

把交叉表表示成关系#

交叉表可以重新表示成普通关系。

为了表示 汇总 ，有时会引入一个像 all 这样的值。

例如：

1
(item_name, color, clothes_size, quantity)

其中某些列取 all，表示 这一维已经被汇总掉了 。

但 SQL 标准里通常不是用 all 这个字面量，而是用 null 来表示这种 汇总占位 。

这就会带来一个问题：

• 普通 null 表示 值未知 / 不存在
• 汇总用 null 表示 all

二者语义会冲突

这也是后面 grouping() 函数存在的原因。

cube#

cube 会对指定属性的每个子集都做分组聚合。

例如：

1
select item_name, color, clothes_size, sum(quantity)
2
from sales
3
group by cube(item_name, color, clothes_size);

它等价于把这些 group by 的结果做并集：

• (item_name, color, clothes_size)
• (item_name, color)
• (item_name, clothes_size)
• (color, clothes_size)
• (item_name)
• (color)
• (clothes_size)
• ()

如果有 3 个维度，就一共是 2^3 = 8 种分组。

这说明：

• cube 很强
• 但结果规模也会迅速膨胀

grouping() 与把 null 还原成 all#

由于 cube/rollup 会用 null 表示 这一维被汇总掉 ，所以要区分：

• 这个 null 是原始数据里的真空值
• 还是聚合产生的 all 占位

grouping(attr) 就是干这个的。

• 若 attr 的 null 是聚合产生的，返回 1
• 否则返回 0

例如：

1
select item_name, color, clothes_size, sum(quantity),
2
       grouping(item_name) as item_name_flag,
3
       grouping(color) as color_flag,
4
       grouping(clothes_size) as size_flag
5
from sales
6
group by cube(item_name, color, clothes_size);

进一步，还可以用 case / decode 把这类 null 显示成 all：

1
select case when grouping(item_name) = 1 then 'all' else item_name end as item_name,
2
       case when grouping(color) = 1 then 'all' else color end as color,
3
       sum(quantity) as quantity
4
from sales
5
group by rollup(item_name, color);

这里不能简单用 coalesce(item_name, 'all')，因为那会把原始数据里真正的空值也误改成 all。

rollup#

rollup 和 cube 类似，但只对前缀分组做聚合。

例如：

1
select item_name, color, clothes_size, sum(quantity)
2
from sales
3
group by rollup(item_name, color, clothes_size);

它产生的只是：

• (item_name, color, clothes_size)
• (item_name, color)
• (item_name)
• ()

所以：

• cube：所有子集
• rollup：只有前缀链

因此 rollup 特别适合层级结构。

例如：

1
select category, item_name, sum(quantity)
2
from sales, itemcategory
3
where sales.item_name = itemcategory.item_name
4
group by rollup(category, item_name);

这里就会自然得到：

• 每个商品小计
• 每个类别小计
• 总计

多个 rollup/cube 组合#

还可以在一个 group by 中组合多个 rollup/cube。

例如：

1
select item_name, color, clothes_size, sum(quantity)
2
from sales
3
group by rollup(item_name), rollup(color, clothes_size);

它的本质是：

• 第一个 rollup(item_name) 生成 {(item_name), ()}
• 第二个 rollup(color, clothes_size) 生成 {(color, clothes_size), (color), ()}
• 两者做笛卡尔积

最终得到多个组合层级。

这个思想和关系代数其实是连得上的：

每个 rollup 都在生成一组 group-by 列表， 多个 rollup/cube 组合时，本质上就是这些分组集合的组合。

grouping sets：更精细地指定分组集合#

它的作用是：

• cube 太多
• rollup 太少
• 我就手工指定 只要这几种分组

例如只想要：

• (color, clothes_size)
• (clothes_size, item_name)

那么可以写：

1
select item_name, color, clothes_size, sum(quantity)
2
from sales
3
group by grouping sets (
4
    (color, clothes_size),
5
    (clothes_size, item_name)
6
);

这比 cube 更可控。

MOLAP / ROLAP / HOLAP#

OLAP 系统实现上也分路线：

MOLAP

• 用多维数组等专门结构直接存 data cube
• 响应快
• 但实现更专门化

ROLAP

• 基于关系数据库实现 OLAP
• 靠 SQL、聚合、索引、物化视图等能力做分析

HOLAP

• 混合模式
• 一部分汇总放内存
• 明细数据和其他汇总放关系数据库

预计算与优化#

早期 OLAP 系统常常尝试 把所有可能汇总都预先算好 。

问题是：

• 若有 n 个维度
• 可能的 group by 组合就有 2^n 个

空间和时间代价都很夸张。

所以更合理的做法通常是：

• 只预计算一部分关键汇总
• 其他汇总按需从更细粒度结果再聚合出来

例如：

• (item_name, color) 的汇总
• 可以从 (item_name, color, size) 的汇总再聚出来

这对像 sum/count 这样的可分解聚合尤其适用。

而像 median 这种 非可分解聚合 ，就没这么方便。