第08章 数据库编程(2).ppt

1、数据库系统概论 An Introduction to Database System 第八章 数据库编程 (2),复 习,SQL和主语言之间的三种通信方式？ 不用游标的SQL语句有哪几种？ 使用游标的SQL语句有哪几种？,说明性语句、数据定义语句、数据控制语句 查询结果为单记录的SELECT语句 INSERT语句 非CURRENT形式的UPDATE或DELETE语句,查询结果为多条记录的SELECT语句 CURRENT形式的UPDATE或DELETE语句,2.CURRENT形式的UPDATE和DELETE语句,(1)CURRENT形式的UPDATE语句和DELETE语句的用途 非CURRENT

2、形式的UPDATE和DELETE语句 面向集合的操作 一次修改或删除所有满足条件的记录,如果只想修改或删除其中某个记录 用带游标的SELECT语句查出满足条件的记录 从中进一步找出要修改或删除的某条记录 用CURRENT形式的UPDATE语句和DELETE语句修改或删除,2.CURRENT形式的UPDATE和DELETE语句,(2)步骤 DECLARE 说明游标 OPEN 打开游标，把满足查询条件的记录从表取至缓冲区 FETCH 推进游标指针，并把当前记录从缓冲区中取出来送至主变量,2.CURRENT形式的UPDATE和DELETE语句,(2)步骤 检查该记录是否是要修改或删除的记录，是则处理

3、；,UPDATE语句或DELETE语句中要用子句 WHERE CURRENT OF 重复第(3)和(4)步, CLOSE 关闭游标，释放结果集占用的资源,2.CURRENT形式的UPDATE和DELETE语句,注意： 当游标定义中的SELECT语句带有UNION或ORDER BY子句时，不能使用CURRENT形式的UPDATE语句和DELETE语句。,因为这样相当于定义一个不可更新的视图。,WHY？,例对某个系的学生信息(待查系名由主变量deptname指定) ，根据用户的要求删除其中某些记录。 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SE

4、CTION; /* 说明主变量 deptname,HSno,HSname等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR /* 说明游标 */ SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname;,2.CURRENT形式的UPDATE和DELETE语句,EXEC SQL OPEN SX /* 打开游标 */ WHILE(1) EXEC SQL FETCH SX IN

5、TO :HSno, :HSname, :HSsex; IF (sqlca.sqlcode SUCCESS) break; printf(“%s, %s, %s, %d”, Sno, Sname, Ssex); printf(DELETE ? ); scanf(%c, /* 关闭游标 */ ,Student,WHERE CURRENT OF SX,8.1 嵌入式SQL,8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL与主语句之间通信 8.1.3 不用游标的SQL语句 8.1.4 使用游标的SQL语句 8.1.5 动态SQL简介,1.静态SQL 概念：SQL语句中主变量的个数与数据类型

6、在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的。 例如： SELECT Sno, Sname, Ssex,Sage FROM student WHERE Sdept=deptn,8.1.5 动态SQL简介,1.静态SQL 优点： 用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。 缺点： 不能编写更为通用的程序。 如：查询条件、要查询的属性列均不确定时，就无法用一条静态SQL语句实现。,8.1.5 动态SQL简介,【例如】对SC表，有如下查询要求： 任课教师想查选修某门课程的所有学生的学号及其成绩； 班主任想查某个学生选修的所有课程的课程号

7、及相应成绩； 学生想查某个学生选修某门课程的成绩。,8.1.5 动态SQL简介,2.动态SQL： 允许在程序运行过程中临时“组装”SQL语句。 (1)概念： 在预编译时SQL语句正文 主变量个数 主变量的数据类型 SQL语句中引用的数据库对象 （包括：列、索引、基本表、视图等） 等信息不能确定时。,8.1.5动态SQL简介,2.动态SQL： (2)三种形式 语句可变： 临时构造完整的SQL语句。 条件可变： WHERE子句中或 HAVING短语中的条件 数据库对象、查询条件均可变：,8.1.5动态SQL简介,SELECT、FROM、WHERE和HAVING用户临时构造。 即语句的输入/输出都是

8、不确定的。,例一个游标的具体使用,declare s_c1 SCROLL cursor for select * from sc where cno=2 open s_c1 print fetch_status declare sno char(8),cno char(8),grade int While fetch_status0 Begin fetch next from s_c1 into sno,cno,grade print sno+cno+cast( grade as char(10) end,第八章 数据库编程,8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程,8.2

9、 存储过程,8.2.1 TSQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存储过程,8.2.2 变量常量的定义,2.变量定义 (1)局部变量的定义 DECLARE 局部变量名 数据类型 , (2)局部变量的赋值 SET语句： SET 局部变量名 表达式 SELECT语句： SELECT 局部变量名 表达式 , n,8.2.2 变量常量的定义,2.变量定义 (3)局部变量的输出 SELECT语句： SELECT 局部变量名, n PRINT语句： PRINT 局部变量名,8.2.2 变量常量的定义,例1分析下列代码： declare v1 varchar(20),

10、v2 varchar(40) set v1=2008 set v2=v1+北京奥运 select v1,v2,Print v1，v2,8.2.2 变量常量的定义,例2 查询学号为0215123学生的姓名。 declare v1 nvarchar(20), v2 nvarchar(40) SET v1=无此人 SET v2=0215123 SELECT Sname FROM Student WHERE sno=v2 SELECT v1 AS 姓名,V1=,例3给出以下语句的执行结果。 DECLARE f float,cn char(8) SET f=85 SET cn=2 SELECT * FR

11、OM SC WHERE cno= cn AND gradef,8.2.2 变量常量的定义,例4分析以下语句执行后sn的值。 DECLARE sn char(8) (1)SELECT sn=sname FROM student WHERE sno=101 (2)SELECT sn=sname FROM student,提示：SELECT 通常用于返回单个值； 若是返回多个值，将最后一个值赋给变量,8.2.2 变量常量的定义,8.2 存储过程,8.2.1 TSQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存储过程,1.条件控制语句: 格式：IF 条件表达式 A块 EL

12、SE B块 三种使用方式 IF IF ELSE IF 嵌套,8.2.3 控制结构,8.2.3 控制结构,例5 如果“数学”的平均成绩高于60分，则显示“该课程教学成绩合格”。 declare text varchar(30) IF( )60 set text=该课程教学成绩合格 else select text=该课程教学成绩不合格 select text,Select avg(grade) from SC,Course where Cname=数学 and o=o,2.循环控制语句: WHILE格式：WHILE 条件表达式 循环体,8.2.3 控制结构,在循环体内可以使用 BREAK CON

13、TINUE 等语句。,8.2.3 控制结构,例6 显示字符串“China”中每个字符的ASCII码值和字符。 declare p int, string varchar(8) set p=1 set string=China while p=datalength(string) begin select substring(string,p,1) , ascii(substring(string,p,1) set p=p+1 end,8.2 存储过程,8.2.1 TSQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存储过程,1.存储过程的优点 存储过程在服务器端运行

14、，执行效率高； 存储过程执行一遍后，驻留在高速缓冲中，再使用只需调用，无需编译； 存储过程降低了客户机和服务器之间的通信量； 方便实施企业规划。,8.2.4 存储过程,8.2.4 存储过程,存储过程的类型 系统存储过程： 前缀是“sp_”，可以在任何数据库使用; 本地存储过程： 用户定义的存储过程 临时存储过程等 #、#,2.存储过程的创建,CREATE PROCEDURE 存储过程名 参数1, AS ,8.2.4 存储过程,3.存储过程的执行,EXECUTE 存储过程名 参数1,参数2, ,4.存储过程的删除,DROP PROCEDURE 存储过程名,8.2.4 存储过程,例7从学生成绩数据

15、库的三个表中查询，返回学生学号、姓名、课程名、成绩。 CREATE PROC s_info AS SELECT student.sno,sname,cname,grade From student ,sc,course Where sdutent.sno=sc.sno and o=o,EXEC s1_info,8.2.4 存储过程,执行存储过程： 方法1： EXEC s1_info 0215122 , 数学 方法2： EXEC s1_info cn= 数学 , sn=0215122,8.2 存储过程,8.2.1 TSQL的块结构 8.2.2 变量常量的定义 8.2.3 控制结构 8.2.4 存

16、储过程,1.存储过程的优点 执行效率高、常驻高速缓冲、减少信息量传输、便于企业规划 2.存储过程的分类 3.存储过程的创建与调用,8.2.4 存储过程,8.2.4 存储过程,例8 从学生成绩数据库的三个表中查询，返回指定学号和课程号的学生姓名、课程名、成绩。 CREATE PROC s1_info sn char(10),cn char(20) AS SELECT sname,cname,grade FROM student ,sc,course WHERE student.sno=sc.sno and o=o and student.sno=sn and cname=cn Go,8.2.4

17、存储过程,例9 用于计算指定学生的总分。 CREATE PROC tg sn char(10),total int OUT AS SELECT total=SUM(grade) FROM student ,sc WHERE Sname=sn and student.sno=sc.sno GROUP BY student.sno,DECLARE total int EXEC tg 刘晨,total SELECT 刘晨,total,OUTPUT,第八章 数据库编程,8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程,1.数据库互联的概述 ODBC产生的原因是由于不同的RDBMS的存在；

18、适应性和可移植性差 在某个RDBMS下编写的应用程序很难移植到另一个RDBMS运行 应用程序需要共享多个部门的数据资源，访问不同操作系统上的不同的RDBMS。,8.3 ODBC编程,ODBC由微软推出，用于实现数据库的互联,8.3 ODBC编程,用户应用程序,ODBC API,ODBC Manager,Driver,Driver,Driver,SQL Server,Oracle,DB2,标准接口,驱动程序管理器,驱动程序,网络,2.ODBC工作原理Open DataBase Connectivity,2.ODBC工作原理 (1)应用程序 提供用户界面、应用逻辑、事务逻辑。 请求连接数据库； 向

19、数据库发送SQL语句； 为SQL语句结果分配存储空间，定义数据格式 获取数据库操作结果，或处理错误； 进行数据处理并向用户提交处理结果； 请求事务的提交或回滚； 断开与数据源的连接。,8.3 ODBC编程,(2)驱动程序管理器 包括在ODBC32.dll中 管理应用程序和驱动程序之间的通信 主要功能： (1)装载ODBC驱动程序 (2)选择和连接正确的驱动程序 (3)管理数据源 (4)检查ODBC调用参数的合法性 (5)记录ODBC函数的调用 可以建立、配置、删除数据源等,8.3 ODBC编程,(3)数据库驱动程序 提供应用系统与数据库平台的独立性 应用程序不能直接存取数据库 (4)ODBC数

20、据源管理 数据源是数据库用户最终访问的数据，是一种数据连接的抽象 ODBC为每一个被访问的数据源指定惟一的数据源名,8.3 ODBC编程,3.与OLEDB、OLEDB-ODBC接口三者间的比较 这三种接口技术具有相通性。可以比喻为客户机有3条到达SQL Server2000服务器的通路。 ODBC： 好比一条干道，什么样的驱动程序都可以在这条干道上跑，所以执行效率不是很高。 OLEDB： 好比一条专用道(SQL Server专用)，执行效率高。开发的程序只能驱动SQL Server。,8.3 ODBC编程,3.与OLEDB、OLEDB-ODBC接口三者间的比较 OLEDB-ODBC ： 好比一

21、条交叉道，先走一段OLEDB接口，再走一段ODBC接口。这样开发的数据库也可以支持多种数据库。,8.3 ODBC编程,4.ODBC数据库连接 (1)创建ODBC数据源 控制面板管理工具 (2)使用ODBC数据源 dim connstr as string connstr=“DSN=数据源名; userid=sa; password=12” （或 connstr=“DSN=数据源名;uid=sa;pwd=12”） conn.ConnectionString= connstr,8.3 ODBC编程,5.ODBC API的直接接口技术 ODBC API是一种直接使用ODBC接口技术 在WIN XP

22、Professional中，如果我们直接使用odbc32.dll文件提供的函数来编程，这里的函数也是SQL Server 2000的ODBC驱动程序最后使用的函数。那么可以不经过中间的翻译过程，直接驱动SQL Server 2000服务器，所以可以大大提高执行效率。 因此说,ODBC API是一种直接的ODBC接口使用技术。,8.3 ODBC编程,8.3 ODBC编程,应用程序,数据源名(DSN),ODBC管理器,驱动程序管理器,OBDC驱动程序,SQL Server服务器,应用层,ODBC层,数据层,应用程序,数据源名(DSN),ODBC API,驱动程序管理器,OBDC驱动程序,应用层,O

23、DBC层,数据层,SQL Server服务器,连接方式 通过ADO连接SQL Server数据库分为： 有源数据库连接 无源数据库连接 它们的区别是否需要配置数据源(DSN)。, 数据库的连接方式,(1)无源数据库连接 不是不需要数据源 只是不需要配置ODBC数据源，利用ADO就可通过代码进行连接。 数据访问接口有三种： ActiveX数据对象（ADO） 远程数据对象（RDO） 数据访问对象（DAO）,数据库的连接方式,(1)无源数据库连接 ADO最新，它比RDO和DAO更加简单，更加灵活。 ADO是Microsoft数据库应用程序开发的接口，是建立在OLE DB之上的高层数据库访问技术。它封

24、装了OLE DB所提供的接口。 ADO的接口可以使程序员在更高级别上进行数据交互。 ADO可以用统一的方法对不同的文件系统进行访问，简化了程序编制，增加程序的可移植性。,数据库的连接方式,(2)有源数据库连接 首要任务是要注册数据源名称（DSN）。 通过配置ODBC环境，进行数据源的注册，然后才能在对数据库编程时，对数据源进行连接、访问和操作。,数据库的连接方式,(2)有源数据库连接 ODBC数据源分为以下三类： 用户DSN：只有创建数据源的用户才可以使用，而且只能在当前的计算机上使用。 系统DSN：任何使用你的计算机的用户和程序都可以使用的DSN。 文件DSN：除了具有系统DSN的功能之外，

25、还能被其它用户在其它计算机上使用。,数据库的连接方式,1.引用组件 在VB6.0菜单中“工程引用”选项； 在弹出的对话框中，选择： Microsoft ActiveX Data Objects 2.6 Library Microsoft ActiveX Data Objects Recordset 2.6 Library 这两个组件。,建立无源数据库连接,2.连接数据库 运用ADO对象模型的主要元素： Connection(连接)中的ConnectionString属性进行连接; 为ConnectionString指定一个连接字符串，告诉ADO如何连接数据库。,建立无源数据库连接,定义连接对象和记录集 Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Set conn = New ADODB.Connection Set rs = New ADODB.Recordset con