Oracle自增字段的创建（转）

ftsplxmbbh

　　在SQLServer数据库中，本身含有自增字段这种类型。
但Oracle中却没有，我们可以用触发器trigger的功能来实现它：
--created by http://hi.baidu.com/fjqzax date 20080327
1、首先建立一个创建自增字段的存储过程
create or replace procedure pro_create_seq_col
(tablename varchar2,columnname varchar2)
as
strsql varchar2(1000);
begin
        strsql := 'create sequence seq_'||tablename||' minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache';
        execute immediate strsql;
        strsql := 'create or replace trigger trg_'||tablename||' before insert on '||tablename||' for each row begin select seq_'||tablename||'.nextval into :new.'||columnname||' from dual; end;';
        execute immediate strsql;
end;
/
　　2、Oracle中执行动态SQL时要显示授权（即使该用户拥有该相关权限）
GRANT CREATE ANY SEQUENCE TO UserName;
GRANT CREATE ANY TRIGGER TO UserName;
　　3、重新Compile存储过程pro_create_seq_col;
　　4、搞定，下面我们就可以用这个存储过程建立自增自段了。
　　5、调用存储过程建立自增字段（提示: 第一个参数是表名，第二个参数为自增字段的名字）
exec pro_create_seq_col('sb_zsxx','zsxh');
exec pro_create_seq_col('sb_sbxx','sbxh');
exec pro_create_seq_col('sb_jkx','pzxh');
exec pro_create_seq_col('sdspfp','sysfpid');
... ...
----------------------------------------------
PLSQL创建顺序实例：
----------------------------------------------
--删除测试表
DROP TABLE T_SB_TTXX ;
--创建测试表
CREATE TABLE T_SB_TTXX AS SELECT * FROM SB_TTXX WHERE 1=2;
--为测试表增加测试字段
ALTER TABLE T_SB_TTXX ADD(SEQ_XH NUMBER(20));
--删除测试序列号
DROP sequence seq_T_SB_TTXX ;
--创建测试序列号
create sequence seq_T_SB_TTXX minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache;
--创建自动增加序列号的触发器
create or replace trigger trg_T_SB_TTXX before insert on T_SB_TTXX for each row
begin
select seq_T_SB_TTXX.nextval into :new.SEQ_XH from dual;
end;
/
--测试数据，往测试表插数据
INSERT INTO T_SB_TTXX
(ttxh,
    pz_dm,
    xtsphm,
    yssphm,
    kdtskxh,
    wspzxh,
    nsrdzdah,
    sssq_q,
    sssq_z,
    kprq,
    zsxm_dm,
    zspm_dm,
    skzl_dm,
    sksx_dm,
    se,
    pzzl_dm,
    pzlrr_dm,
    ttlx_dm,
    jmyy_dm,
    jmyj_dm,
    cktsgc_dm,
    ckts_qylx_dm,
    hgdm,
    lhjhh,
    ckts_zddh_bz,
    tzlx_dm,
    tzrq,
    djzclx_dm,
    hy_dm,
    yskm_dm,
    ysfpbl_dm,
    ysfpbl_zy,
    ysfpbl_ss,
    ysfpbl_ds,
    ysfpbl_xq,
    ysfpbl_xz,
    ysfpbl_xc,
    zzsnsrlx_dm,
    skgk_dm,
    yhzl_dm,
    yh_dm,
    yhzh,
    zfr_dm,
    zfrq,
    xhr_dm,
    xhrq,
    thrq,
    thrq_jz,
    jdxz_dm,
    zgswry_dm,
    skss_swjg_dm,
    ttjg_dm,
    nsr_swjg_dm,
    swjg_dm,
    lrr_dm,
    lrrq,
    xgr_dm,
    xgrq)
SELECT ttxh,
          pz_dm,
          xtsphm,
          yssphm,
          kdtskxh,
          wspzxh,
          nsrdzdah,
          sssq_q,
          sssq_z,
          kprq,
          zsxm_dm,
          zspm_dm,
          skzl_dm,
          sksx_dm,
          se,
          pzzl_dm,
          pzlrr_dm,
          ttlx_dm,
          jmyy_dm,
          jmyj_dm,
          cktsgc_dm,
          ckts_qylx_dm,
          hgdm,
          lhjhh,
          ckts_zddh_bz,
          tzlx_dm,
          tzrq,
          djzclx_dm,
          hy_dm,
          yskm_dm,
          ysfpbl_dm,
          ysfpbl_zy,
          ysfpbl_ss,
          ysfpbl_ds,
          ysfpbl_xq,
          ysfpbl_xz,
          ysfpbl_xc,
          zzsnsrlx_dm,
          skgk_dm,
          yhzl_dm,
          yh_dm,
          yhzh,
          zfr_dm,
          zfrq,
          xhr_dm,
          xhrq,
          thrq,
          thrq_jz,
          jdxz_dm,
          zgswry_dm,
          skss_swjg_dm,
          ttjg_dm,
          nsr_swjg_dm,
          swjg_dm,
          lrr_dm,
          lrrq,
          xgr_dm,
          xgrq
     FROM SB_TTXX;
COMMIT;
--查看测试结果
SELECT t.seq_xh,t.* FROM T_SB_TTXX t;
--删除测试表
DROP TABLE T_SB_TTXX;