oracle生成主键

最新推荐文章于 2024-03-27 16:55:52 发布

转载最新推荐文章于 2024-03-27 16:55:52 发布 · 132 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/yasepix/p/6512966.html

文章标签：

#数据库

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 <procedure-name>[(<arg1>,<arg2>, ...)]}
         * 调用存储过程 2.{call <procedure-name>[(<arg1>,<arg2>, ...)]}
         */
        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;
    }