初学MySql,写存储过程,好多语法与SQL不同,以下是使用SQL写得存储过程,能否帮忙修改成MySql,以供学习参考,谢谢。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[IDCardTo18]
(
@IDCardNum nvarchar(20) OUTPUT,
@DateBount int -- 时间分界。19XX年为19,18XX年为18……
)
AS
SET NOCOUNT ON
DECLARE @ErrCode AS smallint -- 错误代码。-1为时间分界错误,-2为身份证长度错误。
DECLARE @Year smallint
DECLARE @YearFrist smallint
DECLARE @YearSecond smallint
SET @Year = 2
SET @ErrCode =
CASE
WHEN LEN(@IDCardNum) <> 15 THEN -2
WHEN (@DateBount < 10) OR (@DateBount > 99) THEN -1
END
IF @ErrCode <> 0
BEGIN
RETURN @ErrCode
END
SET @YearFrist = CAST(SUBSTRING(CAST(@DateBount AS nvarchar(2)),1,1) as smallint)
SET @YearSecond = CAST(SUBSTRING(CAST(@DateBount AS nvarchar(2)),2,1) as smallint)
IF (SUBSTRING(@IDCardNum,13,3) NOT IN('999','998','997','996'))
BEGIN
SET @IDCardNum = SUBSTRING(@IDCardNum, 1, 6) + CAST(@DateBount AS nvarchar(2))+SUBSTRING(@IDCardNum, 7, 9)+
SUBSTRING('10X98765432',
(
CAST(SUBSTRING(@IDCardNum, 1, 1) AS int) * 7
+ CAST(SUBSTRING(@IDCardNum, 2, 1) AS int) * 9
+ CAST(SUBSTRING(@IDCardNum, 3, 1) AS int) * 10
+ CAST(SUBSTRING(@IDCardNum, 4, 1) AS int) * 5
+ CAST(SUBSTRING(@IDCardNum, 5, 1) AS int) * 8
+ CAST(SUBSTRING(@IDCardNum, 6, 1) AS int) * 4
+ @YearFrist * 2
+ @YearSecond * 1
+ CAST(SUBSTRING(@IDCardNum, 7, 1) AS int) * 6
+ CAST(SUBSTRING(@IDCardNum, 8, 1) AS int) * 3
+ CAST(SUBSTRING(@IDCardNum, 9, 1) AS int) * 7
+ CAST(SUBSTRING(@IDCardNum, 10, 1) AS int) * 9
+ CAST(SUBSTRING(@IDCardNum, 11, 1) AS int) * 10
+ CAST(SUBSTRING(@IDCardNum, 12, 1) AS int) * 5
+ CAST(SUBSTRING(@IDCardNum, 13, 1) AS int) * 8
+ CAST(SUBSTRING(@IDCardNum, 14, 1) AS int) * 4
+ CAST(SUBSTRING(@IDCardNum, 15, 1) AS int) * 2
)
% 11 + 1, 1)
END
SET NOCOUNT OFF
RETURN
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[IDCardTo18]
(
@IDCardNum nvarchar(20) OUTPUT,
@DateBount int -- 时间分界。19XX年为19,18XX年为18……
)
AS
SET NOCOUNT ON
DECLARE @ErrCode AS smallint -- 错误代码。-1为时间分界错误,-2为身份证长度错误。
DECLARE @Year smallint
DECLARE @YearFrist smallint
DECLARE @YearSecond smallint
SET @Year = 2
SET @ErrCode =
CASE
WHEN LEN(@IDCardNum) <> 15 THEN -2
WHEN (@DateBount < 10) OR (@DateBount > 99) THEN -1
END
IF @ErrCode <> 0
BEGIN
RETURN @ErrCode
END
SET @YearFrist = CAST(SUBSTRING(CAST(@DateBount AS nvarchar(2)),1,1) as smallint)
SET @YearSecond = CAST(SUBSTRING(CAST(@DateBount AS nvarchar(2)),2,1) as smallint)
IF (SUBSTRING(@IDCardNum,13,3) NOT IN('999','998','997','996'))
BEGIN
SET @IDCardNum = SUBSTRING(@IDCardNum, 1, 6) + CAST(@DateBount AS nvarchar(2))+SUBSTRING(@IDCardNum, 7, 9)+
SUBSTRING('10X98765432',
(
CAST(SUBSTRING(@IDCardNum, 1, 1) AS int) * 7
+ CAST(SUBSTRING(@IDCardNum, 2, 1) AS int) * 9
+ CAST(SUBSTRING(@IDCardNum, 3, 1) AS int) * 10
+ CAST(SUBSTRING(@IDCardNum, 4, 1) AS int) * 5
+ CAST(SUBSTRING(@IDCardNum, 5, 1) AS int) * 8
+ CAST(SUBSTRING(@IDCardNum, 6, 1) AS int) * 4
+ @YearFrist * 2
+ @YearSecond * 1
+ CAST(SUBSTRING(@IDCardNum, 7, 1) AS int) * 6
+ CAST(SUBSTRING(@IDCardNum, 8, 1) AS int) * 3
+ CAST(SUBSTRING(@IDCardNum, 9, 1) AS int) * 7
+ CAST(SUBSTRING(@IDCardNum, 10, 1) AS int) * 9
+ CAST(SUBSTRING(@IDCardNum, 11, 1) AS int) * 10
+ CAST(SUBSTRING(@IDCardNum, 12, 1) AS int) * 5
+ CAST(SUBSTRING(@IDCardNum, 13, 1) AS int) * 8
+ CAST(SUBSTRING(@IDCardNum, 14, 1) AS int) * 4
+ CAST(SUBSTRING(@IDCardNum, 15, 1) AS int) * 2
)
% 11 + 1, 1)
END
SET NOCOUNT OFF
RETURN
1、LEN -》 LENGTH
2、DECLARE @YearFrist smallint -》 DECLARE YearFrist smallint;
3、IF -》IF .... THEN END IF;
4、SUBSTRING(@IDCardNum, 2, 1) -》 SUBSTRING(IDCardNum, 1, 2);