Lec 5: Advanced SQL⚓︎

Accessing SQL from Programming Languages⚓︎

虽然 SQL 提供了强大的指示性 (directive) 查询语言，相比通用的编程语言更适合编写查询，但有时数据库程序员不得不使用通用编程语言，原因有：

• 不是所有的查询都能用 SQL 表达，有时只能用通用编程语言表达
• 对于非指示性的动作，SQL 无法做到，比如打印报告、与用户交互或将查询结果发送到 GUI 等

在通用编程语言中使用 SQL 的方法有：

• 动态 SQL(dynamic SQL)：程序通过一组特定的函数或方法和数据库服务器建立连接，随后动态 SQL 允许程序在运行时(runtime) 用字符串构造 SQL 查询，提交查询，并且将检索到的结果放到程序变量中（一次仅存一个元组）。动态 SQL 有以下标准：
  • JDBC：Java 用于连接数据库的 API
  • ODBC：原来为 C 写的用于连接数据库的 API，现在也适用于 C++、C#、Ruby、Go、PHP、VB 等
• 嵌入 SQL(embedded SQL)：预处理器(preprocessor) 在编译时识别 SQL 语句，将用 SQL 语句表达的请求转译为函数调用语句。在运行时，这些函数调用通过 API 与数据库连接。

JDBC⚓︎

public static void JDBCexample(String userid, String passwd) {
    try (
            Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@db.yale:1521:univdb",
                userid,
                passwd
            );
            Statement stmt = conn.createStatement();
    ) {
        try {
            stmt.executeUpdate(
                "INSERT INTO instructor VALUES('77987', 'Kim', 'Physics', 98000)"
            );
        } catch (SQLException sqle) {
            System.out.println("Could not insert tuple. " + sqle);
        }
        ResultSet rset = stmt.executeQuery(
            "SELECT dept_name, AVG(salary) " + 
            "FROM instructor " +
            "GROUP BY dept_name"
        );
        while (rset.next()) {
            System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
        }

    } catch (Exception sqle) {
        System.out.println("Exception: " + sqle);
    }
}

在上述程序中，必须在开头处导入 java.sql.*，里面包含了 JDBC 提供的功能接口定义。

Database Connection⚓︎

在 Java 程序中访问数据库的第一步是建立与数据库的连接，连接好后才能执行 SQL 语句。具体来说，需要使用 DriverManager 类的 getConnection() 方法，它接收以下参数：

• 数据库服务器相关信息，包括 URL/ 机器名、协议、端口号、数据库名
  • 关于协议：
    • JDBC 并没有规定协议，协议取决于数据库实现
    • JDBC 支持多种协议，比如 jdbc:oracle:thin 是 Oracle 支持的协议，而 jdbc:mysql 是 MySQL 支持的协议等
• 数据库用户名
• 密码

该方法返回的是一个 Connection 对象。

Shipping SQL Statements to the Database System⚓︎

建立连接后，就要将 SQL 语句发送到数据库系统，然后在里面执行语句，在 Java 中通过 Statement 类的实例来做到这一点。Statement 对象并非 SQL 语句本身，而是一种让 Java 程序调用和传送 SQL 语句到数据库相关的方法的对象。

而执行语句需要调用 executeQuery() 或 executeUpdate() 方法，它们分别对应查询语句和非查询语句（更新、插入、删除、创建等）的执行，并且后者会返回一个表示被插入 / 更新 / 删除的元组数（如果是创建语句的话则返回 0）。

Exceptions and Resource Management⚓︎

关于异常(exception)：

• 执行任何的 SQL 语句都有可能抛出异常，所以编程时需要记得用 try {...} catch {...} 语句块捕获异常。
• 异常可以分为 SQLException（与 SQL 相关的异常）和 Exception（一般的异常，与 Java 相关，比如空指针、数组越界等）。
• 如果可以的话，最好编写一个完整的异常处理函数，以应对各种异常。

关于资源管理(resource management)：

• 建立连接、创建语句以及其他 JDBC 对象都会占用系统资源，所以需要确保程序能够关闭上述这些资源，以免产生资源池耗尽导致的故障。
• 一种方法是显式调用关闭语句（比如 conn.close()、stmt.close() 分别关闭连接和语句），但一旦遇到异常，提前退出的话，这些关闭语句就来不及被调用，那么问题还是没解决。
• 更可靠的做法是使用 try-with-resources 构造块，就是在 try 关键字和语句块之间加上圆括号，里面包含连接、语句对象等资源，这样的话当离开 try 语句块时，这些资源会被自动关闭。

Retrieving the Result of Query⚓︎

• 使用 executeQuery() 方法执行查询后，检索得到的元组会放在一个 ResultSet 对象上，但一次只能取其中的一个元组。
• 具体来说，该对象调用 next() 方法获取下一个元组（如果还有的话），返回值是一个布尔值，表明是否成功获取元组。
• 另外，该对象提供了一些以 get 开头的方法来获取元组中具体属性的值，它们接收单个参数，可以是属性名（字符串），也可以是属性的位置（整数值从 1 开始）。常见的 get 方法有：
  • getString()：可以检索任意 SQL 基本数据类型
  • getFloat()：仅限于获取浮点数

Prepared Statements⚓︎

我们不必预先编写一条完整的 SQL 语句，而先创建一条预备语句(prepared statements)，其中语句中出现的值用 ? 替代（占位符），之后再将具体的值插入到对应的位置上。数据库系统会编译好这种预备语句。在执行这种语句的时候，数据库系统复用先前编译好的预备语句，然后将具体值应用到到语句中，构成一条完整的语句。

• Connection 类的 prepareStatement() 方法用于设置预备语句，该方法返回的是一个 PreparedStatement 类的对象，该对象同样具有 executeQuery() 和 executeUpdate() 方法。
• 在 prepareStatement() 语句内的 SQL 语句具体值必须用 ? 替代，之后可以用 set 开头的方法来设置具体值（比如 setInt()、setString()）。这类方法接收两个参数，第 1 个参数指明设置的是第几个 ?（从 1 开始），第 2 个参数是具体值。

PreparedStatement pStmt = conn.prepareStatement(
    "INSERT INTO instructor VALUES(?, ?, ?, ?)"
);
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();

INSERT INTO instructor VALUES("88878", "Perry", "Finance", 125000);

预备语句的优势：

• 在多次执行相同格式的查询语句时，由于只需编译一次，只是在运行的时候用上具体值，因此执行效率更高
• 对于特殊字符，set 类方法会将其转化为转义字符，从而避免语法错误，并且能够缓减 SQL 注入(injection) 攻击

"SELECT * FROM instructor WHERE name = '" + name + "'"

"SELECT * FROM instructor WHERE name = '" + "X' OR 'Y' = 'Y" + "'"

"SELECT * FROM instructor WHERE name = 'X' OR 'Y' = 'Y'

Callable Statements⚓︎

JDBC 提供了 CallableStatement 接口，用于调用 SQL 的过程或函数，比如：

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

函数的返回值，以及过程的出参数 (out parameters) 的数据类型必须使用 registerOutParameter() 方法注册，且可以使用类似结果集的 get 方法检索这些值。

Metadata Features⚓︎

通常，Java 程序会在运行时，从数据库系统中获取数据声明。

用于存储执行查询语句的结果的 ResultSet 接口有一个方法 getMetaData()，它返回一个 ResultSetMetaData 类对象，里面包含结果集的元数据(metadata)。而这个 ResultSetMetaData 对象也有一些寻找元数据信息的方法，比如结果的列数、具体列的名称和类型等等，这样我们就能获取数据声明（模式 (schema)）了。

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

Connection 接口有一个方法 getMetaData()，它返回一个 DatabaseMetaData 对象。而 DatabaseMetaData 接口则提供了寻找数据库元数据的途径，提供了更为丰富的方法，比如返回产品名、版本号等等。

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

while (rs.next()) {
    System.out.println(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"));
}

DatabaseMetaData 还有其他方法：

• getTables()：列出数据库中的所有表。前三个参数和 getColumns() 一致，最后一个参数用于限制符合条件的表，如果设为 null 则返回所有表（包括系统内部的表）
• getPrimaryKeys()：获取主键
• getCrossReference()：获取外键参照
• ...

Other Features⚓︎

• 可更新的结果集(updatable result sets)：在这种结果集上的更新操作会同步更新其对应的关系上。
• 默认情况下，每个 SQL 语句就是一个单独的事务。Connection 接口的 setAutoCommit() 方法用于改变这种行为，其中 setAutoCommit(false) 就可以关闭这种自动提交的行为，但此时需要用 commit() 和 rollback() 方法手动提交或回滚。
• 关于大型对象（Blob、Clob）
  • 读：
    • ResultSet 对象提供了 getBlob() 和 getClob() 方法，分别返回 Blob 和 Clob 类型的对象
    • 这些对象不会存储完整的大型对象，而存的是定位器 (locator)（本质上是个指针）
    • 可通过 getBytes() 和 getSubString() 方法获取大型对象里的内容，类似读取文件或输入流
  • 写：
    • preparedStatement 类允许应用 Blob 和 Clob 对象，可以分别使用 setBlob() 和 setClob() 方法传送，其中第一个参数是整数类型的参数索引，第二个参数是 InputStream 类的输入流

Database Access from Python⚓︎

import psycopg2

def PythonDatabaseExample(userid, passwd):
    try:
        conn = psycopg2.connect(
            host="db.yale",
            port=5432,
            dbname="univdb",
            user=userid,
            password=passwd
        )
        cur = conn.cursor()
        try:
            cur.execute(
                "INSERT INTO instructor VALUES(%s %s %s %s)",
                ("77987", "Kim", "Physics", 98000)
            )
            conn.commit()
        except Exception as sqle:
            print("Could not insert tuple.", sqle)
            conn.rollback()
        cur.execute((
            "SELECT dept_name, AVG(salary)"
            "FROM instructor"
            "GROUP BY dept_name"
        ))
        for dept in cur:
            print(dept[0], dept[1])
    except Exception as sqle:
        print("Exception:", sqle)

ODBC⚓︎

• 很多应用程序，包括 GUI、静态包以及电子表格等都采用相同的 ODBC API 与数据库服务器连接。
• 支持 ODBC 的数据库系统会提供一个用于链接客户端程序的库。当客户端程序发起 ODBC API 的调用时，库代码便与服务器通信，执行需要执行的动作，并返回结果。

void ODBCexample() {
    RETCODE error;
    HENV env;          // environment
    HDBC conn;         // database connection

    SQLAllocEnv(&env);
    SQLAllocConnect(env, &conn);
    // open the database connection
    // SQL_NTS denotes that the previous argument is a null-terminated string
    SQLConnect(conn, "db.yale", SQL_NTS, "avi", SQL_NTS, "avipassswd", SQL_NTS);

    {
        char deptname[80];
        float salary;
        int lenOut1, lenOut2;
        HSTMT stmt;

        char * sqlquery = "SELECT dept_name, SUM(salary)"
                          "FROM instructor"
                          "GROUP BY dept_name";
        SQLAllocStmt(conn, &stmt);
        // send SQL command
        error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
        if (error == SQL_SUCCESS) {
            // store attribute values into corresponding C variables
            // SQLBindCol(
                // statement, 
                // position(start from 1), 
                // type( conversion), 
                // variable, 
                // maximum length(for fixed-length types, the value is ignored), 
                // actual length
            // )
            SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1);
            SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0, &lenOut2);
            // fetch result tuples
            while (SQLFetch(stmt) == SQL_SUCCESS) {
                printf(" %s %g\n", deptname, salary);
            }
        }
        SQLFreeStmt(stmt, SQL_DROP);
    }
    SQLDisconnect(conn);
    SQLFreeConnect(conn);
    SQLFreeEnv(env);
}

• 可以创建带参数的 SQL 语句
• 默认，SQL 语句会被看作单个的事务，并且被自动提交。可以用 SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0) 关闭自动提交功能，但此时需要记得用 SQLTransact(conn, SQL_COMMIT) 和 SQLTransact(conn, SQL_ROLLBACK) 手动提交和回滚。
• ODBC 标准定义了一致性等级(conformance levels)：分为核心等级、等级 1 和等级 2。其中后两者都是高级等级——等级 1 支持获取目录中的信息，而等级 2 支持更多功能。

Embedded SQL⚓︎

• 被嵌入 SQL 语句的语言称为宿主语言(host language)，其中的 SQL 语句就是嵌入 SQL(embedded SQl)。用宿主语言编写的程序可使用嵌入 SQL 的语法来访问和更新存储在数据库的数据。
• 嵌入 SQL 程序在编译前需要被一种特殊的预处理器处理，它将嵌入 SQL 请求替换为宿主语言的声明和过程调用，以允许在运行时执行对数据库的访问，然后再由宿主语言的编译器编译预处理后的程序。
• 嵌入 SQL 的形式为：EXEC SQL <embedded SQL statement>
• 具体的嵌入 SQL 语法取决于宿主语言
• 相比动态 SQL 的优势：
  • 可以在预处理的时候捕获 SQL 相关的错误，而动态 SQL 需要在运行时才有机会发现
  • 易于理解
• 但也有以下缺陷：
  • 预处理器创建了新的宿主语言代码，这会给调试带来麻烦
  • 预处理器创建的代码可能与原有代码之间有语法冲突
• 因此大多数系统还是使用动态 SQL

Functions and Procedures⚓︎

前面的章节中已经介绍过一些 SQL 语言自带的函数，而现在我们来了解一下如何用 SQL 语言编写、存储和调用自定义函数和过程。使用自定义的函数和过程有以下好处：

• 可以专门处理特殊的数据类型，比如图像或几何对象等
• 可以在多个应用中复用这些函数或过程，可以减少代码修改量等等

除了用 SQL 定义函数或过程外，还可以用外部的编程语言来实现。

虽然下面介绍的是 SQL 标准下的函数或过程的语法，但是大多数数据库实现采用的都是非标准的语法，这主要是因为在标准出现前，这些数据库系统就已经支持了函数或过程的功能。

Declaration and Invocation⚓︎

下面给出一个函数定义和调用的例子：

-- function declaration
CREATE FUNCTION dept_count(dept_name VARCHAR(20))
    RETURNS INTEGER
    BEGIN
    DECLARE d_count INTEGER;
        SELECT COUNT(*) INTO d_count
        FROM instructor
        WHERE instructor.dept_name = dept_name;
    RETURN d_count;
    END

--- function invocation
SELECT dept_name, budget
FROM department
WHERE dept_count(dept_name) > 12;

此外，SQL 标准还支持将表作为返回结果的函数，这样的函数称为表函数(table functions)，也可以看作是带参数的实体化视图。具体的函数定义和调用如下所示：

-- function declaration
CREATE FUNCTION instructor_of(dept_name VARCHAR(20))
    RETURNS TABLE(
        ID VARCHAR(5),
        name VARCHAR(20),
        dept_name VARCHAR(20),
        salary NUMERIC(8, 2)
    )
    RETURN TABLE(
        SELECT ID, name, dept_name, salary
        FROM instructor
        WHERE instructor.dept_name = instructor_of.dept_name
    );

-- function invocation
SELECT *
FROM TABLE(instructor_of('Finance'));

下面则给出关于过程定义和调用的例子：

-- procedure declaration
CREATE PROCEDURE dept_count_proc(
    IN dept_name VARCHAR(20),
    OUT d_count INTEGER
)
    BEGIN
        SELECT COUNT(*) INTO d_count
        FROM instructor
        WHERE instructor.dept_name = dept_count_proc.dept_name
    END

-- procedure invocation
DECLARE d_count INTEGER;
CALL dept_count_proc('Physics', d_count);

SQL 允许多个参数不同的函数或过程同名，因为 SQL 会同时根据函数 / 过程名以及参数来识别函数 / 过程。

Language Constructs⚓︎

SQL 还支持类似其他编程语言的构造块 (constructs)，称为持久存储模块(persistent storage module, PSM)。有以下常见的构造块：

• 变量的声明和赋值分别用 DECLARE 和 SET 语句表示
• 复合语句 (compound statement)：由多条 SQL 语句构成的语句块
  • 被包裹在 BEGIN 和 END 关键字之间
  • 可以在复合语句内声明局部变量
  • 而被包裹在 BEGIN ATOMIC 和 END 关键字之间的复合语句会被视为单个事务

• 循环：

  -- 1. WHILE statements
  WHILE boolean expression DO
      sequence of statements;
  END WHILE
  
  -- 2. REPEAT statements
  REPEAT
      sequence of statements;
  UNTIL boolean expression
  END REPEAT
  
  -- 3. FOR statements
  DECLARE n INTEGER DEFAULT 0;
  FOR r AS       -- fetch one row at a time
      SELECT budget FROM department
      WHERE dept_name = 'Music'
  DO 
      SET n = n - r.budget
  END FOR
  

  • 循环体内使用 LEAVE 关键字可提前退出循环，而 ITERATE 则忽略当前元组，处理下一个元组。它们类似编程语言的 continue 和 break 语句

• 条件分支：

  IF boolean expression
      THEN statement or compound statement
  ELSE boolean expression
      THEN statement or compound statement
  ELSE statement or compound statement
  END IF
  

  • SQL 还支持 case 语句（除了在 Lec 3 中见过的那个）

• 前面介绍过的过程定义也是构造块

• 异常条件(exception conditions) 和处理函数(handlers)

  例子

  DECLARE out_of_classroom_seats CONDITION
  DECLARE EXIT HANDLER FOR out_of_classroom_seats
  BEGIN
  sequence of statements
  END
  

  • 在 BEGIN 和 END 之间通过执行 SIGNAL out_of_classroom_seats 来手动抛出异常，此时会立即退出 BEGIN...END 块
  • 也可以使用 CONTINUE 语句，要求执行抛出异常的下一条语句，而不是退出
  • 除了使用自定义的条件外，还可以使用预定义的条件，比如 SQLEXCEPTION、SQLWARNING 和 NOT FOUND

External Language Routines⚓︎

但很遗憾，上述介绍的构造块鲜有数据库支持，因此程序员转而使用外部的编程语言：先用其他编程语言（比如 Java、C++ 等）定义函数后，再用 SQL 语句导入外部的过程或函数，比如：

CREATE PROCEDURE dept_count_proc(
    IN dept_name VARCHAR(20),
    OUT count INTEGER
) 
LANGUAGE C
EXTERNAL NAME '/usr/avi/bin/dept_count_proc';

CREATE FUNCTION dept_count(
    dept_name VARCHAR(20)
)
RETURN INTEGER
LANGUAGE C
EXTERNAL NAME '/usr/avi/bin/dept_count';

需要注意的是，在使用外部编程语言定义的函数或过程时，需要考虑如何处理 null 值，同时也要考虑如何处理通信成功 / 失败，异常等情况。

• 在函数 / 过程中设置额外的参数：用 SQLSTATE 类的值存储失败 /c 成功状态，一个用于存储函数返回值的参数，以及一个表明返回结果中是否存在 null 值的指示变量
• 传递指针而不是直接传递值
• 在函数 / 过程声明中额外加一行 PARAMETER STYLE GENERAL 表明函数 / 过程忽视 null 值（不接收也不处理）

由于执行外部定义的函数 / 过程可能会毁坏原有的数据库结构，所以有些数据库系统出于安全考虑，会让外部代码单独放在一个进程中执行，但这样会产生开销较大的进程间通信(interprocess communication)。

而对于一些较为“安全”的语言（比如 Java、C# 等），外部代码可能会放在进程里面的沙箱(sandbox) 执行。这个沙箱允许外部代码访问自己的这块内存空间，但不允许读取或更新沙箱外的进程相关的内存，或者访问文件系统。这样就可以大大降低进程间通信导致的开销。

Triggers⚓︎

触发器(trigger) 是一种系统自动执行的语句，作为对数据库修改的“副作用”。要想定义一个触发器，需要：

• 指定触发器何时执行——这点可以分解为检查触发器的事件(event)，以及执行触发器需满足的条件(condition)
• 指定触发器需要执行的动作(actions)

触发器有以下用途：

• 为我们实现仅靠 SQL 约束语法难以描述的完整性约束
• 对用户的提醒
• 自动化执行某些任务
• ...

CREATE TRIGGER timeslot_check1 AFTER INSERT ON section
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.time_slot_id NOT IN (
    SELECT time_slot_id
    FROM time_slot
))
BEGIN
    ROLLBACK
END;

CREATE TRIGGER timeslot_check2 AFTER DELETE ON timeslot
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN (orow.time_slot_id NOT IN (
        SELECT time_slot_id
        FROM time_slot
    ) 
    AND orow.time_slot_id IN (
        SELECT time_slot_id
        FROM section
    )
)
BEGIN
    ROLLBACK
END;

CREATE TRIGGER credits_earned AFTER UPDATE OF takes ON grade
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL
    AND (orow.grade = 'F' OR orow.grade IS NULL)
BEGIN ATOMIC
    UPDATE student
    SET tot_cred = tot_cred + (
        SELECT credits
        FROM course
        WHERE course.course_id = nrow.course_id
    )
    WHERE student.id = nrow.id;
END;

CREATE TRIGGER setnull BEFORE UPDATE OF takes
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.grade = '')
    SET nrow.grade = NULL;
END;

很多数据库系统还支持其他触发事件，比如用户登录数据库、系统关机、修改系统设置等。

在上述例子中，可以看到触发器既可以在事件发生前执行，也可以在事件发生后执行。一般来说，前者作为一个额外的约束限制，不仅阻止非法行为引起的错误，还要采取补救措施，使语句变得合法。

除了将触发器的动作一行行地应用到表中的每个行记录上，也可以将触发器一次性作用于满足 SQL 的所有行记录上，只要：

• 将 FOR EACH ROW 改为 FOR EACH STATEMENT
• 并且使用 REFERENCING OLD TABLE AS 和 REFERENCING NEW TABLE AS 来创建过渡表

我们还可以决定启用或禁用触发器，相关语法为：ALTER TRIGGER trigger_name DISABLE。

• 有些数据库采用另一种语法：DISABLE TRIGGER trigger_name。

此外，还可以删除触发器：DROP TRIGGER trigger_name。

与函数 / 过程的语法类似，由于很多数据库系统在 SQL 相关标准建立前就广泛使用触发器了，因此几乎每个数据库系统都有自己的触发器语法，它们是互不兼容的（所以这个标准存在的意义是什么呢？）

When Not to Use Triggers⚓︎

实际上，很多看似能够用触发器解决的问题， SQL 标准早已为我们提供了更方便的方法来解决这些问题，所以在以下场景中，没有必要使用触发器：

• 维护实体化视图：现在很多数据库系统都支持自动维护了，因此无需使用触发器手动维护
• 维护数据库的拷贝：理由同上
• 从备份拷贝上加载数据，或备份地点上复制数据库更新：（这里涉及到数据库的备份和故障恢复，之后再讲）

评论区