初学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 二级或无线分类设计 求sql语句
- 商品表和图片表左联,求优化方案
- 哪位大神帮忙看看这种查询该怎么实现
- 请问一下Mysql有没有instead of触发器
- 请教一个MYSQL下的SQL语句写法
- 请教一个jdbc连mysql的奇怪问题,连接只能支持一天,第一天什么问题都没有,第二天早上报措:急死了!分不够再
- 谁用过maxdb(sapdb)的?我们正在考察这个
- 请问关于mysql数据同步的问题!!急急,救救救!!!!!
- 请教:如何设置字段默认值为某个函数?
- 跪求!Hibernate mysql bug
- 怎么设定最小值
- 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);