将复杂的 Oracle PL/SQL 游标逻辑封装为视图的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to encapsulate complex Oracle PL/SQL cursor logic as a view?
提问by yukondude
I've written PL/SQL code to denormalize a table into a much-easer-to-query form. The code uses a temporary table to do some of its work, merging some rows from the original table together.
我已经编写了 PL/SQL 代码来将表非规范化为更容易查询的形式。该代码使用一个临时表来完成它的一些工作，将原始表中的一些行合并在一起。
The logic is written as a pipelined table function, following the pattern from the linked article. The table function uses a PRAGMA AUTONOMOUS_TRANSACTIONdeclaration to permit the temporary table manipulation, and also accepts a cursor input parameter to restrict the denormalization to certain ID values.
该逻辑按照链接文章中的模式编写为流水线表函数。table 函数使用PRAGMA AUTONOMOUS_TRANSACTION声明来允许临时表操作，并且还接受游标输入参数以将非规范化限制为某些 ID 值。
I then created a view to query the table function, passing in all possible ID values as a cursor (other uses of the function will be more restrictive).
然后我创建了一个视图来查询表函数，将所有可能的 ID 值作为游标传递（该函数的其他用途将受到更多限制）。
My question: is this all really necessary? Have I completely missed a much more simple way of accomplishing the same thing?
我的问题：这一切真的有必要吗？我是否完全错过了完成同一件事的更简单的方法？
Every time I touch PL/SQL I get the impression that I'm typing way too much.
每次我接触 PL/SQL 时，我都会觉得我打字太多了。
Update:I'll add a sketch of the table I'm dealing with to give everyone an idea of the denormalization that I'm talking about. The table stores a history of employee jobs, each with an activation row, and (possibly) a termination row. It's possible for an employee to have multiple simultaneous jobs, as well as the same job over and over again in non-contiguous date ranges. For example:
更新：我将添加我正在处理的表格的草图，让每个人都了解我正在谈论的非规范化。该表存储员工工作的历史记录，每个工作都有一个激活行和（可能）一个终止行。员工可能同时拥有多个工作，以及在不连续的日期范围内一遍又一遍地执行相同的工作。例如：
| 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 |
Querying that to figure out who is working when in what job is non-trivial. So, my denormalization function populates the temporary table with just the date ranges for each job, for any EMP_IDs passed in though the cursor. Passing in EMP_IDs 1 and 2 would produce the following:
查询以找出谁在从事什么工作时在工作并不重要。因此，对于EMP_ID通过游标传入的任何s ，我的非规范化函数仅使用每个作业的日期范围填充临时表。传入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_DATEallows NULLs for jobs that don't have a predetermined termination date.)
（END_DATE允许NULLs 用于没有预定终止日期的工作。）
As you can imagine, this denormalized form is much, much easier to query, but creating it--so far as I can tell--requires a temporary table to store the intermediate results (e.g., job records for which the activation row has been found, but not the termination...yet). Using the pipelined table function to populate the temporary table and then return its rows is the only way I've figured out how to do it.
可以想象，这种非规范化形式更容易查询，但创建它——据我所知——需要一个临时表来存储中间结果（例如，激活行已被找到了，但不是终止……还没有）。使用流水线表函数填充临时表然后返回其行是我想出的唯一方法。
采纳答案by Nick Pierpoint
I think a way to approach this is to use analytic functions...
我认为解决这个问题的一种方法是使用分析函数......
I set up your test case using:
我使用以下方法设置您的测试用例：
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;
I've used the leadfunction to get the next date and then wrapped it all as a sub-query just to get the "A" records and add the end date if there is one.
我使用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
I'm sure there's some use cases I've missed but you get the idea. Analytic functions are your friend :)
我确定我遗漏了一些用例，但您明白了。分析函数是你的朋友 :)
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
回答by Tony Andrews
The real problem here is the "write-only" table design - by which I mean, it's easy to insert data into it, but tricky and inefficient to get useful information out of it! Your "temporary" table has the structure the "permanent" table should have had in the first place.
这里真正的问题是“只写”表设计——我的意思是，向其中插入数据很容易，但从中获取有用信息却很棘手且效率低下！您的“临时”表具有“永久”表首先应该具有的结构。
Could you perhaps do this:
你能不能这样做：
- Create a permanent table with the better structure
- Populate it to match the data in the first table
- Define a database trigger on the original table to keep the new table in sync from now on
- 创建一个结构更好的永久表
- 填充它以匹配第一个表中的数据
- 在原表上定义一个数据库触发器，从现在开始保持新表同步
Then you can just select from the new table to perform your reporting.
然后您可以从新表中选择来执行您的报告。
回答by mathewbutler
It sounds like you are giving away some read consistency here ie: it will be possible for the contents of your temporary table to be out of sync with the source data, if you have concurrent modification data modification.
听起来您在这里放弃了一些读取一致性，即：如果您有并发修改数据修改，则临时表的内容可能与源数据不同步。
Without knowing the requirements, nor complexity of what you want to achieve. I would attempt
不知道要求，也不知道要实现的目标的复杂性。我会尝试
- to define a view, containing (possibly complex) logic in SQL, else I'd add some PL/SQL to the mix with;
- A pipelined table function, but using an SQL collection type (instead of the temporary table ). A simple example is here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4447489221109
- 定义一个视图，在 SQL 中包含（可能是复杂的）逻辑，否则我会添加一些 PL/SQL 到混合中；
- 流水线表函数，但使用 SQL 集合类型（而不是临时表）。一个简单的例子在这里：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 4447489221109
Number 2 would give you less moving parts and solve your consistency issue.
数字 2 将为您提供更少的活动部件并解决您的一致性问题。
Mathew Butler
马修巴特勒
回答by hollystyles
Rather than having the input parameter as a cursor, I would have a table variable (don't know if Oracle has such a thing I'm a TSQL guy) or populate another temp table with the ID values and join on it in the view/function or wherever you need to.
我没有将输入参数作为游标，而是有一个表变量（不知道 Oracle 是否有这样的东西，我是一个 TSQL 人）或者用 ID 值填充另一个临时表并在视图中加入它/function 或任何您需要的地方。
The only time for cursors in my honest opinion is when you haveto loop. And when you have to loop I always recommend to do that outside of the database in the application logic.
老实说，只有当您必须循环时才能使用游标。当您必须循环时，我总是建议在应用程序逻辑中的数据库之外执行此操作。
回答by yukondude
I couldn't agree with you more, HollyStyles. I also used to be a TSQL guy, and find some of Oracle's idiosyncrasies more than a little perplexing. Unfortunately, temp tables aren't as convenient in Oracle, and in this case, other existing SQL logic is expecting to directly query a table, so I give it this view instead. There's really no application logic that exists outside of the database in this system.
我完全同意你的观点，HollyStyles。我也曾经是一个 TSQL 专家，发现 Oracle 的一些特性有点令人困惑。不幸的是，临时表在 Oracle 中没有那么方便，在这种情况下，其他现有的 SQL 逻辑期望直接查询一个表，所以我给它这个视图。在这个系统中，真的没有数据库之外的应用程序逻辑。
Oracle developers do seem to use cursors much more eagerly than I would have thought. Given the bondage & discipline nature of PL/SQL, that's all the more surprising.
Oracle 开发人员似乎比我想象的更热衷于使用游标。鉴于 PL/SQL 的束缚和纪律性质，这更令人惊讶。
回答by Sergey Stadnik
The simplest solution is:
最简单的解决方案是：
Create a global temporary tablecontaining just IDs you need:
CREATE GLOBAL TEMPORARY TABLE tab_ids (id INTEGER) ON COMMIT DELETE ROWS;Populate the temporary table with the IDs you need.
Use EXISTS operation in your procedure to select the rows that are only in the IDs table:
SELECT yt.col1, yt.col2 FROM your\_table yt WHERE EXISTS ( SELECT 'X' FROM tab_ids ti WHERE ti.id = yt.id )
创建一个仅包含您需要的 ID的全局临时表：
CREATE GLOBAL TEMPORARY TABLE tab_ids (id INTEGER) ON COMMIT DELETE ROWS;使用您需要的 ID 填充临时表。
在您的过程中使用 EXISTS 操作来选择仅在 IDs 表中的行：
SELECT yt.col1, yt.col2 FROM your\_table yt WHERE EXISTS ( SELECT 'X' FROM tab_ids ti WHERE ti.id = yt.id )
You can also pass a comma-separated string of IDs as a function parameter and parse it into a table. This is performed by a single SELECT. Want to know more - ask me how :-) But it's got to be a separate question.
您还可以将逗号分隔的 ID 字符串作为函数参数传递，并将其解析为表格。这是由单个 SELECT 执行的。想知道更多 - 问我如何:-) 但这必须是一个单独的问题。
