厦门大学计算机科学系本科生课程 《数据库系统原理》

Presentation on theme: "厦门大学计算机科学系本科生课程 《数据库系统原理》"— Presentation transcript:

1 厦门大学计算机科学系本科生课程 《数据库系统原理》
第8章 数据库编程 （2017版） 林子雨 厦门大学计算机科学系 主页： 厦门大学计算机科学系 版

2 第八章 数据库编程 8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程 8.4 JDBC编程

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

4 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 嵌入式SQL语句与主语言之间的通信 不使用游标的SQL语句 使用游标的SQL语句 动态SQL 小结

5 8.1.1 嵌入式SQL的处理过程 主语言 嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。 处理过程 预编译方法

6 嵌入式SQL的处理过程（续） 主语言程序 含ESQL语句 RDBMS的 预处理程序 ESQL语句转换 为函数调用 主语言 编译程序
目标语言程序 ESQL基本处理过程

7 嵌入式SQL的处理过程（续） 为了区分SQL语句与主语言语句， 所有SQL语句必须加前缀EXEC SQL，以(;)结束:
EXEC SQL <SQL语句>;

8 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL语句与主语言之间的通信 8.1.3 不使用游标的SQL语句
8.1.6 小结

9 8.1.2 嵌入式SQL语句与主语言之间的通信 SQL语句 高级语言语句 它们之间应该如何通信？
描述性的面向集合的语句 负责操纵数据库 高级语言语句 过程性的面向记录的语句 负责控制程序流程 它们之间应该如何通信？

10 嵌入式SQL语句与主语言之间的通信（续）
数据库工作单元与源程序工作单元之间的通信： 1. SQL通信区 向主语言传递SQL语句的执行状态信息 使主语言能够据此控制程序流程 2. 主变量 主语言向SQL语句提供参数 将SQL语句查询数据库的结果交主语言进一步处理 3. 游标 解决集合性操作语言与过程性操作语言的不匹配

11 一、SQL通信区 描述系统当前工作状态 SQLCA： SQL Communication Area SQLCA的用途
SQL语句执行后，RDBMS反馈给应用程序信息 描述系统当前工作状态 描述运行环境 这些信息将送到SQL通信区SQLCA中 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句

12 SQL通信区 SQLCA使用方法： 定义SQLCA 用EXEC SQL INCLUDE SQLCA定义 使用SQLCA
SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错 sqlerrmc用来保留与sqlcode中的状态码对应 的错误信息文本 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理 备注：在C语言中，被存放在sqlca.h的SQLCA结构体中 嵌入SQL语句的C程序都必须要加入一条语句，即EXEC SQL INCLUDE sqlca.h

13 二、主变量 主变量 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据
在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）

14 主变量（续） 主变量的类型 输入主变量 输出主变量 一个主变量有可能既是输入主变量又是输出主变量

15 主变量（续） 指示变量： 一个主变量可以附带一个指示变量（Indicator Variable） 什么是指示变量 指示变量的用途
一个整形变量，用来指示所指主变量的值或条件 指示变量的用途

16 在SQL语句中使用主变量和指示变量的方法
主变量（续） 在SQL语句中使用主变量和指示变量的方法 1) 说明主变量和指示变量 BEGIN DECLARE SECTION (说明主变量和指示变量) END DECLARE SECTION

17 主变量（续） C语言中的主变量声明

18 主变量（续） 2) 使用主变量 3) 使用指示变量 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现
指示变量前也必须加冒号标志 必须紧跟在所指主变量之后

19 在SQL语句之外(主语言语句中)使用主变量和指示变量的方法
主变量（续） 在SQL语句之外(主语言语句中)使用主变量和指示变量的方法 可以直接引用，不必加冒号

20 三、游标（cursor） 为什么要使用游标 SQL语言与主语言具有不同数据处理方式
SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录 主语言是面向记录的，一组主变量一次只能存放一条记录 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求 嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式

21 游标（续） 游标 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果 每个游标区都有一个名字

22 游标（续）

23 EXEC SQL CONNECT TO target [AS connection-name] [USER user-name];
四、建立和关闭数据库连接 建立数据库连接 EXEC SQL CONNECT TO target [AS connection-name] [USER user-name]; target是要连接的数据库服务器 关闭数据库连接 EXEC SQL DISCONNECT [connection];

24 C语言编写嵌入式SQL实例（1）

25 C语言编写嵌入式SQL实例(2)

26 C语言编写嵌入式SQL实例(3) 通知

27 C语言编写嵌入式SQL实例(4)

28 C语言编写嵌入式SQL实例(5)

29 六、编译运行C程序 嵌入SQL的C应用程序具体到VC++6.0、SQL Server2000下调试可分为五步： (1)环境初始化 (2)预编译 (3)编译 (4)链接 (5)运行

30 六、编译运行C程序-1.环境初始化 (1)SQL Server为其嵌入式SQL提供了一些特殊的接口；默认的安装方式并没有安装这些接口；因此，需要把devtools.rar解压到SQL Server的系统目录下；如果操作系统安装在C盘，则SQL Server的系统目录是：C:\Program Files\Microsoft SQL Server。 (2)初始化Visual C++6.0编译器环境。在命令行方式下运行文件：\Microsoft Visual Studio\VC98\Bin\vcvars32.bat。

31 六、编译运行C程序-1.环境初始化 (3)初始化SQL Server的预编译环境。在命令行方式下运行文件：\Devtools\samples\esplc\setenv.bat。 (4)VC++6.0环境配置。具体配置分为如下三步： [1]Tools->options->directories->Include Files: C:\Program Files\Microsoft SQL Server\devtools\include [2]Tools->options->directories->Lib Files: C:\Program Files\Microsoft SQL Server\devtools\x86lib [3]Project->Settings->Link->Object/Library Modules，添加库文件：SQLakw32.lib，Caw32.lib。这两个文件之间用空格分开。

32 六、编译运行C程序-2.预编译 C语言编译程序不能识别应用程序中的SQL语句，需要经过预处理程序将其转换成C语句。SQL Server的预处理程序是nsqlprep.exe。nsqlprep.exe在SQL Server安装目录的MSSQL\Binn下。若SQL Server数据库采用的是默认安装方式，则需要把binn.rar的内容拷贝到指定目录下。nsqlprep.exe的常用语法格式与相关说明见表3。

33 六、编译运行C程序-3.编译、链接与运行 在VC++6.0中创建一个“WIN32 Console Application”类型的Project，然后将预编译生成的c文件加入Project，编译链接即可生成访问SQL Server的可执行程序。Visual C++6.0进行编译连接时需要用到动态链接库SQLakw32.dll与SQLaiw32.dll；尽管这两个文件已经随同binn.rar被拷贝到 SQL Server安装目录的MSSQL\Binn文件夹下，但仍然需要把它们的路径加到系统路径变量中，以使得程序运行时能找到它们，具体添加方法如下：     方法1：把这两个文件拷贝到操作系统目录下的system32子目录中。     方法2：“我的电脑”->“属性”->“高级”->“环境变量”->“path，编辑”，在变量值中加入路径值；新路径已有路径间用“；”间隔。

34 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL语句与主语言之间的通信 8.1.3 不使用游标的SQL语句
8.1.6 小结

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

36 不用游标的SQL语句（续） 一、查询结果为单记录的SELECT语句 二、非CURRENT形式的增删改语句

37 这类语句不需要使用游标，只需要用INTO子句指定存放查询结果的主变量
一、查询结果为单记录的SELECT语句 这类语句不需要使用游标，只需要用INTO子句指定存放查询结果的主变量 [例2] 根据学生号码查询学生信息。假设已经把要查询的学生的学号赋给了主变量givensno。 EXEC SQL SELECT Sno，Sname，Ssex，Sage，Sdept INTO :Hsno，:Hname，:Hsex，:Hage，:Hdept FROM Student WHERE Sno=:givensno；

38 查询结果为单记录的SELECT语句（续）
(1) INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量 (2)查询返回的记录中，可能某些列为空值NULL。 (3) 如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，RDBMS会在SQLCA中返回错误信息

39 指示变量（补充） 指示变量是与宿主变量相关联的一类SQL变量，它被用来监督和管理与其相关联的宿主变量，每一个宿主变量都可以定义一个指示变量。指示变量的具体作用如下： （1）       向数据库表列输入NULL值 （2）       检查从数据库表列中选取的数据是否是NULL值，或是否发生截断问题

40 指示变量（补充） 指示变量的值，及其代表的含义如下： 0 操作成功 -1 该指示变量对应的宿主变量返回了或插入、更新成了NULL 值
-2 从数据库存放数据到对应的宿主变量时，数据超长，并且不能推断出截断了多少字节的长度 >0 在FETHC 或SELECT 语句时，因数据超长而被截断存放在了对应的宿主变量中，指示变量存放对应列的长度

41 查询结果为单记录的SELECT语句（续）
[例3] 查询某个学生选修某门课程的成绩。假设已经把将要查询的学生的学号赋给了主变量givensno，将课程号赋给了主变量givencno。 EXEC SQL SELECT Sno，Cno，Grade INTO :Hsno，:Hcno，:Hgrade:Gradeid /*指示变量Gradeid*/ FROM SC WHERE Sno=:givensno AND Cno=:givencno； 如果Gradeid < 0，不论Hgrade为何值，均认为该学生成绩为空值。

42 二、非CURRENT形式的增删改语句 在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量
[例4] 修改某个学生选修1号课程的成绩。 EXEC SQL UPDATE SC SET Grade=:newgrade /*修改的成绩已赋给主变量*/ WHERE Sno=:givensno； /*学号赋给主变量givensno*/

43 非CURRENT形式的增删改语句（续） [例5] 将计算机系全体学生年龄置NULL值。 Sageid=-1； EXEC SQL UPDATE Student SET Sage=:Raise :Sageid WHERE Sdept= ‘CS’； 将指示变量Sageid赋一个负值后，无论主变量Raise为何值，RDBMS都会将CS系所有学生的年龄置空值 。 等价于： SET Sage=NULL WHERE Sdept= 'CS'；

44 非CURRENT形式的增删改语句（续） [例6] 某个学生退学了，现要将有关他的所有选课记录删除掉。假设该学生的姓名已赋给主变量stdname。 EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname)；

45 非CURRENT形式的增删改语句（续） [例7] 某个学生新选修了某门课程，将有关记录插入SC表中。假设插入的学号已赋给主变量stdno，课程号已赋给主变量couno。 gradeid=-1； /*用作指示变量，赋为负值*/ EXEC SQL INSERT INTO SC(Sno，Cno，Grade) VALUES(:stdno，:couno，:gr :gradeid)； 由于该学生刚选修课程，成绩应为空，所以要把指示变量赋为负值

46 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL语句与主语言之间的通信 8.1.3 不使用游标的SQL语句
8.1.6 小结

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

48 一、 查询结果为多条记录的SELECT语句
使用游标的步骤 1. 说明游标 2. 打开游标 3.推进游标指针并取当前记录 4. 关闭游标

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

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

51 EXEC SQL FETCH [[NEXT|PRIOR|
3.推进游标指针并取当前记录 使用FETCH语句 语句格式 EXEC SQL FETCH [[NEXT|PRIOR| FIRST|LAST] FROM] <游标名> INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...;

52 推进游标指针并取当前记录（续） 功能 指定方向推动游标指针，然后将缓冲区中的当前记录取出来送至主变量供主语言进一步处理
NEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式 NEXT：向前推进一条记录 PRIOR：向回退一条记录 FIRST：推向第一条记录 LAST：推向最后一条记录 缺省值为NEXT

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

54 二、CURRENT形式的UPDATE语句和DELETE语句
面向集合的操作 一次修改或删除所有满足条件的记录

55 CURRENT形式的UPDATE语句和DELETE语句(续)
如果只想修改或删除其中某个记录 用带游标的SELECT语句查出所有满足条件的记录 从中进一步找出要修改或删除的记录 用CURRENT形式的UPDATE语句和DELETE语句修改或删除之 UPDATE语句和DELETE语句中的子句： WHERE CURRENT OF <游标名> 表示修改或删除的是最近一次取出的记录，即游标指针指向的记录

56 CURRENT形式的UPDATE语句和DELETE语句(续)
当游标定义中的SELECT语句带有UNION或ORDER BY子句 该SELECT语句相当于定义了一个不可更新的视图

57 CURRENT形式的UPDATE语句和DELETE语句(续)
SQL Server当中的实例： DECLARE My_Cursor CURSOR --定义游标 FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游标中 OPEN My_Cursor; --打开游标 FETCH NEXT FROM My_Cursor ; --读取第一行数据   BEGIN   --UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新  --DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除  FETCH NEXT FROM My_Cursor; --读取下一行数据  END CLOSE My_Cursor; --关闭游标 DEALLOCATE My_Cursor; --释放游标

58 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL语句与主语言之间的通信 8.1.3 不使用游标的SQL语句
8.1.6 小结

59 8.1.5 动态SQL 静态嵌入式SQL 动态嵌入式SQL 静态嵌入式SQL语句能够满足一般要求

60 动态SQL简介（续） 一、使用SQL语句主变量 二、动态参数

61 一、使用SQL语句主变量 SQL语句主变量： 程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量

62 使用SQL语句主变量（续） [例9] 创建基本表TEST EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test(a int);"; /* SQL语句主变量 */ EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :stmt; /* 执行语句 */

63 二、动态参数 动态参数 SQL语句中的可变元素 使用参数符号(?)表示该位置的数据在运行时设定 和主变量的区别
动态参数的输入不是编译时完成绑定 而是通过 (prepare)语句准备主变量和执行(execute)时绑定数据或主变量来完成

64 使用动态参数的步骤： 动态参数（续） 1.声明SQL语句主变量。 2.准备SQL语句(PREPARE)。
EXEC SQL PREPARE <语句名> FROM <SQL语句主变量>; 3.执行准备好的语句(EXECUTE) EXEC SQL EXECUTE <语句名> [INTO <主变量表>] [USING < 主变量或常量>];

65 动态参数（续） [例10]向TEST中插入元组。 EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test VALUES(?);"; /*声明SQL主变量 */ EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE mystmt FROM :stmt; /* 准备语句 */ EXEC SQL EXECUTE mystmt USING 100; /* 执行语句 */ EXEC SQL EXECUTE mystmt USING 200; /* 执行语句 */

66 8.1 嵌入式SQL 8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL语句与主语言之间的通信 8.1.3 不使用游标的SQL语句
8.1.6 小结

67 在嵌入式SQL中，SQL语句与主语言语句分工非常明确
8.1.6 小 结 在嵌入式SQL中，SQL语句与主语言语句分工非常明确 SQL语句 直接与数据库打交道，取出数据库中的数据。 主语言语句 控制程序流程 对取出的数据做进一步加工处理

68 小结（续） SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录 主语言是面向记录的，一组主变量一次只能存放一条记录

69 第八章 数据库编程 8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程

70 SQL-invoked routines：
8.2 存储过程 SQL-invoked routines： 存储过程(SQL-invoked procedure) 函数(SQL-invoked function)

71 8.2 存储过程 8.2.1 Transact-SQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存储过程
本课程教学以SQL Server为例 Transact-SQL的块结构 变量常量的定义 控制结构 存储过程 小结

72 8.2.1 Transact-SQL的块结构 Transact-SQL ： SQL的扩展 增加了过程化语句功能 基本结构是块
块之间可以互相嵌套 每个块完成一个逻辑操作

73 8.2.1 Transact-SQL的块结构 Transact-SQL块的基本结构： 定义的变量、常量等只能在该基本块中使用
1.定义部分 DECLARE ------变量、常量、游标、异常等 定义的变量、常量等只能在该基本块中使用 当基本块执行结束时，定义就不再存在

74 Transact-SQL的基本结构(续)：
2.执行部分 BEGIN ------SQL语句、 Transact-SQL的流程控制语句 END；

75 8.2.2 变量常量的定义 变量的声明： 通过DECLARE 语句操作初始化 Transact-SQL 变量，语法格式为：
变量常量的定义 变量的声明： 通过DECLARE 语句操作初始化 Transact-SQL 变量，语法格式为： 数据类型; 注意： 指定系统提供的或用户定义的数据类型和长度 将值设置为 NULL 例如： int; 如果需要同时声明多个局部变量，使用逗号隔开

76 变量常量的定义 例如： nchar(2); 可以通过set来设置局部变量的初值。 局部变量通常用于计数器（计算循环执行次数或者控制循环执行次数）、保存数值以供控制流语句测试、保存存储过程返回代码要返回的数值或者函数的返回值。

77 8.2.2 变量常量的定义 T-SQL里的全局变量以@@开头，由系统维护，不可以被创建。 常见全局变量：
变量常量的定义 常见全局变量：

78 8.2.3 控制结构 T-SQL中的流程控制语句是指用来控制程序执行和流程分支的命令，主要用于控制SQL语句、语句块或者存储过程执行流程。
控制结构 T-SQL中的流程控制语句是指用来控制程序执行和流程分支的命令，主要用于控制SQL语句、语句块或者存储过程执行流程。  几个主要的流程控制语句： BEGIN…END语句 IF…ELSE语句 WHILE语句 CASE语句

79 控制结构 BEGIN…END语句 将多个T-SQL语句组成一个语句块，视作一个单元处理。语法格式为： begin {sql_statement | statement_block} end 详见：

80 控制结构 IF…ELSE语句 判断语句，可以没有ELSE字句部分，可嵌套。 语法格式为： if boolean_expression {sql_statement | statement_block} [ else {sql_statement|statement_block} ] 详见： BEGIN …… END ELSE

81 8.2.3 控制结构 WHILE语句 用于重复执行SQL语句，可内嵌BREAK和CONTINUE。 语法格式为：
控制结构  WHILE语句 用于重复执行SQL语句，可内嵌BREAK和CONTINUE。 语法格式为： WHILE Boolean_expression { sql_statement | statement_block | BREAK | CONTINUE } 详见： WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN …… END

82 8.2.3 控制结构 CASE语句 可以计算多个条件，并将其中一个符合条件的结果表达式返回。 语法格式为：
控制结构 CASE语句 可以计算多个条件，并将其中一个符合条件的结果表达式返回。 语法格式为： CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END 或者Searched CASE expression: CASE     WHEN Boolean_expression THEN result_expression [ ...n ] 详见： CASE应用例子：

83 控制结构 CASE语句实例

84 8.2 存储过程 8.2.1 Transact-SQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存储过程
变量常量的定义 控制结构 存储过程 小结

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

86 存储过程（续） 一、 存储过程的优点 二、 存储过程的用户接口 三、 游标

87 存储过程（续） 1. 运行效率高 2. 降低了客户机和服务器之间的通信量 3. 方便实施企业规则
存储过程：由Transact-SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可 一、存储过程的优点： 1. 运行效率高 2. 降低了客户机和服务器之间的通信量 3. 方便实施企业规则

88 存储过程（续） 二、 存储过程的用户接口： 1. 创建存储过程 2. 执行存储过程 3. 删除存储过程

89 存储过程（续） 创建存储过程 CREATE PROCEDURE sp_Select_All_Employees AS SELECT employeeid,firstname,lastname FROM Employees ORDER BY lastname , firstname

90 存储过程（续） 创建一个带参数的存储过程 CREATE PROCEDURE int AS SELECT orderid , customerid FROM orders WHERE employeeid /* 为输入参数，且后面为该参数类型 */

91 存储过程（续） 创建一个传入参数、返回值和输出参数的存储过程 CREATE PROCEDURE int = 0 output AS SELECT orderid,customerid FROM orders WHERE employeeid = count(*) WHERE employeeid

92 存储过程（续） 修改存储过程 ALTER PROCEDURE --存储程序名称 --改变的参数 AS --改变后的T-SQL语句 查看存储过程 execute sp_helptext --存储过程名称 重命名存储过程 EXECUTE sp_rename --需要改的存储过程名称 --改后的存储过程名称 调用存储过程 EXECUTE sp_Orders_By_EmployeeId2

93 三、游 标 在Transact-SQL中，如果SELECT语句只返回一条记录，可以将该结果存放到变量中。
当查询返回多条记录时，就要使用游标对结果集进行处理 一个游标与一个SQL语句相关联。 Transact-SQL中的游标由Transact-SQL引擎管理

94 Transact-SQL游标的概念 游标(Cursor)使用户可逐行访问由SQL Server返回的结果集 使用游标的一个主要的原因就是把集合操作转换成单个记录处理方式 用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合 游标机制允许用户在SQL Server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录

95 游标的优点 允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作 提供对基于游标位置的表中的行进行删除和更新的能力  游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来

96 游标的使用 使用游标的顺序 声名游标 打开游标 读取数据 关闭游标 删除游标

97 声明游标 最简单游标声明： DECLARE <游标名> CURSOR FOR <SQL语句>； 其中，select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询 例子： declare cursor_stu cursor for select * from Student 这样就对表Student表声明了一个游标cursor_stu

98 打开游标 语法格式：OPEN <游标名> OPEN cursor_stu

99 读取数据 FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  } [ [，…] ]  参数说明： NEXT   取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项 把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致 

100 读取数据 当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据： Fetch next from cursor_stu 或 Fetch first from cursor_stu

101 读取数据 这样就取出了游标里的数据，还需要将取出的数据赋给变量 //声明2个变量 NVARCHAR(5) char(8) //将取出的值传入刚才声明的2个变量 Fetch next from cursor_stu into

102 关闭游标和删除游标 CLOSE <游标名> 实例：close cursor_stu DEALLOCATE <游标名> 实例：deallocate cursor_stu

103 实例 为了运行创建的游标，可以将游标写在存储过程里，方便我们看到游标的整个使用过程，在sqlserver2008中新建一个存储过程：
---在存储过程中使用游标--- create procedure cursor_test as ---声明两个变量 VARCHAR(5) char(8) ---声明一个游标 declare cursor_stu cursor for select sno,sname from Student ---打开游标 OPEN cursor_stu --从游标里取出数据赋值到我们刚才声明的2个变量中 Fetch next from cursor_stu into --后面代码见下一页PPT

104 实例 --续上一页PPT --判断游标的状态 --0 fetch语句成功 ---1 fetch语句失败或此行不在结果集中
---2 被提取的行不存在 while begin --显示出我们每次用游标取出的值 print '游标成功取出一条数据' --用游标去取下一条记录 fetch next from cursor_stu into end --关闭游标 close cursor_stu deallocate cursor_stu

105 8.2 存储过程 8.2.1 Transact-SQL的块结构 变量常量的定义 控制结构 存储过程 小结

106 小 结 存储过程的优点 经编译和优化后存储在数据库服务器中，运行效率高 降低客户机和服务器之间的通信量 有利于集中控制，方便维护

107 第八章 数据库编程 8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程

108 8.3 ODBC编程 ODBC优点： 移植性好 能同时访问不同的数据库 共享多个数据资源

109 8.3 ODBC编程 8.3.1 数据库互连概述 8.3.2 ODBC工作原理概述 8.3.3 ODBC API 基础
数据库互连概述 ODBC工作原理概述 ODBC API 基础 ODBC的工作流程 小结

110 数据库互连概述 ODBC产生的原因： 由于不同的数据库管理系统的存在，在某个RDBMS下编写的应用程序就不能在另一个RDBMS下运行 许多应用程序需要共享多个部门的数据资源，访问不同的RDBMS

111 数据库互连概述（续） ODBC： ODBC约束力：
是微软公司开放服务体系(Windows Open Services Architecture，WOSA)中有关数据库的一个组成部分 提供了一组访问数据库的标准API ODBC约束力： 规范应用开发 规范RDBMS应用接口

112 8.3 ODBC编程 数据库互连概述 ODBC工作原理概述 小结

113 8.3.2 ODBC工作原理概述 ODBC应用系统的体系结构 ： 一、 用户应用程序 二、 驱动程序管理器 三、 数据库驱动程序

114 一、 应用程序 请求连接数据库； 向数据源发送SQL语句； 为SQL语句执行结果分配存储空间，定义所读取的数据格式；
ODBC应用程序包括的内容: 请求连接数据库； 向数据源发送SQL语句； 为SQL语句执行结果分配存储空间，定义所读取的数据格式； 获取数据库操作结果，或处理错误； 进行数据处理并向用户提交处理结果； 请求事务的提交和回滚操作； 断开与数据源的连接。

115 二、驱动程序管理器 驱动程序管理器：用来管理各种驱动程序 包含在ODBC32.DLL中 管理应用程序和驱动程序之间的通信
主要功能： 装载ODBC驱动程序 选择和连接正确的驱动程序 管理数据源 检查ODBC调用参数的合法性 记录ODBC函数的调用等

116 三、数据库驱动程序 ODBC通过驱动程序来提供应用系统与数据库平台的独立性 ODBC应用程序不能直接存取数据库
其各种操作请求由驱动程序管理器提交给某个RDBMS的ODBC驱动程序 通过调用驱动程序所支持的函数来存取数据库。 数据库的操作结果也通过驱动程序返回给应用程序。 如果应用程序要操纵不同的数据库，就要动态地链接到不同的驱动程序上。

117 数据库驱动程序（续） ODBC驱动程序类型： 单束 多束 数据源和应用程序在同一台机器上 驱动程序直接完成对数据文件的I/O操作
驱动程序相当于数据管理器 多束 支持客户机/服务器、客户机/应用服务器/数据库服务器等网络环境下的数据访问 由驱动程序完成数据库访问请求的提交和结果集接收 应用程序使用驱动程序提供的结果集管理接口操纵执行后的结果数据

118 8.3.3 小结 ODBC目的：为了提高应用系统与数据库平台的独立性，使得应用系统的移植变得容易 ODBC优点：
小结 ODBC目的：为了提高应用系统与数据库平台的独立性，使得应用系统的移植变得容易 ODBC优点： 使得应用系统的开发与数据库平台的选择、数据库设计等工作并行进行 方便移植 大大缩短整个系统的开发时间

119 Eclipse连接SQL Server 2008数据库
8.4 JDBC编程 JDBC（Java Data Base Connectivity,Java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序 Eclipse连接SQL Server 2008数据库

120 Eclipse连接SQL Server 2008数据库
在连接数据库之前必须保证SQL Server 2008是采用SQL Server身份验证方式而不是windows身份验证方式。 SQL Server 2008装好后，默认协议是没有开启的，可打开配置工具下的 SQL Server配置管理器中开启；

121 Eclipse连接SQL Server 2008数据库

122 Eclipse连接SQL Server 2008数据库
设置sa的密码并启用sa登录名

123 Eclipse连接SQL Server 2008数据库

124 Eclipse连接SQL Server 2008数据库
验证sa登录 在桌面上新建一个文本文件，文件名为TestSQL.UDL，双击这个文件打开“数据链接属性”对话框，点击“提供程序”选项卡，如右图： OLE DB（OLEDB）是微软的战略性的通向不同的数据源的低级应用程序接口。OLE DB不仅包括微软资助的标准数据接口开放数据库连通性（ODBC）的结构化查询语言（SQL）能力，还具有面向其他非SQL数据类型的通路。 作为微软的组件对象模型（COM）的一种设计，OLE DB是一组读写数据的方法（在过去可能被称为渠道）。OLE DB中的对象主要包括数据源对象、阶段对象、命令对象和行组对象。

125 Eclipse连接SQL Server 2008数据库
点击“下一步”按钮进入“高级”选项卡，输入数据源为：local，即我们安装的SQL Server 2008实例，并选择“使用指定的用户名称和密码”，输入登录名sa和sa的密码，最后，我们点击“测试连接”按钮，测试sa登录。可以看到连接成功！为了下一次使用方便，勾选“允许保存密码”，最后，点击“确定”按钮保存这个数据链接。

126 Eclipse连接SQL Server 2008数据库

127 Eclipse连接SQL Server 2008数据库
必须保证SQL Server 2008是采用SQL Server身份验证方式下开始配置 1、安装好SQL Server 2008后，运行 开始 → 所有程序 → Microsoft SQL Server 2008 → 配置工具 → SQL Server配置管理器，在打开的窗口的左边找到 MSSQLSERVER的协议，在右边右单击 TCP/IP，选择 已启用。如果Named Pipes 未启用也设为启用。

128 Eclipse连接SQL Server 2008数据库
图1

129 Eclipse连接SQL Server 2008数据库
图2

130 Eclipse连接SQL Server 2008数据库
2、双击图2右边的 TCP/IP，在弹出的窗口中选择 IP地址 标签，把 IPAll 中的 TCP端口 设成 1433，并将上方所有的 已启用 选项设置成 是。如图3所示： 图3

131 Eclipse连接SQL Server 2008数据库
3、然后重启数据库，重启完毕后，接下来使用命令测试1433端口是否打开。开始菜单 → 运行cmd → 在命令提示符下输入：telnet   1433，（注意telnet与127之间有空格，1与1433之间有空格）若提示“不能打开到主机的连接，在端口 1433: 连接失败”，则说明1433端口没有打开，需要重新进行以上配置。若连接成功，显示如图4所示：

132 Eclipse连接SQL Server 2008数据库
图4

133 Eclipse连接SQL Server 2008数据库
补充：如何设置开启win7中的telnet服务 打开控制面板 在控制面板中选择“程序”项，打开程序设置页面 选择“打开或关闭windows功能”项，打开window功能设置窗口 将其中的“telnet服务器”、“telnet客户端”前面打上勾。 默认是没有打上勾的，所以打上勾后，点击“确定”完成开启

134 Eclipse连接SQL Server 2008数据库
三、环境变量CLASSPATH配置 1、下载Microsoft JDBC Driver for SQL Server 可以网络搜索找到最新版本驱动 下载JDBC的驱动文件，例如下载得到的文件是sqljdbc_ _chs.exe ，解压文件。假设将其解压到C:\Microsoft JDBC Driver 6.0 for SQL Server目录下。

135 Eclipse连接SQL Server 2008数据库
2、在桌面上右击 我的电脑（见图5），依次选择 属性 → 高级 → 环境变量，在 系统变量中双击 CLASSPATH变量，追加“; C:\SQL Server 2008 JDBC Driver\sqljdbc_6.0\chs\jre8\sqljdbc42.jar” 若不存在，应当新建CLASSPATH变量，并且将其值设为“C:\SQL Server 2008 JDBC Driver\sqljdbc_6.0\chs\jre8\sqljdbc42.jar”

136 Eclipse连接SQL Server 2008数据库
图5

137 Eclipse连接SQL Server 2008数据库
1、打开SQL Server 2008，在其中新建数据库 Test，然后退出SQL Server 2008 2、运行Eclipse，新建一个Java Project 名为 Test 3、右单击工程名，依次选择 Build Path → Configure Build Path，在打开的窗口的右边选择 Libraries标签，然后单击 Add External JARs，找到 sqljdbc42.jar 文件并打开，然后单击 OK 完成构建路径的配置

138 Eclipse连接SQL Server 2008数据库
4、在Test中新建包pkg，在pkg中新建一个类MyTestMain，在其中输入代码如下： package pkg import java.sql.*; public class MyTestMain { public static void main(String [] args) { String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver"; String dbURL="jdbc:sqlserver://localhost:1433;DatabaseName=test"; String userName="sa"; String userPwd="123456“; 剩余代码见下一页

139 Eclipse连接SQL Server 2008数据库
try{ Connection dbConn=DriverManager.getConnection(dbURL,userName,userPwd); System.out.println("连接数据库成功！"); String sql1 ="create table sc2(sno char(5),cno char(1), grade int)"; String sql2="insert into sc2(sno,cno,grade) values('95888','3',88)"; String sql3="select sno,cno,grade from sc2"; Statement stmt = dbConn.createStatement(); stmt.execute(sql1); stmt.execute(sql2); ResultSet rs=stmt.executeQuery(sql3); while(rs.next()){ String sno = rs.getString(1); String cno = rs.getString(2); int grade = rs.getInt(3); System.out.println("学号:"+sno+"课程:"+cno+"成绩:"+grade); } dbConn.close(); 剩余代码见下一页

140 Eclipse连接SQL Server 2008数据库
catch(Exception e) { e.printStackTrace(); System.out.print("SQL Server连接失败！"); }

141 Eclipse连接SQL Server 2008数据库
5、点击右键，选择run as——>Java Application,出现下图6则连接成功！

142 附录：主讲教师 主讲教师：林子雨 单位：厦门大学计算机科学系 E-mail: ziyulin@xmu.edu.cn
个人网页： 数据库实验室网站： 扫一扫访问个人主页 林子雨，男，1978年出生，博士（毕业于北京大学），现为厦门大学计算机科学系助理教授（讲师），曾任厦门大学信息科学与技术学院院长助理、晋江市发展和改革局副局长。中国计算机学会数据库专业委员会委员，中国计算机学会信息系统专业委员会委员，荣获“2016中国大数据创新百人”称号。中国高校首个“数字教师”提出者和建设者，厦门大学数据库实验室负责人，厦门大学云计算与大数据研究中心主要建设者和骨干成员，2013年度厦门大学奖教金获得者。主要研究方向为数据库、数据仓库、数据挖掘、大数据、云计算和物联网，并以第一作者身份在《软件学报》《计算机学报》和《计算机研究与发展》等国家重点期刊以及国际学术会议上发表多篇学术论文。作为项目负责人主持的科研项目包括1项国家自然科学青年基金项目(No )、1项福建省自然科学青年基金项目(No.2013J05099)和1项中央高校基本科研业务费项目(No )，同时，作为课题负责人完成了国家发改委城市信息化重大课题、国家物联网重大应用示范工程区域试点泉州市工作方案、2015泉州市互联网经济调研等课题。中国高校首个“数字教师”提出者和建设者，2009年至今，“数字教师”大平台累计向网络免费发布超过100万字高价值的研究和教学资料，累计网络访问量超过100万次。打造了中国高校大数据教学知名品牌，编著出版了中国高校第一本系统介绍大数据知识的专业教材《大数据技术原理与应用》，并成为京东、当当网等网店畅销书籍；建设了国内高校首个大数据课程公共服务平台，为教师教学和学生学习大数据课程提供全方位、一站式服务，年访问量超过50万次。具有丰富的政府和企业信息化培训经验，厦门大学管理学院EDP中心、浙江大学管理学院EDP中心、厦门大学继续教育学院、泉州市科技培训中心特邀培训讲师，曾给中国移动通信集团公司、福州马尾区政府、福建龙岩卷烟厂、福建省物联网科学研究院、石狮市物流协会、厦门市物流协会、浙江省中小企业家、四川泸州企业家、江苏沛县企业家等开展信息化培训，累计培训人数达3000人以上。

143 附录：课程助教 助教：魏亮 单位：厦门大学计算机科学系数据库实验室2016级硕士研究生 E-mail: 851189929@qq.com
助教：曾冠华 单位：厦门大学计算机科学系数据库实验室2016级硕士研究生

144 附录：班级网站 林子雨主讲《数据库系统原理》2017班级主页 http://dblab.xmu.edu.cn/post/7657/
扫一扫访问班级网站 支持手机浏览

145 Department of Computer Science, Xiamen University, 2017
实时主动数据仓库相关问题研究 Department of Computer Science, Xiamen University, 2017