oracle存储过程生成流水号,用存储过程生成流水号

public class DefaultWmsGenerateSequenceDaoManager extends HibernateDaoSupport implements

WmsGenerateSequenceDaoManager {

public Map generateSequence(final String keyName) {

return (Map) this.getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session) throws HibernateException, SQLException {

// 需要执行的存储过程

String procedure = "{call wms.GenSeq(?,?)}";

CallableStatement callableStatement = session.connection().prepareCall(procedure);

// 设置输入参数

callableStatement.setString(1, keyName);

// 注册输出参数

callableStatement.registerOutParameter(2, java.sql.Types.DOUBLE);

callableStatement.execute();

// 取回输出参数

Double retValue = callableStatement.getDouble(2);

Map result = new HashMap();

result.put("retValue", retValue);

callableStatement.close();

return result;

}

});

}

}

GenSeq.sql

---生成流水号

create or replace procedure GenSeq(keyName in varchar, retValue out number) is

next_id sequence.next_id%TYPE;

id sequence.id%TYPE;

---user seqSequence.nextval for the key id of sequence

BEGIN

--IF return -1 then identified incorrect call

retValue:=-1;

SELECT next_id INTO next_id FROM sequence WHERE sequence_key=ltrim(rtrim(keyName));--去掉头为空格

UPDATE sequence SET next_id=next_id+1 WHERE sequence_key=ltrim(rtrim(keyName));

COMMIT;

retValue:=next_id+1;

--DBMS_OUTPUT.put_line(retValue);

EXCEPTION

WHEN NO_DATA_FOUND THEN

---New key value found

BEGIN

INSERT INTO sequence (id,sequence_key,sequence_type,next_id)

VALUES (seqSequence.Nextval,keyName,' ',1);

retValue:=1;

COMMIT;

-- DBMS_OUTPUT.put_line(retValue);

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

WHEN OTHERS THEN

ROLLBACK;

end GenSeq;