ストレージ・プロシージャによるフロー番号の生成

public class DefaultWmsGenerateSequenceDaoManager extends HibernateDaoSupport implements
		WmsGenerateSequenceDaoManager {

	public Map<String, Object> generateSequence(final String keyName) {
		return (Map<String, Object>) 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<String, Object> result = new HashMap<String, Object>();
			    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;