Oracle 存储过程与 case when 位于 where 原因中

Question

以下存储过程出现错误。看来错误是在 where 原因的 case 语句中。我该如何修复它？

create or replace PROCEDURE APPEND_HIST_TBLS_PROC AS 
CurTerm varchar2(4) := '1222';
PS_ACAD_PROG_HISTORY varchar2(35) := 'PS_ACAD_PROG_HISTORY_' || CurTerm;
Begin
execute immediate 'insert into ' || PS_ACAD_PROG_HISTORY
|| '(select sysdate as date_created,
EFFDT, 
ADMIT_TERM, 
EXP_GRAD_TERM, 
CAMPUS
from ERP_ACAD
where CASE WHEN SUBSTR(ADMIT_TERM,4,1)= '6' THEN SUBSTR(ADMIT_TERM,1,3) || '9' ELSE ADMIT_TERM END = '||CurTerm ||'
)';
END APPEND_HIST_TBLS_PROC;

Answer 1

动态SQL很难维护和调试。过程编译成功这一事实没有告诉关于动态语句本身的信息 - 它只是说过程“按原样”没有错误。

编写语句并将其存储到局部变量中，然后显示其内容是一个好习惯；一旦确认没问题，就执行它。

另外，由于您必须转义单引号（有时它会变得令人讨厌，有几个连续的单引号可以满足您的需要），因此使用q-quoting机制可以让您编写“正常”语句（那么单引号确实是单引号）。

像这样的事情：

SQL> CREATE OR REPLACE PROCEDURE append_hist_tbls_proc
  2  AS
  3     curterm               VARCHAR2 (4) := '1222';
  4     ps_acad_prog_history  VARCHAR2 (35) := 'PS_ACAD_PROG_HISTORY_' || curterm;
  5     l_str                 VARCHAR2 (4000);
  6  BEGIN
  7     -- Compose the INSERT statement into a VARCHAR2 local variable so that you'd be able
  8     -- to check whether you did it right or not.
  9     -- Use the q-quoting mechanism as it helps with consecutive single quotes issues
 10     l_str :=
 11           'INSERT INTO '
 12        || ps_acad_prog_history
 13        || q'[ SELECT sysdate as date_created,
 14                      effdt,
 15                      admit_term,
 16                      exp_grad_term,
 17                      campus
 18        FROM erp_acad
 19        WHERE CASE WHEN SUBSTR(admit_term, 4, 1) = '6' THEN
 20                        SUBSTR(admit_term, 1, 3) || '9'
 21                   ELSE ADMIT_TERM
 22              END = ]'
 23        || curterm;
 24
 25     -- FIRST check the command you're about to execute
 26     DBMS_OUTPUT.put_line (l_str);
 27
 28     -- When you verified that it is correct, then comment DBMS_OUTPUT.PUT_LINE call
 29     -- and uncomment EXECUTE IMMEDIATE
 30     -- EXECUTE IMMEDIATE l_str;
 31  END append_hist_tbls_proc;
 32  /

Procedure created.

让我们尝试一下：

SQL> SET SERVEROUTPUT ON
SQL> EXEC append_hist_tbls_proc;
INSERT INTO PS_ACAD_PROG_HISTORY_1222 SELECT sysdate as date_created,

effdt,
                    admit_term,
                    exp_grad_term,

campus
      FROM erp_acad
      WHERE CASE WHEN SUBSTR(admit_term, 4, 1) = '6'
THEN
                      SUBSTR(admit_term, 1, 3) || '9'
                 ELSE
ADMIT_TERM
            END = 1222

PL/SQL procedure successfully completed.

SQL>

输出看起来很难看（这是在过程中让它漂亮的代价），但是 - 如果你格式化它 - 它看起来像这样：

INSERT INTO PS_ACAD_PROG_HISTORY_1222
   SELECT SYSDATE AS date_created,
          effdt,
          admit_term,
          exp_grad_term,
          campus
     FROM erp_acad
    WHERE CASE
             WHEN SUBSTR (admit_term, 4, 1) = '6'
             THEN
                SUBSTR (admit_term, 1, 3) || '9'
             ELSE
                ADMIT_TERM
          END = 1222

所以，如果你真的可以执行它（我不能，我没有你的表），然后取消注释立即执行并使用该过程。否则，请修复该语句。

Answer 2

似乎是引用的问题。您是否尝试过此操作：

create or replace PROCEDURE APPEND_HIST_TBLS_PROC AS 
CurTerm varchar2(4) := '1222';
PS_ACAD_PROG_HISTORY varchar2(35) := 'PS_ACAD_PROG_HISTORY_' || CurTerm;
Begin
execute immediate 'insert into ' || PS_ACAD_PROG_HISTORY
|| '(select sysdate as date_created,
EFFDT, 
ADMIT_TERM, 
EXP_GRAD_TERM, 
CAMPUS
from ERP_ACAD
where CASE WHEN SUBSTR(ADMIT_TERM,4,1)= ''6'' 
THEN CONCAT(SUBSTR(ADMIT_TERM,1,3), ''9'') ELSE ADMIT_TERM END = '||CurTerm ||'
)';
END APPEND_HIST_TBLS_PROC;

假设您想在 admin_term 子字符串的末尾附加 9，也使用 CONCAT 而不是双管道。

确实取决于您的数据和您要实现的逻辑。该过程现在应该可以编译，但它是否达到所需的结果 - 取决于您在此处尝试实现的逻辑。