oracle - 使用函数评估字符串的替代方法

Question

我正在尝试输出记录列表，但有些记录在主题列中可能没有值。
我有一个 altSubject 列，可以指定要输出的内容。

简化例如

insert all 
    into myTable 
        (id, subject, altSubject, partNumber, serialNumber, startDate, endDate) 
        values
        (1, 'test',null,'xyz','123','1/1/2019', '1/5/2019') 
    into myTable 
        (id, subject, altSubject, partNumber, serialNumber, startDate, endDate) 
        values
        (2, null, '''SN: '' || serialNumber','abc','789','1/1/2019', '1/5/2019') 

输出应如下所示:

subject | Part Number | Start Date | End Date
test    | xyz         | 1/1/2019   | 1/5/2019
SN: 789 | abc         | 1/1/2019   | 1/5/2019

我已经能够使用带有下面函数的案例来做到这一点，但我遇到的问题是在 40k 行表上运行需要 5 分钟。

select
    ...
    ...
    case when altSubject is not null then
        fAltSubject(id,altSubject)
    else
      subject
    end subject
from
    myTable
where
    status = 'closed'

功能:

create or replace function fAltSubject
    (pID in number
    , pAltSubject in varchar2)
    return varchar2 
  as
    vNewSubject varchar2(400) := '';
  begin
    vSql := 'select ' ||
      pAltSubject ||
      ' from
          myTable
        where
          id = ' || pID;
    execute immediate vSql
     into 
       vNewSubject;
    return vNewSubject;
end faltsubject;

有没有更好的方法来做到这一点，不需要 5 分钟？

提前致谢。

Answer 1

“当掩码可以是字段和文本的组合时，如何在列中使用用户定义的掩码”。

这是我能做的最好的，并获得良好的性能。

该表定义了主题、替代主题和显示主题。

触发器根据其他两个字段设置 DisplaySubject。

触发器必须引用特定的列名，因此每次添加列时都需要重新生成。也许是夜类？

create table myTable (
  id number, 
  subject varchar2(64), 
  altSubject varchar2(128),
  displaySubject varchar2(128),
  partNumber varchar2(16), 
  serialNumber varchar2(16), 
  startDate date, 
  endDate date
);

create or replace procedure generate_mytable_trigger is
l_newline constant varchar2(1) := chr(10);
l_text clob := to_clob(
'create or replace trigger mytable_displaysubject
before insert or update on mytable
for each row
declare
  lt_column_names sys.odcivarchar2list;
begin
  if :new.subject is not null then
    :new.altsubject := null;
    :new.displaysubject := :new.subject;
    return;
  end if;
  :new.displaysubject := :new.altsubject;
  -- start lines to be generated');
l_end_text constant varchar2(4000) := 
'-- end lines to be generated
  return;
end mytable_displaysubject;';
begin
  for rec in (
    select l_newline ||
      ':new.displaysubject := replace(:new.displaysubject, ''#'||column_name||'#'', :new.'||column_name||');'
      as text
    from user_tab_columns where table_name = 'MYTABLE'
    and column_name not in ('SUBJECT','ALTSUBJECT','DISPLAYSUBJECT')
  ) loop
    l_text := l_text || rec.text;
  end loop;
  l_text := l_text || l_newline || l_end_text;
  execute immediate l_text;
end;
/

exec generate_mytable_trigger;

现在做一个小测试:

insert into mytable(id, subject, altsubject, partnumber, serialnumber, startdate, enddate)
select 1, 'test',null,'xyz','123',sysdate, sysdate+1 from dual
union all 
select 2, null,'PN: #PARTNUMBER#','abc','789',sysdate, sysdate+1 from dual
union all 
select 3, null,'PN: #PARTNUMBER#, SN: #SERIALNUMBER#','qsdf','789',sysdate, sysdate+1 from dual
union all 
select 3, null,'PN: #PARTNUMBER#, ??: #BADCOLUMN#','qsdf','789',sysdate, sysdate+1 from dual;
commit;

select subject, altsubject, displaysubject from mytable;

SUBJECT   ALTSUBJECT                             DISPLAYSUBJECT              
test                                               test                         
           PN: #PARTNUMBER#                        PN: abc                      
           PN: #PARTNUMBER#, SN: #SERIALNUMBER#    PN: qsdf, SN: 789            
           PN: #PARTNUMBER#, ??: #BADCOLUMN#       PN: qsdf, ??: #BADCOLUMN#