I have a requirement in oracle forms to write a PLSQL block that would read from the database through a cursor that contains comma separated record. I have completed the logic but wanted to check if this logic which I have developed can be improved in any way.

我在Oracle Forms中要求编写一个PLSQL块，该块将通过包含逗号分隔记录的游标从数据库中读取数据。我已经完成了逻辑，但想检查一下我开发的这个逻辑是否可以以任何方式改进。

DECLARE
CURSOR vest_hist (p_ind_id NUMBER,
                  p_plan_id NUMBER)
IS  
SELECT narrative
  FROM dummy_ev_tbld
 WHERE subject_id = p_ind_id
   AND sub_subject_id = p_plan_id
   AND evty_code = 'VEDC'
 ORDER BY dpdate_time;
    
 v_narrative dummy_ev_tbld.narrative%TYPE;
 v_hire_date VARCHAR2(11);
 v_ev_id VARCHAR2(10);
 v_step_seqnbr VARCHAR2(3);
 v_credited_hours VARCHAR2(10);
 BEGIN
 OPEN vest_hist(123,
                123);
 LOOP -- looping for the child ga_id's
 FETCH vest_hist INTO v_narrative;
 EXIT WHEN vest_hist%notfound;   
 v_hire_date := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,3)+1,INSTR(v_narrative,',',1,4)-INSTR(v_narrative,',',1,3)-1),'N/A');
 v_ev_id := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,4)+1,INSTR(v_narrative,',',1,5)-INSTR(v_narrative,',',1,4)-1),'N/A');
 v_step_seqnbr := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,5)+1,INSTR(v_narrative,',',1,6)-INSTR(v_narrative,',',1,5)-1),'N/A');
 v_credited_hours := NVL(SUBSTR(v_narrative,INSTR(v_narrative,',',1,9)+1,INSTR(v_narrative,',',1,10)-INSTR(v_narrative,',',1,9)-1),'N/A');`your text`
         /*DO SOMETHING with these variables......*/
 END LOOP;
 EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('E-1526-0004-0038-'||SQLCODE||':Contact Systems');
 RAISE FORM_TRIGGER_FAILURE;    
 END;

So I am using the substr and instr to split the narrative data and then storing them in the respective variable. So is there any way I can do it in one query or logic?

因此，我使用substr和instr来拆分叙事数据，然后将它们存储在各自的变量中。那么有没有什么方法可以在一个查询或逻辑中完成呢？

The v_narrative data: 14-JUL-2019,92149,1,10

V_叙事数据：2019年7月14日，92149，1，10

There are a few things you could pay attention to.

有几件事你可以注意一下。

The first one is that code you posted won't work because of NVL function calls. One of local variables is DATE, the rest of them are NUMBERs and you can't put N/A into any of them because that's a string.

第一个问题是，由于NVL函数调用，您发布的代码将无法工作。其中一个局部变量是DATE，其余是数字，你不能把N/A放入其中任何一个，因为那是一个字符串。

Furthermore, are you sure that - for sample narrative value (14-JUL-2019,92149,1,10) your substrings return correct values? You're searching for e.g. 9th or 10th appearance of a comma character in it. There aren't that many commas there.

此外，对于样本叙述值(14-07-2019,92149，1，10)，您确定您的子串返回了正确的值吗？例如，您正在搜索逗号字符在其中出现的第9次或第10次。那里没有那么多逗号。

As of exception handler: although it won't fail, it won't do anything because Oracle Forms is incapable of displaying output of dbms_output.put_line. Use message built-in (call it twice so that is displayed in a pop-up window on the screen, or switch to alert.

截至异常处理程序：虽然它不会失败，但它不会执行任何操作，因为Oracle Forms无法显示DBMS_output.putline的输出。使用Message内置(呼叫两次，使其显示在屏幕上的弹出窗口中，或切换到警报。

How to simplify that code? Get rid of explicitly declared cursor, cursor variable, local variables, opening the cursor (and closing it - which is what you failed to do), worrying about exiting the loop and switch to a cursor for loop.

如何简化这些代码？去掉显式声明的游标、游标变量、局部变量，打开游标(和关闭游标--您没有做到这一点)，担心退出循环并切换到游标for循环。

Here's a suggestion which shows alternative way of doing it.

这里有一个建议，它展示了做这件事的替代方法。

 begin
   for cur_r in 
     (select to_date(substr(narrative, 1, 11), 'dd-mon-yyyy', 'nls_date_language = english') hire_date,
       substr(narrative, instr(narrative, ',', 1, 1) + 1,
                   instr(narrative, ',', 1, 2) - instr(narrative, ',', 1, 1) - 1
             ) ev_id,
       substr(narrative, instr(narrative, ',', 1, 2) + 1,
                   instr(narrative, ',', 1, 3) - instr(narrative, ',', 1, 2) - 1
             ) step_seqnbr,
       substr(narrative, instr(narrative, ',', 1, 3) + 1) credited_hours             
      from dummy_ev_tbld
   ) loop
     null;
     -- do something with values returned by cursor. Reference them as e.g.
     -- if cur_r.hire_date is null then ...
  end loop;
exception
  when others then
    message('E-1526-0004-0038-'||SQLCODE||':Contact Systems');
    raise form_trigger_failure;
end;
/