在应用程序中使用SQL的全面指南

### 在应用程序中使用 SQL 的全面指南 #### 1. 引言 SQL 作为一种标准化语言，用于与数据库进行交互、操作数据库对象以及检索其中的数据。然而，仅靠 SQL 不足以编写需要数据库作为后端的大型功能应用程序。像 C、C++、Java 等应用开发语言（即宿主语言）能提供更多的控制和功能逻辑能力，它们可以与 SQL 很好地集成，在应用程序中与数据库进行交互。 #### 2. 事务的概念 在深入了解不同的 SQL 应用技术之前，需要先理解事务的概念。事务或工作单元是一组数据库操作，只有当所有操作都成功执行时，才能称该事务成功。 例如，银行要从账户 A 向账户 B 转账 1000 美元，需要完成以下步骤： - 减少账户 A 的余额 1000 美元。 - 增加账户 B 的余额 1000 美元。 用 SQL 术语来说，上述步骤需要两条 SQL 语句。如果其中任何一条或多条 SQL 语句失败，资金转账就不会成功。因此，在这个例子中，这两条 SQL 语句共同构成一个事务。应用程序中的 SQL 处理提供了提交或回滚工作单元的方法，以维护数据的完整性。使用事务的常见应用程序示例包括在线预订系统、在线购物等。 #### 3. 嵌入式 SQL 嵌入式 SQL 是指将 SQL 语句嵌入到用高级宿主编程语言（如 C、C++ 或 COBOL）编写的宿主应用程序中。宿主应用程序包含必要的业务逻辑，并根据需要调用 SQL 语句来访问数据库中的信息。 那么，如何编译包含嵌入式 SQL 语句的高级编程语言应用程序呢？由于没有编程语言编译器支持 SQL 编译和语法验证，答案是预编译。以 DB2 为例，它提供了一个预编译器，将 SQL 语法直接转换为 DB2 运行时服务 API 调用。预编译后的代码再由宿主语言开发工具进行编译和链接。 在宿主语言中，嵌入式 SQL 语句需要用特定的语句或块来标识。在 C、C++ 和 COBOL 中，使用语句初始化器 `EXEC SQL` 来实现这一目的。该语句后面跟着要执行的 SQL 语句字符串，并以分号（;）结尾，分号是语句终止符。例如： ```sql EXEC SQL UPDATE employee.details SET emp_desig = 'Mgr' WHERE emp_desig = 'Asst Mgr'; ``` SQL 语句也可以嵌入到 Java 应用程序中，这种嵌入式 SQL Java 应用程序被称为 SQLJ 应用程序。与 `EXEC SQL` 语句初始化器类似，SQLJ 应用程序要求 SQL 语句以 `#sql` 开头。例如，上述相同的 `UPDATE` SQL 语句在 SQLJ 应用程序中编写如下： ```sql #sql { UPDATE employee.details SET emp_desig = 'Mgr' WHERE emp_desig = 'Asst Mgr'}; ``` 上述两个示例都包含静态 SQL 语句，因为 SQL 语法中的表名、列名和其他数据库对象在每次应用程序运行时都是已知且常量的。相反，如果这些对象在运行时作为输入提供给程序，那么就是动态 SQL，因为确切的 SQL 语句是在运行时确定的。 ##### 3.1 静态 SQL 静态 SQL 是构建嵌入式 SQL 应用程序的最传统方式。静态 SQL 应用程序适用于每次与数据库交互时都需要发出相同 SQL 语句的场景。例如，更新库存的应用程序总是发出相同的 SQL 语句来添加新库存；在线预订系统总是更新相同的表，并将相同的列标记为“已预订”以进行新预订；为了获取最新的预订状态，它总是在相同的表上发出相同的 `SELECT` 语句。 一个嵌入式 SQL 应用程序，如果其 SQL 语法事先完全已知，并且 SQL 语句硬编码在应用程序的源代码中，则称为静态嵌入式 SQL 应用程序。应用程序可以提供给 SQL 语句的唯一输入是需要插入表中的实际数据值或 SQL 语句的谓词值。这些输入值通过宿主变量提供给 SQL 语句。 ###### 3.1.1 宿主变量 宿主变量是仅用于静态 SQL 处理的编程语言变量。在使用这些宿主变量之前，需要在应用程序中进行声明。一个好的做法是在声明它们后立即用默认值对其进行初始化。另一个好的做法是在宿主变量名后面加上 `_hv`，以将它们与其他变量以及列名区分开来。 以下是一个嵌入式 SQL C 代码片段： ```c EXEC SQL SELECT emp_name, emp_dept, emp_salary INTO :name_hv, :dept_hv, :salary_hv FROM employee.details WHERE emp_id = :id_hv ; EXEC SQL UPDATE employee.details SET emp_salary = :new_salary_hv WHERE emp_id = :id_hv ; ``` 在这两条语句中，表名（`employee.details`）和列名（`emp_name`、`emp_dept` 等）都是硬编码的。在运行时可以提供给 SQL 语句的信息通过宿主变量（`id_hv`、`dept_hv` 等）传递。每个宿主变量前面的冒号（:）是嵌入式 SQL 语法的一部分。 ###### 3.1.2 嵌入式 SQL 应用程序结构 无论使用哪种宿主语言，所有嵌入式 SQL 应用程序都由以下三个主要元素组成，这些元素是设置和执行 SQL 语句所必需的： - 用于声明宿主变量的 `DECLARE SECTION`。 - 应用程序的主体，包括 SQL 语句的设置和执行。 - 用于提交或回滚 SQL 语句所做更改的逻辑（如果需要）。 以下是一个嵌入式 SQL C 代码片段，展示了这三个元素： ```c int getDetails( int employee_id, double new_salary) { int ret_code = 1; EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; sqlint32 id_hv = 0; // employee id char name_hv[129] = {0}; // employee name char dept_hv[129] = {0}; // employee department double salary_hv = 0; // employee salary EXEC SQL END DECLARE SECTION; // Copy the employee id and salary passed to this function // into the host variables id_hv = employee_id; salary_hv = new_salary; // Issue the UPDATE statement to set the new salary of an employee EXEC SQL UPDATE employee.details SET emp_salary = :salary_hv WHERE emp_id = :id_hv; if (SQLCODE < 0) { printf(“\n UPDATE SQL Error:%ld\n”,SQLCODE); EXEC SQL ROLLBACK; // Rollback the transaction ret_code = 0; // error } else { EXEC SQL COMMIT; // Commit the transaction // Issue a SELECT to fetch updated salary information EXEC SQL SELECT emp_name, emp_dept, emp_salary INTO :name_hv, :dept_hv, :salary_hv FROM employee.details WHERE emp_id = :id_hv; if (SQLCODE < 0) { printf(“\n SELECT SQL Error:%ld\n”,SQLCODE); Ret_code = 0; } else { // Display the updated salary information printf(“\n Employee name: %s”,name_hv); printf(“\n Employee Id: %d”,id_hv); printf(“\n Employee Department: %s”,dept_hv); printf(“\n Employee New Salary: Rs. %ld p.a”,salary_hv); } } return ret_code; } ``` 上述示例展示了如何将 SQL 语句嵌入到作为宿主语言的 C 中。其他宿主语言（如 C++、COBOL、Java 等）也具有上述相同的三个主要元素。 ###### 3.1.3 SQL 通信区、SQLCODE 和 SQLSTATE SQL 通信区（SQLCA）是一种数据结构，用作数据库服务器与其客户端之间的通信媒介。SQLCA 数据结构包含多个变量，这些变量在每次 SQL 执行结束时都会更新。`SQLCODE` 是该数据结构中的一个变量，每次成功执行 SQL 后，它被设置为 0（零）。如果 SQL 语句执行完成时带有警告，它被设置为一个正的非零值；如果 SQL 语句返回错误，它被设置为一个负值。 `SQLCODE` 值可能对应于特定硬件或操作系统问题，例如文件系统已满或访问文件时出错。在每次 SQL 执行后检查返回的 `SQLCODE` 是一个好主意，如上述应用程序所示。 `SQLSTATE` 是 SQLCA 中提供的另一个变量，它以字符串形式存储返回码，也指示最近执行的 SQL 语句的结果。然而，`SQLSTATE` 提供了一个更通用的消息，该消息在不同数据库供应商产品中是标准化的。 ###### 3.1.4 编译静态 SQL 应用程序的步骤 如前所述，嵌入式 SQL 应用程序需要先使用 DB2 预编译器进行预编译。预编译器检查源代码中的 SQL 语句，将其替换为宿主语言支持的等效 D