【数据库系统】数据库系统概论====第八章 数据库编程

第八章 数据库编程

---

前言

SQL编程语言技术可以有效克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统间的互操作性。
SQL编程访问和管理数据库的方式：嵌入式SQL、过程化SQL、存储过程和定义函数、开放数据库互连、OLE DB、Java数据库连接等编程方式。

8.1嵌入式SQL

SQL语言的特点之一是交互式和嵌入式两种不同的使用方式下，SQL的语法结构基本一致。在程序设计的环境下，SQL语句要做某些必要的扩充。

8.1.1嵌入式SQL的处理过程

主语言：嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。
嵌入式SQL前缀：为了区分SQL语句与主语言语句，所有SQL语句必须加前缀。
嵌入式SQL处理过程：预编译方法。具体过程如下图：

8.1.2嵌入式SQL语句与主语言之间的通信

嵌入式SQL语句中会含有两种不同计算模型的语句：SQL语句负责操纵数据库，高级语言语句负责控制逻辑流程。
数据库单元工作与源程序工作单元之间的通信：
（1）向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程，主要用SQL通信区（SQLCA：SQL Communication Area）实现。
（2）主语言向SQL语句提供参数，主要用主变量（host variable）实现。
（3）将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标（cursor）实现。

1. SQL通信区
   SQLCA的用途：SQL语句执行后，系统将描述系统当前工作状态的数据和描述运行环境的数据送到SQL通信区中，应用程序从SQL通信区中取出这些状态信息，据此决定接下来执行的语句。
   定义SQLCA格式：

EXEC SQL INCLUDE SQLCA；

说明：

• SQLCA中设置变量SQLCODE，存放每次执行SQL语句后返回代码。
• 当SQLCODE等于SUCCESS，表示SQL语句成功，否则出错。
• 应用程序没执行完一条SQL语句都应该测试一下SQLCODE的值，以了解SQL语句执行情况并做相应处理。

2. 主变量
   嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据，在SQL语句中使用的主语言程序变量，简称为主变量。
   输入主变量：由应用程序对其赋值，SQL语句引用。
   输出主变量：由SQL语句对其赋值或设置状态信息，返回给应用程序。
   指示变量：是一个整型变量，一个主变量可以附带一个指示变量，用来指示所指主变量的值或条件，例如指示变量的用途、指示输入主变量是否为空值、检测输出变量是否为空值、值是否被截断等。
   定义主变量和指示变量的格式：

BEGIN DECLARE SECTION
...
...（说明主变量和指示变量）
...
END DECLARE SECTION

说明：

• 定义的主变量可以在SQL语句中任何一个能够使用表达式的地方出现。
• 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（：）作为标志。
• 指示变量必须紧跟在所指主变量之后，指示变量前也必须加冒号标志。
• 在SQL语句之外（主语言语句中）可以直接引用主变量和指示变量，不必加冒号。

3. 游标
   （1）为什么要使用游标
   SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录。主语言是面向记录的，一组主变量一次只能存放一条记录。
   仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求。
   嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式。
   （2）什么是游标
   游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
4. 建立和关闭数据库连接
   （1）建立数据库连接
   格式：

EXEC SQL CONNECT TO target[AS connection-name][USER user-name]；

说明：

• target是要连接的数据库服务器，常见的如：包含服务器标识的SQL串常量、@:、DEFAULT。
• connection-name指定的连接名。连接名必须是一个有效的标识符。如果在整个程序内只有一个连接时可以不指定连接名。
• 程序运行过程中可以用以下语句修改当前连接：

EXEC SQL SET CONNECTION connection-name|DEFAULT；

（2）关闭数据库连接
格式：

EXEC SQL DISCONNECT [connection]；

说明：
connection是用EXEC SQL CONNECT所建立的数据库连接。

8.1.3不用游标的SQL语句

当SQL语句为以下种类时，可以不用游标：
（1）说明性语句。
（2）数据定义语句。
（3）数据控制语句。
（4）查询结果为单词记录的SELECT语句。
（5）非CURRENT的增删改语句。

1. 查询结果为单词记录的SELECT语句
   这类语句不需要使用游标，只需要INTO子句指定存放查询结果的主变量。
   说明：

• INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量。
• 查询结果的列为空值NULL，系统会自动将主变量后的指示变量设置为负值，当指示变量为负值时，不管主变量为何值，系统均认为主变量为NULL。
• 如果查询结果实际上是多条记录，则程序出错，关系数据库管理系统会在SQLCA中返回错误信息。

2. 非CURRENT形式的增删改语句
   在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量。

8.1.4使用游标的SQL语句

必须使用游标的SQL语句如下：
（1）查询结果为多条记录的SELECT语句。
（2）CURRENT形式的UPDATE语句。
（3）CURRENT形式的DELETE语句。

1. 查询结果为多条记录的SELECT语句
   使用游标的步骤：
   ①说明游标。
   ②打开游标。
   ③推进游标指针并取当前记录。
   ④关闭游标。
   （1）说明游标
   语句格式：

EXEC SQL DECLARE<游标名>CURSOR FOR <SELECT语句>；

功能：该语句是一条说明性语句，这时关系数据库管理系统并不执行SELECT语句。
（2）打开游标
语句格式：

EXEC SQL OPEN<游标名>；

功能：执行相应的SELECT语句，把查询结果取到缓冲区中，这时游标处于活动状态，指针指向查询结果集中的第一条记录。
（3）推进游标指针并取当前记录
语句格式：

EXEC SQL FETCH<游标名>
INTO<主变量>[<指示变量>][，<主变量>[<指示变量>]]...；

功能：指定方向推动游标指针，同时将缓冲区中的当前记录取出来送至主变量提供主语言进一步处理。
（4）关闭游标
语句格式：

EXEC SQL CLOSE<游标名>；

功能：关闭游标，释放结果集占用的缓冲区及其他资源。
说明：
①游标被关闭后，就不再和原来的查询结果集相联系。
②被关闭的游标可以再次被打开，与新的查询结果相联系。
2. CURRENT形式的UPDATE语句和DELETE语句
如修改或删除某个记录，用带游标的SELECT语句查出满足条件的集合，从中进一步找出要修改或删除的记录，用CURRENT形式的UPDATE语句和DELETE语句修改或删除之。
UPDATE语句和DELETE语句中要用子句：

WHERE CURRENT OF<游标名>

表示修改或删除游标指针指向的记录。
注意：游标定义中的SELECT语句带有UNION或ORDER BY子句，或者该DELECT语句相当于定义了一个不可更新的视图，则不能使用CURRENT形式的UPDATE语句和DELETE语句。

8.1.5动态SQL

静态嵌入式SQL：静态嵌入式SQL语句能够满足一般要求，无法满足要到执行时才能够确定要提交的SQL语句、查询的条件。
动态嵌入式SQL：在程序运行过程中可临时“组装”SQL语句，支持动态组装SQL语句和动态参数两种形式。

1. 使用SQL语句主变量
   SQL语句主变量：程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量。
   SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行。
2. 动态参数
   动态参数是SQL语句中的可变元素，使用参数符号（？）表示该位置的数据在运行时设定。
   与主变量不同动态参数的输入不是编译时完成绑定，而是通过PREPARE语句准备主变量和执行语句EXECUTE绑定数据或主变量来完成。
   使用动态参数的步骤：
   （1）声明SQL语句主变量
   SQL语句主变量的值包含动态参数（？）。
   （2）准备SQL语句（PREPARE）

EXEC SQL PREPARE<语句名>FROM<SQL语句主变量>；

（3）执行准备好的语句（EXECUTE）
格式：

EXEC SQL EXECUTE<语句名>
[INTO<主变量表>][USING<主变量或常量>]；

8.2过程化SQL

8.2.1过程化SQL的块结构

过程化SQL是SQL的扩展，增加了过程化语句功能，基本结构是块，每个块完成一个逻辑操作，块之间可以互相嵌套。
过程化SQL块的基本结构：

1. 定义部分
   DECLARE：定义的变量、常量等只能在该基本块中使用。
   变量、常量、游标、异常等：当基本块执行结束时，定义就不再存在。
2. 执行部分

BEGIN
SQL语句、过程化SQL的流程控制语句
EXCEPTION
异常处理部分
END；

8.2.2变量和常量的定义

1. 变量定义
   变量名 数据类型 [[[NOT NULL]：=初值表达式]
   或
   变量名 数据类型 [[NOT NULL] 初值表达式]
2. 常量定义
   常量名 数据类型 CONSTANT：=常量表达式
   常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它2，过程化SQL将返回一个异常。
3. 赋值语句
   变量名称：=表达式

8.2.3流程控制

过程化SQL主要提供了条件控制语句、循环控制语句等流程控制语句，这些语句的语法、语义和一般高级语言类似。

1. 条件控制语句
   IF-THEN、IF-THEN-ELSE、嵌套的IF语句。
   （1）IF语句格式

IF 条件 THEN
语句；
END IF；

（2）IF-THEN-ELSE语句格式

IF 条件 THEN
条件为真时执行的语句；
ELSE
条件为假或NULL时执行的语句；
END IF；

（3）嵌套的IF语句
在THEN和ELSE子句中还可以再包含IF语句，即IF语句可以嵌套。
2. 循环控制语句
主要有LOOP，WHILE-LOOP和FOR-LOOP三类。
（1）简单的循环语句LOOP格式

LOOP
循环执行的语句；
END LOOP；

多数数据库服务器的过程化SQL都提供EXIT、BREAK或LEAVE等循环结束语句，保证LOOP语句块能够结束。
（2）WHILE-LOOP循环格式

WHILE 条件 LOOP
条件为真时执行的语句；
END LOOP；

执行过程：每次执行循环体语句之前，首先对条件进行求值，如果条件为真，则执行循环体内的语句序列，如果条件为假，则跳过循环并把控制传递给下一个语句。
（3）FOR-LOOP循环格式

FOR 循环遍历 IN [REVERSE] bound1 ... bound2 LOOP
循环语句；
END LOOP；

执行过程：循环变量设置为下界bound1，检查循环变量是否小于上界bound2，如果是执行循环体，循环变量加1，如果否，退出循环。当设置REVERSE，表示bound1为上界，bound2为下界，循环变量减1.
3. 错误处理

• 如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句。
• SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器提供完善的异常处理机制。

8.3存储过程和函数

过程化SQL块分为匿名块和命名块两类。
匿名块：每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用。
命名块：编译后保存在数据库中，可以被反复调用，运行速度较快，过程和函数是命名块。

8.3.1存储过程

存储过程：由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。

1. 存储过程的优点
   （1）运行效率高。
   （2）降低了客户机和服务器之间的通信量。
   （3）方便实施企业规划。
2. 存储过程的用户接口
   （1）创建存储过程格式

CREATE OR REPLACE PROCEDURE 过程名（[参数1，参数2，...]）
AS <过程化SQL块>；

说明：
①过程名：数据库服务器合法的对象标识。
②参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数，默认为输入参数。
③过程体：是一个<过程化SQL块>，包括声明部分和可执行语句部分。
（2）执行存储过程

CALL/PERFORM PROCEDURE 过程名（[参数1，参数2，...]）；

说明：
使用CALL或者PERFORM等方式激活存储过程的执行，在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程。
（3）修改存储过程

ALTER PROCEDURE 过程名1 RENAME TO 过程名2；

（4）删除存储过程

DROP PROCEDURE 过程名（）；

8.3.2函数

本章所讲的函数是用户自定义函数，函数和存储过程类似都是持久性存储模块，不同的是函数必须指定返回的类型。

1. 函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 （[参数1，参数2，...]）
RETURNS <类型> AS <过程化SQL块>；

2. 函数的执行语句格式

CALL/SELECT 函数名 （[参数1，参数2，...]）；

3. 修改函数
   重命名：ALTER FUNCTION 函数名1 RENAME TO 函数名2；
   重新编译：ALTER FUNCTION 函数名 COMPILE；

8.3.3过程化SQL中的游标

在过程化SQL中如果查询返回多条记录时，就要使用游标对结果集进行处理。在存储过程中可以定义普通游标、REFCURSOR类型游标、带参数游标等。

8.4 ODBC编程

使用ODBC编写的应用程序可移植性好，能同时访问不同的数据库，共享多个数据资源。

8.4.1ODBC概述

1. ODBC产生的原因
   （1）由于不同的数据库管理系统的存在，在某个关系数据库管理系统下编写的应用程序就不能在另一个关系数据库管理系统下运行。
   （2）许多应用程序需要共享多个部门的数据资源，访问不同的关系数据库管理系统。
2. 什么是ODBC
   ODBC是微软公司开放服务体系中有关数据库的一个组成部分，它提供了一组访问数据库的应用程序编程接口（API）。
   主要作用是规范应用开发和规范关系数据库管理系统应用接口。

8.4.2ODBC规则原理概述

ODBC应用系统的体系结构包括：用户应用程序、ODBC驱动程序管理器、数据库驱动程序、数据源。如下图：

1. 用户应用程序
   ODBC应用程序包括的内容：
   （1）请求连接数据库。
   （2）向数据源发生SQL语句。
   （3）为SQL语句执行结果分配存储空间，定义读取的数据格式。
   （4）获取数据库操作结果或处理错误。
   （5）进行数据处理并向用户提交处理结果。
   （6）请求事务的提交和回滚操作。
   （7）断开与数据源的连接。
2. ODBC驱动程序管理器
   驱动程序管理器包含在ODBC32.DLL中，对用户透明，管理应用程序和驱动程序之间的通信。
   主要功能：
   （1）装载ODBC驱动程序。
   （2）选择和连接正确的驱动程序。
   （3）管理数据源。
   （4）检查ODBC调用参数的合法性。
   （5）记录ODBC函数的调用等。
3. 数据库驱动程序
   ODBC通过驱动程序来提供应用系统与数据库平台的独立性，ODBC应用程序不能直接存取数据库，各种操作由驱动程序提交给DBMS的ODBC驱动程序，调用驱动程序支持的函数来存取数据库。

ODBC驱动程序类型：

• 单束：数据源和应用程序在同一台机器上，驱动程序直接完成对数据文件的I/O操作，驱动程序相当于数据管理器。
• 多束：支持客户机-服务器、客户机-应用服务器/数据库服务器等网络环境下的数据访问，由驱动程序完成数据库访问请求的提交和结果集接收，应用程序使用驱动程序提供的结果集管理接口操纵行后的结果数据。

4. ODBC数据源管理
   数据源：是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，是一种数据连接的抽象。
   说明：

• ODBC给每个被访问的数据源指定唯一的数据源名，并映射到所有必要的、用来存取数据的低层软件。
• 在连接中，用数据源名来代表用户名、服务器名、所连接的数据库名等。用户无须知道数据库历系统或其他数据管理软件、网络以及有关ODBC驱动程序的细节。

8.4.3ODBC API基础

ODBC应用程序编程接口的一致性是指API一致性和语法一致性。

1. 函数概述
   ODBC 3.0标准提供了76个函数接口，大致可分为：
   （1）分配和释放环境句柄、连接句柄、语句句柄。
   （2）连接函数（SQLDriverconnect等）。
   （3）与信息相关的函数（SQLGetinfo、SQLGetFunction等）。
   （4）事务处理函数（如SQLEndTran）。
   （5）执行相关函数（SQLExecdirect、SQLExecute等）。
   （6）编目函数，ODBC 3.0提供了11 个编目函数，如SQLTables、SQLColumn等。
2. 句柄及其属性
   句柄是32位整数值，代表一个指针。
   ODBC 3.0中句柄分为环境句柄、连接句柄、语句句柄、描述符句柄。具体关系如下图：
   
   说明：
   ①每个ODBC应用程序需要建立一个ODBC环境，分配一个环境句柄，存取数据的全局性背景，如环境状态、当前环境状态诊断、当前在环境上分配的连接句柄等。
   ②一个环境句柄可以建立多个连接句柄，每一个连接句柄实现与一个数据源之间的连接。
   ③在一个连接中可以建立多个语句句柄，它不只是一个SQL语句，还包括SQL语句产生的结果集以及相关的信息等。
   ④在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL语句的参数、结果集列的元数据集合。
3. 数据类型
   ODBC数据类型：SQL数据类型用于数据源；C数据类型用于应用程序的C代码。
   应用程序可以通过SQLGetTypeInfo来获取不同的驱动程序对于数据类型的支持情况。
   SQL数据类型和C数据类型之间的转换规则：

	SQL数据类型	C数据类型
SQL数据类型	数据源之间转换	应用程序变量传送到语句参数（SQLBindparameter）
C数据类型	从结果集列中返回到应用程序变量（SQLBindcol）	应用程序变量之间转换

8.4.4ODBC的工作流程

1. 配置数据源
   配置数据源有两种方法：一是运行数据源管理工具来进行配置，二是使用Driver Manager提供的ConfigDsn函数来增加、修改或删除数据源。
2. 初始化环境
   没有和具体的驱动程序相关联，由Driver Manager来进行控制，并配置环境属性。应用程序通过调用连接函数和某个数据源进行连接后，Driver Manager才调用所连的驱动程序中的SQLAllocHandle，来真正分配环境句柄的数据结构。
3. 建立连接
   应用程序调用SQLAllocHandle分配连接句柄，通过SQLConnect、SQLDriverConnect或SQLBrowseConnect与数据源连接。
4. 分配语句句柄
   处理任何SQL语句之前，应用程序还需要首先分配一个语句句柄，语句句柄含有具体的SQL语句以及输出的结果集等信息。
5. 执行SQL语句
   处理SQL语句的两种方式：预处理（SQLPrepare、SQLExecute适用于语句的多次执行）和直接执行（SQLExecdirect）。
   应用程序根据语句类型进行的处理；
   有结果集的语句则进行结果集处理。
   没有结果集的函数，可以直接利用本语句句柄继续执行新的语句或是获取行计数（本次执行所影响的行数）之后继续执行。
6. 结果集处理
   应用程序可以通过SQLNumResultCols来获取结果集中的列数通过SQL DescribeCol或是SQLColAttribute函数来获取结果集每一列的名称、数据类型、精度和范围。
   ODBC中使用游标来处理结果集数据，ODBC中游标类型有：
   ①Forward-only游标（ODBC的默认游标类型）：只能在结果集中向前滚动，它是ODBC的默认游标类型。
   ②可滚动（Scroll）游标：静态、动态、码集驱动、混合型。
7. 中止处理
   处理结束后，应用程序首先释放语句句柄，然后释放数据库连接并与数据库服务器断开，最后释放ODBC环境。

8.5 OLE DB

1. 什么是OLE DB

• OLE DB对象链接与嵌入式数据库，是微软提出的数据库访问标准。是基于组件对象模型（COM）来访问各种数据源的ActiveX的通用接口。
• OLE DB可以连接数据库，也可以是文本文件、Excel表格等各种不同格式的数据存储。

2. OLE DB的结构
   OLE DB中包括了消费者和提供者
   （1）消费者：利用OLE DB提供的接口访问数据库数据的客户端应用程序或其他工具。
   （2）提供者：提供者是一个由COM组件构成的数据访问中介，位于数据库和消费者之间。
   ①服务提供者。这类提供者自身没有数据，它通过OLE DB接口封装服务，从下层获取数据并向上层提供数据，具有提供者和消费者双重身份。
   ②数据提供者。数据提供者自己拥有数据并通过接口形成表格形式的数据。
   
3. OLE DB编程模型
   数据管理任务必须由消费者访问数据，由提供者发布数据。

OLE DB编程模型分类：

• Rowset编程模型：假定数据源中的数据比较规范，提供者以行集4形式发布数据。
• Binder编程模型：提供者不提供标准表格式数据，OLE DB采用Binder编程模型将URL和一个OLE DB对象相关联或绑定，并在必要时创建层次结构的对象。

8.6JDBC编程

JDBC是Java制定的数据库连接技术的简称、在应用程序中与ODBC类似，是Java实现数据库访问的应用程序编程接口。

1. JDBC编程是面向对象的接口标准，一般由具体数据库厂商提供。
2. JDBC编程与ODBC类似，为Java提供统一、无缝地操作各种数据库的接口。

8.7小结

本章讲解了如何使用编程方法对数据库进行操纵的技术。

1. 嵌入式SQL把SQL语言嵌入到高级语言中。
2. SQL与主语言具有不同的数据处理方式。
3. 过程化SQL编写存储过程和自定义函数。
4. ODBC工作原理和工作流程。