oracle获取sequence值方式

1、通过执行sql查询sequence下一个值

SELECT BASE_DICT_DBID_SEQ.NEXTVAL AS DBID FROM DUAL

2、通过调用function存储过程（可以进行复杂操作限制）

create or replace function P_GET_SEQ( i_seq_name in char, o_seq_v out numeric )
return int
AS
        t_sqlstr varchar(100);
begin
        t_sqlstr := \'select \'||i_seq_name||\'.nextval from dual\';
        --dbms_output.put_line( t_sqlstr );

        execute immediate t_sqlstr into o_seq_v;

        --select rrn.nextval into o_seq_v from dual;

        return(0);
exception
    when OTHERS then
         --dbms_output.put_line( sqlerrm );
         rollback;
         return(-1);
end P_GET_SEQ;

mybatis配置

<!-- 调用function获取Sequence值 -->
    <parameterMap id="INOUT_PM" type="map">
        <parameter property="i_seq_name" jdbcType="VARCHAR"
            javaType="String" mode="IN" />
        <parameter property="o_seq_v" jdbcType="INTEGER" javaType="Long"
            mode="OUT" />
    </parameterMap>
    <select id="getVoucherSequence" statementType="CALLABLE"
        parameterType="HashMap" resultType="map">
        {#{d,mode=OUT,jdbcType=INTEGER}=call ams.P_GET_SEQ(
        #{i_seq_name,mode=IN,jdbcType=VARCHAR},
        #{o_seq_v,mode=OUT,jdbcType=BIGINT}
        )}  
    </select>

使用

package com.trunkbow.mchsp.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Date;

import org.apache.log4j.Logger;

import com.sssoft.common.dao.CommDAOFactory;
import com.sssoft.framework.dao.BaseDao;
import com.sssoft.framework.util.DateUtil;
import com.sssoft.framework.util.StringUtil;

public class SeqUtil {
    private static Logger log = Logger.getLogger("SeqUtil");
    public static final String VOUCHER = "voucher_seq";
    public static final String DXZF_VOUCHER = "dxzf_voucher_seq";
    public static final String SYSTRACE = "systrace";
    public static final String HD_SEQ = "hd_seq";
    public static final String ACC_NO_SEQ = "acc_no_seq";
    public static final String PROD_NO_SEQ = "prod_no_seq";
    public static final String OLD_COMPINFO_ID = "old_compinfo_id";
    public static final String SYS_LOOKUP_CATEG_SEQ = "sys_lookup_categ_seq";
    public static final String SYS_LOOKUP_CODE_SEQ = "sys_lookup_code_seq";

    public static String getSystrace() throws Exception {
        Long seq = getSeq("systrace");
        return StringUtil.Long2String(seq, 6);
    }

    public static String getVoucher() throws Exception {
        Long seq = getSeq("voucher_seq");
        return DateUtil.dateToStr(new Date(), "yyyyMMdd") + "-"
                + StringUtil.Long2String(seq, 6);
    }

    public static String getDxzfVoucher() throws Exception {
        Long seq = getSeq("dxzf_voucher_seq");
        return "D-" + DateUtil.dateToStr(new Date(), "yyyyMMdd") + "-"
                + StringUtil.Long2String(seq, 6);
    }

    public static String getOldCompInfoId() throws Exception {
        Long seq = getSeq("old_compinfo_id");
        return DateUtil.dateToStr(new Date(), "yyyyMMdd")
                + StringUtil.Long2String(seq, 6);
    }

    public static String getHdSeq() throws Exception {
        Long seq = getSeq("hd_seq");
        return StringUtil.Long2String(seq, 8);
    }

    public static String getAccNoSeq() throws Exception {
        Long seq = getSeq("acc_no_seq");
        return StringUtil.Long2String(seq, 8);
    }

    public static String getProdNoSeq() throws Exception {
        Long seq = getSeq("prod_no_seq");
        return StringUtil.Long2String(seq, 8);
    }

    public static Long getSeq(String seqName) throws Exception {
        BaseDao baseDao = CommDAOFactory.getBaseDao();
        String sql = "{? = call p_get_seq(?,?)}";
        Connection conn = baseDao.openConnection();
        CallableStatement cstmt = null;
        Long result = null;
        try {
            cstmt = conn.prepareCall(sql);
            cstmt.registerOutParameter(1, 4);
            cstmt.registerOutParameter(3, 2);
            cstmt.setString(2, seqName);
            cstmt.executeUpdate();
            result = Long.valueOf(cstmt.getLong(3));
        } catch (Exception e) {
            log.info("Call p_get_seq has a Exception : " + e);
            throw e;
        } finally {
            cstmt.close();
            conn.close();
        }
        return result;
    }
}

通过以上两种方式可以获取定义的sequence的下一个值，

注意：db2与oracle在这点是一样的可以先获取只再使用，而mysql不可以

db2：

select nextval for MY_SEQ from sysibm.sysdummy1

秒客网

oracle获取sequence值方式

相关文章