重点与难点

  • 数据库语言嵌入到高级语言中使用需要解决的问题—过程及其思维
  • 怎么在高级语言中处理数据集—游标的使用技巧
  • 错误捕获机制—设置错误陷阱与SQLCA的作用与使用
  • 事物的概念—保证数据正确性的机制

一. 嵌入式SQL语言概述

1. 交互式SQL语言的局限

  • 从使用者的角度：普通用户不友好（复杂SQL语句有难度），提供数据库应用程序

  • 从SQL本身角度：特别复杂的检索结果难以用一条交互式SQL语句完成，考虑是否可以通过多条SQL语句联合检索，需要结合高级语言循环、分支等结构处理

2. 嵌入式SQL语言

  • 继承高级语言的过程控制性
  • 结合SQL语言的复杂结果集操作的非过程性
  • 又为数据库操作者提供安全可靠的操作方式

以宿主语言C语言为例：

1
exec sql select Sname, Sage into :vSname, :vSage from Student where Sname='张三';

exec sql 引导 SQL 语句：提供给 C 编译器，以便对 SQL 语句预编译成C编译器可识别的语句

增加 into 子句：用于指出接收 SQL 语句检索结果的程序变量

由冒号引导的程序变量，如 :vSname、:vSage

3. 嵌入式SQL语言需要解决的问题

image-20220322160203333

4. 主要过程

SQLCA：SQL Communication Area Declare Section SQL错误捕获语句 SQL Connect SQL Commit/Rollback Work SQL Disconnect

二. 变量声明与数据库连接

要解决的问题：

  • 问题 1：高级语言程序如何与数据库连接和断开连接
  • 问题 2：高级语言的程序变量如何传递给SQL语句

1. 变量的声明和使用

1
2
3
4
5
6
exec sql begin declare section;
	char vSname[10], specName[10]=“张三”;
	int	vSage;
exec sql end declare section;
// 宿主语言语句所使用的变量需要上述特殊的声明
exec sql select Sname, Sage	into :vSname, :vSage from Student where Sname= :specName;

  • 宿主程序的字符串变量长度应比字符型字段的长度多1，C的字符串尾部终止符为‘\0‘

  • 宿主程序变量类型与数据库字段类型之间有些是有差异的, 有些DBMS可支持自动转换，有些不能。

2. 程序与数据库的连接与断开

嵌入式SQL程序执行之前，首先要与数据库进行连接

1
2
3
4
5
6
7
// SQL标准建议的语法
exec sql connect to target-server as connect-name user user-name';
exec sql connect to default;
// Oracle数据库连接方法
exec sql connect :usre_name identified by :user_pwd;
// DB2 UDB中数据库连接
exec sql connect to mydb user :user_name using :user_pwd;

在嵌入式SQL程序执行之后，需要与数据库断开连接

1
2
3
4
5
6
7
8
9
10
// SQL标准中建议的断开连接的语法为：
exec sql disconnect	connect-name;
exec sql disconnect current;

// Oracle中断开连接:
exec sql commit release;
exec sql rollback release;
// DB2 UDB中断开连接:
exec sql connect reset;
exec sql disconnect current;

3. SQL执行的提交与撤销

SQL语句在执行过程中，必须有提交和撤销 语句才能确认其操作结果

1
2
3
4
// SQL执行的提交
exec sql commit work;
// SQL执行的撤销
exec sql rollback work;

很多DBMS都设计了捆绑提交/撤消与断开连接的语句，在断开连接之前使用户确认提交或撤消先前的工作

如上述的Oracle断开连接的两条语句，分别对应断开时提交/撤销

4. 事务

事务：（从应用程序员角度）是一个存取或改变数据库内容的程序的一次执行，或者说一条或多条SQL语句的一次执行被看作一个事务。

1
2
3
4
5
6
Begin	Transaction
	exec sql ...
    ...
	exec sql ...
	exec sql commit work | exec sql rollback work
End Transaction

任何一条数据库操纵语句（如exec sql select等）都会引发一个新事务的开始，只要该程序当前没有正在处理的事务。而事务的结束是需要应用程序员通过 commit 或 rollback 确认的。因此Begin Transaction 和End Transaction两行语句是不需要的。

事务：（微观角度，或者从DBMS角度）是数据库管理系统提供的控制数据操作的一种手段，通过这一手段，应用程序员将一系列的数据库操作组合在一起作为一个整体进行操作和控制，以便数据库管理系统能够提供一致性状态转换的保证。

事务的特征：ACID

  • 原子性 Atomicity：一组操作原子不可分，全做/全不做
  • 一致性 Consistency：事务的操作状态是正确的，符合一致性的操作规则，进一步由隔离性来保证
  • 隔离性 Isolation：多个事务之间或不影响，即使并发执行也有先后执行次序
  • 持久性 Durability：已提交事务的影响是持久的，被撤销事务的影响是可恢复的

三. 数据集和游标

要解决的问题：

  • 问题 3：SQL语句如何执行
  • 问题 4：检索结果传递到宿主程序
  • 问题 5：静态SQL

1. 读取单行/多行数据

  • 检索单行结果，可将结果直接传送到宿主程序的变量中
1
2
3
4
EXEC SQL SELECT [ALL | DISTINCT] expression [, expression...]
	INTO host-variable , [host-variable, ...]
	FROM tableref [corr_name] [ , tableref [corr_name] ...]
    WHERE search_condition;
  • 检索多行结果，则需使用游标（Cursor）
    • 游标是指向某检索记录集的指针
    • 读一行操作是通过Fetch…into语句实现的，每次都是向下移动指针
    • 记录集结束标志 EOF

2. 游标

游标的定义、打开、读取和关闭：

1
2
3
4
5
6
EXEC SQL DECLARE cursor_name CURSOR	FOR Subquery
	[FOR [READ ONLY | UPDATE [OF columnname [, columnname ...]]]];

EXEC SQL OPEN cursor_name;
EXEC SQL FETCH cursor_name INTO host-variable, [host-variable, ...];
EXEC SQL CLOSE cursor_name;

游标的使用：只有在打开游标时才执行上述select语句，游标定义一次可以多次打开 

1
2
3
4
5
6
7
8
9
10
// 游标的声明
exec sql declare cur_student cursor for
select Sno, Sname, Sclass from Student where Sclass=‘035101’ ;
// 打开游标，只有在打开游标时才执行上述select语句，游标定义一次可以多次打开
exec sql open cur_student;
// 一条条处理
exec sql fetch cur_student int :vSno, :vSname, :vSclass;
// ...
// 关闭游标
exec sql close cur_student;

3. 可滚动游标

ODBC（Open DataBase Connectivity）是一种跨DBMS的DB操作平台，它在应用程序与实际的DBMS之间提供了一种通用接口。ODBC支持可滚动的游标。

可滚动游标是可使游标指针在记录集之间灵活移动、使每条记录可以反复被访问的一种游标

1
2
3
4
5
EXEC SQL DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR Subquery
	[FOR [READ ONLY | UPDATE [OF columnname [, columnname ...]]]];

EXEC SQL FETCH [NEXT |PRIOR |FIRST |LAST |[ABSOLUTE | RELATIVE] value_spec]
    from cursor_name INTO host-variable, [host-variable, ...];

与标注游标的区别：声明时[INSENSITIVE] [SCROLL]；移动时指明方向next、prior等，并且游标名前有from

NEXT 结束方向移动一条； PRIOR 开始方向移动一条；FIRST 到第一 条；LAST 到最后一条；

ABSOLUTE value_spec定向检索指定位置的行，value_spec由1至当前记录集最大值；

RELATIVE value_spec相对当前记录向前或向后移动，value_spec为正数向结束方向移动，为负数向开始方向移动

  • 最后一条记录之后的位置EOF、起始记录之前的位置BOF
  • 不区分时：whenever not found

4. 嵌入式SQL的数据库增删改

4.1 删除

查找删除（与交互式 DELETE 语句相同）、定位删除

1
2
EXEC SQL DELETE FROM tablename [corr_name]
	WHERE search_condition | WHERE CURRENT OF cursor_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 示例：查找删除（与交互式SQL相似）
exec sql delete from customers c
    where c.city = ‘Harbin’ and not exists (select * from orders o where o.cid = c.cid);
// 示例：定位删除（先定义游标，再通过游标一条条删除）
exec sql declare delcust cursor for
	select cid from customers c where c.city =‘harbin’ and
	not exists (select * from orders o where o.cid = c.cid)
    for update of cid;

exec sql open delcust;
While (TRUE) {
	exec sql fetch	delcust into :cust_id;
	exec sql delete from customers where curren of delcust;
}

4.2 更新

查找更新（与交互式 UPDATE 语句相同）、定位更新

1
2
3
EXEC SQL UPDATE	tablename [corr_name]
	SET columnname = expr [, columnname = expr ...]
	[ WHERE search_condition ] | WHERE CURRENT OF cursor_name;
1
2
3
4
5
6
7
8
9
10
11
// 示例：查找更新（与交互式SQL的更新操作相似）
exec sql update	student	s set sclass = ‘035102’ where s.sclass = ‘034101’;
// 示例：定位更新（先定义游标，再通过游标一条条更新）
exec sql declare stud cursor for
	select * from student s	where s.sclass =‘034101’
	for  update  of	sclass;
exec sql open stud;
While (TRUE) {
	exec sql fetch stud into :vSno, :vSname, :vSclass;
	exec sql update	student set sclass = ‘035102’ where current of stud;
}

4.3 插入

只有一种类型的插入操作，与交互式SQL插入语句相似

1
2
exec sql insert into student (sno, sname, sclass) values (‘03510128’, ‘张三’, ‘035101’);
exec sql insert into student (sno, sname, sclass) select sno, sname, sclass from student;

四. 状态捕获及错误处理机制

要解决的问题：

  • 问题 6：宿主程序如何知道SQL语句执行状态，是否发生错误

1. 基本机制

状态是指嵌入式SQL语句的执行状态，尤其是一些出错状态 知道并处理。捕获状态构成

  • 设置 SQL通信区 SQLCA：一般再嵌入式SQL程序开始处设置

    • ```c exec sql include sqlca; 

      1
      2
      3
      4
      5
      6
      7

        - 已经声明过，是DBMS与宿主程序之间交流的桥梁之一

      - ***设置状态捕获语句：***任何位置、可多次设置、***有作用域（需要注意作用域）***

        - ```c
          exec sql whenever condition action;

    • Whenever 设置一个“条件陷阱”，会对其后面的所有由 Exec SQL 语句所引起的对数据库系统的调用自动检查它是否满足条件（由 condition 指出）

    • condition 具体有：SQLERROR—检查是否有SQL语句出错，NOT FOUND—没有相应的结果记录出现，SQLWARNING

    • action （满足 condition 采取的动作）：CONTINUE—忽略条件或错误继续执行，GOTO 标号—转移到标号指示的语句，STOP—终止运行、撤销当前工作、断开数据库连接，DO函数或CALL函数：调用宿主程序的函数

    • Whenever的作用范围是其后的所有Exec SQL语句，一直到程序中出现另一条相同条件的Whenever语句为止

    • 状态捕获语句Whenever的使用容易引发无限循环：处理语句中又出现错误，又跳到处理语句开头

  • 设置处理语句

    •   report_error: exec sql rollback;

2. 状态信息

典型DBMS系统记录状态信息的三种方法（显式状态处理）

  • sqlcode：典型 DBMS 都提供一个 sqlcode 变量来记录其执行 sql 语句的状态
    • sqlcode == 0, successful call
    • sqlcode < 0, error
    • sqlcode > 0, warning
  • sqlca.sqlcode：支持SQLCA的产品一般要在SQLCA中填写sqlcode 来记录上述信息; 除此而外，sqlca还有其他状态信息的记录
  • sqlstate：有些 DBMS 提供的记录状态信息的变量是 sqlstate 或 sqlca.sqlstate

3. 程序处理

注意显示的错误处理：因为一旦create table发生错误, 则执行 handle_error标号后的语句，在显示处理之前添加一句 exec sql whenever sqlerror continue。