数据库编程

发表于 Waline： 阅读次数： 本文字数： 5.3k 阅读时长 ≈ 19 分钟

数据库编程概述

数据库编程允许应用程序与数据库管理系统（DBMS）进行交互，执行数据查询、操纵和管理任务。SQL 是关系数据库的标准语言，但在应用程序中直接使用 SQL 有其局限性，因此发展出多种数据库编程方法。

SQL 语言的三种主要使用方式

SQL 语言根据其执行环境和与宿主语言的结合方式，主要有以下三种使用方式：

使用方式 实现方法 应用场景
交互式 SQL（ISQL） 命令行/批处理 可独立运行，一般供临时用户操作或 DBA 管理数据库使用，例如执行即席查询（ad-hoc query）。
嵌入式 SQL（ESQL） 主语言 + ESQL 将 SQL 语句嵌入到高级程序设计语言（如 C, C++, Java）中，用于开发复杂的数据库应用程序。
过程化 SQL（PL/SQL） SQL 编程 扩展了 SQL，使其具备高级程序设计语言的流程控制、变量定义等功能。可在数据库服务器中独立运行，常用于编写存储过程、存储函数、触发器。

数据交换模型

在应用程序与数据库系统交互时，基本的数据交换模型如下：

  1. 应用程序向数据库系统发送 SQL 语句。
  2. 数据库系统执行 SQL 语句。
  3. 数据库系统返回执行状态代码给应用程序。
  4. 如果 SQL 语句是查询，数据库系统还会返回结果数据给应用程序。
graph LR
App[数据库应用] -- SQL语句 --> DBMS[数据库系统]
DBMS -- 返回（执行状态代码） --> App
DBMS -- 返回（结果数据） --> App

嵌入式 SQL

嵌入式 SQL（Embedded SQL, ESQL）是将 SQL 语句直接嵌入到高级程序设计语言（如 C, C++, Java, Python 等）中，利用宿主语言的过程化处理能力来弥补 SQL 非过程化的特性。被嵌入 SQL 的程序设计语言称为宿主语言（Host Language）。

为什么需要嵌入式 SQL？

  1. SQL 的非过程性：标准 SQL 缺乏流程控制（如循环、条件判断）和复杂的计算能力，难以实现复杂的业务逻辑。
  2. 事务处理需求：许多事务处理应用需要结合高级语言的计算能力、用户交互和 SQL 的数据管理能力。

ESQL 的处理过程

由于宿主语言编译器通常不认识 SQL 语句，ESQL 程序需要一个预编译（Precompilation）过程：

graph LR
    %% 定义样式
    classDef process fill:#f9f,stroke:#333,stroke-width:2px
    classDef decision fill:#ccf,stroke:#333,stroke-width:2px
    classDef output fill:#9cf,stroke:#333,stroke-width:2px

    %% 节点定义和样式应用
    A[含 ESQL 语句的主语言程序]:::process --> B{DBMS 预处理程序}:::decision
    B -- 转换 ESQL 为函数调用 --> C[转换后的主语言程序]:::process
    C --> D{主语言编译程序}:::decision
    D -- 编译链接 --> E[目标可执行程序]:::output

    %% 链接文本样式
    linkStyle 1 stroke:#555,stroke-width:2px,color:#555
    linkStyle 3 stroke:#555,stroke-width:2px,color:#555
  1. 预编译器：扫描源代码，找出嵌入的 SQL 语句。
  2. 转换：将 ESQL 语句转换为宿主语言的函数调用（调用 DBMS 提供的运行时库）。
  3. 编译：宿主语言编译器将转换后的代码编译成目标程序。
  4. 链接：与 DBMS 运行时库链接，生成可执行文件。

ESQL 的核心机制

在 ESQL 中，需要解决宿主语言与 SQL 之间通信的几个关键问题：

  1. 区分 SQL 语句与主语言语句：[1]
    • ESQL 语句通常以 EXEC SQL（Execute SQL）开头。
    • 在 C 语言中，ESQL 语句以 EXEC SQL 开头，并以分号 ; 结束。 
      EXEC SQL SELECT Sno, Sname INTO :hsno, :hsname FROM Student WHERE Sage > :hage;

  1. 主语言变量与 SQL 变量的区分：

    • 主变量（Host Variable）：在宿主语言中定义，用于在宿主语言和 SQL 语句之间传递数据。在 SQL 语句中使用时，主变量名前通常加冒号 : 作为前缀。
    • SQL 变量：指数据库中的表名、列名等。 
      1
      2
      3
      4
      5
      6
      // C 语言中声明主变量
      char hsno[10];
      int hage = 20;

      // 在 ESQL 中使用主变量
      EXEC SQL SELECT Sno INTO :hsno FROM Student WHERE Sage > :hage;

  2. 数据交换与通信：

    • SQL 通信区（SQL Communication Area - SQLCA）
    • 主变量（Host Variables）
    • 指示变量（Indicator Variables）
    • 游标（Cursors）

SQL 通信区

SQLCA

SQLCA (SQL Communication Area) 是一个由 DBMS 定义的数据结构，用于在数据库系统和应用程序之间传递 SQL 语句执行后的状态信息。

  • 用途：
    • 反馈 SQL 语句的执行状态（成功、失败、警告、未找到数据等）。
    • 描述系统当前工作状态和运行环境。
  • 定义：在程序中（通常在主变量声明区之前或之内）通过 EXEC SQL INCLUDE SQLCA; 语句引入。
  • 核心成员 SQLCODE：
    • SQLCODE 是 SQLCA 中的一个重要整型变量，用于存放最近执行的 SQL 语句的状态码。
    • SQLCODE == 0（或预定义常量 SUCCESS）：表示语句成功执行。
    • SQLCODE > 0（通常 SQLCODE == 100 或 NOT FOUND）：表示未找到数据或查询结果为空。
    • SQLCODE < 0：表示发生错误。
    • 应用程序应在每条 EXEC SQL 语句执行后检查 SQLCODE 的值。

主变量

主变量（Host Variables）是在宿主语言中声明的变量，用于：

  • 输入主变量: 由应用程序赋值，在 SQL 语句中被引用（如 WHERE 子句的条件值，INSERT 语句的插入值）。
  • 输出主变量: 由 SQL 语句赋值（如 SELECT ... INTO ... 子句的结果），返回给应用程序。

声明主变量:
主变量必须在使用前在特定的声明段中声明。在 C 语言中，这通常是 EXEC SQL BEGIN DECLARE SECTION; 和 EXEC SQL END DECLARE SECTION; 之间。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXEC SQL BEGIN DECLARE SECTION;
    char sno_var[10];
    char sname_var[30];
    int  sage_var;
    char givensno_var[10] = "2023001"; // 输入主变量，已赋值
EXEC SQL END DECLARE SECTION;

// ... 宿主语言代码 ...

// 使用主变量
EXEC SQL SELECT Sname, Sage
    INTO :sname_var, :sage_var
    FROM Student
    WHERE Sno = :givensno_var;
  • 作用：
    1. 预编译时进行类型检查，确保主变量类型与数据库列类型兼容。
    2. 为从数据库接收数据预先分配内存。
  • 类型转换：DBMS 通常会自动进行宿主语言类型和 SQL 类型之间的合理转换。例如，C 的 int 和 SQL 的 INTEGER。对于字符类型，C 语言字符串通常以 \0 结尾，而数据库中的 CHAR 或 VARCHAR 类型有固定或可变长度，不一定有 \0。因此，声明 C 字符串主变量时，长度通常要比数据库列定义的最大长度多一个字节以容纳 \0。

指示变量

指示变量

指示变量（Indicator Variable）是一个与主变量配对使用的整型变量，主要用于处理 SQL 中的 NULL 值或检测数据截断。

  • 用途：

    1. 处理 NULL 值：
      • 向数据库插入 NULL：如果一个主变量对应的指示变量被设为负值（通常是 -1），则表示该主变量的值应被视作 NULL。
      • 从数据库读取 NULL：如果数据库返回的列值为 NULL，则其对应主变量的指示变量会被设为负值。
    2. 检测数据截断：如果从数据库检索的字符串值长度超过了主变量的容量，指示变量会被设为一个正值，表示发生了截断（该正值通常是字符串的原始长度）。

  • 声明与使用：指示变量在主变量声明区声明，类型通常为 short 或 int。在 ESQL 语句中，指示变量紧跟在其对应的主变量之后，可以用空格或关键字 INDICATOR 分隔。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    EXEC SQL BEGIN DECLARE SECTION;
        char  sname_var[20];
        short sname_ind; // sname_var 的指示变量
        int   grade_var;
        short grade_ind; // grade_var 的指示变量
    EXEC SQL END DECLARE SECTION;

    // 示例 1: 查询，可能返回 NULL
    EXEC SQL SELECT Sname, Grade
        INTO :sname_var :sname_ind, :grade_var :grade_ind
        FROM Student WHERE Sno = '123';

    if (sname_ind < 0) { /* Sname 为 NULL */ }
    if (grade_ind < 0) { /* Grade 为 NULL */ }
    if (sname_ind > 0) { /* Sname 被截断，sname_ind 是原始长度 */ }

    // 示例 2: 更新，将 Grade 设置为 NULL
    grade_ind = -1; // 表明 :grade_var 的值是 NULL
    EXEC SQL UPDATE Student
        SET Grade = :grade_var :grade_ind
        WHERE Sno = '123';

  • 指示变量的值：

    • 0：成功赋值，值非 NULL。
    • -1（或其他负值）：值为 NULL。
    • >0：字符串数据被截断，该值通常是原始字符串长度。

游标

游标

游标（Cursor）是一个数据库对象，它允许应用程序对 SQL 查询返回的多行结果集进行逐行处理。SQL 是面向集合的，而宿主语言通常是面向记录的，游标充当了两者之间的桥梁，实现了「一次一行」的数据访问原则。

为什么需要游标？

  • SELECT ... INTO ... 语句只能处理返回单行结果的查询。如果查询可能返回多行，使用 INTO 会导致错误。
  • 游标提供了一种机制来遍历多行结果集。

游标操作流程：

  1. 声明游标（DECLARE CURSOR）：定义游标及其关联的 SELECT 语句。这只是一个定义，不执行查询。 
    1
    2
    EXEC SQL DECLARE student_cursor CURSOR FOR
        SELECT Sno, Sname FROM Student WHERE Sdept = :dept_var;
    • FOR READ ONLY：声明游标是只读的。
    • FOR UPDATE [OF column_list]：声明游标是可更新的，可以用于后续的 UPDATE ... WHERE CURRENT OF 或 DELETE ... WHERE CURRENT OF 语句。
  2. 打开游标（OPEN CURSOR）：执行游标声明中定义的 SELECT 语句，将结果集装入一个临时工作区（缓冲区）。此时，游标指向结果集的第一行之前。 
    EXEC SQL OPEN student_cursor;
  3. 提取数据（FETCH CURSOR）：从游标的当前位置检索一行数据，并将其存入指定的主变量中。同时，游标前移到下一行。通常在一个循环中执行 FETCH，直到 SQLCODE 指示没有更多数据（如 SQLCODE == 100）。 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    EXEC SQL BEGIN DECLARE SECTION;
        char sno_val[10], sname_val[30];
    EXEC SQL END DECLARE SECTION;
    // ...
    while (1) {
        EXEC SQL FETCH student_cursor INTO :sno_val, :sname_val;
        if (SQLCODE != 0) { // 或检查 SQLCA.SQLCODE
            break; // 没有更多行或发生错误
        }
        // 处理 :sno_val 和 :sname_val
    }
  4. 关闭游标（CLOSE CURSOR）：释放游标占用的资源（如结果集缓冲区）。关闭后，游标不再可用，除非重新打开。 
    EXEC SQL CLOSE student_cursor;

可滚动游标（Scrollable Cursors）：标准 SQL 允许定义可滚动游标，允许 FETCH 操作不仅向前移动，还可以向后、到第一行、到最后一行或到绝对/相对位置。声明时使用 SCROLL 关键字。FETCH 语句可以使用 NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n 等选项。

1
2
EXEC SQL DECLARE emp_scroll_cursor SCROLL CURSOR FOR SELECT ...;
EXEC SQL FETCH PRIOR emp_scroll_cursor INTO ...;

数据库连接

  • 建立连接（CONNECT）： 
    1
    2
    3
    EXEC SQL CONNECT TO target [AS connection_name] [USER user_name];
    // target 可以是 "dbname@hostname:port", "DEFAULT", 或 SQL 字符串常量
    // user_name 可以是 "username/password"
  • 设置当前连接（SET CONNECTION）：如果程序管理多个连接。 
    EXEC SQL SET CONNECTION connection_name | DEFAULT;
  • 断开连接（DISCONNECT）： 
    EXEC SQL DISCONNECT [connection_name | CURRENT | ALL | DEFAULT];

ESQL 语句分类

  1. 不用游标的 SQL 语句：

    • 说明性语句：DECLARE SECTION, INCLUDE SQLCA。
    • 数据定义语句（DDL）：CREATE TABLE, DROP TABLE 等（某些 DBMS 可能限制在 ESQL 中使用 DDL）。
    • 数据控制语句（DCL）：GRANT, REVOKE。
    • 查询结果为单记录的 SELECT ... INTO ... 语句。
    • 非 CURRENT OF 形式的 INSERT, UPDATE, DELETE 语句。 
      1
      2
      3
      4
      5
      6
      7
      // 单行查询
      EXEC SQL SELECT Sname, Sage INTO :sname_var, :sage_var
          FROM Student WHERE Sno = :sno_var;

      // 更新
      EXEC SQL UPDATE Student SET Sage = :new_age_var
          WHERE Sno = :sno_var;

  2. 必须使用游标的 SQL 语句：

    • 查询结果为多条记录的 SELECT 语句（通过 DECLARE CURSOR, OPEN, FETCH, CLOSE 处理）。
    • UPDATE ... WHERE CURRENT OF <游标名>：修改游标当前指向的行。
    • DELETE ... WHERE CURRENT OF <游标名>：删除游标当前指向的行。 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      // 声明可更新游标
      EXEC SQL DECLARE upd_cursor CURSOR FOR
          SELECT Sage FROM Student WHERE Sdept = 'CS' FOR UPDATE OF Sage;
      // ... OPEN upd_cursor ...
      // ... FETCH upd_cursor INTO :current_age ...
      if (current_age < 20) {
          EXEC SQL UPDATE Student SET Sage = 20
              WHERE CURRENT OF upd_cursor;
      }
      // ... CLOSE upd_cursor ...

不可更新游标

如果游标定义的 SELECT 语句包含 UNION, ORDER BY（某些 DBMS）, DISTINCT, 或聚合函数等，通常该游标是只读的，不能用于 WHERE CURRENT OF 子句。这相当于定义了一个不可更新的视图。

异常处理

WHENEVER 语句用于定义一个全局规则，指定当某种 SQL 执行条件（异常）发生时，程序应采取的默认动作。预编译器会在每条 ESQL 语句后自动插入检查代码。

语法：

EXEC SQL WHENEVER <condition> <action>;

  • <condition>：

    • SQLERROR：发生严重错误（SQLCODE < 0）。
    • NOT FOUND：未找到数据或结果集结束（SQLCODE == 100 或特定正值）。
    • SQLWARNING：发生警告（如数据截断，SQLCODE 为特定正值）。

  • <action>：

    • CONTINUE：忽略异常，继续执行下一条语句。
    • GOTO <label>（或 GO TO <label>）：跳转到宿主语言中的指定标签处。
    • STOP：终止程序执行（可能伴随回滚和断开连接）。
    • DO <function_call>：调用一个宿主语言函数（不常用，且支持程度不一）。
    • BREAK/CONTINUE（在循环中）：改变循环的执行路径（C 语言中不直接支持，通常用 GOTO 模拟）。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    EXEC SQL WHENEVER SQLERROR GOTO handle_error;
    EXEC SQL WHENEVER NOT FOUND GOTO end_of_data;
    // ... ESQL statements ...
    // ...
    return 0; // 正常结束

    handle_error:
        printf("SQL Error: %ld\n", SQLCA.SQLCODE); // 或 sqlca.sqlcode
        EXEC SQL ROLLBACK;
        EXEC SQL DISCONNECT;
        return -1;

    end_of_data:
        printf("No more data or data not found.\n");
        // 通常在这里关闭游标等
        return 0; // 或继续其他逻辑

    WHENEVER 语句的作用域从其在代码中出现的位置开始，直到文件结束或遇到另一个相同条件的 WHENEVER 语句。

动态 SQL

静态 ESQL 的 SQL 语句在预编译时就已确定。动态 SQL 允许 SQL 语句在程序运行时才构建和执行。

为什么需要动态 SQL？

  • SQL 语句本身在运行时才能确定（例如，用户通过界面构建查询）。
  • 查询的条件、涉及的表或列在运行时才确定。
  • 希望根据数据库当时的统计信息优化查询。

主要语句：

  1. EXECUTE IMMEDIATE <string_host_variable>：直接执行存放在字符串主变量中的 SQL 语句。适用于不返回结果集或不含动态参数（?）的语句。 
    1
    2
    3
    4
    5
    EXEC SQL BEGIN DECLARE SECTION;
        char sql_stmt_str[200];
    EXEC SQL END DECLARE SECTION;
    sprintf(sql_stmt_str, "DELETE FROM Student WHERE Sno = '%s'", user_input_sno);
    EXEC SQL EXECUTE IMMEDIATE :sql_stmt_str;
  2. PREPARE ... FROM ... 和 EXECUTE ... USING ...：用于执行包含动态参数（占位符 ?）或可能返回结果集的 SQL 语句。
    • PREPARE <stmt_name> FROM <string_host_variable>：预编译字符串中的 SQL 语句，并为其分配一个语句名。
    • EXECUTE <stmt_name> [INTO :host_vars] [USING :host_vars_for_params]：执行预编译的语句。USING 子句提供参数值，INTO 子句接收结果（如果是 SELECT）。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    EXEC SQL BEGIN DECLARE SECTION;
        char sql_query_str[200] = "SELECT Sname FROM Student WHERE Sage > ? AND Sdept = ?";
        char sname_result[30];
        int  age_param;
        char dept_param[10];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL PREPARE my_query FROM :sql_query_str;

    age_param = 20;
    strcpy(dept_param, "CS");
    EXEC SQL EXECUTE my_query INTO :sname_result USING :age_param, :dept_param;

    // 如果 my_query 是 SELECT 且可能返回多行，则需要结合游标使用 PREPARE
    EXEC SQL DECLARE dyn_cursor CURSOR FOR my_query;
    EXEC SQL OPEN dyn_cursor USING :age_param, :dept_param;
    ... FETCH ...
    EXEC SQL CLOSE dyn_cursor;

优缺点：

  • 优点：灵活性高，可以根据运行时数据库状态选择最优访问路径。
  • 缺点：预编译时无法进行完整的语法检查和优化，执行时需要额外的语法分析和路径选择开销，可能影响性能。安全性风险（SQL 注入）也需要特别注意，应避免直接拼接用户输入到 SQL 字符串中，优先使用参数化查询。

ESQL 总结

特性/问题 ESQL 解决方案
主语言与 SQL 语句区别 EXEC SQL ... ; 前缀/后缀
主语言与 ESQL 通信 SQLCA（状态），主变量（数据/参数），游标（多行结果）
主变量与 SQL 变量区别 主变量在 SQL 中用 : 前缀；SQL 变量指表名、列名等
单条结果元组数据交换 SELECT ... INTO ...
多条结果元组数据交换 游标（DECLARE, OPEN, FETCH, CLOSE）
处理 NULL 值 指示变量
异常处理 WHENEVER condition action;
运行时确定 SQL 动态 SQL（EXECUTE IMMEDIATE, PREPARE/EXECUTE）

过程化 SQL

过程化 SQL（Procedural SQL）是对标准 SQL 的扩展，增加了程序设计语言的特性，如变量定义、流程控制（条件、循环）、异常处理等。这使得开发者可以直接使用增强的 SQL 语言在数据库服务器内部编写复杂的逻辑单元，如存储过程、函数和触发器。

常见的过程化 SQL 方言包括 Oracle 的 PL/SQL (Procedural Language/SQL) 和 Microsoft SQL Server 的 T-SQL (Transact-SQL)。

过程化 SQL 的优势：

  • 独立编程：不再需要宿主语言和预编译过程。
  • 服务器端执行：逻辑在数据库服务器内部执行，减少了客户端与服务器之间的数据传输和网络通信开销。
  • 模块化与封装：可以将业务逻辑封装在存储过程和函数中，便于管理和重用。
  • 性能提升：编译后的过程化 SQL 代码存储在数据库中，执行效率较高。

过程化 SQL 的基本结构：块

过程化 SQL 程序通常以块（Block）为基本单位。一个典型的块结构（以 PL/SQL 为例）包含：

1
2
3
4
5
6
7
8
9
10
11
12
[DECLARE] -- 可选的声明部分
    -- 变量、常量、游标、自定义类型、异常等的声明
BEGIN     -- 必须的执行部分
    -- SQL 语句
    -- 过程化 SQL 语句（流程控制、赋值等）
[EXCEPTION] -- 可选的异常处理部分
    -- WHEN exception1 THEN
    --   处理语句
    -- WHEN OTHERS THEN
    --   处理语句
END;      -- 必须的结束标志
/         -- （在某些工具中，如 SQL*Plus, 表示块结束并执行）
  • 块可以嵌套。
  • 每个块完成一个逻辑操作。

变量和常量

  • 定义：在 DECLARE 部分定义。 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- PL/SQL 示例
    DECLARE
        v_student_name VARCHAR2(50);
        v_age          NUMBER(3) NOT NULL := 20; -- 带默认值且非空
        C_PI           CONSTANT NUMBER := 3.14159;
    BEGIN
        v_student_name := 'Alice'; -- 赋值语句
        -- ...
    END;
    /
  • 数据类型：
    • 标准 SQL 数据类型（NUMBER, VARCHAR2, CHAR, DATE, BOOLEAN 等）。
    • %TYPE：使变量的数据类型与某个已存在的表列或另一个变量的类型相同。 
      1
      2
      3
      DECLARE
          v_sno student.sno%TYPE; -- v_sno 的类型与 student 表的 sno 列相同
          v_sname v_sno%TYPE;     -- v_sname 的类型与 v_sno 相同
    • %ROWTYPE：定义一个记录类型变量，其结构与某个表或游标的行结构相同。 
      1
      2
      3
      4
      5
      6
      7
      DECLARE
          r_student student%ROWTYPE; -- r_student 是一个包含 student 表所有列的记录
      BEGIN
          SELECT * INTO r_student FROM student WHERE sno = '1001';
          DBMS_OUTPUT.PUT_LINE('Name: ' || r_student.sname || ', Dept: ' || r_student.dept);
      END;
      /
  • 作用域：变量通常具有局部作用域（在其声明的块内有效）。

流程控制

条件控制

1
2
3
4
5
6
7
8
-- PL/SQL 示例
IF condition1 THEN
    statements1;
ELSIF condition2 THEN
    statements2;
ELSE
    statements3;
END IF;

循环控制

  1. 简单循环（LOOP） 
    1
    2
    3
    4
    5
    -- PL/SQL 示例
    LOOP
        statements;
        EXIT WHEN condition; -- 退出循环的条件
    END LOOP;
  2. WHILE 循环 
    1
    2
    3
    4
    -- PL/SQL 示例
    WHILE condition LOOP
        statements;
    END LOOP;
  3. FOR 循环（遍历整数范围或游标） 
    1
    2
    3
    4
    5
    6
    7
    8
    -- PL/SQL 示例（整数范围）
    FOR i IN 1..10 LOOP
        statements; -- i 自动递增
    END LOOP;

    FOR i IN REVERSE 1..10 LOOP
        statements; -- i 自动递减
    END LOOP;

游标在过程化 SQL 中

游标在过程化 SQL 中的使用方式与 ESQL 类似，但语法嵌入在过程化 SQL 块中。

  • 声明：在 DECLARE 部分。
  • 操作：OPEN, FETCH ... INTO ..., CLOSE 在 BEGIN...END 块中。
  • 游标属性（PL/SQL）：
    • cursor_name%ISOPEN：布尔值，游标是否打开。
    • cursor_name%FOUND：布尔值，最近的 FETCH 是否成功返回一行。
    • cursor_name%NOTFOUND：布尔值，cursor_name%FOUND 的反面。
    • cursor_name%ROWCOUNT：数字，到目前为止 FETCH 返回的行数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- PL/SQL 示例
DECLARE
    v_sno student.sno%TYPE;
    v_sname student.sname%TYPE;
    CURSOR c_students IS SELECT sno, sname FROM student WHERE dept = 'CS';
BEGIN
    OPEN c_students;
    LOOP
        FETCH c_students INTO v_sno, v_sname;
        EXIT WHEN c_students%NOTFOUND; -- 检查是否还有数据
        DBMS_OUTPUT.PUT_LINE(v_sno || ': ' || v_sname);
    END LOOP;
    CLOSE c_students;
END;
/
  • 隐式游标：对于非 SELECT 语句（如 INSERT, UPDATE, DELETE）以及返回单行的 SELECT ... INTO ... 语句，PL/SQL 会自动使用一个名为 SQL 的隐式游标。可以通过 SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN（总是 FALSE）来获取这些语句的执行状态。

T-SQL 要点

T-SQL (Transact-SQL) 是 Microsoft SQL Server 的过程化 SQL 方言。虽然核心概念相似，但语法细节有所不同。

  • 批处理（Batch）：一组 T-SQL 语句，作为一个单元发送给 SQL Server 执行。用 GO 命令分隔批处理（GO 不是 T-SQL 语句，是客户端工具如 SSMS 的命令）。
  • 变量声明与赋值：
    • 局部变量以 @ 开头，用 DECLARE 声明。
    • 赋值用 SET @variable = value 或 SELECT @variable = expression [FROM ...]。
    1
    2
    3
    4
    5
    -- T-SQL 示例
    DECLARE @MyName VARCHAR(50), @Age INT;
    SET @MyName = 'Bob';
    SELECT @Age = 25;
    PRINT 'Name: ' + @MyName + ', Age: ' + CAST(@Age AS VARCHAR);
  • 全局变量：以 @@ 开头，是系统定义的，如 @@ROWCOUNT (受上一语句影响的行数), @@ERROR (上一语句的错误号)。
  • 流程控制：
    • BEGIN ... END: 定义语句块。
    • IF ... ELSE ...: 条件控制。
    • CASE ... WHEN ... THEN ... ELSE ... END: 多分支选择。
    • WHILE condition BEGIN ... END: 循环。BREAK 退出循环，CONTINUE 进入下一次迭代。
    • GOTO label: 跳转。
    • WAITFOR DELAY 'time_to_wait' 或 WAITFOR TIME 'time_to_execute': 延迟执行。
    • RETURN [integer_expression]: 从查询或过程中无条件退出。
  • 错误处理：TRY ... CATCH 结构。 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- T-SQL 示例
    BEGIN TRY
        -- 可能出错的 T-SQL 语句
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred:';
        PRINT ERROR_MESSAGE(); -- 获取错误信息
    END CATCH;

存储过程和函数

存储过程（Stored Procedure）和存储函数（Stored Function，也称用户定义函数 UDF）是用过程化 SQL 编写的、编译后存储在数据库服务器中的命名程序单元。

匿名块 vs. 命名块：

  • 匿名块：没有名称，每次执行时都需要重新编译（如直接在 SQL*Plus 或 SSMS 中执行的 DECLARE...BEGIN...END; 块）。
  • 命名块：即存储过程和函数。它们被编译一次并存储在数据库中，可以被反复调用，运行速度较快。

存储过程

一段为完成特定功能而编写的 PL/SQL 或 T-SQL 程序，经编译后存储在数据库中。

  • 创建（PL/SQL）： 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE [OR REPLACE] PROCEDURE procedure_name
        [(parameter1 [MODE] datatype [DEFAULT value], ...)]
    IS -- 或 AS
        -- 声明部分（变量, 游标等）
    BEGIN
        -- 执行部分
    [EXCEPTION]
        -- 异常处理部分
    END [procedure_name];
    /
    • MODE: 参数模式，可以是 IN (默认，输入), OUT (输出), IN OUT (输入输出)。
  • 执行（PL/SQL）： 
    1
    2
    3
    EXECUTE procedure_name(arguments);
    -- 或者在 PL/SQL 块中直接调用:
    -- procedure_name(arguments);
  • 修改/删除（PL/SQL）：
    • ALTER PROCEDURE procedure_name COMPILE;（重新编译）
    • DROP PROCEDURE procedure_name;（删除）

存储函数

与存储过程类似，但必须返回一个值。

  • 创建（PL/SQL）： 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE [OR REPLACE] FUNCTION function_name
        [(parameter1 [MODE] datatype [DEFAULT value], ...)]
    RETURN return_datatype -- 必须指定返回类型
    IS -- 或 AS
        -- 声明部分
    BEGIN
        -- 执行部分（必须包含 RETURN 语句返回值）
    [EXCEPTION]
        -- 异常处理部分
    END [function_name];
    /
  • 执行（PL/SQL）：函数通常在 SQL 表达式中调用，或赋值给变量。 
    1
    2
    3
    4
    -- 在 SELECT 语句中
    SELECT function_name(column_value) FROM my_table;
    -- 赋值给变量
    v_result := function_name(argument);

存储过程 vs. 函数

特性 存储过程 存储函数
返回值 本身无返回值，通过 OUT 或 IN OUT 参数返回 必须有一个返回值，通过 RETURN 语句指定
调用方式 通常独立执行（EXECUTE, CALL） 主要在 SQL 表达式中使用，或赋值给变量
用途 执行操作、复杂业务逻辑、数据修改 计算、返回值，通常不应有副作用（如修改数据）

优点

  1. 运行效率高：预编译，存储在数据库中。
  2. 降低网络开销：客户端只需发送调用请求和参数，大量 SQL 和逻辑在服务器端执行。
  3. 模块化和代码重用：封装业务逻辑，便于维护和统一管理。
  4. 安全性：可以授予用户执行存储过程/函数的权限，而不直接授予对基表的权限。

PL/SQL 存储过程示例：转账

假设有一个 Accounts 表（account_num VARCHAR2(20) PRIMARY KEY, balance NUMBER）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE PROCEDURE transfer_funds (
    p_from_account IN VARCHAR2,
    p_to_account   IN VARCHAR2,
    p_amount       IN NUMBER
) IS
    v_from_balance NUMBER;
    v_to_balance   NUMBER;
BEGIN
    -- 检查转出账户余额
    SELECT balance INTO v_from_balance FROM Accounts WHERE account_num = p_from_account FOR UPDATE;
    IF v_from_balance < p_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds in account ' || p_from_account);
    END IF;

    -- 检查转入账户是否存在
    SELECT balance INTO v_to_balance FROM Accounts WHERE account_num = p_to_account FOR UPDATE;
    IF SQL%NOTFOUND THEN -- 隐式游标判断
         RAISE_APPLICATION_ERROR(-20002, 'Recipient account ' || p_to_account || ' does not exist.');
    END IF;

    -- 执行转账
    UPDATE Accounts SET balance = balance - p_amount WHERE account_num = p_from_account;
    UPDATE Accounts SET balance = balance + p_amount WHERE account_num = p_to_account;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transfer successful.');
EXCEPTION
    WHEN NO_DATA_FOUND THEN -- 特指 SELECT INTO 未找到数据
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: One of the accounts not found.');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error during transfer: ' || SQLERRM);
        RAISE; -- 重新抛出异常
END transfer_funds;
/

-- 调用
BEGIN transfer_funds('ACC001', 'ACC002', 100); END;
/

基于驱动的数据库编程

除了嵌入式 SQL 和过程化 SQL，现代应用程序更常使用数据库驱动程序（Database Driver）和 API 来与数据库交互。这种方式提供了更高层次的抽象和数据库无关性。

核心思想

应用程序通过一个标准的 API 与数据库驱动程序通信，驱动程序再负责与具体的 DBMS 进行交互。

graph LR
    App[应用程序] -- 调用 --> API[数据库访问 API];
    API -- 通过 --> Driver[数据库驱动程序];
    Driver -- 特定协议 --> DBMS[数据库系统];

常见技术

  1. ODBC(Open Database Connectivity)：

    • 由微软提出，是一个 C 语言级别的 API 标准，旨在提供一种统一的方式访问不同的数据库。
    • 体系结构： 
      graph LR
    A[应用程序] --> B[ODBC 驱动管理器] --> C[特定数据库的 ODBC 驱动] --> D[数据库]
    • 工作流程：
      1. 配置数据源（DSN - Data Source Name）。
      2. 分配环境句柄（SQLAllocHandle SQL_HANDLE_ENV）。
      3. 分配连接句柄（SQLAllocHandle SQL_HANDLE_DBC）。
      4. 连接数据库（SQLConnect 或 SQLDriverConnect）。
      5. 分配语句句柄（SQLAllocHandle SQL_HANDLE_STMT）。
      6. 执行 SQL (SQLExecDirect 或 SQLPrepare/SQLExecute)。
      7. 处理结果（如 SQLFetch, SQLGetData）。
      8. 释放句柄（SQLFreeHandle），断开连接（SQLDisconnect）。

  2. JDBC(Java Database Connectivity)：

    • Java 语言访问数据库的标准 API，由一组 Java 类和接口组成。
    • 体系结构： 
      graph LR
    A[Java 应用] --> B[JDBC API] --> C[JDBC 驱动管理器] --> D[JDBC 驱动] --> E[数据库]
    • JDBC 驱动类型：
      1. JDBC-ODBC 桥（依赖 ODBC）。
      2. Native-API 驱动（部分 Java，部分本地代码）。
      3. 网络协议驱动（纯 Java，通过中间件）。
      4. 数据库协议驱动（纯 Java，直接与数据库通信，最常用）。
    • 核心接口/类：DriverManager, Connection, Statement, PreparedStatement, CallableStatement, ResultSet.
    • 基本流程：
      1. 加载驱动 (Class.forName())。
      2. 建立连接 (DriverManager.getConnection())。
      3. 创建语句对象 (Connection.createStatement() 或 prepareStatement())。
      4. 执行 SQL (Statement.executeQuery() 或 executeUpdate())。
      5. 处理结果集（ResultSet）。
      6. 关闭资源（ResultSet, Statement, Connection，通常在 finally 块中）。

  3. Python DB-API：

    • Python 访问数据库的规范（PEP 249），定义了一套标准的 API 模块应如何工作。
    • 具体数据库需要相应的 DB-API 兼容模块（如 PostgreSQL 的 psycopg2、MySQL 的 mysql.connector、SQLite 有内置的 sqlite3）。
    • 核心对象：Connection 对象, Cursor 对象。
    • 基本流程：
      1. 导入数据库模块。
      2. 建立连接（module.connect()）。
      3. 获取游标对象（Connection.cursor()）。
      4. 执行 SQL（Cursor.execute()）或 executemany())。
      5. 获取结果（Cursor.fetchone()） fetchall(), fetchmany())。
      6. 提交事务（Connection.commit()）或回滚（Connection.rollback()）。
      7. 关闭游标（Cursor.close()）和连接（Connection.close()）。

Web 数据库应用中的数据交换

在 Web 应用中，用户通过浏览器与 Web 服务器交互，Web 服务器再通过数据库中间件（驱动程序）与数据库服务器交互。

graph TD
    %% 定义节点样式
    classDef browser fill:#4CAF50,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef webServer fill:#FF9800,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef appLogic fill:#03A9F4,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef dbMiddleware fill:#9C27B0,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef dbServer fill:#E91E63,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;

    %% 节点定义
    Browser[浏览器]:::browser -- "HTTP 请求" --> WebServer[Web 服务器<br>（如 Apache, Nginx）]:::webServer;
    WebServer -- "处理脚本<br>（PHP, Python, Java）" --> AppLogic[应用程序逻辑/脚本<br>（PHP, Flask, Servlet）]:::appLogic;
    AppLogic -- "使用 DB API/Driver" --> DBMiddleware[数据库中间件/驱动程序<br>（PDO, JDBC, DB-API）]:::dbMiddleware;
    DBMiddleware -- "网络协议" --> DBServer[数据库服务器<br>（MySQL, PostgreSQL, etc.）]:::dbServer;
    DBServer -- "网络协议" --> DBMiddleware;
    DBMiddleware -- "结果" --> AppLogic;
    AppLogic -- "生成 HTML" --> WebServer;
    WebServer -- "HTTP 响应<br>（HTML）" --> Browser;
  • Web 服务器中的应用程序逻辑（如 PHP 脚本、Java Servlet/JSP、Python Flask/Django 应用）使用相应的数据库 API（如 PHP 的 PDO、Java 的 JDBC、Python 的 Python DB-API）来访问数据库。

这些基于驱动的方法通常比 ESQL 更灵活、更易于移植，并且与现代编程语言和框架集成得更好。

  1. 为了获得更好的语法高亮，大部分 ESQL 用的语言代码是 sql 而非 c。下面的 PL/SQL 用的是 pgsql，T-SQL 用的是 sql（因为 tsql 要额外的 highlightjs-tsql）。 ↩︎