第一种写法:
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 */;;大家说说这两种写法有什么区别啊!
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 */;;大家说说这两种写法有什么区别啊!
解决方案 »
- MY SQL这个问题怎么解决
- 请问postgreSQL C库(libpq)或C++库(libpq++)中有无数据库备份和恢复的接口?
- 请教一个SQL语句
- 关于把中文插到postgres数据库的问题!!!!!!!
- 通过jdbc建立mysql的存储过程,为什么这段代码总是执行错误??
- Mysql是否存在函数,可将result的结果(只有一列,N行)存为一个数组,请教了.
- 请问mysql_fetch_fields()的返回结果需要自己释放吗?
- 求教游戏同时在线人数查询
- Mysql事务问题求指教(在线等)
- MYSQL多表多字段嵌套查询怎么写?谢谢
- 请问是sql算比较有效率还是用脚本计算比较有效率?
- mysql5.1误删除root的恢复办法
第一种写法,和第二种写法都是执行相同的操作!
我是在工作中遇到了这种问题!!
我在自己本地msyql数据库上面
执行第一种方法能达到目的,
而在远程连接上却不能达到效果!
而第二种方法在远程连接上却能达到效果!
两边数据库的数据是相同的,
我这边是那边备份下来的!
我想问问这两种写法有啥区别呢!
!
逻辑是一样的!
我取一小部分你看看!
第一种:
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
还有两种游标的写法!:
第一种游标:
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_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 */;;这样两种写法有什么不同呢!还有
存储过程前面加
/*!50003 CREATE*/ /*!50020 DEFINER=`galaxy`@`%`*/ /*!50003 PROCEDURE `Pros_Account_bobby_main`()
这些东西!有不有特殊的意义呢!
这俩个的逻辑不都是一样的么?
第一种
if exists(select 1 from `user` u where u.AccountID=_accountId)是判断有这个玩家没有!
如果有的话 把Id赋值给@userId
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
先把userId的值付给ProcCounter 在根据 ProcCounter的值来判断是否有这个玩家!!
如果有的话,就直接拿起来用了!
呃呃呃..
区别在于,一个是直接select,一个是用中级变量。好处是当你逻辑有N次一样的业务场景的时候,中级变量的会节省N-1次select时间。
第一种写法:
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;
第二种写法:
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 */;;
这两种游标写法逻辑都是一样的!
那这两种游标用法又有什么不同呢!
第一种游标:
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_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 */;;这样两种写法有什么不同呢!还有
存储过程前面加
/*!50003 CREATE*/ /*!50020 DEFINER=`galaxy`@`%`*/ /*!50003 PROCEDURE `Pros_Account_bobby_main`()
这些东西!有不有特殊的意义呢!
1,cursor循环遍历中while 和repeat就像 应用中的for与while一样,没有啥大的区别的,看个人习惯问题了。2,后面的那些都是注释的,不起作用。