對系統(tǒng)默認的約束名和索引名進行重命名

對系統(tǒng)默認的約束名和索引名進行重命名的存儲過程示例：
    create or replace procedure proc_rename_constraint
    as
    --查找用戶所有表的游標(biāo)
    cursor cur_table is select table_name from user_tables;
    --查找某張表所有約束的游標(biāo)
    cursor cur_cons (c_table varchar2) is select
    c.constraint_name,c.constraint_type,
    c.search_condition from user_constraints c
    where c.table_name=c_table
    and substr(c.constraint_name,2,2) <> 'K_';
    --查找某個約束所有字段的游標(biāo)
    cursor cur_columns(c_cons varchar2) is select
    column_name from user_cons_columns
    where constraint_name=c_cons;
    --存儲修改后的約束名
    v_new_cons_name varchar2(100);
    --存儲修改約束名的SQL語句
    v_sql varchar2(150);
    --存儲重名的個數(shù)
    cnt number :=1;
    --存儲新索引名的個數(shù)
    n_idx number;
    --存儲新約束名的個數(shù)
    n_con number;
    begin
    --循環(huán)取表名
    for cur_ltable in cur_table loop
    --循環(huán)取約束名
    for cur_lcons in cur_cons(cur_ltable.table_name) loop
    v_new_cons_name :=null;
    --循環(huán)取字段名
    for cur_lcolumns in cur_columns(cur_lcons.constraint_name) loop
    v_new_cons_name := v_new_cons_name || cur_lcolumns.column_name;
    end loop;
    v_new_cons_name := replace(v_new_cons_name,'_','');
    v_new_cons_name := cur_ltable.table_name ||'_' || v_new_cons_name;
    if cur_lcons.constraint_type='P' then
    v_new_cons_name := 'PK_' || v_new_cons_name;
    elsif cur_lcons.constraint_type='R' then
    v_new_cons_name := 'FK_' || v_new_cons_name;
    elsif cur_lcons.constraint_type='U' then
    v_new_cons_name := 'UK_' || v_new_cons_name;
    elsif cur_lcons.constraint_type='C'
    and instr(cur_lcons.search_condition,'IS NOT NULL') > 0 then
    v_new_cons_name := 'CK_' || v_new_cons_name || 'NOTNULL' ;
    elsif cur_lcons.constraint_type='C'
    and instr(cur_lcons.search_condition,'IS NOT NULL') = 0
    and cur_lcons.search_condition is not null then
    v_new_cons_name := 'CK_' || v_new_cons_name;
    end if;
    --約束名如果超過30個字符的處理
    if length(v_new_cons_name) > 29 then
    v_new_cons_name := substr(v_new_cons_name,1,15)
    || substr(v_new_cons_name,-14);
    end if;
    --查找系統(tǒng)里是否有新的約束名
    select count(*) into n_con from user_constraints
    where constraint_name=v_new_cons_name;
    select count(*) into n_idx from user_indexes where
    index_name=v_new_cons_name;
    if n_con > 0 or n_idx > 0 then
    v_new_cons_name := v_new_cons_name || to_char(cnt);
    cnt := cnt +1;
    end if;
    --對由主鍵和鍵創(chuàng)建的索引進行改名,改后的名字為新的約束名
    if cur_lcons.constraint_type='P' or cur_lcons.constraint_type='U' then
    select count(*) into n_con from user_indexes where
    index_name=cur_lcons.constraint_name;
    if n_con = 1 then
    v_sql := 'alter index ' || cur_lcons.constraint_name
    || ' rename to ' || v_new_cons_name;
    execute immediate v_sql;
    end if;
    end if;
    --對約束改名
    v_sql := 'alter table ' || cur_ltable.table_name || ' rename constraint ';
    v_sql := v_sql || cur_lcons.constraint_name || ' to ' || v_new_cons_name;
    execute immediate v_sql;
    end loop;
    end loop;
    end;