第八章

第八章 数据库编程

主要内容 • 嵌入式SQL（ESQL） • 存储过程（PL/SQL） • ODBC • JDBC

主要数据库访问方式

嵌入式SQL（Embedded SQL） • 为什么学？ • 在高级语言中实现数据库操作,( e.g. C，称为宿主语言，host Language) • 学什么？ • 高级语言和SQL之间的如何通信，其中 • SQL语句是描述性的面向集合的语句，负责操纵数据库 • 高级语言是过程性的面向记录的语句；负责控制程序流程

嵌入式SQL（Embedded SQL） • 格式 EXEC SQL <embedded SQL statement > END_EXEC 或 EXEC SQL <embedded SQL statement >; • 预编译方法，参见P238 图8.1 • ESQL三大关键概念 • SQLCA：向主语言传递数据库执行状态信息 • 主变量（指示变量）：主语言和SQL之间传递参数 • 游标：协调面向集合和面向记录两种不同的处理方式

关键概念－SQLCA • SQLCA：SQL通信区 • 定义 EXEC SQL INCLUDE SQLCA; • 常用代码; • Sqlcode。该代码的定义可在头文件 sqlerr.h 中找到。 SQLCODE＝0，表示操作成功； SQLCODE>0,表示警告， SQLCODE>0,表示错误。 • sqlcaid 8 字节字符字段，包含作为 SQLCA 结构标识的字符串 SQLCA。在您查看内存内容时，该字段可帮助进行调试。 • sqlcabc 包含 SQLCA 结构长度（136 字节）的长整数。 • sqlerrml sqlerrmc 字段中信息的长度。 • sqlerrmc 可以包含准备插入到错误消息中的一个或多个字符串。一些错误消息包含占位符字符串 (%1)，它将被该字段中的文本代替。 • sqlerrp 保留。 • sqlerrd 长整数的实用程序数组。 • sqlstate SQLSTATE 状态值。

关键概念－主变量 • 输入主变量和输出主变量 • 默认为全局变量 • SQL 语句中使用主变量时，必须在变量名之前添加冒号 (:) • 可以使用主变量代替任何 SQL 语句中的常量值，不能用于代替表或列的名称； • 不允许使用 typedef 类型和结构 • 定义 EXEC SQL BEGIN DECLARE SECTION; long employee_number = 0; EXEC SQL END DECLARE SECTION;

指示符的值 向数据库提供值从数据库接收值 0 主变量的值读取的非 NULL 值 –1 NULL 值读取的 NULL 值关键概念－指示变量 • 一个整形变量，用于指示输入和输出主变量是否为空值。 • 要检测或指定 NULL 值，可将指示变量放在 SQL 语句中紧随主变量之后的位置上。 • 例 EXEC SQL INSERT INTO Employee VALUES (:employee_number, :employee_name, :employee_initials, :employee_phone:ind_phone ); • 应用方式

关键概念－游标（Cursor） • 游标用来协调集合和记录这两种不同的处理方式 • 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果 • 每个游标区都有一个名字 • 用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理

嵌入式SQL语句的处理 • 不用游标的SQL语句 • 对数据集不需要逐个处理的SQL语句 • Create table等 • 非current形式的update、delete • 结果为单记录的select语句 • 使用游标的SQL语句 • current形式的update、delete • 结果为多记录的select语句

举例:非current形式的语句 • 删除给定学号学生的选课信息，学号已存入主变量xx EXEC SQL delete from sc Where sno=:xx; • 把计算机系全体学生年龄置为NULL Sageid=-1; EXEC SQL Update student Set Sage=:Raise:Sageid Where Sdept=‘CS’;

举例:结果为单记录的select语句 EXEC SQL CONNECT TO target; EXEC SQL SELECT sno,cno,grade INTO :hsno, :hcno, :hgrade:gradeid FROM student WHERE sno= :givensno; EXEC SQL DISCONNECT ALL;

举例:结果为单记录的select语句 • 单行查询最多从数据库中检索一行。 • 单行查询 SELECT 语句可在选择列表之后和 FROM 子句之前有一个 INTO子句。当执行 SELECT 语句时，数据库服务器检索结果并将其放在主机变量中。 • INTO 子句包含一个主机变量的列表，用来接收每个选择列表项的值。主机变量和选择列表项的数目必须相同。 • 主机变量可以和指示符变量一起使用，以指示 NULL 结果。 • 出错处理 • 如果查询选择了多行，则数据库服务器返回 SQLE_TOO_MANY_RECORDS 错误。 • 如果查询没有选择任何行，则返回 SQLE_NOTFOUND 警告

游标的使用 • 使用游标的步骤 • 1. 说明游标 • 2. 打开游标 • 3. 移动游标指针，然后取当前记录 • 4. 关闭游标

1. 说明游标 • 使用DECLARE语句 • 语句格式 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>; • 功能 • 是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。

2. 打开游标 • 使用OPEN语句 • 语句格式 EXEC SQL OPEN <游标名>; • 功能 • 打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中 • 这时游标处于活动状态，指针指向查询结果集中第一条记录之前

3. 移动游标指针，然后取当前记录 • 使用FETCH语句 • 语句格式 EXEC SQL FETCH [[NEXT|PRIOR| FIRST|LAST] FROM] <游标名> INTO <主变量>[<指示变量>] [,<主变量>[<指示变量>]]...; 缺省值为NEXT

3.移动游标指针，然后取当前记录（续） • 说明 (1) 主变量必须与SELECT语句中的目标列表达式具有一一对应关系 (2) FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理 (3) 为进一步方便用户处理数据，现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针

4. 关闭游标 • 使用CLOSE语句 • 语句格式 EXEC SQL CLOSE <游标名>; • 功能 • 关闭游标，释放结果集占用的缓冲区及其他资源 • 说明 • 游标被关闭后，就不再和原来的查询结果集相联系 • 被关闭的游标可以再次被打开，与新的查询结果相联系

结果集为多记录的SELECT 例1 查询某个系全体学生的信息（学号、姓名、性别和年龄）。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中 ...... ...... EXEC SQL INCLUDE SQLCA;

例题（续） ...... EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ ...... ...... EXEC SQL END DECLARE SECTION; ...... ...... gets(deptname); /* 为主变量deptname赋值 */ ......

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; /* 说明游标 */

例题（续） EXEC SQL OPEN SX /* 打开游标 */ WHILE(1) /* 用循环结构逐条处理结果集中的记录 */ { EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; …… if (sqlca.sqlcode <> SUCCESS) break; …… }; EXEC SQL CLOSE SX; /* 关闭游标 */

CURRENT形式的UPDATE语句和DELETE语句(1) • 为UPDATE语句说明游标 • 使用带FOR UPDATE OF <列名>短语的DECLARE语句 • 语句格式 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句> FOR UPDATE OF <列名>; • FOR UPDATE OF <列名>短语用于指明检索出的数据在指定列上是可修改的，以便DBMS进行并发控制

CURRENT形式的UPDATE语句和DELETE语句(2) • 为DELETE语句说明游标 • 使用带FOR Delete短语的DECLARE语句 • 语句格式 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句> FOR Delete; • FOR delete短语提示DBMS进行并发控制

CURRENT形式的UPDATE语句和DELETE语句(3) • 修改或删除当前记录 • 经检查缓冲区中记录是要修改或删除的记录，则用UPDATE语句或DELETE语句修改或删除该记录 • 语句格式 • <UPDATE语句> WHERE CURRENT OF <游标名> • <DELETE语句> WHERE CURRENT OF <游标名> • WHERE CURRENT OF <游标名>子句表示修改或删除的是该游标中最近一次取出的记录

CURRENT形式的UPDATE语句和DELETE语句(4) • 注意问题－－以下两种情况除外 • 当游标定义中的SELECT语句带有UNION或ORDER BY子句 • 或者该SELECT语句相当于定义了一个不可更新的视图时

例题例对某个系的学生信息，根据用户的要求修改其中某些人的年龄字段。 • 思路 • 查询某个系全体学生的信息(要查询的系名由主变量deptname指定） • 然后根据用户的要求修改其中某些记录的年龄字段

例题（续） ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ ...... EXEC SQL END DECLARE SECTION; ...... gets(deptname); /* 为主变量deptname赋值 */ ......

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE OF Sage; /* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */

例题（续） WHILE(1) {/* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/

例题（续） if (sqlca.sqlcode <> SUCCESS) break; /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", HSno, HSname, HSsex, HSage); /* 显示该记录 */ printf("UPDATE AGE ? "); /* 问用户是否要修改 */ scanf("%c",&yn);

例题（续） if (yn='y' or yn='Y') /* 需要修改 */ { printf("INPUT NEW AGE: "); scanf("%d",&NEWAge); /* 输入新的年龄值 */ EXEC SQL UPDATE Student SET Sage=:NEWAge WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */ };

例题（续） ...... ...... }; EXEC SQL CLOSE SX; /* 关闭游标 */ ...... ......

例题（续） 例4 对某个系的学生信息，根据用户的要求删除其中某些人的记录。 ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; ...... /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; ...... gets(deptname); /* 为主变量deptname赋值 */ ......

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE; /* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */

例题（续） WHILE(1){ /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/

例题（续） if (sqlca.sqlcode <> SUCCESS) break; /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", HSno, HSname, HSsex, HSage); /* 显示该记录 */

例题（续） printf("DELETE ? "); /* 问用户是否要删除 */ scanf("%c",&yn); if (yn='y' or yn='Y') /* 需要删除 */ EXEC SQL DELETE FROM Student WHERE CURRENT OF SX; /* 删除当前记录 */ ...... }; EXEC SQL CLOSE SX; /* 关闭游标 */ ......

静态嵌入式SQL • 静态嵌入式SQL的特点 • 语句中主变量的个数与数据类型在预编译时都是确定的，只有是主变量的值是程序运行过程中动态输入的。 • 静态嵌入式SQL的不足 • 查询条件是不确定的，要查询的属性列也是不确定的 • 任课教师想查选修某门课程的所有学生的学号及其成绩 • 班主任想查某个学生选修的所有课程的课程号及相应成绩 • 学生想查某个学生选修某门课程的成绩

动态嵌入式SQL简介 • 用于执行时才能确定SQL语句和查询条件 • 动态组装SQL语句 • 动态参数

动态组装SQL语句 • 举例 EXEC SQL BEGIN DECLARE SECTION; Const char * stmt =“ CREATE TABLE test(a int);”; EXEC SQL END DECLARE SECTION; …… EXEC SQL EXECUTE IMMEDIATE :stmt;

动态参数SQL语句 • 举例 EXEC SQL BEGIN DECLARE SECTION; Const char * stmt =“ INSERT INTO test VALUES(?);”; EXEC SQL END DECLARE SECTION; …… EXEC SQL PREPARE mystmt FROM :stmt; EXEC SQL EXECUTE mystmt USING 100; EXEC SQL EXECUTE mystmt USING 200;

第八章

第八章

Presentation Transcript