1、数据库系统概论 An Introduction to Database System第八章 数据库编程 (1),第八章 数据库编程,8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程,学习目标,了解嵌入式SQL的一般形式 理解嵌入式SQL语句与主语言之间的通信 掌握不用游标的SQL语句 掌握使用游标的SQL语句 了解动态SQL,8.1 嵌入式SQL,SQL语言提供了两种不同的使用方式： 交互式：作为独立语言在终端交互方式下使用 嵌入式：将SQL语言嵌入到某种高级语言中 为什么要引入嵌入式SQL SQL语言:是面向集合的描述性语言，是非过程的 事务处理需要高级语言的过程性结构来实现。
2、这两种方式细节上的差别： 在程序设计环境下，SQL语句要做某些必要的扩充,8.1 嵌入式SQL,8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL与主语言之间通信 8.1.3 不用游标的SQL语句 8.1.4 使用游标的SQL语句 8.1.5 动态SQL简介,8.1.1 嵌入式SQL的处理过程,1.主语言嵌入式SQL是将SQL语句嵌入到程序设计语言中，被嵌入的程序设计语言称为宿主语言，简称主语言。,8.1.1 嵌入式SQL的处理过程,(1)由RDBMS的预处理程序对源程序进行扫描，识别出SQL语句; (2)把它们转换成主语言调用语句，以使主语言编译程序能识别它; (3)最后由主语言的
3、编译程序将整个源程序编译成目标码.,2.处理过程目前采用较多的是预编译方法，其处理过程是：,8.1.1 嵌入式SQL的处理过程,3.区分嵌入式SQL语句与主语言语句: 前缀：EXEC SQL结束标志：随主语言的不同而不同,在C语言中嵌入SQL语句形式：EXEC SQL ；例： EXEC SQL DROP TABLE Student;,8.1.1 嵌入式SQL的处理过程,在COBOL语言中嵌入SQL语句：EXEC SQL END-EXEC例：EXEC SQL DROP TABLE Student END-EXEC,8.1 嵌入式SQL,8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL
4、与主语言之间通信 8.1.3 不用游标的SQL语句 8.1.4 使用游标的SQL语句 8.1.5 动态SQL简介,8.1.2 嵌入式SQL语句与主语言之间的通信,将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句: SQL语句描述性的面向集合的语句负责操纵数据库 高级语言语句过程性的面向记录的语句负责控制程序流程,8.1.2 嵌入式SQL语句与主语言之间的通信,工作单元之间的通信方式,(1) SQL通信区向主语言传递SQL语句的执行状态信息主语言能够根据此信息控制程序流程 (2) 主变量主语言向SQL语句提供参数将SQL语句查询数据库的结果交主语言进一步处理 (3) 游标解决
5、集合性操作语言与过程性操作语言不匹配,1.SQL通信区,(1)SQLCA是SQL Communication Area的缩写SQLCA是一个数据结构。 SQLCA的用途,SQL语句执行后，RDBMS反馈给应用程序信息描述系统当前工作状态描述运行环境 这些信息将送到SQL通信区SQLCA中 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句,1.SQL通信区,(2)SQLCA的使用方法,定义SQLCA语句格式：EXEC SQL INCLUDE SQLCA,1.SQL通信区,使用SQLCA SQLCA中有一个变量SQLCODE，存放每次执行SQL语句后的返回值； 如果SQLCODE返
6、回0（即常量SUCCESS），则表示SQL语句成功，否则表示出错。 应用程序每执行完一条SQL语句后，都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。,2.主变量,(1) 什么是主变量 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据 在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）,2.主变量,(2)主变量的类型 输入主变量由应用程序对其赋值，SQL语句引用 输出主变量由SQL语句赋值或设置状态信息，返回给应用程序 说明：一个主变量有可能既是输入主变量又是输出主变量,2.主变量,(3)主变量的用途 输入主变量指定向数据库中插入的
7、数据将数据库中的数据修改为指定值指定WHERE子句或HAVING子句中的条件指定执行的操作,2.主变量,(3)主变量的用途 输出主变量获取SQL语句的结果数据获取SQL语句的执行状态,(4)指示变量 一个主变量可以附带一个指示变量（Indicator Variable） 什么是指示变量 整型变量 用来“指示”所指主变量的值或条件,2.主变量,(4) 指示变量 指示变量的用途输入主变量可以利用它赋空值输出主变量可以利用它检测出是否空值，值是否被截断,2.主变量,2.主变量,(5)在SQL语句中使用主变量和指示变量的方法 1)说明主变量和指示变量 BEGIN DECLARE SECTION. .
8、(说明主变量和指示变量). END DECLARE SECTION,2.主变量,(5)在SQL语句中使用主变量和指示变量的方法,2)使用主变量 主变量在SQL语句中任何可使用表达式的地方出现； 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加分隔符（:）作为标志,2.主变量,例主变量的说明及使用 EXEC SQL BEGIN DECLARE SECTION; CHAR title_id(7);CHAR title(81);INT royalty; EXEC SQL END DECLARE SECTION;, if (sqlca.sqlcodeSUCCESS) bre
9、ak; printf(“Sno:%s,Cno:%s,Grade:%d“,:Sno,:Cno,:Grade);,2.主变量,(5)在SQL语句中使用主变量和指示变量的方法3)使用指示变量指示变量前必须加冒号标志必须紧跟在所指主变量之后,说明：在SQL语句之外(主语言语句中)使用主变量和指示，可以直接引用，不必加分隔符,3.游标(cursor),(1)为什么要使用游标 SQL语言与主语言具有不同数据处理方式 SQL语言是面向集合的 主语言是面向记录的,主变量不能完全满足SQL语句向应用程序输出数据的要求。 游标用来协调这两种不同的处理方式,(2)什么是游标 游标是系统为用户开设的一个数据缓冲区,存
10、放SQL语句的执行结果； 每个游标区都有一个名字； 用户可以用SQL语句逐一从游标中获取记录,并赋给主变量，交由主语言进一步处理。,3.游标(cursor),(3)嵌入式SQL的工作原理 SQL语句与主语言语句分工明确SQL语句：直接与数据库打交道主语言语句：控制程序流程对SQL语句结果做进一步处理 SQL语句用主变量从主语言中接收执行参数，操纵数据库 SQL语句的执行状态由DBMS送至SQLCA中,3.游标(cursor),(3) 嵌入式SQL的工作原理(续) 主语言程序从SQLCA中取出状态信息，据此决定下一步操作 如果SQL语句成功地检索出数据，则通过主变量传给主语言做进一步处理 SQL
11、语言和主语言的不同数据处理方式通过游标来协调,3.游标(cursor),8.1.2 嵌入式SQL语句与主语言之间的通信,4.建立和关闭数据库连接,(1) 建立数据库的连接EXEC SQL CONNECT TO target AS connection-name USER user-name;其中：target：为要连接的数据库服务器，格式：connection-name：标识一个程序同时建立的多个连接，只有一个连接时可以省略。,(2) 关闭数据库连接EXEC SQL DISCONNECT connection;,例：带有嵌入式SQL的一小段C程序,EXEC SQL begin declare
12、section; /*(1)说明主变量*/char deptn20;char Hsno9;char Hsname20;char Hsex2;int Hage;int newage; EXEC SQL end declare section; long sqlcode; EXEC SQL include sqlca; /*(2)定义SQL通信区*/,例：带有嵌入式SQL的一小段C程序,int main() int count=0;char yn;printf(“please choose the department name(cs/is):“);scanf(“%s“, /* (4) 打开游标操
13、作*/,例：带有嵌入式SQL的一小段C程序,for(;)EXEC SQL fetch sx into :hsno,:hname,:hsex,:hage;if (sqlca.sqlcode!=0) break;printf(“%-10s %-20s %-10s %-10d n“,hsno,hsname,hsex,hsage)printf(“update age(y/n)?“);do scanf(“%c“,if (yn=y|yn=Y) ,8.1 嵌入式SQL,8.1.1 嵌入式SQL的处理过程 8.1.2 嵌入式SQL与主语句之间通信 8.1.3 不用游标的SQL语句 8.1.4 使用游标的SQL
14、语句 8.1.5 动态SQL简介,8.1.3 不用游标的SQL语句,不用游标的SQL语句的种类说明性语句数据定义语句数据控制语句查询结果为单记录的SELECT语句非CURRENT形式的UPDATE语句非CURRENT形式的DELETE语句INSERT语句,嵌入式SQL中最简单的一类语句，不需返回结果数据，也不需使用主变量,-不用游标，通常需使用主变量,较复杂,1.说明性语句,说明性语句是专为在嵌入式SQL中说明主变量、SQLCA等而设置的 说明主变量EXEC SQL BEGIN DECLARE SECTION; EXEC SQL END DECLARE SECTION;这两条语句必须配对出现，
15、相当于一对括号，两条语句中间是主变量的说明 说明SQLCAEXEC SQL INCLUDE SQLCA,1.说明性语句,例如：EXEC SQL INCLUDE SQLCA; /* (1) 定义SQL通信区 */* (2) 说明主变量 */EXEC SQL BEGIN DECLARE SECTION; CHAR Hsno (6);CHAR Hname(8);CHAR Hsex(1);INT Hage;CHAR Hdept(8);EXEC SQL END DECLARE SECTION;,例1 建立一个学生表Student。EXEC SQL CREATE TABLE Student(Sno CHA
16、R(5) NOT NULL UNIQUE, Sname CHAR(8),Ssex CHAR(1),Sage INT, Sdept CHAR(15);,2.数据定义语句,说明：数据定义语句中不允许使用主变量。如下列语句是错误的：EXEC SQL DROP TABLE :table_name;,3.数据控制语句,例2 把查询Student表权限授给用户U1。EXEC SQL GRANT SELECT ON TABLE Student TO U1;,4.查询结果为单记录的SELECT语句,(1)语句格式 EXEC SQL SELECT ALL|DISTINCT ,.INTO ,. FROM , .W
17、HERE GROUP BY HAVING ORDER BY ASC|DESC;,说明: 比交互式SELECT语句多了一个INTO子句，作用是把从数据库中找到的符合条件的记录放到INTO子句指出的主变量中去。,4.查询结果为单记录的SELECT语句,(2) 使用注意事项 使用主变量INTO子句WHERE子句的条件表达式HAVING短语的条件表达式,4.查询结果为单记录的SELECT语句,(2) 使用注意事项,使用指示变量指示变量只能用于INTO子句中;,当查询得出的某个数据项为空值时，系统会将相应主变量的指示变量置为负值，不向该主变量赋值，即主变量值仍保持SQL语句执行前的值;当发现指示变量值为
18、负值时，不管主变量为何值，均应认为主变量值为NULL.,4.查询结果为单记录的SELECT语句,查询结果为空集 如果数据库中没有满足条件的记录，即查询结果为空，则DBMS将SQLCODE的值置为100 查询结果为多条记录 程序出错，DBMS会在SQLCA中返回错误信息,例2 根据学生学号查询学生信息.设已将要查询的学生学号赋给了主变量givensno。EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept INTO :Hsno, :Hname, :Hsex, :Hage, :HdeptFROM Student WHERE Sno=:givensno;,4.查
19、询结果为单记录的SELECT语句,其中：Hsno, Hname, Hsex, Hage, Hdept和givensno均是主变量，并均已在前面的程序中说明过了。,例3 查询某个学生选修某门课程的成绩。设：已将要查询的学生学号赋给了主变量givensno，将课程号赋给了主变量givencnoEXEC SQL SELECT Sno, Cno, GradeINTO :Hsno, :Hcno, :Hgrade:Gradeid FROM SCWHERE Sno=:givensno AND Cno=:givencno;,4.查询结果为单记录的SELECT语句,提示：从提高应用程序的数据独立性角度考虑，SE
20、LECT语句在任何情况下都应该使用游标,5.非CURRENT形式的UPDATE语句,使用主变量SET子句WHERE子句 使用指示变量SET子句非CURRENT形式的UPDATE语句可以操作多条元组,例4将全体学生1号课程的考试成绩增加若干分。设：增加分数已赋给主变量Raise。EXEC SQL UPDATE SC SET Grade=Grade+ :Raise WHERE Cno=1;,5.非CURRENT形式的UPDATE语句,例5 修改某个学生1号课程的成绩。设:该学生学号已赋给主变量givensno；修改后的成绩已赋给主变量newgradeEXEC SQL UPDATE SC SET G
21、rade=:newgrade WHERE no=:givensnoAND Cno=1 ;,5.非CURRENT形式的UPDATE语句,例6 将计算机系全体学生年龄置NULL值。Sageid=-1;EXEC SQL UPDATE Student SET Sage= :Hage :Sageid WHERE Sdept=CS;,5.非CURRENT形式的UPDATE语句,说明：将指示变量Sageid赋一个负值后，无论主变量Hage为何值，DBMS都会将CS系所有记录的年龄置空,例6 将计算机系全体学生年龄置NULL值。Sageid=-1;EXEC SQL UPDATE Student SET Sag
22、e= :Hage :Sageid WHERE Sdept=CS;,5.非CURRENT形式的UPDATE语句,它等价于：EXEC SQL UPDATE Student SET Sage=NULL WHERE Sdept=CS;,例7 某个学生退学，现将他的所有选课记录删除掉。 假设:该学生的姓名已赋给主变量stdname。EXEC SQL DELETE FROM SCWHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname);,6.非CURRENT形式的DELETE语句,在WHERE子句可以使用主变量 非CURRENT形式的DELETE语
23、句可以操作多条元组,7.非CURRENT形式的INSERT语句,非CURRENT形式的INSERT语句一次只能输入一条元组 VALUES子句可以使用主变量和指示变量,例8 某个学生新选修了某门课程，将有关记录插入。 假设：学生的学号已赋给主变量stdno；课程号已赋给主变量couno。gradeid=-1;EXEC SQL INSERTINTO SC(Sno, Cno, Grade)VALUES(:stdno, :couno, :gr:gradeid);,插入的grade成绩是多少？WHY？,8.1.4 使用游标的SQL语句,必须使用游标的SQL语句 查询结果为多条记录的SELECT语句 CU
24、RRENT形式的UPDATE语句 CURRENT形式的DELETE语句,1.查询结果为多条记录的SELECT语句,使用游标的步骤 (1) 说明游标 (2) 打开游标 (3) 移动游标指针，然后取当前记录 (4) 关闭游标,SELECT语句查询的结果是多条记录，而高级语言一次只能处理一条记录，因此需要用游标机制，将多条记录一次一条送至主语言程序处理，从而把对集合的操作转换为对单个记录的处理。,游标的典型使用过程,语句格式DECLARE SCROLL CURSORFOR FOR READ ONLY|UPDATE OF 列名列表 功能是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。
25、,指定指针移动方式,创建只读游标,定义游标可更新的列,默认为全部,1.查询结果为多条记录的SELECT语句,(1) 说明游标,1.查询结果为多条记录的SELECT语句,(2) 打开游标,使用OPEN语句 语句格式EXEC SQL OPEN ; 功能 打开游标, 是执行相应的SELECT语句,把所有满足查询条件的记录取到缓冲区; 游标处于活动状态，指针指向查询结果集的第一条记录之前.,1.查询结果为多条记录的SELECT语句,(3)移动游标指针，然后取当前记录,使用FETCH语句 语句格式 EXEC SQL FETCH NEXT|PRIOR| FIRST|LAST FROM INTO ,. ;,
26、功能 指定游标指针移动方向，将缓冲区中的当前记录取出来送至主变量，供主语言进一步处理。 注意指针移动方式的指定。缺省值为NEXT,1.查询结果为多条记录的SELECT语句,(3)移动游标指针，然后取当前记录,说明 (1)主变量必须与SELECT语句中的目标列表达式具有一一对应关系 (2)FETCH语句通常用在一个循环结构中，实现逐条取出结果集中的行进行处理 (3)现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针,1.查询结果为多条记录的SELECT语句,(4) 关闭游标,用CLOSE语句 语句格式:EXEC SQL CLOSE ; 功能：关闭游标，释
27、放结果集占用的资源 说明 游标被关闭后，不再和原查询结果集相联系 游标可再次打开，与新查询结果相联系,(5) 删除游标 EXEC SQL DEALLOCATE ,例1查询某个系全体学生的信息(学号、姓名、性别和年龄)。设：待查系名由用户在程序运行时指定,放在deptname中。EXEC SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/EXEC SQL END DECLARE SECTION;gets(deptname); EXEC SQL DECLARE
28、SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM StudentWHERE SDept=:deptname; /* 说明游标 */,1.查询结果为多条记录的SELECT语句,EXEC SQL OPEN SX; WHILE(1) EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; if (sqlca.sqlcode SUCCESS) break; ; EXEC SQL CLOSE SX; /* 关闭游标 */ ,1.查询结果为多条记录的SELECT语句,例2 查询某些系全体学生的信息。EXE
29、C SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE SX CURSOR FORSELECT Sno, Sname, Ssex, Sage FROM StudentWHERE SDept= :deptname;,1.查询结果为多条记录的SELECT语句,WHILE (gets(deptname)!=NULL) /* 接收主变量deptname的值 */ /* 开始处理
30、deptname指定系的学生信息,每次循环中deptname可具有不同的值 */EXEC SQL OPEN SXWHILE (1) EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;if (sqlca.sqlcode SUCCESS) break; /* 由主语言语句进行进一步处理 */; EXEC SQL CLOSE SX; ; ,1.查询结果为多条记录的SELECT语句,2.CURRENT形式的UPDATE和DELETE语句,(1)CURRENT形式的UPDATE语句和DELETE语句的用途 非CURRENT形式的UPDATE和DE
31、LETE语句面向集合的操作一次修改或删除所有满足条件的记录,如果只想修改或删除其中某个记录用带游标的SELECT语句查出满足条件的记录从中进一步找出要修改或删除的某条记录用CURRENT形式的UPDATE语句和DELETE语句修改或删除,2.CURRENT形式的UPDATE和DELETE语句,(2)步骤 DECLARE说明游标 OPEN打开游标，把满足查询条件的记录从表取至缓冲区 FETCH 推进游标指针，并把当前记录从缓冲区中取出来送至主变量,2.CURRENT形式的UPDATE和DELETE语句,(2)步骤 检查该记录是否是要修改或删除的记录，是则处理；,UPDATE语句或DELETE语句
32、中要用子句 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 SECTION; /* 说明主变量 deptnam
33、e,HSno,HSname等*/EXEC SQL END DECLARE SECTION;gets(deptname); /* 为主变量deptname赋值 */EXEC SQL DECLARE SX CURSOR FOR /* 说明游标 */SELECT Sno, Sname, Ssex, Sage FROM StudentWHERE SDept=:deptname;,2.CURRENT形式的UPDATE和DELETE语句,EXEC SQL OPEN SX /* 打开游标 */ WHILE(1) EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex; I
34、F (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语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是程序运行过
35、程中动态输入的。 例如： SELECT Sno, Sname, Ssex,Sage FROM student WHERE Sdept=deptn,8.1.5 动态SQL简介,1.静态SQL 优点：用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。 缺点：不能编写更为通用的程序。如：查询条件、要查询的属性列均不确定时，就无法用一条静态SQL语句实现。,8.1.5 动态SQL简介,【例如】对SC表，有如下查询要求： 任课教师想查选修某门课程的所有学生的学号及其成绩； 班主任想查某个学生选修的所有课程的课程号及相应成绩； 学生想查某个学生选修某门课程的成绩。,8
36、.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用户临时构造。 即语句的输入/输出都是不确定的。,例一个游标的具体使用,declare s_c1 SCROLL
37、cursorfor select * from sc where cno=2 open s_c1 print fetch_status declare sno char(8),cno char(8),grade int While fetch_status0 Beginfetch next from s_c1into sno,cno,gradeprint sno+cno+cast( grade as char(10) end,作业,P149第13、14、15题。 预习4.14.2。,下课了。,休息。,追 求,说明性语句 嵌入SQL语句 数据定义 可执行语句 数据控制 数据操纵 允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句 允许出现说明语句的地方，都可以写说明性SQL语句,3.7.1 嵌入式SQL的一般形式,3.嵌入SQL语句的分类：,复 习,SQL语句提供有_和_两种形式 嵌入式SQL语句与主语言之间的通信有_、_和_。 三种通信方式有何不同？各自主要解决什么问题？,交互式,嵌入式,SQLCA,主变量,游标,
