存儲(chǔ)過程USE?[Member]
GO
/******?Object:??StoredProcedure?[dbo].[GetNextSeq]????Script?Date:?10/25/2016?14:23:30?******/
SET?ANSI_NULLS?ON
GO
SET?QUOTED_IDENTIFIER?ON
GO
--?=============================================
--?Author:????????<>
--?Create?date:?<2016.3.31>
--?Description:????<獲取編號(hào)的下一個(gè)序列號(hào)>
--?=============================================
ALTER?PROCEDURE?[dbo].[GetNextSeq]?
????--?Add?the?parameters?for?the?stored?procedure?here
????@Code?varchar(20),??--序列編碼
????@Lenth?smallint,???--返回序列長度
????@IsDate?char(1),????--是否在序列中包含日期信息?0：不包含?1：包含
????@NewSeq?char(14)?output
AS
BEGIN
????Declare?@NextSeq?int
????Declare?@CurrentDate?char(8)
????Declare?@OldSeq?int???????????????????--表中存儲(chǔ)的當(dāng)前序列號(hào)
????Declare?@OldDate?char(8)??????????????--返回表中當(dāng)前的日期
????--Declare?@NewSeq?varchar(20)???????????--通過過程計(jì)算返回的最終編號(hào)
????Declare?@TabelSeq?Table?(NextSeq?int)?--表值變量，用于獲得最新更新到表中的序列號(hào)
????
????--判斷傳的序列編碼值是否存在
????If?Not?Exists(Select?Code?From?Sequence?Where?Code?=@Code)
????Begin
????????Set?@NewSeq?='-1'
????????Select?'-1'
????????Return
????End
????--獲取序列表中相關(guān)字段當(dāng)前值
????Select?@OldDate?=CurrentDate,@OldSeq?=NextSeq?From?Sequence?Where?Code?=@Code????????
????--獲取系統(tǒng)當(dāng)前日期，將格式轉(zhuǎn)換為20160331八位樣式
????Select?@CurrentDate?=?convert(char(8),GETDATE(),112)
????--不處理日期，表示取順序流水號(hào)
????If?@IsDate?=?'0'
????Begin
????????--將新的序列號(hào)寫入表中
????????Update?Sequence?Set?NextSeq?=NextSeq?+1?Output?inserted.NextSeq?into?@TabelSeq?Where?Code?=@Code
????????Select?@NextSeq?=NextSeq?From?@TabelSeq
????????Set?@NewSeq?=Right('0000000000000000000'?+Ltrim(Rtrim(Convert(varchar(20),@NextSeq))),@Lenth)
????????select?@NewSeq
????End
????--取日期，產(chǎn)生的流水號(hào)前八位為日期，后面為順序流水號(hào)（輸入?yún)?shù)的長度包括日期長度）
????Else?If?@IsDate?=?'1'
????Begin
????????If?@OldDate?=@CurrentDate
????????Begin
????????????Update?Sequence?Set?NextSeq?=NextSeq?+1?Output?inserted.NextSeq?into?@TabelSeq??Where?Code?=@Code
????????????Select?@NextSeq?=NextSeq?From?@TabelSeq?
????????????Set?@NewSeq?=@CurrentDate?+Right('0000000000000000000'?+Ltrim(Rtrim(Convert(varchar(20),@NextSeq))),@Lenth?-8)
????????????select?@NewSeq???????????????????????
????????End
????????Else
????????Begin
????????????Update?Sequence?Set?CurrentDate?=@CurrentDate?,NextSeq?=1?Where?Code?=@Code
????????????Set?@NewSeq?=@CurrentDate?+Right('00000000000000000001',@Lenth?-8)
????????????select?@NewSeq????????????????????????
????????End????????
????End
ENDmapper.xml<?xml?version="1.0"?encoding="UTF-8"??>
<!DOCTYPE?mapper?PUBLIC?"-//mybatis.org//DTD?Mapper?3.0//EN"?"http://mybatis.org/dtd/mybatis-3-mapper.dtd"?>
<mapper?namespace="com.jkw100.ssm.mapper.CustomerMapperCustom"?>
????<select?id="getNextSeq"?parameterMap="getNextSeqMap"?statementType="CALLABLE"?resultMap="resultNextSeqMap">
???????CALL?GetNextSeq(#{Code},#{Lenth},#{IsDate},#{NewSeq})
????</select>
????<parameterMap?type="java.util.Map"?id="getNextSeqMap">
????????<parameter?property="Code"?mode="IN"?jdbcType="VARCHAR"/>
????????<parameter?property="Lenth"?mode="IN"?jdbcType="SMALLINT"/>
????????<parameter?property="IsDate"?mode="IN"?jdbcType="CHAR"/>
????????<parameter?property="NewSeq"?mode="OUT"?jdbcType="CHAR"/>
????</parameterMap>
????<resultMap?type="java.util.Map"?id="resultNextSeqMap">
????????<result?column="NewSeq"?property="NewSeq"?javaType="String"?jdbcType="CHAR"/>
????</resultMap>
</mapper>接口方法Map<String,Object>?getNextSeq(Map<String,?Object>?map);Service接口public?Map<String,?Object>?getNextSeq(Map<String,?Object>?map)?throws?Exception;Service實(shí)現(xiàn)類@Override
????public?Map<String,?Object>?getNextSeq(Map<String,?Object>?map)?throws?Exception?{
????????return?customerMapperCustom.getNextSeq(map);
????}Controller方法@RequestMapping(value="/getNextSeq",method={RequestMethod.GET})
????@ResponseBody
????public?MessageResult?getNextSeq()
????{
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("Code","CustomerID");??
????????map.put("Lenth",?12);?
????????map.put("IsDate",?"1");?
????????map.put("NewSeq",?"newSeq");?
????????try?{
????????????System.out.println("getNextSeq:"+customerService.getNextSeq(map));
????????????return?MessageResult.ok();
????????}?catch?(Exception?e)?{
????????????e.printStackTrace();
????????????return?MessageResult.build(1,?e.getMessage());
????????}
????}錯(cuò)誤提示###?Error?querying?database.??Cause:?java.sql.SQLException:?[Microsoft][SQLServer?2000?Driver?for?JDBC][SQLServer]'@P1'?附近有語法錯(cuò)誤。
###?The?error?may?exist?in?com/jkw100/ssm/mapper/CustomerMapperCustom.xml
###?The?error?may?involve?com.jkw100.ssm.mapper.CustomerMapperCustom.getNextSeqMap
###?The?error?occurred?while?setting?parameters
###?SQL:?CALL?GetNextSeq(?,?,?,?)
###?Cause:?java.sql.SQLException:?[Microsoft][SQLServer?2000?Driver?for?JDBC][SQLServer]'@P1'?附近有語法錯(cuò)誤。
;?uncategorized?SQLException?for?SQL?[];?SQL?state?[HY000];?error?code?[102];?[Microsoft][SQLServer?2000?Driver?for?JDBC][SQLServer]'@P1'?附近有語法錯(cuò)誤。;?nested?exception?is?java.sql.SQLException:?[Microsoft][SQLServer?2000?Driver?for?JDBC][SQLServer]'@P1'?附近有語法錯(cuò)誤。",
添加回答
舉報(bào)
0/150
提交
取消