oracle生成主键-白红宇

oracle生成主键

阅读量：5213 次

发布时间：2019-06-14

本文共 6738 字，大约阅读时间需要 22 分钟。

create table SYS_SEQUENCE(  PK1           VARCHAR2(32) not null,  ATYPE         VARCHAR2(20) not null,  OWNER         VARCHAR2(10) not null,  INITCYCLE     CHAR(1) not null,  CUR_SERNUM    VARCHAR2(50) not null,  ZERO_FLG      VARCHAR2(2) not null,  SEQUENCESTYLE VARCHAR2(50),  MEMO          VARCHAR2(60));-- Add comments to the columns comment on column SYS_SEQUENCE.PK1  is '主键';comment on column SYS_SEQUENCE.ATYPE  is '序列号类型';comment on column SYS_SEQUENCE.OWNER  is '序列号所有者';comment on column SYS_SEQUENCE.INITCYCLE  is '序列号递增';comment on column SYS_SEQUENCE.CUR_SERNUM  is '序列号';comment on column SYS_SEQUENCE.ZERO_FLG  is '序列号长度';comment on column SYS_SEQUENCE.SEQUENCESTYLE  is '序列号样式';comment on column SYS_SEQUENCE.MEMO  is '备注';-- Create/Recreate primary, unique and foreign key constraints alter table SYS_SEQUENCE  add primary key (PK1);-- Create/Recreate indexes create index PK_SYS_SEQUENCE on SYS_SEQUENCE (ATYPE, OWNER);

insert into SYS_SEQUENCE (PK1, ATYPE, OWNER, INITCYCLE, CUR_SERNUM, ZERO_FLG, SEQUENCESTYLE, MEMO)values ('0A772AEDFBED4FEEA46442003CE1C6A6', 'ZDBCONTCN', '750405', '1', '200002', '7', '$YEAR$年$ORGAPP$质字第$SER$号', '质押合同中文编号');insert into SYS_SEQUENCE (PK1, ATYPE, OWNER, INITCYCLE, CUR_SERNUM, ZERO_FLG, SEQUENCESTYLE, MEMO)values ('0A772AEDFBED4FEEA46442003CE1C6A7', 'T_BASIC_ROLE', '750405', '1', '9200022', '8', '$SER$', '质押合同中文编号');

CREATE OR REPLACE FUNCTION GEN_SYS_SEQUENCE(I_ATYPE IN VARCHAR2, /*序列类别*/                                            I_OWNER IN VARCHAR2 /*序列所有者*/)  RETURN VARCHAR2 IS  /**************************************************************************************************/  /* PROCEDURE NAME : GEN_SYS_SEQUENCE               */  /* DEVELOPED BY : WANGXF                  */  /* DESCRIPTION : 主要用来生成自定义的序列号             */  /* DEVELOPED DATE : 2016-10-08                 */  /* CHECKED BY  :                    */  /* LOAD METHOD : F1-DELETE INSERT                */  /**************************************************************************************************/  O_AUTOCODE        VARCHAR2(100); /*输出的序列号*/  V_INITCYCLE       SYS_SEQUENCE.INITCYCLE%TYPE; /*序列号递增*/  V_CUR_SERNUM      SYS_SEQUENCE.CUR_SERNUM%TYPE; /*序列号*/  V_ZERO_FLAG       SYS_SEQUENCE.ZERO_FLG%TYPE; /*序列号长度*/  V_SEQUENCESTYLE   SYS_SEQUENCE.SEQUENCESTYLE%TYPE; /*序列号样式*/  V_SEQ_NUM         VARCHAR2(100); /*本次序列号*/  V_DATE_YEAR       CHAR(4); /*年份，如2016*/  V_DATE_YEAR_MONTH CHAR(6); /*年份月份，如201610*/  V_DATE_DATE       CHAR(8); /*年份月份日，如20161008*/  V_DATE_DATE_ALL   CHAR(14); /*完整年份序列，如20161008155732*/  /*   支持的参数序列：   $YEAR$ --> 年份   $YEAR_MONTH$ --> 年份+月份，不含汉子   $DATE$ --> 年份+月份+日期，不含汉子   $DATE_ALL$ --> 完整日期，不含汉子   $ORGAPP$ --> 所有者   $SER$ --> 当前序列号  */  --解决查询事务无法执行DML的问题  Pragma Autonomous_Transaction;BEGIN    --格式化当前日期  SELECT TO_CHAR(SYSDATE, 'yyyy'),         TO_CHAR(SYSDATE, 'yyyyMM'),         TO_CHAR(SYSDATE, 'yyyyMMdd'),         TO_CHAR(SYSDATE, 'yyyyMMddHH24MISS')    INTO V_DATE_YEAR, V_DATE_YEAR_MONTH, V_DATE_DATE, V_DATE_DATE_ALL    FROM DUAL;      begin    -- 查询复核条件的序列号配置    SELECT T.INITCYCLE, T.CUR_SERNUM, T.ZERO_FLG, T.SEQUENCESTYLE      INTO V_INITCYCLE, V_CUR_SERNUM, V_ZERO_FLAG, V_SEQUENCESTYLE      FROM SYS_SEQUENCE T     WHERE T.ATYPE = I_ATYPE       AND T.OWNER = I_OWNER;  EXCEPTION    WHEN OTHERS THEN      null;      insert into SYS_SEQUENCE        (PK1,         ATYPE,         OWNER,         INITCYCLE,         CUR_SERNUM,         ZERO_FLG,         SEQUENCESTYLE,         MEMO)      values        (I_ATYPE || V_DATE_DATE_ALL,         I_ATYPE,         I_OWNER,         '1',         '0',         '8',         '$SER$',         V_DATE_DATE_ALL||'系统生成');          SELECT T.INITCYCLE, T.CUR_SERNUM, T.ZERO_FLG, T.SEQUENCESTYLE        INTO V_INITCYCLE, V_CUR_SERNUM, V_ZERO_FLAG, V_SEQUENCESTYLE        FROM SYS_SEQUENCE T       WHERE T.ATYPE = I_ATYPE         AND T.OWNER = I_OWNER;  end;  -- 日期处理  O_AUTOCODE := REPLACE(V_SEQUENCESTYLE, '$YEAR$', V_DATE_YEAR);  O_AUTOCODE := REPLACE(O_AUTOCODE, '$YEAR_MONTH$', V_DATE_YEAR_MONTH);  O_AUTOCODE := REPLACE(O_AUTOCODE, '$DATE$', V_DATE_DATE);  O_AUTOCODE := REPLACE(O_AUTOCODE, '$DATE_ALL$', V_DATE_DATE_ALL);  --所有者处理  O_AUTOCODE := REPLACE(O_AUTOCODE, '$ORGAPP$', I_OWNER);  --序号处理  V_SEQ_NUM := TO_CHAR(TO_NUMBER(V_CUR_SERNUM) + TO_NUMBER(V_INITCYCLE));  --反写当前序列号，确保每次都是递增  UPDATE SYS_SEQUENCE T     SET T.CUR_SERNUM = V_SEQ_NUM   WHERE T.ATYPE = I_ATYPE     AND T.OWNER = I_OWNER;  --不满足长度的前面补0  IF LENGTH(V_SEQ_NUM) < TO_NUMBER(V_ZERO_FLAG) THEN    /*       LOOP        V_SEQ_NUM := '0'||V_SEQ_NUM;       EXIT WHEN LENGTH(V_SEQ_NUM) = TO_NUMBER(V_ZERO_FLAG);       END LOOP;    　　　　　　*/    V_SEQ_NUM := LPAD(V_SEQ_NUM, TO_NUMBER(V_ZERO_FLAG), '0');  END IF;  O_AUTOCODE := REPLACE(O_AUTOCODE, '$SER$', V_SEQ_NUM);  COMMIT;  RETURN O_AUTOCODE;EXCEPTION  --如果没有对应的配置项，则返回ERROR值  WHEN NO_DATA_FOUND THEN    ROLLBACK;    DBMS_OUTPUT.put_line('there is no config as you need...');    RETURN 'ERROR';END GEN_SYS_SEQUENCE;

public String getNewPkId(String tableName) throws SQLException {        if (getDbType().equals("oracle")) {            String sql = "{?= call GEN_SYS_SEQUENCE(?,?)}";            return callDatabaseFunction(sql, tableName, "750405");        }else if (getDbType().equals("mysql")) {            return null;        }        return null;    }    /**     * @param sql 类似于：{?= call SF_SYS_GEN_AUTOCODE(?,?)}     * @return     */    public String callDatabaseFunction(String sql, Object... params) throws SQLException {        String retStr = "";        Connection connection = null;        /**         * 调用存储函数 1.{?= call 
     
      [(
      
       ,
       
        , ...)]}         * 调用存储过程 2.{call 
        
         [(
         
          ,
          
           , ...)]} */ CallableStatement callableStatement = null; connection = getConnection(); /* * 1.通过COnnection对象的prepareCall()方法创建一个CallableStatement * 对象的实例,在使用Connection对象的prepareCall() 方法时，需要传入一个String类型的字符串， * 该字符串用于指明如何调用存储过程 */ callableStatement = connection.prepareCall(sql); /* * 2.通过CallableStatement对象的registerOutParameter() 方法注册Out参数 */ //callableStatement.registerOutParameter(1, Types.NUMERIC); callableStatement.registerOutParameter(1, Types.VARCHAR); /* * 3.通过CallableStatement对象的setXxx()方法设定IN或In out * 参数，若想将参数设为null，可以使用setNUll() */ for (int i = 0; i < params.length; i++) { callableStatement.setObject(i + 2, params[i]); }// callableStatement.setString(2, "T_BASIC_ROLE");// callableStatement.setString(3, "012805"); /* 4.通过CallableStatement对象的execute()方法执行存储过程 */ callableStatement.execute(); /* * 5.如果所调用的是带返回参数的存储过程没还需要通过CallableStatement对象的getXxx() */ retStr = callableStatement.getString(1); DbUtils.close(connection); return retStr; }