如何将未选中的复选框值提取到Oracle Apex中的游标中

Question

下面是我的PL/SQL代码，用于在按钮的动态操作中获取复选框值。

DECLARE
IHC_ID_Y VARCHAR2(9);
IHC_ID_N VARCHAR2(9);
CURSOR Y IS
    SELECT regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level)
    FROM DUAL
    CONNECT BY regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) IS NOT NULL;
CURSOR N IS
    SELECT regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level)
    FROM DUAL
    CONNECT BY regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) IS NULL;
BEGIN
    OPEN Y;
    OPEN N;
    LOOP
        FETCH Y INTO IHC_ID_Y;
        EXIT WHEN Y%NOTFOUND;
        INSERT INTO INFANT_RESPONSE (INF_ID, IHC_ID, IR_DESCRIPTION) VALUES (:INF_ID, IHC_ID_Y, 'Y'); 
    END LOOP;
    CLOSE Y;
        LOOP
        FETCH N INTO IHC_ID_N;
        EXIT WHEN N%NOTFOUND;
        INSERT INTO INFANT_RESPONSE (INF_ID, IHC_ID, IR_DESCRIPTION) VALUES (:INF_ID, IHC_ID_N, 'N'); 
    END LOOP;
    CLOSE N;
END;

然而，我在这部分遇到了一个问题，

CURSOR N IS
    SELECT regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level)
    FROM DUAL
    CONNECT BY regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) IS NULL;

 LOOP
        FETCH N INTO IHC_ID_N;
        EXIT WHEN N%NOTFOUND;
        INSERT INTO INFANT_RESPONSE (INF_ID, IHC_ID, IR_DESCRIPTION) VALUES (:INF_ID, IHC_ID_N, 'N')

如您所见，当我将条件从IS NOT NULL更改为IS NULL时，所获取的值都将为空，因此不能插入到表行中。有任何方法来获取未选中的复选框值吗？

Answer 1

我建议采取另一种方法：

• insert已检查的运行状况(您知道如何标识它们；它是您的游标Y)；我也使用了它，但是在游标for循环中使用它要简单得多--您不必显式打开它，请注意退出循环或何时关闭游标-- Oracle会为您执行此操作。为了插入未检查的健康状况，请对表中存在的所有条件执行此操作，以便在第一步

中创建未“选中”的复选框。

就像这样：

begin
  -- Checked conditions (using your cursor, but as a cursor FOR loop)
  for cur_y in 
    (SELECT regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) ihc_id_y
     FROM DUAL
     CONNECT BY regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) IS NOT NULL
    )
  loop
    insert into infant_response (inf_id, ihc_id, ir_description)
    values (:inf_id, cur_y.ihc_id_y, 'Y');
  end loop;

  -- To insert unchecked conditions, insert values that exist in the table
  -- that contains ALL health conditions (let's call it LIST_OF_HEALTH_CONDITIONS)
  -- that aren't inserted as "checked" in the above cursor FOR loop
  insert into infant_response (inf_id, ihc_id, ir_description)
  select :inf_id, 
         h.ihc_id,
         'N'
  from list_of_health_conditions h
  where h.ihc_id not in (select r.ihc_id
                         from infant_response r
                         where r.inf_id = :inf_id
                           and r.ir_description = 'Y'
                        );
end;                        

不能测试它，因为我没有您的表或数据，但是-除非我做了一个错误，这应该是可以的。

Answer 2

这就是我如何使用MINUS集操作符来实现我想要的结果。

CURSOR N IS
    SELECT IHC_ID FROM INFANT_HEALTH_CONDITIONS
    MINUS
    SELECT regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level)
    FROM DUAL
    CONNECT BY regexp_substr(:INFANT_HEALTH_CONDITIONS, '[^:]+', 1, level) IS NOT NULL;