以下存盤程序符合錯誤。似乎錯誤出現在 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;
uj5u.com熱心網友回復：
動態 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
所以，如果你真的可以執行它（我不能，我沒有你的表），然后取消注釋execute immediate并使用該程序。否則，修復該陳述句。
uj5u.com熱心網友回復：
似乎是參考的問題。你有沒有試過這個：
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;
CONCAT假設您想在 admin_term 的子字串末尾附加 9，也可以用來代替雙管道。
真的取決于你的資料和你試圖實作的邏輯。此程序現在應該可以編譯，但它是否達到預期的結果 - 取決于您在此處嘗試實作的邏輯。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432928.html