第8章数据库编程(1)

1、1/19数据库原理及应用数据库原理及应用Principle and Application of Database第八章数据库编程第八章数据库编程2/19n理解嵌入式理解嵌入式SQL的处理过程的处理过程n掌握嵌入式掌握嵌入式SQL语句与主语言之间的通信方法语句与主语言之间的通信方法n掌握不用游标和使用游标的掌握不用游标和使用游标的SQL语句语句n掌握存储过程的创建、执行和删除方法掌握存储过程的创建、执行和删除方法3/19nSQL语言的使用方式语言的使用方式n交互式：交互式：作为独立语言在终端交互方式下使用。作为独立语言在终端交互方式下使用。n嵌入式：嵌入式：将将SQL语言嵌入到某种高级语言中，

2、利用高级语言的语言嵌入到某种高级语言中，利用高级语言的过程性结构实现事务处理。被嵌入的高级语言如过程性结构实现事务处理。被嵌入的高级语言如VC+或或Java称为宿主语言，简称主语言。称为宿主语言，简称主语言。n嵌入式嵌入式SQL的处理过程的处理过程n由由DBMS的预处理程序对源程序进行扫描，识别出的预处理程序对源程序进行扫描，识别出SQL语句。语句。n把它们转换成主语言调用语句，使主语言编译程序能识别它。把它们转换成主语言调用语句，使主语言编译程序能识别它。n由主语言的编译程序将整个源程序编译成目标码。由主语言的编译程序将整个源程序编译成目标码。n为了区分为了区分SQL语句与主语言语句，所有语

3、句与主语言语句，所有SQL语句都必须加前语句都必须加前缀缀EXEC SQL，以分号；结束。，以分号；结束。4/19n嵌入式嵌入式SQL语句与主语言之间的通信：语句与主语言之间的通信：将将SQL嵌入到高级语言嵌入到高级语言中混合编程，中混合编程，SQL语句负责操纵数据库，高级语言语句负责控语句负责操纵数据库，高级语言语句负责控制程序流程。这是两种不同计算模型的语句，通信方式为：制程序流程。这是两种不同计算模型的语句，通信方式为：n数据库工作单元与源程序工作单元之间的通信数据库工作单元与源程序工作单元之间的通信n向主语言传递向主语言传递SQL语句的执行状态信息，使主语言能够据此控语句的执行状态信息

4、，使主语言能够据此控制程序流程，主要用制程序流程，主要用SQL通信区通信区(SQLCA)实现。实现。n主语言向主语言向SQL语句提供参数，主要用主变量实现。语句提供参数，主要用主变量实现。n将将SQL语句查询数据库的结果交主语言进一步处理，主要用主语句查询数据库的结果交主语言进一步处理，主要用主变量和游标实现。变量和游标实现。nSQL通信区通信区(SQLCA)n作用：作用：SQL语句执行后，语句执行后，DBMS反馈给应用程序若干信息，这反馈给应用程序若干信息，这些信息将送到些信息将送到SQLCA中，应用程序从中取出信息以决定接下中，应用程序从中取出信息以决定接下来执行的语句。来执行的语句。5/

5、19n定义：定义：EXEC SQL INCLUDE SQLCA定义。定义。SQLCA中有一个中有一个存放每次执行存放每次执行SQL语句后返回代码的变量语句后返回代码的变量SQLCODE，若它等，若它等于预定义常量于预定义常量SUCCESS，则表示，则表示SQL语句成功，否则表示出语句成功，否则表示出错。应用程序每执行完一条错。应用程序每执行完一条SQL 语句后都应测试语句后都应测试SQLCODE的值，以了解该的值，以了解该SQL语句的执行情况并做相应处理。语句的执行情况并做相应处理。n主变量：主变量：嵌入式嵌入式SQL语句中使用的主语言程序变量。语句中使用的主语言程序变量。n输入主变量：输入主

6、变量：由应用程序对其赋值，由应用程序对其赋值，SQL语句引用。语句引用。n输出主变量：输出主变量：由由SQL语句赋值或设置状态，返回给应用程序。语句赋值或设置状态，返回给应用程序。n指示变量：指示变量：一个主变量可以附带一个指示变量。它是一个整型一个主变量可以附带一个指示变量。它是一个整型变量，用来指示所指主变量的值或条件。变量，用来指示所指主变量的值或条件。n主变量和指示变量的说明：主变量和指示变量的说明：在在BEGIN DECLARE SECTION与与END DECLARE SECTION之间说明。为了与数据库对象名区之间说明。为了与数据库对象名区别，别，SQL语句中主变量名前要加冒号语

7、句中主变量名前要加冒号:作为标志。作为标志。6/19n游标游标(cursor)：SQL语言是面向集合的，一条语言是面向集合的，一条SQL语句可产生语句可产生或处理多条记录；而主语言是面向记录的，一组主变量一次只或处理多条记录；而主语言是面向记录的，一组主变量一次只能存放一条记录，仅使用主变量并不能完全满足能存放一条记录，仅使用主变量并不能完全满足SQL语句向应语句向应用程序输出数据的要求。为此嵌入式用程序输出数据的要求。为此嵌入式SQL引入了游标的概念，引入了游标的概念，用来协调这两种不同的处理方式。用来协调这两种不同的处理方式。n概念：概念：游标是系统为用户开设的一个数据缓冲区，存放游标是系

8、统为用户开设的一个数据缓冲区，存放SQL语语句的执行结果，每个游标区都有一个名字。用户可以用句的执行结果，每个游标区都有一个名字。用户可以用SQL语语句逐一从游标中获取记录，并赋给主变量，交由主语言处理。句逐一从游标中获取记录，并赋给主变量，交由主语言处理。n建立数据库连接：建立数据库连接：嵌入式嵌入式SQL程序要访问程序要访问DB必须先连接必须先连接DB。EXEC SQL CONNECT TO 目标名目标名 USER 用户名用户名;n其中目标名是：其中目标名是：.n关闭数据库连接：关闭数据库连接：嵌入式嵌入式SQL程序完成所有的程序完成所有的DB操作后，必操作后，必须关闭须关闭DB的连接。的

9、连接。EXEC SQL DISCONNECT;7/19n程序实例程序实例 例例1依次检索每个学生元组，显示其姓名和年龄。依次检索每个学生元组，显示其姓名和年龄。#include #include EXEC SQL INCLUDE sqlca;int main() EXEC SQL BEGIN DECLARE SECTION; /主变量主变量 char sname8; char sdept20; short sdeptInd; /指示变量指示变量 EXEC SQL END DECLARE SECTION;8/19 printf(This is my Embedded SQL for C appl

10、icationn); EXEC SQL CONNECT TO zhs.ss USER sa.zhs; /连接到数据库连接到数据库 if (SQLCODE = 0) printf(Connection to SQL Server establishedn); else /连接连接DBMS错误错误 printf(ERROR: Connection to SQL Server failedn); return (1); /下面为程序主体部分下面为程序主体部分 EXEC SQL WHENEVER SQLERROR GOTO error; /错误处理错误处理 EXEC SQL WHENEVER NOT

11、FOUND GOTO done; EXEC SQL DECLARE c1 CURSOR FOR SELECT sname,sdept FROM student; EXEC SQL OPEN c1;9/19 for ( ; ; ) EXEC SQL FETCH c1 INTO :sname,:sdept:sdeptInd; printf (Sname:%s ,sname); if ( sdeptInd 0) /指示变量指示变量sqlcode); done: EXEC SQL WHENEVER SQLERROR continue; EXEC SQL CLOSE c1; EXEC SQL COMMI

12、T WORK RELEASE; EXEC SQL DISCONNECT ALL; return 0; 10/19n不用游标的不用游标的SQL语句：语句：说明性语句、数据定义语句、数据控制说明性语句、数据定义语句、数据控制语句、查询结果为单记录的语句、查询结果为单记录的SELECT语句、非语句、非CURRENT形式形式的增删改语句。的增删改语句。n查询结果为单记录的查询结果为单记录的SELECT语句：语句：这类语句不需使用游标，这类语句不需使用游标，只需用只需用INTO子句指定存放查询结果的主变量。子句指定存放查询结果的主变量。 例例2根据学生号码查询学生信息。假设已经将要查询的学生根据学生号码

13、查询学生信息。假设已经将要查询的学生学号赋给了主变量学号赋给了主变量givensno。 EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept INTO :HSno,:HSname,:HSsex,:HSage,:Hdeptname FROM Student WHERE Sno=:givensno;说明：说明：nINTO子句、子句、WHERE子句和子句和HAVING短语的条件表达式中均可短语的条件表达式中均可以使用主变量。以使用主变量。11/19n指示变量只能用于指示变量只能用于INTO子句中。若子句中。若INTO子句中主变量后面跟有子句中主变量后面跟有指示变量，则当

14、查询得出的某个数据项为空值时，系统会自动指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应主变量后的指示变量置为负值，但不向该主变量赋值。将相应主变量后的指示变量置为负值，但不向该主变量赋值。n若查询结果实际上并不是单条记录，则程序出错，并在若查询结果实际上并不是单条记录，则程序出错，并在SQLCA中返回错误信息。中返回错误信息。例例3查询某个学生选修某门课程的成绩。假设已将要查询的学查询某个学生选修某门课程的成绩。假设已将要查询的学生学号和课程号分别赋给了主变量生学号和课程号分别赋给了主变量givensno和和givencno。 EXEC SQL SELECT Sno,Cno,Gr

15、ade INTO :HSno,:HCno,:HGrade:Gradeid FROM SC WHERE Sno=:givensno AND Cno=:givencno;n非非CURRENT形式的增删改语句：形式的增删改语句：在在UPDATE的的SET子句和子句和 WHERE子句中可使用主变量，子句中可使用主变量，SET子句还可使用指示变量。子句还可使用指示变量。12/19例例4修改某个学生选修修改某个学生选修1号课程的成绩。号课程的成绩。 EXEC SQL UPDATE SC SET Grade=:newgrade WHERE Sno=:givensno;例例5将计算机系全体学生年龄置将计算机系

16、全体学生年龄置NULL值。值。 sageid=-1; EXEC SQL UPDATE Student SET Sage=:Raise:sageid WHERE Sdept=CS; 例例6某个学生退学了，现要将有关他的所有选课记录删除掉。某个学生退学了，现要将有关他的所有选课记录删除掉。 EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname); 例例7 某个学生选修了某门课程，将有关记录插入到某个学生选修了某门课程，将有关记录插入到SC表中。表中。 gradeid=-1; EXEC SQL

17、 INSERT INTO SC VALUES(:stdno,:couno,:gr:grageid);13/19n使用游标的使用游标的SQL语句语句n查询结果为多条记录的查询结果为多条记录的SELECT语句：语句：SELECT语句的查询结语句的查询结果是多条记录，使用游标机制可将多条记录一次一条送主程序果是多条记录，使用游标机制可将多条记录一次一条送主程序处理，从而把对集合的操作转换为对单个记录的处理。方法处理，从而把对集合的操作转换为对单个记录的处理。方法：n说明游标：说明游标：EXEC SQL DECLARE CURSORn FOR ;n打开游标：打开游标：EXEC SQL OPEN ; n

18、 即执行相应即执行相应SELECT语句，将查询结果取到缓冲区中，游标语句，将查询结果取到缓冲区中，游标处于活动状态，指针指向查询结果集中第一条记录。处于活动状态，指针指向查询结果集中第一条记录。n推进游标指针并取当前记录：推进游标指针并取当前记录：EXEC SQL FETCH n INTO ； 其中主变量必须与其中主变量必须与SELECT语句中的目标列表达式一一对应。语句中的目标列表达式一一对应。n关闭游标：关闭游标：EXEC SQL CLOSE ;14/19nCURRENT形式的形式的UPDATE和和DELETE语句语句nUPDATE和和DELETE语句都是面向集合的操作，若只想修改语句都是

19、面向集合的操作，若只想修改或删除其中某个记录，则需用带游标的或删除其中某个记录，则需用带游标的SELECT语句查出所有语句查出所有满足条件的记录，找出要修改或删除的记录，然后用满足条件的记录，找出要修改或删除的记录，然后用CURRENT形式的形式的UPDATE语句和语句和DELETE语句修改或删除之语句修改或删除之。n UPDATE和和DELETE语句中要用语句中要用WHERE CURRENT OF 来表示修改或删除的是最近一次取出的记录，即游标来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录。指针指向的记录。 注意：当游标定义中注意：当游标定义中SELECT语句带有语句带有UNI

20、ON或或ORDER BY子子句时，或者该句时，或者该SELECT语句相当于定义了一个不可更新的视图语句相当于定义了一个不可更新的视图时，不能使用时，不能使用CURRENT形式的形式的UPDATE和和DELETE语句。语句。15/19nPL/SQL：是编写数据库存储过程的一种过程语言，它结合了是编写数据库存储过程的一种过程语言，它结合了SQL的数据操纵能力和过程化语言的流程控制能力，是的数据操纵能力和过程化语言的流程控制能力，是SQL的的过程化扩展。过程化扩展。n存储过程的概念：存储过程的概念：是由是由PL/SQL语句书写的过程，经编译和优语句书写的过程，经编译和优化后存储在数据库服务器中，使用

21、时只需调用即可。化后存储在数据库服务器中，使用时只需调用即可。n存储过程的优点存储过程的优点n由于存储过程不像解释执行的由于存储过程不像解释执行的SQL语句那样在提出操作请求时语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，提供了在服务才进行语法分析和优化工作，因而运行效率高，提供了在服务器端快速执行器端快速执行SQL语句的有效途径。语句的有效途径。n降低了客户机和服务器之间的通信量。降低了客户机和服务器之间的通信量。n可以将企业规则写成存储过程，当企业规则发生变化时只要修可以将企业规则写成存储过程，当企业规则发生变化时只要修改存储过程，无须修改其他应用程序。改存储过程，无须修改其他应用程序。16/19n创建存储过程创建存储过程n CREATE PROCEDURE 过程名过程名(参数列表参数列表)/*过程首部过程首部*/