批量生成Oracle Sequence

项目里面有几百张表，没有创建sequence， 

还好每个表的主键命名都是ID，

 

create or replace PROCEDURE "ADD_SEQ_TRIGGER" AS 
  ctr number := 0;
  ctr2 number := 0;
  addcount number := 0;
  totcount number := 0;
  mcount number := 0;
  var_name varchar(50) := 'ID';
  var_seq varchar(50);
  Var_Trg Varchar(50);
  var_nex varchar(50);
begin
 FOR t IN (select table_name from user_tables where table_name like 'TD_PRO%') 
  LOOP    
      select count(*) into ctr from user_tab_columns where table_name like t.table_name and column_name like var_name;
      IF (ctr > 0) THEN   
        var_seq :=t.table_name||'_SEQ';
        var_trg :=t.table_name||'_TRG';
        var_nex :=t.table_name||'_SEQ'||'.nextval';      
        select count(*) into ctr2 from USER_SEQUENCES where sequence_name = var_seq;
        if(ctr2> 0) THEN
           dbms_output.put_line('sequence exist for table: '||t.table_name||'!'); 
        ELSE
          EXECUTE IMMEDIATE 'CREATE SEQUENCE '||var_seq||' START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';    
          addcount := addcount + 1;
        END IF;  
         dbms_output.put_line('create or replace trigger for table: '||t.table_name||'!'); 
        EXECUTE IMMEDIATE 'create or replace trigger '||var_trg||' BEFORE INSERT ON '||t.table_name||' for each row begin '||
                          'if inserting then if :NEW."ID" is null then '||
                          'select '||var_nex||' into :NEW."ID" from dual; end if;end if;end;';     
      ELSE  
        dbms_output.put_line('ID does NOT exist for table: '||t.table_name||'!');      
      END IF;  
         totcount :=  totcount + 1;
  END LOOP;
   DBMS_OUTPUT.PUT_LINE('total tables count: '||totcount);
   DBMS_OUTPUT.PUT_LINE('total add count: '||addcount);
END ADD_SEQ_TRIGGER;