通过脚本动态重命名索引和约束

Question

我在表上有一些以SYS_开头的未命名约束。例如，我想用TABLENAME_CONSTRAINTTYPE来命名它们，而不是SYS_C12345我想将其命名为MyTable1_PK

现在，问题是我需要在多个环境中执行此操作，并且约束的名称因环境而异。所以我不能硬编码任何东西，因为同一个脚本将在所有环境中运行。

我想出了一种根据列名和约束类型动态获取约束名称的方法：

SELECT acc.CONSTRAINT_NAME
FROM   ALL_CONS_COLUMNS acc
INNER JOIN ALL_CONSTRAINTS ac
ON ( acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME )
WHERE  ac.OWNER        = 'SCHEMA1'
AND    ac.TABLE_NAME   = 'MY_TABLE1'
AND    acc.COLUMN_NAME = 'MY_ID'
AND    ac.CONSTRAINT_TYPE IN ('P') 
AND acc.CONSTRAINT_NAME LIKE 'SYS_%'

但是我在将其与重命名SQL集成时遇到问题：

ALTER TABLE SCHEMA1.MY_TABLE1 RENAME CONSTRAINT '' TO MY_TABLE1_PK;

谁能指出我正确的方向？

Answer 1

您可以在循环内的 PL/SQL 代码块中使用execute immediate，如下所示

begin
for c in (
SELECT acc.CONSTRAINT_NAME, ac.TABLE_NAME, ac.OWNER
FROM   ALL_CONS_COLUMNS acc
INNER JOIN ALL_CONSTRAINTS ac
ON ( acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME )
WHERE  ac.OWNER        = 'SCHEMA1'
AND  ac.TABLE_NAME   = 'MY_TABLE1'
AND  acc.COLUMN_NAME = 'MY_ID'
AND  ac.CONSTRAINT_TYPE IN ('P') 
AND acc.CONSTRAINT_NAME LIKE 'SYS_%'
)
loop
execute immediate 'ALTER TABLE '||c.OWNER||'.'||c.TABLE_NAME||' RENAME CONSTRAINT 
'||c.CONSTRAINT_NAME||' TO '||c.TABLE_NAME||'_pk';
end loop;
end;

Answer 2

您可以创建 SP 并使用正确的参数调用它。

create or replace PROCEDURE sp_update_constraints(p_schema_name IN VARCHAR2,
p_table_name  IN VARCHAR2,
p_column_name IN VARCHAR2) IS
CURSOR constraint_name_cur(p_schema_name VARCHAR2,
p_table_name  VARCHAR2,
p_column_name VARCHAR2) IS
SELECT acc.CONSTRAINT_NAME
FROM   ALL_CONS_COLUMNS acc
INNER JOIN ALL_CONSTRAINTS ac
ON ( acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME )
WHERE  ac.OWNER        = p_schema_name
AND    ac.TABLE_NAME   = p_table_name
AND    acc.COLUMN_NAME = p_column_name
AND    ac.CONSTRAINT_TYPE IN ('P') 
AND acc.CONSTRAINT_NAME LIKE 'SYS_%';
BEGIN
FOR cn IN constraint_name_cur(p_schema_name, p_table_name, p_column_name) LOOP
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || p_schema_name || '.' || p_table_name || ' RENAME CONSTRAINT ' || cn.constraint_name || ' TO ' || p_table_name ||'_PK');
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_schema_name || '.' || p_table_name || ' RENAME CONSTRAINT ' || cn.constraint_name || ' TO ' || p_table_name ||'_PK';
END LOOP;
END;