10、数据库数据创建、派生与转换技巧

数据库数据创建、派生与转换技巧

在数据库管理和数据分析中，我们常常会遇到各种数据处理的需求，比如处理虚拟列、查找缺失的行、进行行列转换等。下面将详细介绍一些常见问题及对应的解决方案。

1. 虚拟列的使用与限制

虚拟列可以增强数据库的可用性，同时减少需要在数据库视图中维护的元数据量。不过，在使用虚拟列时，有一些限制需要注意：
- 函数确定性 ：如果虚拟列包含内置或用户函数，该函数必须声明为 DETERMINISTIC，即对于相同的输入值集，函数必须返回相同的结果。例如，不能在虚拟列中使用 SYSDATE 或 SYSTIMESTAMP 函数，因为每次检索行时它们几乎总是返回不同的结果。
- 引用限制 ：虚拟列不能引用表中的其他虚拟列，但可以将一个虚拟列的定义包含在另一个虚拟列的定义中。此外，虚拟列只能引用同一表中的列。

对于不支持虚拟列的 Oracle 版本（Oracle Database 11g 之前），可以通过在虚拟列使用的表达式上创建基于函数的索引来模拟虚拟列的索引功能。

2. 查找缺失的行

有时候，表的主键序列号会存在间隙，我们可能需要找出这些中间的序列号。例如，公司棒球队以 EMPLOYEE_ID 列的数字作为队服背后的号码，由于队服号码限制为三位数，经理希望尽可能重用旧的号码。

要查找跳过或当前未使用的员工编号，可以使用以下包含两个嵌入式子查询的查询：

with all_used_emp_ids as
   (select level po