之前没怎么接触过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
