我已经编写了一个pl/sql进程来插入从select语句中获取的大容量记录，但在第一次开始后，我将引发错误，而不是插入任何记录帮助我修复插入过程中的错误如何插入从select语句返回的11个gr\ U编号/行它显示找不到数据或超过获取的行异常occour。图像已附加。在此处输入图像描述在此处输入图像描述
DECLARE
vtm NUMBER;
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'
AND gr_number = vtm;
--dbms_output.put_line(vtm);
--for i in 1..tab.count loop
FOR i IN 1..tab.count LOOP 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
gr_number,
student_id,
next_day_flg,
to_date(upper(:p7_attend_dt_tmp), 'DD-MON-YYYY'),
prd_start_tm,
prd_end_tm,
prd_diff_minutes,
course_name,
class_time,
branch_id,
shift_id,
teacher_id,
class_id,
:v_employee_id,
course_id,
class_uid
FROM
(
WITH class_start_tm AS (
SELECT
s.gr_number,
s.class_id,
s.class_time AS student_class_time_list,
ct.class_time,
ct.seq,
to_date(upper(:p7_attend_dt_tmp)
|| ' '
|| ct.class_time, 'DD-MON-YYYY HHMIAM') AS class_first_tm
FROM
student s
JOIN class_time ct ON s.class_id = ct.class_id
WHERE
s.gr_number = :p7_gr_tmp
AND ct.seq = 1
), tm AS (
SELECT
s.gr_number,
cl.duration_minutes,
t.class_first_tm,
to_date(upper(:p7_attend_dt_tmp)
|| ' '
|| to_char(cl.start_time, 'HHMIAM'), 'DD-MON-YYYY HHMIAM') AS prd_start_tm,
to_date(upper(:p7_attend_dt_tmp)
|| ' '
|| to_char(cl.start_time, 'HHMIAM'), 'DD-MON-YYYY HHMIAM') + INTERVAL '30' MINUTE AS prd_end_tm,
(
CASE
WHEN to_date(upper(:p7_attend_dt_tmp)
|| ' '
|| to_char(cl.start_time, 'HHMIAM'), 'DD-MON-YYYY HHMIAM') < t.class_first_tm THEN
'Y'
ELSE
'N'
END
) next_day_flg
FROM
calender_24hr cl
JOIN student s ON instr(s.class_time, cl.time_12hr) > 0
AND s.gr_number = :p7_gr_tmp
JOIN class_start_tm t ON t.gr_number = s.gr_number
), tm1 AS (
SELECT
gr_number,
duration_minutes,
-- class_first_tm,
(
CASE
WHEN next_day_flg = 'Y' THEN
prd_start_tm + 1
ELSE
prd_start_tm
END
) prd_start_tm,
(
CASE
WHEN next_day_flg = 'Y' THEN
prd_end_tm + 1
ELSE
prd_end_tm
END
) prd_end_tm,
next_day_flg
FROM
tm
ORDER BY
(
CASE
WHEN next_day_flg = 'Y' THEN
prd_start_tm + 1
ELSE
prd_start_tm
END
)
), tm2 AS (
SELECT
gr_number,
MAX(next_day_flg) AS next_day_flg,
MIN(prd_start_tm) AS prd_start_tm,
MAX(prd_end_tm) AS prd_end_tm,
round((MAX(prd_end_tm) - MIN(prd_start_tm)) * 24 * 60, 0) AS prd_diff_minutes,
SUM(duration_minutes) actual_minutes,
round((MAX(prd_end_tm) - MIN(prd_start_tm)) * 24 * 60, 0) - SUM(duration_minutes) AS gap_minutes
FROM
tm1
GROUP BY
gr_number
)
SELECT
st.gr_number,
st.student_id,
tm.next_day_flg,
prd_start_tm,
prd_end_tm,
tm.prd_diff_minutes,
tm.actual_minutes,
tm.gap_minutes,
st.course AS course_id,
cr.course_name,
cl.class_uid,
st.class_id,
st.class_time,
st.branch_id,
st.shift_id,
cl.teacher_id
FROM
tm2 tm
JOIN student st ON tm.gr_number = st.gr_number
LEFT JOIN course cr ON cr.course_id = st.course
LEFT JOIN class cl ON cl.class_id = st.class_id
);
END LOOP;
--exception
--when too_many_rows then
EXCEPTION
WHEN no_data_found THEN
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'
AND gr_number = vtm;
dbms_output.put_line('Error, rows = ' || vtm);
end;
END loop;
COMMIT;
end;
2条答案
按热度按时间fhg3lkii1#
ORA-01403（找不到数据）当您的查询不返回任何内容，而您尝试通过INTO.ORA-01422（行太多）当您的查询返回的行数超过您试图容纳的行数时发生INTO.你正在处理
no_data_found从一开始SELECT但您没有处理too_many_rows从第一个查询。如果你的第一次SELECT查询抛出no_data_found异常，将从EXCEPTION，否则如果它抛出too_many_rows例外情况下，它将失败作为你的第二个形象。那你的车呢
no_data_found发生异常？可能是你第二次来的SELECT查询。你应该循环第一个
SELECT查询以避免no_data_found或者too_many_rows确保你的第二个SELECT查询总是返回1行。pbwdgjma2#
找不到的数据实际上被提升了两次。在初始select期间的第一次（begin之后的第二条语句）。第二次由异常过程处理第一次发生的情况。这是因为异常处理程序尝试完全相同的查询。因此得到了完全相同的结果。
那么，为什么第一次出现“未找到”数据呢。将局部变量vtm定义为number，但不初始化该值。这使其值为空。然后继续在select语句的where子句中使用vtm:（and gr_number=vtm；）这个 predicate 将只返回null，从而使整个where子句始终为false，这意味着没有数据满足where条件。
更正：我不知道。您没有提供示例数据或参数值。但是，您必须以某种方式将变量vtm初始化为现有的gr\u编号（这可能不是唯一的原因），或者从查询中删除该变量。再次强调，删除它可能是导致“太多行”异常的原因。
如果是这种情况，那么你需要转换“选择。。。以及从处理集合到处理游标的循环。
免责声明：我没有验证变量vtm，tab不在insert的扩展中，也没有尝试在该扩展中进行其他更改（如果有的话）。但很可能需要一些。