存储过程不在 100 000 行的 for 循环中运行

Question

我有一个包含 100000 条记录的表，我必须对每一行进行计算，我使用以下 SP 进行计算

PROCEDURE PROCESS_DATA(TOTAL_PREG_IN IN NUMBER, SIN_RESPUESTA_IN IN NUMBER, CORRECTAS_IN IN NUMBER, ERRONEAS_IN IN NUMBER, TIEMPO_IN IN VARCHAR2, NOTAS_DATOS_ID_IN IN NUMBER, ESQUEMA_IN IN NUMBER, FilasAfectadas OUT NUMBER)
IS
CALIFICACION NUMBER;
REGLA NUMBER;
LEVEL_ID NUMBER;
TIEMPO_AUX VARCHAR2(5);
TIEMPO NUMBER;
e_div_zero EXCEPTION;
BEGIN
IF (CORRECTAS_IN IS NOT NULL AND ERRONEAS_IN IS NOT NULL) THEN
IF (SIN_RESPUESTA_IN = 0 AND (CORRECTAS_IN + ERRONEAS_IN) = TOTAL_PREG_IN) THEN
REGLA:=1;
IF(TOTAL_PREG_IN = 0) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN)/ TOTAL_PREG_IN;
ELSE IF ((SIN_RESPUESTA_IN >= 1 AND SIN_RESPUESTA_IN <= 4) AND (CORRECTAS_IN + ERRONEAS_IN + SIN_RESPUESTA_IN) = TOTAL_PREG_IN) THEN
REGLA := 2;
IF(TOTAL_PREG_IN = 0) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN)/ TOTAL_PREG_IN;
ELSE IF ((SIN_RESPUESTA_IN = 5 OR SIN_RESPUESTA_IN = 10) AND (CORRECTAS_IN + ERRONEAS_IN + SIN_RESPUESTA_IN) = TOTAL_PREG_IN) THEN
REGLA := 3;
IF(CORRECTAS_IN + ERRONEAS_IN = 0) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN)/(CORRECTAS_IN + ERRONEAS_IN);
ELSE IF ((SIN_RESPUESTA_IN >= 6 AND SIN_RESPUESTA_IN <= 9) AND (CORRECTAS_IN + ERRONEAS_IN) = TOTAL_PREG_IN) THEN
REGLA := 4;
IF(TOTAL_PREG_IN = 0) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN)/ TOTAL_PREG_IN;
ELSE IF (CORRECTAS_IN + ERRONEAS_IN + SIN_RESPUESTA_IN != TOTAL_PREG_IN) THEN
REGLA := 5;
IF(CORRECTAS_IN + ERRONEAS_IN + SIN_RESPUESTA_IN = 0 ) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN) / (CORRECTAS_IN + ERRONEAS_IN + SIN_RESPUESTA_IN );
ELSE IF (SIN_RESPUESTA_IN > 10) THEN
REGLA := 6;
IF((CORRECTAS_IN + ERRONEAS_IN) > (TOTAL_PREG_IN/2))THEN
IF(CORRECTAS_IN + ERRONEAS_IN = 0 ) THEN
RAISE e_div_zero;
END IF;
CALIFICACION := (CORRECTAS_IN * ESQUEMA_IN) / (CORRECTAS_IN + ERRONEAS_IN);
ELSE
CALIFICACION := ESQUEMA_IN * 0.8;
END IF;
ELSE IF (CORRECTAS_IN = 0 AND ERRONEAS_IN = 0) THEN
REGLA := 7;
SELECT LEVEL AS id, REGEXP_SUBSTR(TIEMPO_IN, '[^:]+', 1, LEVEL) AS data
INTO LEVEL_ID, TIEMPO_AUX
FROM dual where level = 2
CONNECT BY REGEXP_SUBSTR(TIEMPO_IN, '[^:]+', 1, LEVEL) IS NOT NULL;
TIEMPO := TO_NUMBER(TIEMPO_AUX);
IF(TIEMPO >= 9) THEN
CALIFICACION := ESQUEMA_IN * 0.8;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
ELSE 
REGLA:=7;
SELECT LEVEL AS id, REGEXP_SUBSTR(TIEMPO_IN, '[^:]+', 1, LEVEL) AS data
INTO LEVEL_ID, TIEMPO_AUX
FROM dual where level = 2
CONNECT BY REGEXP_SUBSTR(TIEMPO_IN, '[^:]+', 1, LEVEL) IS NOT NULL;
TIEMPO := TO_NUMBER(TIEMPO_AUX);
IF(TIEMPO >= 9) THEN
CALIFICACION := ESQUEMA_IN * 0.8;
END IF;
END IF;
EXCEPTION 
WHEN e_div_zero THEN
calificacion := -1;
INSERT INTO TABLA_AC AC (AC.AC_REGLA, AC.AC_FECHA_CALIFICACION, AC.AC_CALIFICACION, AC.AC_ARCHIVONOTAS_ID)
VALUES (REGLA, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'), CALIFICACION, NOTAS_DATOS_ID_IN );
FilasAfectadas:= SQL%RowCount;
COMMIT;
END PROCESS_DATA;

我在 for 循环中的另一个 sp 中调用这个 sp

PROCEDURE MASIVE_PROCESS(FilasAfectadas_OUT OUT NUMBER)
IS
datos_notas t_row_datos_nota;
contador number := 0;
contador2 number := 0;
BEGIN
SELECT nd.* bulk collect INTO datos_notas 
FROM SGC_ARCHIVO_NOTAS_DATOS nd
inner join SGC_ARCHIVO_NOTAS an ON an.AN_ID = nd.AND_ID_ARCHIVO;
FOR idx IN datos_notas.FIRST..datos_notas.LAST
Loop
DB_ESCHEMA.PKG_TEST.PROCESS_DATA(
TOTAL_PREG_IN        => datos_notas(idx).AND_TOTAL_PREG,
SIN_RESPUESTA_IN     => datos_notas(idx).AND_SIN_RESPUESTA,
CORRECTAS_IN         => datos_notas(idx).AND_CORRECTAS,
ERRONEAS_IN          => datos_notas(idx).AND_ERRONEAS,
TIEMPO_IN            => datos_notas(idx).AND_TIEMPO,
NOTAS_DATOS_ID_IN        => datos_notas(idx).AND_ID,
ESQUEMA_IN           => datos_notas(idx).AND_ESQUEMA_CALF,
FILASAFECTADAS   => contador);
contador2 := contador2 +1;
DBMS_OUTPUT.put_line ('cont ' || contador2 );
END LOOP;
FilasAfectadas_OUT := contador;
END MASIVE_PROCESS;

变量 counter2 达到 100000 条记录，但只插入在 for 循环中执行的 sp 的 256 或 512 条记录。

这段代码有什么问题？

Answer 1

根据代码中的缩进，您的意图似乎是，如果出现除以零错误，则calificacion设置为 1，然后完成异常处理。 然而，这不是你写的。EXCEPTION和END process_data之间的所有内容都是异常处理程序的一部分。 因此，只有在有除以零错误的情况下，您才能插入tabla_ac。

EXCEPTION 
WHEN e_div_zero THEN
calificacion := -1;
INSERT INTO TABLA_AC AC (AC.AC_REGLA, AC.AC_FECHA_CALIFICACION, AC.AC_CALIFICACION, AC.AC_ARCHIVONOTAS_ID)
VALUES (REGLA, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'), CALIFICACION, NOTAS_DATOS_ID_IN );
FilasAfectadas:= SQL%RowCount;
COMMIT;
END PROCESS_DATA;

我希望你打算用一个额外的嵌套PL/SQL块来做这样的事情

BEGIN
BEGIN -- BEGIN the new nested PL/SQL block
IF (CORRECTAS_IN IS NOT NULL AND ERRONEAS_IN IS NOT NULL) THEN
<<rest of your code>>
END IF;
EXCEPTION 
WHEN e_div_zero THEN
calificacion := -1;
END; -- END the nested PL/SQL block
INSERT INTO TABLA_AC AC (AC.AC_REGLA, AC.AC_FECHA_CALIFICACION, AC.AC_CALIFICACION, AC.AC_ARCHIVONOTAS_ID)
VALUES (REGLA, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'), CALIFICACION, NOTAS_DATOS_ID_IN );
FilasAfectadas:= SQL%RowCount;
COMMIT;
END PROCESS_DATA;

其他一些与您的直接问题无关的评论..

1( 如果要具有多个ELSE条件的IF语句，请使用ELSIF而不是开始新的IF语句。 这样你就不会连续得到六个END IF语句。 说起来更有意义

IF <<condition 1>> THEN
<<do something>>
ELSIF <<condition 2>> THEN
<<do something else>>
ELSIF <<condition 3>> THEN
<<do a third thing>>
ELSE
<<do a final thing>>
END IF;

2( 如果过程中有COMMIT，则绝大多数运行时将是尝试运行 100,000 次提交的结果。 这将比插入 100,000 行并在外部过程中执行单个提交花费数百倍的时间。

您的代码似乎不是设计为可重入的，因此进行临时提交在功能上也可能有问题。 如果处理了 50,000 行并且代码引发异常，会发生什么情况？ 您没有明显的方法可以在第 50,001 行重新启动进程，因此如果您重新运行循环，您最终会在tabla_ac中得到 150,000 行，而不是 100,000 行，这不太可能是您想要的。