数据库系统概论第五版（王珊）-设计与应用开发（八）

第8章 数据库编程

SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统间的互操作性。

在应用系统中使用SQL编程来访问和管理数据库中数据的方式主要有:嵌入式SQL( Embedded SQL，ESQL)、 过程化SQL ( Procedural Language/SQL, PL/SQL)、 存储过程和自定义函数、开放数据库互连( Open Data Base Connectivity, ODBC)、OLE DB (Object Linking and Embedding DB)、Java 数据库连接(Java Data Base Connectivity, JDBC)等编程。

8.1 嵌入式SQL

1. SQL 的特点之一是在交互式和嵌入式两种不同的使用方式下，SQL的语法结构基本上是一致的。当然在程序设计环境下，SQL语句要做某些必要的扩充。
2. 对嵌入式SQL,数据库管理系统一般采用预编译方法处理，即由数据库管理系统的预处理程序对源程序进行扫描，识别出嵌入式SQL语句，把它们转换成主语言调用语句，以使主语言编译程序能识别它们，然后由主语言的编译程序将纯的主语言程序编译成目标码。
   嵌入式SQL基本处理过程如图8.1所示。
   
3. 在嵌入式SQL中，为了能够快速区分SQL语句和主语言语句，所有SQL语句都必须加前缀。
   当主语言是C语言时，语法格式为：exec SQL<SQL语句>
   如果主语言是Java，语法格式为：#SQL{<SQL语言>}

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

数据库工作单元与源程序工作单元之间的通信主要包括:
（1）向主语言传递SQL语句的执行状态信息，使主语言能够据此信息控制程序流程，主要用SQL通信区( SQL Communication Area, SQLCA) 实现。
（2）主语言向SQL语句提供参数，主要用主变量(host variable) 实现。
（3）将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标(cursor) 实现。

1. SQL通信区
   （1）SQL语句执行后，系统要反馈给应用程序若干信息，主要包括描述系统当前工作状态和运行环境的各种数据。这些信息将送到SQL通信区中，应用程序从SQL通信区中取出这些状态信息，据此决定接下来执行的语句。
   （2）SQL通信区在应用程序中用exec SQL include sqlca加以定义。SQL通信区中有一个变量sql code,用来存放每次执行SQL语句后返回的代码。
   （3）应用程序每执行完 一条SQL语句之后都应该测试 -一下sql code的值，以了解该SQL语句执行情况并做相应处理。如果sql code等于预定义的常量SUCCESS，则表示SQL语句成功，否则在sql code存放错误代码。程序员可以根据错误代码查找问题。
2. 主变量
   （1）嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。SQL语句中使用的主语言程序变量简称为主变量。主变量根据其作用的不同分为输入主变量和输出主变量。输入主变量由应用程序对其赋值，SQL语句引用；输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序。
   （2）一个 主变量可以附带一个任选的指示变量( indicator variable)。指示变量是一个整型变量，用来**“指示”所指主变量的值或条件**。指示变量可以指示输入主变量是否为空值，可以检测输出主变量是否为空值，值是否被截断。
   （3）所有主变量和指示变量必须在SQL语句begin declare section与end declare section之间进行说明。说明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方出现。
   为了与数据库对象名(表名、视图名、列名等)区别，SQL语句中的主变量名和指示变量前要加冒号(: ) 作为标志。
3. 游标
   SQL是面向集合的（一条SQL语句可以产生或处理多条记录）；而主语言是面向记录的（一组主变量一次只能存放一条记录）。所以仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。
   游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量， 交由主语言进一步处理。
4. 建立和关闭数据库连接
   （1）建立数据库连接的嵌入式SQL语句：exec SQL connect to target[as connection-name][user user-name]

• target：要连接的数据库服务器。可以是常见的服务器标识串，< dbname >@< hostname >:< port>;也可以是服务器标识的SQL串常量，也可以是default。
• connection-name：可选连接名。
• 程序运行中可以修改当前连接：exec SQL set connection connection-name|default；
  （2）关闭数据库连接：exec SQL disconnect[connection]；

8.1.3 不适用游标的SQL语句

有的嵌入式SQL语句不需要使用游标，它们是说明性语句、数据定义语句、数据控制语句、查询结果为单记录的select语句、非current形式的增删改语句。

1. 查询结果为单记录的select语句
   这类语句因为查询结果只有一个，只需要用into子句指定存放查询结果的主变量，不需要使用游标。

EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
	INTO :Hsno;Hname;Hsex;Hage,;Hdept
	FROM Student
	WHERE Sno=:givensno;       /*把要查询的学生的学号赋给了主变量givensno */

2. 非current形式的增删改查
   有些非CURRENT形式的增删改语句不需要使用游标。在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量。

8.1.4 使用游标的SQL语句

必须使用游标的SQL语句有查询结果为多条记录的SELECT语句、CURRENT形式的UPDATE和DELETE语句。

1. 查询结果为多条记录的SELECT语句
   使用游标的步骤为：
   （1）说明游标
   用DECLARE语句为一.条SELECT语句定义游标:EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>;
   定义游标仅仅是一条说明性语句，这时关系数据库管理系统并不执行SELECT语句。
   （2）打开游标
   用OPEN语句将定义的游标打开：EXEC SQL OPEN <游标名>;
   打开游标实际上是执行相应的SELECT语句，把结果取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中的第一条记录。
   （3）推进游标指针并取当前记录：
   EXEC SQL FETCH <游标名> INTO <主变量>[<指示变量>][<主变量>[<指示变量>1]……
   其中主变量必须与SELECT语句中的目标列表达式具有一一对应关系。
   （4）关闭游标
   用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源：EXEC SQL CLOSE <游标名>;
   游标被关闭后就不再和原来的查询结果集相联系。但被关闭的游标可以再次被打开，与新的查询结果相联系。
2. CURRENT形式的UPDATE和DELETE语句
   UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中某个记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的UPDATE和DELETE语句修改或删除之。即UPDATE语句和DELETE语句中要用子句: where current of <游标名>;来表示修改或删除的最近一次取出的记录,即游标指针指向的记录.

8.1.5 动态SQL

前面所讲的嵌入式SQL语句中使用的主变量、查询目标列、条件等都是固定的，属于静态SQL语句。静态嵌入式SQL语句能够满足一般要求，但某些应用可能要到执行时才能够确定要提交的SQL语句、查询的条件，此时就要使用动态SQL语句来解决这类问题。
动态SQL方法允许在程序运行过程中临时“组装”SQL语句。动态SQL支持动态组装SQL语句和动态参数两种形式，给开发者提供设计任意SQL语句的能力。

1. 使用SQL语句主变量
   SQL语句主变量:程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量。SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行。
2. 动态参数
   使用动态参数的步骤如下:
   (1)声明SQL语句主变量：SQL语句主变量的值包含动态参数(?)。
   (2)准备SQL语句(PREPARE)
   PREPARE将分析含主变量的SQL语句内容，建立语句中包含的动态参数的内部描述符，并用<语句名>标识它们的整体。EXEC SQL PREPARE <语句名> FROM <SQL语句主变量>;
3. 执行准备好的语句（execute）
   EXECUTE将SQL语句中分析出的动态参数和主变量或数据常量绑定，作为语句的输入或输出变量。
   EXEC SQL EXECUTE <语句名> [INTO <主变量表>][USING <主变量或常量>];

8.2 过程化SQL

8.2.1 过程化SQL的块结构

过程化SQL程序的基本结构是块。所有的过程化SQL程序都是由块组成的。这些块之间可以互相嵌套，每个块完成一个逻辑操作。图8.2是过程化SQL块的基本结构。

8.2.2 变量和常量的定义

1. 变量定义：变量名 数据类型 [[NOT NULL] :=初值表达式]或变量名 数据类型[[NOT NULL] 初值表达式]
2. 常量定义：常量名 数据类型 CONSTANT:=常量表达式
3. 赋值语句：变量名:= 表达式

8.2.3 流程控制

1. 条件控制语句：一般有三种形式：IF-THEN语句、IF-THEN-ELSE语句、嵌套的IF语句。
2. 循环控制语句：LOOP、WHILE-LOOP和FOR-LOOP。

8.3 存储过程和函数

过程化SQL块主要有两种类型：命名快和匿名块。

8.3.1 存储过程

存储过程可以定义的游标包括：普通游标、REFCURSOR类型游标、带参数的游标。
存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

1. 存储过程的优点：
   （1）运行效率高，提供了服务器端快速执行SQL语句的有效途径。
   （2）存储过程降低了客户机和服务器之间的通信量
   （3）方便实施企业规则。
2. 存储过程的用户接口
   （1）创建存储过程

create OR replace procedure 过程名([参数1,参数..... /*存储过程首部*/
AS <过程化SQL块>;;国内m /*存 储过程体，描述该存储过程的操作*/

（2）执行存储过程：call/perform procedure 过程名([参数1,参数...);
（3）修改存储过程：
重命名存储过程：ALTER PROCEDURE 过程名1 RENAMETO 过程名2;
重编译存储过程：ALTER PROCEDURE 过程名 compile;
（4）删除存储过程：drop procedure 过程名()；

8.3.2 函数

本节讲到的函数指自定义函数，和存储过程类似，都是持久化存储模块，不同的是：函数必须指定返回的类型。

1. 函数定义：CREATE OR REPLACE FUNCTION函数名([参数1,参数2...) RETURNS <类型> AS <过程化 SQL块>;
2. 函数执行：CALL/SELECT函数名([参数1,参数...);
3. 修改函数：
   （1）重命名：ALTER FUNCTION 过程名1 RENAME TO过程名2;
   （2）重编译：ALTER FUNCTION 函数名 COMPILE;

8.3.3 过程化SQL中的游标

在过程化SQL中如果SELECT语句只返回一条记录，可以将该结果存放到变量中。当查询返回多条记录时，就要使用游标对结果集进行处理。

8.4 ODBC编程

8.4.2 ODBC工作原理概述

ODBC应用系统的体系结构如图所示，它由4部分构成:用户应用程序、ODBC驱动程序管理器、数据库驱动程序、数据源。

1. 用户应用程序
   用户应用程序提供用户界面、应用逻辑和事物逻辑。
2. ODBC驱动程序管理器
   用来管理各种驱动程序。ODBC驱动程序管理器可以建立、配置或删除数据源，并查看系统当前所安装的数据库ODBC驱动程序。
3. 数据库驱动程序
   ODBC通过数据库驱动程序来提供应用系统与数据库平台的独立性。
4. 数据源
   数据源是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，实际上是一种数据连接的抽象。

8.4.4 ODBC的工作流程

8.5 OLE DB

1. 概念：对象链接与嵌入数据库(Object Linking and Embedding Database, OLE DB)也是微软公司提出的数据库连接访问标准。
2. 图8.6是一个基于OLE DB体系结构设计程序的编程模型。OLE DB体系结构包含了消费者（consumer）和提供者（provide）。
   
   （1）消费者
   OLEDB的消费者利用OLEDB提供者提供的接口访问数据源数据的客户端应用程序或其他工具。在OLE DB实现中，OLE DB组件本身也可能作为消费者存在。
   （2）提供者

• 服务提供者：
  这类提供者自身没有数据，它通过OLE DB接口封装服务，从下层获取数据并向上层提供数据，具有提供者和消费者双重身份。
• 数据提供者：数据提供者自已拥有数据并通过接口形成表格形式的数据。

8.6 JDBC编程

ODBC部分见java部分的内容