我想列印一個動態構建的查詢。然而我被困在變數宣告中；錯誤在第 2 行。我需要這些 VARCHAR2 變數的最大大小。
我有一個良好的整體結構嗎？
我在動態查詢中使用 WITH 的結果。
DECLARE l_sql_query VARCHAR2(2000);
l_sql_queryFinal VARCHAR2(2000);
with cntp as (select distinct
cnt.code code_container,
*STUFF*
FROM container cnt
WHERE
cnt.status !='DESTROYED'
order by cnt.code)
BEGIN
FOR l_counter IN 2022..2032
LOOP
l_sql_query := l_sql_query || 'SELECT cntp.code_container *STUFF*
FROM cntp
GROUP BY cntp.code_container ,cntp.label_container, cntp.Plan_Classement, Years
HAVING
cntp.Years=' || l_counter ||'
AND
/*stuff*/ TO_DATE(''31/12/' || l_counter ||''',''DD/MM/YYYY'')
AND SUM(cntp.IsA)=0
AND SUM(cntp.IsB)=0
UNION
';
END LOOP;
END;
l_sql_queryFinal := SUBSTR(l_sql_query, 0, LENGTH (l_sql_query) – 5);
l_sql_queryFinal := l_sql_queryFinal||';'
dbms_output.put_line(l_sql_queryFinal);
uj5u.com熱心網友回復：
您發布的代碼有很多問題，其中包括：
- 您將
with(CTE) 作為宣告部分中的獨立片段，這是無效的。如果您希望它成為動態字串的一部分，則將其放入字串中； - 你
END;在錯誤的地方； - 你有
–而不是-; - 您洗掉了最后 5 個字符，但以新行結尾，因此您需要洗掉 6 以包含最后一個 UNION 的 U；
- 附加分號的行本身就缺少一個（盡管對于動態 SQL，您通常不需要分號，因此可以洗掉整行）；
- 2000 個字符對于您的示例來說太小了，但實際最大值為 32767 就可以了。
DECLARE
l_sql_query VARCHAR2(32767);
l_sql_queryFinal VARCHAR2(32767);
BEGIN
-- initial SQL which just declares the CTE
l_sql_query := q'^
with cntp as (select distinct
cnt.code code_container,
*STUFF*
FROM container cnt
WHERE
cnt.status !='DESTROYED'
order by cnt.code)
^';
-- loop around each year...
FOR l_counter IN 2022..2032
LOOP
l_sql_query := l_sql_query || 'SELECT cntp.code_container *STUFF*
FROM cntp
GROUP BY cntp.code_container ,cntp.label_container, cntp.Plan_Classement, Years
HAVING
cntp.Years=' || l_counter ||'
AND
MAX(TO_DATE(cntp.DISPOSITION_DATE,''DD/MM/YYYY'')) BETWEEN TO_DATE(''01/01/'|| l_counter ||''',''DD/MM/YYYY'') AND TO_DATE(''31/12/' || l_counter ||''',''DD/MM/YYYY'')
AND SUM(cntp.IsA)=0
AND SUM(cntp.IsB)=0
UNION
';
END LOOP;
l_sql_queryFinal := SUBSTR(l_sql_query, 0, LENGTH (l_sql_query) - 6);
l_sql_queryFinal := l_sql_queryFinal||';';
dbms_output.put_line(l_sql_queryFinal);
END;
/
db<>小提琴
第q[^...^]一個分配中的 是替代參考機制，這意味著您不必轉義（通過加倍）該字串中的引號， around 'DESTYORED'。請注意，^分隔符不會出現在最終生成的查詢中。
生成的查詢是否真的做你想要的是另一回事......這cntp.Years=部分可能應該在一個where子句中，而不是having; 并且您可能可以將其簡化為單個查詢而不是許多聯合，因為您已經在聚合。不過，所有這些都超出了您的問題范圍。
uj5u.com熱心網友回復：
有沒有辦法像“VARCHAR2（MAX_STRING_SIZE）一樣把最大尺寸“自動地”放好？
不，不。
PL/SQL 中的最大大小varchar2為 32767。如果您想在未來某個時間點對沖這種變化，您可以在共享包中宣告用戶定義的子型別...
create or replace package my_subtypes as
subtype max_string_size is varchar2(32767);
end my_subtypes;
/
...并在您的程式中參考它...
DECLARE
l_sql_query my_subtypes.max_string_size;
l_sql_queryFinal my_subtypes.max_string_size;
...
因此，如果 Oracle 隨后在 PL/SQL 中提高了 VARCHAR2 的最大允許大小，您只需更改在my_subtypes.max_string_size使用該子型別的任何地方都會提高邊界的定義。
或者，只需使用 CLOB。當 CLOB 的大小 <= 32k 時，Oracle 非常聰明地將 CLOB 視為 VARCHAR2。
要解決您的其他問題，您需要將 WITH 子句視為字串并將其分配給您的查詢變數。
l_sql_query my_subtypes.max_string_size := q'[
with cntp as (select distinct
cnt.code code_container,
*STUFF*
FROM container cnt
WHERE cnt.status !='DESTROYED'
order by cnt.code) ]';
請注意使用特殊的引號語法q'[ ... ]'以避免在查詢片段中轉義引號。
動態字串查詢不訪問臨時表？
動態 SQL 是一個包含 DML 或 DDL 陳述句的字串，我們使用 EXECUTE IMMEDIATE 或 DBMS_SQL 命令執行該陳述句。否則它與靜態 SQL 完全相同，它的行為沒有任何不同。事實上，撰寫動態 SQL 的最佳方法是首先在作業表中撰寫靜態陳述句，使其正確，然后確定哪些位需要是動態的（變數、占位符）以及哪些位保持靜態（樣板檔案）。在您的情況下， WITH 子句是陳述句的靜態部分。
uj5u.com熱心網友回復：
正如已經指出的那樣，您似乎誤解了with子句是什么：它是 SQL 陳述句的子句，而不是程序宣告。我的定義，后面必須跟select.
但是，作為一般規則，我建議盡可能避免使用動態 SQL。在這種情況下，如果您可以模擬具有所需年份范圍的表，則可以加入，而不必多次運行相同的查詢。
這樣做的簡單技巧是使用 Oracle 的connect by語法來使用遞回查詢來生成預期的行數。
完成此操作后，將此表添加為連接非常簡單：
WITH cntp AS
(
SELECT DISTINCT code code_container,
[additional columns]
FROM container
WHERE status !='DESTROYED') cntc,
(
SELECT to_date('01/01/'
|| (LEVEL 2019), 'dd/mm/yyyy') AS start_date,
to_date('31/12/'
|| (LEVEL 2019), 'dd/mm/yyyy') AS end_date,
(LEVEL 2019) AS year
FROM dual
CONNECT BY LEVEL <= 11) year_table
SELECT cntp.code_container,
[additional columns]
FROM cntp
join year_table
ON cntp.years = year_table.year
GROUP BY [additional columns],
years,
year_table.start_date,
year_table.end_date
HAVING max(to_date(cntp.disposition_date,''dd/mm/yyyy'')) BETWEEN year_table.start_date AND year_table.end_date
AND SUM(cntp.isa)=0
AND SUM(cntp.isb)=0
（此查詢完全未經測驗，可能無法真正滿足您的需求；我根據可用資訊提供最佳近似值。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/461880.html