通过 SQL 程序修改数据

前一章节描述如何将 SQL 语句插入或嵌入到其他语言编写的程序之内，特别是 SELECT 语句。嵌入式 SQL 使程序能够从数据库检索数据行。

本章节讨论当程序需要删除、插入或更新行来修改数据库时发生的问题。如在 SQL 编程中那样，此章节为您阅读您的 GBase 8s 嵌入式语言出版物做准备。

在修改数据中讨论 INSERT、UPDATE 和 DELETE 语句的常规用法。本章节从程序之内检验它们的使用。您可方便地将语句嵌入在程序中，但难以处理错误和处理来自多个程序的并发修改。

DELETE 语句

要从表删除行，程序执行 DELETE 语句。DELETE 语句可以常规方式以 WHERE 子句指定行，或它可引用单个行，通过指定的游标访存最后一行。

每当您删除行时，您必须考虑其他表中的行是否依赖于删除了的行。在修改数据中论述协调删除的这个问题。当从程序内删除时，问题是一样的。

直接删除

您可在程序中嵌入 DELETE 语句。下列示例使用 GBase 8s ESQL/C：

EXEC SQL delete from items
WHERE order_num = :onum;

您还可动态地准备和执行同样形式的语句。在任一情况下，该语句直接作用于数据库来影响一行或多行。

示例中的 WHERE 子句使用名为 onum 的主变量的值。通常在该操作之后，将结果发布在 SQLSTATE 中以及在 sqlca 结构中。即使发生错误，SQLERRD 数组的第三个元素也包含删除的行的计数。SQLCODE 中的值展示操作的完全成功。如果该值不是负的，则未发生错误且 SQLERRD 的第三个元素是满足了 WHERE 子句并被删除了的所有行的计数。

直接删除期间的错误

当发生错误时，该语句提前终止。SQLSTATE 中的值和 SQLCODE 中的值以及 SQLERRD 的第二个元素说明它的原因，且行的计数显示删除了的行数。对于许多错误，由于错误阻止数据库服务器开始操作，因此那个计数为零。例如，如果不存在命名的表，或如果重命名 WHERE 子句中测试的列，则不会尝试任何删除。

然而，在操作开始且处理某些行之后，可发现某些错误。这些错误中最常见的是锁冲突。在数据库服务器可删除那行之前，它必须获取行上的排他锁。其他程序可能正在使用来自该表的行，阻止该数据库服务器锁定行。由于锁定的问题影响所有类型的修改，因此在对多用户环境编程中讨论它。

另外，在删除开始之后，可出现较少见的错误类型。例如，在更新数据库时发生硬件错误。

事务日志记录

在修改期间为任何种类的错误做准备的最好方式是使用事务日志记录。万一发生错误，您可告诉数据库服务器将数据库恢复原样。下列示例是基于直接删除部分中的示例的，将其扩展为使用事务：

EXEC SQL begin work;                 /* 开启事务 */
EXEC SQL delete from items
where order_num = :onum;
del_result = sqlca.sqlcode;          /* 保存两个错误 */
del_isamno = sqlca.sqlerrd[1];       /* 代码编号 */
del_rowcnt = sqlca.sqlerrd[2];       /* 以及行的计数 */
if (del_result < 0)                  /* 发现的问题：*/
EXEC SQL rollback work;           /* 恢复一切 */
else                                 /* 一切正常：*/
EXEC SQL commit work;             /* 结束事务 */

此示例中的关键在于，在程序结束该事务之前，它将重要的返回值保存在 sqlca 结构中。像其他 SQL 语句一样，ROLLBACK WORK 和 COMMIT WORK 语句都在 sqlca 结构中设置返回代码。然而，如果您想要报告错误生成的代码，则必须在执行 ROLLBACK WORK 之前保存它们。ROLLBACK WORK 语句移除所有暂挂的事务，包括它的错误代码。

使用事务的优势在于，不管发生什么错误，数据库都处于已知的、可预测的状态。不存在修改完成了多少的问题；要不就是都完成了，要不就是都没完成。

在带有日志记录的数据库中，如果用户未启动一个显式的事务，则数据库服务器在语句执行之前初始化一个内部的事务，并在执行完成或失败后终止该事务。如果语句执行成功，则提交该内部的事务。如果语句失败，则回滚该内部的事务。

协调的删除

当您必须修改多个表时，事务日志记录的用处特别明显。例如，考虑从演示数据库删除一个订单的问题。在该问题的最简单的形式中，您必须从两个表 orders 和 items 同时删除行，如下列 GBase 8s ESQL/C 的示例所示：

EXEC SQL BEGIN WORK;
EXEC SQL DELETE FROM items
WHERE order_num = :o_num;
if (SQLCODE >= 0)
{
EXEC SQL DELETE FROM orders
WHERE order_num == :o_num;
{
if (SQLCODE >= 0)
EXEC SQL COMMIT WORK;
{
else
{
printf("Error %d on DELETE", SQLCODE);
EXEC SQL ROLLBACK WORK;
}

不论是否使用事务，此程序的逻辑都很相似。如果未使用它们，则看到错误消息的人员更难作出决定。依赖于错误发生的时间，下列情况中的一种适用：

未执行删除；此订单的所有行都保留在数据库中。
删除了某些商品行，但不是全部；仅保留某些商品的订单记录。
删除了所有商品行，但保留订单行。
删除了所有行。

在第二种和第三种情况下，数据库受到一定程度的损害；它包含可导致某些查询产生错误结果的部分信息。您必须小心行事来恢复信息的一致性。当使用事务时，会防止所有这些不确定性。

使用游标删除

您还可使用游标编写 DELETE 语句来删除最后访存了的行。以此方式删除行允许您的程序基于在 WHERE 子句中不可测试的条件进行删除。由于设置事务的开始和结束的方式的缘故，下列示例仅适用于不符合 ANSI 的数据库

警告

此示例中的 GBase 8s ESQL/C 函数的设计是不安全的。它依赖于正确的操作的当前隔离级别。该章节稍后讨论隔离级别。要获取关于隔离级别的更多信息，请参阅对多用户环境编程。即使当该函数按期望的方式运行时，它的影响也依赖于表中行的物理顺序，这样做通常并不理想。

int delDupOrder()
{
int ord_num;
int dup_cnt, ret_code;

EXEC SQL declare scan_ord cursor for
select order_num, order_date
into :ord_num, :ord_date
from orders for update;
EXEC SQL open scan_ord;
if (sqlca.sqlcode != 0)
return (sqlca.sqlcode);
EXEC SQL begin work;
for(;;)
{
EXEC SQL fetch next scan_ord;
if (sqlca.sqlcode != 0) break;
dup_cnt = 0; /* default in case of error */
EXEC SQL select count(*) into dup_cnt from orders
where order_num = :ord_num;
if (dup_cnt > 1)
{
EXEC SQL delete from orders
where current of scan_ord;
if (sqlca.sqlcode != 0)
break;
}
}
ret_code = sqlca.sqlcode;
if (ret_code == 100)             /* merely end of data */
EXEC SQL commit work;
else      /* error on fetch or on delete */
EXEC SQL rollback work;
return (ret_code);
}

该函数的目的是删除包含重复的订单号码的行。实际上，在演示数据库中，orders.order_num 列有唯一约束，因此，其中不可出现重复的行。然而，可为另一数据库编写一个类似的函数；这一个使用熟悉的列名称。

该函数声明游标 scan_ord 来扫描 orders 表中的所有行。使用 FOR UPDATE 子句声明它，说明该游标可修改数据。如果该游标正确地打开，则该函数开始一个事务，然后对表的行进行循环。对于每一行，它使用嵌入式 SELECT 语句来确定该表的多少行具有当前行的订单编号。如果没有正确的隔离级别，此步骤失败，如对多用户环境编程描述的那样。）

在演示数据库中，使用它在此表上的唯一约束，返回到 dup_cnt 的计数始终为一。然而，如果它更大，则该函数删除表的当前行，将重复的计数减少一个。

有时需要这类清理函数，但它们一般需要更复杂的设计。此函数删除所有重复的行，除了数据库服务器返回的最后一行之外。那个顺序对这些行的内容及其含义没有任何关系。您或许可通过将 ORDER BY 子句添加到游标声明来提升前面例子中函数的性能。然而，您不可同时使用 ORDER BY 与 FOR UPDATE。插入示例提供一种更好的方法。

INSERT 语句

您可在程序中嵌入 INSERT 语句。它的形式和在程序中的使用与修改数据中描述的一样，带有您可在表达式中使用主变量的附加的特性，在 VALUES 和 WHERE 子句中都一样。此外，您在程序中有使用游标来插入行的附加能力。

插入游标

DECLARE CURSOR 语句有许多种变体。大部分用于为不同种类的数据扫描创建游标，但有一种变体创建特殊种类的游标，称为插入游标。您可使用 PUT 和 FLUSH 语句来插入游标，以便高效地将行批量插入到表内。

声明插入游标

要创建插入游标，请为 INSERT 语句而不是 SELECT 语句声明游标。您不可使用这样的游标来访存数据行；您仅可使用它来插入它们。

当您打开插入游标时，在内存中创建缓冲区来保存一块行。当程序产生数据行时，该缓冲区接收它们；然后，当缓冲区满时，将它们以块的形式传到数据库服务器。该缓冲区减小程序与数据库服务器之间的通信量，允许数据库服务器比较容易地插入行。因此，插入操作更快。

该缓冲区始终足够大，以保持至少两行插入了的值。当这些行比最小缓冲区大小还小时，它大到足以保存超过两行的值。

使用游标来插入

前面示例中的代码（声明插入游标）为使用准备插入游标。如下例所示，接下来演示如何使用该游标。为了简化起见，此示例假设名为 next_cust的函数或者返回关于新客户的信息，或者返回空数据来标志输入的结束。

EXEC SQL BEGIN WORK;
EXEC SQL OPEN new_custs;
while(SQLCODE == 0)
{
next_cust();
if(the_company == NULL)
break;
EXEC SQL PUT new_custs;
}
if(SQLCODE == 0)                    /* 如果 PUT 没有问题 */
{
EXEC SQL FLUSH new_custs;        /* 写留下的任何行 */
if(SQLCODE == 0)                 /* 如果 FLUSH 没有问题 */
EXEC SQL COMMIT WORK;         /* 提交更改 */
}
else
EXEC SQL ROLLBACK WORK;          /* 否则，取消更改 */

此示例中的代码反复地调用 next_cust，当它返回非空数据时，PUT 语句将返回的数据发送到该行缓冲区。当缓冲区填满时，自动地将它包含的行发送到数据库服务器。当 next_cust 没有更多数据返回时，该循环正常结束。然后，FLUSH 语句写入缓冲区中余下的任何行，之后，事务终止。

重新检查关于 INSERT 语句的信息。请参阅 INSERT 语句。该语句本身不是游标定义的一部分，它将单个行插入到 customer 表内。实际上，可从示例代码删除整个插入游标的装置，且可将 INSERT 语句写到 PUT 语句正所在的位置。不同之处在于，插入游标导致程序运行得更快些。

PUT 和 FLUSH 之后的状态代码

当程序执行 PUT 语句时，程序应测试是否成功地将该行放入缓冲区中。如果新的行适合该缓冲区，则 PUT 的唯一操作就是将该行复制到缓冲区。在此情况下不可发生错误。然而，如果该行不适合，则为了插入将整个缓冲区负载传到数据库服务器，可发生错误。

返回到“SQL 通信区域”（SQLCA）内的值为程序提供它需要的信息，来整理每一种情况。如果未发生错误，则在每个 PUT 语句之后，将 SQLCODE 和 SQLSTATE 设置为零，如果发生错误，则设置为负的错误代码。

数据库服务器将 SQLERRD 的第三个元素设置为实际插入到表内的行数，如下

零，如果仅将新行移至缓冲区
缓冲区中的行数，如果插入缓冲区负载而未发生错误
错误发生之前插入了的行数，如果发生错误

请再次阅读代码来了解如何使用 SQLCODE（请参阅前面的示例）。首先，如果 OPEN 语句发生错误，则由于 WHILE 条件失败，不执行该循环，则不执行 FLUSH 操作，且该事务回滚。其次，如果 PUT 语句返回一个错误，则由于 WHILE 条件的缘故结束该循环，不执行 FLUSH 操作，且该事务回滚。仅当该循环至少一次生成足够的行来填充缓冲区，此条件才可发生。否则，PUT 语句不可生成错误。

程序可能结束该循环，而这些行还在缓冲区中，可能未插入任何行。此时，SQL 状态为零，且发生 FLUSH 操作。如果 FLUSH 操作产生一错误代码，则该事务回滚。仅当成功地执行所有操作，才提交该事务。

常量行

插入游标机制支持一种特殊的情况，在此易于获得高性能。在此情况下，罗列在 INSERT 语句中的所有值都是常量：不罗列表达式和主变量，仅罗列文字数值和字符串。不管发生多少次这样的 INSERT 操作，它产生的行都是相同的。当这些行是相同的时，复制、缓冲和传输每一相同的行都是没有意义的。

但是，对于此类 INSERT 操作，PUT 语句除了增加计数器之外不进行任何操作。当最终执行 FLUSH 操作时，将该行的单个副本以及插入的计数传到数据库服务器。数据库服务器创建并在一个操作中插入许多行。

您通常不插入一些相同的行。当您首次建立此数据库来操作带有空数据的大型表时，您可插入相同的行。

插入示例

使用游标删除包含一个 DELETE 语句的示例，其目的在于查找并删除表的重复的行。执行此任务的更好方式是选择期望的行，而不是删除不期望的行。下列 GBase 8s ESQL/C 示例中的代码展示执行此任务的一种方法：

EXEC SQL BEGIN DECLARE SECTION;
long last_ord = 1;
struct {
long int o_num;
date     o_date;
long     c_num;
char     o_shipinst[40];
char     o_backlog;
char     o_po[10];
date     o_shipdate;
decimal  o_shipwt;
decimal  o_shipchg;
date     o_paiddate;
} ord_row;
EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN WORK;
EXEC SQL INSERT INTO new_orders
SELECT * FROM orders main
WHERE 1 = (SELECT COUNT(*) FROM orders minor
WHERE main.order_num = minor.order_num);
EXEC SQL COMMIT WORK;

EXEC SQL DECLARE dup_row CURSOR FOR
SELECT * FROM orders main INTO :ord_row
WHERE 1 < (SELECT COUNT(*) FROM orders minor
WHERE main.order_num = minor.order_num)
ORDER BY order_date;
EXEC SQL DECLARE ins_row CURSOR FOR
INSERT INTO new_orders VALUES (:ord_row);

EXEC SQL BEGIN WORK;
EXEC SQL OPEN ins_row;
EXEC SQL OPEN dup_row;
while(SQLCODE == 0)
{
EXEC SQL FETCH dup_row;
if(SQLCODE == 0)
{
if(ord_row.o_num != last_ord)
EXEC SQL PUT ins_row;
last_ord = ord_row.o_num
continue;
}
break;
}
if(SQLCODE != 0 && SQLCODE != 100)
EXEC SQL ROLLBACK WORK;
else
EXEC SQL COMMIT WORK;
EXEC SQL CLOSE ins_row;
EXEC SQL CLOSE dup_row;

此示例以一个常规的 INSERT 语句开始，该语句查找该表的所有非重复的行，并将它们插入到另一表内，假定在程序启动之前已创建了该表。那个操作仅留下重复的行。（在演示数据库中，orders 表有唯一约束，不可有重复的行。假设此示例处理的是其他数据库。）

然后，前面示例中的代码声明两个游标。第一个称为 dup_row，返回表中的重复的行。由于 dup_row 仅用于输入，因此，它可使用 ORDER BY 子句来强制一些重复的顺序，而不是在使用游标删除页上的示例中使用的物理记录顺序。在此示例中，按重复的行的日期对它们排序，保留最早的日期，但您可基于该数据使用任何其他的顺序。

第二个游标 ins_row 是插入游标。此游标利用该能力来使用 C 结构 ord_row，以支持该行中所有列的值。

剩余的代码检查通过 dup_row 返回的行。它将来自每一组重复的行中的第一行插入到新表内，并忽略其余的。

为了简洁起见，前面的示例使用最简单的错误处理类型。如果在已处理了所有行之前发生错误，则该样例代码回滚活动的事务。

多少行受到了影响？

当您的程序使用游标来选择行时，它可测试 SQLCODE 是否为 100（或 SQLSTATE 是否为 02000），即“数据的结束”返回代码。设置此代码来指示没有行或没有更多的行满足该查询条件。对于不符合 ANSI 的数据库，仅跟在 SELECT 语句之后在 SQLCODE 或 SQLSTATE 中设置“数据的结束”返回代码；不跟在 DELETE、INSERT 或 UPDATE 语句之后使用它。对于符合 ANSI 的数据库，对于不影响任何行的更新、删除和插入操作，也将 SQLCODE 设置为 100。

找不到数据的查询是不成功的。然而，仍将碰巧未更新或插入行的 UPDATE 或 DELETE 语句视为成功。它更新了或删除了它的 WHERE 子句表明它应更新或产出的行集；然而，该集合为空。

同样地，即使当插入了的行的来源是 SELECT 语句，且该 SELECT 未选择任何行，INSERT 语句也不设置“数据的结束”返回代码。因为该 INSERT 语句插入了要求它插入的行数（即，零行），因此，该语句成功。

要了解插入了、更新了或删除了多少行，程序可测试 SQLERRD 的第三个元素。行的计数在那里，这与 SQLCODE 中的值（零还是负的）无关。

UPDATE 语句

您可以修改数据描述的任何形式将 UPDATE 语句嵌入在程序中，附加的特性是，您可同时在 SET 和 WHERE 子句中命名表达式中的主变量。此外，程序可更新游标找到的行。

更新游标

更新游标允许您删除或更新当前行；即，最近访存的行。使用 GBase 8s ESQL/C 编写的下列示例展示更新游标的声明：

EXEC SQL
DECLARE names CURSOR FOR
SELECT fname, lname, company
FROM customer
FOR UPDATE;

使用此游标的程序可以常规的方法访存行。

EXEC SQL
FETCH names INTO :FNAME, :LNAME, :COMPANY;

如果该程序然后决定需要修改该行，则它可这么做。

if (strcmp(COMPANY, "SONY") ==0)
{
EXEC SQL
UPDATE customer
SET fname = 'Midori', lname = 'Tokugawa'
WHERE CURRENT OF names;
}

关键字 CURRENT OF names 替代 WHERE 子句中的常规测试表达式。在其他方面，UPDATE 语句保持不变，即使包括表名称的规范，在游标名称中其为隐式的，但仍然需要。

关键字 UPDATE 的用途

游标中的关键字 UPDATE 的用途是让数据库服务器可更新（或删除）它访存的任何行。数据库服务器在通过更新游标访存的行上放置较多的需求锁，而当它访存未使用那个关键字声明的游标的行时，放置较少的需求锁。此操作使常规的游标有较好的性能，以及在多处理系统中更高的并发使用级别。（对多用户环境编程讨论锁和并发使用的级别。）

更新特定的列

下列示例已更新了前面的更新游标示例的特定的列：

EXEC SQL
DECLARE names CURSOR FOR
SELECT fname, lname, company, phone
INTO  :FNAME,:LNAME,:COMPANY,:PHONE FROM customer
FOR UPDATE OF fname, lname
END-EXEC.

仅可通过此游标更新 fname 和 lname 列。作为错误拒绝如下语句：

EXEC SQL
UPDATE customer
SET company = 'Siemens'
WHERE CURRENT OF names
END-EXEC.

如果程序尝试这样的更新，则返回错误代码且不发生更新。由于删除影响所有列，因此，也拒绝使用 WHERE CURRENT OF 的删除尝试。

不总是需要的 UPDATE 关键字

SQL 的 ANSI 标准不提供游标定义中的 FOR UPDATE 子句。当程序使用符合 ANSI 的数据库时，它可使用任何游标来更新或删除。

清理表

如何使用更新游标的一个最终的假设示例提出一个问题，使用已建立的数据库不应出现该问题，但在应用程序的初始设计阶段可能出现。

在该示例中，创建并操纵一个名为 target 的大型表。字符列 dactyl 无意中获得一些空值。应删除这些行。此外，使用 ALTER TABLE 语句将新列serials 添加到表。此列将安装唯一的整数值。下列示例展示您用来完成这些任务的 GBase 8s ESQL/C 代码：

EXEC SQL BEGIN DECLARE SECTION;
char dcol[80];
short dcolint;
int sequence;
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE target_row CURSOR FOR
SELECT datcol
INTO :dcol:dcolint
FROM target
FOR UPDATE OF serials;
EXEC SQL BEGIN WORK;
EXEC SQL OPEN target_row;
if (sqlca.sqlcode == 0) EXEC SQL FETCH NEXT target_row;
for(sequence = 1; sqlca.sqlcode == 0; ++sequence)
{
if (dcolint < 0) /* null datcol */
EXEC SQL DELETE WHERE CURRENT OF target_row;
else
EXEC SQL UPDATE target SET serials = :sequence
WHERE CURRENT OF target_row;
}
if (sqlca.sqlcode >= 0)
EXEC SQL COMMIT WORK;
else EXEC SQL ROLLBACK WORK;

总结

程序可执行 INSERT、DELETE 和 UPDATE 语句，如同修改数据描述的那样。程序还可使用游标来扫描整个表，更新或删除选择了的行。它还可使用游标来插入行，这样做的好处是缓冲这些行，并以块为单位发送到数据库服务器。

在所有这些活动中，您必须确保当发生错误时，程序会检测错误并将数据库返回到一已知的状态。实现这一点的最重要的工具是事务日志记录。没有事务日志记录，更难以编写可从错误恢复的程序。

DELETE 语句​

直接删除​

直接删除期间的错误​

事务日志记录​

协调的删除​

使用游标删除​

INSERT 语句​

插入游标​

声明插入游标​

使用游标来插入​

PUT 和 FLUSH 之后的状态代码​

常量行​

插入示例​

多少行受到了影响？​

UPDATE 语句​

更新游标​

关键字 UPDATE 的用途​

更新特定的列​

不总是需要的 UPDATE 关键字​

清理表​

总结​