初学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
解决方案 »
- 查询录入mysql数据时float类型数据出现误差,怎么回事?
- MYSQL如何创建两个游标呀??
- 问个简单的问题insert嵌套select
- 这样逻辑的应用,用MYSQL的select要怎么写呢?里面有没有提供这样的函数呢?(没那么多分了,各位帮帮忙吧!)
- 不包含某字符串用SQL查询怎么写
- 支招:用mysql是否安全?
- 新手安装Mysql的问题!! 请大家多帮忙!!
- 如何才能拷贝带有外键的数据库?
- 数据库表容量越来越大解决方法 查询越来越慢
- NT 上已安装好mysql服务器,WIN98 client连接不上,求救
- 怎么设定最小值
- mysql无法连接 使用PHPMYADMIN无法对数据库进行操作 提示表在使用中
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);