Oracle使用遊標為所有使用者表添加主鍵語句，oracle遊標

Oracle使用遊標為所有使用者表添加主鍵語句，oracle遊標

應用場合：資料表新增自增一主鍵能加快資料表的訪問速度，而且是整形的索引速度最快。本程式適合在匯入Oracle資料庫時刪除不存在主鍵的情況下運行。

代碼說明：所有的表主鍵欄位名都設定為ID,如果已存在ID欄位，則判斷是否是整形，如果不是就重新命名欄位為[表名ID]，然後新增ID，如果不存在則直接添加自增一ID的主鍵

操作說明：開啟PQSQL串連資料庫後直接執行下面的詳細指令碼代碼運行即可，指令碼有風險(會刪除原來的索引跟主鍵約束)，請不要輕易在正式啟動並執行資料庫上直接執行

--Oracle使用遊標為所有使用者表添加主鍵語句
--參考語句如下：
--查詢所有主鍵約束select * from user_constraints
--查詢所有序列select * from user_sequences;
--查詢所有觸發器select * from user_triggers;
--查詢觸發器的使用者select distinct(table_owner) from user_triggers;

declare

addstring NVARCHAR2(2000):=' '; --定義添加欄位變數 
renamestring NVARCHAR2(2000):=' '; --定義重新命名欄位變數 
tablestring NVARCHAR2(2000):=' '; --定義序列變數
keyidname NVARCHAR2(255):='ID'; --定義主鍵欄位名變數
tableidname NVARCHAR2(255):=' '; --定義新的欄位名變數
trigerstring NVARCHAR2(2000):=' '; --定義建立觸發器字串變數    
trgname NVARCHAR2(255):=' '; --定義觸發器名稱變數 
seqstring NVARCHAR2(2000):=' '; --定義建立序列字串變數   
seqname NVARCHAR2(255):=' '; --定義序列名稱變數
pkname NVARCHAR2(255):=' '; --定義主鍵索引名稱變數

constring NVARCHAR2(2000):=' '; --定義索引變數  
notnullstring NVARCHAR2(2000):=' '; --定義主鍵不為空白變數 

cursor mycursor is select * from user_tables where TABLESPACE_NAME='SZGABL' ORDER BY TABLE_NAME; --定義遊標擷取所所有使用者資料表名稱
myrecord mycursor%rowtype;  --定義遊標記錄類型
CounterName int :=0;   --定義是否存在對應的列名變數
CounterData int :=0;   --定義是否存在對應的資料類型

begin  

dbms_output.put_line('declare counter int :=0;begin ');

open mycursor;  --開啟遊標  
if mycursor%isopen  then  --判斷開啟成功  
loop --迴圈擷取記錄集    
fetch mycursor into myrecord; --擷取遊標中的記錄        

if mycursor%found then  --遊標的found屬性判斷是否有記錄 
begin
  --擷取有效資料表名
  select replace(myrecord.TABLE_NAME,'TB_','') into tablestring from dual;
  select 'SEQ_'||tablestring into seqname from dual;
  select 'TRG_'||tablestring into trgname from dual;
  select 'PK_'||tablestring into pkname from dual; 
  select tablestring||UPPER(keyidname) into tableidname from dual;
 
  --判斷當前資料表是否包含欄位名為ID的列
  SELECT COUNT(*) INTO CounterName FROM dual WHERE EXISTS(SELECT * FROM user_tab_cols WHERE LOWER(COLUMN_NAME)=LOWER(keyidname) and TABLE_NAME=myrecord.TABLE_NAME);
  if CounterName=0 then
    begin
     dbms_output.put_line('--當前資料表'||myrecord.TABLE_NAME||'不存在欄位名為ID的列');
         --添加主鍵欄位
         addstring:='execute immediate ''alter table '||myrecord.TABLE_NAME||' add '||keyidname||' NUMBER'';';
         dbms_output.put_line(addstring);
         --execute immediate addstring;        

         --建立一個序列        
         seqstring:='select count(*) into counter from dual  where exists(select * from user_sequences where sequence_name='''||seqname||''');if counter>0 then execute immediate ''drop sequence '||seqname||'''; end if; execute immediate '' create sequence SEQ_'||tablestring||'  INCREMENT BY 1 START WITH 1  NOMAXVALUE  NOCYCLE  NOCACHE'';';      
         dbms_output.put_line(seqstring);
         --execute immediate seqstring;
         --建立一個觸發器
         trigerstring:='select count(*) into counter from dual  where exists(select * from user_triggers where trigger_name='''||trgname||''');if counter>0 then execute immediate ''drop trigger '||trgname||'''; end if; execute immediate '' create trigger TRG_'||tablestring||' BEFORE INSERT ON '||myrecord.TABLE_NAME||' FOR EACH ROW WHEN (new.'||keyidname||' is null) begin  select '||seqname||'.nextval into: new.'||keyidname||' from dual; end'';';          
         dbms_output.put_line(trigerstring);
         --execute immediate trigerstring;
         --添加主鍵約束
         constring:='select count(*) into counter from dual  where exists(select * from user_constraints where constraint_name='''||pkname||''');if counter>0 then execute immediate ''drop constraint '||pkname||'''; end if; execute immediate ''alter table '||myrecord.TABLE_NAME||' add constraint '||pkname||' primary key('||keyidname||')'';';
         dbms_output.put_line(constring);
         --execute immediate constring;
         --更新主鍵不為空白
         notnullstring:='select count(*) into counter from dual  where exists(select * from user_tab_cols where table_name='''||myrecord.TABLE_NAME||'''  and column_name='''||keyidname||''' AND NULLABLE=''Y'' );if counter>0 then execute immediate ''alter table '||myrecord.TABLE_NAME||' modify '||keyidname||' not null''; end if;';
         dbms_output.put_line(notnullstring);
         --execute immediate notnullstring;
     end;
  else    
    begin
      --判斷當前資料表是否包含欄位名為ID且資料類型為NUMBER
      SELECT COUNT(*) INTO CounterData FROM dual WHERE EXISTS(SELECT * FROM user_tab_cols WHERE LOWER(COLUMN_NAME)=LOWER(keyidname) AND DATA_TYPE='NUMBER' and TABLE_NAME=myrecord.TABLE_NAME);
      if CounterData=0 then   
         begin
         dbms_output.put_line('--當前資料表'||myrecord.TABLE_NAME||'存在欄位名為ID，但資料類型不為NUMBER的列');
         --先重新命名欄位，然後添加主鍵欄位        
         renamestring:='execute immediate ''alter table '||myrecord.TABLE_NAME||' rename column '||keyidname||' to '||tableidname||''';';
         dbms_output.put_line(renamestring);
         --execute immediate renamestring;
         --添加主鍵欄位         
         addstring:='execute immediate ''alter table '||myrecord.TABLE_NAME||' add '||keyidname||' NUMBER'';';
         dbms_output.put_line(addstring);

         --execute immediate addstring;      
         --建立一個序列   
         seqstring:='select count(*) into counter from dual  where exists(select * from user_sequences where sequence_name='''||seqname||''');if counter>0 then execute immediate ''drop sequence '||seqname||'''; end if; execute immediate '' create sequence SEQ_'||tablestring||'  INCREMENT BY 1 START WITH 1  NOMAXVALUE  NOCYCLE  NOCACHE'';';
         dbms_output.put_line(seqstring);
         --execute immediate seqstring;
         --建立一個觸發器
         trigerstring:='select count(*) into counter from dual  where exists(select * from user_triggers where trigger_name='''||trgname||''');if counter>0 then execute immediate ''drop trigger '||trgname||'''; end if; execute immediate '' create trigger TRG_'||tablestring||' BEFORE INSERT ON '||myrecord.TABLE_NAME||' FOR EACH ROW WHEN (new.'||keyidname||' is null) begin  select '||seqname||'.nextval into: new.'||keyidname||' from dual; end'';';          
         dbms_output.put_line(trigerstring);
         --execute immediate trigerstring;
         --添加主鍵約束
         constring:='select count(*) into counter from dual  where exists(select * from user_constraints where constraint_name='''||pkname||''');if counter>0 then execute immediate ''drop constraint '||pkname||'''; end if; execute immediate ''alter table '||myrecord.TABLE_NAME||' add constraint '||pkname||' primary key('||keyidname||')'';';
         dbms_output.put_line(constring);
         --execute immediate constring;  
         --更新主鍵不為空白        
         notnullstring:='select count(*) into counter from dual  where exists(select * from user_tab_cols where table_name='''||myrecord.TABLE_NAME||'''  and column_name='''||keyidname||''' AND NULLABLE=''Y'' );if counter>0 then execute immediate ''alter table '||myrecord.TABLE_NAME||'  modify '||keyidname||' not null''; end if;';
         dbms_output.put_line(notnullstring);
         --execute immediate notnullstring;           
         end;
      else
        begin
         dbms_output.put_line('--當前資料表'||myrecord.TABLE_NAME||'存在欄位名為ID，且資料類型為NUMBER的列');    
         --建立一個序列   
         seqstring:='select count(*) into counter from dual  where exists(select * from user_sequences where sequence_name='''||seqname||''');if counter>0 then execute immediate ''drop sequence '||seqname||'''; end if; execute immediate '' create sequence SEQ_'||tablestring||'  INCREMENT BY 1 START WITH 1  NOMAXVALUE  NOCYCLE  NOCACHE'';';  
         dbms_output.put_line(seqstring);
         --execute immediate seqstring;
         --建立一個觸發器
         trigerstring:='select count(*) into counter from dual  where exists(select * from user_triggers where trigger_name='''||trgname||''');if counter>0 then execute immediate ''drop trigger '||trgname||'''; end if; execute immediate '' create trigger TRG_'||tablestring||' BEFORE INSERT ON '||myrecord.TABLE_NAME||' FOR EACH ROW WHEN (new.'||keyidname||' is null) begin  select '||seqname||'.nextval into: new.'||keyidname||' from dual; end'';';          
         dbms_output.put_line(trigerstring);
         --execute immediate trigerstring;
         --添加主鍵約束        
         constring:='select count(*) into counter from dual  where exists(select * from user_constraints where constraint_name='''||pkname||''');if counter>0 then execute immediate ''drop constraint '||pkname||'''; end if; execute immediate ''alter table '||myrecord.TABLE_NAME||' add constraint '||pkname||' primary key('||keyidname||')'';';
         dbms_output.put_line(constring);
         --execute immediate constring;
         --更新主鍵不為空白
         notnullstring:='select count(*) into counter from dual  where exists(select * from user_tab_cols where table_name='''||myrecord.TABLE_NAME||'''  and column_name='''||keyidname||''' AND NULLABLE=''Y'' );if counter>0 then execute immediate ''alter table '||myrecord.TABLE_NAME||'  modify '||keyidname||' not null''; end if;';
         dbms_output.put_line(notnullstring);
         --execute immediate notnullstring;    
         end;
      end if;
    end;
  end if;
      dbms_output.put_line('');
end;

else
exit;
end if;
  
end loop;  
else    
dbms_output.put_line('--遊標沒有開啟');  
end if; 

close mycursor;

      dbms_output.put_line('end;');
end;