我正在嘗試更改所有 PK 約束名稱。下面是我試圖運行的代碼：
BEGIN
FOR i IN (
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'SOME_SCHEME'
AND TABLE_NAME NOT LIKE 'flyway%'
AND TABLE_NAME NOT LIKE 'BIN%'
AND CONSTRAINT_TYPE = 'P')
LOOP
dbms_output.put_line(i.CONSTRAINT_NAME || i.table_name);
EXECUTE IMMEDIATE 'ALTER TABLE i.table_name rename constraint i.constraint_name to i.table_name || ''_PK''';
END LOOP;
END;
我收到以下錯誤：
SQL Error [946] [42000]: ORA-00946: missing TO keyword
ORA-06512: at line 17
ORA-06512: at line 17
為什么它說 TO 沒有丟失？如何解決這個問題？
uj5u.com熱心網友回復：
如果你dbms_output在執行之前使用它來顯示陳述句，你會看到它的格式不正確；無論表和約束是什么，您當前嘗試執行的陳述句實際上是：
ALTER TABLE i.table_name rename constraint i.constraint_name to i.table_name || '_PK'
這些i.*部分不是變數或值，它們實際上是那些字串。當然，手動運行該陳述句會得到相同的錯誤。
您需要將回圈變數值連接到陳述句中；您還需要指定所有者，因為您正在查看all_tables，并且您不妨參考名稱：
DECLARE
l_stmt varchar2(4000);
BEGIN
FOR ...
LOOP
dbms_output.put_line(i.CONSTRAINT_NAME || i.table_name);
l_stmt := 'ALTER TABLE SOME_SCHEME."' || i.table_name || '"'
|| ' rename constraint "' || i.constraint_name || '"'
|| ' to "' || i.table_name || '_PK"';
dbms_output.put_line(l_stmt);
EXECUTE IMMEDIATE l_stmt;
END LOOP;
END;
/
這會產生更像：
ALTER TABLE SOME_SCHEME."SDO_DATUMS" rename constraint "DATUM_PRIM" to "SDO_DATUMS_PK"
db<>小提琴
我已經硬編碼了SOME_SCHEME您在回圈查詢中使用的名稱；您還可以通過添加到選擇串列中從查詢本身中獲取它OWNER，然后也將其連接起來（同樣，參考過于謹慎）。或者將其設定在區域變數中并在兩個地方使用它，正如@MTO 在評論中建議的那樣。
如果您只以SOME_SCHEME用戶身份運行它，那么您可以查詢user_constraints：
DECLARE
l_stmt varchar2(4000);
BEGIN
FOR i IN (
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME NOT LIKE 'flyway%' -- are these really quoted lower-case?
AND TABLE_NAME NOT LIKE 'BIN%'
AND CONSTRAINT_TYPE = 'P'
AND CONSTRAINT_NAME != TABLE_NAME || '_PK'
)
LOOP
l_stmt := 'ALTER TABLE "' || i.table_name || '"'
|| ' RENAME constraint "' || i.constraint_name || '"'
|| ' TO "' || i.table_name || '_PK"';
dbms_output.put_line(l_stmt);
EXECUTE IMMEDIATE l_stmt;
END LOOP;
END;
/
db<>fiddle with example tables, with quoted and unquoted identifiers.
You might also want your loop query to include:
AND CONSTRAINT_NAME != TABLE_NAME || '_PK'
... so you don't touch constraints that already have the name pattern you want.
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/425297.html