批量插入记录时未找到数据或发生行数过多异常错误 [英] No data found or too many rows exception error occured during bulk record insertion
问题描述
我编写了一个 pl/sql 进程来插入从 select 语句获取的批量记录之后首先开始，但我会引发错误并且不插入任何记录帮助我修复错误插入如何插入从 select 语句返回的 11 个 gr_numbers/rows它显示未找到数据或获取的行数更多异常发生.图片已附加.在此处输入图片描述在此处输入图片描述
I have Write a pl/sql process to insert bulk record which is getting from select statement after first begin but i will raised error and not insert any record help me to fix the error during insertion How to Insert my 11 gr_numbers/rows which is returned from select statement It Shows No Data found or More then rows fetched exceptions occour. Image has attached. enter image description here enter image description here
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;
推荐答案
no_data_found 实际上被引发了两次.在初始选择期间的第一次(begin 之后的第二条语句).第二次由异常过程处理第一次出现.发生这种情况是因为您的异常处理程序尝试完全相同的查询.因此完全相同的结果.
那么为什么第一次提出 no_data_found .您将局部变量 vtm 定义为数字，但未初始化该值.这使得它的值为 NULL.然后继续在 select 语句的 where 子句中使用 vtm :(AND gr_number = vtm;) 这个谓词只会返回 NULL 从而使整个 where 子句始终为 false，这意味着没有数据满足 where 条件.
更正:嗯，我不知道.您没有提供示例数据或参数值.但是，您必须以某种方式将变量 vtm 初始化为现有的 gr_number(这可能不是唯一的原因)或从查询中删除该变量.然后再次删除它可能是导致太多行"异常的原因.
如果是这种情况，那么您需要将select ... into ..."和从处理集合到处理游标的循环转换.
The no_data_found is actually being raised twice. The first time during the initial select (the 2nd statement after begin). The second time by the exception procedure handling the first occurrence. This occurs because your exception handler attempts the exact same query. Thus the exact same result.
So why is the no_data_found raised the first time. You define the local variable vtm as number, but you do not initialize the value. This makes its value NULL. You then go on to use vtm in the where clause of the select statement :(AND gr_number = vtm;) This predicate will only return NULL thus making the entire where clause always false, which means no data satisfies the where condition.
Correction: Well I don't know. You didn't provide sample data nor parameter values. However, somehow you must initialize the variable vtm to an existing gr_number (that may NOT be the only cause) or remove the variable from the query. Then again perhaps removing it is what caused your "too many rows" exception.
If that is the case then you need to convert "select ... into ..." and and the loop from processing a collection to processing the cursor.
declare
gr_cursor cursor is
( select s.gr_number
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
);
begin
for grc in gr_cursor
loop
insert into student_class_attend (
... ;
end loop;
commit;
end;
免责声明:我没有验证变量 vtm、tab 不在插入的扩展中，也没有尝试在该扩展中进行其他更改(如果有).但很可能有一些需要.
Disclaimer: I did NOT verify the variables vtm, tab are not in the expansion for the insert, nor attempt other changes, if any, within that expansion. But there may very well be some needed.
这篇关于批量插入记录时未找到数据或发生行数过多异常错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
