代码之家 › 专栏 › 技术社区 › Coding Duchess

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

oracle sql

Coding Duchess · 技术社区 · 7 年前

我在表上有一些未命名的约束 SYS_ . 例如,我想用TABLENAME\u 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_%'

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

有人能给我指出正确的方向吗?

0 回复 | 直到 7 年前

Barbaros Özhan 7 年前

你可以用 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;

Giovanni Esposito 7 年前

您可以创建一个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;