国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

對系統(tǒng)默認(rèn)的約束名和索引名進(jìn)行重命名來源：賽迪網(wǎng) 編輯：若水 時間：2008-04-28 34對系統(tǒng)默認(rèn)的約束名和索引名進(jìn)行重命名的存儲過程示例：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)建的索引進(jìn)行改名,改后的名字為新的約束名 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;