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

最新推荐文章于 2024-08-08 07:46:40 发布

aisirea

最新推荐文章于 2024-08-08 07:46:40 发布

阅读量1.2k

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/aisirea/article/details/124093137

版权

本文介绍了如何在GBase 8s中创建和使用SPL例程，包括过程和函数。SPL例程是增强SQL的存储过程，能提升性能，简化应用，控制数据访问。通过CREATE PROCEDURE或CREATE FUNCTION创建，支持参数、返回值、显示标签等功能，并可通过ON EXCEPTION处理异常。SPL例程可包含SQL和SPL语句，且支持变体函数、文档注释以及编译时警告处理。通过调试和异常处理，能有效地管理和优化SPL例程。

摘要由CSDN通过智能技术生成

本部分描述如何创建和使用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

12. 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语句。

12. 2. 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例程中为注释使用连字符(--)。

12. 2. 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

12. 2. 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'; }

12. 2. 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数据类型：