将复杂的Oracle PL / SQL游标逻辑封装为视图的最佳方法？
我已经编写了PL / SQL代码，以将表非规范化为更易于查询的形式。该代码使用临时表来完成其某些工作，将原始表中的某些行合并在一起。
遵循链接文章中的模式，将逻辑编写为流水线表函数。表函数使用PRAGMA AUTONOMOUS_TRANSACTION声明来允许临时表操作，并且还接受游标输入参数以将非规范化限制为某些ID值。
然后，我创建了一个用于查询表函数的视图，将所有可能的ID值作为游标传递(该函数的其他用法将更具限制性)。
我的问题是：这一切真的有必要吗？我是否完全错过了完成同一件事的简单得多的方法？
每次触摸PL / SQL时，我都会觉得自己打字太多。
更新：我将添加一张我要处理的表的草图，以使每个人都了解我正在谈论的非规范化。该表存储了员工作业的历史记录，每个作业都有一个激活行和一个(可能)一个终止行。员工可能有多个同时工作，并且在不连续的日期范围内一次又一次地执行同一工作。例如：
| EMP_ID | JOB_ID | STATUS | EFF_DATE | other columns... | 1 | 10 | A | 10-JAN-2008 | | 2 | 11 | A | 13-JAN-2008 | | 1 | 12 | A | 20-JAN-2008 | | 2 | 11 | T | 01-FEB-2008 | | 1 | 10 | T | 02-FEB-2008 | | 2 | 11 | A | 20-FEB-2008 |
进行查询以弄清楚谁在从事什么工作是不平凡的。因此，我的非规范化函数将为每个通过光标传递的任何EMP_ID的日期范围填充每个工作的临时表。传递EMP_IDs 1和2将产生以下结果：
| EMP_ID | JOB_ID | START_DATE | END_DATE | | 1 | 10 | 10-JAN-2008 | 02-FEB-2008 | | 2 | 11 | 13-JAN-2008 | 01-FEB-2008 | | 1 | 12 | 20-JAN-2008 | | | 2 | 11 | 20-FEB-2008 | |
(" END_DATE"允许对没有预定终止日期的作业使用" NULL"。)
我们可以想象，这种非规范化的形式要查询的非常容易得多，但是据我所知，创建它需要一个临时表来存储中间结果(例如，已激活行所在的作业记录)找到了，但还没找到终止...)。使用管道表函数填充临时表，然后返回其行是我弄清楚如何做的唯一方法。
解决方案
回答
而不是将输入参数用作游标，我将拥有一个表变量(不知道Oracle是否有我是TSQL专家)或者使用ID值填充另一个临时表并在视图中加入/ function或者我们需要的任何地方。
在我看来，游标唯一的时间就是必须循环。而且，当我们必须循环时，我总是建议在应用程序逻辑中在数据库外部执行此操作。
回答
HollyStyles，我完全同意意见。我也曾经是TSQL专家，并且发现Oracle的一些特质不仅仅是有些困惑。不幸的是，临时表在Oracle中不那么方便，在这种情况下，其他现有的SQL逻辑期望直接查询表，因此我给它提供此视图。在此系统中，数据库之外确实不存在任何应用程序逻辑。
Oracle开发人员似乎比我想象的要急切得多地使用游标。鉴于PL / SQL的约束性和纪律性质，这更加令人惊讶。
回答
最简单的解决方案是：
- 创建仅包含所需ID的全局临时表：在COMMIT DELETE ROWS上创建GLOBAL TEMPORARY TABLE tab_ids(id INTEGER)；
- 用所需的ID填充临时表。
- 在过程中使用EXISTS操作来选择仅在ID表中的行：SELECT yt.col1，yt.col2 FROM your_table yt WISTE EXISTS(SELECT'X'FROM tab_ids ti WHERE ti.id = yt.id)
我们还可以将以逗号分隔的ID字符串作为函数参数传递，并将其解析到表中。这是由单个SELECT执行的。想知道更多，请问我如何:-)但这必须是一个单独的问题。
回答
听起来我们在这里放弃了一些读取一致性，即：如果我们同时进行修改数据修改，则临时表的内容可能与源数据不同步。
不知道要求，也不知道要实现的复杂性。我会尝试
- 定义一个视图，在SQL中包含(可能是复杂的)逻辑，否则我将添加一些PL / SQL到其中；
- 管道表功能，但使用SQL集合类型(而不是临时表)。一个简单的示例在这里：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4447489221109
2号将使运动部件更少，并解决了一致性问题。
马修·巴特勒(Mathew Butler)
回答
我认为解决此问题的一种方法是使用解析函数...
我使用以下方法设置测试用例：
create table employee_job (
emp_id integer,
job_id integer,
status varchar2(1 char),
eff_date date
);
insert into employee_job values (1,10,'A',to_date('10-JAN-2008','DD-MON-YYYY'));
insert into employee_job values (2,11,'A',to_date('13-JAN-2008','DD-MON-YYYY'));
insert into employee_job values (1,12,'A',to_date('20-JAN-2008','DD-MON-YYYY'));
insert into employee_job values (2,11,'T',to_date('01-FEB-2008','DD-MON-YYYY'));
insert into employee_job values (1,10,'T',to_date('02-FEB-2008','DD-MON-YYYY'));
insert into employee_job values (2,11,'A',to_date('20-FEB-2008','DD-MON-YYYY'));
commit;
我使用了lead函数来获取下一个日期，然后将其包装为子查询，只是为了获取" A"记录并添加结束日期(如果有)。
select
emp_id,
job_id,
eff_date start_date,
decode(next_status,'T',next_eff_date,null) end_date
from
(
select
emp_id,
job_id,
eff_date,
status,
lead(eff_date,1,null) over (partition by emp_id, job_id order by eff_date, status) next_eff_date,
lead(status,1,null) over (partition by emp_id, job_id order by eff_date, status) next_status
from
employee_job
)
where
status = 'A'
order by
start_date,
emp_id,
job_id
我敢肯定我错过了一些用例，但我们明白了。分析函数是朋友：)
EMP_ID JOB_ID START_DATE END_DATE 1 10 10-JAN-2008 02-FEB-2008 2 11 13-JAN-2008 01-FEB-2008 2 11 20-FEB-2008 1 12 20-JAN-2008
回答
这里真正的问题是"只写"表设计，我的意思是说，很容易将数据插入其中，但是要从中获取有用的信息却很棘手且效率低下！"临时"表具有"永久"表首先应具有的结构。
我们也许可以这样做：
- 创建具有更好结构的永久表
- 填充它以匹配第一个表中的数据
- 从现在开始在原始表上定义数据库触发器，以使新表保持同步
然后，我们可以从新表中选择执行报告。
