由于业务需要, MSSQL数据库转到MYSQL
之前没怎么接触过MYSQL, 看了两天资料就动手改写存储过程, 下面是改的第一个
执行成功了, 各位帮我看下有没有什么地方写的不对或语法上不规范-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`localhost` PROCEDURE `prAllUser_AddNewUser`(
IN iPUserID varchar(32) -- 来源方用户ID
, IN iPLoginName varchar(32) -- 来源方用户登录名
, IN iNickName varchar(32) -- 游戏昵称
, IN iSex char -- 性别 w/m
, IN iAdult char -- 成年用户 y/n
, IN iSite varchar(32) -- 第三方用户标识
, OUT oUserID int -- 本地用户ID
, OUT oErrMsg varchar(1024) -- 出错信息
, OUT oResult tinyint -- 出错信息
-- , @intIntroID int = -1 -- 推广员ID
)
proc : BEGIN
DECLARE txn_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error = -1;
END; IF iPUserID = '' THEN
SET oErrMsg = 'iPUserID = ';
SET oResult = -1;
leave proc;
END IF; SELECT UserID INTO oUserID FROM com_alluser_site WHERE PUserID = iPUserID AND Site = iSite;
-- ---------------------如果用户已存在,同步用户信息-----------------------
IF (oUserID > 0) THEN
IF(iNickName = '') THEN
UPDATE com_allUser
SET Sex = iSex
,Adult = iAdult
,LastTime = NOW()
WHERE UserID = oUserID;
ELSE
UPDATE com_allUser
SET NickName = iNickName
,Sex = iSex
,Adult = iAdult
,LastTime = NOW()
WHERE UserID = oUserID;
END IF;
SET oErrMsg = '登录成功,用户信息已同步';
SET oResult = 2;
leave proc;
END IF; -- ---------------------用户不存在,添加用户信息-----------------------
START TRANSACTION;
CALL prAllUser_GenUserID(oUserID); IF oUserID = -1 THEN
SET oErrMsg = '获取新用户ID出错!';
SET oResult = -1;
leave proc;
END IF; SET @FaceID = '2.png';
IF iSex = 'm' THEN
SET @FaceID = '1.png';
END IF; IF (iNickName = '') THEN
SET iNickName = '新用户' + RIGHT(iPUserID, 4);
END IF;
-- 来源映射表
INSERT INTO `usercenter`.`com_alluser_site`
(`PLoginName`,`PUserID`,`Site`,`UserID`)
VALUES
(iPLoginName,iPUserID,iSite,oUserID);
-- 基本表
INSERT INTO `usercenter`.`com_alluser`
(`Adult`,`FaceID`,`LastTime`,`NickName`,`RegTime`,`Sex`,`Status`,`UserID`)
VALUES
(iAdult,@FaceID,Now(),iNickName,Now(),iSex,1,oUserID);
-- 初始化保险箱
SET @PwdSalt = left(replace(uuid(), '-', ''),8);
SET @DBPassword = md5('888888' + @PwdSalt);
INSERT INTO `usercenter`.`com_alluser_safebox`
(`Money`,`PwdSalt`,`SafePassword`,`UserID`)
VALUES
(0,@PwdSalt,@DBPassword,oUserID); IF txn_error THEN
ROLLBACK;
SET oErrMsg = '新用户添加失败!';
SET oResult = -1;
leave proc;
END IF; COMMIT; SET oErrMsg = '新用户添加成功';
SET oResult = 1;
END
之前没怎么接触过MYSQL, 看了两天资料就动手改写存储过程, 下面是改的第一个
执行成功了, 各位帮我看下有没有什么地方写的不对或语法上不规范-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$CREATE DEFINER=`root`@`localhost` PROCEDURE `prAllUser_AddNewUser`(
IN iPUserID varchar(32) -- 来源方用户ID
, IN iPLoginName varchar(32) -- 来源方用户登录名
, IN iNickName varchar(32) -- 游戏昵称
, IN iSex char -- 性别 w/m
, IN iAdult char -- 成年用户 y/n
, IN iSite varchar(32) -- 第三方用户标识
, OUT oUserID int -- 本地用户ID
, OUT oErrMsg varchar(1024) -- 出错信息
, OUT oResult tinyint -- 出错信息
-- , @intIntroID int = -1 -- 推广员ID
)
proc : BEGIN
DECLARE txn_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
SET txn_error = -1;
END; IF iPUserID = '' THEN
SET oErrMsg = 'iPUserID = ';
SET oResult = -1;
leave proc;
END IF; SELECT UserID INTO oUserID FROM com_alluser_site WHERE PUserID = iPUserID AND Site = iSite;
-- ---------------------如果用户已存在,同步用户信息-----------------------
IF (oUserID > 0) THEN
IF(iNickName = '') THEN
UPDATE com_allUser
SET Sex = iSex
,Adult = iAdult
,LastTime = NOW()
WHERE UserID = oUserID;
ELSE
UPDATE com_allUser
SET NickName = iNickName
,Sex = iSex
,Adult = iAdult
,LastTime = NOW()
WHERE UserID = oUserID;
END IF;
SET oErrMsg = '登录成功,用户信息已同步';
SET oResult = 2;
leave proc;
END IF; -- ---------------------用户不存在,添加用户信息-----------------------
START TRANSACTION;
CALL prAllUser_GenUserID(oUserID); IF oUserID = -1 THEN
SET oErrMsg = '获取新用户ID出错!';
SET oResult = -1;
leave proc;
END IF; SET @FaceID = '2.png';
IF iSex = 'm' THEN
SET @FaceID = '1.png';
END IF; IF (iNickName = '') THEN
SET iNickName = '新用户' + RIGHT(iPUserID, 4);
END IF;
-- 来源映射表
INSERT INTO `usercenter`.`com_alluser_site`
(`PLoginName`,`PUserID`,`Site`,`UserID`)
VALUES
(iPLoginName,iPUserID,iSite,oUserID);
-- 基本表
INSERT INTO `usercenter`.`com_alluser`
(`Adult`,`FaceID`,`LastTime`,`NickName`,`RegTime`,`Sex`,`Status`,`UserID`)
VALUES
(iAdult,@FaceID,Now(),iNickName,Now(),iSex,1,oUserID);
-- 初始化保险箱
SET @PwdSalt = left(replace(uuid(), '-', ''),8);
SET @DBPassword = md5('888888' + @PwdSalt);
INSERT INTO `usercenter`.`com_alluser_safebox`
(`Money`,`PwdSalt`,`SafePassword`,`UserID`)
VALUES
(0,@PwdSalt,@DBPassword,oUserID); IF txn_error THEN
ROLLBACK;
SET oErrMsg = '新用户添加失败!';
SET oResult = -1;
leave proc;
END IF; COMMIT; SET oErrMsg = '新用户添加成功';
SET oResult = 1;
END
DECLARE pVal int
什么时候用
@VAL只知道 @VAL可以直接使用,不用先定义, 但这个是会话变量,
比如存储过程里使用了@val
执行完了存储过程, SELECT @VAL还是有值, 变量没有释放使用declare val int; 又好象只能在存储过程里BEGIN开始那地方用不明白什么时候用什么变量
GO
/****** Object: StoredProcedure [dbo].[User_Exists] Script Date: 05/08/2013 13:14:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[User_Exists]
@users varchar(50)
AS
BEGIN
Declare @tem int
SET NOCOUNT ON;
if exists(select [username] from [users] where [username] = @users)
set @tem = 1
else
set @tem = 0
return @tem
END
可以帮忙改一下吗,MYSQL不是很了解,改一个列子参考一下,万分感谢!