GBase 8s SQL 指南：教程———10通过SQL程序修改数据

最新推荐文章于 2023-06-13 15:14:53 发布

Joe72

最新推荐文章于 2023-06-13 15:14:53 发布

阅读量361

点赞数

文章标签： sql 数据库 database

原文链接：https://www.modb.pro/db/399427

版权

10.1 DELETE 语句

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

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

1. 1直接删除

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

EXEC SQL delete from items

WHERE order_num = :onum;

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

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

直接删除期间的错误

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

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

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

事务日志记录

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

EXEC SQL begin work;

/开启事务/

EXEC SQL delete from items

where ordejnum = :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 8 s ESQL/C的示例所示：

EXEC SQL BEGIN WORK;

EXEC SQL DELETE FROM items

WHERE order_num = 😮_num;

if (SQLCODE >= 0)

{

EXEC SQL DELETE FROM orders

WHERE order_num == 😮_num;

{

if (SQLCODE >= 0)

EXEC SQL COMMIT WORK;

{

else

{

printf(“Error %d on DELETE”, SQLCODE);

EXEC SQL ROLLBACK WORK;

}

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

•未执行删除；此订单的所有行都保留在数据库中。

• 删除了某些商品行，但不是全部；仅保留某些商品的订单记录。

•删除了所有商品行，但保留订单行。

•删除了所有行。

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

1. 2使用游标删除

您还可使用游标编写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 ordejnum, ordejdate

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 ordejnum = :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。插入示例提供一种更好的方法。

10.2 INSERT 语句

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

1. 1插入游标

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操作产生一错误代码，则该事务回滚。仅当成功地执行所有操作，才提交该事务。

1. 2常量行

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

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

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

2.3插入示例

使用游标删除包含一个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.ordejnum);

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中的值(零还是负的)无关。

10.3 UPDATE 语句

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

1. 1更新游标

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

EXEC SQL

DECLARE names CURSOR FOR

SELECT fname, Iname, 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, Iname, 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的数据库时，它可使用任何游标来更新或删除。

1. 2清理表

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

在该示例中，创建并操纵一个名为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;

10.4总结

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

在所有这些活动中，您必须确保当发生错误时，程序会检测错误并将数据库返回到一已知的状态。实现这一点的最重要的工具是事务日志记录。没有事务日志记录，更难以编写可从错误恢复的程序。
————————————————
版权声明：本文为CSDN博主「aisirea」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/aisirea/article/details/124092889