DB2 9 基础（730 考试）认证指南，第 4 部分: 处理 DB2 数据

　2009-01-22 16:39:07　来源：WEB开发网　　　

核心提示：结构化查询语言（Structured Query Language，SQL）SQL 的组成部分SQL 是一种用来定义和操纵数据库对象的语言，DB2 9 基础（730 考试）认证指南，第 4 部分: 处理 DB2 数据，使用 SQL 定义数据库表、将数据插入表中、修改表中的数据和从表中检索数据，与所有语言一样，第 3 部

结构化查询语言（Structured Query Language，SQL）

SQL 的组成部分

SQL 是一种用来定义和操纵数据库对象的语言。使用 SQL 定义数据库表、将数据插入表中、修改表中的数据和从表中检索数据。与所有语言一样，SQL 也定义了语法和一套语言元素。

大多数 SQL 语句包含一个或多个以下语言元素：

单字节的字符可以是字母（A-Z、a-z、$、# 和 @，或某个扩展字符集的成员）、数字（0-9）或特殊字符（包括逗号、星号、加号、百分号、与符号等等）。

标记（token）是包含一个或多个字符的序列。它不能包含空白字符，除非它是被限界的标识符（由双引号包围的一个或多个字符）或字符串常量。

SQL 标识符是用来形成名称的标记。

值的数据类型决定 DB2 如何解释这个值。DB2 支持许多内置的数据类型，还支持用户定义的类型（UDT）。

常量指定一个值。它们分为字符、图形或十六进制字符串常量，以及整数、小数或浮点数字常量。

特殊寄存器是数据库管理程序为一个应用程序进程定义的一个存储区域，用来存储可以在 SQL 语句中引用的信息。特殊寄存器的示例是 CURRENT DATE、CURRENT DBPARTITIONNUM 和 CURRENT SCHEMA。

例程可以是函数、方法或过程。

函数表示一个或多个输入数据值与一个或多个结果值之间的关系。数据库函数可以是内置的或用户定义的。

列（即聚合）函数对一个列中的一组值进行操作，返回单一值。例如：

SUM(sales) 返回 Sales 列中值的总和。

AVG(sales) 返回 Sales 列中值的平均值（即总和除以值的数量）

MIN(sales) 返回 Sales 列中的最小值。

MAX(sales) 返回 Sales 列中的最大值。

COUNT(sales) 返回 Sales 列中非空值的数量。

标量函数对单一值进行操作，返回另一个单一值。例如：

ABS(-5) 返回 -5 的绝对值，即 5。

HEX(69) 返回数字 69 的十六进制表示，即 45000000。

LENGTH('Pierre') 返回字符串 “Pierre” 中的字节数量，即 6。对于 GRAPHIC 字符串，LENGTH 函数返回双字节字符的数量。

YEAR('03/14/2002') 提取 03/14/2002 的年份部分，即 2002。

MONTH('03/14/2002') 提取 03/14/2002 的月份部分，即 3。

DAY('03/14/2002') 提取 03/14/2002 的日部分，即 14。

LCASE('SHAMAN') 或 LOWER('SHAMAN') 返回已经转换为全小写字符的字符串，即 ‘shaman’。

UCASE('shaman') 或 UPPER('shaman') 返回已经转换为全大写字符的字符串，即 ‘SHAMAN’。

用户定义的函数使用 CREATE FUNCTION 语句注册到数据库的系统编目中（可以通过 SYSCAT.ROUTINES 编目视图访问）。

方法也是一组输入数据值和一组结果值之间的关系。但是，数据库方法是作为用户定义的结构化类型的一部分隐式或显式地定义的。例如，一个称为 CITY 的方法（类型为 ADDRESS）接受 VARCHAR 类型的输入值，结果是 ADDRESS 的一个子类型。用户定义的方法使用 CREATE METHOD 语句注册到数据库的系统编目中（可以通过 SYSCAT.ROUTINES 编目视图访问）。关于结构化类型的更多信息，请参考 DB2 基础: 结构化数据类型和类型化表简介。

过程是一个可以通过执行 CALL 语句来启动的应用程序。过程的参数是不同类型的标量值，可以用来将值传递进过程中、接受过程的返回值或者同时有这两种作用。用户定义的过程使用 CREATE PROCEDURE 语句注册到数据库的系统编目中（可以通过 SYSCAT.ROUTINES 编目视图访问）。

表达式指定一个值。有字符串表达式、算术表达式和情况表达式，情况表达式可以用来根据对一个或多个条件的计算来指定某一结果。

谓词（predicate）指定关于给定的行或组的一个条件，结果可以是真、假或未知。谓词有几个子类型：

基本谓词对两个值进行比较（例如，x > y）。

BETWEEN 谓词将一个值与一个值范围进行比较。

EXISTS 谓词测试某些行是否存在。

IN 谓词判断一个或多个值是否在一个值集合中。

LIKE 谓词搜索具有某一模式的字符串。

NULL 谓词测试空值。

数据操纵语言（Data Manipulation Language，DML）

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

SELECT 语句用来检索表或查看数据。最简单形式的 SELECT 语句可以用来检索一个表中的所有数据。例如，要从 SAMPLE 数据库中检索所有 STAFF 数据，应该发出以下命令：

SELECT * FROM staff

下面是这个查询返回的部分结果集：

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
10	Sanders	20	Mgr	7	18357.50	-
20	Pernal	20	Sales	8	18171.25	612.45
30	Marenghi	38	Mgr	5	17506.75	-

要限制结果集中行的数量，可以使用 FETCH FIRST 子句，例如：

SELECT * FROM staff FETCH FIRST 10 ROWS ONLY

可以通过指定选择列表从表中检索特定的列，选择列表由逗号分隔的列名组成。例如：

SELECT name, salary FROM staff

使用 DISTINCT 子句消除结果集中的重复行。例如：

SELECT DISTINCT dept, job FROM staff

使用 AS 子句给选择列表中的表达式或项目分配一个有意义的名称。例如：

SELECT name, salary + comm AS pay FROM staff

如果没有 AS 子句，派生的列会命名为 2，这表示它是结果集中的第二列。

使用 WHERE 子句和谓词限制查询返回的数据量

使用 WHERE 子句指定一个或多个搜索标准（即搜索条件），从而从表或视图选择特定的行。搜索条件由一个或多个谓词组成。谓词指定关于行的某一情况（参见 SQL 的组成部分）。在构建搜索条件时，要确保：

算术操作只应用于数字数据类型

只在可比较的数据类型之间进行比较

将字符值包围在单引号中

字符值应该指定为与数据库中的值完全一样

我们来看几个示例。

寻找工资超过 $20,000 的职员的姓名："SELECT name, salary FROM staff 　WHERE salary > 20000"

将语句包围在双引号中，可以防止操作系统错误地解释特殊字符，比如 * 或 >；如果不这么做，大于号会被解释为输出重定向请求。

列出工资超过 $20,000 的不是经理的职员的姓名、头衔和工资："SELECT name, job, salary FROM staff 　WHERE job <> 'Mgr' 　AND salary > 20000"

寻找以字母 S 开头的所有姓名：SELECT name FROM staff 　WHERE name LIKE 'S%'

在这个示例中，百分号（%）是一个通配符，代表零个或多个字符的字符串。

子查询（subquery）是主查询的 WHERE 子句中出现的 SELECT 语句，它将结果集提供给 WHERE 子句。例如：

"SELECT lastname FROM employee 　WHERE lastname IN 　(SELECT sales_person FROM sales 　　WHERE sales_date < '01/01/1996')"

相关名称（correlation name）是在查询的 FROM 子句中定义的，可以作为表的简短名称。相关名称还可以消除对来自不同表的相同列名的二义性引用。例如：

"SELECT e.salary FROM employee e 　WHERE e.salary < 　(SELECT AVG(s.salary) FROM staff s)"

使用 ORDER BY 子句对结果进行排序

使用 ORDER BY 子句按照一个或多个列中的值对结果集进行排序。ORDER BY 子句中指定的列名不一定在选择列表中指定。例如：

"SELECT name, salary FROM staff 　WHERE salary > 20000 　ORDER BY salary"

在 ORDER BY 子句中指定 DESC 可以对结果集进行降序排序：

ORDER BY salary DESC

使用联结从多个表中检索数据

联结（join）是一种将来自两个或更多表中的数据组合起来的查询。常常需要从两个或更多的表中选择信息，因为所需的数据常常是分散的。联结将列添加到结果集中。例如，对两个具有三列的表进行完全联结，会产生具有六列的结果集。

最简单的联结中没有指定条件。例如：

SELECT deptnumb, deptname, manager, id, name, dept, job 　FROM org, staff

这个语句从 ORG 表和 STAFF 表返回列的所有组合。前三列来自 ORG 表，后四列来自 STAFF 表。这样的结果集（两个表的叉积（cross product））没什么用处。需要用一个联结条件（join condition）来调整结果集。例如，下面这个查询标识出那些是经理的职员：

SELECT deptnumb, deptname, id AS manager_id, name AS manager 　FROM org, staff 　WHERE manager = id 　ORDER BY deptnumb

下面是这个查询返回的部分结果集：

DEPTNUMB	DEPTNAME	MANAGER_ID	MANAGER
10	Head Office	160	Molinare
15	New England	50	Hanes
20	Mid Atlantic	10	Sanders

前面的语句是一个内部联结的示例。内部联结（inner join）只返回叉积中满足联结条件的行。如果一行在一个表中存在，但是在另一个表中不存在，它就不包含在结果集中。要显式地指定内部联结，可以重新编写前面的查询，在 FROM 子句中添加 INNER JOIN 操作符：

... 　FROM org INNER JOIN staff 　ON manager = id ...

关键字 ON 为进行联结的表指定联结条件。DeptNumb 和 DeptName 是 ORG 表中的列，而 Manager_ID 和 Manager 基于 STAFF 表中的列（ID 和 Name）。在内部联结的结果集中，行的值分别匹配左表（ORG）以及右表（STAFF）中的 Manager 和 ID 列。（在两个表上执行联结时，可以任意指定一个表为左表，另一个表为右表。）

外部联结（Outer join）返回内部联结操作产生的行，加上内部联结操作不会返回的行。有三种类型的外部联结：

左外部联结包括内部联结，加上左表中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 LEFT OUTER JOIN（或 LEFT JOIN）操作符。

右外部联结包括内部联结，加上右表中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 RIGHT OUTER JOIN（或 RIGHT JOIN）操作符。

完全外部联结包括内部联结，加上左表和右表中内部联结操作不会返回的行。这种联结在 FROM 子句中使用 FULL OUTER JOIN（或 FULL JOIN）操作符。

回答更复杂的问题需要构造更复杂的查询。下面的查询生成负责项目的职员的列表，通过列出他们管理的部门标识出是经理的那些职员：

SELECT empno, deptname, projname 　FROM (employee 　LEFT OUTER JOIN project 　ON respemp = empno) 　LEFT OUTER JOIN department 　ON mgrno = empno

第一个外部联结获得职员负责的任何项目的名称；这个外部联结包围在圆括号中并首先被解析。第二个外部联结获得是经理的职员的部门名称。

使用 UNION 集合操作符将两个或更多的查询组合成一个查询

使用 UNION 操作符、EXCEPT 或 INTERSECT 将两个或更多的查询组合成一个查询。集操作符对查询的结果进行处理、消除重复并返回最终的结果集。

UNION 操作符将两个或更多的结果表组合在一起，生成一个结果表。

EXCEPT 集合操作符生成的结果表中包含第一个查询返回的所有行，但是去掉了第二个或任何后续查询返回的行。

INTERSECT 集合操作符生成的结果表只包含所有查询都返回的行。

下面是一个使用 UNION 集合操作符的查询示例。同样的查询可以使用 EXCEPT 或 INTERSECT 集合操作符替代关键字 UNION。

"SELECT sales_person FROM sales 　WHERE region = 'Ontario-South' UNION SELECT sales_person FROM sales 　WHERE sales > 3"

使用 GROUP BY 子句对结果进行汇总

使用 GROUP BY 子句对结果集中的行进行组织。每个组在结果集中由一行表示。例如：

SELECT sales_date, MAX(sales) AS max_sales FROM sales 　GROUP BY sales_date

这个语句从 SALES 表中返回销售日期的列表。SAMPLE 数据库中的 SALES 表包含销售数据，包括特定销售人员在特定日期完成的成功交易的数量。通常在每天有多个记录。GROUP BY 子句按日期对数据进行分组，这个示例中的 MAX 函数返回每个销售日期记录的最大销售数量。

另一个风格的 GROUP BY 子句要指定 GROUPING SETS 子句。分组集可以用来在一遍中分析不同聚合层次上的数据。例如：

SELECT YEAR(sales_date) AS year, region, SUM(sales) AS tot_sales 　FROM sales 　GROUP BY GROUPING SETS (YEAR(sales_date), region, () )

在这里，YEAR 函数用来返回日期值的年份部分，SUM 函数用来返回每个分组的销售区间的总量。分组集列表指定如何对数据进行分组，即聚合。在分组集列表中添加一对空的圆括号，可以获得结果集中的总量。这个语句返回以下结果：

YEAR	REGION	TOT_SALES
-	-	155
-	Manitoba	41
-	Ontario-North	9
-	Ontario-South	52
-	Quebec	53
1995	-	8
1996	-	147

如果一个语句与前面的语句几乎相同，但是指定 ROLLUP 子句或 CUBE 子句而不是 GROUPING SETS 子句，那么它返回的结果集会提供更详细的数据透视图。它可以根据位置或时间进行汇总。

HAVING 子句常常与 GROUP BY 子句一起使用，从而检索出满足特定条件的组的结果。HAVING 子句可以包含一个或多个谓词，将组的某一属性与组的另一个属性或常量进行比较。例如：

"SELECT sales_person, SUM(sales) AS total_sales FROM sales 　GROUP BY sales_person 　HAVING SUM(sales) > 25"

这个语句返回销售总量超过 25 的销售人员的列表。

使用 INSERT 语句在表或视图中添加新的行

INSERT 语句用来在表或视图中添加新的行。在视图中插入一个新行也会在视图基于的表中插入这一行。

使用 VALUES 子句为一行或多行指定列数据。例如：INSERT INTO staff VALUES (1212,'Cerny',20,'Sales',3,90000.00,30000.00) INSERT INTO staff VALUES (1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)

下面的语句是等效的：INSERT INTO staff (id, name, dept, job, years, salary, comm) 　VALUES 　(1212,'Cerny',20,'Sales',3,90000.00,30000.00), 　(1213,'Wolfrum',20,'Sales',2,90000.00,10000.00)

可以指定完全选择来标识出要从其他表或视图复制的数据。完全选择（fullselect）是产生结果表的语句。例如：CREATE TABLE pers LIKE staff INSERT INTO pers 　SELECT id, name, dept, job, years, salary, comm 　　FROM staff 　　WHERE dept = 38

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

UPDATE 语句用来修改表或视图中的数据。通过指定 WHERE 子句，可以修改满足条件的每一行的一个或多个列的值。例如：

UPDATE staff 　SET dept = 51, salary = 70000 　　WHERE id = 750

下面的语句是等效的：

UPDATE staff 　SET (dept, salary) = (51, 70000) 　　WHERE id = 750

如果没有指定 WHERE 子句，DB2 就会更新表或视图中的每一行！

使用 DELETE 语句删除数据

DELETE 语句用来从表中删除整行的数据。通过指定 WHERE 子句，删除满足条件的每一行。例如：

DELETE FROM staff 　WHERE id IN (1212, 1213)

如果没有指定 WHERE 子句，DB2 就会删除表中的所有行！

使用 MERGE 语句将有条件更新、插入或删除操作组合起来

MERGE 语句使用来自源表的数据更新目标表或可更新视图。仅仅用一个操作，目标表中与源表匹配的行就可以被更新或删除，目标表中不存在的行被插入。

例如，将 EMPLOYEE 表作为目标表，其中包含某大公司的职员的最新信息。分支办公室通过维护自己的 EMPLOYEE 表版本 MY_EMP 来处理本地职员记录的更新。可以使用 MERGE 语句用 MY_EMP 表（合并操作的源表）中包含的信息来更新 EMPLOYEE 表。

以下语句将编号为 000015 的新职员的行插入 MY_EMP 表。INSERT INTO my_emp (empno, firstnme, midinit, lastname, workdept, 　phoneno, hiredate, job, edlevel, sex, birthdate, salary) 　VALUES ('000015', 'MARIO', 'M', 'MALFA', 'A00', 　'6669', '05/05/2000', 'ANALYST', 15, 'M', '04/02/1973', 59000.00)

并发出以下语句更新 MY_EMP 表中现有职员 000010 的工资数据。INSERT INTO my_emp (empno, firstnme, midinit, lastname, edlevel, salary) 　VALUES ('000010', 'CHRISTINE', 'I', 'HAAS', 18, 66600.00)

现在，插入的数据只存在于 MY_EMP 表中，因为它还没有与 EMPLOYEE 表进行合并。下面的 MERGE 语句获取 MY_EMP 表的内容并将它们合并到 EMPLOYEE 表中。MERGE INTO employee AS e 　USING (SELECT 　 empno, firstnme, midinit, lastname, workdept, phoneno, 　 hiredate, job, edlevel, sex, birthdate, salary 　 FROM my_emp) AS m 　ON e.empno = m.empno 　WHEN MATCHED THEN 　　UPDATE SET (salary) = (m.salary) 　WHEN NOT MATCHED THEN 　　INSERT (empno, firstnme, midinit, lastname, workdept, phoneno, 　　 hiredate, job, edlevel, sex, birthdate, salary) 　　　VALUES (m.empno, m.firstnme, m.midinit, m.lastname, 　　　 m.workdept, m.phoneno, m.hiredate, m.job, m.edlevel, 　　　 m.sex, m.birthdate, m.salary)

给源表和目标表都分配了相关名称，以避免搜索条件中的表引用产生二义性。这个语句指定了 MY_EMP 表中应该考虑的列。语句还指定当 MY_EMP 中的行在 EMPLOYEE 表中有匹配时或没有匹配时，应该采取什么操作。

现在，对 EMPLOYEE 表执行以下查询会返回职员 000015 的记录：SELECT * FROM employee WHERE empno = '000015'

以下查询会返回职员 000010 的记录，其中 SALARY 列的值是更新后的值：SELECT * FROM employee WHERE empno = '000010'

使用数据修改-表引用子句在同一个工作单元中获得中间结果集

假设您想在同一个工作单元（UOW）中给职员 000220 加薪 7% 并检索她原来的工资。可以使用数据修改-表引用子句来实现，这个子句是 SQL 语句中 FROM 子句的一部分。SELECT salary FROM OLD TABLE ( 　UPDATE employee SET salary = salary * 1.07 　WHERE empno = '000220' ); SALARY ----------- 　 29840.00 　1 record(s) selected.

数据修改操作（插入、更新或删除）的目标中的列变成中间结果表中的列，可以在查询的选择列表中按名称引用这些列（在这个示例中是 Salary）。关键字 OLD TABLE 指定中间结果表应该包含数据修改操作之前的值。关键字 NEW TABLE 指定中间结果表应该包含数据修改操作之后（在发生引用完整性计算和触发操作后触发器之前）的值。关键字 FINAL TABLE 指定中间结果表应该包含数据修改操作、引用完整性计算和触发操作后触发器之后的值。

假设有一个 CUSTOMERS 表，其定义为：CREATE TABLE customers ( 　cust_id INTEGER GENERATED ALWAYS AS IDENTITY ( 　 START WITH 10001 　), 　cust_name VARCHAR(12), 　PRIMARY KEY (cust_id) );

这个表的主键 Cust_ID 是自动生成的标识列。可以使用数据修改-表引用子句检索生成的标识列值，这个值用作顾客编号。SELECT * FROM FINAL TABLE ( 　INSERT INTO customers (cust_name) VALUES ('Lamarr') ); CUST_ID　　 CUST_NAME ----------- ------------ 　　　10001 Lamarr 　1 record(s) selected.

COMMIT 和 ROLLBACK 语句和事务边界

工作单元和保存点

工作单元（unit of work，UOW）也称为事务，它是应用程序进程中一个可恢复的（recoverable）操作序列。UOW 的经典示例是简单的银行转帐事务，即把资金从一个帐号转到另一个帐号中。在应用程序从一个帐号减去一定数量的资金之后，数据库会出现不一致的状态；在第二个帐号中增加同样数量的资金之后，这种不一致才会消除。当这些修改已经提交之后，其他应用程序才能使用它们。

当应用程序进程中的第一个 SQL 语句对数据库发出时，一个 UOW 隐式地开始。同一个应用程序后续的所有读写操作被认为是同一个 UOW 的一部分。应用程序可以在适当的时候发出 COMMIT 或 ROLLBACK 语句来结束 UOW。COMMIT 语句将这个 UOW 中所做的所有修改持久化，而 ROLLBACK 语句撤消这些修改。如果应用程序正常地结束，而没有发出显式的 COMMIT 或 ROLLBACK 语句，那么 UOW 会自动地提交。如果应用程序在 UOW 结束之前意外地终止，那么这个工作单元会自动地回滚。

保存点（savepoint）允许选择性地回滚组成 UOW 的操作子集，这样就不会丢失整个事务。可以嵌套保存点并可以同时拥有几个活跃的保存点级别（savepoint level）；这允许应用程序根据需要回滚到特定的保存点。假设在某个 UOW 中定义了三个保存点（A、B 和 C）：do some work; savepoint A; do some more work; 　savepoint B; 　do even more work; 　 savepoint C; 　 wrap it up; 　roll back to savepoint B;

回滚到保存点 B 会自动地释放保存点 C，但是保存点 A 和 B 仍然是活跃的。

SQL 过程和用户定义的函数

创建和调用 SQL 过程

SQL 过程是过程体用 SQL 编写的过程。过程体包含 SQL 过程的逻辑。它可以包含变量声明、条件处理、流控制语句和 DML。可以在复合语句（compound statement）中指定多个 SQL 语句，复合语句将几个语句组合成一个可执行块。

当成功地调用 CREATE PROCEDURE (SQL) 语句时，就会创建一个 SQL 过程，这会在应用服务器上定义 SQL 过程。SQL 过程是一种定义比较复杂的查询或任务的简便方式，可以在需要时调用它们。

创建 SQL 过程的一种简便方法是在命令行处理程序（CLP）脚本中编写 CREATE PROCEDURE (SQL) 语句。例如，如果将下面的语句放在一个称为 createSQLproc.db2 的文件中，就可以执行这个文件来创建 SQL 过程：

连接 SAMPLE 数据库。

发出以下命令：db2 -td@ -vf createSQLproc.db2

db2 命令指定 -td 选项标志，这让命令行处理程序使用 @ 作为语句终止字符（因为在过程体内已经使用分号作为语句终止字符）；-v 选项标志让命令行处理程序将命令文本回显到标准输出；-f 选项标志让命令行处理程序从指定的文件（而不是标准输入）读取命令输入。

CREATE PROCEDURE sales_status (IN quota INTEGER, OUT sql_state CHAR(5)) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN 　DECLARE SQLSTATE CHAR(5); 　DECLARE rs CURSOR WITH RETURN FOR 　SELECT sales_person, SUM(sales) AS total_sales 　　FROM sales 　　GROUP BY sales_person 　　HAVING SUM(sales) > quota; 　OPEN rs; 　SET sql_state = SQLSTATE; END @

这个过程称为 SALES_STATUS，它接受一个输入参数 quota 并返回输出参数 sql_state。过程体中只有一个 SELECT 语句，它返回销售总量超过指定额度的销售人员的姓名和销售总量。

大多数 SQL 过程接受至少一个输入参数。在我们的示例中，输入参数包含一个值（quota），这个值用在过程体包含的 SELECT 语句中。

许多 SQL 过程返回至少一个输出参数。我们的示例包含一个输出参数（sql_state），这个参数用来报告 SQL 过程是成功还是失败。DB2 返回一个 SQLSTATE 值来响应条件，可以作为 SQL 语句的结果。因为返回的 SQLCODE 或 SQLSTATE 值属于过程体中发出的最后一个 SQL 语句，而且访问这些值会改变这些变量的后续值（因为访问它们需要使用 SQL 语句），所以应该将 SQLCODE 或 SQLSTATE 值赋值给一个局部定义的变量并通过它返回（比如我们示例中的 sql_state 变量）。

SQL 过程的参数列表可以指定零个或更多的参数，每个参数可以是三种类型之一：

IN 参数将一个输入值传递给 SQL 过程；在过程体内不能修改这个值。

OUT 参数从 SQL 过程返回一个输出值。

INOUT 参数将一个输入值传递给 SQL 过程并从 SQL 过程返回一个输出值。

SQL 过程可以返回零个或更多的结果集。在我们的示例中，SALES_STATUS 过程返回一个结果集。返回结果集的方法是：

在 DYNAMIC RESULT SETS 子句中声明 SQL 过程返回的结果集数量。

在过程体中为返回的每个结果集声明一个游标（使用 WITH RETURN FOR 子句）。游标（cursor）是一个命名的控制结构，应用程序使用它指向有序行集中的特定行。游标用来从行集中检索行。

打开返回的每个结果集的游标。

当 SQL 过程返回时，让游标打开着。

变量必须在 SQL 过程体的开头进行声明。要声明一个变量，应该分配一个惟一的标识符并指定变量的 SQL 数据类型，还可以可选地分配一个初始值。

我们的 SQL 过程示例中的 SET 子句是一个流控制子句。在 SQL 过程体中可以使用以下的流控制语句、结构和子句来进行有条件处理：

CASE 结构根据对一个或多个条件的计算选择一个执行路径。

FOR 结构对于表中的每一行执行一个代码块。

GET DIAGNOSTICS 语句将关于前一个 SQL 语句的信息返回到一个 SQL 变量中。

GOTO 语句将控制转移到一个有标签的块（一个或多个语句的块，由一个惟一的 SQL 名称和冒号来标识）。

IF 结构根据对条件的计算选择一个执行路径。ELSEIF 和 ELSE 子句允许执行分支，或指定在其他条件不满足时执行的默认操作。

ITERATE 子句将流控制传递到一个有标签的循环的开头。

LEAVE 子句使程序控制离开一个循环或代码块。

LOOP 子句多次执行一个代码块，直到 LEAVE、ITERATE 或 GOTO 语句使控制离开循环。

REPEAT 子句重复执行一个代码块，直到指定的搜索条件返回真为止。

RETURN 子句将控制从 SQL 过程返回给调用者。

SET 子句将一个值赋值给一个输出变量或 SQL 变量。

WHILE 在指定的条件为真时重复执行一个代码块。

要想成功地创建 SQL 过程，必须在数据库服务器上安装 DB2 Application Development Client。（关于 Application Development Client 的更多信息见本系列中的第一个教程。）以前需要用 C 编译器来创建 SQL 过程，这种依赖性在 DB2 Universal Database Version 8 中已经消除了。以前依赖于 C 编译器的所有操作现在由驻留在虚拟机中的 DB2 生成的字节码执行。关于这个改进的更多信息见参考资料。

使用 SQL CALL 语句从 DB2 命令行调用 SQL 过程。被调用的过程必须在系统编目中进行定义。用任何支持的语言编写的客户机应用程序都可以调用 SQL 过程。为了调用 SQL 过程 SALES_STATUS，执行以下步骤：

连接 SAMPLE 数据库。

发出以下语句：db2 CALL sales_status (25, ?)

因为圆括号对于基于 UNIX 的系统上的命令 shell 有特殊意义，所以在这些系统上必须在它们前面加上反斜线（）字符，或者用双引号包围它们：db2 "CALL sales_status (25, ?)"

如果以交互输入模式使用命令行处理程序（CLP）（由 db2 => 输入提示表示），那么不必包含双引号。

在这个示例中，值 25 作为输入参数 quota 传递给 SQL 过程，并使用问号（?）作为输出参数 sql_state 的占位符。这个过程返回销售总量超过指定额度（25）的每个销售人员的姓名和销售总量。下面是这个语句返回的输出示例：

SQL_STATE: 00000

SALES_PERSON	TOTAL_SALES
GOUNOT	50
LEE	91
"SALES_STATUS" RETURN_STATUS: "0"

创建和使用 SQL 用户定义函数

可以创建用户定义函数来扩展内置的 DB2 函数。例如，创建计算复杂的算术表达式或操作字符串的函数，然后在 SQL 语句中像对待任何现有的内置函数一样引用这些函数。

假设需要一个返回圆的面积的函数，这个函数的输入参数是圆的半径。内置的 DB2 函数中没有这样的函数，但是可以创建一个用户定义的 SQL 标量函数来执行这个任务，可以在 SQL 语句中支持标量函数的任何地方引用这个函数。CREATE function ca (r DOUBLE) 　RETURNS DOUBLE 　LANGUAGE SQL 　CONTAINS SQL 　NO EXTERNAL ACTION 　DETERMINISTIC 　RETURN 3.14159 * (r * r);

NO EXTERNAL ACTION 子句指出这个函数不会对数据库管理程序不管理的对象的状态有任何影响。DETERMINISTIC 关键字指出这个函数对于给定的参数值总是返回相同的结果。在查询优化期间会使用这个信息。执行这个函数的简便方法是在一个查询中引用它。在下面的示例中，针对 SYSIBM.SYSDUMMY1 编目视图（其中只有一行）执行这个查询（可以选择任意的查询目标）：db2 SELECT ca(96.8) AS area FROM sysibm.sysdummy1 AREA ------------------------ 　+2.94374522816000E+004 　1 record(s) selected.

还可以创建用户定义的表函数，它接受零个或更多的输入参数并以表的形式返回数据。表函数只能用在 SQL 语句的 FROM 子句中。

假设需要一个返回拥有特定工作的所有职员的姓名和职员号的函数，函数的参数是这个工作的头衔。下面是执行这个任务的表函数示例：CREATE FUNCTION jobemployees (job VARCHAR(8)) 　RETURNS TABLE ( 　 empno CHAR(6), 　 firstname VARCHAR(12), 　 lastname VARCHAR(15) 　) 　LANGUAGE SQL 　READS SQL DATA 　NO EXTERNAL ACTION 　DETERMINISTIC 　RETURN 　 SELECT empno, firstnme, lastname 　　 FROM employee 　　 WHERE employee.job = jobemployees.job;

以下查询在 FROM 子句中引用这个新的表函数，并传递工作头衔 ‘CLERK’ 作为函数的参数。语法要求用关键字 AS 引入一个相关名称：db2 SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk EMPNO　FIRSTNAME　　LASTNAME ------ ------------ --------------- 000120 SEAN　　　　 O'CONNELL 000230 JAMES　　　　JEFFERSON 000240 SALVATORE　　MARINO 000250 DANIEL　　　 SMITH 000260 SYBIL　　　　JOHNSON 000270 MARIA　　　　PEREZ 　6 record(s) selected.

结束语

本教程旨在讲解结构化查询语言（Structured Query Language，SQL）以及 DB2 9 使用 SQL 操纵关系数据库中数据的一些方式。还讨论了 SQL 的基础知识，包括 SQL 语言元素、数据操纵语言（Data Manipulation Language，DML）、SQL 过程和用户定义函数。第 5 部分：操作 DB2 对象将讨论 DB2 定义的数据类型、表、视图和索引，帮助您理解如何创建和使用它们。

本系列其他教程推荐

DB2 9 基础（730 考试）认证指南，第 1 部分: DB2 规划 1

DB2 9 基础（730 考试）认证指南，第 1 部分: DB2 规划 2

DB2 9 基础（730 考试）认证指南，第 2 部分: 安全性

DB2 9 基础（730 考试）认证指南，第 3 部分: 访问 DB2 数据

DB2 9 基础（730 考试）认证指南，第 5 部分: 处理 DB2 对象

DB2 9 基础（730 考试）认证指南，第 6 部分: 数据并发性

DB2 9 基础（730 考试）认证指南，第 7 部分: XQuery 简介