sql - 尝试使用循环将多个值存储到一个变量中，并使用该特定变量插入

Question

我想写pl/sql来存储多个值 进入一个变量并代表该变量我想插入在变量中返回的值。但它不起作用 - 我该怎么办？请帮帮我。我的第一个 select 语句总是返回 12-13 行，我想将这些行存储到 vtm多变的。

DECLARE
   vtm  NUMBER (38);

   -- vtm student.gr_number%TYPE;
   tab  apex_application_global.vc_arr2;
BEGIN
   tab := APEX_UTIL.string_to_table (vtm);

   SELECT s.gr_number
     INTO vtm
     FROM student s
          LEFT JOIN class_time ct
             ON     ct.class_id = s.class_id
                AND INSTR (s.class_time, ct.class_time) > 0
    WHERE     UPPER (TRIM (ct.class_id)) = UPPER (TRIM ( :APP_USER))
          AND s.gr_number IS NOT NULL
          AND is_active_flg = 'Y';

   DBMS_OUTPUT.put_line (vtm);

   FOR i IN vtm .. tab.COUNT
   LOOP
      BEGIN
         INSERT INTO student_class_attend (gr_number,
                                           student_id,
                                           period_next_day_flg,
                                           attend_date,
                                           period_start_dt,
                                           period_end_dt,
                                           period_duration,
                                           course_name,
                                           class_time,
                                           branch_id,
                                           shift_id,
                                           teacher_id,
                                           class_id,
                                           marked_by,
                                           course_id,
                                           class_uid)
              VALUES ( :P7_GR_NUMBER,
                      :P7_STUDENT_ID,
                      :P7_PERIOD_NEXT_DAY_FLG,
                      TO_DATE (UPPER ( :P7_ATTEND_DATE), 'DD-MON-YYYY'),
                      :P7_PERIOD_START_DT,
                      :P7_PERIOD_END_DT,
                      :P7_PERIOD_DURATION,
                      :P7_COURSE_NAME,
                      :P7_CLASS_TIME,
                      :P7_BRANCH_ID,
                      :P7_SHIFT_ID,
                      :P7_TEACHER_ID,
                      :P7_CLASS_ID,
                      :v_employee_id,
                      :P7_COURSE_ID,
                      :P7_CLASS_UID);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('there is no data..');
      END;
   END LOOP;

   COMMIT;
END;

查看我想存储到 vtm 中的 gr 图像列表，然后一键插入所有记录 enter image description here

Answer 1

长话短说，你以错误的方式做这件事，使事情过于复杂。一切都可以在一条 SQL 语句中完成；不需要数组，循环......什么都没有。

INSERT INTO STUDENT_CLASS_ATTEND (gr_number,
                                  student_id,
                                  PERIOD_NEXT_DAY_FLG,
                                  attend_date,
                                  period_start_dt,
                                  period_end_dt,
                                  PERIOD_DURATION,
                                  course_name,
                                  class_time,
                                  branch_id,
                                  shift_id,
                                  teacher_id,
                                  class_id,
                                  marked_by,
                                  course_id,
                                  class_uid)
   SELECT :P7_GR_NUMBER,
          :P7_STUDENT_ID,
          :P7_PERIOD_NEXT_DAY_FLG,
          TO_DATE (UPPER ( :P7_ATTEND_DATE), 'DD-MON-YYYY'),
          :P7_PERIOD_START_DT,
          :P7_PERIOD_END_DT,
          :P7_PERIOD_DURATION,
          :P7_COURSE_NAME,
          :P7_CLASS_TIME,
          :P7_BRANCH_ID,
          :P7_SHIFT_ID,
          :P7_TEACHER_ID,
          :P7_CLASS_ID,
          s.gr_number,       --> this is what you wanted to put into VTM
          :P7_COURSE_ID,
          :P7_CLASS_UID
     FROM student s
          LEFT JOIN class_time ct
             ON     ct.class_id = s.class_id
                AND INSTR (s.class_time, ct.class_time) > 0
    WHERE     UPPER (TRIM (ct.class_id)) = UPPER (TRIM ( :APP_USER))
          AND s.gr_number IS NOT NULL
          AND is_active_flg = 'Y';