oracle apex穿梭节省取消选举

Question

我使用的是Apex 4.2.4和Oracle 11g。我有一个邮件列表应用程序，在那里我维护多个邮件列表。给定的收件人可能属于一个或多个列表。我在用Apex穿梭机来维护名单。所有邮件收件人的来源都在表中:mail_recipition。mail_recipition中有四个重要字段：

• prim_key
• first_name
• last_name
• 电子邮件地址

另一个表存储给定邮件列表的选定收件人:mail_recipient_category:mail_recipient_category中的重要字段有：

• prim_key
• Recipient_fkey（存储mail_recipition表中的prim_key)
• 类别
• merge_check

页面上有两个显示项。类别下拉列表(P31_email_list)和穿梭列表(P31_email_list_assignments)P31_email_list_assignments的LOV为：

Select last_name || ', ' || first_name || ' -- ' || email_address, prim_key from mail_recipient
order by 1;

穿梭源的PL/SQL函数体是：

declare
  emp_list  apex_application_global.vc_arr2;
  i                   number := 1;
begin
  for r in (Select mr.last_name || ', ' || mr.first_name || ' -- ' || mr.email_address, mr.prim_key
            From mail_recipient mr left outer join 
              mail_recipient_category mrc
            On mr.prim_key = mrc.recipient_fkey
            Where  mrc.category = :P31_EMAIL_LIST)
  loop
    emp_list(i) := r.prim_key;
    i := i + 1;
  end loop;
  return APEX_UTIL.TABLE_TO_STRING(emp_list, ':');
end;

还有一个单页进程来更新数据库表mail_recipient_category。在计算和验证之后，在提交时执行该流程。开始

MERGE INTO MAIL_RECIPIENT_CATEGORY ss
USING (
    Select
        shuttle.column_value shuttle_st
      , db.recipient_fkey db_st
    from
        table(string_to_coll(:P31_email_list_assignments)) shuttle
          left outer join mail_recipient_category db
            on shuttle.column_value = db.recipient_fkey
           and db.category = :P31_email_list) t
on  (ss.recipient_fkey = t.db_st
and ss.category = :P31_email_list    
    )
when matched
then
    update
    set
        ss.merge_check = ss.merge_check
    delete
    where
        t.shuttle_st is null and ss.category = :P31_email_list
--        t.shuttle_st is null

  when not matched 
  then
    insert
      (recipient_fkey, category)
    values
      (t.shuttle_st, :P31_email_list);
end;

穿梭机工作良好，从左到右装载和保存项目。我的问题是取消选择项目从右侧到左侧的航天飞机。将邮件收件人从右侧移到左侧后，当我按下提交按钮时，邮件不会离开穿梭机的右侧，处理过程也不会从mail_recipient_category表中删除该行。
感谢您在这方面的帮助。

Answer 1

在与一位非常有经验的同事一起工作后，我们确定页面流程出了问题。具体地说，Merge语句的Delete子句似乎对这个穿梭不起作用。我们最后在页面进程的Merge语句之后添加了一个单独的Delete语句。
我还删除了mail_recipient_category表，现在使用更有意义的名称mail_recipient_list。穿梭字段名为(p31_email_list_assignments)。

工作航天飞机LOV定义代码为：

Select last_name || ', ' || first_name || ' -- ' || email_address, prim_key 
From mail_recipient
order by 1;

工作穿梭机源代码是：

declare
  mail_list  apex_application_global.vc_arr2;
  i                   number := 1;
begin
  for r in (Select mr.last_name || ', ' || mr.first_name || ' -- ' || mr.email_address, mr.prim_key
            From mail_recipient mr left join 
              mail_recipient_list mrl
            On mr.prim_key = mrl.recipient_fkey
            Where  mrl.list = :P31_EMAIL_LIST)
  loop
    mail_list(i) := r.prim_key;
    i := i + 1;
  end loop;
  return APEX_UTIL.TABLE_TO_STRING(mail_list, ':');
end;

提交页面后的工作流程代码为：

Begin
Merge Into MAIL_RECIPIENT_LIST mrl
Using (
    Select
        shuttle.column_value shuttle_cv
      , db.recipient_fkey db_rfk
    From
        table(string_to_coll(:P31_email_list_assignments)) shuttle
          left outer join mail_recipient_list db
            on shuttle.column_value = db.recipient_fkey
           and db.list = :P31_email_list) t
On  (mrl.recipient_fkey = t.db_rfk
And  mrl.list = :p31_email_list    
    )
When Matched
Then
    Update
    Set
        mrl.merge_check = mrl.merge_check
--    Delete
--      Where
--      t.shuttle_cv is null

When Not Matched 
  Then
    Insert
      (recipient_fkey, list)
    Values
      (t.shuttle_cv, :P31_email_list);

/* The commented-out delete clause of the Merge statement never worked with this shuttle.
   The following delete statement will every time this page process is called
*/
Delete from MAIL_RECIPIENT_LIST 
Where instr(':' || :P31_email_list_assignments || ':',':' || recipient_fkey || ':') = 0  
And list = :P31_email_list;
end;

我希望有人发现这有用。