为集合变量赋值时出错

wqsoz72f  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(338)
procedure ins_note (pi_data in note_detail_arr)   

is 
begin

   FOR i IN pi_data.first..pi_data.last loop

    IF pi_data(i).template_id IN (022, 019, 015, 017, 021, 001, 010, 016, 018, 020, 023) AND pi_data(i).channel_id IS NULL THEN
        pi_data(i).channel_id := 'XLS';
    END IF;
      INSERT INTO note_msg (template_id,channel_id)                                                      
                                VALUES (
                                 pi_data(i).template_id, pi_data(i).channel_id);                  
                     end loop;
commit;
end;
jm2pwxwz

jm2pwxwz1#

piu数据是一个元素数组,它是一个in参数。不能将值赋给参数中的。声明一个局部变量来存储通道id,或者声明一个note\u msg%rowtype类型的局部变量,并用它来存储通道id。您没有提到note\u detail\u arr的声明是什么,所以我定义了一个rowtype类型的局部变量。例子:

procedure ins_note(pi_data IN note_detail_arr) 
is 
  l_note_msg_row note_msg%ROWTYPE;
BEGIN
  FOR i IN pi_data.first..pi_data.last LOOP
    l_note_msg_row.channel_id := pi_data(i).channel_id;
    IF
      pi_data(i).template_id IN (022, 019, 015, 017, 021, 001, 010, 016, 018, 020, 023 ) AND pi_data(i).channel_id IS NULL
    THEN
      l_note_msg_row.channel_id := 'XLS';
    END IF;

    INSERT INTO note_msg (
      template_id,
      channel_id
    ) VALUES (
      pi_data(i).template_id,
      l_note_msg_row.channel_id
    );

  END LOOP;
  -- you should not commit in your procedure. Instead only commit when your complete transaction is done from code that calls this proc.
  COMMIT;   
END ins_note;

相关问题