第八章数据库编程

第八章数据库编程

本章学习内容 • 嵌入式SQL • 游标的使用 • 存储过程的使用 • ODBC编程

一、嵌入式SQL • SQL语言提供了两种不同的使用方式： • 交互式 • 嵌入式 • 为什么要引入嵌入式SQL • SQL语言是非过程性语言 • 事务处理应用需要高级语言 • 主语言 • 将SQL语句嵌入到程序设计语言中，被嵌入的程序设计语言，如C、C++、Java称为宿主语言，简称主语言；

1、嵌入式SQL语句与主语言之间的通信 • 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句 • SQL语句 • 描述性的面向集合的语句 • 负责操纵数据库 • 高级语言语句 • 过程性的面向记录的语句 • 负责控制程序流程 • 它们之间应该如何通信？

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

游标 • 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果； • 每个游标区都有一个名字； • 用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理；

2、建立和关闭数据库连接 • 建立数据库连接 • EXEC SQL CONNECT TO target [AS connection-name] [USER user-name]; • target是要连接的数据库服务器； • connect-name是可选的连接名，连接必须是一个有效的标识符； • 在整个程序内只有一个连接时可以不指定连接名； • 关闭数据库连接 • EXEC SQL DISCONNECT [connection]; • 修改当前连接 • EXEC SQL SET CONNECTION connection-name | DEFAULT;

3、示例 例1：根据学生号码查询学生信息；假定已经把要查询的学生的学号赋给了主变量givensno；执行语句为： EXEC SQL SELECT Sno，Sname，Ssex，Sage，Sdept INTO :Hsno， : Hname ，:Hsex，:Hage，:Hdept FROM Student WHERE Sno=:givensno

说明： • (1) INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量； • (2) 查询结果为空值的处理； • 查询返回的记录中，可能某些列为空值NULL； • 为表示空值，在主变量后面跟有指示变量；当指示变量为负值时，不管主变量为何值，均认为主变量值为NULL； • (3) 指示变量只能用于INTO子句中； • (4)如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，RDBMS会在SQLCA中返回错误信息。

在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量 ；例1：修改某个学生选修1号课程的成绩；假定修改后的成绩已赋给主变量newgrade，学号赋给主变量givensno； EXEC SQL UPDATE SC SET Grade=:newgrade WHERE Sno=:givensno；

例2：某个学生退学了，现要将有关他的所有选课记录删除掉例2：某个学生退学了，现要将有关他的所有选课记录删除掉假设该学生的姓名已赋给主变量stdname； EXEC SQL DELETE from sc where sno = (SELECT sno from student where sname=: stdname)

以上操作主要为： • 查询结果为单记录的SELECT语句 • 非CURRENT（当前记录）形式的增、删、改语句问题： • 查询结果为多条记录的SELECT语句、CURRENT形式的UPDATE和DELETE语句，如何执行？ • ——使用游标；

二、游标的使用 • 一个对表进行操作的T-SQL语句通常都可产生或处理一组记录，但是许多应用程序尤其是T-SQL嵌入到的主语言（如C、VB、PB等开发工具）通常不能把整个结果集作为一个单元来处理，这些应用程序就需要一种机制来保证每次处理结果集中的一行或几行，游标（CURSOR）就提供了这种机制； • 游标可看作一种特殊的指针，与查询结果相联系，指向结果集的任意位置，以便对指定位置的数据进行处理。

使用游标的步骤为： • 声明游标 • 打开游标 • 读取数据 • 关闭游标 • 删除游标

1、声明游标 • 语法格式： EXEC SQL DECLARE <游标名> CURSOR FOR <select 语句> [FOR {read only| update [of column_name]} ] 例1：定义游标，可以查询计算机系所有学生的基本情况； EXEC SQL DECLARE cs_stu CURSOR FOR SELECT sno，sname，ssex FROM student WHERE sdept =‘cs’

例2：定义一个游标，可对计算机系学生姓名列进行修改；例2：定义一个游标，可对计算机系学生姓名列进行修改； EXEC SQL DECLARE cs_stu CURSOR FOR SELECT sno，sname FROM student WHERE sdept =‘cs’ FOR UPDATE of sname

2、打开游标 • 声明游标后，要使用游标从中提取数据，就必须打开游标，打开游标就是执行相应的SELECT语句，把查询结果读取到缓冲区中，此时游标指针指向查询结果集中的第一条记录； • 语法格式： EXEC SQL OPEN <游标名>

3、读取数据 • 游标打开后，就可以使用FETCH语句从中读取数据； • 语法格式为： EXEC SQL FETCH [ [NEXT|PRIOR| FIRST|LAST]FROM] <游标名> INTO <主变量> [<指示变量>][, […n] • 主变量必须与select语句中的目标列表达式一一对应；

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

4、关闭游标 • 游标使用完后，要及时关闭；游标被关闭后，就不再和原理的查询结果集相联系； • 语法格式： EXEC SQL CLOSE <游标名> 5、删除游标 • 语法格式： EXEC SQL DEALLOCATE <游标名>

例：要查询CS系的所有学生的学号、姓名、性别和年龄。例：要查询CS系的所有学生的学号、姓名、性别和年龄。 • 首先定义游标SX，将其与查询结果集（即CS系的所有学生的学号、姓名、性别和年龄）相联系（步骤①）。这时相应的SELECT语句并没有真正执行。 • 然后打开游标SX，这时DBMS执行与SX与相联系的SELECT语句，即查询CS系的所有学生的学号、姓名、性别和年龄（步骤②），之后SX处于活动状态。

接下来在一个循环结构中逐行取结果集中的数据，分别将学号Sno、姓名Sname、性别Ssex和年龄Sage送至主变量HSno、HSname、HSsex和HSage中（步骤③）。主语言语句将对这些主变量做进一步处理。接下来在一个循环结构中逐行取结果集中的数据，分别将学号Sno、姓名Sname、性别Ssex和年龄Sage送至主变量HSno、HSname、HSsex和HSage中（步骤③）。主语言语句将对这些主变量做进一步处理。 • 最后关闭游标SX（步骤④）。这时SX不再与deptname系的学生数据相联系。

三、存储过程的使用 • SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程，是数据库对象之一。 • 存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。 • 存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上执行。

在SQL Server中存储过程分为五类：即系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。 • 系统存储过程：存储在master数据库中，由前缀sp标识。 • 本地存储过程：这是用户在独立的用户数据库中为了完成某一特定功能而编写的存储过程。 • 临时存储过程：它与临时表类似，通常又分为本地和全局临时存储过程两种，当临时存储过程为本地时，其名字以符号#开始，为全局时，以符号##开始。 • 远程存储过程：从远程服务器上调用的存储过程。 • 扩展存储过程：在SQL Server环境之外执行的动态链接库。

存储过程的优点 • 提供了安全机制：让用户通过存储过程操作数据库中的数据，而不让用户直接操作于存储过程相关的表，从而保证数据库中数据的安全性。 • 改进了执行性能：存储过程在第二次执行时，无需预编译，从而改进系统的执行性能。 • 减少了网络流量：存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上运行，用户无需在网络上发送上百个SQL语句，或是将众多数据从服务器下载至客户端后再进行处理，从而大大减少了网络负载。

创建存储过程的步骤及注意事项 • 不能将CREATE PROCEDURE语句与其它SQL语句（如：create view | default | rule）组合到单个批处理中。 • 创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权。 • 存储过程是数据库对象，在命名用户自定义的存储过程时应避免使用sp前缀，以免和系统存储过程混淆。 • 尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而影响系统的执行性能。

主要操作 • 创建和执行用户存储过程 • 修改编辑用户存储过程 • 删除存储过程

1、创建和执行用户存储过程 • 创建存储过程语法格式： CREATE PROCEDURE procedure_name （[参数1 data_type，参数2 data_type,...n ] ） AS sql_statements • 执行存储过程 EXEC procedure_name

1）无参数的存储过程 例1：定义一个存储过程查询数据库stu中每位学生的选课基本情况然后执行该存储过程。步骤如下： /*创建存储过程*/ CREATE PROCEDURE stu_info AS SELECT DISTINCTa.sno,sname,cno,grade FROM student a ,sc WHERE a. sno =sc.sno /*调用存储过程*/ USE stu EXEC stu_info

例2：创建男同学选课情况； /*创建存储过程*/ CREATE procdure male_sc as SELECT DISTINCTa.sno,sname,cno,grade FROM student a ,sc WHERE a. sno =sc.sno and ssex=‘男’ /*调用存储过程*/ EXEC male_sc

2）使用带参数的存储过程 例1：编写一存储过程，根据学生学号查询学生选课基本情况； /*创建存储过程*/ CREATE PROCEDURE stu_info（ @xh char(9)） AS SELECT DISTINCTa.sno,sname,cno,grade FROM student a ,sc WHERE a. sno =sc.sno and a.sno= @xh /*调用存储过程*/ Exec stu_info（‘200515001’）

例2：编写一存储过程，根据院系查询学生选课情况；例2：编写一存储过程，根据院系查询学生选课情况； /*创建存储过程*/ CREATE PROCEDURE stu_sc（@yx char(4) ） as SELECT DISTINCTa.sno,sname,cno,grade FROM student a ,sc WHERE a. sno =sc.sno and sdept= @yx /*调用存储过程*/ Exec stu_sc（‘cs’）

3）对表进行操作的存储过程 • 例1：在student表中插入一条记录； /*创建存储过程*/ CREATE PROCEDURE student_insert （@sno char(9) ,@sname char(8),@ssex char(2),@sage int,@sdept char（4）） as insert into student(sno, sname, ssex, sage, sdept ) values(@sno,@sname,@ssex,@sage,@sdept ) /*调用存储过程*/ EXEC student_insert （‘200515002’,‘王旺’,‘女’,20，‘it‘）

例2：根据学号删除student表中一条记录； /*创建存储过程*/ CREATE PROCEDURE student_delete（@sno char(9)） as delete from student where student.sno=@sno /*调用存储过程*/ EXEC student_delete ‘200515008’

例3：根据学号更改student表记录； CREATE PROCEDURE student_update（@sno char(9) ,@sname char(8),@ssex char(2),@sage int,@sdept char（4）） as update student set sname =@sname, ssex=@ssex, sage=@sage, sdept= @sdept where sno=@sno /*调用存储过程*/ EXEC student_update（‘200515002’,‘王卫’,‘女’,20，‘it‘）

2、修改用户存储过程 • 语法格式： ALTER PROCEDURE procedure_name （[参数1 data_type，参数2 data_type,...n ] ） AS sql_statements • 修改用户存储过程名称 Exec sp_rename <更改前名字>，<更改后名字>

3、删除用户存储过程 • 如果确认一个数据库的某个存储过程与其它对象没有任何依赖关系，则可用DROP PROCEDURE语句永久地删除该存储过程。 • 语法格式： DROP PROCEDURE procedure_name [ ,...n ]

四、ODBC编程 • ODBC产生的原因： • 由于不同的数据库管理系统的存在，在某个RDBMS下编写的应用程序就不能在另一个RDBMS下运行； • 许多应用程序需要共享多个部门的数据资源，访问不同的RDBMS； • ODBC优点： • 移植性好 • 能同时访问不同的数据库 • 共享多个数据资源 • 使得应用系统的开发与数据库平台的选择、数据库设计等工作并行进行

1、ODBC工作原理概述 • ODBC应用系统的体系结构： • 用户应用程序 • 驱动程序管理器 • 数据库驱动程序 • ODBC数据源管理

ODBC数据源管理 • 数据源：是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，是一种数据连接的抽象； • 数据源对最终用户是透明的 • ODBC给每个被访问的数据源指定唯一的数据源名（Data Source Name，简称DSN）,并映射到所有必要的、用来存取数据的低层软件； • 在连接中，用数据源名来代表用户名、服务器名、所连接的数据库名等； • 最终用户无需知道DBMS或其他数据管理软件、网络以及有关ODBC驱动程序的细节；

例：假设在某个学校创建了两个数据库MS SQL Server和KingbaseES：学校人事数据库和教学科研数据库； • 学校的信息系统要从这两个数据库中存取数据； • 为方便与两个数据库连接，为学校人事数据库创建一个数据源名PERSON，为教学科研数据库创建一个名为EDU的数据源； • 当要访问每一个数据库时，只要与PERSON和EDU连接即可,不需要记住使用的驱动程序、服务器名称、数据库名。

2、ODBC工作流程 • 操作步骤： • 配置数据源 • 初始化环境 • 建立连接 • 分配语句句柄 • 执行SQL语句 • 结果集处理 • 中止处理

总结： • 嵌入式SQL • 嵌入式SQL语句与主语言之间的通信 • 建立和关闭数据库连接 • 游标的使用 • 声明、打开、读取、关闭及删除 • 存储过程的使用 • 创建、执行、查看、修改及删除 • ODBC编程 • 工作原理 • 工作流程

第八章 数据库编程