第8章——数据库编程

• 标准SQL是非过程化的查询语言，具有操作统一、面向集合、功能丰富、使用简单等多项优点。但和程序设计语言相比，高度非过程化的优点也造成了它的一个弱点：缺少流程控制能力，难以实现应用业务中的逻辑控制。SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统间的互操作性。
• 在应用系统中使用SQL编程来访问和管理数据库中数据的方法有：嵌入式SQL、过程化SQL、存储过程和自定义函数、开放数据库互连、OLE DB、Java数据库连接等编程方式。

嵌入式SQL

• SQL的特点之一是在交互式和嵌入式两种不同的使用方式下，SQL的语法结构基本上是一致的。

嵌入式SQL的处理过程

• 嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java等称为宿主语言，简称主语言。
• 对嵌入式SQL，数据库管理系统一般采用预编译方法处理，即由数据库管理系统的预处理程序对源程序进行扫描，识别出嵌入式SQL语句，把它们转换成主语言调用语句，以使主语言编译程序能识别它们，然后由主语言的编译程序将纯的主语言程序编译成目标码。
• 嵌入式SQL基本处理过程如图所示：
  
• 在嵌入式SQL中，为了能够快速区分SQL语句与主语言语句，所有SQL语句都必须加前缀。
• 当主语言为C语言时，语法格式为：EXEC SQL <SQL语句>;
• 如果主语言为Java，则嵌入式SQL称为SQLJ，语法格式为：# SQL {<SQL语句>};

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

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

SQL通信区

• SQL语句执行后，系统要反馈给应用程序若干信息，主要包括描述系统当前工作状态和运行环境的各种数据。这些信息将送到SQL通信区中，应用程序从SQL通信区中取这些状态信息，据此决定接下来执行的语句。
• SQL通信区在应用程序中用EXEC SQL INCLUDE SQLCA加以定义。SQL通信区中有一个变量SQLCODE，用来存放每次执行SQL语句后返回的代码。
• 应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则在SQLCODE存放错误代码。程序员可以根据错误代码查找问题。

主变量

• 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。SQL语句中使用的主语言程序变量简称为主变量。主变量根据其作用的不同分为输入主变量和输出主变量。输入主变量由应用程序对其赋值，SQL语句引用；输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序。
• 一个主变量可以附带一个任选的指示变量(indicator variable)，指示变量是一个整型变量，用来"指示"所指主变量的值或条件。指示变量可以指示输入主变量是否为空值，可以检测输出主变量是否为空值，值是否被截断。
• 所有主变量和指示变量必须在SQL语句BEGIN DECLARE SECTION 与END DECLARE SECTION之间进行说明。说明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方出现，为了与数据库对象名(表名、视图名、列名等)区别，SQL语句中的主变量名和指示变量前要加冒号(:)作为标志。

游标

• SQL是面向集合的，一条SQL语句可以产生或处理多条记录；而主语言是面向记录的，一组主变量一次只能存放一条记录。所以仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调者两种不同的处理方式。
• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

建立和关闭数据库连接

• 嵌入式SQL程序要访问数据库必须先连接数据库，关系数据库管理系统根据用户信息对连接请求进行合法性验证，只有通过了身份验证，才能建立一个可用的合法连接。

(1)建立数据库连接
建立连接的嵌入式SQL语句是：EXEC SQL CONNECT TO target[AS connection-name][USER user-name];
其中：target是要连接的数据库服务器，是一个常见的服务器标识串，如<dbname>@:<port>，可以是包含服务器标识的SQL串常量，也可以是DEFAULT。
Connection-name是可选的连接名，连接名必须是一个有效的标识符，主要用来识别一个程序内同时建立的多个连接，如果在整个程序内只有一个连接，也可以不指定连接名。
如果程序运行过程中建立了多个连接，执行的所有数据库单元的工作都在该操作提交时所选择的当前连接上。程序运行过程中可以修改当前连接，对应的嵌入式SQL语句为：EXEC SQL SET CONNECTION connection-name|DEFAULT;

(2)关闭数据库连接
当某个连接上的所有数据库操作完成后，应用程序应该主动释放所占用的连接资源。关闭数据库连接的嵌入式SQL语句是：EXEC SQL DISCONNECTION [connection];
其中connection是EXEC SQL CONNECT所建立的数据库连接。

不用游标的SQL语句

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

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

• 这类语句因为查询结果只有一个，只需用INTO子句指定存放查询结果的主变量，不需要使用游标。
• 使用查询结果为单记录的SELECT语句需要注意以下几点：
  (1)INTO子句、WHERE子句和HAVING短语的条件表示式中均可以使用主变量。
  (2)查询结果为空值的处理。查询返回的记录中可能某些列为空值NULL。为了表示空值，在INTO子句的主变量后面跟有指示变量，当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，而不再向该主变量赋值。所以当指示变量值为负值时，不管主变量为何值，均认为主变量值为NULL。
  (3)如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，关系数据库管理系统会在SQL通信区中返回错误信息。

非CURRENT形式的增删改语句

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

使用游标的SQL语句

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

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

• 一般情况下，SELECT语句查询结果是多条记录，因此需要用游标机制将多条记录一次一条地送主程序处理，从而把对集合的操作转换为对单个记录的处理。使用游标的步骤为：

(1)说明游标

• 用DECLARE语句为一条SELECT语句定义游标：EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>;
  定义游标仅仅是一条说明性语句，这时关系数据库管理系统并不执行SELECT语句

(2)打开游标

• 用OPEN语句将定义的游标打开。
  EXEC SQL OPEN <游标名>;
  打开游标实际上是执行相应的SELECT语句，把查询结果取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中的第一条记录。

(3)推进游标指针并取当前记录

• EXEC SQL FETCH <游标名>
  INTO <主变量>[<指示变量>][,<主变量>][<指示变量>]…;
  其中主变量必须与SELECT语句中的目标列表达式具有一一对应关系，用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。通过循环执行FETCH语句逐条取出结果集中的行进行处理。

(4)关闭游标

• 用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。
  EXEC SQL CLOSE <游标名>;
  游标被关闭后就不再和原来的查询结果集相联系，但被关闭的游标可以再次被打开，与新的查询结果相联系。

CURRENT形式的UPDATE和DELETE语句

• UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中某个记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的UPDATE和DELETE语句修改或删除之。即UPDATE语句和DELETE语句中要用子句：WHERE CURRENT OF <游标名>来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录。
• 注意：当游标定义中的SELECT语句带有UNION或ORDER BY子句，或者该SELECT语句相当于定义了一个不可更新的视图时，不能使用CURRENT形式的UPDATE语句和DELETE语句

动态SQL

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

使用SQL语句主变量

• 程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量，这样的变量称为SQL语句主变量。SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行。

动态参数

• 动态参数是SQL语句中的可变元素，使用参数符号(?)表示该位置的数据在运行时设定。和前面使用的主变量不同，动态参数的输入不是编译时完成绑定，而是通过PREPARE语句准备主变量和执行语句EXECUTE绑定数据或主变量来完成。
• 使用动态参数的步骤如下：

(1)声明SQL语句主变量
SQL语句主变量的值包含动态参数(?)
(2)准备SQL语句(PREPARE)
PREPARE将分析含主变量的SQL语句内容，建立语句中包含的动态参数的内部描述符，并用<语句名>标识它们整体。

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

执行准备好的语句(EXECUTE)

• EXECUTE将SQL语句中分析出的动态参数和主变量或数据常量绑定，作为语句的输入或输出变量

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

过程化SQL

过程化SQL的块结构

• 基本的SQL是高度非过程化的语言。嵌入式SQL将SQL语句嵌入程序设计语言，借助高级语言的控制功能实现过程化。过程化SQL是对SQL的扩展，使其增加了过程化语句功能。
• 过程化SQL程序的基本结构是块。所有的过程化SQL程序都是由块组成的。这些块之间可以相互嵌套，每个块完成一个逻辑操作。
• 过程化SQL块的基本结构：
  $$定义部分\{\begin{array}{l}DECLARE/\ast 定义的变量、常量等只能在该基本块中使用\ast /\\ 变量、常量、游标、异常等/\ast 当基本块执行结束时，定义就不再存在\ast /\end{array}$$
  $$执行部分\{\begin{array}{l}BEGIN\\ SQL语句、过程化SQL的流程控制语句\\ EXCEPTION/\ast 遇到不能继续执行的情况称为异常\ast /\\ 异常处理部分/\ast 在出现异常时，采取措施来纠正错误或报告\ast /\\ END;\end{array}$$

变量和常量的定义

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

流程控制

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

1. 条件控制语句

• 一般有三种形式的IF语句：IF-THEN语句、IF-THEN-ELSE语句和嵌套的IF语句。

(1)IF语句

 IF condition THEN
 	Sequence_of_statements; /*条件为真时语句序列才被执行*/
 END IF; /*条件为假或NULL时什么也不做，控制转移至下一个语句*/

(2)IF-THEN语句

IF condition THEN
	Sequence_of_statements1; /*条件为真时执行语句序列1*/
ELSE
	Sequence_of_statements2; /*条件为假或NULL时执行语句序列2*/
END IF;

(3)嵌套的IF语句
在THEN和ELSE子句中还可以再包含IF语句，即IF语句可以嵌套。

2. 循环控制语句

• 过程化SQL有三种循环结构：LOOP、WHILE-LOOP和FOR-LOOP

(1)最简单的循环语句LOOP

LOOP
	Sequence_of_statements; /*循环体，一组过程化SQL语句*/
END LOOP;

多数数据库服务器的过程化SQL都提供EXIT、BREAK或LEAVE等循环结束语句，以保证LOOP语句块能够在适当的条件下提前结束。

(2)WHILE-LOOP循环语句

WHILE condition LOOP
	Sequence_of_statements; /*条件为真时执行循环体内的语句序列*/
END LOOP;

每次执行循环体语句之前首先要对条件进行求值，如果条件为真则执行循环体内的语句序列，如果条件为假则跳过循环并把控制传递给下一个语句

(3)FOR-LOOP循环语句

FOR count IN [REVERSE] bound1..bound2 LOOP
	Sequence_of_statements;
END LOOP;

FOR循环的基本执行过程：将count设置为循环的下界bound1，检查它是否小于上界bound2。当指定REVERSE时则将count设置为循环的上界bound2，检查count是否大于下界bound1。如果越界则执行跳出循环，否则执行循环体，然后按照步长(+1 或 -1)更新count的值，重新判断条件。

3. 错误处理

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

存储过程和函数

• 过程化SQL块主要有两种类型：命名块和匿名块。
• 匿名块每次执行时都需要编译，不能被存储到数据库中，也不能在其它过程化SQL块中调用。
• 过程和函数是命名块，它们被编译后保存在数据库中，称为持久性存储模块（Persistent Stored Module，PSM），可以反复调用，运行速度较快。

存储过程

• 存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只需调用即可。

存储过程的优点

(1)由于存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，它提供了在服务器端快速执行SQL语句的有效途径。
(2)存储过程降低了客户机和服务器之间的通信量。客户机的应用程序只要通过网络向服务器发出调用存储过程的名称和参数，就可以让关系数据库管理系统执行其中的多条SQL语句并进行数据处理。只有最终的处理结果才返回客户端。
(3)方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库管理系统管理，既有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只要修改存储过程即可，无需修改其它应用程序。

存储过程的用户接口

• 用户通过下面的SQL语句创建、执行、修改和删除存储过程

(1)创建存储过程

CREATE OR REPLACE PROCEDURE 过程名 ([参数1,参数2,...]) /*存储过程首部*/
AS <过程化SQL块>; /*存储过程体，描述该过程的操作*/

• 存储过程包括过程首部和过程体。在过程首部，"过程名"是数据库服务器合法的对象标识；参数列表[参数1,参数2,…]用名字来标识调用时给出的参数值，必须指定值的数据类型。可以定义输入参数、输出参数或输入/输出参数，默认为输入参数，也可以无参数。
• 过程体是一个<过程化SQL块>，包括声明部分和可执行语句部分。

(2)执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);

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

(3)修改存储过程

• 可以使用ALTER PROCEDURE重命名一个存储过程：

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

• 可以使用ALTER PROCEDURE重新编译一个存储过程：

ALTER PROCEDURE 过程名 COMPILE;

(4)删除存储过程

DROP PROCEDURE 过程名();

函数

• 函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型。

1. 函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,...]) RETURNS <类型>
AS <过程化SQL块>;

2. 函数的执行语句格式

CALL/SELECT 函数名([参数1,参数2,...]);

3. 修改函数

• 可以使用ALTER FUNCTION重命名一个自定义函数：

ALTER FUNCTION 过程名1 RENAME TO 过程名2;

• 可以使用ALTER FUNCTION重新编译一个函数：

ALTER FUNCTION 过程名 COMPILE;

过程化SQL中的游标

• 和嵌入式SQL一样，在过程化SQL中如果SELECT语句只返回一条记录，可以将该结果存放到变量中。当查询返回多条记录时，就要使用游标对结果集进行处理。一个游标与一个SQL语句相关联。在存储过程中可以定义普通游标、REFCURSOR类型游标、带参数的游标等。