GBase 8s SQL 指南：教程———9 SQL编程

最新推荐文章于 2024-05-12 23:13:05 发布

Joe72

最新推荐文章于 2024-05-12 23:13:05 发布

阅读量1.1k

点赞数

文章标签： sql 数据库 database dba

9 SQL编程

前面的示例似乎将SQL作为一种交互的计算机语言；即，似乎您可以直接将SELECT语句输入数据库服务器内并查看回滚给您的数据行。

当然，情况并非如此。在您与数据库服务器之间存在许多软件层。在可显示数据之前，数据库服务器以必须格式化的二进制形式保留数据。它不会立即返回大量数据；当程序请求它时，它一次返回一行。

您可使用DB-Access通过交互的访问，通过以诸如GBase 8s ESQ L/C这样的SQL API编写的应用程序，或通过诸如SPL这样的应用程序语言，来访问您的数据库中的信息。

几乎所有程序都可包含SQL语句，执行它们，并从数据库服务器检索数据。本章节说明如何执行这些活动并指示您可如何编写执行它们的程序。

本章节介绍使用任何语言进行SQL编程的常见概念。在您可以特定的编程语言编写成功的程序之前，您必须先熟悉那种语言。然后，由于每种语言中处理的细节都不同，因此您必须熟悉特定于那种语言的GBase 8s SQL API的出版物。

9.1程序中的SQL

您可以任意几种语言编写程序，并将SQL语句混合在程序的其他语句之中，就如同它们是那种编程语言的一般语句似的。将这些SQL语句嵌入在程序中，且该程序包含嵌入式 SQL，其通常缩写为ESQL。

9.1.1 SQL API 中的 SQL

ESQL产品为GBase 8s SQL API（应用程序编程接口）。GBase为C编程语言产生SQL API。

下图展示SQL API产品如何工作。您编写您在其中将SQL语句处理作为可执行代码的源程序。嵌入式SQL预处理器处理您的源程序，它是一个定位嵌入式SQL语句并将它们转换为一系列过程调用和特殊的数据结构的程序。

图:使用嵌入式SQL语句处理程序的概述

然后，转换了的源程序传递到编程语言编译器。在将它与静态的或动态的SQL API过程库相链接之后，编译器输出称为可执行的程序。当程序运行时，调用该SQL API库过程；它们与数据库服务器建立通信来执行SQL操作。

如果您将您的可执行程序链接到线程库包，则您可开发GBase 8s ESQL/C多线程应用程序。多线程应用程序可具有控制的许多线程。它将一个进程分割成多个执行线程，每一线程独立地运行。多线程的GBase 8s ESQL/C应用程序的主要优势在于，每一线程可同时具有与数据库服务器的许多活动的连接。而非线程的GBase 8s ESQL/C应用程序可创建与一个或多个数据库的许多连接，它一次仅可有一个连接是活动的。对于多线程的GBase 8s ESQL/C应用程序，每一线程可有一个活动的连接，且每个应用程序可有许多线程。

要获取关于多线程的应用程序的更多信息，请参阅《GBase 8s ESQL/C程序员手册》。

9.1.2应用程序语言中的SQL

尽管GBase 8s SQL API产品允许您将SQL嵌入在主语言中，但某些语言包括SQL作为它们的语句集的固有部分。GBase 8s "存储过程语言” （SPL）使用SQL作为它的语句

集的固有部分。您使用SQL API产品编写应用程序。您使用SPL编写例程，例程与数据库一起存储并从应用程序调用它。

9.1.3静态的嵌入

您可通过静态的嵌入或动态的嵌入将SQL语句引入程序。比较简单和常见的方式是通过静态的嵌入，其意味着编写SQL语句作为代码的一部分。该语句为静态的，是因为它们是源文本的固定部分。要获取关于静态的嵌入的更多信息，请参阅检索单行和检索多行。

9.1.4动态的语句

有些应用程序要求有动态地组合SQL语句的能力，以响应用户输入。例如，程序可能必须选择不同的列或将不同的标准应用到行，这依赖于用户想要什么。

使用动态的SQL,程序在内存中将SQL语句组合成字符串，并将它传递给数据库服务器来执行。动态的语句不是代码的一部分；在执行期间在内存中构造它们。要了解更多信息，请参阅动态SQL。

9.1.5程序变量和主变量

应用程序可在SQL语句内使用程序变量。在SPL中，当语法允许时，您可在SQL语句中放置程序变量。例如，DELETE语句可在它的WHERE子句中使用程序变量。

下列代码示例展示SPL中的程序变量。

CREATE PROCEDURE delete_item (drop_number INT)

DELETE FROM items WHERE order_num = drop_number

在使用嵌入式SQL语句的应用程序中，SQL语句可引用程序变量的内容。在嵌入式SQL 语句中命名的程序变量称为主变量，因为在程序中将该SQL语句认作是客人。

下列示例展示DELETE语句，当将它嵌入在GBase 8s ESQL/C源程序中时，它可能出现：

EXEC SQL delete FROM items

WHERE order_num = :onum;

在此程序中，您看到常规的DELETE语句，如同修改数据描述的那样。当执行该GBase 8s ESQL/C程序时，删除items表的一行；还可删除多行。

该语句包含一个新的特性。它将order_num列与编写为：onum的一项相比较，这是主变量的名称。

SQL API产品提供一种分隔主变量的名称的方式，当它们出现在SQL语句的上下文中时。在GBase 8s ESQL/C中，可使用美元符号($)或冒号(:)来引入主变量。冒号是符合ANSI 的格式。示例语句请求数据库服务器删除其中的订单编号等于名为:onum的主变量的当前内容的那些行。在程序中声明了此数值变量并提前分配了值。

在GBase 8s ESQL/C中，可使用前导的美元符号($)或关键字EXEC SQL来引入SQL语句。

在前面的示例中说明的语法的差异很小；重要的是，SQL API和SPL语言使您执行下列任务：

•在源程序中嵌入SQL语句，就好像它们是主语言的可执行语句一样。

•以使用文字值的方式，在SQL表达式中使用程序变量。

如果您有编程经验，则您可立即看到这些可能性。在该示例中，将要删除的订单号码传到变量onum中。那个值来自程序可使用的任何源。可从文件读取它，程序可提示用户输入它，或可从数据库读取它。DELETE语句本身可为子例程的一部分（在此情况下，onum可为该子例程的参数）；可一次或反复地调用该子例程。

总之，当您在程序中嵌入SQL语句时，您可对它们应用主语言的所有功能。您可将SQL语句隐藏在许多接口之下，且可以多种方式优化SQL函数。

9.2调用数据库服务器

执行SQL语句本质上是作为子例程调用数据库服务器。必须将信息从程序传到数据库服务器，且必须将信息从数据库服务器返回到程序。

此通讯的部分是通过主变量完成的。您可将在SQL语句中命名的主变量视作对数据库服务器调用的过程的参数。在前面的示例中，主变量作为WHERE子句的参数。主变量收到数据库服务器返回的数据，如同检索多行描述的那样。

SQL通信区域
数据库服务器始终在一个称为“SQL通信区域”（SQLCA ）的数据结构中返回结果代码，以及关于操作结果的其他可能信息。如果数据库服务器在用户定义的例程中执行SQL语句，则调用应用程序的SQLCA包含在该例程中SQL语句触发的值。

在从表1至表1中罗列SQLCA的主体字段。在编程语言之中，您用来描述诸如 SQLCA这样的数据结构的语法，以及您用来应用其中字段的语法是不同的。要了解详细信息，请参阅您的SQL API出版物。

特别地，您通过其命名SQLERRD和SQLWARN数组的一个元素的下标是不同的。在 GBase 8s ESQL/C中，数组元素从零开始编号，但在其他语言中，从一开始。在本讨论中，以诸如third这样的特定词命名字段，且您必须将这些词翻译成您的编程语言的语法。

您还可使用GET DIAGNOSTICS语句的SQLSTATE变量来检测、处理和诊断错误。请参阅SQLSTATE值。

SQLCODE 字段
SQLCODE字段是数据库服务器的主要返回代码。在每个SQL语句之后，将SQLCODE 设置为如下表所示的一个整数值。当那个值为零时，无误地执行该语句。特别地，当假定一个语句将数据返回到主变量内时，代码零意味着已返回了该数据且可使用它。任何非零代码都意味着相反的意思。未将有用的数据返回到了主变量。

表1. SQLCODE的值

返回值解释

返回值

解释

值〈0

指定一个错误代码。

值=0

指示成功。

0 < 值〈100

在DESCRIBE语句之后，表示描述SQL语句的类型的一个整数值。

100

在未返回行的成功查询之后，指示NOT FOUND条件。在INSERT INTO/SELECT、UPDATE、DELETE 或 SELECT… INTO TEMP 语句未能访问任何行之后，NOT FOUND还可发生在符合ANSI的数据库中。

数据的结束

当正确地执行语句，但未找到行时，数据库服务器将SQLCODE设置为100。在两种情况下可发生此情况。

第一种情况涉及使用游标的查询。（检索多行描述使用游标的查询。）在这些查询中,FETCH 语句将来自活动集的每一值检索到内存内。检索最后一行之后，后续的FETCH语句不可返回任何数据。当发生此情况时，数据库服务器将SQLCODE设置为100,指示数据的结束，找不到行。

第二种情况涉及不使用游标的查询。在此情况下，当没有行满足查询条件时，数据库服务器将SQLCODE设置为100。在不符合ANSI的数据库中，仅不返回行的SELECT语句会导致将SQLCODE设置为100。

在符合ANSI的数据库中，如果未返回行，贝U SELECT、DELETE、UPDATE和INSERT 语句都将SQLCODE设置为100。

负代码

在语句期间，当发生意外错误时，数据库服务器在SQLCODE中返回一个负数值来说明该问题。在联机错误消息文件中记录这些代码的含义。

9.2.3 SQLERRD 数组

在SQLCODE中可报告的某些错误代码反映一般的问题。数据库服务器可在SQLERRD 的第二个字段中设置更详细的代码，显示数据库服务器I/O例程或操作系统遇到的错误。

将SQLERRD数组中的整数设置为跟在不同语句之后的不同值。在GBase 8s ESQL/C中，仅使用数组的第一个和第四个元素。下表展示如何使用这些字段。

表1. SQLERRD的字段

字段

解释

第一个

对于SELECT、UPDATE、INSERT或DELETE语句，在成功的PREPARE语句之后，或在打开Select游标之后，此字段包含估计的受影响的行数。

第二个

当SQLC0DE包含一个错误代码时，此字段包含零或附加的错误代码，称为ISAM错误代码，说明主要错误的原因。在对单个行的成功的插入操作之后，此字段包含任何SERIAL、BIGSERIAL或SERIAL8值为那行生成的值。（然而，当通过表上的触发器，或通过视图上的INSTEAD OF触发器，将一序列列作为触发器的活动直接插入时，不更新此字段。）

第三个

在成功的多行插入、更新或删除操作之后，此字段包含处理了的行数。在以错误结束的多行插入、更新或删除操作之后，此字段包含在检测到该错误之前成功地处理了的行数。

第四个

在对于SELECT、UPDATE、INSERT或DELETE语句的成功的PREPARE语句之后，或在已打开了选择游标之后，此字段包含磁盘访问的与处理的全部行的估计加权总和。

第五个

在PREPARE, EXECUTE IMMEDIATE, DECLARE或静态的SQL语句中的语法错误之后，此字段包含检测到该错误的位置的语句文本的偏移量。

第六个

在对选择了的行的成功的访存之后，或成功的插入、更新或删除操作之后，此字段包含处理了的最后一行的rowid （物理地址）。此rowid值是否对应于数据库服务器返回给用户的行，依赖于数据库服务器处理查询的方式，特别是对于SELECT语句。

第七个

保留。

这些附加的详细信息可是有用的。例如，您可使用第三个字段中的值来报告删除了或更新了多少行。当您的程序准备一个用户输入的SQL语句并发现错误时，第五个字段中的值使得您能够向用户显示错误的精确点。（当您在错误之后请求修改语句时，DB-Access使用此特性来定位游标。）

9.2.4 SQLWARN 数组

将SQLWARN数组中的八个字符字段设置为空，或设置为W来指示各种特殊的情况。它们的含义依赖于刚刚执行的语句。

当数据库打开时，即，跟在CONNECT、DATABASE或CREATE DATABASE语句之后，岀现一组警告标志。这些标志告诉您数据库的一些整体特征。

第二组标志跟在任何其他语句之后出现。这些标志反映在该语句期间发生的不寻常事件，这些事件通常没有严重到通过SQLCODE来反映的程度。

在下表中总结这两组SQLWARN值。

表1. SQLWARN的字段

字段

当打开或连接到数据库时

所有其他SQL操作

第一个

当将任何其他警告字段设置

为W时，设置为W。如果为空，则不需要检查其他的。

当将任何其他警告字段设置为W时，设置为 Wo

第二个

当现在打开的数据库使用事务日志

时，设置为Wo

如果截断列值，当使用FETCH或

SELECT…INTO语句将它访存到主变量内时，设置为 W 。在REVOKE ALL语句上，当未取消全部七个表级别权限时，设置为 Wo

第三个

当现在打开的数据库符合ANSI时，设置为 Wo

当FETCH或SELECT语句返回为NULL 的聚集函数（SUM、AVG、MIN、MAX）值时，设置为 Wo

第四个

当数据库服务器为GBase 8s时，设置为 Wo

在 SELECT … INTO、FETCH . … INTO 或 EXECUTE . . . INTO 语句上，当 projection 列表项数不同于在INTO子句中检索它们的主变量的数目时，设置为Wo在GRANT ALL语句上，当未授予全部七个表级别访问权限时，设置为Wo

第五个

当数据库服务器以DECIMAL形式存储FLOAT数据类型时，设置为Wo 当主机系统缺乏对FLOAT类型的支持时，这样做。

如果准备好的对象包含不带有WHERE子

句的DELETE语句或UPDATE语句时，设

置为 Wo

第六个

保留。

跟在不使用ANSI标准SQL语法的语句执行之后（假设设置了DBANSIWARN环境变量），设置为 Wo

字段

当打开或连接到数据库时

所有其他SQL操作

第七个

当将应用程序连接到在数据复制对中为辅助服务器的数据库服务器上时，设置为W。即，该服务器仅对读取操作可用。

在查询处理期间（当DATASKIP特性为on 时），当已跳过了数据分片（dbspace）时，设置为Wo

第八个

当客户机DB_LOCALE与数据库语言环境不相匹配时，设置为W。要获取更多信息，请参阅《GBase 8s GLS用户指南》。

当 SET EXPLAIN ON AVOID_EXECUTE 语句阻止查询执行时，设置为Wo

SQLERRM 字符串
SQLERRM可存储最多72字节的字符串。SQLERRM字符串包含放置在错误消息里的标识符，诸如表名称。对于某些网络化的应用程序，它包含网络软件生成的错误消息。

如果由于违反约束导致INSERT操作失败，则将失败了的约束名称写到SQLERRM。

提示：如果错误字符串长于72字节，则静默地废弃溢出的部分。在某些上下文中，这可导致关于运行时错误的信息丢失。

SQLSTATE 值
某些GBase 8s产品，诸如GBase 8s ESQL/C，支持符合X/Open和ANSI SQL标准的 SQLSTATE值。在您运行SQL语句之后，GET DIAGNOSTICS语句读取SQLSTATE值来诊断错误。数据库服务器以称为SQLSTATE的变量中存储的五个字符的字符串来返回结果代码。SQLSTATE错误代码，或值，告诉您关于最近执行的SQL语句的下列信息：

该语句是否成功
该语句是否成功但生成了警告
•该语句是否成功但未生成数据

该语句是否失败了
要获取关于GET DIAGNOSTICS语句、SQLSTATE变量以及SQLSTATE返回代码的含义的更多信息，请参阅《GBase 8s SQL指南：语法》中的GET DIAGNOSTICS语句。

提示：如果您的GBase 8s产品支持GET DIAGNOSTICS和SQLSTATE,则推荐您使用它们作为检测、处理和诊断错误的主要结构。使用SQLSTATE允许您检测多个错误，且它符合 ANSI。

9.3检索单行

SELECT语句返回的行集是它的活动集。单个SELECT语句返回单个行。您可使用嵌入式 SELECT语句来从数据库将单个行检索到主变量内。然而，当SELECT语句返回多行数据时，程序必须使用游标来一次检索一行。在检索多行中讨论“多行”选择操作。

要检索单行数据，只要在您的程序中嵌入SELECT语句。下列示例展示您可如何使用GBase 8s ESQL/C来编写嵌入式SELECT语句：

EXEC SQL SELECT avg (total_price)

INTO :avg_price

FROM items

WHERE order_num in

(SELECT order_num from orders WHERE order_date < date(‘6/1/98’));

INTO子句是将此语句与编写SELECT语句或编写高级SELECT语句中的任何示例区分开来的唯一细节。此子句指定要检索产生的数据的主变量。

当程序执行嵌入式SELECT语句时，数据库服务器执行该查询。示例语句选择聚集值，以便于它恰好产生一行数据。该行仅有单个列，且它的值存储在名为avg_price的主变量中。程序的后续行可使用那个变量。

您可使用此类语句来将单行数据检索到主变量内。单个行可有所期望的许多列。如果查询产生多行数据，则数据库服务器不可返回任何数据，而是返回一个错误代码。

您在INTO子句中罗列的主变量应与选择列表中的项一样多。如果这些列表的长度碰巧不一样，则数据库服务器返回尽可能多的值并在SQLWARN的第四个字段中设置警告标志。

9.3.1数据类型转换

下列GBase 8s ESQL/C示例检索DECIMAL列的平均值，其自身是DECIMAL值。然而，将DECIMAL列的平均值放置其内的主变量不要求具有那种数据类型。

EXEC SQL SELECT avg (total_price) into :avg_price

FROM items;

不展示在前面的GBase 8s ESQL/C代码示例中接收的变量avg_price的声明。该声明可为任一下列定义：

int avg_price;

double avg_price;

char avg_price[16];

dec_t avg_price; /* typedef of decimal number structure */

注释语句中使用的每一主变量的数据类型，并使用该语句传到数据库服务器。数据库服务器尽量将列数据转换为接收的变量使用的形式。允许几乎任何转换，尽管某些转换会导致精度损失。依赖于接收的主变量的数据类型，前面的示例的结果会不同，如下表所示。

数据类型

结果

FLOAT

数据库服务器将十进制结果转换为FLOAT，可能截断某些小数位。如果

十进制的数量超过FLOAT格式的最大数量，则返回一个错误。

INTEGER

数据库服务器将结果转换为INTEGER，如有必要会截断小数位。如果被转换的数值的整数部分与接收的变量不适合，则发生错误。

CHARACTER

数据库服务器将十进制值转换为CHARACTER字符串。如果对于接收的变量来说该字符串太长，则截断它。将SQLWARN的第二个字段设置为W，且SQLSTATE变量中的值为01004。

9.3.2如果程序检索到NULL值，该怎么办？

可在数据库中存储NULL值，但编程语言支持的数据类型不识别NULL状态。程序必须采用某种方式来识别NULL项，以免将它作为数据来处理。

在SQL API中，指示符变量满足此需要。指示符变量是与可能收到NULL项的主变量相关联的一个附加的变量。当数据库服务器将数据放在主变量中时，它还在指示符变量中放置一个特殊的值来展示该数据是否为NULL。在下列GBase 8s ESQL/C示例中，选择单个行，并将单个值检索到主变量op_date内：

EXEC SQL SELECT paid_date

INTO :op_date:op_d_ind

FROM orders

WHERE order_num = $the_order;

if （op_d_ind < 0） /* data was null */

rstrdate （‘01/01/1900’, :op_date）;

语句该值可能为NULL，名为op_d_ind的指示符变量与该主变量相关联。（必须在程序中的其他地方将它声明为以短整数。）

跟在SELECT语句的执行之后，程序测试该指示符变量为负值。负值（通常为-1）意味着检索到主变量内的值为NULL。如果该变量为NULL，则此程序使用GBase 8s ESQL/C库函数来将缺省的值指定给主变量。（函数rstrdate是GBase 8s ESQL/C产品的一部分。）

您用来将指示符变量与主变量相关联的语法，不同于您正在使用的语言，但在所有语言中，该原则是相同的。

9.3.3处理错误

虽然数据库服务器自动地处理数据类型之间的转换，但使用SELECT语句仍可发生错误。在SQL编程中，如同在任何种类的编程中一样，您必须预见错误并随时为应对其做好准备。

数据的结束

通常会发生没有行满足查询的情况。在SELECT语句之后，通过SQLSTATE代码02000 和SQLCODE中的代码100标志此事件。此代码指示一个错误或一般事件，这完全依赖于您的应用程序。如果您确信应有一行或多行满足查询（例如，如果您使用您刚从另一表的行读取的键值读取一行的话），则“数据的结束”代码表示在该程序的逻辑中的严重错误。在另一方面，如果您基于用户提供的键，或其他来源提供的不如程序可靠的键，则数据的缺乏可能是正常事件。

不符合ANSI的数据库的数据的结束

如果您的数据库不符合ANSI，则跟在SELECT语句之后，仅在SQLCODE中设置“数据的结束”返回代码100。此外，将SQLSTATE值设置为02000。（诸如INSERT、 UPDATE和DELETE这样的其他语句设置SQLERRD的第三个元素，来展示他们影响了多少行；通过SQL程序修改数据讨论此主题。）

严重的错误

将SQLCODE设置为负值的错误，或将SQLSTATE设置为任何不以00、01或02开头的值的错误通常都很严重。您已开发好的程序或正在生产的程序几乎不应报告这些错误。然而，很难预料每一种有问题的情况，因此您的程序必须能够处理这些错误。

例如，查询可返回错误-206,这意味着在该查询中指定的表不在数据库中。如果有人在编写了程序之后删除了该表，或如果该程序通过某种逻辑错误或输入错误打开了错误数据库，则会发生此情况。

使用聚集函数解释数据的结束

使用诸如SUM、MIN或AVG这样的聚集函数的SELECT语句总会成功地返回至少一行数据，即使当没有行满足WHERE子句时。基于行的空集的聚集值为空，但它仍然存在。

然而，如果聚集值是基于都包含空值的一行或多行，则聚集值也为空。如果您必须能够检测不基于任何行的聚集值与基于某些都是空的行的聚集值之间的差异，则您必须在该语句中包括COUNT函数和聚集值上的指示符变量。然后，您可得出下列情况。

计数值

指示

符

具体情况

计数值

指示

符

具体情况

-1

选择了零行

0

-1

选择了某些行；全部为空

0

选择了一些非空行

缺省值

您可以许多方式处理这些不可避免的错误。在某些应用程序中，使用比执行功能更多的行代码来处理错误。然而，在本部分的示例中，最简单的解决方案之一，缺省值，应奏效，如下例所示：

avg_price = 0; /设置错误的缺省值/

EXEC SQL SELECT avg (total_price)

INTO :avg_price:null_flag

FROM items;

if (null_flag < 0) /* 可能没有行 */ avg_price = 0; /设置0行的缺省值/

前面的示例处理下列事项：

•如果查询选择一些非空的行，则返回并使用正确的值。这是期望的并且最常用的结果。

•如果查询没有选择行，或在发生的可能性很小的情况下，仅选择在total_price列(从不应为空的列)中有空值的那些行，则设置该指示符变量，并指定缺省值。

•如果发生任何严重的错误，则保持主变量不变；它包含最初设置的缺省值。在程序中的此点，程序员无需捕获这类错误并报告它们。

9.4检索多行

当存在查询可返回多行的任何可能时，程序必须以不同的方式执行该查询。分两个阶段处理多行查询。首先，程序启动查询。(不立即返回数据。)然后，程序一次请求一行数据。

使用称为游标的特殊数据对象来执行这些操作。游标是表示查询的当前状态的数据结构。下表展示程序操作的一般的顺序：

程序声明游标及其相关联的SELECT语句，其只是分配持有该游标的存储。
程序打开游标，其启动相关联的SELECT语句的执行，并检测其中的任何错误。
程序将一行数据访存到主变量内，并处理它。
在访存最后一行之后，程序关闭游标。
当不再需要该游标时，程序释放游标来释放它使用的资源。
使用名为DECLARE、OPEN、FETCH、CLOSE和FREE的SQL语句执行这些操作。

9.4.1声明游标

您使用DECLARE语句来声明游标。此语句给游标一个名称，指定它的使用，并将它与语句相关联。下列示例是用GBase 8s ESQL/C编写的：

EXEC SQL DECLARE the_item CURSOR FOR

SELECT order_num, item_num, stock_num

INTO 😮_num, :i_num, 😒_num

FROM items

FOR READ ONLY;

声明给游标一个名称（在此示例中为the_item）并将它与SELECT语句相关联。（通过SQL 程序修改数据讨论还可如何与INSERT语句相关联。）

此示例中的SELECT语句包含INTO子句。INTO子句指定哪个变量接收数据。您还可使用FETCH语句来指定哪个变量接收数据，如定位INTO子句讨论的那样。

DECLARE语句不是活动的语句；它仅仅创建游标的特性并为它分配存储。您可使用在前面示例中声明的游标来通读items表一次。可声明向后读和向前读游标（请参阅游标输入模式）。由于此游标缺少FOR UPDATE子句，且由于指定FOR READ ONLY，因此，它仅用于读取数据，不修改它。通过SQL程序修改数据说明如何使用游标来修改数据。

9.4.2打开游标

当程序准备使用游标时，它打开它。OPEN语句激活游标。它将相关联的SELECT语句传给数据库服务器，其开始搜索相匹配的行。数据库服务器处理该查询至定位到或构造输出的第一行的位置。它并不真正地返回那行数据，但它确实在SQLSTATE中和在SQLCODE 中为SQL API设置返回代码。下列示例展示GBase 8s ESQL/C中的OPEN语句：

EXEC SQL OPEN the_item;

由于数据库服务器正在第一次查看查询，因此，它可能检测到一些错误。在程序打开游标之后，它应测试SQLSTATE或SQLCODE。如果SQLSTATE值大于02000或SQLCODE 包含负值，则该游标不可用。在SELECT语句中可能出现错误，或某些其他问题可能阻止数据库服务器执行该语句。

如果SQLSTATE等于00000，或SQLCODE包含零，则SELECT语句在语法上是有效的，且准备使用该游标。然而，此时，该程序不知道游标能否产生任何行。

9.4.3访存行

程序使用FETCH语句来检索输岀的每一行。此语句命名游标，且还可命名接收该数据的主变量。下列示例展示完整的GBase 8s ESQL/C代码：

EXEC SQL DECLARE the_item CURSOR FOR

SELECT order_num, item_num, stock_num

INTO 😮_num, :i_num, 😒_num

FROM items;

EXEC SQL OPEN the_item;

while(SQLCODE == 0)

{

EXEC SQL FETCH the_item;

if(SQLCODE == 0)

printf("%d, %d, %d", o_num, i_num, s_num);

}

检测数据的结束

在前面的示例中，WHILE条件在OPEN语句返回错误时阻止执行循环。当将SQLCODE 设置为100来标志数据的结束时，相同的条件会终止该循环。然而，该循环包含SQLCODE 的测试。此测试是必需的，因为如果SELECT语句是有效的但找不到相匹配的行，则OPEN 语句返回零，但第一次访存返回100 (数据的结束)并不返回任何数据。下列示例展示编写同一循环的另一种方式：

EXEC SQL DECLARE the_item CURSOR FOR

SELECT order_num, item_num, stock_num

INTO 😮_num, :i_num, 😒_num

FROM items;

EXEC SQL OPEN the_item;

if(SQLCODE == 0)

EXEC SQL FETCH the_item; /* fetch 1st row*/

while(SQLCODE == 0)

{

printf("%d, %d, %d", o_num, i_num, s_num);

EXEC SQL FETCH the_item;

}

在此版本中，早已处理了无返回行的情况，因此，在循环中不存在第二次SQLCODE测试。由于SQLCODE测试的时间成本是访存成本的很小一部分，因此这些版本在性能上没有多大差异。

定位INTO子句

INTO子句命名要接收数据库服务器返回的数据的主变量。INTO必须出现在SELECT或 FETCH语句中。然而，它不可同时出现在两个语句中。下列示例指定FETCH语句中的主变量：

EXEC SQL DECLARE the_item CURSOR FOR

SELECT order_num, item_num, stock_num

FROM items;

EXEC SQL OPEN the_item;

while(SQLCODE == 0)

{

EXEC SQL FETCH the_item INTO 😮_num, :i_num, 😒_num; if(SQLCODE == 0)

printf("%d, %d, %d", o_num, i_num, s_num);

}

此形式允许您将不同的行访存到不同的位置内。例如，您可以使用此形式来将连续的行访存到数组的连续元素内。

9.4.4游标输入模式

为了输入，游标以顺序的或滚动的两种模式中的一种运行。顺序的游标仅可访存序列中的下一行，因此，每一次打开游标，顺序的游标仅可通读表一次。滚动游标可访存下一行或任何输出行，因此，滚动游标可多次读取相同的行。下列示例展示在GBase 8s ESQL/C中声明的顺序的游标。

EXEC SQL DECLARE pcurs cursor for

SELECT customejnum, Iname, city

FROM customer;

在打开游标之后，仅可使用检索下一行数据的顺序的访存来使用它，如下例所示：

EXEC SQL FETCH p_curs into:cnum, :clname, :ccity;

每一顺序的访存返回一个新行。

使用关键字SCROLL CURSOR声明滚动游标，如来自GBase 8s ESQL/C的下列示例所示的那样：

EXEC SQL DECLARE s_curs SCROLL CURSOR FOR

SELECT order_num, order_date FROM orders

WHERE customer_num > 104

使用不同的访存选项来使用滚动游标。例如，ABSOLUTE选项指定要访存的行的绝对行位置。

EXEC SQL FETCH ABSOLUTE :numrow s_curs

INTO :nordr, :nodat

此语句访存在主变量numrow中给出其位置的行。您还可在此访存当前的行，或您可访存第一行然后再次扫描所有行。然而，这些特性可能导致应用程序运行得更慢，如下一部分描述的那样。要了解适用于滚动游标的附加的选项，请参阅《GBase 8s SQL指南：语法》中的FETCH 语句。

9.4.5游标的活动集

一旦打开游标，就意味着选择一些行。查询产生的所有行的集合称为该游标的活动集。可以简单地将活动集视为定义良好的行的集合，且将游标视为指向该集合的一行。只要没有其他程序正在并发地修改同一数据，就会发生此情况。

创建活动集

当打开游标时，数据库服务器进行有必要的任何操作来定位选择了的数据的第一行。依赖于该查询的叙述方式，此活动可很容易，或者它可需要大量的工作和时间。请考虑下列游标的声明：

EXEC SQL DECLARE easy CURSOR FOR

SELECT fname, lname FROM customer

WHERE state = ‘NJ’

由于在简单的方式中此游标仅需要单个表，因此，数据库服务器快速地确定是否有任何行满足该查询，并标识第一行。第一行是该游标此次找到的唯一一行。该活动集中余下的行仍然未知。作为对比，请考虑下列游标的声明：

EXEC SQL DECLARE hard SCROLL CURSOR FOR

SELECT C.customer_num, O.order_num, sum (items.total_price) FROM customer C, orders O, items I

WHERE C.customer_num = O.customer_num

AND O.order_num = I.order_num

AND O.paid_date is null GROUP BY C.customer_num, O.order_num

通过连接三个表并将输出行分组，生成此游标的活动集。优化器可能能够使用索引来以正确的顺序产生这些行，但通常情况下，ORDER BY或GROUP BY子句的使用要求在可确定哪一行标识第一行之前，数据库服务器生成所有行，将它们复制到临时表并对该表排序。在活动集全部生成并保存在临时表中的情况下，数据库服务器可花费相当多的时间来打开游标。接着，数据库服务器可以确切地告诉程序活动集包含多少行。然而，此信息不可用。一个原因是您永远不可确定优化器会使用哪种模式。如果优化器可避免排序和临时表，则它会这样做；但在查询中、在表的大小方面或在可用的索引方面的小更改都可更改优化器的方式。

顺序游标的活动集

数据库服务器尝试使用尽可能少的资源来维护游标的活动集。如果它可这么做，则数据库服务器从不保留下次访存的单个行之外的行。它可为大部分顺序的游标这么做。对于每一访存，它都返回当前行的内容并定位下一行。

SCROLL游标的活动集

必须保留SCROLL游标的活动集中的所有行，直到游标关闭为止，因为数据库服务器不可确定程序下一次会请求哪一行。

更常见的是，数据库服务器将滚动游标的活动集作为临时表来实现。然而，数据库服务器可能不立即填充此表（除非它创建了临时表来处理该查询）。通常当打开游标时，它创建临时表。然后，第一次访存行时，数据库服务器将它复制到临时表内并将它返回到程序。当第二次访存行时，可从临时表取得它。如果在程序访存所有行之前，它放弃该查询，则此方案使用最少的资源。不创建或保存从不访存的行。

活动集和并发

当仅一个程序正在使用数据库时，活动集的成员不可更改。大多数个人计算机都是这种情况，且是要考虑的最简单情况。但必须为了在多编程系统中使用来设计一些程序，在此，两个、三个或几十个不同的程序可同时在相同的表上工作。

在您的游标是打开的时，当其他程序可更新表时，活动集的思路用处不大了。您的程序在某一时刻仅看到一行数据，但表中的所有其他行可能正在更改。

在简单查询的情况下，当数据库服务器仅持有活动集的一行时，任何其他行都可更改。在您的程序访存行之后的那一刻，另一程序可删除同一行或更新它，于是，如果在此检查它，它不再是活动集的一部分。

当在临时表中保存活动集或它的一部分时，旧数据可出现问题。即，从其派生活动集行的实际的表中的行可更改。如果真是这样，某些活动集行不再反映当前的表内容。

最初这些想法令人不安，但只要您的程序仅读取数据，就不存在旧数据，更确切地说，所有数据都同样陈旧。活动集是数据在某一时刻的快照。第二天行就不一样了；如果它在下一毫秒也不一样，倒无所谓。换言之，在程序正在运行时发生的更改，与该程序终止的那一刻保存和应用的更改之间，没有实际的差异。

旧数据可导致问题的唯一时刻，是当程序打算使用输入的数据来修改同一数据库时；例如，当银行业应用程序必须读取账户余额、更改它并将它写回时。通过SQL程序修改数据讨论修改数据的程序。

9.4.6部件爆炸问题

当您使用由程序逻辑补充的游标时，您可解决普通的SQL不可解决的问题。这些问题之一就是部件爆炸问题，有时称为材料单处理。此问题的核心是对象之间的递归关系；一个对象包含其他对象，其又包含其他对象。

通常以制造库存为例来说明该问题。例如，公司制造各种部件。有些部件是分立的，但有些是其他部件的组合。

在可能称为contains的单个表中说明这些关系。列contains.parent持有系组合的部件的部件编号。列contains.child具有为父部件的组件的部件的部件编号。如果部件编号123400是九个部件的组合，则存在九行，123400在第一列中，其他部件编号在第二列中。下图展示描述部件编号123400的多行中的一行。

图部件爆炸问题

部件爆炸问题在于：给定一个部件编号，产生为那个部件的组件的所有部件的列表。下列示例是一种解决方案的概要，如以GBase 8s ESQL/C实现的那样：

int part_list[200];

boom(top_part)

int top_part;

(

long this_part, child_part;

int next_to_do = 0, next_free = 1;

part_list[next_to_do] = top_part;

EXEC SQL DECLARE part_scan CURSOR FOR

SELECT child INTO child_part FROM contains

WHERE parent = this_part;

while(next_to_do < next_free)

(

this_part = part_list[next_to_do];

EXEC SQL OPEN part_scan;

while(SQLCODE == 0)

(

EXEC SQL FETCH part_scan;

if(SQLCODE == 0)

(

part_list[next_free] = child_part;

next_free += 1;

}

EXEC SQL CLOSE part_scan;

next_to_do += 1;

}

return (next_free - 1);

}

从技术上讲，contains表的每一行都是有向无环图，或树，的头结点。该函数执行对该树的宽度优先搜索，树根是作为它的参数传递的部件编号。该函数使用名为part_scan的游标返回在parent列中带有特定的值的所有行。最内层的while循环打开part_scan游标，在选择集中访存每一行，并当已检索了每一组件的部件编号时，关闭该游标。

此函数解决部件爆炸问题的核心，但该函数不是完整的解决方案。例如，它不允许组件在树中出现多个级别。此外，实际的contains表还会有列count,给出在每一 parent中使用的child部件的计数。返回每一组件部件的总计数的程序要复杂得多。

之前描述的迭代方法不是解决部件爆炸问题的唯一方法。如果代的数目有固定的限制，则您可使用嵌套的外部自连接，以单个SELECT语句解决该问题。

如果在一个最高级别部件内，可包含最多四代部件，则下列SELECT语句返回所有部件：

SELECT a.parent, a.child, b.child, c.child, d.child

FROM contains a

OUTER (contains b,

OUTER (contains c, outer contains d))

WHERE a.parent = top_part_number

AND a.child = b.parent

AND b.child = c.parent

AND c.child = d.parent

此SELECT语句为来源于指定为top_part_number的祖先的每一行返回一行。对于不存在的级别，返回Null值。（请使用指示符变量来检测它们。）要将此解决方案扩展到更多级别，请选择contains表的附加的嵌套外部连接。您还可修订此解决方案来返回每一级别上部件的数目的计数。

9.5动态SQL

虽然静态SQL是有用的，但在您编写程序的时候，它要求您知道每个SQL语句确切内容。例如，您必须确切说明在任何WHERE子句中测试哪些列，以及在任何选择列表中重命名哪些列。

当您编写程序来执行定义良好的任务时，不存在任何问题。但不可事先完善地定义某些程序的数据库任务。特别地，必须响应交互用户的程序可能需要根据用户输入的内容来组合 SQL语句。

动态SQL允许程序在执行期间形成SQL语句，因此，用户输入决定该语句的内容。以下列步骤执行此活动：

程序将SQL语句的文本组装为一个字符串，将该字符串存储在程序变量中。
它执行PREPARE语句，请求数据库服务器测试该语句文本并为执行来准备它。
它使用EXECUTE语句来执行该准备好的语句。
这样，基于任何种类的用户输入，程序可构造并然后使用任何SQL语句。例如，它可读取一个SQL语句的文件并准备和执行每一语句。

DB-Access是一个您可用来交互地探索SQL的实用程序，它是一个动态地构造、准备和执行SQL语句的GBase 8s ESQL/C程序。例如，DB-Access允许您使用简单的、交互式的菜单来指定表的行。当您完成时，DB-Access动态地构建必需的CREATE TABLE或 ALTER TABLE语句并准备和执行它。

9.5.1准备语句

在形式上，动态SQL语句像任何其他写入程序的SQL语句一样，除了它不可包含任何主变量的名称之外。

准备好的SQL语句有两个限制。首先，如果它是SELECT语句，则它不可包括

INTO variable子句。INTO variable子句指定将列数据放入其内的主变量，而不允许在准备好的对象的文本中使用主变量。其次，不论主变量的名称通常出现在表达式中的任何位置，都将问号（？）写作PREPARE语句中的占位符。仅PREPARE语句可指定问号（?）占位符。

您可使用PREPARE语句以此形式为执行准备语句。使用GBase 8s ESQL/C编写下列示例：

EXEC SQL prepare query_2 from

'SELECT * from orders

WHERE customer_num = ? and order_date > ?’;

此示例中的两个问号指示当执行该语句时，在那两个位置使用主变量的值。

您可动态地准备几乎任何SQL语句。您唯一不可准备的语句就是与动态SQL和游标管理直接相关联的语句，诸如PREPARE和OPEN语句。在您准备UPDATE或DELETE语句之后，最好测试SQLWARN的第五个字段来查看您是否使用了 WHERE子句(请参阅SQLWARN数组)。

准备语句的结果是表示该语句的数据结构。此数据结构与产生它的字符串不一样。在 PREPARE语句中，您赋予该数据结构一个名称；它是前面示例中的query_2。使用此名称来执行准备好的SQL语句。

PREPARE语句不将字符串限制于一个语句。它可包含多个用分号分隔的SQL语句。下列示例展示用GBase 8s ESQL/C编写的相当复杂的事务：

strcpy(big_query, "UPDATE account SET balance = balance + ?

WHERE customer_id = ?; \ UPDATE teller SET balance = balance + ? WHERE teller_id = ?;");

EXEC SQL PREPARE bigl FROM :big_query;

当执行此语句的列表时，主变量必须为六个占位的问号提供值。虽然设置多语句列表更为复杂，但由于在程序与数据库服务器之间发生更少的交换，因此性能往往更好。

9.5.2执行准备好的SQL

在您准备语句之后，您可多次执行它。使用EXECUTE语句执行不是SELECT语句的那些语句，以及仅返回一行的SELECT语句。

下列GBase 8s ESQL/C代码准备并执行银行账户的多语句更新：

EXEC SQL BEGIN DECLARE SECTION;

char bigquery[270] = “begin work;”;

EXEC SQL END DECLARE SECTION;

stcat ("update account set balance = balance + ? where ", bigquery);

stcat ("acct_number = ?;’, bigquery);

stcat ("update teller set balance = balance + ? where ", bigquery);

stcat ("tellejnumber = ?;’, bigquery);

stcat ("update branch set balance = balance + ? where ", bigquery); stcat ("branch_number = ?;’, bigquery);

stcat (“insert into history values(timestamp, values);”, bigquery);

EXEC SQL prepare bigq from :bigquery;

EXEC SQL execute bigq using :delta, :acct_number, :delta,

:teller_number, :delta, :branch_number;

EXEC SQL commit work;

EXECUTE语句的USING子句提供主变量的列表，以其值替代准备好的语句中的问号。如果SELECT (或EXECUTE FUNCTION)仅返回一行，则您可使用EXECUTE的INTO 子句来指定接收这些值的主变量。

9.5.3动态主变量

支持动态地分配数据对象的SQL API进动态语句更进一步。它们允许您动态地分配接收列数据的主变量。

变量的动态分配使得有可能从程序输入中取到任意的SELECT语句，确定它产生多少个值及其数据类型，并分配适当类型的主变量来保存它们。

此功能的关键是DESCRIBE语句。它取到准备好的SQL语句的名称，并返回关于该语句及其内容的信息。它设置SQLCODE来指定语句的类型；即，它开头的动词。如果准备好的语句是SELECT语句，则DESCRIBE语句还返回关于选择了的输出数据的信息。如果准备好的语句是INSERT语句，则DESCRIBE语句返回关于输入参数的信息oDESCRIBE 语句返回信息的数据结构是预定义的数据结构，为此用途分配该数据结构并称为系统描述符区域。如果您正在使用GBase 8s ESQL/C，则可使用系统描述符区域，或作为sqlda结构的替代方案。

DESCRIBE语句返回的或为SELECT语句引用的数据结构包括结构的一个数组。每一结构描述为选择类表中一个项目的数据。程序可检测该数组并发现包括十进制值、某长度的字符值以及整数的一行数据。

利用此信息，程序可分配保存接收了的数据的内存，并为要使用的数据库服务器将必要的指针放置在该数据结构中。

9.5.4释放准备好的语句

准备好的SQL语句占据内存中的空间。对于某些数据库服务器，它可消耗数据库服务器拥有的空间以及属于该程序的空间。当程序终止时，释放此空间，但通常您应在使用完此空间时就释放它。

您可使用FREE语句来释放此空间。FREE语句采用语句的名称，或为语句名称声明了的游标的名称，并释放分配给准备好的语句的空间。如果在该语句上定义多个游标，则释放该语句不会释放游标。

9.5.5快速执行

对于不需要游标或主变量的简单语句，您可将PREPARE, EXECUTE和FREE语句组合到单个操作内。下列示例展示EXECUTE IMMEDIATE语句如何获取字符串、准备它、执行它，并释放一个操作中的存储：

EXEC SQL execute immediate ‘drop index my_temp_index’;

此能力使得编写简单SQL语句更轻松。然而，由于不允许使用USING子句，EXECUTE IMMEDIATE语句不可用于SELECT语句。

9.6嵌入数据定义语句

数据定义语句是创建数据库和修改表的定义的SQL语句，通常将这些语句放在程序内。原因是很少执行它们。数据库只创建一次，但会多次查询和更新它。

使用DB-Access，一般都是交互地完成数据库及其表的创建。还可从语句的文件运行这些工具，因此可使用操作系统命令来完成数据库创建。在GBase 8s SQL指南：语法中描述数据定义语句。

9.7授予和撤销应用程序中的权限

反复地执行与数据定义相关的一个任务：授予和撤销权限。由于必须频繁地授予和撤销权限，有可能是由不熟悉SQL的用户操作，因此一种策略是将GRANT和REVOKE语句打包在程序中，来提供给他们更简单、更方便的用户接口。

GRANT和REVOKE语句特别适合于动态SQL。每一语句采用下列参数：

一个或多个权限的列表
•表名称

用户的名称
您可能至少需要基于程序输入（来自于用户、命令行参数或文件）提供这些值的一部分，但都不可以主变量的形式提供。这些语句的语法不允许在任何位置使用主变量。

一个替代方案是将语句的各个部分组合到字符串内，并准备和执行组合好的语句。程序输入可作为字符串合并到准备好的语句内。

下列GBase 8s ESQL/C函数从参数组合GRANT语句，然后准备和执行它：

char priv_to_grant[100];

char table_name[20];

char usejid[20];

table_grant(priv_to_grant, table_name, usejid)

char *priv_to_grant;

char *table_name;

char*use曰d;

{

EXEC SQL BEGIN DECLARE SECTION;

char grant_stmt[200];

EXEC SQL END DECLARE SECTION;

sprintf(grant_stmt, “GRANT %s ON %s TO %s”, priv_to_grant, table_name, user_id);

PREPARE the_grant FROM :grant_stmt;

if(SQLCODE == 0)

EXEC SQL EXECUTE the_grant;

else

printf(“Sorry, got error # %d attempting %s”,

SQLCODE, grant_stmt);

EXEC SQL FREE the_grant;

}

下列示例展示的该函数的打开语句指定它的名称及其三个参数。这三个参数指定要授予的权限、对其授予权限的表的名称，以及要接收它们的用户的ID。

table_grant(priv_to_grant, table_name, usejid)

char *priv_to_grant;

char *table_name;

char*use曰d;

该函数使用下例中的语句来定义本地变量grant_stmt，使用其来组合并保存GRANT语句：

EXEC SQL BEGIN DECLARE SECTION;

char grant_stmt[200];

EXEC SQL END DECLARE SECTION;

如下例所示，通过将该语句的常量部分与函数参数连起来，创建该GRANT语句：

sprintf(grant_stmt, " GRANT %s ON %s TO %s",priv_to_grant,

table_name, user_id);

此语句将下列六个字符串连接起来：

‘GRANT’
•指定要授予的权限的参数

‘ON’
•指定表名称的参数

‘TO’
•指定用户的参数

结果是部分由程序输入组成的完整的GRANT语句。PREPARE语句将组合的语句文本传给数据库服务器进行解析。

如果数据库服务器跟在PREPARE语句之后在SQLCODE中返回错误代码，则该函数显示错误消息。如果数据库服务器认可该语句的形式，则它设置零返回值。此活动并不保证正确地执行该语句；它仅意味着该语句的语法正确。它可能引用不存在的表或某些种类仅可在执行期间才能检测到的错误。该示例的下列部分在执行之前检查the_grant是否准备成功了：

if(SQLCODE == 0)

EXEC SQL EXECUTE the_grant;

else

printf(“Sorry, got error # %d attempting %s”, SQLCODE, grant_stmt);

如果准备成功，则SQLCODE = = 0,下一步执行准备好的语句。

9.7.1指定角色

或者，DBA可使用CREATE ROLE语句定义一个角色，并使用GRANT和REVOKE语句来将角色分配给用户或取消，以及授予和撤销角色权限。例如：

GRANT engineer TO nmartin;

需要SET ROLE语句来激活非缺省的角色。要获取关于角色和权限的更多信息，请参阅访问管理策略和对数据库级对其对象的权限。要获取关于这些语句的语法的更多信息，请参阅《GBase 8s SQL指南：语法》。

9.8总结

可将SQL语句写入程序内，如同它们是不同的编程语言的语句那样。可在WHERE子句中使用程序变量，可将来自数据库的数据访存到它们之内。预处理器将SQL代码翻译为过程调用和数据结构。

编写不返回数据的语句，或仅返回一行数据的查询，就像该语言的普通命令语句一样。可返回多行的查询与表示当前数据行的游标相关联。通过游标，程序可根据需要访存数据的每一行。

将静态SQL语句写入程序的文本内。然而，程序在它运行时动态地形成新的SQL语句，并执行它们。在最先进的情况下，程序可获得关于查询返回的列的数目和类型，并动态地分配内存空间来保存它们。
————————————————
版权声明：本文为CSDN博主「aisirea」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/aisirea/article/details/122999830