mysql存储过程 两种写法的区别！大家讨论下
第一种写法：
drop procedure if exists Pros_Account_1;
create procedure Pros_Account_1
(
_accountId int
)
BEGIN
if exists(select 1 from `user` u where u.AccountID=_accountId)
then
select @userId:=ID from `user` where AccountID=_accountId;
if exists(select 1 from items where ItemInfoID=1063 and userId=@userId)
then
select @ItemsiD:=ID from items where ItemInfoID=1063 and userId=@userId;
update items set ItemNum=ItemNum+1 where ID=@ItemsiD;
select @ItemsiD;
else
insert into items(IteminfoId,userId,ItemNum)
values
(1063,@userId,1);
end if;
end if;
END;
drop procedure if exists Pros_Account;
create procedure Pros_Account()
BEGIN
— 定义变量
declare done1 int default 0;
declare a int;
declare b int default 1;
— Cursor one to get the group total 定义游标
declare cur1 cursor for select id from account where DATE_FORMAT(Regtime,’%Y-%m-%d %H:%i:%s’)< DATE_FORMAT(‘2011-03-18 12:00:00′,’%Y-%m-%d %H:%i:%s’);
— 游标异常处理
declare continue handler for 1329 set done1 = 1;
— Temporary table to save the result.
— 打开游标
open cur1;
set autocommit=0;
— 开始循环
while done1 != 1
do
fetch cur1 into a;
call Pros_Account_1(a);
set b=b+1;
— 结束循环
end while;
select b;
COMMIT;
— 关闭游标
close cur1;
end;
第二种写法：
DROP PROCEDURE `Pros_Account_bobby`;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`galaxy`@`%`*/ /*!50003 PROCEDURE `Pros_Account_bobby`( IN InputAccountID int )
BEGIN
DECLARE ProcUserID INT DEFAULT 0;
DECLARE ProcItemID INT DEFAULT 0;
DECLARE ProcCounter INT DEFAULT 0;
SELECT COUNT(*), IFNULL(ID,0) INTO ProcCounter, ProcUserID FROM user WHERE AccountID = InputAccountID;
IF ( ProcUserID > 0 ) THEN
SELECT COUNT(*), IFNULL(ID,0) INTO ProcCounter, ProcItemID FROM items WHERE ItemInfoID = 1063 AND UserID = ProcUserID LIMIT 1;
IF ( ProcItemID > 0 ) THEN
UPDATE items SET ItemNum = ItemNum + 1 WHERE ID = ProcItemID;
ELSE
INSERT INTO items ( `ItemInfoID`, `UserID`, `ItemNum` ) VALUES ( 1063, ProcUserID, 1);
END IF;
END IF;
END */;;
DROP PROCEDURE `Pros_Account_bobby_main`;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`galaxy`@`%`*/ /*!50003 PROCEDURE `Pros_Account_bobby_main`()
BEGIN
DECLARE FINISHDONE TINYINT DEFAULT 0;
DECLARE SendAccountID INT DEFAULT 0;
DECLARE RepeatCount INT DEFAULT 0;
DECLARE SELECTRESULT1 CURSOR FOR SELECT ID FROM account WHERE RegTime < ‘2011-03-18 12:00:00’ AND ID != ‘-1’ AND ID != ‘8139’;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET FINISHDONE = 1;
SET FINISHDONE = 0;
OPEN SELECTRESULT1;
REPEAT
FETCH SELECTRESULT1 INTO SendAccountID;
CALL `Pros_Account_bobby` ( SendAccountID );
SET RepeatCount = RepeatCount + 1;
UNTIL FINISHDONE = 1 END REPEAT;
CLOSE SELECTRESULT1;
SELECT RepeatCount;
END */;;
大家说说这两种写法有什么区别啊！
引用第一种：
if exists(select 1 from `user` u where u.AccountID=_accountId)
then
select @userId:=ID from `user` where AccountID=_accountId;
第二种
SELECT COUNT(*), IFNULL(ID,0) INTO ProcCounter, ProcUserID FROM user WHERE AccountID = InputAccountID;
IF ( ProcUserID > 0 ) THEN
探讨
这两种写法有什么区别呢？？
