Day28-16｜6.4.2 嵌入 SQL 访问接口：让宿主程序与数据库操作协同工作

本文对应《系统架构设计师教程（第2版）》第6章“数据库设计基础知识”6.4.2“嵌入 SQL 访问接口”。嵌入 SQL 是在 C、Java、COBOL 等宿主语言程序中直接书写 SQL 语句，借助预编译器、驱动或运行时库完成宿主变量与数据库数据之间的交互。它把业务控制流程交给宿主语言，把集合查询和数据操作交给数据库，是传统企业应用和部分高性能系统常见的数据库访问方式。系统架构师需要理解其执行机制、接口边界、事务协同和演进风险。

一、学习目标与考试定位

完成本节后，应能够说明嵌入 SQL 的基本概念和访问流程；区分静态 SQL 与动态 SQL，理解宿主变量、指示变量、游标、预编译和绑定；能够设计事务、异常、批量和资源释放；能够分析 SQL 与程序耦合、动态 SQL 注入、游标泄漏、空值处理和模式变更风险；能够在案例题和论文中说明嵌入 SQL 如何满足性能、安全、可维护和可移植要求。

软考题目常问“为什么先预编译”“宿主变量如何传递”“查询多行记录如何处理”“动态 SQL 有哪些风险”。案例题则会给出程序直接拼接 SQL、事务提交时机错误、游标未关闭、数据库字段变更导致编译失败等情境。答题要覆盖预编译、参数绑定、结果处理、事务边界、错误码和资源释放。

二、嵌入 SQL 的基本思想

宿主语言负责循环、分支、用户交互、调用外部服务和业务编排，SQL 负责集合查询、插入、更新、删除和数据库事务。两者通过宿主变量交换数据，并由预编译器或运行时接口转换成可执行程序。

嵌入 SQL 的优势是语句与程序逻辑结合紧密，静态语句可以提前检查和优化，适合事务型应用；缺点是数据库方言、表结构和类型会与程序形成耦合，模式变化可能需要重新预编译或修改代码，跨数据库迁移成本较高。

合理的架构应把嵌入 SQL 集中在数据访问层，避免 SQL 散落在业务代码、控制器和脚本中。访问层封装连接、事务、参数、错误、日志和映射，向上层提供面向业务的接口。

三、嵌入 SQL 的典型流程

传统嵌入 SQL 通常经过以下步骤：在宿主程序中编写嵌入语句；预编译器识别 SQL 并生成宿主语言调用代码；编译器编译宿主程序；链接数据库运行时库；运行时建立连接、执行语句、返回结果并处理事务。

现代语言中可能没有独立预编译器，而由驱动、框架或注解处理器完成类似工作，但核心思想仍然是把 SQL 结构与宿主变量映射起来。

可以用下面的链条记忆：

编写嵌入语句 → 预编译/解析 → 宿主编译链接 → 绑定变量 → 执行并读取结果 → 提交/回滚 → 释放资源。

四、静态 SQL

静态 SQL 的语句结构在编译或预编译阶段基本确定，只在运行时提供参数值。它适合结构稳定、执行频繁和安全要求高的操作。由于语句结构固定，数据库可以复用解析和执行计划，预编译阶段还可能发现语法、类型和部分对象错误。

静态 SQL 的缺点是灵活性有限，查询条件、排序字段和表结构变化时可能需要修改程序和重新构建；不同数据库的类型、函数、异常机制和方言也可能造成可移植性问题。

静态 SQL 仍然需要参数绑定，不能因为结构固定就把用户输入拼接到字符串中。程序要明确 NULL、日期、金额和字符集的转换，避免宿主语言默认类型与数据库类型不一致。

五、动态 SQL

动态 SQL 在运行时决定部分或全部语句结构，适合条件、表、列和排序方式变化较多的场景。例如通用查询、报表筛选和可配置字段。动态 SQL 可以通过准备、绑定、执行和释放完成。

动态 SQL 最大的风险是安全和计划稳定性。用户输入直接拼接会造成 SQL 注入；动态表名和列名不能用普通值参数绑定，必须通过白名单映射；不同语句结构可能导致解析开销和执行计划波动。动态 SQL 也增加测试组合和审计难度。

使用动态 SQL 时，值参数必须参数化，结构参数采用白名单；限制可查询列、排序方向、分页和条件数量；记录 SQL 模板和调用方，脱敏参数；对高频语句设置上限和超时，并用代表性条件验证计划和性能。

六、宿主变量与指示变量

宿主变量是宿主程序与 SQL 之间传递数据的变量。输入宿主变量把业务参数传给 SQL，输出宿主变量接收查询或过程的结果，输入输出变量可以双向传递。变量类型、长度、精度、字符集和时区需要与数据库字段匹配。

由于 SQL 的 NULL 不是普通值，嵌入 SQL 常使用指示变量表示值是否为空、是否截断或是否发生特殊状态。程序读取可空字段时必须检查指示变量，不能把 NULL 当成空字符串、零或 false。写入时也要明确是设置 NULL 还是设置默认值。

金额、时间和大对象尤其容易出错。浮点类型可能产生精度误差，时间需要明确时区和夏令时，长文本和二进制数据要采用流式或分块处理，不能盲目放进固定长度变量。

七、游标与多行结果

单行查询可以直接把结果放入宿主变量，多行查询需要游标逐行或分批读取。游标通常包括声明、打开、获取、处理、关闭等操作。显式游标适合需要逐行处理、分页或复杂流程的场景。

游标会占用数据库和连接资源，打开时间过长会阻塞清理、持有快照或消耗内存。应尽量使用集合操作代替逐行循环；必须使用游标时，设置稳定排序、批量获取、最大行数和超时，并在成功和异常路径都关闭。

分页不能依赖自然顺序，应使用明确排序和唯一补充键。若数据在读取期间变化，要明确一致性要求，避免同一记录重复或漏读。批量处理还要设计失败断点、提交批次、重试和幂等。

八、事务与宿主程序控制流

嵌入 SQL 中的提交和回滚应由清晰的业务事务边界控制。程序不能每执行一条语句就提交，也不能把用户交互和远程调用放在数据库事务中长期等待。事务中的 SQL 应围绕必须共同成立的不变量组织。

异常分支要及时回滚或确认事务状态。数据库错误、宿主程序异常、连接断开和提交后网络超时的处理方式不同。提交请求发出后连接断开，程序不能简单回滚并重试，应使用业务幂等键和状态查询确认结果。

保存点可以把较大事务分成局部可回退的步骤，但不能替代合理事务边界。批量导入时要平衡提交频率、日志、锁和恢复范围，记录已处理水位，避免失败后重复或遗漏。

九、错误处理与资源释放

嵌入 SQL 的运行时通常返回 SQL 状态、错误码、诊断信息和影响行数。程序要区分约束冲突、无数据、连接失败、死锁、超时、权限不足和语法错误，并采取提示、回滚、重试、降级或升级。

错误信息应记录语句模板、调用方、数据库实例、事务标识和相关追踪号，但不能记录密码、令牌和完整敏感数据。对用户返回可理解且不泄露内部结构的错误。

资源释放包括游标、预编译语句、LOB、连接和临时对象。使用结构化资源管理、finally、自动关闭或语言提供的上下文机制，确保异常路径也能释放。连接池归还前清理事务和会话状态，避免跨请求污染。

十、嵌入 SQL 与模式演进

静态 SQL 直接引用表、列和类型，模式变更可能在预编译阶段失败；动态 SQL 可能运行时才暴露错误。模式演进应采用兼容策略：先增加可选字段或新表，部署兼容代码，完成数据迁移和验证，再废弃旧结构。

SQL 应集中在版本库和数据访问层，建立需求、字段、语句、测试和报表追踪。数据库迁移与应用发布要协调，明确先后顺序、灰度、双读、回滚和历史数据处理。禁止应用在运行时随意创建或修改核心表结构。

十一、可移植性与性能

嵌入 SQL 的可移植性受 SQL 方言、数据类型、分页、存储过程、错误码、事务隔离级别和驱动影响。可以使用标准 SQL、适配器和能力矩阵减少耦合，对确需使用厂商特性的部分隔离并建立测试。

性能方面要关注预编译复用、批量操作、网络往返、结果集大小、游标持有、事务长度、连接池、执行计划和 N+1 查询。优化不能只看 SQL 单句耗时，还要看端到端延迟、数据库资源、应用 CPU、网络和并发下的尾部延迟。

十二、案例分析：报表接口的动态 SQL 注入与事务污染

某管理系统使用嵌入 SQL 生成报表。程序把用户输入的筛选字段和排序字符串直接拼接到查询中；查询执行期间逐行处理结果，并在循环中调用外部文件服务；异常发生时没有关闭游标，也没有回滚。上线后出现注入风险、连接池耗尽和报表偶发重复。

问题分析

值和结构直接拼接造成注入，动态字段没有白名单；游标和连接被长时间占用，外部调用扩大事务和资源持有时间；异常路径没有关闭和回滚；逐行处理增加网络往返和结果不一致风险。

改进方案

固定报表 SQL 模板，筛选值使用参数绑定，字段和排序方向使用白名单映射；将数据查询和外部文件处理拆开，短事务读取必要数据后释放连接，文件处理使用异步任务和状态记录；采用批量获取或集合查询，限制结果集和分页；通过结构化资源管理确保游标、语句和连接释放，异常时回滚或确认状态；增加注入、并发、超时、泄漏和数据一致性测试。

十三、综合知识练习与详细答案

练习一

静态 SQL 已经在编译期确定结构，是否可以把用户输入拼接到语句中？

参考答案：不可以。静态 SQL 通常只表示语句结构固定，用户输入仍应通过宿主变量和参数绑定传递；字符串拼接会造成注入、类型转换和计划问题。需要动态列名或排序时，采用白名单映射，不把任意输入当作 SQL 结构。

练习二

为什么逐行游标处理大批量数据通常不如集合操作？

参考答案：逐行处理会增加数据库与应用之间的网络往返、上下文切换、游标资源和事务持有时间，吞吐和尾部延迟较差，也容易在异常时产生部分处理。能用 SQL 集合更新或批量接口时应优先采用；必须逐行处理时使用批量获取、分批提交、断点、幂等和资源释放。

练习三

嵌入 SQL 执行后连接断开，程序不知道写操作是否提交，应如何处理？

参考答案：不能直接按失败重试，因为数据库可能已经提交。应使用业务幂等键、唯一约束或状态查询确认结果，把记录置为待确认并通过回调、对账或补偿处理；重试必须有限、退避且携带同一幂等标识。后续要记录连接、提交和异常状态，完善监控和测试。

十四、论文写作素材

论文中可以这样描述嵌入 SQL 访问：我将嵌入 SQL 集中在数据访问层，使用预编译和参数绑定传递宿主变量，动态结构通过白名单控制；对可空字段、金额、时间、字符集和大对象明确转换规则。连接、语句、游标和结果集使用结构化资源管理，在成功和异常路径都能提交、回滚和释放。

我围绕业务不变量划分事务，避免在持有数据库资源期间调用外部服务；对多行结果优先采用集合查询和批量获取，控制分页、游标寿命和结果集大小；对死锁、超时和连接断开根据错误类型有限重试、状态查询、幂等和补偿，避免重复写入。SQL 模板、迁移脚本、字段映射和测试进入版本管理，形成需求到数据访问的追踪。

在性能和演进方面，我通过执行计划、网络往返、连接池、事务时长、慢查询和 P95/P99 指标验证访问层；对数据库方言和厂商特性通过适配器隔离，对模式变化采用扩展—迁移—收缩和灰度发布。安全测试覆盖注入、越权、错误信息泄露和敏感日志，保证嵌入 SQL 成为受控的架构能力。

十五、今日复盘

嵌入 SQL 的关键是宿主程序与数据库职责清晰、变量传递安全、事务边界合理、游标和资源可控、错误可诊断、模式可演进。静态 SQL 结构稳定、利于复用和检查；动态 SQL 灵活但必须参数化和白名单；多行处理优先集合和批量；提交后断连要用幂等和状态查询确认，不能盲目重试。

记忆主线：

宿主控流程，SQL 做集合；预编译保安全与性能，游标要短、事务要清、资源必释、异常可恢复。

下一篇进入 6.4.3 通用数据接口标准，学习 ODBC、JDBC 等通用接口的思想、驱动管理、可移植性和性能权衡。