[MSSQL] 오라클의 sequence 같은 기능 구현하기

mssql에서 sequence 구현하기

1. 테이블 생성

CREATE TABLE SEQUENCES (

ID VARCHAR(100) PRIMARY KEY

, SEQ INT

)

GO

2. 프로시져 생성

CREATE PROCEDURE nextval

@id VARCHAR(100)

, @seq INT OUTPUT

as

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM SEQUENCES 

WHERE ID = @id

PRINT @cnt

IF @cnt = 0

BEGIN

INSERT INTO SEQUENCES (ID, SEQ) VALUES (@id, 0)

END

UPDATE SEQUENCES SET @seq = seq = seq + 1

WHERE ID = @id

RETURN @seq

GO

3. java

public static int getSequence(Connection connection, String sequenceName) throws Exception {

String procname = null;

procname = "{call nextval(?, ?)}";

CallableStatement cs = null;

int result = 0;

cs = connection.prepareCall(procname);

cs.setString(1, sequenceName);

cs.registerOutParameter(2, java.sql.Types.INTEGER);

cs.execute();

result = Integer.parseInt(cs.getString(2));

return result;

}