数据库系统 | 第八章数据库编程

嵌入式SQL

SQL语言提供了两种不同的使用方式
- 交互式
- 嵌入式
为什么要引入嵌入式SQL
- SQL语言是非过程性语言
- 事务处理应用需要高级语言
这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充

嵌入式SQL的处理过程

主语言
嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。
处理过程
预编译方法
为了区分SQL语句与主语言语句，所有SQL语句必须加前缀EXEC SQL，
- 主语言为C语言时，语句格式：
  EXEC SQL <SQL语句>;
- 主语言为Java语言时，语句格式：
  # SQL <SQL语句>;
- 主语言为C++语言时
  
  使用ADO：

嵌入式SQL语句与主语言之间的通信

将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句
- SQL语句
  描述性的面向集合的语句
  负责操纵数据库
- 高级语言语句
  过程性的面向记录的语句
  负责控制逻辑流程
- 它们之间应该如何通信？
数据库工作单元与源程序工作单元之间的通信
（1）向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程，主要用SQL通信区实现
（2）主语言向SQL语句提供参数，主要用主变量实现
（3）将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标实现
SQLCA： SQL Communication Area
SQLCA是一个数据结构
SQLCA的用途
- SQL语句执行后，系统反馈给应用程序信息
  描述系统当前工作状态
  描述运行环境
- 这些信息将送到SQL通信区中
- 应用程序从SQL通信区中取出这些状态信息，据此决定接下来执行的语句
SQLCA使用方法
- 定义SQLCA
  - 用EXEC SQL INCLUDE SQLCA定义
- 使用SQLCA
  - SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE
  - 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错
  - 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理
主变量
- 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据
- 在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）
主变量的类型
- 输入主变量
  由应用程序对其赋值，SQL语句引用
- 输出主变量
  由SQL语句对其赋值或设置状态信息，返回给应用程序
指示变量
- 是一个整型变量，用来“指示”所指主变量的值或条件
- 一个主变量可以附带一个指示变量（Indicator Variable）
- 指示变量的用途
  - 指示输入主变量是否为空值
  - 检测输出变量是否为空值，值是否被截断
在SQL语句中使用主变量和指示变量的方法
说明主变量和指示变量
BEGIN DECLARE SECTION
…
… （说明主变量和指示变量）
…
END DECLARE SECTION
在SQL语句中使用主变量和指示变量的方法（续）
- 使用主变量
  说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现
  为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志
- 使用指示变量
  指示变量前也必须加冒号标志
  必须紧跟在所指主变量之后
在SQL语句之外（主语言语句中）使用主变量和指示变量的方法
可以直接引用，不必加冒号
为什么要使用游标
- SQL语言与主语言具有不同数据处理方式
- SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录
- 主语言是面向记录的，一组主变量一次只能存放一条记录
- 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求
- 嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式
游标
- 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果
- 每个游标区都有一个名字
- 用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理
建立数据库连接
EXEC SQL CONNECT TO target[AS connection-name][USER user-name];
target是要连接的数据库服务器
常见的服务器标识串，如@:
包含服务器标识的SQL串常量
DEFAULT
connect-name是可选的连接名，连接名必须是一个有效的标识符
在整个程序内只有一个连接时可以不指定连接名
程序运行过程中可以修改当前连接
EXEC SQL SET CONNECTION connection-name
|DEFAULT;
关闭数据库连接
EXEC SQL DISCONNECT [connection];

不用游标的SQL语句

不用游标的SQL语句的种类
- 说明性语句
- 数据定义语句
- 数据控制语句
- 查询结果为单记录的SELECT语句
- 非CURRENT形式的增删改语句

使用游标的SQL语句

必须使用游标的SQL语句
- 查询结果为多条记录的SELECT语句
- CURRENT形式的UPDATE语句
- CURRENT形式的DELETE语句

动态SQL

静态嵌入式SQL
静态嵌入式SQL语句能够满足一般要求
无法满足要到执行时才能够确定要提交的SQL语句、查询的条件
动态嵌入式SQL
允许在程序运行过程中临时“组装”SQL语句
支持动态组装SQL语句和动态参数两种形式

使用SQL语句主变量
动态参数执行准备好的语句（EXECUTE）
动态参数

主变量
[例8.6] 创建基本表TEST。

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt=“CREATE TABLE test(a int);”;
/SQL语句主变量，内容是创建表的SQL语句/
EXEC SQL END DECLARE SECTION;
…
EXEC SQL EXECUTE IMMEDIATE :stmt;
/执行动态SQL语句/
动态参数
- SQL语句中的可变元素
- 使用参数符号（?）表示该位置的数据在运行时设定
- 和主变量的区别
  - 动态参数的输入不是编译时完成绑定
  - 而是通过 PREPARE语句准备主变量和执行语句EXECUTE绑定数据或主变量来完成
使用动态参数的步骤
（1）声明SQL语句主变量
（2）准备SQL语句（PREPARE）
EXEC SQL PREPARE <语句名>
FROM <SQL语句主变量>;

EXEC SQL EXECUTE <语句名>
[INTO <主变量表>]
[USING <主变量或常量>];
准备好的SQL
[例8.7] 向TEST中插入元组。
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = “INSERT INTO test VALUES(?);”;
/*声明SQL主变量内容是INSERT语句 */
EXEC SQL END DECLARE SECTION;
…
EXEC SQL PREPARE mystmt FROM :stmt; /准备语句/
…
EXEC SQL EXECUTE mystmt USING 100;
/*执行语句，设定INSERT语句插入值100 /
EXEC SQL EXECUTE mystmt USING 200;
/ 执行语句，设定INSERT语句插入值200 */

过程化SQL

过程化SQL的块结构

SQL的扩展
- 增加了过程化语句功能
- 基本结构是块
- 块之间可以互相嵌套
- 每个块完成一个逻辑操作
过程化SQL块的基本结构
1. 定义部分
  DECLARE 变量、常量、游标、异常等
  定义的变量、常量等只能在该基本块中使用
  当基本块执行结束时，定义就不再存在
2. 执行部分
  BEGIN
  
  SQL语句、过程化SQL的流程控制语句
EXCEPTION
异常处理部分
END；

变量和常量的定义

变量定义
变量名数据类型 [[NOT NULL]:=初值表达式]或
变量名数据类型 [[NOT NULL] 初值表达式]
常量定义
常量名数据类型 CONSTANT :=常量表达式
常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它，过程化SQL将返回一个异常
赋值语句
变量名称 :=表达式

流程控制

过程化SQL功能

条件控制语句
循环控制语句
错误处理

条件控制语句
IF-THEN，IF-THEN-ELSE和嵌套的IF语句
（1）IF condition THEN
Sequence_of_statements;
END IF;
（2）IF condition THEN
Sequence_of_statements1;
ELSE
Sequence_of_statements2;
END IF;
（3）在THEN和ELSE子句中还可以再包含IF语句，即IF语句可以嵌套
循环控制语句
LOOP，WHILE-LOOP和FOR-LOOP
（1）简单的循环语句LOOP
LOOP
Sequence_of_statements;
END LOOP;
多数数据库服务器的过程化SQL都提供EXIT、BREAK或
LEAVE等循环结束语句，保证LOOP语句块能够结束

（2）WHILE-LOOP
WHILE condition LOOP
Sequence_of_statements;
END LOOP;
每次执行循环体语句之前，首先对条件进行求值
如果条件为真，则执行循环体内的语句序列
如果条件为假，则跳过循环并把控制传递给下一个语句

（3）FOR-LOOP
FOR count IN [REVERSE] bound1 … bound2 LOOP
Sequence_of_statements;
END LOOP;
1. 错误处理
- 如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句
- SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器提供完善的异常处理机制

存储过程和函数

存储过程

过程化SQL块类型
- 命名块
  编译后保存在数据库中，可以被反复调用，运行速度较快，过程和函数是命名块
- 匿名块
  每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用
存储过程：

由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。
- 存储过程的优点
  （1）运行效率高
  （2）降低了客户机和服务器之间的通信量
  （3）方便实施企业规则
存储过程的用户接口
（1）创建存储过程
（2）执行存储过程
（3）修改存储过程
（4）删除存储过程
（1）创建存储过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,…]) AS <过程化SQL块>；
过程名：数据库服务器合法的对象标识
参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数，默认为输入参数
过程体：是一个<过程化SQL块>，包括声明部分和可执行语句部分
[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT)
/定义存储过程TRANSFER，其参数为转入账户、转出账户、转账额度/
AS DECLARE /定义变量/
totalDepositOut Float;
totalDepositIn Float;
inAccountnum INT;

BEGIN /*检查转出账户的余额 */
SELECT Total INTO totalDepositOut FROM Accout
WHERE accountnum=outAccount;
IF totalDepositOut IS NULL THEN
/如果转出账户不存在或账户中没有存款/
ROLLBACK; /回滚事务/
RETURN;
END IF;

IF totalDeposit Out< amount THEN /如果账户存款不足/
ROLLBACK; /回滚事务/
RETURN;
END IF;

SELECT Accountnum INTO inAccountnum FROM Account
WHERE accountnum=inAccount;

IF inAccount IS NULL THEN /如果转入账户不存在/
ROLLBACK; /回滚事务/
RETURN;
ENDIF;

UPDATE Account SET total=total-amount WHERE accountnum=outAccount;
/* 修改转出账户余额，减去转出额 /
UPDATE Account SET total=total + amount
WHERE accountnum=inAccount;
/ 修改转入账户余额，增加转入额 /
COMMIT; / 提交转账事务 */
END;
（2）执行存储过程
CALL/PERFORM PROCEDURE 过程名([参数1,参数2,…])；
使用CALL或者PERFORM等方式激活存储过程的执行
在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程
[例8.9] 从账户01003815868转10000元到01003813828账户中。
CALL PROCEDURE TRANSFER(01003813828,01003815868,10000);
（3）修改存储过程
ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
（4）删除存储过程
DROP PROCEDURE 过程名()；

函数

函数和存储过程的异同
同：都是持久性存储模块
异：函数必须指定返回的类型

函数的定义语句格式
CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;
函数的执行语句格式
CALL/SELECT 函数名 ([参数1,参数2,…]);
修改函数
重命名
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译
ALTER FUNCTION 过程名 COMPILE;

存储过程：

优点：
- 有if/else,case,while等控制语句，通过编写存储过程，可以实现一些逻辑比较复杂的功能；
- 模块化；对一些功能进行了封装，代码的复用；
- 响应速度快，只有在首次执行时需要经过编译和优化步骤，后被调用直接执行，省去了重新编写代码计算的步骤。
- 减少网络传输。存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。
- 方便DBA优化。所有的SQL集中在一个地方
缺点：
复杂的业务逻辑。没办法应用缓存。

触发器

优点：
- 安全。以基于数据库的值使用户具有操作数据库的某种权利。可以跟踪用户对数据库的操作。
- 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。
缺点：*数据集数据量又较大时，触发器效果会非常低（因为自动计算数据值，需要变动整个数据集导致效率下降）
- 对于批量操作并不适合使用触发器，使用触发器实现的业务逻辑在出现问题时很难进行定位。

ODBC编程

ODBC优点
- 移植性好
- 能同时访问不同的数据库
- 共享多个数据资源

ODBC概述

ODBC产生的原因
- 由于不同的数据库管理系统的存在，在某个关系数据库管理系统下编写的应用程序就不能在另一个关系数据库管理系统下运行
- 许多应用程序需要共享多个部门的数据资源，访问不同的关系数据库管理系统
ODBC
- 是微软公司开放服务体系（Windows Open Services Architecture，WOSA）中有关数据库的一个组成部分
- 提供了一组访问数据库的应用程序编程接口（Application Programming Interface，API ）
ODBC约束力
- 规范应用开发
- 规范关系数据库管理系统应用接口

ODBC工作原理概述

ODBC应用系统的体系结构
1. 用户应用程序
2. ODBC驱动程序管理器
3. 数据库驱动程序
4. 数据源
ODBC应用程序包括的内容
- 请求连接数据库
- 向数据源发送SQL语句
- 为SQL语句执行结果分配存储空间，定义所读取的数据格式
- 获取数据库操作结果或处理错误
- 进行数据处理并向用户提交处理结果
- 请求事务的提交和回滚操作
- 断开与数据源的连接
驱动程序管理器：用来管理各种驱动程序
- 包含在ODBC32.DLL中
- 管理应用程序和驱动程序之间的通信
- 建立、配置或删除数据源，并查看系统当前所安装的数据库ODBC驱动程序
主要功能：
- 装载ODBC驱动程序
- 选择和连接正确的驱动程序
- 管理数据源
- 检查ODBC调用参数的合法性
- 记录ODBC函数的调用等
ODBC驱动程序类型
- 单束
  数据源和应用程序在同一台机器上
  驱动程序直接完成对数据文件的I/O操作
  驱动程序相当于数据管理器
- 多束
  支持客户机—服务器、客户机—应用服务器/数据库服务器等网络环境下的数据访问
  由驱动程序完成数据库访问请求的提交和结果集接收
  应用程序使用驱动程序提供的结果集管理接口操纵执行后的结果数据
数据源：是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，是一种数据连接的抽象
数据源对最终用户是透明的
- ODBC给每个被访问的数据源指定唯一的数据源名（Data Source Name，简称DSN），并映射到所有必要的、用来存取数据的低层软件
- 在连接中，用数据源名来代表用户名、服务器名、所连接的数据库名等
- 最终用户无须知道数据库管理系统或其他数据管理软件、网络以及有关ODBC驱动程序的细节
例如，假设某个学校在SQL Server和KingbaseES上创建了两个数据库：学校人事数据库和教学科研数据库。
学校的信息系统要从这两个数据库中存取数据
为了方便地与两个数据库连接，为学校人事数据库创建一个数据源名PERSON，为教学科研数据库创建一个名为EDU的数据源
当要访问每一个数据库时，只要与PERSON和EDU连接即可，不需要记住使用的驱动程序、服务器名称、数据库名

ODBC API基础

ODBC 应用程序编程接口的一致性
- API一致性
  包含核心级、扩展1级、扩展2级
- 语法一致性
  包含最低限度SQL语法级、核心SQL语法级、扩展SQL语法级
1. 函数概述
2. 句柄及其属性
3. 数据类型
ODBC 3.0 标准提供了76个函数接口
- 分配和释放环境句柄、连接句柄、语句句柄
- 连接函数（SQLDriverconnect等）
- 与信息相关的函数（SQLGetinfo、SQLGetFuction等）
- 事务处理函数（如SQLEndTran）
- 执行相关函数（SQLExecdirect、SQLExecute等）
- 编目函数，ODBC 3.0提供了11个编目函数，如SQLTables、SQLColumn等。应用程序可以通过对编目函数的调用来获取数据字典的信息，如权限、表结构等
句柄是32位整数值，代表一个指针
ODBC 3.0中句柄分类
- 环境句柄
- 连接句柄
- 语句句柄
- 描述符句柄
应用程序句柄之间的关系（续）
- 在一个连接中可以建立多个语句句柄，它不只是一个SQL语句，还包括SQL语句产生的结果集以及相关的信息等
- 在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL语句的参数、结果集列的元数据集合
ODBC数据类型
- SQL数据类型：用于数据源
- C数据类型：用于应用程序的C代码
应用程序可以通过SQLGetTypeInfo来获取不同的驱动程序对于数据类型的支持情况
[例8.11] 将KingbaseES数据库中Student表的数据备份到SQL Server数据库中。
- 该应用涉及两个不同的关系数据库管理系统中的数据源
- 使用ODBC来开发应用程序，只要改变应用程序中连接函数（SQLConnect）的参数，就可以连接不同关系数据库管理系统的驱动程序，连接两个数据源
操作步骤
1. 配置数据源
2. 初始化环境
3. 建立连接
4. 分配语句句柄
5. 执行SQL语句
6. 结果集处理
7. 中止处理
应用程序中止步骤
- 释放语句句柄
- 释放数据库连接
- 与数据库服务器断开
- 释放ODBC环境