45、DB2通用数据库高级特性解析

DB2通用数据库高级特性解析

1. 动态SQL与描述器的使用

动态SQL可以像静态SQL一样嵌入到编程语言中，但这些程序需要预编译。有特定的语句（以 exec sql 开头）来指定动态SQL操作。例如，SQL命令的准备阶段可以使用 prepare name from variable 语句，其中变量存储SQL命令，该命令可以包含问号，表示在执行阶段要提供的参数。

在查询时，可以像静态SQL一样使用游标机制。如果SQL命令的属性数量和类型事先未知，可以使用描述器。DB2系统提供了预定义的描述器 sqlda ，它是一个记录，包含可变数量的 sqlvar 类型的字段，每个字段对应一个要描述的属性。在准备阶段，可以使用 prepare name into sqlda from variable 语法来指示使用该描述器，执行该语句后，变量中存储的命令描述将加载到 sqlda 中。利用这种技术，可以轻松实现一个个性化的用户界面，能够接受并执行任意SQL命令。

2. SQL查询扩展

DB2遵循SQL - 2的入门级别，但也提供了一些扩展，这些扩展显著提高了语言的表达能力，特别是在查询操作方面。
- CASE表达式
- 在DB2的SQL的 select 子句中，可以指定条件表达式，使用 case 构造。例如，对于车辆表 VEHICLE(RegistrationNumber, Type, Manufacturer, Length, NWheels) ，计算1997年后注册车辆的税费：

select RegistrationNumber,
  case Type
    when 'car' then 42.50 * Length
    when 'truck' then 25.00 * NWheels
    when 'motorbike' then 37.00
    else null
  end as Tax
from Vehicle
where RegistrationYear > 1997;

- `case`表达式也可用于更新操作。例如，根据`EMPLOYEE`关系中`Department`列的值修改工资：

update Employee
set Salary =
  case
    when Department = 'Administration' then Salary * 1.1
    when Department = 'Production' then Salary * 1.15
    when Department = 'Distribution' then Salary * 1.12
    else Salary
  end;

- 在标准SQL中，执行相同操作需要使用多个命令。

• 查询嵌套
  DB2的SQL允许查询嵌套，不仅可以在 where 子句中，还可以在 from 子句中。例如，查找比所在部门平均工资高30%的员工：

select Surname, Salary
from Employee as Emp, (select Department, avg(Salary)
                       from Employee
                       group by Department)
                       as AvgSalDept(Department, AvgSalary)
where Emp.Department = AvgSalDept.Department and
      Emp.Salary > AvgSalDept.AvgSalary * 1.3

- 这个查询也可以用标准方式表达，但将嵌套SQL语句移到`where`子句中。不过，这种功能增加了语言的表达能力，有些嵌入在`from`子句中的SQL查询无法用其他方式表达。

• OLAP操作
  DB2提供了 group by 子句的扩展，用于指定涉及复杂数据聚合的查询，这些操作通常称为在线分析处理（OLAP）。
  • ROLLUP构造 ：可以指定在同一维度的不同级别上进行聚合。例如，计算连锁店按商店、城市和州分组的总收入：

select Location.Store, City, State, sum(Income) as TotIncome
from Sale, Location
where Sale.Store = Location.Store
group by rollup(State, City, Location.Store)

- **CUBE构造**：可以沿着多个维度进行聚合，可能在不同级别上。例如，计算按城市和产品类别分组的销售总数：

select City, Category, count(Income) as NoOfSales
from  Sale, Location, Product
where Sale.Store = Location.Store and
      Sale.Item = Product.Item
group by cube(City, Category)

• 递归查询
  对于存储每个员工直接上级的 SUPERVISION(Employee, Head) 表，要查找员工Jones的所有上级，可以使用递归查询：

with Superior(Employee, SuperHead) as
     ((select Employee, Head
       from Supervisor)
       union all
      (select Supervisor.Employee, Superior.SuperHead
       from Supervisor, Superior
       where Supervisor.Head = Superior.Employee))
select SuperHead
from Superior
where Employee = 'Jones'

3. DB2的对象导向特性

DB2提供了三个独立的特性，可以组合使用以创建具有面向对象特性的数据库：复杂数据类型、用户数据类型和用户函数。
- 复杂数据类型（LOBs）
- Blob（二进制大对象） ：表示二进制形式的数据项，最大可达2GB，不能与其他类型的数据进行赋值或比较。
- Clob（字符大对象） ：由单字节字符序列组成的数据项，最大可达2GB，可以与字符串类型（ Char 和 Varchar ）的数据进行比较。
- Dbclob（双字节字符大对象） ：由双字节字符序列组成的数据项，最大可达2GB，只能在具有适当配置的数据库中使用。
- 例如，创建包含LOB数据的 Employee 表：

create table Employee (
  EmployeeId integer not null unique,
  Name       varchar(20),
  Salary     decimal(7,3),
  HiringDate date,
  Picture    blob(5M) compact,
  Resume     clob(500K)
)

- 使用LOB数据在DB2 SQL命令中有很多限制，不能直接使用`=`、`>`、`<`或`in`等运算符进行比较，但可以使用`like`运算符。例如：

select EmployeeId, Name
from Employee
where Resume like '%DBA%'

- DB2以最小化LOB在内存中移动的方式管理它们，可以使用定位器在应用程序中操作LOB。定位器表示一个LOB，但不实际存储它，通过适当使用定位器，可以延迟甚至避免将LOB加载到主内存中。

• 用户类型
  用户类型（在DB2中称为 distinct ）是从DB2的基本数据类型定义的。例如：

create distinct type Money as decimal(7,2) with comparisons;
create distinct type Image as blob(100M);
create distinct type Text as clob(500K) compact;

定义后，用户类型可以自由用于创建表。例如，将 Employee 表的定义重写为：

create table Employee (
  EmployeeId integer not null unique,
  Name       varchar(20),
  Salary     money,
  HiringDate date,
  Picture    image,
  Resume     text
)

但在用户类型定义的列上，通常不能应用与源类型相同的操作，可以通过定义合适的用户函数来解决这个限制。

以下是ROLLUP操作可能的结果示例表格：
| Store | City | State | TotIncome |
| — | — | — | — |
| White | Los Angeles | CA | 34 |
| Black | Los Angeles | CA | 32 |
| NULL | Los Angeles | CA | 66 |
| Brown | San Francisco | CA | 25 |
| NULL | San Francisco | CA | 25 |
| NULL | NULL | CA | 91 |
| Red | New York | NY | 28 |
| NULL | New York | NY | 28 |
| NULL | NULL | NY | 28 |
| NULL | NULL | NULL | 119 |

以下是CUBE操作可能的结果示例表格：
| City | Category | NoOfSales |
| — | — | — |
| Los Angeles | milk | 2453 |
| Los Angeles | coffee | 988 |
| New York | milk | 789 |
| New York | coffee | 987 |
| Los Angeles | NULL | 3441 |
| New York | NULL | 1776 |
| NULL | milk | 3242 |
| NULL | coffee | 1975 |
| NULL | NULL | 5217 |

下面是一个简单的mermaid流程图，展示动态SQL的基本处理流程：

graph TD;
    A[开始] --> B[预编译动态SQL程序];
    B --> C[准备SQL命令];
    C --> D[执行SQL命令];
    D --> E[结束];

4. 用户函数的类型与应用

用户函数可以使用 create function 语句显式声明，这些函数与数据库关联，只能在该数据库的上下文中使用。DB2用户函数遵循面向对象编程中的重载原则，可以多次定义同一个函数，只要不同定义的输入参数在类型和/或数量上有所不同，还可以重新定义预定义的DB2函数。DB2用户函数可以分为以下几类：
- 内部函数 ：基于预定义的DB2函数（源函数）构建，类似于用户类型的定义方式。例如，对于前面定义的用户类型 Money ，可以定义内部函数：

create function "*"(Money, Decimal()) returns Money
  source "*"(Decimal(), Decimal())
create Function Total(Money) returns Money
  source Sum(Decimal())

这些声明定义了函数名、输入参数类型、结果类型和源函数。通过这些声明，以下SQL命令变得合法：

select Age, Total(Salary)
from Employee
group by Age;
update Employee
set Salary = Salary * 1.1
where Department = 'Production';

• 外部函数 ：对应于用传统编程语言（如C或Java）编写的外部程序，可能包含也可能不包含SQL命令。外部函数的声明包含实现函数的代码的物理位置的规范，可分为以下两种类型：
  • 标量函数 ：可以接收多个参数作为输入，但只返回一个值作为输出。如果函数名重新定义了一个基本运算符（如 + ），则可以使用中缀表示法调用此类函数。例如，定义一个计算员工工资的外部标量函数：

create function StandardSalary(Date) returns Money
  external name '/usr/db2/bin/salary.exe!StdSal'
  deterministic
  no external action
  language c parameter style db2sql
  no sql;

该声明定义了外部函数 StandardSalary ，它接收一个 Date 类型的输入并返回一个 Money 类型的数据项。可以使用以下命令检索比参考工资高20%的员工的姓氏和工资：

select Surname, Salary, StandardSalary(HiringDate)
from Employee
where Salary > StandardSalary(HiringDate) * 1.2;

- **表函数**：返回一组值的元组，这些元组被视为表的行。每次调用这些函数时，它们会返回一个新的元组或一个特殊代码，表示没有更多的元组可用。例如，定义一个表函数，该函数接受一个城市名称作为输入，并返回该城市中商店的销售信息：

create function Sales(Char(20))
  returns table (Store   char(20),
                 Product char(20),
                 Income  Integer)
  external name '/usr/db2/bin/sales'
  deterministic
  no external action
  language c parameter style db2sql no sql
  scratchpad
  final call disallow parallel;

可以使用以下SQL命令查询洛杉矶商店玩具销售的总收入：

select Store, sum(Income)
from table(Sales('Los Angeles')) as LASales
where Product = 'Toy'
group by Store;

5. 用户类型与用户函数的结合应用

通过结合使用用户类型和用户函数，可以获得具有面向对象特性的数据管理系统。例如，可以使用LOB用户类型定义一个“Polygons”类，然后将一系列用于构造新对象、修改对象、打印对象以及计算对象周长和面积的方法定义为用户函数。以下是一个简单的步骤说明：
1. 定义用户类型 ：

create distinct type Polygon as blob(10M);

2. 定义用户函数作为类的方法 ：

-- 构造新多边形对象的函数
create function CreatePolygon() returns Polygon
  external name '/usr/db2/bin/polygon.exe!Create'
  language c parameter style db2sql no sql;

-- 计算多边形周长的函数
create function Perimeter(Polygon) returns Decimal(7, 2)
  external name '/usr/db2/bin/polygon.exe!Perimeter'
  language c parameter style db2sql no sql;

-- 计算多边形面积的函数
create function Area(Polygon) returns Decimal(7, 2)
  external name '/usr/db2/bin/polygon.exe!Area'
  language c parameter style db2sql no sql;

3. 使用用户类型和用户函数 ：

-- 创建一个新的多边形对象
declare @myPolygon Polygon;
set @myPolygon = CreatePolygon();

-- 计算多边形的周长和面积
select Perimeter(@myPolygon) as Perimeter, Area(@myPolygon) as Area;

6. 总结

DB2通用数据库提供了丰富的高级特性，包括动态SQL和描述器的使用、SQL查询扩展、对象导向特性以及用户函数等。这些特性大大增强了数据库的功能和灵活性，使得用户可以更高效地进行数据管理和分析。

动态SQL和描述器的使用使得在属性数量和类型未知的情况下也能方便地执行SQL命令，为个性化用户界面的实现提供了可能。SQL查询扩展中的CASE表达式、查询嵌套、OLAP操作和递归查询等功能，显著提高了查询语言的表达能力，满足了复杂查询的需求。

对象导向特性中的复杂数据类型、用户类型和用户函数的结合，让DB2能够处理非传统数据，并实现具有面向对象特性的数据管理。特别是用户函数的重载原则和多种类型的支持，为数据库操作提供了极大的灵活性。

通过合理运用这些高级特性，开发者可以构建出更加高效、灵活和强大的数据库应用程序。

以下是DB2用户函数类型的总结表格：
| 函数类型 | 输入参数 | 输出结果 | 特点 | 示例 |
| — | — | — | — | — |
| 内部函数 | 可以是用户类型 | 特定类型 | 基于预定义DB2函数构建 | create function "*"(Money, Decimal()) returns Money |
| 外部标量函数 | 多个参数 | 单个值 | 可使用中缀表示法调用 | create function StandardSalary(Date) returns Money |
| 外部表函数 | 输入参数 | 一组元组 | 返回结果视为表的行 | create function Sales(Char(20)) returns table (...) |

下面是一个mermaid流程图，展示用户类型和用户函数结合应用的基本流程：

graph TD;
    A[定义用户类型] --> B[定义用户函数];
    B --> C[创建对象实例];
    C --> D[调用用户函数操作对象];
    D --> E[结束操作];