您现在的位置: 教育资源库 >> 电脑学院 >> 认证考试 >> IBM认证 >> 正文

Informix Dynamic Server 11.50 基础考试 555 认证准备,第 5 部分: 使用 SQL_5151doc

Informix Dynamic Server 11.50 基础考试 555 认证准备,第 5 部分: 使用 SQL
作者:未知 文章来源:网络 点击数: 更新时间:2010-3-4

  开始之前

  本教程将全面概述结构化查询语言 (SQL) 的 Informix Dynamic Server (IDS) 实现。

  关于本系列

  这个免费的共包含 9 篇教程的 Informix Dynamic Server 11.50 基础考试 555 认证准备系列 的目的是为了帮助您准备 Informix Dynamic Server (IDS) 基础认证考试(555)。这个认证将考察关于 IDS 11.50 管理的入门级知识,包括基础 SQL、如何安装 IDS 11.50、如何创建数据库和数据库对象、安全性、事务隔离、备份和恢复流程,以及数据复制技术。这些教程为考试的每部分打下了坚实的基础。不过,您不能仅使用这些教程作为唯一的考试准备材料。

  关于本教程

  本教程将向您介绍 SQL 并帮助您理解 IDS 如何使用 SQL 操作关系数据库中的数据。

  目标

  本教程旨在帮助您熟悉:

  如何对表执行查询

  如何使用 SQL 操作数据

  事务的概念

  存储过程和用户定义例程的使用

  前提条件

  本教程面向初中级水平的数据库专业人员。您应该熟悉基本的数据库概念。

  系统需求

  要完成本教程,您并不需要 IDS 副本。但如果有一个可以使用,那么无疑可以充分发挥本教程的作用。如果还没有副本,可以下载免费试用版本。

  SQL

  理解 SQL

  SQL 是一种用于定义和操作数据库对象的语言。您可以使用 SQL 定义数据库表、在表中插入数据、更改表中的数据以及检索表中的数据。SQL 中最重要的一种应用是 SELECT 语句。SELECT 语句允许您检索关系数据库中的数据,而 INSERT、UPDATE、MERGE 和 DELETE 则可用于检索数据库中的数据。与其他任何语言类似,SQL 有既定的语法和一组语言元素。在本教程中,我们主要讨论如何使用 SQL 检索和操作数据。

  本教程中的所有 SQL 示例都基于 stores_demo 数据库,它随带在 IDS 产品中。stores_demo 数据库包含一组描述虚拟业务的表。您可以轻松的创建和填充 stores_demo 数据库,只需要使用 log 选项运行 dbaccessdemo 脚本:dbaccessdemo -log。在运行 dbaccessdemo 脚本时,您需要设置 Informix 环境变量。dbaccessdemo 脚本位于您 Informix 安装的 bin 目录中。

  数据操作语言(Data Manipulation Language,DML) — 检索数据

  使用 SELECT 语句从表中检索数据

  SELECT 语句用于从表或视图中检索数据。这种操作也被称为查询。您需要具备 Connect 权限才能访问关系数据库,并且需要具备 Select 权限才能使用 SELECT 检索表或视图中的数据。采用最简单的形式,SELECT 语句可用于检索表或视图中的所有数据。以下语句是使用 SELECT 语句检索 customer 表中的所有数据的一个示例:

SELECT * FROM customer; 

  SELECT 语句也可以像上述语句那样简单地使用,但也可支持多个 SQL 语句和关键字的复杂使用。IDS 支持许多不同的 SQL 语句。本教程将介绍其中最常用的一些 SQL 语句。

  您可以使用 FIRST 关键字来限制查询结果的行数。以下代码示例展示了如何检索 customer 表中的前 10 行:

SELECT FIRST 10 * FROM customer;

  要从表中检索具体的列,可以在 SELECT 关键字后指定使用逗号分隔的列名。以下示例展示了如何从 customer 表中检索 fname 和 lname 列:

SELECT fname, lname FROM customer;

  在 SELECT 语句中使用 DISTINCT 关键字可以清除查询结果中的重复行。以下示例展示了如何清除 orders 表中的重复 customer 数据:

SELECT DISTINCT customer_num FROM orders;

  您可以在 SELECT 语句 语句中使用 AS 子句为 SELECT 列表中的表达式或列分配一个有意义的名称。以下示例展示了如何使用 AS 子句计算 orders 表中的订购日期与配送日期之间的处理时间:

SELECT ship_date-order_date AS process_time FROM orders;

  您可以在 SELECT 语句中使用许多内置 SQL 函数。表 1 列出了 SELECT 语句常用的一些内置函数:

表 1. 常用 SQL 函数

函数描述
聚合函数函数  
COUNT查询结果中的行数
SUM数值列求和
AVG数值列的平均值
MAX某列的最大值
MIN某列的最小值
时间函数  
DAY表示日的整型值
MONTH表示月的整型值
YEAR表示年的整型值
WEEKDAY表示一周中的某一天的整型值(0 表示周日)
DATE返回日期值
CURRENT返回当前日期时间值
TODAY返回系统日期
其他函数  
TRUNC返回某表达式截断后的值
HEX返回十六进制值
ROUND返回四舍五入值
LENGTH返回字符串列的长度

  以下 SELECT 语句示例使用 COUNT (*) 函数返回 customer 表中的行数:

SELECT COUNT(*) FROM customer;

  过滤数据

  使用 WHERE 子句和谓语过滤查询返回的数据

  通过指定一个或多个选择标准或搜索条件,您可以使用 WHERE 子句选择表或视图中的具体行。搜索条件包括一个或多个谓语。谓语指定与某行相关的或真或假的判断。在构建搜索条件时考虑以下事项:

  算术运算仅针对数值数据类型。

  仅对具有可比性的数据类型执行比较。

  在引号中包含字符串值。

  指定数据库中真实存在的字符串值。

  我们来看一些如何使用 WHERE 子句的示例。

  首先,这个示例展示了如何从 customer 表中查找编号大于 120 的客户的名称:

SELECT fname, lname, customer_num FROM customer 
 WHERE customer_num > 120; 

  以下示例将从 ustomer 表中查找居住在 Denver 且客户编号大于 120 的客户的名称:

SELECT fname, lname, customer_num FROM customer 
 WHERE customer_num > 120 
 AND city = 'Denver'; 

  LIKE 关键字支持在引用的字符串中使用通配符。百分比符号 (%) 是一个代表零个或多个字符串的通配符。

  以下示例展示了如何查找 customer 表中以字母 L 开头的所有客户名称:

SELECT fname FROM customer 
 WHERE fname LIKE 'L%'; 

  您可以在 WHERE 子句中使用许多关键字与运算符来定义搜索条件。表 2 提供了最常用的关键字和运算符的列表:

表 2. WHERE 子句中常用的关键字和运算符列表

关键字/运算符描述
关键字  
BETWEEN值的范围
IN值的子集
LIKE变量文本搜索
MATCHES变量文本搜索
IS NULL搜索 NULL 字符串
IS NOT NULL搜索非 NULL 字符串
AND/OR/NOT用于连接两个或多个条件的逻辑运算符
算术运算符  
+
-
*
/
关系运算符  
= or ==等于
!= or <> 不等于
>大于
>=大于或等于
<小于
<=小于或等于

  您可以使用在 WHERE 子句中使用前置关键字和运算符来创建执行以下操作的比较条件查询:

  包括某些值

  排除某些值

  查找某范围中的值

  排除某范围中的值

  查找值的某个子集

  识别 NULL 值

  包括某些值

  使用等号 (=)关系运算符在 WHERE 子句中包括某些行,如以下示例所示。

SELECT customer_num, call_code, call_dtime, res_dtime 
 FROM cust_calls 
 WHERE user_id = 'maryj';

  此查询将返回 cust_calls 表中的所有行。下面给出了一个使用 1=1 表达式的查询示例:

SELECT customer_num, call_code, call_dtime, res_dtime 
 FROM cust_calls 
 WHERE 1=1; 

  排除某些值

  使用 != or <> 关系运算符在 WHERE 子句中排除某些值。

清单 1. 使用 != 和 <> 运算符排除某些值的示例

SELECT customer_num, company, city, state 
 FROM odin.customer 
 WHERE state != 'CA'; 
 
SELECT customer_num, company, city, state 
 FROM odin.customer 
 WHERE state <> 'CA'; 

  查找某范围中的值

  清单 2 显示了在 WHERE 子句中指定行范围的两种方法:

  使用 BETWEEN 和 AND 关键字

  使用 >= 和 <= 运算符

清单 2. 指定值范围的示例

SELECT catalog_num, stock_num, manu_code, cat_advert 
 FROM catalog 
 WHERE catalog_num BETWEEN 10005 AND 10008; 
 
SELECT catalog_num, stock_num, manu_code, cat_advert 
 FROM catalog 
 WHERE catalog_num >= 10005 AND catalog_num <= 10008;

  排除某范围中的行

  清单 3 使用关键字 NOT BETWEEN 排除 zipcode 列中字符串范围从 94000 到 94999 的行:

清单 3. 排除范围的示例

SELECT fname, lname, city, state 
  FROM customer 
  WHERE zipcode NOT BETWEEN '94000' AND '94999';

  查找值的子集

  与排除行相类似,清单 4 中的示例假定使用 ANSI 兼容的数据库。引号中的所有者限定符用于保留字面字符串的大小写敏感性。

清单 4. 使用 OR 和 IN 关键字查找值的子集示例

SELECT lname, city, state, phone 
  FROM 'Aleta'.customer 
  WHERE state = 'AZ' OR state = 'NJ' 
  ORDER BY lname; 
 
SELECT lname, city, state, phone 
  FROM 'Aleta'.customer 
  WHERE state IN ('AZ', 'NJ') 
  ORDER BY lname;

  识别 NULL 值

  使用 IS NULL 或 IS NOT NULL 关键字检查 NULL 值。NULL 值表示数据不存在或不可知。NULL 值不同于零、空白或空的字符串。

清单 5. 返回拥有 null paid_date 值的所有行示例

SELECT order_num, customer_num, po_num, ship_date 
  FROM orders 
  WHERE paid_date IS NULL 
  ORDER BY customer_num; 

  排序

  使用 ORDER BY 子句对查询返回的数据排序

  使用 ORDER BY 子句按一列或多列对查询结果排序。不需要在 Projection 列表中指定 ORDER BY 子句中指定的列名。以下示例演示了如何使用 ORDER BY 子句,它将从 customer 表检索数据并根据客户编号对其进行排序:

SELECT fname, lname FROM customer 
  ORDER BY customer_num;

  默认情况下,ORDER BY 子句在排序时将使用升序。您可以在 ORDER BY 子句中使用 DESC 关键字将查询结果更改为降序排列。

  结合

  使用 join 从多个表中检索数据

  您可以结合两个或多个表的查询结果。经常出现的一种情况是,所需的数据分布在两个或多个表中。举例来说,完全结合两个三列表将生成一个包含六列的查询结果。结合条件将为要结合的各表中的至少一列指定关系。由于结合条件中的列需要进行比较,因此它们必须具备可比的数据类型。清单 6 提供了一个结合两个表(customer 和 orders)的示例。列名 customer_num 表示表之间的关系。

清单 6. 结合的示例

SELECT a.company, b.order_num, b.order_date 
  FROM customer a, orders b 
  WHERE a.customer_num = b.customer_num ;

  IDS 支持多种类型的结合:

  CROSS JOIN:通过结合所有可能的行对生成笛卡尔积

  INNER JOIN:仅从表中生成满足结合条件的行

  LEFT OUTER JOIN:生成一个表中的某些行,以及另一个表中的所有行

  RIGHT OUTER JOIN:类似于 LEFT,但两个表的角色将互换

  FULL OUTER JOIN:生成两个表的 INNER JOIN 中的所有行,或者如果与其他表不匹配的各表中的所有行

  清单 6 显示了 INNER JOIN 的一个示例。 INNER JOIN 仅从表中返回满足结合条件的行。如果某行存在于某个表中,而不存在于另一个表中,那么它不会包含在查询结果中。可以通过在 FROM 子句中明确指定 INNER JOIN来覆盖相同的查询,如清单 7 所示:

清单 7. INNER JOIN 的示例

SELECT a.company, b.order_num, b.order_date 
  FROM customer a INNER JOIN orders b 
  ON a.customer_num = b.customer_num ;

  清单 8 显示了 INNER JOIN 查询的结果。

清单 8. INNER JOIN 查询的结果

COMPANY        ORDER_NUM ORDER_DATE 
 
Play Ball          1001 05/20/1998 
All Sports Supplies     1002 05/21/1998 
Play Ball          1003 05/22/1998 
Watson & Son        1004 05/22/1998 

  清单 9 显示了 LEFT OUTER JOIN 的一个示例。您将使用与前一示例相同的数据集,以及几乎完全相同的 SQL 语句。唯一的区别在于合并的类型。在此合并语句中,customer 表是出现在关键字左侧以 OUTER JOIN 开始的主要表。 LEFT OUTER JOIN 将返回合并条件为真的所有行,以及主要表中的所有其他行,并将从属表中的相应值显示为 NULL。ORDERS 表在这里为从属表。

清单 9. LEFT OUTER JOIN 的示例

SELECT a.company, b.order_num, b.order_date 
  FROM customer a LEFT OUTER JOIN orders b 
  ON a.customer_num = b.customer_num ;

  清单 10 显示了左外部合并的结果:

清单 10. LEFT OUTER JOIN 的查询结果

COMPANY        ORDER_NUM ORDER_DATE 
 
All Sports Supplies    1002 05/21/1998 
Sports Spot 
Phil's Sports 
Play Ball         1001 05/20/1998 
Play Ball         1003 05/22/1998 
Play Ball         1011 06/18/1998 
Play Ball         1013 06/22/1998 
Los Altos Sports 
Watson & Son        1004 05/22/1998 
Watson & Son        1014 06/25/1998 

  合并查询

  使用 set 运算符将两个或多个查询合并为一个查询

  您可以通过使用 UNION 子句、 NOT EXISTS 或 NOT IN 子句,或者 EXISTS、IN 子句将两个或多个查询合并为一个查询。Set 运算符将处理查询结果、清除复制并返回最终结果集。

  UNION 子句通过合并两个或多个查询结果来生成最终查询结果。

  使用 NOT EXISTS 或 NOT IN 子句引入子查询来显示两组集之间的差异。这些子句将包含第一个查询返回的所有行(而非第二或任何后续查询返回的行),由此生成查询结果。

  使用 EXISTS 或 IN 子句引入子查询来显示两组集之间的交集。这些子句仅包含所有查询返回的行,由此来生成查询结果。

  清单 11 演示了使用 UNION set 运算符的查询示例。在该示例中,您将从 customer 表中选择 state 列,并从 state 表中选择相应的 code 列。

清单 11. 使用 UNION 子句合并两个查询的示例

SELECT state FROM customer 
  WHERE customer_num BETWEEN 100 AND 125 
 
UNION 
 
SELECT code FROM state 
  WHERE sname LIKE 'C%';

  并没有必要合并两个完全相同的列或表。合并查询的 projection 子句中的相应列必须有可比的数据类型,但各列不需要使用相同的列名。

  对结果分组

  使用 GROUP BY 子句汇总查询结果

  使用 GROUP BY 子句组织查询结果中的行。分组的各唯一集都由查询结果中的单一行表示。GROUP BY 子句还用于指定将哪些列分组到一起,以便为 COUNT、SUM、 MIN、MAX 和 AVG 等列函数提供输入。清单 12 显示了 customer 表中的所有客户和他们所居住的城市名的列表:

清单 12. customer 表中的数据

FNAME      CITY 
 
Chris      Bartlesville 
Kim       Blue Island 
James      Brighton 
Bob       Cherry Hill 
Eileen     Denver 
Raymond     Los Altos 
Margaret    Los Altos 
Lana      Menlo Park 
Alfred     Menlo Park 
Jean      Mountain View 
George     Mountain View 
Dick      Oakland 
Charles     Palo Alto 
Philip     Palo Alto 
Fred      Phoenix 
Frank      Phoenix 
Cathy      Princeton 
Anthony     Redwood City 
Roy       Redwood City 
Donald     Redwood City 
Arnold     Redwood City 
Frank      Redwood City 

  以下示例展示了如何使用 GROUP BY 子句计数居住在各城市的客户的数量:

SELECT city, COUNT(city) TOTAL 
 FROM customer 
 GROUP BY city;

  COUNT 函数将在查询结果中生成以下行。您将城市指定为 GROUP BY 列并将 expression 列标记为 TOTAL。COUNT 函数将返回分组到各新行中的行数。

清单 13. 使用 COUNT 函数的 GROUP BY 表达式的结果

CITY            TOTAL 
 
Los Altos           2 
Mountain View         2 
Brighton            1 
Palo Alto           2 
Denver             1 
Oakland            1 
Phoenix            2 
Princeton           1 
Redwood City          5 
Blue Island          1 
Menlo Park           2 
Cherry Hill          1 
Bartlesville          1

  插入数据

  使用 INSERT 语句向表添加数据

  INSERT 语句用于向表或视图添加新行。在视图中插入行也会在该视图所基于的表中插入行。可以采用一些不同的方法在表中插入数据。我们来看 INSERT 语句的一些示例。

  以下示例使用 VALUES 子句向 customer 表插入了一个行:

INSERT INTO customer VALUES 
 (202,'Cerny', 'Paul', 'IBM ', '2 Main St','', 'Lenexa', 'KS',66012,''); 

  以下示例将从 customer 表中查找客户编号大于 120 的记录,并将相同的记录插入到临时的 temp_cust 表中:

SELECT * FROM customer 
 WHERE customer_num > 120 
 INSERT INTO TEMP temp_cust;

  以下示例使用 LOAD FROM 子句从 customer.txt 分隔文本文件向 customer 表插入记录。文本文件可以拥有一个或多个记录。

LOAD FROM customer.txt 
 INSERT INTO customer; 

  在连续列中插入值

  表可以在列中存储连续的数据类型(SERIAL, SERIAL8, BIGSERIAL)。数据库服务器将为连续列生成值。在插入值时,可以为连续列指定零值。数据库服务器将顺序生成下一个实际值。连续列不允许 NULL 值。

  您可以插入连续数、明确值或用于重设 SERIAL、SERIAL8 或 BIGSERIAL 列中的值的明确值。

  要插入连续的值,可以为 INSERT 语句中的连续列指定零值 (0)。在本例中,数据库服务器将分配下一个最大的值。数据库服务器为您的生成的值是列中大于最大值的某个值。

  要插入某个明确值,可以为连续列指定一个非零值,并且数据库服务器将使用该值。如果连续列拥有唯一的索引,或者有唯一的约束,并且您的值与表中的值重复,则会出现错误。如果连续列没有唯一约束,那么可以将值插入到连续列中。如果值大于当前的最大值,则会在连续序列中创建一个缺口。

  要在序列中创建一个缺口(即重置连续值),可以在列中指定一个大于当前最大值的正值。

  NULL 值在连续列中是无效的。如果在 DB-Access 中使用交互式模式编辑器定义表,则会自动为 SERIAL 列使用唯一的索引。

  更新数据

  使用 UPDATE 语句更改表或视图中的数据

  UPDATE 语句用于修改表或视图中的数据。您可以在 UPDATE 语句中使用 WHERE 子句来指定一个条件。表或视图中满足 WHERE 子句指定的条件的各行都将被修改。如果未指定 WHERE 子句,则会修改表中的所有行。以下示例使用 UPDATE 语句将特定订单编号的配送费用修改为 $15.00:

UPDATE orders 
 SET ship_charge = 15.00 
 WHERE order_num = 1001;

  删除数据

  使用 DELETE 语句清除表中的数据

  DELETE 语句用于从表中删除整行数据。您可以使用 DELETE 语句结合 WHERE 子句来指定一个条件。表中满足 WHERE 子句指定的条件的所有行都将被删除。如果未指定 WHERE 子句,则会删除表中的所有行。以下示例使用 DELETE 语句删除 orders 表中没有订单存货的所有记录:

DELETE FROM orders 
 WHERE backlog = 'n'; 

  事务概念

  理解事务

  事务是作为单一操作执行的一个或多个 SQL 语句。在关系数据库世界中,事务还表示一个工作单元。您可以把事务看作必须完全完成或根本不完成的修改序列。事务在 SQL 语句序列中的第一条语句执行之前开始。事务完成的条件是所有 SQL 语句成功执行,或者在出现错误的情况下,SQL 执行的更改回滚到序列中。事务可以预防出现不可预测的故障,并且可以在检测到逻辑错误时采用可编程的方式退出。

  您可以把事务看作 IDS 中由逻辑日志文件跟踪的活动。您在事务中执行的任何更改都将记录在逻辑日志文件中。在使用事务前考虑以下事项:

  必须为数据库开启日志功能。

  必须配置足够数量的逻辑日志文件。

  请阅读本系列 Informix Dynamic Server 11.50 基础考试 555 认证准备:第 3 部分:DBMS 实例和存储对象 的 “数据库日志模式” 部分,获取关于如何开启数据库日志功能的详细信息。

  在未使用 LOG MODE ANSI 选项创建的数据库中,使用 BEGIN WORK 语句发起一个事务,并发起 COMMIT WORK 语句结束事务。您还可以使用 ROLLBACK WORK 语句取消自 BEGIN WORK 之后完成的所有修改。

BEGIN WORK

BEGIN WORK
语句将开始一个事务。该语句仅对于支持事务日志的数据库有效。 UPDATE、DELETE 或 INSERT 语句在事务中影响的各行都将被锁定,并且将在整个事务过程中保持为锁定状态。

COMMIT WORK

COMMIT WORK
语句用于确认和终止事务。该语句将执行自事务之初对数据库执行的所有修改。 COMMIT WORK 语句将释放在事务过程中添加的所有行和表锁定。

ROLLBACK WORK

ROLLBACK WORK
语句用于取消和终止事务。通过将数据库恢复到事务开始之前的状态,该语句将事务开始后的所有更改设置为无效。 ROLLBACK WORK 语句将释放事务过程中添加的所有行和表锁定。

  WORK 关键字在 BEGIN WORK、 COMMIT WORK 和 ROLLBACK WORK 语句中为可选。

  以下示例显示了 BEGIN WORK 和 COMMIT WORK 语句绑定的事务:

BEGIN WORK; 
DELETE FROM orders WHERE backlog = 'n'; 
INSERT INTO STATE VALUES ('QB', 'Quebec'); 
COMMIT WORK;

  在本例中,您首先从 orders 表中删除 backlog 列等于 n 的行。然后,在 state 表中插入一个新行。事务将确保两个事务同时成功,或者都不成功。

  如果未成功发起 COMMIT WORK 语句,则 IDS 会在会话结束时回滚事务对数据库执行的所有修改。如果未发起 BEGIN WORK 语句启动事务,则各语句将在自己的事务中执行,并被认为是单语句事务。这些单语句事务不需要 BEGIN WORK 语句或 COMMIT WORK 语句。

  数据库日志的类型将定义管理 SQL 语句中的事务的方式。如果数据库使用 ANSI 日志模式,则不用使用 BEGIN WORK 来启动事务。事务将在 ANSI 兼容的数据库中隐式启动。您将使用 COMMIT 或 ROLLBACK 语句来结束事务。

  UDR

  用户定义的例程

  用户定义的例程(UDR)是在系统目录表中创建和注册的例程,并且将在 SQL 语句或其他例程中调用。

  UDR 可以是函数或者过程。作为例程的函数可以选择接受一组参数,并返回一组值。函数可以在 SQL 表达式中使用。作为例程的过程可以选择接受一组参数,但不会返回任何值。过程不能在 SQL 表达式中使用,因为它不返回任何值。

  可以使用 Informix 存储过程语言(SPL)或者受支持的外部语言(比如说 C 或者 Java® 技术)来编写用户定义的例程。

  Informix 存储过程语言(SPL)例程存储过程语言(SPL)是数据库服务器的一部分。SPL 易于使用,并且不需要在数据库服务器外部获得支持。SPL 为 SQL 提供流控制扩展。SPL 例程是一个使用 SPL 和 SQL 编写的 UDR。SPL 例程的主体包含 SQL 语句和流控制语句(用于循环和分支)。SPL 例程经过了尽可能的优化和解析,然后以可执行的格式存储在系统目录表中。如果某应用程序在执行 SQL 任务只需要很少或者不需要任何用户交互,则执行各任务的例程可以存储在数据库服务器上。使用 SPL 例程有若干显著的优势:

  使用 SPL 例程可显著降低处理开销并极大缩短处理时间。每次在执行 SQL 语句时,它都会从客户机发送到服务器,并且在实际执行其操作之前都需要经过优化和解析过程。使用 SPL 例程后,解析和优化将成为例程创建过程中的一部分,因此执行的速度更快。

  存储过程将减少网络流量。一天中可能需要重复执行复杂的 SQL 语句若干次,这会降低网络速度。您可以将相同的 SQL 语句放在相同的存储过程中,以便于显著减少网络流量。这样,只有调用存储过程的语句才会经过网络。

  可以采用许多不同的方式来使用 SPL 过程或函数:

  您可以从触发器调用它们来扩展触发器的功能。

  它们可以嵌入在 SQL 语句,或者使用 EXECUTE PROCEDURE 语句调用它们。

  您可以通过传递参数使它们变为动态。

  外部语言例程外部语言例程是使用外部语言编写的 UDR。外部语言例程的主体包含流控制和循环等操作语句。因此,您必须使用适当的编译工具将外部语言例程解析和编译为可执行的格式。数据库服务器支持使用 C 和 Java 技术语言编写的 UDR。

外部例程通常用于对用户定义的数据类型执行操作。本教程不会详细讨论外部语言例程。有关详细信息,请参阅 IBM Informix Database Server 文档。

  创建 SPL 例程

  SPL 例程是使用 SQL 和 SPL 编写的用户定义的例程。在创建存储过程之前,您必须连接到数据库。创建过程操作将执行解析、优化和转换到 ASCII 操作,然后才实际存储到 sysprocedures 系统目录表中。您可以使用 CREATE PROCEDURE 语句创建一个用户定义的例程。如果创建使用 C 或 Java 技术语言编写的过程,则应将源代码存放在一个单独的文件中并使用 CREATE PROCEDURE FROM 语句。

  清单 14 是一个简单的创建存储过程的示例,它向列中的一些列中插入了一些整型值:

清单 14. 创建 SPL 过程的示例

CREATE PROCEDURE insert_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;

  在 IDS 中,虽然可以使用 CREATE PROCEDURE 编写和注册返回一个或多个值的 SPL 例程(也就是一个 SPL 函数),但仍然建议您使用 CREATE FUNCTION 作为替代。要注册外部函数,必须使用 CREATE FUNCTION。

  要创建返回一个或多个值的 SPL 函数,使用 CREATE FUNCTION 关键字作为开始,并使用 END FUNCTION 关键字作为结束。清单 15 显示了如何开始和结束 SPL 函数:

清单 15. 创建 SPL 函数的示例

CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument 
  RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2), 
   CHAR(5); -- 6 return values 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, address1, 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; 

  Informix Dynamic Server 允许您创建多个使用相同名称但参数不同的 SPL 例程。此特性被称作例程重载。举例来说,您可以在数据库中创建名称为 read_address 的以下数据库。

清单 16. 例程重载的示例

CREATE FUNCTION read_address (customer_id INT ) -- one argument 
  RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), 
   CHAR(5); -- 6 items 
 
  DEFINE p_lname,p_fname,p_lastname,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, lname, address1, city, state, zipcode 
   INTO p_fname, p_lastname, p_add, p_city, p_state, p_zip 
   FROM customer 
   WHERE customer_num = customer_id; 
 
  RETURN p_fname, p_lastname, p_add, p_city, p_state, p_zip; 
   --6 items 
END FUNCTION; 

  由于例程过载的问题,SPL 例程不可以在 IDS 使用名称唯一标识。但是,可以通过特定名 来唯一标识例程。特定名 是您在 CREATE PROCEDURE 或 CREATE FUNCTION 语句中除例程名称之外定义的唯一标识符。特定的名称使用 SPECIFIC 关键字定义,并且在数据库中是唯一的。相同数据库中的两个例程不能使用相同的特定名,即使它们的所有者不同。

  清单 17 展示了如何在创建 read_address 函数的 CREATE FUNCTION 语句中定义特定名。我们将 read_address_bylastame 声明为按 lastname 查找地址的函数的特定名,并将 read_address_bycustid 声明为按客户编号查找地址的函数的特定名。

清单 17. 使用特定名的的函数的示例

CREATE FUNCTION read_address (lastname CHAR(15)) -- one argument 
  RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), 
   CHAR(5) -- 6 items 
SPECIFIC read_address_bylastame; 
 
...... . 
...... . 
END FUNCTION;

清单 18. 使用不同特定名的相同函数的示例

CREATE FUNCTION read_address    (lastname CHAR(15)) -- one argument 
  RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2), 
   CHAR(5) -- 6 items 
SPECIFIC read_address_bycustid; 
 
...... . 
...... . 
END FUNCTION;

  执行 SPL 例程

  您可以使用 EXECUTE PROCEDURE 或 EXECUTE FUNCTION 来执行 SPL 例程或外部例程。通常,最好是使用 EXECUTE PROCEDURE 来执行 SPL 过程,而使用 EXECUTE FUNCTION 来执行 SPL 函数。可以从 DB-Access、SPL 例程或者外部例程来调用存储过程。SPL 例程可以执行使用外部语言编写的例程,并且外部语句例程可以执行 SPL 例程。

  以下示例显示了名称为 insert_rows() 的 SPL 过程的执行。

EXECUTE PROCEDURE insert_rows(10);

  要执行函数,您需要考虑使用 INTO 子句来存储返回值。清单 19 中的示例展示了名称为 update_customer() 的 SPL 函数的执行,它接受两个输入参数并使用 INTO 子句指定存储返回值的变量:

清单 19. SPL 函数执行的示例

CREATE PROCEDURE get_customer_info(cust_num INT) 
  DEFINE lname, fname, city CHAR(15); 
  DEFINE add CHAR(20); 
  DEFINE state CHAR(2); 
  DEFINE zip CHAR(5); 
  EXECUTE FUNCTION read_address(cust_num INT) 
INTO fname, lastname, add, city, state, zip; 
END PROCEDURE;

  您将使用 CALL 语句从 SPL 例程中执行 UDR。CALL 语句的行为与 EXECUTE PROCEDURE 和 EXECUTE FUNCTION 语句相同,但您只能在 SPL 例程中使用 CALL。清单 20 中的示例从 SPL 例程 add_numbers 中调用 read_address 函数:

清单 20. 调用 UDR 的示例

CREATE PROCEDURE get_customer_info(cust_num INT) 
  DEFINE lname, fname, city CHAR(15); 
  DEFINE add CHAR(20); 
  DEFINE state CHAR(2); 
  DEFINE zip CHAR(5); 
  CALL read_address(cust_num) RETURNING fname, lastname, add, city, state, zip; 
END PROCEDURE;

  存储过程的必要权限

  存储过程共有三种与之相关的权限水平:

  数据库级权限

  语言级权限

  例程级权限

  首先,您需要具备数据库 Resource 权限才能在该数据库中创建任何种类的 SPL 过程或 SPL 函数。默认情况下,SPL 的 Usage 权限将授予 PUBLIC。以下示例展示了如何将数据库的 Resource 权限授予程序员角色:

GRANT RESOURCE TO programmers;

  其次,您需要 SPL 语言的 Usage 权限才能创建 SPL 过程。SPL 语言的 Usage 权限默认将授予 PUBLIC。只有用户 informix、DBA 或者授予 Usage 权限 WITH GRANT OPTION 的用户才可以将 SPL 的 Usage 权限授予其他用户。以下示例展示了如何将 SPL 语言的 Usage 权限授予程序员角色:

GRANT USAGE ON LANGUAGE SPL TO programmers;

  最后,您还需要例程执行许可。在注册 UDR 时,您会自动接受该例程的 Execute 权限。Execute 权限允许您调用 UDR。以下示例展示了如何将 add_numbers SPL 例程的 Execute 权限授予程序员角色:

GRANT EXECUTE ON add_numbers TO programmers;

  抛弃存储过程

  您可以使用 DROP PROCEDURE 语句从数据库抛弃过程。但是,只有过程的所有者和具备 DBA 权限的用户才能抛弃过程。您还可以使用 DROP ROUTINE 语句从数据库中删除 UDR。抛弃 UDR 将从数据库中删除文本和可执行版本的 UDR。以下示例展示了如何抛弃存储过程:

DROP PROCEDURE insert_rows;

  如果有多个使用相同名称的过程,那么您还可以在过程名称后面包括参数数据类型,用于标识过程。举例来说,以下语句可以用于抛弃过载函数 read_address:

drop procedure read_address(INT); 
drop procedure read_address(CHAR);

  如果在创建过程时声明了一个特定名,那么可以稍后在修改、抛弃、授予权限、撤回权限或更新过程的统计数据时使用该名称。

  以下示例抛弃了一个使用特定名的过载函数:

drop specific procedure read_address_bylastname; 
drop specific procedure read_address_bycustid; 

  使用函数创建的索引

  使用函数索引

  IDS 还支持使用函数来创建索引。创建和使用函数索引可以显著改善 SELECT 查询的性能。索引以排序的顺序存储列值。函数索引将转换列中的数据并以排序的顺序存储生成的值。

  假设某个表单独存储客户的姓和名。但是,需求是运行需要搜索客户全名(姓和名)的查询。在此场景中,您需要将姓和名连接在一起,以便生成所需的结果。以下查询示例将搜索姓以 L 开头而名以 P 开头的客户:

SELECT * FROM customer 
 WHERE TRIM(fname) || " " || TRIM(lname) LIKE 'L% P%';

  如果表比较大或者许多会话发起了这类查询,则性能会受到负面影响。改善此查询的一种方法是对姓名创建一个函数索引。

清单 21. 连接两个列的函数示例

CREATE FUNCTION fullname ( fname CHAR(15), lname CHAR(15) ) RETURNS VARCHAR(31) 
    WITH (NOT VARIANT); 
 
    RETURN TRIM(fname) || " " || TRIM(lname); 
END FUNCTION;

  下面是如何创建一个函数索引:

CREATE INDEX fullnameIndex ON customer (fullname (fname,lname) );

  现在,对姓名执行查询时,数据库服务器便可以使用函数索引 fullnameIndex 来确定哪些行能满足查询。通过在 INSERT、UPDATE、MERGE 和 DELETE 操作的过程中更新函数索引,IDS 将确保函数索引始终与表中的数据库保持一致。

  结束语

  本教程向您介绍了结构化查询语言(SQL)以及 IDS 使用 SQL 操作关系数据库中的数据的一些方法。它还涵盖了 SQL 的基础知识,包括 SQL 语言元素、数据操作语言(DML)、SQL 过程和用户定义的函数。

编缉推荐阅读以下文章

  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 9 部分: Informix 复制技术
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 8 部分: 备份和恢复
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 7 部分: 工具和实用程序(下)
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 7 部分: 工具和实用程序(上)
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 6 部分: 数据并发性
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 4 部分: 检查数据库对象
  • Informix Dynamic Server 11.50 基础考试 555 认证准备: 第 3 部分:DBMS 实例和存储对象
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 2 部分: 安全性
  • Informix Dynamic Server 11.50 基础考试 555 认证准备,第 1 部分: IDS 计划和安装

本文来自[教育资源网]edu.5151doc.com收集与整理,感谢原作者。
本文版权归原作者所有,如需转载或摘录请注明出处:教育资源网 http://edu.5151doc.com

文章录入:魅力教师    责任编辑:5151doc 
【字体: 】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口
| 设为首页 | 加入收藏 | 联系站长 | 在线投稿 | 版权申明 | 网站登陆 |