欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle为当前用户下所有表单且主键名称为ID的表建立序列及触发器

程序员文章站 2022-04-05 22:20:48
...

该存储过程存在几个小问题,比如序列的命名方式使用的是seq_tablename_id,包括触发器使用的命名则是以tablename_trirger,可能引发的问题是:

1.表明过长超过oracle限定最长对象的30个字符。

2.表名可能含有无意义的前缀或者后缀,占用字符。

由于这两个问题因人而异只能根据具体问题具体修改了,如有需要,可以下载附件,下面附上代码仅供参考:

 

declare
--获取当前用户所拥有的全部表的数量
  tables_names_length varchar2(200);
--游标获取到的表名
  table_name varchar2(200);
--主键名称
  primary_key varchar2(200);
--主键数量(为了衡量当前表是否存在主键)
  primary_key_nums varchar2(200);
--序列数量(为了衡量穿件的序列是否存在)  
  sequences_numbers varchar2(200);
--序列名称  
  sequences_name varchar2(200);
--是否需要全盘创建触发器
  is_create_trigger varchar2(200) := true;
  
  sql_select_tables_names_length varchar2(2000);

  sql_select_tables_primary_key varchar2(2000);
  
  sql_select_tables_key_nums varchar2(2000);
  
  sql_insert_sequences varchar2(2000);
  
  sql_select_sequences_numbers varchar2(2000);
  
  sql_create_sequences varchar2(2000);

  sql_create_trigger varchar2(2000);

  CURSOR CURSOR_TABLE_NAME IS 
  select TABLE_NAME from USER_TABLES;
  
begin
--获取当前用户拥有的表数量
  sql_select_tables_names_length := 'select count(*) from USER_TABLES';
  EXECUTE IMMEDIATE sql_select_tables_names_length
  INTO tables_names_length;    
  
  OPEN CURSOR_TABLE_NAME;
  for i in 1 .. tables_names_length LOOP
    FETCH CURSOR_TABLE_NAME
      INTO table_name;
    --根据游标获取的表名获取当前表的主键数量
    sql_select_tables_key_nums:= 'select COUNT(*) from user_cons_columns   
                                  where constraint_name = (select constraint_name from user_constraints   
                                  where table_name = '''||table_name||''' and constraint_type =''P'')';
    execute immediate sql_select_tables_key_nums 
    INTO primary_key_nums;
--判断主键是否存在  
    if (primary_key_nums<>0) then
    sql_select_tables_primary_key := 'select COLUMN_NAME from user_cons_columns   
                                  where constraint_name = (select constraint_name from user_constraints   
                                  where table_name = '''||table_name||''' and constraint_type =''P'')'; 
    execute immediate sql_select_tables_primary_key 
    INTO primary_key;
    --判断主键名称是否为ID
         if (primary_key='ID') then
           --根据命名规则创建符合规则的序列名称
           sequences_name:='SEQ_'||table_name||'_'||primary_key;
           sql_select_sequences_numbers := 'SELECT count(*) FROM All_Sequences where SEQUENCE_NAME='''||sequences_name||'''';
           execute immediate sql_select_sequences_numbers 
           INTO sequences_numbers;
           --判断待创建序列是否存在
           if (sequences_numbers=0) then
              sql_create_sequences:='create sequence '||sequences_name||' 
                                            increment by 1 
                                            start with 1 
                                            minvalue 1 
                                            nomaxvalue 
                                            nocycle';
              execute immediate sql_create_sequences;
              if (is_create_trigger) then
                sql_create_trigger:='create or replace trigger '||table_name||'_trigger
                                        before insert on '||table_name||'
                                        for each row
                                        begin
                                            '||sequences_name||'.NEXTVAL into:new.id from sys.dual;
                                        end;'
                execute immediate sql_create_trigger; 
              end if;
           end if;
         end if;
    end if;
  END LOOP; 
end;