代码之家 › 专栏 › 技术社区 › yukondude

将复杂的Oracle PL/SQL光标逻辑封装为视图的最佳方法是什么?

plsql oracle sql

yukondude · 技术社区 · 16 年前

我已经编写了pl/sql代码,将表非规范化为更易于查询的表单。代码使用一个临时表来完成它的一些工作,将原始表中的一些行合并在一起。

逻辑写为 pipelined table function ,遵循链接文章中的模式。table函数使用 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 s通过光标传入。传球 清蛋白 S 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 对于没有预定终止日期的工作。)

正如您可以想象的那样,这个非规范化的表单查询起来容易得多,但是创建它——据我所知——需要一个临时表来存储中间结果(例如,已经找到激活行的作业记录,但还没有终止……)。使用pipelined table函数填充临时表,然后返回其行,这是我了解如何执行此操作的唯一方法。

6 回复 | 直到 6 年前

Nick Pierpoint 16 年前

我认为解决这个问题的方法是使用解析函数…

我设置测试用例时使用:

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;

我已经用过了领导函数获取下一个日期,然后将其包装为一个子查询,只需获取“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

hollystyles 16 年前

我没有将输入参数作为光标,而是有一个表变量(不知道Oracle是否有这样的东西,我是TSQL的家伙),或者用ID值填充另一个临时表,并在视图/函数中或需要的任何地方联接它。

在我看来,只有当你有循环。当您需要循环时,我总是建议在应用程序逻辑中在数据库外部执行该操作。

mathewbutler 16 年前

听起来您好像在这里提供了一些读一致性,即:如果您同时修改了修改数据,临时表的内容可能与源数据不同步。

不知道需求,也不知道你想要实现什么的复杂性。我会尝试

为了在SQL中定义一个包含(可能很复杂)逻辑的视图,否则我将添加一些pl/sql到mix with中;
管道表函数,但使用SQL集合类型(而不是临时表)。这里有一个简单的例子: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4447489221109

数字2可以减少运动部件,解决一致性问题。

马修巴特勒

Tony Andrews 16 年前

这里真正的问题是“只写”表设计——我的意思是,向其中插入数据很容易,但从中获取有用信息却很困难且效率低下!您的“临时”表具有“永久”表应该具有的结构。

你可以这样做吗:

使用更好的结构创建永久表
填充它以匹配第一个表中的数据
在原始表上定义数据库触发器以使新表从现在起保持同步

然后,您可以从新表中选择以执行报告。

yukondude 16 年前

我不能再同意你了,霍利斯蒂尔。我也曾经是一个TSQL的人,发现甲骨文的一些特性不仅仅是有点让人困惑。不幸的是,临时表在Oracle中并没有那么方便,在这种情况下,其他现有的SQL逻辑期望直接查询一个表,所以我给出了这个视图。在这个系统中,数据库之外实际上没有应用程序逻辑。

Oracle开发人员似乎比我想象的更热衷于使用光标。考虑到pl/sql的束缚性和纪律性,这就更加令人惊讶了。

DJo 1JD 6 年前

最简单的解决方案是:

创建一个 global temporary table 只包含您需要的ID:

CREATE GLOBAL TEMPORARY TABLE tab_ids (id INTEGER)  
ON COMMIT DELETE ROWS;

用所需的ID填充临时表。

在过程中使用exists操作来选择仅在ID表中的行:

  SELECT yt.col1, yt.col2 FROM your\_table yt  
   WHERE EXISTS (  
      SELECT 'X' FROM tab_ids ti  
       WHERE ti.id = yt.id  
   )

您还可以将一个逗号分隔的ID字符串作为函数参数传递,并将其解析为一个表。这是由一个选择执行的。想知道更多——问我怎么做——)但这必须是一个单独的问题。