GBase 8s SQL 指南：教程———12 创建和使用SPL例程

本部分描述如何创建和使用SPL例程。SPL例程是以GBase 8s "存储过程语言”（SPL） 编写的用户定义的例程。GBase 8s SPL是提供流控制的SQL的扩展，诸如循环和分支。 在数据库上有Resource权限的任何人都可创建SPL例程。

尽可能地解析和优化以SQL编写的例程，然后以可执行的格式存储在系统目录表中。对 于SQL密集的任务，SQL例程可能是一个好的选择。SPL例程可执行以C或其他外部 语言编写的例程，且外部的例程可执行SPL例程。

您可使用SPL例程来执行您可以SQL执行的任何任务，且可扩展您可单独使用SQL完 成的任务。由于SQPL是数据库的本地语言，且当创建SPL例程而不是在运行时时，解 析和优化SPL例程，对于某些任务，SPL例程可提升性能。SPL例程还可减少客户机应 用程序与数据库服务器之间的流量并降低程序复杂度。

在GBase 8s SQL指南：语法中描述每一 SPL语句的语法。每一语句都配有示例。

12.1 SPL例程介绍

SPL例程是包括SPL过程和SPL函数的一个广义术语。SPL过程是以SPL和SQL 编写的不返回值的例程。SPL函数是以SPL和SQL编写的返回单个值、复合数据类型 的值或多个值的例程。通常，以SPL编写的返回一个值的例程是SPL函数。

使用SQL和SPL语句来编写SPL例程。仅可在CREATE PROCEDURE, CREATE PROCEDURE FROM、CREATE FUNCTION 和 CREATE FUNCTION FROM 语句内使用 SPL语句。使用诸如GBase 8s ESQL/C这样的SQL API都可用所有这些语句。使用 DB-Access 可用 CREATE PROCEDURE 和 CREATE FUNCTION 语句。

要在数据库中罗列所有SPL例程，请运行此命令，该命令创建和显示数据库的模式：

dbschema -d database_name -f all

1. 1. 1使用SPL例程可做什么

使用SPL例程，您可实现广泛的目标，包括提升数据库性能，简化应用程序编写，以及限 制或监视对数据的访问。

由于以可执行的格式存储SPL例程，您可使用它来频繁地执行反复的任务以提升性能。当 您执行SPL例程而不是直接的SQL代码时，您可绕过反复的解析、有效性检查以及查询 优化。

您可在数据操纵SQL语句中使用SPL例程来为那个语句提供值。例如，您可使用例程来 执行下列操作：

•提供要插入到表内的值

• 提供一个值，该值是组成SELEC、DELETE或UPDATE语句中条件子句的一部

分

这些操作是在数据操作语句中例程的两种可能的使用，但也存在其他的。实际上，数据操 纵SQL语句中的任何表达式都可由例程调用构成。

您还可在SPL例程中发出SQL语句来对数据库用户隐藏那些SQL语句。不是让所有用 户都了解如何使用SQL, 一位有经验的SQL用户可编写SPL例程来封装SQL活动， 并让其他人了解在该数据库中存储着该例程，以便他们可以执行它。

您可编写SPL例程，由不具有DBA权限的用户使用DBA权限来运行它。此特征允许 您限制和控制对数据库中数据的访问。另外，SPL例程可监视访问某些表或数据的用户。

12.2 SPL例程格式

SPL例程由开始语句、语句块和结束语句组成。在语句块内，您可使用SQL或SPL语 句。

1. 1. 1 CREATE PROCEDURE 或 CREATE FUNCTION 语句

您必须首先决定您正在创建的例程是否返回值。如果例程不返回值，则使用CREATE PROCEDURE语句来创建一个SPL过程。如果例程返回一个值，则使用CREATE FUNCTION语句来创建一个SPL函数。

要创建SPL例程，请使用一个CREATE PROCEDURE或CREATE FUNCTION语句来 编写该例程体，并注册它。

开始和结束例程

要创建不返回值的SPL例程，请使用CREATE PROCEDURE语句开始，并以END

PROCEDURE关键字结束。下图展示如何开始和结束SPL过程。

图:开始和结束SPL例程。

CREATE PROCEDURE new_price( per_cent REAL )

END PROCEDURE;

要获取关于命名约定的更多信息，请参阅《GBase 8s SQL指南：语法》中的“标识符”段。

要创建返回一个或多个值的SPL函数，请使用CREATE FUNCTION语句开始，并以 END FUNCTION关键字结束。下图展示如何开始和结束SPL函数。

图:开始和结束SPL函数。

CREATE FUNCTION discount_price( per_cent REAL)

RETURNING MONEY;

END FUNCTION;

在SPL例程中，END PROCEDURE或END FUNCTION 关键字是必需的。

重要：为了与较早的GBase 8s产品相兼容，您可使用带有RETURNING子句的CREATE PROCEDURE来创建返回值的用户定义的例程。然而，如果您对于不返回值的SPL例程(SPL 过程)使用CREATE PROCEDURE，而对于返回一个或多个值的SPL例程(SPL函数)使用 CREATE FUNCTION，则您的代码会更易于阅读和维护，

指定例程名称

紧跟在CREATE PROCEDURE或CREATE FUNCTION语句之后，且在参数列表之前为 SPL例程指定名称，如图所示。

图:为SPL例程指定名称。

CREATE PROCEDURE add_price (arg INT )

GBase 8s允许您以相同的名称但以不同的参数创建多个SPL例程。此特性称为例程重载。 例如，您可能在您的数据库中创建下列每一 SPL例程：

CREATE PROCEDURE multiply (a INT, b FLOAT)

CREATE PROCEDURE multiply (a INT, b SMALLINT)

CREATE PROCEDURE multiply (a REAL, b REAL)

如果您以名称multiply。调用例程，则数据库服务器评估该例程的名称和它的参数来确定执 行哪个例程。

例程解析是数据库服务器在其中搜索它可使用的例程签名，给定例程的名称和参数列表的 过程。每个例程都有一个基于下列信息唯一地标识该例程的签名：

•例程的类型(过程或函数)

•例程名称

参数的数目
参数的数据类型
参数的顺序
如果您输入该例程的完整参数列表，则在CREATE、DROP或EXECUTE语句中使用该 例程签名。例如，下图中的每一语句都使用例程签名。

图:例程签名。

CREATE FUNCTION multiply(a INT, b INT);

DROP PROCEDURE end_of_list(n SET, row_id INT);

EXECUTE FUNCTION compare_point(m point, n point);

添加特定的名称

由于GBase 8s支持例程重载，因此，不可能单独通过SPL例程的名称来唯一地标识它。 然而，可通过特定的名称来唯一地标识例程。除了例程名称之外，特定的名称是您在 CREATE PROCEDURE或CREATE FUNCTION语句中定义的唯一的标识符。使用 SPECIFIC关键字来定义特定的名称，且该名称在数据库中是唯一的。在同一数据库中，不 可有两个相同的特定的名称，即使它们有不同的所有者也不行。

特定的名称最长可达128字节。下图展示如何在创建calculate。函数的CREATE FUNCTION语句中定义特定的名称calcl。

图:定义特定的名称。

CREATE FUNCTION calculate（a INT, b INT, c INT）

RETURNING INT

SPECIFIC calc1;

END FUNCTION;

由于所有者bsmith已给定了 SPL函数特定的名称calcl,因此任何其他用户都不可使用特 定的名称calc1来定义例程 SPL或外部的。现在，您可引用该例程为bsmith.calculate,

或在任何需要SPECIFIC关键字的语句中使用SPECIFIC关键字calcl。

添加参数列表

当您创建SPL例程时，您可定义参数列表，以便当调用例程时，它接受一个或多个参数。 参数列表是可选的。

SPL例程的参数必须有名称，且可使用缺省值来定义。下列是参数可指定的数据类型的种 类：

•内建的数据类型

Opaque数据类型
Distinct 数据类型
Row类型
集合类型
智能大对象（CLOB和BLOB）
参数列表不可直接地指定任一下列数据类型：

SERIAL
SERIAL8
BIGSERIAL
TEXT
BYTE
然而，对于序列数据类型，例程可返回在数值上对等的值，将它们强制转型为对应的整数 类型（INT、INT8或BIGINT）。类似地，对于支持简单大对象数据类型的例程，参数列 表可包括REFERENCES关键字来返回指向TEXT或BYTE对象的存储位置的描述符。

CREATE PROCEDURE raise_price(per_cent INT);

CREATE FUNCTION raise_price(per_cent INT DEFAULT 5);

CREATE PROCEDURE update_emp(n employee」)；

CREATE FUNCTION update_nums( list1 LIST(ROW (a

VARCHAR(10),

b VARCHAR(10), c INT) NOT NULL ));

当您定义参数时，同时完成两个任务：

•当执行例程时，您请求用户提供值。

•您隐式地定义您可在例程体中用作本地变量的变量(带有与参数名称相同的名称)。 如果您以缺省值定义参数，则用户可使用或不用对应的参数来执行该SPL例程。如果用户 执行不带参数的SPL例程，则数据库服务器指定参数的缺省值作为参数。

当您调用SPL例程时，您可对参数给定NULL值。在缺省情况下，SPL例程处理NULL 值。然而，如果参数为集合元素，则您不可对该参数给定NULL值。

简单大对象作为参数

虽然您不可使用简单大对象(包含TEXT或BYTE数据类型的大对象)来定义参数，但 您可使用

CREATE PROCEDURE proc1(lo_text REFERENCES TEXT)

CREATE FUNCTION proc2(lo_byte REFERENCES BYTE DEFAULT

NULL)

REFERENCES关键字意味着将包含指向简单大对象的指针的描述符传给SPL例程，而不 是传对象本身。

未定义的参数

当您调用SPL例程时，您可指定全部或部分已定义的参数，或不指定参数。如果您未指定 参数，且如果它的对应的参数没有缺省值，则给定在SPL例程内作为变量使用的该参数的 状态为未定义的。

未定义是为没有值的SPL变量使用的特殊的状态。只要您在该例程体中不尝试使用状态为 未定义的变量，该SPL例程就不会出错。

未定义的状态与NULL值不一样。(NULL值意味着该值为未知的，或不存在，或不适 用。)

添加返回子句

如果您使用CREATE FUNCTION来创建SPL例程，您必须指定返回一个或多个值的返 回子句。

提示：如果您使用CREATE PROCEDURE语句来创建SPL例程，则您有指定返回子句的选 项。然而，如果您使用CREATE FUNCTION语句来创建返回值的例程，则您的代码会比较易 读且易于维护。

要指定返回子句，请使用带有该例程将返回的数据类型的列表的RETURNING或

RETURNS关键字。数据类型可为除了 SERIAL、SERIAL8、TEXT或BYTE之外的任何 SQL数据类型。

FUNCTION find_group(id INT)

RETURNING INT, REAL;

END FUNCTION;

在您指定返回子句之后，您还必须在例程体中指定RETURN语句，显式地返回调用例程 的值。要获取关于编写RETURN语句的更多信息，请参阅从SPL函数返回值。

要指定应返回简单大对象(TEXT或BYTE值)的函数，您必须使用REFERENCES子 句，如下图所示，这是因为SPL例程仅返回指向该对象的指针，而不是该对象本身。

图:使用REFERENCES子句。

CREATE FUNCTION find_obj(id INT)

RETURNING REFERENCES BYTE;

添加显示标签

您可使用CREATE FUNCTION来创建例程，其为返回的值指定显示标签的名称。如果您 未为显示标签指定名称，则该标签会显示为expressiono

此外，虽然对于返回值的例程推荐使用CREATE FUNCTION，但您可使用CREATE PROCEDURE来创建返回值的例程，并指定返回的值的显示标签。

如果您选择为一个返回值指定显示标签，则您必须为每个返回值指定显示标签。此外，每 一返回值必须有唯一的显示标签。

要添加显示标签，您必须指定返回子句，请使用RETURNING关键字。下图中的返回子句 指定该例程将返回一个带有serial_num显示标签的INT值，一个带有name显示标签的

CHARE值，以及一个带有points显示标签的INT值。您可使用下图中的CREATE FUNCTION 或 CREATE PROCEDURE o

图:指定返回子句。

CREATE FUNCTION p(inval INT DEFAULT 0)

RETURNING INT AS serial_num, CHAR (10) AS name, INT AS points;

RETURN (inval + 1002), “Newton”, 100;

END FUNCTION;

在下图中展示返回的值和它们的显示标签。

图:返回的值和它们的显示标签。

serial_num name points

1002 Newton 100

提示：由于您可在SELECT语句中直接地为返回值指定显示标签，因此，当在SELECT语 句中使用SPL例程时，该标签会显示为expression。要获取关于在SELECT语句中为返 回值指定显示标签的更多内容，请参阅编写SELECT语句。

指定SPL函数是否为变体

当您创建SPL时，在缺省情况下，该函数为变体。当使用相同的参数调用函数时，如果它 返回不同的结果，或如果它修改数据库或变量状态，则该函数为变体。例如，返回当前的 日期或时间的函数是变体函数。

虽然在缺省情况下，SPL函数为变体，但如果当您创建函数时指定WITH NOT VARIANT， 则该函数不可包含任何SQL语句。您仅可在非变体函数上定义函数索引。

添加修饰符

当您编写SPL函数时，您可使用 WITH子句来将修饰符添加到CREATE FUNCTION语 句。在 WITH子句中，您可指定COMMUTATOR或NEGATOR函数。其他修饰符是用 于外部例程的。

限制：您仅可以SPL函数使用COMMUTATOR或NEGATOR修饰符。您不可以SPL过 程使用任何修饰符。

COMMUTATOR 修饰符

COMMUTATOR修饰符允许您指定SPL函数为您正在创建的SPL函数的转换函数。转 换函数接受相同的参数作为您正在创建的SPL函数，但以相反的顺序，并返回相同值。对 于SQL优化器的执行，转换函数的成本效益比更高。

例如，如果a小于b,则函数lessthan(a,b)返回TRUE，而如果b大于或等于a，

则greaterthan(b,a)返回TRUE，二者是转换函数。下图使用WITH子句来定义转换函数。

图:定义转换函数。

CREATE FUNCTION lessthan( a dtypel, b dtype2 )

RETURNING BOOLEAN

WITH ( COMMUTATOR = greaterthan );

END FUNCTION;

如果greaterthan(b,a)的执行成本低于lessthan(a,b),则优化器可能使用greaterthan(b,a)。要 指定转换函数，您必须同时拥有该转换函数和您正在编写的SPL函数。您还必须将两个函 数的Execute权限授予您的SPL函数的用户。

要了解授予权限的详细描述，请参阅《GBase 8s SQL指南：语法》中的GRANT语句的 描述。

NEGATOR修饰符

NEGATOR修饰符是用于布尔函数的变量。如果两个布尔函数的参数相同，顺序相同，且 返回互补的布尔值，则它们是否定函数。

例如，如果a等于b，则函数equal(a,b)返回TRUE，而如果a等于b,则notequal(a,b)返 回FALSE，二者是否定函数。如果您指定的否定函数的执行成本低于原始的函数，则优化 器可能选择执行该否定函数。

下图展示如何使用CREATE FUNCTION语句的WITH子句来指定否定函数。

图:指定否定函数。

CREATE FUNCTION equal( a dtype1, b dtype2 )

RETURNING BOOLEAN

WITH ( NEGATOR = notequal );

END FUNCTION;

提示：在缺省情况下，任何SPL例程都可处理在参数列表中传给其的NULL值。换言之， 对于SPL例程，将HANDLESNULLS修饰符设置为YES,且您不可更改它的值。

要获取关于COMMUTATOR和NEGATOR修饰符的更多信息，请参阅《GBase 8s SQL 指南：语法》中的Routine Modifier段。

指定DOCUMENT子句

DOCUMENT 和 WITH LISTING IN 子句跟在 END PROCEDURE 或 END FUNCTION 语句之后。

DOCUMENT子句允许您将注释添加到您的SPL例程，另一例程可从系统目录表选择它， 如果需要的话。下图中的DOCUMENT子句包含展示用户如何运行该SPL例程的用法语 句。

图:展示用户如何运行SPL例程的用法语句。

CREATE FUNCTION raise_prices(per_cent INT)

END FUNCTION

DOCUMENT “USAGE: EXECUTE FUNCTION raise_prices (xxx)”, “xxx = percentage from 1 - 100”;

请记住在文字的子句两头放置单引号或双引号。如果文字的子句跨过一行，则在每一行的 两头放置引号。

指定清单文件

WITH LISTING IN选项允许您将任何可能发生的编译时警告定向到文件。

当您在UNIX™上工作时，下图展示如何将编译时警告记录在/tmp/warn_file中。

图:在UNIX上记录编译时警告。

CREATE FUNCTION raise_prices(per_cent INT)

END FUNCTION

WITH LISTING IN 7tmp/warn_file’

当您在 Windows™上工作时，下图展示如何将编译时警告记录在\tmp\listfile中。

图:在Windows上记录编译时警告

CREATE FUNCTION raise_prices(per_cent INT)

END FUNCTION

WITH LISTING IN ‘C:\tmp\listfile’

请始终记住在文件名称或路径名称两头放置单引号或双引号。

添加注释

您可将注释添加到SPL例程的任何行，即使是空行也行。

要添加注释，请使用任一下列注释说明类型：

在注释的左边放置双连字符(–)。
•将注释文本括在一对大括号之间({ … . })。

在C类型的“斜杠和星号”注释指示符之间定界注释(/* . . . */)。
要添加多行注释，请执行下列操作之一：

在每一行注释前放置双连字符
将全部注释括在一对大括号之内。
•在注释的第一行的左边放置/,并在注释的最后一行的末尾放置/。

以大括号作为注释指示符是GBase 8s对SQL语言的ANSI/ISO标准的扩展。在SPL例 程中，全部三种注释类型也是有效的。

如果您使用大括号或C类型注释指示符来定界注释的文本，则开头的指示符必须与结尾的 指示符是同一类型。

下图中的所有实例都是有效的注释。

图:有效的注释实例。

SELECT * FROM customer – Selects all columns and rows

SELECT * FROM customer

–Selects all columns and rows

–from the customer table

SELECT * FROM customer

( Selects all columns and rows from the customer table )

SELECT * FROM customer

/从customer表选择所有的列和行/

重要：大括号(( })可用于定界注释，也用于定界集合中元素的列表。要确保解析器正确地识 别注释的结束或集合中元素列表的结束，请在处理集合数据类型的SPL例程中为注释使用连字 符(–)。

1. 1. 2完整例程的示例

下列CREATE FUNCTION语句创建读取客户地址的例程：

CREATE FUNCTION read_address (lastname CHAR(15)) – one argument

RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2) CHAR(5); – 6 items

DEFINE p_lname,p_fname, p_city CHAR(15);

–define each routine variable

DEFINE p_add CHAR(20);

DEFINE p_state CHAR(2);

DEFINE p_zip CHAR(5);

SELECT fname, addressl, city, state, zipcode

INTO p_fname, p_add, p_city, p_state, p_zip

FROM customer

WHERE lname = lastname;

RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;

–6 items

END FUNCTION;

DOCUMENT ‘This routine takes the last name of a customer as’,

–brief description

‘its only argument. It returns the full name and address’,

‘of the customer.’

WITH LISTING IN ‘pathname’ – modify this pathname according

–to the conventions that your operating system requires

–compile-time warnings go here

;–end of the routine read_address

1. 1. 3在程序中创建SPL例程

要使用SQL API来创建SPL例程，请将CREATE PROCEDURE或CREATE FUNCTION 语句的文本放在文件中。请使用CREATE PROCEDURE FROM 或CREATE

FUNCTION FROM语句并引用那个文件来编译该例程。例如，要创建读取客户姓名的例 程，您可使用诸如在前面的示例中的一个语句，并将它存储在文件中。如果将该文件命名 为read_add_source,则下列语句编译read_address例程：

CREATE PROCEDURE FROM ‘read_add_source’;

下列示例展示在GBase 8s ESQL/C程序中，前面的SQL语句是怎样的：

/* This program creates whatever routine is in *

• the file ‘read_add_source’.

*/

#include <stdio.h>

EXEC SQL include sqlca;

EXEC SQL include sqlda;

EXEC SQL include datetime;

/* Program to create a routine from the pwd */

main()

{

EXEC SQL database play;

EXEC SQL create procedure from ‘read_add_source’; }

1. 1. 4在本地的或远程的数据库中删除例程

在您创建SPL例程之后，您不可更改该例程体。相反，您需要删除该例程并重新创建它。 然而，在您删除例程之前，请确保您在数据库之外的某个地方有它的文本的副本。

通常，请使用带有SPL过程名称的DROP PROCEDURE和带有SPL函数名称的DROP FUNCTION，如下图所示。

图:DROP PROCEDURE 和 DROP FUNCTION。

DROP PROCEDURE raise_prices;

DROP FUNCTION read_address;

提示：您还可使用带有函数名称的DROP PROCEDURE来删除SPL函数。然而，推荐您使 用仅带有过程名称的DROP PROCEDURE，以及仅带有函数名称的DROP FUNCTION „

如果数据库有同名称的其他例程(重载的例程)，则您不可只通过它的例程名称来删除SPL 例程。要删除已重载了的例程，您必须指定它的签名或它的特定的名称。下图展示您可能 删除重载了的例程的两种方式。

图:删除重载了的例程。

DROP FUNCTION calculate( a INT, b INT, c INT);

–this is a signature

DROP SPECIFIC FUNCTION calc1;

–this is a specific name

如果您不知道例程的类型(是函数还是过程)，则您可使用DROP ROUTINE语句来删除 它。DROP ROUTINE对函数或过程都有效。DROP ROUTINE还有SPECIFIC关键字， 如下图所示。

图:DROP ROUTINE 语句。

DROP ROUTINE calculate;

DROP SPECIFIC ROUTINE calc1;

在您删除存储在远程数据库服务器上的SPL例程之前，请注意下列限制。仅当只用例程名 称而不需它的参数，就足以标识该例程时，您才可以 database@dbservername:owner.routinename的形式使用完全限定的例程来删除SPL例程。 分布式操作中对数据类型的限制

如果SPL例程访问非本地的数据库服务器中的表，或调用SPL例程作为另一数据库服务 器的数据库的UDR,则该例程仅可有非opaque内建的数据类型作为它们的参数或返回的 值。

然而，如果表或UDR驻留在同一 GBase 8s示例的另一数据库上，贝。使用SPL （或GBase 8s支持的外部语言）编写的例程的参数和返回的值可为内建的opaque数据类型BLOB、 BOOLEAN、CLOB和LVARCHAR。如果下列条件为真，则它们还可为UDT或 DISTINCT数据类型：

•远程数据库与当前的数据库有相同的服务器。

•将UDT参数显式地强制转型为内建的数据类型。

• DISTINCT类型是基于内建的类型的，且被显式地强制转型为内建的类型。

•在所有参与的数据库中定义SPL例程和所有强制转型。

12.3定义和使用变量

您必须在例程体中定义在SPL例程中使用的任何变量,而不是在例程的参数列表中隐式地 定义的变量。

在内存中保持变量的值；该变量不是数据库对象。因此，回滚事务不恢复SPL变量的值。

要在SPL例程中定义变量，请使用DEFINE语句o DEFINE不是可执行语句。DEFINE必 须出现在CREATE PROCEDURE语句之后且任何其他语句之前。下图中的示例是所有合 法的变量定义。

图:量定^

DEFINE a INT;

DEFINE person person_t;

DEFINE GLOBAL gl_out INT DEFAULT 13;

要获取关于DEFINE的更多信息，请参阅《GBase 8s SQL指南：语法》中的描述。

SPL变量有名称和数据类型。变量名称必须是有效的标识符，如GBase 8s SQL指南：语 法中“标识符”段中描述的那样。

1. 1. 1声明本地变量

您可定义变量为作用域中的本地的或全局的。本部分描述本地变量。在SPL例程中，本地 变量：

•仅对于该SPL例程的持续时间是有效的

•每一次执行例程时，重置为它们的初始值或为用户传给该例程的值

•不可有缺省值

您可在任一下列数据类型上定义本地变量：

• 内建的数据类型(除了 SERIAL、SERIAL8、BIGSERIAL、TEXT 或 BYTE 之夕卜)

•在执行该SPL例程之前，在数据库中定义的任何扩展的数据类型(row类型、 opaque、distinct 或集合类型)

本地变量的作用域时在其中声明它的那个语句块。您可以不同的定义在该语句块之外使用 相同的变量名称。

要获取关于定义全局变量的更多信息，请参阅声明全局变量。

本地变量的作用域

在定义本地变量的那个语句块内以及任何嵌套的语句块内，它是有效的，除非您在该语句 块中重新定义该变量。

在系统中的SPL过程的开头，定义并初始化整数变量x、y和z。

图:定义和初始化变量。

CREATE PROCEDURE scope()

DEFINE x,y,z INT;

LET x = 5;

LET y = 10;

LET z = x + y; --z is 15

BEGIN

DEFINE x, q INT;

DEFINE z CHAR(5);

LET x = 100;

LET q = x + y; – q = 110

LET z = ‘silly’; – z receives a character value

END

LET y = x; – y is now 5

LET x = z; – z is now 15, not ‘silly’

END PROCEDURE;

BEGIN与END语句标记在其中定义整数变量x和q以及CHAR变量z的嵌套的语句 块。在嵌套的块内，重新定义的变量x掩盖原始的变量X。在标记该嵌套的块结束的END 语句之后，可再次访问x的原始值。

声明内建的数据类型的变量

声明为内建的SQL数据类型的变量可持有从那个内建的类型的列检索的值。您可将SPL 变量声明为大部分内建的类型，除了 BIGSERIAL、SERIAL和SERIAL8之外，如下图所 示。

图:内建的类型变量。

DEFINE x INT;

DEFINE y INT8;

DEFINE name CHAR（15）;

DEFINE this_day DATETIME YEAR TO DAY;

您可声明适当的整数数据类型（诸如BIGINT、INT或INT8）的SPL变量，来存储序列 列或序列对象的值。

声明智能大对象的变量

BLOB或CLOB对象（或包含智能大对象的数据类型）的变量不包含该对象本身，而是 指向该对象的指针。下图展示如何为BLOB和CLOB对象定义变量。

图:BLOB或CLOB对象的变量。

DEFINE a_blob BLOB;

DEFINE b_clob CLOB;

声明简单大对象的变量

简单大对象（TEXT或BYTE对象）的变量不包含该对象本身，而是执行该对象的指针。 当您对TEXT或BYTE数据类型定义变量时，您必须在数据类型之前使用关键字 REFERENCES，如下图所示。

图:在数据类型之前使用REFERENCES关键字。

DEFINE t REFERENCES TEXT;

DEFINE b REFERENCES BYTE;

声明集合变量

为了保持从数据库访存的集合，变量必须为类型SET、MULTISET或LIST。

重要：必须将集合变量定义为本地变量。您不可将集合变量定义为全局变量。

SET、MULTISET或LIST类型的变量是保存在DEFINE语句中命名的类型的集合的集合变 量。下图展示如何定义typed集合变量。

图:定^ typed集合变量。

DEFINE a SET ( INT NOT NULL );

DEFINE b MULTISET ( ROW ( bl INT,

b2 CHAR(50),

)NOT NULL );

DEFINE c LIST ( SET (DECIMAL NOT NULL) NOT NULL);

您必须始终将集合变量的元素定义为NOT NULL。在此示例中，定义变量a来保存非

NULL整数的SET；变量b保存非NULL row类型的MULTISET；变量c保存非NULL

十进制值的非NULL集合的LIST。

在变量定义中，您可在任何组合或深度中嵌套复合的类型，来与存储在您的数据库中的数 据类型相匹配。

您不可将一种类型的集合变量分配给另一类型的集合变量。例如，如果您将集合变量定义 为SET，则您不可将另一 MULTISET或LIST类型的集合变量分配给它。

声明row类型变量

Row类型变量保存命名的和未命名的row类型的数据。您可定义命名的row变量或未命

名的row变量。假设您定义如下图所示的命名的row类型。

图:命名的和末命名的row变量

CREATE ROW TYPE zip_t

(

z_code CHAR(5),

z_suffix CHAR(4)

);

CREATE ROW TYPE address_t

(

street

VARCHAR(20),

city

VARCHAR(20),

state

CHAR(2),

zip

zip_t

)；

CREATE ROW TYPE employee_t

(

name VARCHAR(30),

address address_t

salary INTEGER

);

CREATE TABLE employee OF TYPE employee_t;

如果您以命名的row类型的名称定义变量，则该变量仅可保存那种row类型的数据。在 下图中，person变量仅可保存employee_t类型的数据。

图:定^ person变量。

DEFINE person employee_t;

要定义保存在未命名的row类型中的数据的变量，请跟在row类型的字段之后使用 ROW关键字，如下图所示。

图:使用后跟row类型的字段的ROW关键字。

DEFINE manager ROW (name VARCHAR(30),

department VARCHAR(30), salary INTEGER );

由于仅对未命名的row类型的结构等价进行类型检查，因此，以未命名的row类型定义 的变量可保存任何未命名的row类型的数据，其有相同的字段数和相同的类型定义。因此, 变量manager可保存下图中任何row类型的数据。

图:未命名的row类型

ROW ( name VARCHAR(30),

department VARCHAR(30),

salary INTEGER );

ROW ( french VARCHAR(30),

spanish VARCHAR(30),

number INTEGER );

ROW ( title VARCHAR(30),

musician VARCHAR(30), price INTEGER );

重要：在您使用row类型变量之前，您必须使用LET语句或SELECTINTO语句来初始化该 row变量。

声明opaque类型和distinct类型变量

Opaque类型变量保存从opaque数据类型检索的数据。Distinct类型变量保存从distinct

数据类型检索的数据。如果您以opaque数据类型或distinct数据类型定义变量，则该变 量仅可保存那种类型的数据。

如果您定义名为point的opaque数据类型，和名为centerpoint的distinct数据类型，则您 可定义SPL变量来保存这两类数据，如下图所示。

图:定SPL变量来保存opaque和distinct数据类型。

DEFINE a point;

DEFINE b centerpoint;

变量a仅可保存类型point的数据，b仅可保存类型centerpoint的数据。

使用LIKE子句来声明列数据的变量

如果您使用LIKE子句，则数据库服务器定义有相同数据类型的变量作为表或视图中的列。 如果该列包含集合、row类型或嵌套的复合类型，则该变量具有在该列中定义的复合的或 嵌套的复合类型。

在下图中，变量loci定义image表中locations列的数据类型。

图:为image表中的locations列定义loci数据类型

DEFINE loci LIKE image.locations;

声明PROCEDURE类型变量

在SPL例程中，您可定义类型PROCEDURE的变量，并将现有的SPL例程或外部例程 的名称分配给该变量。定义PROCEDURE类型的变量指示该变量是对用户定义的例程的 调用，而不是对同一名称的内建例程的调用。

例如，下图中的语句定义length为一个SPL过程或SPL函数，不作为内建的LENGTH 函数。

图:定^ length作为SPL过程。

DEFINE length PROCEDURE;

LET x = length( a,b,c );

此定义在该语句块的作用域内禁用内建的LENGTH函数。如果您已以名称LENGTH创建 了 SPL或外部例程，则您可使用这样的定义。

由于GBase 8s支持例程重载，因此，您可以相同的名称定义多个SPL例程或外部例程。 如果您从SPL例程调用任何例程，则GBase 8s基于指定的参数和例程确定规则，确定使 用哪个例程。要获取关于例程重载和例程确定的信息，请参阅《GBase 8s用户定义的例程 和数据类型开发者指南》。

提示：如果您以相同的名称创建SPL例程作为聚集函数(SUM、MAX、MIN、AVG、COUNT) 或使用名称extend，则您必须以所有者名称来限定该例程。

带有变量的下标

您可随同 CHAR、VARCHAR、NCHAR、NVARCHAR、BYTE 或 TEXT 数据类型的变 量使用下标。下标指示您想要在变量内使用的起始的和终止的字符位置。

下标必须始终为常量。您不可使用变量作为下标。下图展示如何随同CHAR(15)变量使用 下标。

图:带有CHAR(15)变量的下标。

DEFINE name CHAR(15);

LET name[4,7] = ‘Ream’;

SELECT fname[1,3] INTO name[1,3] FROM customer WHERE lname = ‘Ream’;

在此示例中，将客户的姓置于name的位置4与7之间。将客户的名的前三个字符检索 到name的位置1至3内。由两个下标定界的变量的该部分称为子字符串。

变量与关键字歧义

如果您声明的变量的名字是SQL关键字，则可发生歧义。下列标识符的规则帮助您避免 SPL变量、SPL例程名称和内建的函数名称的歧义：

•定义了的变量优先级最高。

以DEFINE语句中的PROCEDURE关键字定义的例程优先于SQL函数。
SQL函数优先于那些存在但未以DEFINE语句中的PROCEDURE关键字标识 的SPL例程。
通常，请避免为变量的名称使用ANSI保留字。例如，您不可以名称count或max定义变 量，因为它们是聚集函数的名称。要了解您应避免用作变量名称的保留的关键字列表，请 参阅《GBase 8s SQL指南：语法》中的“标识符”段。

要获取关于SPL例程名称与SQL函数名称之间的歧义的信息，请参阅《GBase 8s SQL指 南：语法》。

变量和列名称 如果您为SPL变量使用一个您为列名称使用的同样的标识符,则数据库服务器假定该标识 符的每一实例都是变量。请以表名称限定列名称，使用点符号表示法，以便将标识符用作 列名称。

在下图中的SELECT语句中，customer.lname是列名称，lname是变量名称。

图:SELECT语句中的列名称和变量名称。

CREATE PROCEDURE table_test()

DEFINE lname CHAR(15);

LET lname = ‘Miller’;

SELECT customer.lname INTO lname FROM customer

WHERE customer_num = 502;

END PROCEDURE;

变量和SQL函数

如果您为SPL变量使用与为SQL函数一样的标识符，则数据库服务器假定该表达式的每 一实例都是变量，并不允许使用该SQL函数。在定义该变量的代码块内，您不可使用该 SQL函数。下图中的示例展示在其中定义名为user的变量的SPL过程内的块。此定义不 允许在BEGIN END 块中使用 USER函数。

图:不允许在BEGIN END块中使用USER函数的过程。

CREATE PROCEDURE user_test()

DEFINE name CHAR(10);

DEFINE name2 CHAR(10);

LET name = user; – the SQL function

BEGIN

DEFINE user CHAR(15); – disables user function

LET user = ‘Miller’;

LET name = user; – assigns ‘Miller’ to variable name

END

LET name2 = user; – SQL function again

1. 3.2声明全局变量

全局变量将它的值存储在内存中，其他SPL例程可用，由相同的用户会话运行在同一数据

库上。全局变量有下列特征：

•它需要缺省值。

•可在任何SPL例程中使用它，虽然必须在使用它的每一例程中定义它。

•它将它的值从一个SPL例程带到另一个，直到会话结束为止。

限制：您不可将集合变量定义为全局变量。

下图展示分享一个全局变量的两个SPL函数。

图:分享一个全局变量的两个SPL函数。

CREATE FUNCTION func1() RETURNING INT;

DEFINE GLOBAL gvar INT DEFAULT 2;

LET gvar = gvar + 1;

RETURN gvar;

END FUNCTION;

CREATE FUNCTION func2() RETURNING INT;

DEFINE GLOBAL gvar INT DEFAULT 5;

LET gvar = gvar + 1;

RETURN gvar;

END FUNCTION;

虽然您必须定义带有缺省值的全局变量，但仅在您首次使用它时，将变量设置为缺省值。 如果您以给定的顺序在下图中执行这两个函数，则gvar的值将为4。

图:全局变量缺省值。

EXECUTE FUNCTION func1();

EXECUTE FUNCTION func2();

但是，如果您以相反的顺序执行函数，如下图所示，则gvar的值将为7。

图:全局变量缺省值。

EXECUTE FUNCTION func2();

EXECUTE FUNCTION func1();

要了解更多信息，请参阅执行例程。

1. 1. 3赋值给变量

在SPL例程内，请使用LET语句将值分配给您已定义的变量。

如果您未赋值给变量，或通过传递给例程的参数，或通过LET语句，则该变量有未定义 的值。

未定义的值与NULL值不同。如果您尝试以SPL例程内未定义的值使用变量，则会收到 错误。

您可以下列任一方式赋值给例程变量：

使用LET语句。
使用SELECT INTO语句。
将CALL语句与带有RETURNING子句的过程一起使用。
使用 EXECUTE PROCEDURE INTO 或 EXECUTE FUNCTION INTO 语句。
LET语句

以LET语句，您可以等号(=)和有效的表达式或函数名称来使用一个或多个变量名称。 下图中的每一示例都是有效的LET语句。

图:有效的LET语句。

LET a = 5;

LET b = 6; LET c = 10;

LET a,b = 10,c+d;

LET a,b = (SELECT cola,colb

FROM tabl WHERE cola=10);

LET d = func1(x,y);

GBase 8s允许您将值分配给opaque类型变量、row类型变量，或row类型的字段。您还 可将外部函数或另一 SPL函数的值返回到SPL变量。

假设您定义命名的row类型zip_t和address_t，如图1所示。每当您定义row类型变量 时，您必须在可使用它之前初始化该变量。下图展示您可能如何定义和初始化row类型变 量。您可使用任何row类型值来初始化该变量。

图:定义和初始化row类型变量。

DEFINE a address」；

LET a = ROW (‘A Street’, ‘Nowhere’, ‘AA’,

ROW(NULL, NULL))::address_t

在您定义并初始化row类型变量之后，您可编写下图所示的LET语句。

图:编写LET语句

LET a.zip.z_code = 32601;

LET a.zip.z_suffix = 4555;

–Assign values to the fields of address_t 提示：请以variable.field or variable.field.field的形式使用点符号表示法来访问row类型的字 段，如处理row类型数据描述的那样。

假设您定义opaque-type point，其包含定义二维点的两个值，且该值的文本表示为’(x,y)‘o 您还可能有计算圆的周长的函数circum()，给定的点’(x,y)'和半径r。

如果您定义以一点为圆心的opaque类型center，以及计算圆的周长的函数circum()，基于 点和半径，您可为每一变量编写变量声明。在下图中，c是一个opaque类型变量，d保存 外部函数circum()返回的值。

图:编写变量声明。

DEFINE c point;

DEFINE r REAL;

DEFINE d REAL;

LET c = ‘(29.9,1.0)’;

–Assign a value to an opaque type variable

LET d = circum( c, r );

–Assign a value returned from circum()

GBase 8s SQL指南：语法详细地描述LET语句的语法。

赋值给变量的其他方式

您可使用SELECT语句来从数据库访存一个值，并直接地将它分配给变量，如下图所示。

图:从瓣库访存一个值,并直接地将它分配给变量。

SELECT fname, lname INTO a, b FROM customer

WHERE customer_num = 101

请使用CALL或EXECUTE PROCEDURE语句来将由SPL函数或外部函数返回的值分 配给一个或多个SPL变量。您可能使用下图中的一个语句来将来自SPL函 数read_address的全名和地址返回到指定的SPL变量内。

图:返回来自SPL函数的全名和地址。

EXECUTE FUNCTION read_address(‘Smith’)

INTO p_fname, p_lname, p_add, p_city, p_state,

p_zip;

CALL read_address(‘Smith’)

RETURNING p_fname, p_lname, p_add, p_city, p_state, p_zip;

12.4 SPL例程中的表达式

您可在SPL例程中使用任何SQL表达式，除了聚集表达式以外。GBase 8s SQL指南： 语法提供SQL表达式的完整语法与描述。

下列示例包含SQL表达式：

varl

varl + var2 + 5

read_address(‘Miller’) read_address(lastname = ‘Miller’) get_duedate(acct_num) + 10 UNITS DAY

fname[1,5] || ‘’|| lname ‘(415)’ || get_phonenum(cust_name)

12.5编写语句块

每个SPL例程至少有一个语句块，它是在CREATE语句与END语句之间的一组SQL 和SPL语句。您可在语句块内使用任何SPL语句或任何允许的SQL语句。要了解在 SPL语句块内不允许使用的SQL语句的列表，请参阅《GBase 8s SQL指南：语法》中 语句块段的描述。

1. 1. 1隐式的和显式的语句块

在SPL例程中，隐式的语句块从CREATE语句的结尾扩展到END语句的开头。您还可 定义显式的语句块，它以BEGIN语句开头并以END语句结尾，如下图所示。

图:显式的语句块。

BEGIN

DEFINE distance INT;

LET distance = 2;

END

显式的语句块允许您定义仅在语句块内有效的变量或处理。例如，您可定义或重新定义变 量，或以不同的方式处理异常，仅对于显式的语句块的作用域。

下图中的SPL函数有一个显式的语句块，它重新定义在隐式的块中定义的变量。

图:重新定义在隐式的块中定义的变量的显式的语句块。

CREATE FUNCTION block_demo()

RETURNING INT;

DEFINE distance INT;

LET distance = 37;

BEGIN

DEFINE distance INT;

LET distance = 2;

END

RETURN distance;

END FUNCTION;

在此示例中，隐式的语句块定义变量distance并赋值37。显式的语句块定义名为distance的 不同的变量，并赋值2。然而,RETURN语句返回存储在第一个distance变量中的值，即37。

1. 1. 2 FOREACH 循环

FOREACH循环定义游标，指向一组中的一项的特定的=标识符，或为一组行，或为集合中 的元素。

FOREACH循环声明并打开游标，从数据库访存行，处理该组中的每一项，然后关闭游标。 如果 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句可能返回多行， 则您必须声明游标。在您声明游标之后，将SELECT、EXECUTE PROCEDURE或 EXECUTE FUNCTION 语句放置在其内。

返回一组行的SPL例程称为游标例程，因为您必须使用游标来访问它返回的数据。不返回 值、返回单个值或任何其他值的SPL例程不需要游标，称为无游标例程。FOREACH循 环声明并打开游标，从数据库访存行或集合，处理该组中的每一项，然后关闭游标。如果 SELECT、EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句可能返回多个行或集 合，则您必须声明游标。在您声明游标之后，请将SELECT、EXECUTE PROCEDURE或 EXECUTE FUNCTION 语句放置其内。

在FOREACH循环中，您可使用EXECUTE FUNCTION或SELECT INTO语句来执行 为迭代函数的外部函数。

1. 5.3 FOREACH循环定义游标

FOREACH循环以FOREACH关键字开始，并以END FOREACH结束。在FOREACH 与END FOREACH之间，您可声明游标或使用EXECUTE PROCEDURE或EXECUTE FUNCTIONo下图中的两个示例展示FOREACH循环的结构。

图:FOREACH循环的结构。

FOREACH cursor FOR

SELECT column INTO variable FROM table

END FOREACH;

FOREACH

EXECUTE FUNCTION name() INTO variable; END FOREACH;

下图创建使用FOREACH循环的例程来在employee表上操作。

图：对employee表操作的FROEACH循环。

CREATE_PROCEDURE increase_by_pct( pct INTEGER )

DEFINE s INTEGER;

FOREACH sal_cursor FOR

SELECT salary INTO s FROM employee

WHERE salary > 35000

LET s = s + s * ( pct/100 );

UPDATE employee SET salary = s

WHERE CURRENT OF sal_cursor;

END FOREACH;

END PROCEDURE;

前图中的例程执行FOREACH内的这些任务：

声明游标
一次从 employee 表选择一个 salary 值
按百分率提高salary
以新的salary更新employee
访存下一个salary值
将SELECT语句放置在游标内，因为它返回表中所有大于35000的薪酬。

UPDATE语句中的WHERE CURRENT OF子句仅更新该游标当前定位在其上的行，并在 当前行上设置更新游标。更新游标在该行上放置更新锁，以便于其他用户不可更新该行， 直到您的更新发生为止。

如果FOREACH 循环内的 UPDATE或 DELETE语句使用 WHERE CURRENT OF子 句，贝U SPL例程将自动地设置更新游标。如果您使用WHERE CURRENT OF，则必须显 式地引用FOREACH语句内的游标。如果您正在使用更新游标，则可在FOREACH语句 之前添加BEGIN WORK语句，并在END FOREACH之后添加COMMIT WORK语句， 如下图所示。

图:自动地设置更新游标。

BEGIN WORK;

FOREACH sal_cursor FOR

SELECT salary INTO s FROM employee WHERE salary > 35000;

LET s = s + s * ( pct/100 );

UPDATE employee SET salary = s WHERE CURRENT OF sal_cursor END FOREACH;

COMMIT WORK;

对于前图中FOREACH循环的每一迭代，需要新锁(如果您使用行级别锁定的话)。在 FOREACH循环的最后迭代之后，COMMIT WORK语句释放所有的锁(并将所有更新了 的行作为单个事务提交)。

要在循环的每一迭代之后提交更新了的行，您必须打开游标WITH HOLD,并在 FOREACH循环内包括BEGIN WORK和COMMIT WORK语句，如下列SPL例程那 样。

图:在循环的每一迭代之后提交更新了的行。

CREATE PROCEDURE serial_update();

DEFINE p_col2 INT;

DEFINE i INT;

LET i = 1;

FOREACH cur_su WITH HOLD FOR

SELECT col2 INTO p_col2 FROM customer WHERE 1=1

BEGIN WORK;

UPDATE customer SET customer_num = p_col2 WHERE CURRENT

OF cur_su;

COMMIT WORK;

LET i = i + 1;

END FOREACH;

END PROCEDURE;

SPL例程serial_update()提交每一行作为分开的事务。

对FOREACH循环的限制

在FOREACH循环内，SELECT查询必须在更改该SELECT游标的数据集的任何 DELETE、INSERT或UPDATE操作之前执行完成。确保SELECT查询完成的一种方式 是，在SELECT语句中使用ORDER BY子句。ORDER BY子句在该列上创建索引，并 通过在同一 FOREACH循环中更改SELECT语句的查询结果的UPDATE、INSERT、 DELETE语句来防止导致的错误。

1. 1. 4 IF - ELIF - ELSE 结构

下列SPL例程使用IF - ELIF - ELSE结构来比较该例程接受的两个参数。

图:比较两个参数的IF- ELIF- ELSE结构

CREATE FUNCTION str_compare( strl CHAR(20), str2 CHAR(20))

RETURNING INTEGER;

DEFINE result INTEGER;

IF str1 > st” THEN

LET result = 1;

ELIF st” > str1 THEN

LET result = -1;

ELSE

LET result = 0;

END IF

RETURN result;

END FUNCTION;

假设您以下图所示的列定义名为manager的表。

图:定^ manager 表。

CREATE TABLE manager

(

mgr_name VARCHAR(30), department VARCHAR(12), dept_no SMALLINT, direct_reports SET( VARCHAR(30) NOT NULL ), projects LIST( ROW ( pro_name VARCHAR(15), pro_members SET( VARCHAR(20) NOT NULL )) NOT NULL),

salary INTEGER,

);

下列SPL例程使用IF - ELIF - ELSE结构来检查direct_reports列中SET中元素的数目， 并基于该结果来调用不同的外部例程。

图:检查SET中元素数的F - ELIF- ELSE结构

CREATE FUNCTION checklist( d SMALLINT )

RETURNING VARCHAR(30), VARCHAR(12), INTEGER;

DEFINE name VARCHAR(30);

DEFINE dept VARCHAR(12);

DEFINE num INTEGER;

SELECT mgr_name, department,

CARDINALITY(direct_reports)

FROM manager INTO name, dept, num

WHERE dept_no = d;

IF num > 20 THEN

EXECUTE FUNCTION add_mgr(dept);

ELIF num = 0 THEN

EXECUTE FUNCTION del_mgr(dept);

ELSE

RETURN name, dept, num;

END IF;

END FUNCTION;

cardinality。函数计数集合包含的元素数。要获取更多信息，请参阅基数函数。

SPL例程中的IF - ELIF - ELSE结构有至多下列四个部分：

IF THEN 条件
如果跟在IF语句之后的该条件为TRUE，则例程执行IF块中的语句。如果该条 件为假，则例程对ELIF条件求值。

IF语句中的表达式可为任何有效的条件，如GBase 8s SQL指南：语法的Condition 段描述的那样。要了解IF语句的完整语法和详细的讨论,请参阅《GBase 8s SQL指 南：语法》。

一个或多个ELIF条件(可选的)
仅当IF条件为假时，例程才对ELIF条件求值。如果ELIF条件为真，则例程执 行ELIF块中的语句。如果ELIF条件为假，则例程或对下一个ELIF块求值，或 执行ELSE语句。

ELSE条件(可选的)
如果IF条件和所有ELIF条件都为假，则例程执行ELSE块中的语句。

END IF 语句
END IF语句结束该语句块。

1. 5.5添加 WHILE和FOR循环

WHILE与FOR语句都可在SPL例程中创建执行循环。WHILE循环以

WHILE condition开始，只要条件为真就执行语句块，并以END WHILE结束。

下图展示有效的WHILE条件。只要在WHILE语句中指定的条件为真，例程就执行 WHILE循环。

图:只要在WHILE语句中指定的条件为真,例程就执行WHILE循环。

CREATE PROCEDURE test_rows( num INT )

DEFINE i INTEGER;

LET i = 1;

WHILE i < num

INSERT INTO table1 (numbers) VALUES (i);

LET i = i + 1;

END WHILE;

END PROCEDURE;

前图中的SPL例程接受整数作为参数，然后在它每一次执行WHILE循环时，就将整数 值插入到tablel的numbers列内。插入的值从1开始，且增大到num - 1。

请当心，不要创建无限的循环，如下图所示。

图:接受整数为参数,然后将整数值插入到numbers列的例程。

CREATE PROCEDURE endless_loop()

DEFINE i INTEGER;

LET i = 1;

WHILE ( 1 = 1 ) – don’t do this!

LET i = i + 1;

INSERT INTO table1 VALUES (i);

END WHILE;

END PROCEDURE;

FOR循环从FOR语句扩展到END FOR语句，并执行在FOR语句中定义的指定次数的 迭代。下图展示在FOR循环中定义迭代的几种方式。

对于FOR循环的每一迭代，重置迭代变量(在后面的示例中声明为1)，并以该变量的新 值执行该循环内的语句。

图:定^ FOR循环中的迭代。

FOR i = 1 TO 10

END FOR;

FOR i = 1 TO 10 STEP 2

END FOR;

FOR i IN (2,4,8,14,22,32)

END FOR;

FOR i IN (1 TO 20 STEP 5, 20 to 1 STEP -5, 1,2,3,4,5)

END FOR:

在第一个示例中，只要1介于1之间10,包括1与10,该SPL过程就执行FOR循环。 在第二个示例中，1从1到3、5、7,等等递进，但从不超过10。第三个示例检查1是否在 定义了的值集之内。在第四个示例中，当i为1、6、11、16、20、15、10、5、1、2、3、4或5时， 该SPL过程执行循环一一换言之，执行循环13次。

提示：WHILE循环与FOR循环之间的主要差异是，FOR循环保证会结束，但WHILE循环 不然。FOR语句指定循环执行的确切次数，除非语句导致例程退出该循环。使用WHILE，可 能创建无限的循环。

1. 1. 6退出循环

在没有标签的FOR、FOREACH、LOOP或 WHILE循环中，您可使用CONTINUE或 EXIT语句来控制循环的执行。

CONTINUE导致例程跳过该循环的剩余语句，并移至FOR、LOOP或 WHILE语 句的下一迭代。
EXIT终止该循环，并导致例程继续执行跟在END FOR、END LOOP或END WHILE关键字之后的第一个语句。
请记住，当EXIT出现在为嵌套循环语句的最内层循环的FOREACH语句内时，它必须 后跟FOREACH关键字。当EXIT出现在在FOR、LOOP或 WHILE语句内时，它可不 紧跟关键字出现，但如果您指定一个关键字，该关键字与从其发出了 EXIT语句的循环语 句不相匹配，则发出错误。如果EXIT出现在循环语句的上下文之外，则也发出错误。 要获取关于SPL例程中的循环的更多信息，包括带标签的循环，请参阅《GBase 8s SQL指 南：语法》。

下图展示在FOR循环内的CONTINUE和EXIT的示例。

图:FOR循环内的CONTINUE和EXIT的示例。

FORi = 1 TO 10

IF i = 5 THEN

CONTINUE FOR;

ELIF i = 8 THEN

EXIT FOR;

END IF;

END FOR;

提示：您可使用CONTINUE和EXIT来提升SPL例程的性能，以免执行不必要的循环。

12.6从SPL函数返回值

SPL函数可返回一个或多个值。要是您的SPL函数返回值，需要包括下列两个部分：

在指定要返回的值的数目及其数据类型的CREATE PROCEDURE或CREATE FUNCTION 语句中编写RETURNING 子句。
在函数体内，输入显式地返回值的RETURN语句。
提示：您可以返回值的CREATE PROCEDURE语句来定义例程，但在那种情况下，该例程实 际上是函数。但例程返回值时，推荐您使用CREATE FUNCTION语句。

在您（以RETURNING语句）定义返回子句之后，SPL函数可返回那些与指定的数目和 数据类型相匹配的值，或根本不返回值。如果您指定返回子句，且SPL例程未返回实际 的值，则仍将它视为函数。在那种情况下，例程为在返回子句中定义的每一值都返回一个 NULL 值。

SPL函数可返回变量、表达式，或另一函数调用的结果。如果SPL函数返回变量，则该 函数必须首先通过下列方法之一赋值给该变量：

LET语句
•缺省值

SELECT 语句
•将值传至该变量内的另一函数

SPL函数返回的每一值最长可为32 KB。

重要：SPL函数的返回值必须为特定的数据类型。您不可指定类属行或类属集合数据类型作为 返回类型。

1. 1. 1返回单个值

下图展示SPL函数可如何返回单个值。

图:返回单个值的SPL函数。

CREATE FUNCTION increase_by_pct(amt DECIMAL, pct DECIMAL)

RETURNING DECIMAL;

DEFINE result DECIMAL;

LET result = amt + amt * (pct/100);

RETURN result;

END FUNCTION;

increase_by_pct函数收到两个DECIMAL值的参数，一个为要增加的数量，一个为要增加 的百分比。指定该函数的返回子句将返回一个DECIMAL值。RETURN语句返回存储 在 result 中的 DECIMAL 值。

1. 6.2返回多个值

SPL函数可从表的单个行返回多个值。下图展示从表的单个行返回两个列值的SPL函数。

图:从表的单个行返回两个列值的SPL函数。

CREATE FUNCTION birth_date( num INTEGER )

RETURNING VARCHAR(30), DATE;

DEFINE n VARCHAR(30);

DEFINE b DATE;

SELECT name, bdate INTO n, b FROM emp_tab

WHERE emp_no = num;

RETURN n, b;

END FUNCTION;

该函数从emp_tab表的一行将两个值(名和生日)返回给调用的例程。在此情况下，必须 准备调用的例程来处理返回的VARCHAR和DATE值。

下图展示从多行返回多个值的SPL函数。

图:从多行返回多个值的SPL函数。

CREATE FUNCTION birth_date_2( num INTEGER )

RETURNING VARCHAR(30), DATE;

DEFINE n VARCHAR(30);

DEFINE b DATE;

FOREACH cursor1 FOR

SELECT name, bdate INTO n, b FROM emp_tab

WHERE emp_no > num

RETURN n, b WITH RESUME;

END FOREACH;

END FUNCTION;

在前图中，SELECT语句从其员工编号大于用户输入的编号的行集访存两个值。满足该条 件的行集可能包含一行、多行，或零行。由于SELECT语句可返回多行，因此将它放置在 游标内。

提示：当SPL例程内的语句未返回行时，为对应的SPL变量赋值NULL。

RETURN 语句使用 WITH RESUME关键字。当执行RETURN WITH RESUME时，将控 制返回到调用的例程。但在下一次(通过FETCH或通过调用的例程中的游标的下一迭代) 调用该SPL函数时，SPL函数中的所有变量保持它们的相同的值，并从紧跟在RETURN WITH RESUME语句之后的语句继续执行。

如果您的SPL例程返回多个值，则调用的例程必须能够通过游标或循环来处理多个值，如 下：

如果调用的例程为SPL例程，则它需要FOREACH循环。
如果调用的例程为GBase 8s ESQL/C程序，则它需要以DECLARE语句声明的游 标。
•如果调用的例程为外部的例程，则它需要与编写该例程的语言相适应的游标或循 环。

重要：由UDR从本地服务器的外部数据库返回的值必须为内建的数据类型，或UDT显式地 强制转型为内建的类型，或基于内建的类型的DISTINCT类型并显式地强制转型为内建的类 型。此外，您必须定义UDR和参与的数据库中的所有强制转型。

下列是您可跨数据库执行的SQL操作的示例：

database db1;

create table ltab1(lcol1 integer, lcol2 boolean, lcol3 Ivarchar);

insert into ltabl values(1, ‘t’, “test string 1”);

database db2;

create table rtab1(r1col1 boolean, r1col2 blob, r1col3 integer) put r1col2 in (sbsp);

create table rtab2(r2col1 lvarchar, r2col2 clob) put r2col2 in (sbsp); create table rtab3(r3col1 integer, r3col2 boolean,

r3col3 lvarchar, r3col4 circle);

create view rvw1 as select * from rtab3;

(该示例为跨数据库Insert o)

database db1;

create view lvw1 as select * from db2:rtab2;

insert into db2:rtab1 values(‘t’, filetoblob(‘blobfile’, ‘client’, ‘db2:rtab1’, ‘r1col2’), 100);

insert into db2:rtab2 values(“inserted directly to rtab2”, filetoclob(‘clobfile’, ‘client’, ‘db2:rtab2’, ‘”col2’));

insert into db2:rtab3 (r3col1, r3col2, r3col3) select lcol1, lcol2, lcol3 from ltab1;

insert into db2:rvw1 values(200, ‘f’, “inserted via rvw1”); insert into lvw1 values (“inserted via lvw1”, NULL);

12.7处理row类型数据

在SPL例程中，您可使用命名的ROW类型和未命名的ROW类型作为参数定义、参数、 变量定义和返回值。要获取关于如何在SPL中声明ROW变量的信息，请参阅声明row 类型变量。

下图定义row类型salary_t和emp_info表，它们是本部分使用的示例。

图:定^ row 类型 salary」和 emp_info 表

CREATE ROW TYPE salary_t(base MONEY(9,2), bonus MONEY(9,2))

CREATE TABLE emp_info (emp_name VARCHAR(30), salary salary_t); emp_info表有员工姓名和薪酬信息的列。

1. 1. 1点符号表示法的优先顺序

以GBase 8s, SPL例程中SQL语句中使用点符号表示法(如在proj.name)被解释为有 三种含义之一，优先顺序如下：

variable.field
column.field
table.column
换言之，首先将表达式proj.name求值为variable.field。如果例程未找到变量proj，则它 将该表达式求值为column.field。如果例程未找到列proj，则它将该表达式求值为 table.column。(如果不可将名称解析为数据库中对象的标识符，或在SPL例程中声明了 的变量或字段，则返回错误。)

1. 1. 2更新row类型表达式

从SPL例程内，您可使用ROW变量来更新row类型表达式。下图展示当员工的基本薪 酬按某一百分比增长时，用于更新emp_info表的SPL过程emp_raise。

图:用于更新emp_info表的SPL过程。

CREATE PROCEDURE emp_raise( name VARCHAR(30),

pct DECIMAL"))

DEFINE row_var salary_t;

SELECT salary INTO row_var FROM emp_info

WHERE emp_name = name;

LET row_var.base = row_var.base * pct;

UPDATE emp_info SET salary = row_var

WHERE emp_name = name;

END PROCEDURE;

SELECT语句将来自emp_info表的salary列的行选择到ROW 变量row_var内。

emp_raise过程使用SPL点符号表示法来直接地访问变量row_var的base字段。在此情况 下，点符号表示法意味着variable.field。emp_raise过程重新计算row_var.base的值作 为(row_var.base * pct)。然后，该过程以新的row_var值来更新emp_info表的salary列。

重要：在可设置或引用row类型变量字段之前，必须将它初始化为行。您可以SELECT INTO 语句或LET语句初始化row类型变量。

12.8处理集合

集合是同一数据类型的一组元素，诸如SET、MULTISET或LIST。

表可能包含集合，存储集合作为列的内容，或作为列内ROW类型的字段。集合可为简单 的或嵌套的。简单的集合是内建的、opaque或distinct数据类型的SET、MULTISET或 LIST。嵌套的集合是包含其他集合的集合。

1. 1. 1集合数据类型

本章节的下列部分凭借几个不同的示例来展示您可如何在SPL程序中操纵集合。

在SPL程序中处理集合的基本内容是使用numbers表来说明的，如下图所示。

图:在SPL程序中处理集合。

CREATE TABLE numbers

(

id INTEGER PRIMARY KEY,

primes SET( INTEGER NOT NULL ),

evens LIST( INTEGER NOT NULL ),

twin_primes LIST( SET( INTEGER NOT NULL ) NOT NULL )

primes和evens列保存简单的集合。twin_primes列保存嵌套的集合，SET的LIST。(双 素数是一对相差2的连续素数，诸如5和7，或11和13。)设计twin_primes列以允 许您输入这样的值对。

本章节中的一些示例使用下图中的polygons表，来说明如何操纵集合。polygons表包含集 合来表示两维图形数据。例如，假设您定义名为point的opaque数据类型，其有表示两维 点的x和y坐标的两个双精度值，其坐标可能表示为’1.0, 3.0’。使用point数据类型，您可 创建包含一系列定义多边形的点的表。

图:操纵合。

CREATE OPAQUE TYPE point ( INTERNALLENGTH = 8);

CREATE TABLE polygons

(

id INTEGER PRIMARY KEY,

definition SET( point NOT NULL )

);

polygons表中的definition列包含简单的集合，point值的SET。

1. 1. 2准备集合数据类型

在您可访问和处理简单的或嵌套的集合的个别元素之前，您必须执行下列任务：

•声明集合变量来保存该集合。

•声明元素变量来保存集合的个别元素。

•将集合从数据库选择至集合变量内。

在您做了这些初始的步骤之后，您可将元素插入到集合内，或选择或处理已在集合中的元 素。

在下列部分中，使用numbers表为示例，说明每一步骤。

提示：您可在任何SPL例程中处理集合。

声明集合变量

在您可从数据库将集合检索至SPL例程内之前，您必须声明集合变量。下图展示如何声明 集合变量来从numbers表检索primes列。

图:声明集合变量。

DEFINE p_coll SET( INTEGER NOT NULL );

DEFINE语句声明集合变量p_coll，其类型与存储在primes列中的集合的数据类型相匹 配。

声明元素变量

在您声明集合变量之后，请声明元素变量来保存该集合的个别元素。元素变量的数据类型 必须与集合元素的数据类型相匹配。

例如，要保存primes列中SET的元素，请使用诸如下图所示的一种元素变量声明。

图:元素变量声明。

DEFINE p INTEGER;

要声明保存 twin_primes 列的元素的变量，其保存嵌套的集合，请使用诸如下图所示的一种 变量声明。

图:变量声明。

DEFINE s SET( INTEGER NOT NULL );

变量s保存整数的SET。每一 SET是存储在twin_primes中的LIST的一个元素。

将集合选择至集合变量内 在您声明集合变量之后，您可将集合访存至它内。要将集合访存至集合变量内，请输入 SELECT INTO语句，该语句从数据库将集合列选择至您已命名了的集合变量内。

例如，要选择保存在numbers的primes列的一行中的集合，请添加SELECT语句至您的 SPL例程，诸如下图展示的一个。

图:添加SELECT语句来选择存储在一行中的集合。

SELECT primes INTO p_coll FROM numbers

WHERE id = 220;

SELECT语句中的WHERE子句指定您想要选择只存储在numbers的一行中的集合。该语 句将集合放置到集合变量p_coll内，图1声明它。

现在，变量p_coll保存来自primes列的集合，它会包含值SET {5,7,31,19,13}。

1. 1. 3将元素插入至集合变量内

在您将集合检索至集合变量内之后，您可将值插入至该集合变量。INSERT语句的语法略 有不同，这依赖于您想要条件到的集合的类型。

插入至SET或MULTISET内

要插入至存储在集合变量中的SET或MULTISET内，请使用INSERT语句，并跟在带 有集合变量的TABLE关键字之后，如下图所示。

图:插入至存储在集合变量中的SET或MULTISET内。

INSERT INTO TABLE(p_coll) VALUES(3);

TABLE关键字使得集合变量成为集合派生的表。在处理SELECT语句中的集合部分中 描述集合派生的表。前图派生的集合是一列的虚拟表，集合的每一元素表示表的一行。在 插入之前，请将p_coll考虑作为包含下图展示的行(元素)的虚拟表。

图:虚拟表元素。

5

7

31

19

13

在插入之后，p_coll可能看上去像下图所示的虚拟表一样。

图:虚拟表元素。

5

7

31

19

13

3

由于该集合为SET,因此，将新的值添加到该集合，但未定义新元素的位置。对于 MULTISET，适用同样的原理。

提示：您一次仅可将一个值插入至简单的集合内。

插入至LIST内

如果集合为LIST,则您可将新的元素添加在LIST中的特定点，或添加在LIST的末尾。 如同SET或MULTISET 一样，您必须首先定义集合变量，并从数据库将集合选择至该集 合变量内。

下图展示您需要定义集合变量并从numbers表选择LIST至该集合变量内的语句。

图:定义集合变量并选择LIST

DEFINE e_coll LIST(INTEGER NOT NULL);

SELECT evens INTO e_coll FROM numbers

WHERE id = 99;

此时，e_coll的值可能为LIST ｛2,4,6,8,10｝。由于e_coll保存LIST，因此，每一元素有在 该列表中的编号的位置。要将元素添加在LIST中特定的位置，请将AT position子句添加 到INSERT语句，如下图所示。

图:将元素添加在LIST中特定的点。

INSERT AT 3 INTO TABLE(e_coll) VALUES(12);

现在，e_coll中的LIST有元素｛2,4,12,6,8,10｝，依此顺序。

您在AT子句中为position输入的值可为数值或变量，但它必须有INTEGER或 SMALLINT数据类型。您不可使用字母、浮点数值、十进制值或表达式。

检査LIST集合的基数

有时，您可能想要将元素添加在LIST的末尾。在此情况下，您可使用cardinality。函数来 找到LIST中的元素的编号，然后输入一个大于cardinality()返回的值的位置。

GBase 8s允许您随同存储在列中的集合来使用cardinality。函数，但不允许随同存储在集合 变量中的集合来使用。在SPL例程中，您可以SELECT语句检查列中的集合的基数，并 将该值返回给变量。

假设在numbers表中，其id列为99的那一行的evens列仍然包含集合LIST {2,4,6,8,10}。 这一次，您想要将元素12添加在该LIST的末尾。您可使用SPL过程end_of_list实现， 如下图所示。

图:end_of_listSPL 过程。

CREATE PROCEDURE end_of_list()

DEFINE n SMALLINT;

DEFINE list_var LIST(INTEGER NOT NULL);

SELECT CARDINALITY(evens) FROM numbers INTO n

WHERE id = 100;

LET n = n + 1;

SELECT evens INTO list_var FROM numbers

WHERE id = 100;

INSERT AT n INTO TABLE(list_var) VALUES(12);

END PROCEDURE;

在end_of_list中，变量n保存cardinality。返回的值，即，LIST中的项数。LET语句使n递 增，以便于INSERT语句可在LIST的最后的位置插入值。SELECT语句将来自该表的一 行的集合选择至集合变量list_var内。INSERT语句将元素12插入在该列表的结尾。

VALUES子句的语法

当您插入至SPL集合变量内时，VALUES子句的语法与当您插入至集合列内时是不一样 的。将文字插入至集合变量内的语法规则如下：

在VALUES关键字之后使用圆括号来括起值的完整列表。

如果您正在插入至简单的集合内，则无需使用类型构造函数或方括号。

如果您正在插入至嵌套的集合，则您需要指定文字的集合。

1. 8.4从集合选择元素

假设您想要您的SPL例程从存储在集合变量内的集合选择元素，则请一次选择一个，以便 于您可处理这些元素。

要在集合的元素间移动，您首先需要使用FOREACH语句来声明游标，就如同您会声明游 标来在一组行间一同一样。下图展示FOREACH和END FOREACH语句，在它们之间还 没有语句。

图：FOREACH 和 END FOREACH 语句。

FOREACH cursorl FOR

END FOREACH

在FOREACH循环和GBase 8s SQL指南：语法中描述FOREACH语句。

下一主题，集合查询，描述在FOREACH与END FOREACH语句之间省略的那些语句。 下列部分中的示例是基于图2的polygons表的。

集合査询

在您在FOREACH与END FOREACH语句之间声明游标之后，请您输入称为集合查询的 特殊的、受限形式的SELECT语句。

集合查询是使用后跟集合变量的名称的FROM TABLE关键字的SELECT语句。下图展 示此结构，称其为集合派生的表。

图:集合派生的表。

FOREACH cursorl FOR

SELECT * INTO pnt FROM TABLE(vertexes)

END FOREACH

该SELECT语句使用集合变量vertexes作为集合派生的表。您可将集合派生的表视为一列 的表，该集合的每一元素都是表的一行。例如，您可将存储在vertexes中的四个点的SET可 视化作为带有四行的表，诸如下图展示的一个。

图:带有四 行的表。

‘(3.0,1.0)’

‘(8.0,1.0)’

‘(3.0,4.0)’

‘(8.0,4.0)’

在前图中的FOREACH语句的第一个迭代之后，该集合查询选择vertexes中的第一个元 素，并将其存储在pnt中，因此，pnt包含值’(3.0,1.0)’。

提示：由于集合变量vertexes包含SET,而不是LIST,因此，vertexes中的元素没有定义了的 顺序。在真实的数据库中，值’(3.0,1.0)'可能不是SET中的第一个元素。

将集合査询添加至SPL例程

现在，您可将以FOREACH定义的游标和集合查询添加至SPL例程，如下例所示。

图:以FOREACH定义的游标和集合查询。

CREATE PROCEDURE shapes()

DEFINE vertexes SET( point NOT NULL );

DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons

WHERE id = 207;

FOREACH cursor1 FOR

SELECT * INTO pnt FROM TABLE(vertexes)

END FOREACH

END PROCEDURE;

以上展示的语句形成处理集合变量的元素的SPL例程的框架。要将集合分解为它的元素， 请使用集合派生的表。在将集合分解为它的元素之后，该例程可单独地访问元素作为集合 派生的表的行。既然您已选择了 pnt中的一个元素，您就可更新或删除那个元素，如更新 集合元素和删除集合元素描述的那样。

要了解集合查询的完整语法，请参阅《GBase 8s SQL指南：语法》中的SELECT语句。 要了解集合派生的表的语法，请参阅《GBase 8s SQL指南：语法》中的“集合派生的表” 段。

提示：如果您正在从不包含元素或包含零元素的集合选择，则您可使用未声明游标的集合查询。 然而，如果该集合包含多个元素，且您未使用游标，则您会收到错误消息。

注意：在上述程序段中，如果FOREACH游标定义内的查询(

SELECT * INTO pnt FROM TABLE(vertexes)

)已以分号(;)作为语句终止符终止了，则数据库服务器可能已发出了语法错误。在此， END FOREACH关键字是逻辑的语句终止符。

1. 1. 5删除集合元素

在您将个别的元素从集合变量选择至元素变量内之后，您可从集合删除该元素。例如，在 您以集合查询从集合变量vertexes选择一个点之后，您可将该点从集合移除。

删除集合元素涉及的步骤包括：

声明集合变量和元素变量。
将集合从数据库选择至集合变量内。
声明游标，以便于您可从集合变量一次选择一个元素。
编写定位您想要删除的元素的循环或分支。
使用DELETE WHERE CURRENT OF语句来从集合删除元素，该语句使用集合变 量作为集合派生的表。
下图展示删除vertexes中四个点之一的例程，以便于多边形成为三角形，而不是矩形。

图:删除四个点之一的例程。

CREATE PROCEDURE shapes()

DEFINE vertexes SET( point NOT NULL );

DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons

WHERE id = 207;

FOREACH cursor1 FOR

SELECT * INTO pnt FROM TABLE(vertexes)

IF pnt = ‘(3,4)’ THEN

–calls the equals function that

–compares two values of point type

DELETE FROM TABLE(vertexes)

WHERE CURRENT OF cursorl;

EXIT FOREACH;

ELSE

CONTINUE FOREACH;

END IF;

END FOREACH

END PROCEDURE;

在前图中，FOREACH语句声明游标。SELECT语句是集合派生的查询，从集合变 量vertexes 一次将选择一个元素至元素变量pnt。

IF THEN ELSE结构测试当前在pnt中的值，看它是否为点’(3,4)’。请注意，表达式pnt = ‘(3,4)‘调用在点数据类型上定义的equal。函数的实例。如果pnt中的当前值为’(3,4)’，贝U DELETE语句删除它，且EXIT FOREACH语句退出该游标。

提示：从存储在集合变量中的集合删除元素，未将它从存储在数据库中的集合删除。在您从集 合变量删除元素之后，您必须以新的集合更新存储在数据库中的集合。要了解展示如何更新集 合列的示例，请参阅更新数据库中的集合。

在《GBase 8s SQL指南：语法》中描述DELETE语句的语法。

更新数据库中的集合

在您(通过删除、更新或插入元素)更改SPL例程中集合变量的内容之后，您必须以新的 集合更新数据库。

要更新数据库中的集合，请添加一个设置表中的集合列的UPDATE语句，设置为更新了 的集合变量的内容。例如，下图中的UPDATE语句展示如何更新polygons表，来 将definition列设置为存储在集合变量vertexes中的新集合。

图:更新数据库中的集合。

CREATE PROCEDURE shapes()

DEFINE vertexes SET(point NOT NULL);

DEFINE pnt point;

SELECT definition INTO vertexes FROM polygons

WHERE id = 207;

FOREACH cursor1 FOR

SELECT * INTO pnt FROM TABLE(vertexes)

IF pnt = ‘(3,4)’ THEN

–calls the equals function that

–compares two values of point type

DELETE FROM TABLE(vertexes)

WHERE CURRENT OF cursorl;

EXIT FOREACH;

ELSE

CONTINUE FOREACH;

END IF;

END FOREACH

UPDATE polygons SET definition = vertexes

WHERE id = 207;

END PROCEDURE;

现在，shapes()例程完成。在您运行shapes()之后，更新存储在其ID列为207的行中的集 合，以便于它包含三个值，而不是四个。

您可使用shapes。例程作为框架，来编写操纵集合的其他SPL例程。

出现在存储在polygons表的207的definition列中的集合的元素罗列如下：

‘(3,1)’

‘(8,1)’

‘(8,4)’

删除整个集合

如果您想要删除集合的所有元素，您可使用单个SQL语句。您无需声明游标。要删除整 个集合，您必须执行下列任务：

•定义集合变量。

•将该集合从数据库选择至集合变量内。

• 输入使用集合变量作为集合派生的表的DELETE语句。

•更新来自数据库的集合。

下图展示您可能在SPL例程中用于删除整个集合的语句。

图:删除整个集合的SPL例程。

DEFINE vertexes SET( INTEGER NOT NULL );

SELECT definition INTO vertexes FROM polygons

WHERE id = 207;

DELETE FROM TABLE(vertexes);

UPDATE polygons SET definition = vertexes

WHERE id = 207;

此种形式的DELETE语句删除集合变量vertexes中的整个集合。您不可使用一个使用集合 派生的表的DELETE语句中的 WHERE子句。

在UPDATE语句之后，polygons表包含空的集合，其中id列等于207。

在GBase 8s SQL指南：语法中描述DELETE语句的语法。

1. 8.6更新集合元素

您可通过访问游标内的集合来更新集合元素，就如同您选择或删除个别的元素一样。

如果您想要更新集合SET(100, 200, 300, 500｝来将值500更改为400,请从数据库将该SET 检索至集合变量内，然后声明游标来在SET中的元素间移动，如下图所示。

图:更新集合元素。

DEFINE s SET(INTEGER NOT NULL);

DEFINE n INTEGER;

SELECT numbers INTO s FROM orders

WHERE ordejnum = 10;

FOREACH cursor1 FOR

SELECT * INTO n FROM TABLE(s)

IF ( n == 500 ) THEN

UPDATE TABLE(s)(x)

SET x = 400 WHERE CURRENT OF cursorl;

EXIT FOREACH;

ELSE

CONTINUE FOREACH;

END IF;

END FOREACH

UPDATE语句使用集合变量s作为集合派生的表。要指定集合派生的表，请使用TABLE 关键字。在UPDATE语句中跟在(s)之后的值(x)是您提供的列名称derived column,因为 SET子句需要它，即使集合派生的表没有列也需要。

请将集合派生的表视作有一行，且看起来与下列示例有些相似：

100 200 300 500

在此示例中，x是包含值500的“列”的虚构的列名称。如果您正在更新内建的、opaque、 distinct或集合类型元素的集合，则仅指定派生的列。如果您正在更新row类型的集合， 请使用字段名，而不是派生的列，如更新row类型的集合描述的那样。

使用变量更新集合

您还可使用存储在变量中的值，而不是文字值，来更新集合。

下图中的SPL过程使用类似于图1展示的语句，除了此过程是使用变量，而不是文字值， 来更新manager表的direct_reports列中的SET。图2定义该manager表。

图:使用变量更新集合。

CREATE PROCEDURE new_report(mgr VARCHAR(30),

old VARCHAR(30), new VARCHAR(30))

DEFINE s SET (VARCHAR(30) NOT NULL);

DEFINE n VARCHAR(30);

SELECT direct_reports INTO s FROM manager WHERE mgjname = mgr;

FOREACH cursor1 FOR

SELECT * INTO n FROM TABLE(s)

IF ( n == old ) THEN UPDATE TABLE(s)(x) SET x = new WHERE CURRENT OF cursorl;

EXIT FOREACH;

ELSE

CONTINUE FOREACH;

END IF;

END FOREACH

UPDATE manager SET mgjname = s WHERE mgr_name = mgr;

END PROCEDURE;

嵌套在FOREACH循环中的UPDATE语句使用集合派生的表s和派生的列x。如果n的 当前值与old相同，则UPDATE语句将它更改为new的值。第二个UPDATE语句 在manager表中存储新集合。

1. 8.7更新整个集合

如果您想要将集合中的所有元素更新为相同的值，或如果该集合仅包含一个值，则您无需 使用游标。下图中的语句展示你可如何将集合检索至集合变量内，然后使用一个语句来更 新它。

图:检索并更新集合。

DEFINE s SET (INTEGER NOT NULL);

SELECT numbers INTO s FROM orders

WHERE ordejnum = 10;

UPDATE TABLE(s)(x) SET x = 0;

UPDATE orders SET numbers = s

WHERE ordejnum = 10;

此示例中的第一个UPDATE语句随同集合派生的表s使用名为x的派生的列，并将 值0赋给集合中的所有元素。第二个UPDATE语句在数据库中存储新的集合。

更新row类型的集合

要更新ROW类型的集合，您可采用这些步骤：

声明集合变量，其字段数据类型与该集合中的那些ROW类型相匹配。
将集合变量的个别字段设置为ROW类型的正确数据值。
对于每一 ROW类型，请使用集合变量更新集合派生的表的全部行。
图2中的manager表有一名为projects的列，包含在下图展示其定义的ROW类型的

LIST。

图:ROW类型的LST 定^

projects LIST( ROW( pro_name VARCHAR(15),

pro_members SET(VARCHAR(20) NOT NULL) ) NOT NULL)

要访问LIST中的ROW类型，请声明游标，并将该LIST选择至集合变量内。然而，在 您检索projects列中每一 ROW类型值之后，您不可个别地更

新pro_name或pro_members字段。相反，对于需要在集合中更新的每一 ROW值，您必 须将整个ROW替换为来自包括新字段值的集合变量的值，如下图所示。

图:访问LIST中的ROW类型。

CREATE PROCEDURE update_pro( mgr VARCHAR(30),

pro VARCHAR(15))

DEFINE p LIST(ROW(a VARCHAR(15), b SET(VARCHAR(20)

NOT NULL) ) NOT NULL);

DEFINE r ROW(p_name VARCHAR(15), p_member SET(VARCHAR(20)

NOT NULL));

LET r = ROW(“project”, “SET{‘member’}”);

SELECT projects INTO p FROM manager

WHERE mgjname = mgr;

FOREACH cursor1 FOR

SELECT * INTO r FROM TABLE§

IF (r.p_name == ‘Zephyr’) THEN

LET r.p_name = pro;

UPDATE TABLE§(x) SET x = r WHERE CURRENT OF cursorl;

EXIT FOREACH;

END IF;

END FOREACH

UPDATE manager SET projects = p

WHERE mgjname = mgr;

END PROCEDURE;

在您可在SPL程序中使用row类型变量之前，您必须使用LSET语句或SELECT INTO 语句来初始化该行变量。前图的FOREACH循环中嵌套的UPDATE语句将row类型 的pro_name字段设置为变量pro中提供的值。

提示：要更新ROW类型的pro_members字段中SET中的值，请声明游标，并使用带有派生 的列的UPDATE语句，如更新集合元素说明的那样。

更新嵌套的集合

如果您想要更新集合的集合，则必须声明游标来访问外层的集合，然后声明嵌套的游标来 访问内层的集合。

例如，假设manager表有一附加的列scores，它包含一其元素类型为整数的MULTISET的 LIST，如下图所示。

图:更新集合的集合。

scores LIST(MULTISET(INT NOT NULL) NOT NULL);

要更新MULTISET中的值，请声明在LIST中每一值间移动的游标，以及在MULTISET 中每一值间移动的嵌套的游标，如下图所示。

图:更新MULTISET中的值。

CREATE FUNCTION check_scores ( mgr VARCHAR(30))

SPECIFIC NAME nested;

RETURNING INT;

DEFINE l LIST( MULTISET( INT NOT NULL ) NOT NULL );

DEFINE m MULTISET( INT NOT NULL );

DEFINE n INT;

DEFINE c INT;

SELECT scores INTO l FROM manager

WHERE mgjname = mgr;

FOREACH list_cursor FOR

SELECT * FROM TABLE(l) INTO m;

FOREACH set_cursor FOR

SELECT * FROM TABLE(m) INTO n;

IF (n == 0) THEN

DELETE FROM TABLE(m)

WHERE CURRENT OF set_cursor;

ENDIF;

END FOREACH;

LET c = CARDINALITY(m);

RETURN c WITH RESUME;

END FOREACH

END FUNCTION

WITH LISTING IN ‘/tmp/nested.out’;

该SPL函数将scores列中的每一 MULTISET选择至1内，然后将MULTISET中的每一 值选择至m内。如果m中的值为0,则函数从MULTISET删除它。在删除0的值之后， 该函数统计每一 MULTISET中剩余的元素数，并返回一整数。

提示：由于此函数为LIST中每一 MULTISET返回一值，因此，当您执行该函数时，您 必须使用游标来括起EXECUTE FUNCTION语句。

1. 8.8插入至集合内

您可将值插入至集合内，而不声明游标。如果该集合为SET或MULTISET，则将该值添 加到集合，但不定义新元素的位置，因为该集合没有特定的顺序。如果该值为LIST,则您 可将新元素添加在LIST中特定的位置，或添加在LIST的末尾。

在manager表中，direct_reports列包含SET类型的集合，且projects列包含LIST。要将 名称添加到direct_reports列中的SET，请使用带有集合派生的表的INSERT语句，如下 图所示。

图:将值插入至集合。

CREATE PROCEDURE new_emp( emp VARCHAR(30), mgr VARCHAR(30))

DEFINE r SET(VARCHAR(30) NOT NULL);

SELECT direct_reports INTO r FROM manager WHERE mgr_name = mgr;

INSERT INTO TABLE ® VALUES(emp);

UPDATE manager SET direct_reports = r WHERE mgr_name = mgr;

END PROCEDURE;

此SPL过程将员工姓名和管理者姓名作为参数。然后，该过程为用户已输入了的管理者选 择direct_reports列中的集合，添加用户已输入了的员工名称，并以新集合更新manager表。 前图中的INSERT语句将用户提供的新的员工姓名插入至在集合变量r中包含的SET 内。然后，UPDATE语句将新集合存储在manager表中。

请注意VALUES子句的语法。将文字的数据和变量插入至集合变量内的语法规则如下：

•使用VALUES关键字之后的圆括号来括起值的完整列表。

如果该集合为SET、MULTISET或LIST,则请使用后跟方括号的类型构造函数来 将要插入的值的列表括起来。此外，必须将集合值括在引号中。
VALUES( “SET{ 1,4,8,9 }”)

如果该集合包含 row 类型,请使用后跟圆括号的 ROW 来将要插入的值的列表括 起来：
VALUES( ROW( ‘Waters’, ‘voyager_project’))

•如果该集合为嵌套的集合，则根据定义数据类型的方式，嵌套关键字、圆括号和方 括号：

VALUES( "SET{ ROW(‘Waters’, ‘voyager_project’),

ROW(‘Adams’, ‘horizon_project’) }")

要获取关于将值插入至集合的信息，请参阅修改数据。

插入至嵌套的集合内

如果您想要插入至嵌套的集合内，则VALUES子句的语法有变化。例如，假设您想要将 值插入至图1展示的numbers表的twin_primes列内。

对于twin_primes列，您可能想要将SET插入至LIST,或将元素插入至内层的SET。下 列部分描述每一任务。

将集合插入至外层的集合内

将SET插入至LIST内，类似于将单个值插入至简单的集合内。

要将SET插入至LIST内，请声明集合变量来保存该LIST,并将整个集合选择至它内。 当您使用集合变量作为集合派生的表时，该LIST中的每一 SET成为该表的一行。然后， 您可将另一 SET插入在该LIST的末尾或插入在指定的点。

例如，一个数值行的twin_primes列可能包含下列LIST,如下图所示。

图:样例LIST。

LIST( SET(3,5), SET(5,7), SET(11,13))

如果您将LIST视为集合派生的表，则它看上去可能像这样。

图:将LIST视为集合派生的表。

(3,5)

(5,7)

(11,13)

您可能想要插入值”SET{17,19}”作为LIST中的第二项。下图中的语句展示如何执行。

图:将值插入到LIST中。

CREATE PROCEDURE add_set()

DEFINE l_var LIST( SET( INTEGER NOT NULL ) NOT NULL );

SELECT twin_primes INTO l_var FROM numbers

WHERE id = 100;

INSERT AT 2 INTO TABLE (l_var) VALUES( “SET(17,19)”);

UPDATE numbers SET twin_primes = l

WHERE id = 100;

END PROCEDURE;

在INSERT语句中，VALUES子句将值SET (17,19｝插入在LIST的第二个位置。现在， 该LIST看上去像下图这样。

图:LIST项

(3,5)

(17,19)

(5,7)

(11,13)

通过将SET作为参数传到SPL例程，您可执行相同的插入，如下图所示。

图:将SET作为参数传到SPL例程。

CREATE PROCEDURE add_set( set_var SET(INTEGER NOT NULL),

row_id INTEGER );

DEFINE list_var LIST( SET(INTEGER NOT NULL) NOT NULL );

DEFINE n SMALLINT;

SELECT CARDINALITY(twin_primes) INTO n FROM numbers WHERE id = row_id;

LET n = n + 1;

SELECT twin_primes INTO list_var FROM numbers

WHERE id = row_id;

INSERT AT n INTO TABLE( list_var ) VALUES( set_var );

UPDATE numbers SET twin_primes = list_var

WHERE id = row_id;

END PROCEDURE;

在add_set()中，用户提供SET来添加到LIST,以及标识将SET插入其中的那行的id的 INTEGER 值。

将值插入至内层的集合

在SPL例程中，您还可将值插入至嵌套的集合的内层集合。通常，要访问嵌套的集合的内 层集合并将值插入到它，请执行下列步骤：

声明集合变量来在表的一行中保存整个集合。
声明元素变量来保存该外层的集合的一个元素。元素变量本身是集合变量。
将整个集合从表的一行选择至集合变量。
声明游标，以便您可在外层的集合的元素间移动。
一次将一个元素选择至元素变量内。
请使用分支或循环来定位您想要更新的内层集合。
将新值插入至内层的集合内。
关闭游标。
以新的集合更新数据库表。
作为示例，您可在numbers的twin_primes列上使用此过程。例如，假设twin_primes包含 下图所示的值，且您想要将值18插入至LIST的最后的SET中。

图:twin_primes 列表。

LIST( SET( (3,5), (5,7), (11,13), (17,19)))

下图展示插入该值的过程的开始。

图:插入值的过程。

CREATE PROCEDURE add_int()

DEFINE list_var LIST( SET( INTEGER NOT NULL ) NOT NULL );

DEFINE set_var SET( INTEGER NOT NULL );

SELECT twin_primes INTO list_var FROM numbers

WHERE id = 100;

至此，attaint过程已执行了步骤1、2和3。第一个DEFINE语句声明保存在一个数值行中 的整个集合的集合变量。

第二个DEFINE语句声明保存该集合的元素的元素变量。在此情况下，元素变量本身是集 合变量，因为它保存SET。SELECT语句将整个集合从一行选择至集合变量list_var内。 下图展示如何声明游标，以便于您可在外层的集合的元素间移动。

图:声明游标来在外层的集合的元素间移动。

FOREACH list_cursor FOR

SELECT * INTO set_var FROM TABLE( list_var);

FOREACH element_cursor FOR

12.9执行例程

您可以下列任一方式执行SPL例程或外部例程：

使用从 DB-Access 执行的单独的 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 语句
从另一 SPL例程或外部例程显式地调用例程
•在SQL语句中使用带有表达式的例程名称

执行例程的附加的机制仅支持sysdbopen和sysdbclose过程，DBA可定义这些过程。当用 户通过CONNECT或DATABASE语句连接到数据库时，如果sysdbopen过程的所有 者与数据库中存在的用户的登录标识符相匹配，则自动地执行那个例程。如果没 有sysdbopen例程的所有者与该用户的登录标识符相匹配，但存在PUBLIC.sysdbopen例 程，则执行那个例程。这种自动的调用使得DBA能够在连接时刻为用户定制会话环境。 当用户从数据库断开连接时，类似地调用sysdbclose例程。(要获取关于这些会话配置例 程的更多信息，请参阅《GBase 8s SQL指南：语法》和GBase 8s管理员指南。) 外部例程是以C或某种其他外部语言编写的例程。

1. 1. 1 EXECUTE 语句

您可使用EXECUTE PROCEDURE或EXECUTE FUNCTION来执行SPL例程或外部 例程。通常，最好将EXECUTE PROCEDURE用于过程，将EXECUTE FUNCTION用于 函数。

提示：为了向后兼容，EXECUTE PROCEDURE语句允许您使用SPL函数名称和INTO子 句来返回值。然而，推荐您仅将EXECUTE PROCEDURE用于过程，仅将EXECUTE FUNCTION用于函数。

您可从DB-Access或从SPL例程或外部例程内，发出EXECUTE PROCEDURE和 EXECUTE FUNCTION语句作为独立的语句。如果在数据库内该例程名称是唯一的，且如 果它不需要参数,则您可通过在EXECUTE PROCEDURE之后只输入它的名称和圆括号来 执行它，如下图所示。

图:执行过程。

EXECUTE PROCEDURE update_orders();

由于过程不返回任何值，因此，当您以EXECUTE语句调用过程时，从不出现INTO子 句。

如果例程期望参数，则您必须在圆括号内输入参数值，如下图所示。

图:执行带有参数的过程。

EXECUTE FUNCTION scale_rectangles(107, 1.9)

INTO new;

该语句执行函数。由于函数返回值，因此，EXECUTE FUNCTION使用INTO子句，指定 存储返回值的变量。当您使用EXECUTE语句来执行函数时，始终出现INTO子句。

如果数据库有多个同名的过程或函数，则GBase 8s基于参数的数据类型来定位到正确的函 数。例如，前图中的语句提供INTEGER和REAL值作为参数，因此，如果您的数据库 包含名为scale_rectangles()的多个例程，在数据库服务器仅执行接受INTEGER和REAL 数据类型的scale_rectangles()函数。

SPL例程的参数列表始终有参数名称及数据类型。当您执行例程时，参数名称是可选的。 然而，如果您通过名称(而不是只通过值)来将参数传到EXECUTE PROCEDURE或 EXECUTE FUNCTION，如下图所示，则GBase 8s仅逐个例程地解析名称和参数，该过程 称为部分的例程解析。

图:执行通过名称传递参数的例程。

EXECUTE FUNCTION scale_rectangles( rectid = 107,

scale = 1.9 ) INTO new_rectangle;

您还可通过将限定的例程名称添加到语句来执行存储在另一数据库服务器上的SPL例程； 即，database@dbserver:owner_name.routine_name 形式的名称，如下图所示。

图:执行存储在另一数据库服务器上的SPL例程。

EXECUTE PROCEDURE gbasedbt@davinci:bsmith.update_orders();

当您远程地执行例程时，限定的例程名称中的owner_name是可选的。

1. 1. 2 CALL 语句

您可使用CALL语句，从SPL例程调用SPL例程或外部例程。CALL可执行过程，也 可执行函数。如果您使用CALL来执行函数，则请添加RETURNING子句和将要接收该 函数返回的值的SPL变量的名称。

例如，假设您想要scale_rectangles函数调用计算矩形面积的外部函数，然后返回带有矩形 描述的面积，如下图所示。

图:调用外部函数。

CREATE FUNCTION scale_rectangles( rectid INTEGER,

scale REAL )

RETURNING rectangle_t, REAL;

DEFINE rectv rectangle_t;

DEFINE a REAL;

SELECT rect INTO rectv

FROM rectangles WHERE id = rectid;

IF ( rectv IS NULL ) THEN

LET rectv.start = (0.0,0.0);

LET rectv.length = 1.0;

LET rectv.width = 1.0;

LET a = 1.0;

RETURN rectv, a;

ELSE

LET rectv.length = scale * rectv.length;

LET rectv.width = scale * rectv.width;

CALL area(rectv.length, rectv.width) RETURNING a;

RETURN rectv, a;

END IF;

END FUNCTION;

该SPL函数使用执行外部函数area()的CALL语句。返回的值area()保存在a中，并通 过RETURN语句返回到调用例程。

在此示例中，area()是外部函数，但您可以同样的方式将CALL用于SPL函数。

1. 9.3执行表达式中的例程

正如内建的函数那样，您可通过在SQL和SPL语句中的表达式，使用SPL例程来执行 SPL例程(以及来自SPL例程的外部例程)。表达式中使用的例程通常为函数，因为它 将值返回至语句的剩余部分。

例如，您可能通过将返回值分配给变量的LET语句来执行函数。下图中的语句执行相同 的任务。它们执行SPL例程内的外部函数，并将返回值分配给变量a。

图:执行SPL例程内的外部函数。

LET a = area( rectv.length, rectv.width );

CALL area( rectv.length, rectv.width ) RETURNING a;

–这些语句是等同的

您还可从SQL语句执行SPL例程，如下图所示。假设您编写SPL函数increase_by_pct， 对给定的价格增加给定的百分比。在您编写SPL例程之后，在任何其他SPL例程中都可 使用它。

图:从SQL语句执行SPL例程。

CREATE FUNCTION raise_price ( num INT )

RETURNING DECIMAL;

DEFINE p DECIMAL;

SELECT increase_by_pct(price, 20) INTO p

FROM inventory WHERE prod_num = num;

RETURN p;

END FUNCTION;

该示例选择inventory的指定的行的price列，并使用该值作为SPL函数increase_by_pct的 参数。然后，该函数返回新的price值，在变量中增加20%。

1. 9.4使用RETURN语句执行外部函数

CREATE FUNCTION c_func() RETURNS int

LANGUAGE C;

CREATE FUNCTION spl_func() RETURNS INT;

RETURN(c_func());

END FUNCTION;

EXECUTE FUNCTION spl_func();

当您执行spl_func()函数时，调用c_func()函数，且SPL函数返回外部函数返回的值。

1. 1. 5从SPL例程执行游标函数

游标函数是返回一行或多行数据的用户定义的函数，因此需要游标来执行。游标函数可为 系列函数之一：

• 其RETURN 语句包括 WITH RESUME的SPL函数

•定义作为迭代函数的外部函数

游标函数的行为与SPL函数或外部函数都一样。然而，SPL游标函数每迭代可返回多个 值，而外部游标函数(迭代函数)每迭代仅可返回一个值。

要从SPL例程执行游标函数，您必须在SPL例程的FOREACH循环中包括该函数。下 列示例展示在FOREACH循环中执行游标函数的不同方式：

FOREACH SELECT cur_func1(col_name) INTO spl_var FROM tabl

INSERT INTO tab2 VALUES (spl_var);

END FOREACH

FOREACH EXECUTE FUNCTION cur_func2() INTO spl_var

INSERT INTO tab2 VALUES (spl_var);

END FOREACH

1. 1. 6动态的例程名称规范

通过在调用例程内构建被调用的例程的名称，动态的例程名称规范允许您从另一 SPL例程 执行SPL例程。动态的例程名称规范简化您编写调用另一 SPL例程的SPL例程的方式， 直到运行时才能知道另一例程的名称。数据库服务器允许您在EXECUTE PROCEDURE或 EXECUTE FUNCTION语句中指定SPL变量，而不是SPL例程的显式的名称。

在下图中，SPL过程company_proc更新大型的公司销售表，然后，分配名

为salesperson_proc的SPL变量来保存更新另一较小表的动态地创建的SPL过程的名 称，这个较小的表包含个别销售人员的每月销售情况。

图:动态的例程名称规范。

CREATE PROCEDURE company_proc ( no_of_items INT, itm_quantity SMALLINT, sale_amount MONEY, customer VARCHAR(50), sales_person VARCHAR(30))

DEFINE salesperson_proc VARCHAR(60);

–更新公司表

INSERT INTO company_tbl VALUES (no_of_items, itm_quantity, sale_amount, customer, sales_person);

–生成变量salesperson_proc的过程名称

LET salesperson_proc = sales_person || “.” || “tbl” || current_month || “_” || current_year || “_proc”;

–执行salesperson_proc变量指定的

–SPL过程

EXECUTE PROCEDURE salesperson_proc (no_of_items, itm_quantity, sale_amount, customer)

END PROCEDURE;

在示例中，过程company_proc接受五个参数，并将它们插入至company_tbl内。然后，LET 语句使用不同的值和连接运算符||来产生要执行的另一 SPL过程的名称。在LET语句 中：

sales_person

传给company_proc过程的参数。

current_month

系统日期中的当前月份。

current_year

系统日期中的当前年份。

因此，如果名为Bill的销售人员在1988年7月完成一笔销售，

则 company_proc 在 company_tbl 中插入一记录，并执行 SPL 过程bill.tbl07_1998_proc,更 新包含个别销售人员的每月销售情况的较小的表。

动态例程名称规范的规则

您必须定义保存动态地执行的SPL例程的名称的SPL变量为CHAR、VARCHAR、 NCHAR或NVARCHAR类型。您还必须为SPL变量提供一个有效的且非NULL的名 称。

在可执行动态的例程名称规范标识的SPL例程之前，该例程必须存在。如果你将有效的

SPL例程的名称分配给该SPL变量，贝U EXECUTE PROCEDURE或EXECUTE FUNCTION语句执行在该变量中包含其名称的例程，即使存在同名的内建的函数。

在EXECUTE PROCEDURE或EXECUTE FUNCTION语句中，您不可使用两个SPL变 量来创建形式为owner.routine_name的变量名称。然而，您可使用包含完全限定的例程名称 的SPL变量，例如，bill.proc1。下图同时展示这两种情况。

图:包含完全跛的例程名称的SPL变量。

EXECUTE PROCEDURE owner_variable.proc_variable;

–不允许这样

LET proc1 = bill.proc1;

EXECUTE PROCEDURE proc1;–允许这样

12.10对例程的权限

权限将可创建例程的用户与可执行例程的用户区分开来。有些权限表现为其他权限的一部 分。例如，DBA权限包括创建例程、执行例程的权限，以及将这些权限授予其他用户的权 限。

1. 10.1注册例程的权限

要在数据库中注册例程，被授权的用户将SPL命令包含在CREATE FUNCTION或 CREATE PROCEDURE语句中。数据库服务器存储内部注册了的SPL例程。下列用户具 有在数据库中注册新的例程的资格：

有DBA权限的任何用户可在CREATE语句中，使用或不使用DBA关键字来注 册例程。
要了解DBA关键字的说明，请参阅执行例程的DBA权限。

没有DBA权限的用户需要Resource权限来注册SPL例程。该例程的创建者是 所有者。
没有DBA权限的用户不可使用DBA关键字来注册例程。

DBA必须给其他需要创建例程的用户授予Resource权限。DBA还可撤销 Resource权限，防止用户创建更多的例程。

除了对在其中注册UDR的数据库保存持有DBA权限或Resource权限之外，创 建UDR的用户还必须持有对以其编写UDR的编程语言的Usage权限。这些 SQL可为特定的编程语言授予语言级别的Usage权限：
O GRANT USAGE ON LANGUAGE C

o GRANT USAGE ON LANGUAGE JAVA

o GRANT USAGE ON LANGUAGE SPL

除了个别的用户之外，这些权限的被授予者还可为用户定义的角色，或PUBLIC 组。在将语言级别Usage权限授予角色之后，持有那个角色的任何用户都可通过 使用SQL的SET ROLE语句使得该角色的所有访问权限能够指定那个角色作为 当前的角色。

对于以C语言或Java™语言编写的外部例程，如果启用IFX_EXTEND_ROLE配置参数， 则仅DBSA已授予其EXTERNAL角色的用户可注册、删除或修改外部的UDR或 DataBlade模块。在缺省情况下，启用此参数。通过将IFX_EXTEND_ROLE配置参数设 置为OFF或设置为0，DBSA可禁用对持有DataBlade模块或外部UDR的DDL操作 EXTEND角色的要求。然而，此安全特性对SPL例程不起作用。

总之，持有以上标识的数据库级别和语言级别自主访问控制权限（且还持有EXTEND角 色，如果启用IFX_EXTEND_ROLE且该UDR为外部例程的话）的用户，可在下列SQL 语句中引用UDR：

DBA 或用户可以 CREATE FUNCTION, CREATE FUNCTION FROM、CREATE PROCEDURE, CREATE PROCEDURE FROM、CREATE ROUTINE 或 CREATE ROUTINE FROM 语句来注册新的UDR。
DBA 或现有 UDR 的所有者可以 DROP FUNCTION, DROP PROCEDURE 或 DROP ROUTINE语句来取消那个UDR的注册。
DBA 或现有 UDR 的所有者可以 ALTER FUNCTION、ALTER PROCEDURE 或 ALTER ROUTINE语句来修改那个UDR的定义。
12. 10.2执行例程的权限

Execute权限使得用户能够调用例程。通过EXECUTE或CALL语句，或通过使用表达 式中的函数可能调用例程。下列用户拥有缺省的Execute权限，这使得他们能够调用例程:

•在缺省情况下，任何具有DBA权限的用户都可执行数据库中的任何例程。

如果以限定的 CREATE DBA FUNCTION 或 CREATE DBA PROCEDURE 语句 注册该例程，则仅拥有DBA权限的用户对那个例程有缺省的Execute权限。
如果数据库不符合ANSI，则用户public （任何拥有Connect数据库权限的用户） 自动地拥有对例程的Execute权限，未以DBA关键字注册该例程。
•在符合ANSI的数据库中，过程所有者和任何拥有DBA权限的用户都可执行该 例程，而无需收到附加的权限。

授予和撤销Execute权限

例程有下列GRANT和REVOKE要求：

DBA可将Execute权限授予数据库中的任何例程，也可撤销它。
•例程的创建者可授予或取消对那个特定的例程的Execute权限。通过包括带有 GRANT EXECUTE ON语句的AS grantor子句，创建者丧失授予或撤销的能力。

如果所有者在GRANT EXECUTE ON语句中应用了 WITH GRANT关键字，则另 一用户可授予Execute权限。
对于下列条件，DBA或例程所有者必须显式地将Execute权限授予非DBA用户：

以 DBA 关键字子句注册了的例程
在符合 ANSI 的数据库中的例程
不符合ANSI的数据库中的例程，但将NODEFDAC环境变量设置为yes。
即使数据库服务器缺省地将权限授予public，所有者也可限制对例程的Execute权限。为 此，请发出REVOKE EXECUTE ON PUBLIC语句。DBA和所有者仍可执行该例程，且 如果使用的话，则可将Execute权限授予特定的用户。

使用COMMUTATOR和NEGATOR函数的Execute权限

重要：如果您显式地授予对SPL函数的Execute权限，其为UDR的换向函数或否定函数， 则在被授予者可使用任意函数之前，您还必须授予对换向函数或否定函数的那种权限。您不可 随同SPL过程指定COMMUTATOR或NEGATOR修饰符。

下列示例演示对于函数的限制授权，以及将它的否定函数限定为一组用户。假设您创建下 列否定函数对：

CREATE FUNCTION greater(y PERCENT, z PERCENT)

RETURNS BOOLEAN

NEGATOR= less(y PERCENT, z PERCENT);

CREATE FUNCTION less(y PERCENT, z PERCENT)

RETURNS BOOLEAN

NEGATOR= greater(y PERCENT, z PERCENT);

在缺省情况下，任何用户都可执行该函数和否定函数。下列函数仅允许accounting执行这 些函数：

REVOKE EXECUTE ON FUNCTION greater FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION less FROM PUBLIC;

GRANT accounting TO mary, jim, ted;

GRANT EXECUTE ON FUNCTION greater TO accounting;

GRANT EXECUTE ON FUNCTION less TO accounting;

用户可能接收附带WITH GRANT OPTION授权的Execute权限来将Execute权限授予 其他用户。如果用户失去对例程的Execute权限，则还从通过那个用户授予了 Execute权 限的那些用户撤销Execute权限。

要获取更多信息，请参阅《GBase 8s SQL指南：语法》中的GRANT和REVOKE语句 描述。

1. 10.3对与例程相关联的对象的权限

数据库服务器检查是否存在任何被引用的对象，并验证调用该例程的用户是否拥有访问被 引用的对象的必要权限。

由例程引用的对象可包括：

表和列
序列对象
•用户定义的数据类型

由该例程执行的其他例程
当例程运行时，定义有效的权限为下列的联合：

•运行该例程的用户的权限，

•带有GRANT选项的所有者的权限。

在缺省情况下，数据库管理员拥有数据库中带有GRANT选项的所有权限。因此，执行由 数据库管理员拥有的例程的用户可从给定的数据库中所有表进行选择。

GRANT EXECUTE ON语句提供给被授予者任何表级别权限，授予者从包含WITH GRANT关键字的GRANT语句收到这些权限。

该例程的所有者，不是运行该例程的用户，拥有在执行该例程过程中创建的未限定的对象。 例如，假设用户howie注册创建两个表的SPL例程，使用下列SPL例程：

CREATE PROCEDURE promo()

CREATE TABLE newcatalog

(

catlog_num INTEGER

cat_advert VARCHAR(255, 65)

cat_picture BLOB

);

CREATE TABLE dawn.mailers

(

cust_num INTEGER

interested_in SET(catlog_num INTEGER)

);

END PROCEDURE;

用户julia运行该例程，创建表newcatalog。由于没有所有者名称来限定表名称newcatalog， 因此，例程所有者(howie)拥有newcatalog。相比之下，限定的名称dawn.maillist标识dawn作 为 maillist 的所有者。

1. 10.4执行例程的DBA权限

如果DBA使用DBA关键字创建例程，则数据库服务器自动地仅将Execute权限授予有 DBA权限的其他用户。然而，DBA可显式地将DBA例程上的Execute权限授予没有 DBA 权限的用户。

当用户执行以DBA关键字注册了的例程时，该用户假设在例程持续期间持有DBA权限。 如果没有DBA权限的用户运行DBA例程，则数据库服务器隐式地将临时的DBA权限 授予调用者。在退出DBA例程之前，数据库服务器隐式地撤销该临时的DBA权限。

执行DBA例程的用户拥有在运行该DBA例程期间创建的对象，除非例程中的语句显式 地命名其他用户作为所有者。例如，假设tony以DBA关键字注册promo()例程，如下：

CREATE DBA PROCEDURE promo()

CREATE TABLE catalog

CREATE TABLE libby.mailers

END PROCEDURE;

虽然tony拥有该例程，但如果marty运行它，那么marty拥有catalog表，但由于用 户libby的名称限定libby.mailers表名称，使得她成为该表的所有者，因此它拥有该表。 被调用的例程未继承DBA权限。如果DBA例程执行未以DBA关键字创建了的例程， 则DBA权限不影响被调用的例程。

如果未以DBA关键字注册的例程调用DBA例程，则调用者对于被调用的DBA例程必 须有Execute权限。该DBA例程内的语句执行如同任何DBA例程内的语句一样。

下列示例展示当DBA与非DBA例程相互作用时发生的情况。假设过

程 dbspc_cleanup()执行另一过程 clust_catalog()。还假设 clust_catalog()创建索引，

且clust_catalog()的SPL源代码包括下列语句：

CREATE CLUSTER INDEX c_clust_ix ON catalog (catalog_num);

DBA 过程 dbspc_cleanup() 以下列语句调用其他例程：

EXECUTE PROCEDURE clust_catalog(catalog);

假设tony注册了 dbspc_cleanup()作为DBA过程，而未以DBA关键字注

册clust_catalog(),如下列语句所示：

CREATE DBA PROCEDURE dbspc_cleanup(loc CHAR)

CREATE PROCEDURE clust_catalog(catalog CHAR) GRANT EXECUTE ON dbspc_cleanup(CHAR) to marty;

假设用户marty运行dbspc_cleanup()o由于通过非DBA例程创建索引c_clust_ix，因此， 同时拥有两个例程的tony也拥有c_clust_ix。相对地，如果clust_catalog()为DBA过程， 则marty会拥有索引c_clust_ix,如下列注册和授权语句所示：

CREATE PROCEDURE dbspc_cleanup(loc CHAR);

CREATE DBA PROCEDURE clust_catalog(catalog CHAR); GRANT EXECUTE ON clust_catalog(CHAR) to marty;

请注意，dbspc_cleanup()无需DBA过程来调用DBA过程。

12.11在SPL例程中査找错误 当您以 DB-Access 使用 CREATE PROCEDURE 或 CREATE FUNCTION 来编写 SPL 例 程时，如果在例程体中发生语法错误，则当您从菜单选择Run时，该语句失败。

如果您正在DB-Access中创建例程，当您从菜单选择Modify选项时，光标移动至包含语法 错误的那一行。您可再次选择Run和Modify来检查后续的行。

1. 11.1编译时刻警告

如果数据库服务器检测到潜在的问题，但该SPL例程的语法是正确的，则数据库服务器生 成警告，并将它放置在列表文件中。您可在执行该例程前，检测此文件来检查潜在的问题。

在 CREATE PROCEDURE 或 CREATE FUNCTION 语句的 WITH LISTING IN 子句中

指定文件名称和列表文件的路径名称。要获取关于如何指定列表文件的路径名称的信息， 请参阅 指定DOCUMENT子句。

如果您正在网络上工作，则在该数据库驻留的系统上创建列表文件。如果您为该文件提供 绝对的路径名称和文件名称，则在您指定的位置创建该文件。

对于UNIX™，如果您为列表文件提供相对的路径名称，则在数据库驻留的计算机上您的 home目录中创建该文件。（如果您没有home目录，则在root目录中创建文件。）

对于Windows™，如果您为列表文件提供相对的路径名称，则如果该数据库在本地计算机 上，则缺省的目录为您的当前工作目录。否则，缺省的目录为％GBASEDBTDIR%\bin。

在您创建例程之后，您可查看在WITH LISTING IN子句中指定的文件，来查看它包含的

警告。

1. 11.2生成例程的文本

在您创建SPL例程之后，将它存储在sysprocbody系统目录表中。sysprocbody系统目录表 包含可执行的例程，以及它的文本。

要检索例程的文本，请从sysprocbody系统目录表选择data列。文本条目的datakey列有代 码T。

SELECT data FROM gbasedbt.sysprocbody

WHERE datakey = ‘T’ --查找文本行

AND procid =

（SELECT procid

FROM gbasedbt.sysprocedures

WHERE gbasedbt.sysprocedures.procname =

‘read_address’)

12.12调试SPL例程

在您成功地创建并运行SPL例程之后，您可遇到逻辑错误。如果例程有逻辑错误，则请使 用TRACE语句来帮助查找它们。您可跟踪下列项的值：

变量
参数
返回值
SQL错误代码
ISAM错误代码
要生成被跟踪的值的列表，首先请使用SQL语句SET DEBUG FILE来命名包含被跟踪的 输出的文件。当您创建SPL例程时，请包括TRACE语句。

下列方法指定TRACE输出的形式。

语句

操作

TRACE ON

跟踪SQL语句之外的所有语句。在使用变量之前，打印它们的内容。跟踪例程调 用和返回的值。

TRACE PROCEDURE

仅跟踪例程调用和返回的值。

TRACE expression

打印文字或表达式。如果必要，在将表达式的值发送至文件之前，计算它。

CREATE FUNCTION read_many (lastname CHAR(15))

RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2), CHAR(5);

DEFINE p_lname,p_fname, p_city CHAR(15);

DEFINE p_add CHAR(20);

DEFINE p_state CHAR(2);

DEFINE p_zip CHAR(5);

DEFINE Icount, i INT;

LET Icount = 1;

TRACE ON; --从此开始跟踪每个表达式

TRACE ‘Foreach starts’;–跟踪带有文字的语句

FOREACH

SELECT fname, Iname, address1, city, state, zipcode INTO p_fname, p_lname, p_add, p_city, p_state, p_zip

FROM customer

WHERE lname = lastname

RETURN p_fname, p_lname, p_add, p_city, p_state, p_zip WITH RESUME;

LET lcount = lcount + 1;–对返回的地址计数

END FOREACH

TRACE ‘Loop starts’; --另一文字

FOR i IN (1 TO 5)

BEGIN

RETURN i , i+1, i*i, i/i, i-1,i WITH RESUME;

END

END FOR;

END FUNCTION;

使用TRACE ON语句，在您每次执行被跟踪的例程时，都将条目添加到您在SET DEBUG FILE语句中指定了的文件。要查看调试条目，请使用任何文本编辑器来查看该输出文件。 下列列表包含前面的示例中函数生成的一些输出。每一被跟踪的语句之后是对它的内容的 解释。

语句

操作

TRACE ON

回送TRACE ON语句。

TRACE Foreach starts

在此情况下，跟踪表达式，文字字符串Foreach开始。

start select cursor

提供打开游标来处理FOREACH循环的通知。

select cursor iteration

提供选择游标的每一迭代的开始的通知。

expression: （+lcount, 1）

对遇到的表达式（lcount+1）求值为2。

let Icount = 2

以该值回送每一 LET语句。

12.13异常处理

您可使用ON EXCEPTION语句来捕获数据库服务器返回给您的SPL例程的任何异常 （或错误），或该例程产生的任何异常。RAISE EXCEPTION语句允许您生成SPL例程 内的异常。

在SPL例程中，您不可使用异常处理来处理下列情况：

成功（返回了行）
成功（未返回行）
12. 13.1错误捕获与恢复

ON EXCEPTION语句提供捕获任何错误的机制。

要捕获错误，请将一组语句包含在以BEGIN与END标记的语句块中，并在该语句块的 开头添加ON EXCEPTION IN语句。如果在跟在ON EXCEPTION语句之后的块中发生 错误，则您可采取恢复措施。

BEGIN

DEFINE c INT;

ON EXCEPTION IN

（

-206,–表不存在 -217 --列不存在

)SET err_num

IF err_num = -206 THEN

CREATE TABLE t (c INT);

INSERT INTO t VALUES (10);

–在插入语句之后继续

ELSE

ALTER TABLE t ADD(d INT);

LET c = (SELECT d FROM t);

–在选择语句之后继续

END IF

END EXCEPTION WITH RESUME

INSERT INTO t VALUES (10);–如果 t 不存在，则失败

LET c = (SELECT d FROM t);–如果d不存在，则失败

END

当发生错误时，SPL解释器搜索捕获该错误的最内层ON EXCEPTION声明。捕获错误之 后的第一个操作是重置该错误。当完成错误操作代码的执行时，且如果引起错误的ON EXCEPTION声明包括了 WITH RESUME关键字，则以跟在产生了该错误的语句之后的 语句自动地恢复执行。如果ON EXCEPTION声明未包括 WITH RESUME关键字，则执 行完全地退出当前的块。

1. 13.2 ON EXCEPTION语句的控制作用域

ON EXCEPTION语句的作用域从紧跟在ON EXCEPTION语句之后的语句扩展，并结束 于在其中发出ON EXCEPTION语句的语句块的末尾。如果SPL例程未包括显式的语句 块，则作用域为该例程中所有后续的语句。

对于在IN子句中指定的异常(或对于所有异常，如果未指定IN子句的话)，ON EXCEPTION语句的作用域包括同一语句块内跟在ON EXCEPTION语句之后的所有语 句。如果在那个块内嵌套其他语句块，则该作用域还包括跟在ON EXCEPTION语句之后 的嵌套的语句块中的所有语句，以及在那些嵌套的块内嵌套的语句块中的任何语句。

下列伪代码展示在例程内该例程为有效的位置。即，如果错误201发生在任何指示了的块 中，则发生标号为a201的操作。

END EXCEPTION

BEGIN --嵌套的语句块aa

–执行操作，a201在此有效

END

BEGIN --嵌套的语句块bb

–执行操作，a201在此有效

END

WHILE i < 10

–执行某操作，a201在此有效

END WHILE

END --语句块A的末尾 BEGIN --开始语句块B

–执行某操作

–a201在此NOT有效

END

END PROCEDURE;

1. 13.3用户生成的异常

BEGIN

ON EXCEPTION SET esql, eisam --捕获所有错误

IF esql = -206 THEN --未找到表

–某种恢复

ELSE

RAISE exception esql, eisam;–放过该错误

END IF

END EXCEPTION

–执行某操作

END

在该示例中，ON EXCEPTION语句使用两个变量esql和eisam,来保存数据库服务器返回 的错误编号。如果发生错误且如果SQL错误编号为-206，则执行IF子句。如果捕获任 何其他SQL错误，则将它从此BEGINEND块传至前面的示例的最后BEGINEND块。

模拟SQL错误

BEGIN

IF user = ‘pault’ THEN

RAISE EXCEPTION -273;–拒绝 Paul 的 update 权限

END IF

END

使用RAISE EXCEPTION来退出嵌套的代码

BEGIN

ON EXCEPTION IN (1)

END EXCEPTION WITH RESUME – do nothing significant (cont)

BEGIN

FOR i IN (1 TO 1000)

FOREACH select …INTO aa FROM t

IF aa < 0 THEN

RAISE EXCEPTION 1; – emergency exit

END IF

END FOREACH

END FOR

RETURN 1;

END

–do something; – emergency exit to

–this statement.

TRACE ‘Negative value returned’;

RETURN -10;

END

如果最内层的条件为真(如果aa为负)，则发生异常，且执行跳至跟在该块的END之后 的代码。在此情况下，执行跳至TRACE语句。

请记住，BEGINEND块为单个语句。如果在块中的某处发生错误，且在该块之外捕获，则 当执行恢复时，跳过该块剩余的部分，并从下一语句开始执行。

除非您在该块的某处为此错误设置捕获，否则，将错误条件传回至包含该调用的块，并传 回至包含该块的任何块。如果不存在设置处理该错误的ON EXCEPTION语句，则停止该 SPL例程的执行，为正在执行该SPL例程的例程创建一个错误。

12.14检査SPL例程中处理的行数

在SPL例程内，您可使用DBINFO行数来找出在SELECT、INSERT、UPDATE、DELETE、 EXECUTE PROCEDURE 和 EXECUTE FUNCTION 语句中已处理了的行数。

CREATE FUNCTION del_rows ( pnumb INT )

RETURNING INT;

DEFINE nrows INT;

DELETE FROM sec_tab WHERE part_num = pnumb;

LET nrows = DBINFO(‘sqlca.sqlerrd2’);

RETURN nrows;

END FUNCTION;

要确保有效的结果，请在已执行完毕的SELECT和EXECUTE PROCEDURE或 EXECUTE FUNCTION语句之后使用此选项。此外，如果您在游标内使用’sqlca.sqlerrd2’ 选择，请确保在关闭游标之前访存所有行，来保证有效的结果。

12.15总结

SPL例程为提高您的数据库处理效率提供许多机会，包括提升数据库性能，简化应用程序， 以及限制或监视对数据的访问。您还可使用SPL例程来处理扩展的数据类型，诸如集合类 型、:row类型、opaque类型和distinct类型。要了解SPL语句的语法图，请参阅《GBase 8s SQL指南：语法》。
————————————————
版权声明：本文为CSDN博主「aisirea」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/aisirea/article/details/124093137