查询基于变量的PL/SQL游标

Question

我有一个基本上是的PL/SQL块

DECLARE
PIDM            NUMBER(8);
CLM_TEST_SCORE  NUMBER(5);
CURSOR C_STUDENT IS
select PIDM
from   SOSC.DW_ALL_COLLECTOR
order by PIDM;
CURSOR C_CLM_SCORES IS
select max(to_number(SORTEST_TEST_SCORE))
from   SATURN.SORTEST
where  SORTEST_PIDM   = pidm;
BEGIN
OPEN C_STUDENT;
LOOP
CLM_TEST_SCORE := '';
FETCH c_Student INTO pidm;
EXIT WHEN c_Student%notfound;
OPEN C_CLM_SCORES;
FETCH C_CLM_SCORES INTO CLM_TEST_SCORE;
CLOSE C_CLM_SCORES;

insert into some_table (CLM_TEST_SCORE)
values (CLM_TEST_SCORE);
END LOOP
END

据我所知，C_CLM_SCORES中引用的pidm是第2行中声明的PIDM NUMBER(8)。这意味着游标引用的查询在LOOP的每次迭代中都会发生变化，具体取决于pidm的当前值。这与我对游标作为正在进行的查询的理解不一致，因为底层查询在每个LOOP中都会发生变化。也许是原作者利用了一个聪明的DB算法？

此代码工作。我完全不知道为什么。这到底是怎么回事？

Answer 1

您有一个过于混乱的代码块，这是一个噩梦般的调试：

引用列名和具有相同标识符的局部变量(PIDM和CLM_TEST_SCORE(的SQL语句

每次迭代都会更改的游标，因为它们包含引用局部变量(PIDM(的绑定变量

循环使用效率极低

如果你想让它更清楚，你可以重写PL/SQL块，这样你就不会有重复的标识符，并使用参数化的光标：

DECLARE
v_PIDM            SOSC.DW_ALL_COLLECTOR.PIDM%TYPE;
v_CLM_TEST_SCORE  some_table.CLM_TEST_SCORE%TYPE;
CURSOR C_STUDENT IS
select PIDM
from   SOSC.DW_ALL_COLLECTOR
order by PIDM;
CURSOR C_CLM_SCORES(p_pidm NUMBER) IS
select max(to_number(SORTEST_TEST_SCORE))
from   SATURN.SORTEST
where  SORTEST_PIDM = p_pidm;
BEGIN
OPEN C_STUDENT;
LOOP
FETCH c_Student INTO v_pidm;
EXIT WHEN c_Student%notfound;
OPEN C_CLM_SCORES(v_pidm);
FETCH C_CLM_SCORES INTO v_CLM_TEST_SCORE;
CLOSE C_CLM_SCORES;

insert into some_table (CLM_TEST_SCORE)
values (v_CLM_TEST_SCORE);
END LOOP;
END;
/

然而，这仍然是非常低效的，因为每次迭代都执行SELECT和INSERT，并将生成日志条目。您可以将整个事情重写为一条SQL语句，使其更加简单高效：

INSERT INTO some_table (clm_test_score)
SELECT ( select max(to_number(SORTEST_TEST_SCORE))
from   SATURN.SORTEST s
where  s.SORTEST_PIDM = c.pidm )
FROM   SOSC.DW_ALL_COLLECTOR c;

db<gt；小提琴这里

Answer 2

问题中的代码是"为什么要使用隐式游标&"；。如果你把你的代码重写如下，它会变得更容易理解：

BEGIN
FOR rowStudent IN (select PIDM
from SOSC.DW_ALL_COLLECTOR
order by PIDM)
LOOP
FOR rowScores IN (select max(to_number(SORTEST_TEST_SCORE)) AS CLM_TEST_SCORE
from SATURN.SORTEST
where SORTEST_PIDM = rowStudent.PIDM)
LOOP
insert into some_table (CLM_TEST_SCORE)
values (rowScores.CLM_TEST_SCORE);
END LOOP;  -- rowScores
END LOOP;  -- rowStudent
END;

这消除了所有的变量和光标定义，所有的代码都在您眼前，您可以一眼看到它。

如果你想进一步收紧它，你可以使用一个连接来减少到一个光标：

BEGIN
FOR rowStudent_scores IN (SELECT d.PIDM, MAX(TO_NUMBER(s.SORTEST_TEST_SCORE)) AS CLM_TEST_SCORE
FROM SOSC.DW_ALL_COLLECTOR d
INNER JOIN SATURN.SORTEST s
ON s.SORTEST_PIDM = d.PIDM
GROUP BY d.PIDM)
LOOP
insert into some_table (CLM_TEST_SCORE)
values (rowStudent_scores.CLM_TEST_SCORE);
END LOOP;  -- rowStudent_scores
END;