关于Oracle批量增加自增列问题,求助

[辰爸]

Oracle中多个表，想批量设置表中id为自增列如何实现呢？最好加一个判断，如果表中存在id字段则设置为自增列，否则不处理。。。等高手

[秋雨飘落]

先从系统视图中查询是否存在，返回标志为，

然后在设置ID为主键，

要建序列。。

[BlueskyWide]

介绍点相关知识：

desc col;
desc tab;
select * from col where tname='YOUR_TNAME';   --表名一定要大写。

可以使用编制存贮过程来处理。

[tangren]

--写一个对所有ID字段批量增加序列和触发器的存储过程
CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS
  c           SYS_REFCURSOR;
  v_tablename VARCHAR2(30);
  v_colname   VARCHAR2(30);
BEGIN
  OPEN c FOR 'select table_name,column_name from user_tab_columns';
  LOOP
    FETCH c
      INTO v_tablename, v_colname;
    EXIT WHEN c%NOTFOUND;
    IF v_colname = upper('id') THEN
      --创建序列
      BEGIN
        EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename;
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
      EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename ||
                        ' start with 1 increment by 1';
      --创建触发器
      EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
                        ' before insert on ' || v_tablename ||
                        ' for each row
            begin
              select seq_' || v_tablename ||
                        '.nextval into :new.id from dual;
            end;';
    END IF;
  END LOOP;
END;

[辰爸]

[Quote=引用 3 楼 tangren 的回复:]
SQL code
--写一个对所有ID字段批量增加序列和触发器的存储过程
CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS
c SYS_REFCURSOR;
v_tablename VARCHAR2(30);
v_colname VARCHAR2(30);
BEGIN
OPEN……
[/Quote]
弱弱的问一句，如何执行这个存储过程????????

[tangren]

sql> exec p_batch;

[辰爸]

Warning: PL/SQL procedure successfully completed with compilation errors
报这个错误

[304的的哥]

show error查看错误，
动态sql中，将最后一个分号去掉：

...
      EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
                        ' before insert on ' || v_tablename ||
                        ' for each row
            begin
              select seq_' || v_tablename ||
                        '.nextval into :new.id from dual;
            end';--这里的分号去掉了
...

[辰爸]

大家快来啊。。。。。。。。。。

[tangren]

--进入SQL*Plus或者pl/sql developer工具命令窗口
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 24 22:37:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> --创建存储过程
SQL> CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS
  2    c           SYS_REFCURSOR;
  3    v_tablename VARCHAR2(30);
  4    v_colname   VARCHAR2(30);
  5  BEGIN
  6    OPEN c FOR 'select table_name,column_name from user_tab_columns';
  7    LOOP
  8      FETCH c
  9        INTO v_tablename, v_colname;
 10      EXIT WHEN c%NOTFOUND;
 11      IF v_colname = upper('id') THEN
 12        --创建序列
 13        BEGIN
 14          EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename;
 15        EXCEPTION
 16          WHEN OTHERS THEN
 17            NULL;
 18        END;
 19        EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename ||
 20                          ' start with 1 increment by 1';
 21        --创建触发器
 22        EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
 23                          ' before insert on ' || v_tablename ||
 24                          ' for each row
 25              begin
 26                select seq_' || v_tablename ||
 27                          '.nextval into :new.id from dual;
 28              end;';
 29      END IF;
 30    END LOOP;
 31  END;
 32  /

过程已创建。

SQL> --执行存储过程
SQL> exec p_batch;

PL/SQL 过程已成功完成。

SQL> --当前用户下所有ID列的表都建立完毕了

[junyesuccess]

oracle写批量插入语句的时候，那个序号怎么写进去的啊