sql - 如何在Oracle Apex中将未经检查的复选框值提取到游标中(How to fetch unchecked checkbox value into a cursor in Oracle Apex)

Question

Here is my PL/SQL code to fetch checked checkbox value in a button's dynamic action.

(这是我的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;

However, I met a problem in this part,

(但是，我在这部分遇到了一个问题，)

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')

As you can see, when I change the condition from IS NOT NULL to IS NULL , the values being fetched will all be nulls and hence cannot be inserted into the table rows.

(如您所见，当我将条件从IS NOT NULL更改为IS NULL ，要获取的值将全部为null，因此无法将其插入表行中。)

Is there any way to fetch the unchecked checkbox values?

(有什么方法可以获取未选中的复选框值？)

  ask by Holmes Queen translate from so

Answer 1

I'd suggest a different approach:

(我建议使用另一种方法：)

• insert checked health conditions (as you know how to identify them; it is your cursor Y );

  (插入已检查的健康状况（您知道如何识别它们；它是您的光标Y ）；)

  I used it as well, but within the cursor FOR loop as it is way simpler to use - you don't have to explicitly open it, pay attention about exiting the loop nor when to close the cursor - Oracle does that for you.

  (我也使用了它，但是在游标FOR循环中，因为它使用起来更简单-您不必显式打开它，不必注意退出循环或何时关闭游标-Oracle会为您做到这一点。)

• in order to insert unchecked health conditions, do that for all conditions that exist in a table you use to create those checkboxes that aren't inserted as "checked" in the first step

  (为了插入未检查的健康状况，请针对您用于创建第一步中未插入为“已检查”复选框的表中存在的所有状况进行操作)

Something like this:

(像这样：)

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;                        

Can't test it as I don't have your tables nor data, but - unless I made a typo, that should be OK.

(无法测试它，因为我没有您的表或数据，但是-除非我输入错误，否则应该可以。)