第八章数据库编程pptConvertor.docx

第八章数据库编程pptConvertor.docx

《第八章数据库编程pptConvertor.docx》由会员分享，可在线阅读，更多相关《第八章数据库编程pptConvertor.docx（32页珍藏版）》请在冰豆网上搜索。

第八章数据库编程pptConvertor

数据库原理

咸阳师范学院信息工程学院

第八章数据库编程

本章主要教学内容:

8.1嵌入式SQL

8.2存储过程

8.3ODBC编程

重点及难点：

了解SQL语言的两种方式；

掌握游标的使用；

掌握存储过程的使用；

掌握ODBC编程；

数据库编程

应用系统中使用SQL编程来访问和管理数据库中数据的方式主要有：

嵌入式SQL、PL/SQL、ODBC编程、JDBC编程和OLEDB编程等方式。

嵌入式SQL

SQL语言提供了两种不同的使用方式：

交互式

嵌入式

为什么要引入嵌入式SQL

SQL语言是非过程性语言

事务处理应用需要高级语言

这两种方式细节上有差别，在程序设计的环境下，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的处理过程

为了区分SQL语句与主语言语句，需要:

前缀：

EXECSQL

结束标志：

随主语言的不同而不同

以C为主语言的嵌入式SQL语句的一般形式

EXECSQL;

例：

EXECSQLDROPTABLEStudent;

以COBOL作为主语言的嵌入式SQL语句的一般形式

EXECSQLEND-EXEC

例：

EXECSQLDROPTABLEStudentEND-EXEC

DBMS处理宿主型数据库语言SQL的方法

预编译

修改和扩充主语言使之能处理SQL语句

预编译

1．由DBMS的预处理程序对源程序进行扫描，识别出SQL语句

2．把它们转换成主语言调用语句，以使主语言编译程序能识别它

3．最后由主语言的编译程序将整个源程序编译成目标码。

嵌入SQL语句

说明性语句

嵌入SQL语句数据定义

可执行语句数据控制

数据操纵

允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句

允许出现说明语句的地方，都可以写说明性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.2嵌入式SQL语句与主语言之间的通信

将SQL嵌入到高级语言中混合编程，程序中会含

有两种不同计算模型的语句

SQL语句

描述性的面向集合的语句

负责操纵数据库

高级语言语句

过程性的面向记录的语句

负责控制程序流程

工作单元之间的通信方式

1.SQL通信区

向主语言传递SQL语句的执行状态信息

主语言能够据此控制程序流程

2.主变量

1）主语言向SQL语句提供参数

2）将SQL语句查询数据库的结果交主语言进一步处理

3.游标

解决集合性操作语言与过程性操作语言的不匹配

1.SQL通信区

SQLCA：

SQLCommunicationArea

SQLCA是一个数据结构

SQLCA的用途

SQL语句执行后，DBMS反馈给应用程序信息

描述系统当前工作状态

描述运行环境

这些信息将送到SQL通信区SQLCA中

应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句

SQLCA的内容

与所执行的SQL语句有关

与该SQL语句的执行情况有关

例：

在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息：

违反数据保护规则，操作拒绝

没有满足条件的行，一行也没有删除

成功删除，并有删除的行数

无条件删除警告信息

由于各种原因，执行出错

SQLCA的使用方法

定义SQLCA

用EXECSQLINCLUDESQLCA加以定义

使用SQLCA

SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE

如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错

应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理

2.主变量

什么是主变量

嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据

在SQL语句中使用的主语言程序变量简称为主变量（HostVariable）

主变量

主变量的类型

输入主变量

由应用程序对其赋值，SQL语句引用

输出主变量

由SQL语句赋值或设置状态信息，返回给应用程序

一个主变量有可能既是输入主变量又是输出主变量

主变量

主变量的用途

输入主变量

指定向数据库中插入的数据

将数据库中的数据修改为指定值

指定执行的操作

指定WHERE子句或HAVING子句中的条件

输出主变量

获取SQL语句的结果数据

获取SQL语句的执行状态

主变量

指示变量

一个主变量可以附带一个指示变量（IndicatorVariable）

什么是指示变量

整型变量

用来“指示”所指主变量的值或条件

指示变量的用途

输入主变量可以利用指示变量赋空值

输出主变量可以利用指示变量检测出是否空值，值是否被截断

主变量

在SQL语句中使用主变量和指示变量的方法

1）说明主变量和指示变量

BEGINDECLARESECTION

.........

.........（说明主变量和指示变量）

.........

ENDDECLARESECTION

主变量

2）使用主变量

说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现

为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:

）作为标志

主变量

3）使用指示变量

指示变量前也必须加冒号标志

必须紧跟在所指主变量之后

主变量

在SQL语句之外（主语言语句中）使用主变量和指示变量的方法

可以直接引用，不必加冒号

3.游标（cursor）

为什么要使用游标

SQL语言与主语言具有不同数据处理方式

SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录

游标

主语言是面向记录的，一组主变量一次只能存放一条记录

仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求

嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式

游标

什么是游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果

每个游标区都有一个名字

用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理

四、建立和关闭数据库连接

嵌入式SQL程序要访问数据库必须先连接数据库。

RDBMS根据用户信息对连接请求进行合法性验证，只有通过了身份验证，才能建立一个可用的合法连接。

四、建立和关闭数据库连接

1、建立数据库连接

EXECSQLCONNECTTOtarget[ASconnection-name][USERuser-name]

Target是要连接的数据库服务器，

connection-name是可选的连接名，连接必须是一个有效的标识符，主要用来识别一个程序内同时建立的多个连接，如果在整个程序内只有一个连接也可以不指定连接名。

四、建立和关闭数据库连接

2、关闭数据库连接

当某个连接上的所有数据库操作完成后，应用程序应该主动释放所占用的连接资源。

EXECSQLDISCONNECT[connection]

Connection是所建立的数据库连接。

[例1]依次检查某个系的学生记录,交互式更新某些学生年龄。

EXECSQLBEGINDECLARESECTION;

Chardeptname[20];

CharHSno[9];

CharHSname[20];

CharHSex[2];

例题

例题

intHSage;

intNEWAGE;

EXECSQLENDDECLARESECTION;

LongSQLCODE;

EXECSQLINCLUDEsqlca;

例题

intmain（void）

{

intcount=0;

charyn;

printf（“Pleasechoosethedepartmentname（CS/MA/IS）:

”）;

scanf（“%s”,&deptname）;

例题

EXECSQLCONNECTTOTEST@localhost:

54321USER“SYSTEM”/”MANAGER”;

EXECSQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:

deptname;

EXECSQLOPENSX;

For（;;）

{EXECSQLFETCHSXINTO:

HSno,:

HSname,:

HSsex,:

HSage;

例题

if（sqlca.,sqlcode!

=0）

Break;

if（count++==0）

Printf（“\n%-10s%-20s%-10s%-10s\n”,”Sno”,”Sname”,”Ssex”,”Sage”）;

Printf（“%-10s%-20s%-10s%-10d\n”,HSno,HSname,HSsex,HSage）;

例题

printf（“UPDATEAGE（y/n）?

”）;

do{

scanf（“%c”,&yn）;

}

while（yn!

=‘N’&&yn!

=‘n’&&yn!

=‘Y’&&yn!

=‘y’）;

例题

if（yn==‘y’||yn==‘Y’）

{

print（“INPUTNEWAGE:

”）;

scanf（“%d”,&NEWAGE）;

EXECSQLUPDATEStudent

SETSage=:

NEWAGE

WHERECURRENTOFSX;

}

}

例题

EXECSQLCLOSESX;

EXECSQLCOMMITWORK;

EXECSQLDISCONNECTTEST;

}

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.3不用游标的SQL语句

不用游标的SQL语句的种类

说明性语句

数据定义语句

数据控制语句

查询结果为单记录的SELECT语句

非CURRENT形式的增删改语句

一、查询结果为单记录的SELECT语句

语句格式

EXECSQLSELECT[ALL|DISTINCT]

<目标列表达式>[,<目标列表达式>]...

　INTO<主变量>[<指示变量>]

[,<主变量>[<指示变量>]]...

FROM<表名或视图名>[,<表名或视图名>]...

[WHERE<条件表达式>]

[GROUPBY<列名1>[HAVING<条件表达式>]]

[ORDERBY<列名2>[ASC|DESC]];

查询结果为单记录的SELECT语句

对交互式SELECT语句的扩充就是多了一个INTO子句

把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。

查询结果为单记录的SELECT语句

使用注意事项

1.使用主变量

INTO子句

WHERE子句的条件表达式

HAVING短语的条件表达式

查询结果为单记录的SELECT语句

2.使用指示变量

指示变量只能用于INTO子句中

如果INTO子句中主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，但不向该主变量执行赋值操作，即主变量值仍保持执行SQL语句之前的值

当发现指示变量值为负值时，不管主变量为何值，均应认为主变量值为NULL

查询结果为单记录的SELECT语句

3.查询结果为空集

如果数据库中没有满足条件的记录，即查询结果为空，则DBMS将SQLCODE的值置为100

4.查询结果为多条记录

程序出错，DBMS会在SQLCA中返回错误信息

查询结果为单记录的SELECT语句

例2根据学生号码查询学生信息。

假设已将要查询的学生的学号赋给了主变量givensno

EXECSQLSELECTSno,Sname,Ssex,Sage,Sdept

INTO:

Hsno,:

Hname,:

Hsex,:

Hage,:

Hdept

FROMStudent

WHERESno=:

givensno;

Hsno,Hname,Hsex,Hage,Hdept和givensno均是主变量，并均已在前面的程序中说明过了。

查询结果为单记录的SELECT语句

例3查询某个学生选修某门课程的成绩。

假设已将要查询的学生的学号赋给了主变量givensno，将课程号赋给了主变量givencno。

EXECSQLSELECTSno,Cno,Grade

INTO:

Hsno,:

Hcno,:

Hgrade:

Gradeid

FROMSC

WHERESno=:

givensnoAND

Cno=:

givencno;

查询结果为单记录的SELECT语句

从提高应用程序的数据独立性角度考虑，SELECT语句在任何情况下都应该使用游标

对于仅返回一行结果数据的SELECT语句虽然可以不使用游标

但如果以后数据库改变了，该SELECT语句可能会返回多行数据，这时该语句就会出错

二、非CURRENT形式的增删改语句

非CURRENT形式的UPDATE语句

使用主变量

SET子句

WHERE子句

使用指示变量

SET子句

非CURRENT形式的UPDATE语句可以操作多条元组

例4修改某个学生选修1号课程的成绩。

假设该学生的学号已赋给主变量givensno，

修改后的成绩已赋给主变量newgrade。

EXECSQLUPDATESC

SETGrade=:

newgrade

WHERESno=:

givensno;

二、非CURRENT形式的增删改语句

二、非CURRENT形式的增删改语句

例5将计算机系全体学生年龄置NULL值

Sageid=-1;

EXECSQLUPDATEStudent

SETSage=:

Raise:

Sageid

WHERESdept='CS';

二、非CURRENT形式的增删改语句

将指示变量Sageid赋一个负值后，无论主变量Raise为何值，DBMS都会将CS系所有记录的年龄属性置空值。

它等价于：

EXECSQLUPDATEStudent

SETSage=NULL

WHERESdept='CS';

二、非CURRENT形式的增删改语句

非CURRENT形式的DELETE语句

使用主变量

WHERE子句

非CURRENT形式的DELETE语句可以操作多条元组

二、非CURRENT形式的增删改语句

例6某个学生退学了，现要将有关他的所有选课记录删除掉。

假设该学生的姓名已赋给主变量stdname

EXECSQLDELETE

FROMSC

WHERESno=

（SELECTSno

FROMStudent

WHERESname=:

stdname）;

二、非CURRENT形式的增删改语句

非CURRENT形式的INSERT语句

使用主变量

VALUES子句

使用指示变量

VALUES子句

非CURRENT形式的INSERT语句一次只能输入一条元组

二、非CURRENT形式的增删改语句

例7某个学生新选修了某门课程，将有关记录插入SC表

假设学生的学号已赋给主变量stdno，课程号已赋给主变量couno。

gradeid=-1;

EXECSQLINSERT

INTOSC（Sno,Cno,Grade）

VALUES（:

stdno,:

couno,:

gr:

gradeid）;

由于该学生刚选修课程，尚未考试，因此成绩列为空。

所以本例中用指示变量指示相应的主变量为空值。

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.4使用游标的SQL语句

必须使用游标的SQL语句

查询结果为多条记录的SELECT语句

CURRENT形式的UPDATE语句

CURRENT形式的DELETE语句

一、查询结果为多条记录的SELECT语句

使用游标的步骤

1.说明游标

2.打开游标

3.移动游标指针，然后取当前记录

4.关闭游标

1.说明游标

使用DECLARE语句

语句格式

EXECSQLDECLARE<游标名>CURSOR

FOR;

功能

是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。

2.打开游标

使用OPEN语句

语句格式

EXECSQLOPEN<游标名>;

功能

打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中

这时游标处于活动状态，指针指向查询结果集中第一条记录之前

3.移动游标指针，然后取当前记录

使用FETCH语句

语句格式

EXECSQLFETCH[[NEXT|PRIOR|

FIRST|LAST]FROM]<游标名>

INTO<主变量>[<指示变量>]

[,<主变量>[<指示变量>]]...;

移动游标指针，然后取当前记录

功能

指定方向推动游标指针，然后将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。

NEXT|PRIOR|FIRST|LAST：

指定推动游标指针的方式。

NEXT：

向前推进一条记录

PRIOR：

向回退一条记录

FIRST：

推向第一条记录

LAST：

推向最后一条记录

缺省值为NEXT

移动游标指针，然后取当前记录

说明

（1）主变量必须与SELECT语句中的目标列表达式具有一一对应关系

（2）FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理

（3）为进一步方便用户处理数据，现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针

4.关闭游标

使用CLOSE语句

语句格式

EXECSQLCLOSE<游标名>;

功能

关闭游标，释放结果集占用的缓冲区及其他资源

说明

游标被关闭后，就不再和原来的查询结果集相联系

被关闭的游标可以再次被打开，与新的查询结果相联系

例题

例8查询某个系全体学生的信息（学号、姓名、性别和年龄）。

要查询的系名由用户在程序运行过程中指定，放在主变量deptname中

......

......

EXECSQLINCLUDESQLCA;

EXECSQLBEGINDECLARESECTION;

例题

......

/*说明主变量deptname,HSno,HSname,HSsex,HSage等*/

......

......

EXECSQLENDDECLARESECTION;

......

......

gets（deptname）;/*为主变量deptname赋值*/

......

例题

EXECSQLDECLARESXCURSORFOR

SELECTSno,Sname,Ssex,Sage

FROMStudent

WHERESDept=:

deptname;/*说明游标*/

EXECSQLOPENSX/*打开游标*/

例题

WHILE

（1）/*用循环结构逐条处理结果集中的记录*/

{

EXECSQLFETCHSXINTO:

HSno,:

HSname,:

HSsex,:

HSage;

/*将游标指针向前推进一行，然后从结

果集中取当前行，送相应主变量*/

例题

if（sqlca.sqlcode<>SUCCESS）

break;

　/*若所有查询结果均已处理完或出现

SQL语句错误，则退出循环*/

/*由主语言语句进行进一步处理*/

......

......

};

例题

EXECSQLCLOSESX;/*关闭游标*/

......

......

二、CURRENT形式的UPDATE语句和DELETE语句

CURRENT形式的UPDATE语句和DELETE语句的用途

非CURRENT形式的UPDATE语句和DELETE语句

面向集合的操作

一次修改或删除所有满足条件的记录

二、CURRENT形式的UPDATE语句和DELETE语句

CURRENT形式的UPDATE语句和DELETE语句的用途

如果只想修改或删除其中某个记录

用带游标的SELECT语句查出所有满足条件的记录

从中进一步找出要修改或删除的记录

用CURRENT形式的UPDATE语句和DELETE语句修改或删除之

CURRENT形式的UPDATE语句和DELETE语句

步骤

（1）DECLARE

说明游标

（2）OPEN

打开游标，把所有满足查询条件的记录从指定表取至缓冲区

（3）FETCH

推进游标指针，并把当前记录从缓冲区中取出来送至主变量

CURRENT形式的UPDATE语句和DELETE语句

（4）检查该记录是否是要修改或删除的记录，是则处理之

（5）重复第（3）和（4）步，用逐条取出结果集中的行进行判断和处理

（6）CLOSE

关闭游标，释放结果集占用的缓冲区和其他资源

CURRENT形式的UPDATE语句和DELETE语句

说明游标

为UPDATE语句说明游标

使用带FORUPDATEOF<列名>短语的DECLARE语句

语句格式

EXECSQLDECLARE<游标名>CURSOR

FORFORUPDATE

OF<列名>;

FORUPDATEOF<列名>短语用于指明检索出的数据在指定列上是可修改的，以便DBMS进行并发控制

CURRENT形式的UPDATE语句和DELETE语句

为DELETE语句说明游标

使用带FORUPDATE短语的DECLARE语句

语句格式

EXECSQLDECLARE<游标名>CURSOR

FORFORUPDATE;

FORUPDATE短语提示DBMS进行并发控制

CURRENT